Getting Started

This guide will help you get started with the PFS Target Database.

Prerequisites

PostgreSQL database

You need a PostgreSQL server to host the database. If you do not have a PostgreSQL server, you can use a Docker container to host a database for testing without affecting the host environment. To set up a PostgreSQL Docker container, you need to install Docker from Docker Hub. See the examples for more details.

Q3C extension

The Q3C extension is required for the database. You can install it by the following instruction in the q3c repository

Python environment

Python and the following packages as well as their dependencies will be required for targetdb. The dependencies are automatically installed when you install the targetdb package via pip. Package versions shown here are those used for the development (as of May 2026). Newer (and somewhat older) versions should also work.

Package	Version
Python	3.12.12
alembic	1.18.0
Astropy	7.20
loguru	0.7.3
NumPy	2.4.0
openpyxl	3.1.5
pandas	2.3.3
psycopg2-binary	2.9.11
pyarrow	22.0.0
requests	2.32.5
SQLAlchemy	2.0.45
SQLAlchemy-Utils	0.42.1
tabulate	0.9.0
Typer	0.21.1

For building the documentation, the following packages are required.

Package	Version
MkDocs	1.6.1
mkdocs-material	9.7.1

Additionally, the following tools may be useful for testing and development.

• SchemaCrawler
• md-to-pdf
• Docker
• DBeaver
• tbls

Installation

Installation of the targetdb package can be done by the following command:

# clone the GitHub repository
git clone https://github.com/Subaru-PFS/ets_target_database.git

# move to the directory
cd ets_target_database

# Install with uv
uv sync

# (optional) create a virtual environment and activate it
# python3 -m venv .venv
# source .venv/bin/activate
# python3 -m pip install -e .

Quick Start

Create the targetdb

The dbconf.toml is used as a configuration file for the database connection.

dbconf.toml

[targetdb.db]
host = "localhost"  # database host
port = 5432  # database port
dbname = "targetdb"  # database name
user = "admin"  # database user
password = "admin"  # database password
dialect = "postgresql"  # database dialect


[schemacrawler]
SCHEMACRAWLERDIR = "<path to schemacrawler directory>"  # "_schemacrawler/bin/schemacrawler.sh" under the path will be used

The following commands are to create the targetdb database, install the Q3C extension, create tables, and generate an entity-relationship diagram of the database.

# create a database
pfs-targetdb-cli create-db -c dbconf.toml

# install the Q3C extension
pfs-targetdb-cli install-q3c -c dbconf.toml

# create tables in the database
pfs-targetdb-cli create-schema -c dbconf.toml

# when installed via uv, the command can be run as
# uv run pfs-targetdb-cli create-db -c dbconf.toml
# uv run pfs-targetdb-cli install-q3c -c dbconf.toml
# uv run pfs-targetdb-cli create-schema -c dbconf.toml

Generate an ER diagram

# draw the ER diagram
pfs-targetdb-cli diagram -c dbconf.toml

An ER diagram will be generated as erdiagram_targetdb-YYYYMMDDHHmmss.pdf in the current directory. Note that SchemaCrawler must be installed and the directory must be set in the dbconf.toml file.

Python example

To connect to the database with Python, you can use the following code snippet.

from targetdb import TargetDB

# create a database connection
db = TargetDB(host="localhost", port= 5432, dbname="testdb", user="admin", password="admin")
db.connect()

# fetch all data from the input_catalog table as pandas.DataFrame
df = db.fetch_all("input_catalog")

# print the first 5 rows (should return an empty dataframe)
print(df.head())
# Empty DataFrame
# Columns: [input_catalog_id, input_catalog_name, input_catalog_description, upload_id, created_at, updated_at]
# Index: []

# close the connection
db.close()

Running Tests Locally

The unit tests (tests/test_*.py, excluding tests/integration/) do not require a database and can be run at any time:

uv run pytest tests -m "not integration"

The integration tests (tests/integration/) exercise the same sequence of pfs-targetdb-cli commands as the Test Database GitHub Actions workflow (.github/workflows/test_database.yml): creating the database, installing the Q3C extension, creating the schema, and inserting the example data in examples/data/, this time also verifying the inserted rows with SQL queries. They require Docker (for a disposable PostgreSQL + Q3C container) and are skipped automatically, with a reason, if Docker is not available.

# Docker must be running (e.g. Docker Desktop or OrbStack on macOS)
uv run pytest tests/integration -v

A dedicated PostgreSQL container is built from tests/docker/Dockerfile and started/stopped automatically for the test session by docker compose (tests/docker/docker-compose.test.yml). It listens on localhost:15433 by default; set TARGETDB_TEST_PG_PORT to use a different port. Its data directory is a tmpfs mount, so nothing is left on disk once the tests finish, and all generated files (converted flux standard catalogs, transferred target lists) are written under a pytest-managed temporary directory rather than into examples/data/.

If a test fails and you want to inspect the database afterwards, pass --keep-db to leave the container running:

uv run pytest tests/integration -v --keep-db
psql -h localhost -p 15433 -U postgres -d test_targetdb

# once done, stop the container manually
docker compose -f tests/docker/docker-compose.test.yml down -v

Build the Documentation

The documentation can be built by the following command:

# Install the required packages for building the documentation
python3 -m pip install -e ".[doc]"

# or via uv
# uv sync --extra doc

# Build the documentation with MkDocs
mkdocs build

# or via uv
# uv run mkdocs build

The documentation will be generated in the site directory.