Skip to content

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
pfs-utils 7.2026.3100
psycopg 3.3.4
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

pfs-utils is installed straight from GitHub (targetdb.TargetDB subclasses pfs.utils.database.db.DB), so a .git directory and network access are needed at install time.

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.

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 (optional, see below)
dialect = "postgresql"  # database dialect


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

dialect = "postgresql" is resolved to the psycopg3 driver (postgresql+psycopg); psycopg2 is no longer used. Existing configuration files need no change.

Keeping the password out of dbconf.toml

password is optional. Leave it out and it is omitted from the connection URL entirely, so libpq resolves the credential itself -- first PGPASSWORD, then PGPASSFILE or ~/.pgpass. This lets a configuration file with no secrets in it be kept under version control.

dbconf.toml (no password)
[targetdb.db]
host = "pfsa-db.example.org"
port = 5433
dbname = "targetdb"
user = "admin"
dialect = "postgresql"
~/.pgpass
pfsa-db.example.org:5433:targetdb:admin:the-actual-password

Two things to watch out for:

  • Each ~/.pgpass field is matched against the corresponding TOML value as a literal string. localhost and a fully qualified domain name are different hosts as far as libpq is concerned, even when they resolve to the same server. * may be used as a wildcard for any of the first four fields.
  • The file must be mode 0600. libpq ignores it silently otherwise -- there is no warning, only an authentication failure.

When password is present in the TOML it is used as before and takes precedence, which is the same ordering libpq itself applies.

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()

Connecting without arguments

TargetDB() can be called with no arguments at all. Any parameter left unset falls back to a class default (TargetDB.DEFAULT_HOST, DEFAULT_PORT, DEFAULT_DBNAME, DEFAULT_USER), which point at the production database using obsproc, a read-only account:

from targetdb import TargetDB

with TargetDB() as db:
    df = db.fetch_all("input_catalog")

Because obsproc has no password default, this only works once ~/.pgpass has a matching entry (mode 0600, as described above):

~/.pgpass
pfsa-db:5433:targetdb:obsproc:<password>

obsproc can only SELECT; inserting or updating still requires a config file with an explicit, privileged user (as in the pfs-targetdb-cli examples above). To point the bare TargetDB() form at a different database for a whole process -- a dev instance, say -- call TargetDB.set_default_connection(...) once before constructing any instances:

TargetDB.set_default_connection(host="pfsa-db", port=5437, dbname="targetdb_dev")
db = TargetDB()  # now defaults to targetdb_dev

Database Migrations with Alembic

Each directory under alembic/ (local_test/, pfsa-db01-gb/) has its own alembic.ini and revision history. Run alembic from within the relevant directory. The production and development databases share the pfsa-db01-gb/ history.

The env.py scripts build the connection URL from the same TOML configuration file the CLI uses. Point TARGETDB_CONF at it:

cd alembic/local_test
TARGETDB_CONF=~/database_configs/config_targetdb.toml alembic upgrade head

This keeps the credentials in one place instead of duplicating them between dbconf.toml and alembic.ini, and means the switch to psycopg3 is picked up automatically without hand-editing the sqlalchemy.url of each deployment. Combined with the ~/.pgpass arrangement above, an alembic.ini with no secrets in it can be kept in the repository.

When TARGETDB_CONF is not set, env.py falls back to the sqlalchemy.url entry in alembic.ini as before.

See alembic/README.md for the full migration workflow and the gotchas specific to this schema.

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.