pgdevkit

A helper for developing with Postgres.

pgdb compare

Compare a directory of SQL scripts (see the database-in-source layout convention) against a live database and report differences:

pgdb compare --url postgresql://user:pass@host:port/db path/to/database/

Entra ID auth (Azure Postgres / Databricks Lakebase)

Pass --entra-user <identity> to pgdb compare to authenticate with an Entra ID token instead of a static password. Which token flow is used is auto-detected from the database hostname:

• Azure Database for PostgreSQL (*.postgres.database.azure.com, *.postgres.cosmos.azure.com) — the default: fetches a token via DefaultAzureCredential and uses it directly as the password. Requires the azure extra: pip install pgdevkit[azure].
• Databricks Lakebase (*.database.azuredatabricks.net, *.database.cloud.databricks.com) — fetches a Databricks-scoped Entra token, then exchanges it for a short-lived Postgres credential via the Databricks workspace API. Also requires --databricks-workspace-host and --databricks-instance:

pgdb compare --url postgresql://instance-abc.database.azuredatabricks.net:5432/databricks_postgres \
  --entra-user alice@example.com \
  --databricks-workspace-host https://adb-123456789.azuredatabricks.net \
  --databricks-instance myinstance \
  path/to/database/

(--url's own user/password, if any, are discarded and replaced — --entra-user plus the fetched token become the connection's actual credentials.)

MSSQL

pgdb compare/pgdb fetch-missing default to Postgres. Pass --dialect mssql to compare against a SQL Server database instead:

pgdb compare --dialect mssql --url "Server=host,1433;Database=db;UID=user;PWD=pass" path/to/database/

Requires the mssql extra: pip install pgdevkit[mssql] (pulls in mssql-python, which bundles its own driver — no system ODBC driver install needed). MSSQL has no composite type or native enum equivalent, so those areas of a database/ tree don't have a direct equivalent on this backend — see docs/database-layout.md.

pgdb compare --dialect mssql --entra-user <identity> appends Authentication=ActiveDirectoryDefault to the connection string so mssql-python gets an Entra ID token via DefaultAzureCredential (also install the azure extra). The identity is whatever that credential chain resolves; the flag's value only switches Entra auth on. Current Azure SQL/SQL Server (2025+) does have a native json column type, which parses/introspects/diffs like any other column type; see "pgdevkit.db — helpers for application code" below for how JSON values are handled on the CRUD side (write-side serialization only, no auto-parsing on read — mssql-python doesn't distinguish json columns from nvarchar).

Area and schema filtering

Two independent, composable ways to narrow which files a command touches: area is an explicit opt-in tag; schema is derived automatically from each file's own SQL.

Area tagging

Any migration file or database/ code file can declare one or more areas by starting with a -- area: comment:

-- area: billing
CREATE TABLE billing.invoices (id int primary key);

A file can declare more than one area, either comma-separated on one line (-- area: billing, reporting) or across several -- area: lines — the declared areas union. The directive is only recognized in the file's leading comment block (blank lines and -- comments at the very top, stopping at the first real statement); a -- area: comment later in the file doesn't count. A file with no directive is untagged, and untagged files are treated as shared/common.

Schema filtering

No tag needed — schema membership is parsed straight out of the SQL itself: every schema-qualified (or default-schema, when unqualified) table/view/ function/index reference across every statement in the file, DDL or DML alike, plus any CREATE SCHEMA name. A file whose schema(s) can't be determined (unparseable content, or no table/schema reference in it at all) is treated the same as an untagged file — always kept.

Options

pgdb compare, pgdb migrate check, pgdb migrate apply, pgdb testdb up, and pgdb testdb reset all accept:

• --area NAME (repeatable) — restrict to files declaring one of the given areas, plus every untagged file (untagged files always stay in scope).
• --exclude-area NAME (repeatable) — drop files declaring one of the given areas; untagged files are never dropped by this.
• --schema NAME (repeatable) — restrict to files referencing one of the given schemas, plus every file with no detectable schema reference.
• --exclude-schema NAME (repeatable) — drop files referencing one of the given schemas; files with no detectable reference are never dropped.

All four can be combined — a file must pass every filter it's subject to (an area match doesn't excuse a schema mismatch, and vice versa), and a file matching both an included and an excluded value on the same axis is excluded. Passing none of them applies no filtering (the default, unchanged behavior).

pgdb migrate apply path/to/database/_migration_scripts --url ... --area billing
pgdb compare path/to/database/ --url ... --exclude-area reporting
pgdb migrate check path/to/database/_migration_scripts --url ... --schema billing --exclude-schema reporting
pgdb testdb up --schema billing

compare's default report (no --report-extra-db) only checks that the filtered scripts exist correctly in the DB, so it composes safely with area and schema filtering. Passing --report-extra-db together with either kind of filter also reports every DB object outside the filtered area(s)/ schema(s) as "missing in scripts" — since the live database has no concept of areas, and isn't itself filtered by --schema either — only the scripts side is filtered — so treat that combination's "missing in scripts" results with that in mind (the CLI prints a warning when you combine them).

pgdb fetch-missing deliberately has no --area/--exclude-area (or --schema/--exclude-schema): it diffs the full database against scripts to find genuinely untracked objects, so narrowing the scripts side would make every object tracked under a different area/schema look "missing" too — and --write would then reconstruct a duplicate file for something that already exists.

pgdevkit.areas exposes the tag-filtering logic for scripting: parse_areas/file_areas read a file's declared areas, and area_allowed/filter_by_area apply the only/exclude semantics above. pgdevkit.schemas exposes the equivalent for schema filtering: sql_schemas/file_schemas detect a file's referenced schemas, and schema_allowed/filter_by_schema apply the same only/exclude semantics. pgdevkit.migrate.list_migration_files/pending_migrations and pgdevkit.parser.parse_directory take both pairs of keyword arguments (areas/exclude_areas and schemas/exclude_schemas); pgdevkit.fetch_missing.find_missing_objects takes neither, for the reason above.

Environment-tagged files (<name>.<env>.sql)

A file whose name ends .<env>.sql (e.g. grants.prod.sql, seed.staging.sql) is only in scope when targeting that environment; a plain <name>.sql file is untagged and always in scope, regardless of environment. .init.sql (see docs/database-layout.md) is reserved and is never treated as an environment tag.

• pgdb testdb up/pgdb testdb reset accept --env (default local_test) — so an untagged grants.sql always applies, but grants.prod.sql is skipped unless run with --env prod.
• pgdb migrate check/pgdb migrate apply accept --env too, but it's optional with no default: omit it and every file is a candidate regardless of its tag (unchanged, today's behavior); pass it to restrict to files tagged for that environment plus untagged ones.

pgdb testdb up --env prod   # apply prod-tagged files too, against the local test container
pgdb migrate apply path/to/database/_migration_scripts --url ... --env prod

pgdevkit.envtag exposes the same logic for scripting: file_env reads a file's tag, env_allowed applies the filtering semantics above, and strip_env_suffix returns a tagged file's logical name (e.g. grants.prod.sql -> "grants").

pgdb testdb

Manages a single shared, Podman-backed Postgres container for local tests across all your projects — no more one-container-per-project-per-worktree. Isolation between projects and worktrees is per-database, inside one container.

Add to pyproject.toml:

[tool.pgdevkit]
name = "myproject"        # optional; defaults to the repo directory name
database_dir = "database" # optional; defaults to "database"

Add to conftest.py:

import os
import pytest
from pgdevkit.testdb import ensure_testdb

@pytest.fixture(scope="session", autouse=True)
def ensure_test_postgres():
    for k, v in ensure_testdb().items():
        os.environ[k] = v

CLI: pgdb testdb up|reset|run-sql|status|shell|clean|list-orphaned. up/reset accept --env (default local_test) — see "Environment-tagged files" above.

up/reset accept --area/--exclude-area and --schema/--exclude-schema (see "Area and schema filtering" above) to scope which database/ files get applied — e.g. pgdb testdb up --schema billing for a test DB with only the billing schema's tables/views/functions, without waiting on the rest of the project's schema to apply. ensure_testdb/reset_testdb take the same keyword arguments when called from Python (e.g. from a pytest fixture).

Every git worktree/branch of a project gets its own database, named after project_name + branch (see pgdevkit.testdb.naming.workspace_db_name). Removing a worktree (or deleting its directory without git worktree remove) doesn't drop its database — pgdb testdb list-orphaned lists this project's databases whose worktree no longer exists, and pgdb testdb clean --orphaned drops them (as opposed to --all, which drops every database of this project regardless of whether its worktree is still live). The same is available from Python as pgdevkit.testdb.find_orphaned_dbs() and pgdevkit.testdb.clean_testdb(orphaned=True).

If your project's own test setup also creates a sibling database per worktree (e.g. <main_db>_myservice for a mock service used only by that project's tests), add its literal suffix so orphan detection knows it belongs to a live worktree too:

[tool.pgdevkit]
extra_db_suffixes = ["_myservice"]

pgdevkit.testdb.workspace_db_names(project_root=None) is the single-workspace analog of find_orphaned_dbs(): it returns the exact set of DB names (main + any extra_db_suffixes) owned by the branch currently checked out at project_root, without touching Postgres or diffing against other worktrees. Useful for a caller that's about to remove one specific worktree and wants to know exactly which DB(s) go with it.

Container connection defaults (localhost:54322, postgres/testpwd) can be overridden with PGDEVKIT_TESTDB_HOST, PGDEVKIT_TESTDB_PORT, PGDEVKIT_TESTDB_USER, PGDEVKIT_TESTDB_PASSWORD, and the image with PGDEVKIT_TESTDB_IMAGE. Before touching the Docker API, pgdevkit first checks (with a short timeout) whether Postgres is already reachable at that address and skips container management if so. Set PGDEVKIT_SKIP_CONTAINER=1 to always assume it's already there and skip that check too.

Docker-level resource knobs are unset by default (Docker/Podman's own defaults apply) and can be overridden with PGDEVKIT_TESTDB_SHM_SIZE (e.g. 256m, useful when Docker's 64MB default shared-memory size is too small for parallel query workers), PGDEVKIT_TESTDB_MEM_LIMIT (e.g. 1g), PGDEVKIT_TESTDB_CPUS (e.g. 2 or 0.5), and PGDEVKIT_TESTDB_TMPFS — docker-CLI --tmpfs syntax (/path:options), semicolon-separated for multiple mounts, e.g. /var/lib/postgresql/data:size=512m.

All of the above (including _HOST/_PORT/_USER/_PASSWORD/_IMAGE) only take effect when pgdevkit actually creates the container, not when it finds and restarts an existing stopped one with the same name — remove the existing container (docker rm -f pgdevkit-postgres) first if you've changed any of these on a machine that already has one.

Container management goes through the Docker API (the docker package, docker.from_env(), falling back to Podman's rootful/rootless socket) — it works against a real Docker daemon or Podman transparently, no CLI binary required either way.

To point at a local Postgres install instead of the container — useful when neither is available, or you'd rather use peer authentication as the current OS user — set PGDEVKIT_TESTDB_HOST to the unix socket directory (e.g. /var/run/postgresql) and PGDEVKIT_TESTDB_PASSWORD="". The role named by PGDEVKIT_TESTDB_USER must exist and match your OS user (CREATE ROLE <user> SUPERUSER LOGIN;) and pg_hba.conf must allow peer auth for local connections (Debian/Ubuntu Postgres ships this by default).

MSSQL

Add engine = "mssql" to [tool.pgdevkit] (or set PGDEVKIT_TESTDB_ENGINE=mssql for a one-off run) to manage a shared SQL Server container instead of Postgres — same one-container-per-machine, one-database-per-workspace model. Requires the mssql extra (see above).

Container defaults (localhost:14330, sa/a generated complexity-valid password) can be overridden with PGDEVKIT_TESTDB_MSSQL_HOST, _PORT, _USER, _PASSWORD, _IMAGE, _MEMORY_LIMIT_MB. The container only bootstraps the sa login — additional logins are a known limitation.

The same Docker-level resource knobs as the Postgres container are available here too: PGDEVKIT_TESTDB_MSSQL_SHM_SIZE, PGDEVKIT_TESTDB_MSSQL_MEM_LIMIT (a container-level cgroup limit, distinct from _MEMORY_LIMIT_MB above which only tunes SQL Server's own internal memory management), PGDEVKIT_TESTDB_MSSQL_CPUS, and PGDEVKIT_TESTDB_MSSQL_TMPFS. As with the Postgres container, these only take effect when pgdevkit creates the container, not when restarting an existing stopped one (docker rm -f pgdevkit-mssql first if you've changed any of them).

pgdb testdb shell execs into sqlcmd (an external prerequisite, the same category as psql for the Postgres path) rather than a Python REPL.

pgdb migrate

Applies numbered, forward-only SQL migration files from a directory to a live Postgres or MSSQL database, tracking each one in a schema.table (default public.schema_migrations, dbo.schema_migrations for MSSQL) so repeat runs only apply what's pending.

pgdb migrate check  path/to/database/_migration_scripts --url postgresql://user:pass@host:port/db
pgdb migrate apply  path/to/database/_migration_scripts --url postgresql://user:pass@host:port/db

MSSQL

Pass --dialect mssql (needs the mssql extra) to both check and apply, with --url as an ODBC connection string:

pgdb migrate apply path/to/database/_migration_scripts --dialect mssql \
    --url "Server=host,1433;Database=db;UID=user;PWD=pass"

• Migration files are T-SQL: they are split into batches on standalone GO lines (needed for e.g. CREATE VIEW, which must be first in its batch), and all batches of one file run in a single transaction that is rolled back if any batch fails (including a failure in a later statement of a multi-statement batch). GO <count> is not honored: the batch runs once.
• The tracking table needs filename nvarchar(450) primary key, applied_at datetimeoffset not null default sysdatetimeoffset(), applied_by nvarchar(128) not null default suser_sname(). As on Postgres, a migration creating it can bootstrap it. Override its name with mssql_migrations_table in [tool.pgdevkit] (the Postgres migrations_table key is deliberately ignored for MSSQL) or with --tracking-table.
• --ask auto-detects "already done" for single-statement GO batches that create a table, view or schema, or add a column; anything else is asked about.
• Post-apply verification checks every CREATE TABLE target via OBJECT_ID.
• --entra-user appends Authentication=ActiveDirectoryDefault to the connection string, so mssql-python fetches an Entra ID token through azure-identity's DefaultAzureCredential (install pgdevkit[mssql,azure]). The identity is whatever that credential chain resolves — the flag's value only switches Entra auth on. It can't be combined with an Authentication= already in the connection string.
• pgdevkit.migrate.missing_privileges is not available for MSSQL.

For Postgres, --entra-user works the same as pgdb compare (see above). The tracking table needs filename text primary key, applied_at timestamptz not null default now(), applied_by text not null default current_user (a migration file that creates it, in the same directory, is the usual way to bootstrap it — inserting into a not-yet-existing tracking table is tolerated so that migration can still run).

The tracking table defaults to public.schema_migrations. Override it per-project in pyproject.toml:

[tool.pgdevkit]
migrations_table = "myschema.migrations"

or per-invocation with --tracking-table, which takes precedence over the pyproject.toml value.

--ask prints each pending file and asks yes/no/already-done/quit before running it. Answering yes queues the file on a background worker and moves straight to the next prompt — you can keep reviewing while earlier files are still executing, instead of waiting on each one before seeing the next. A tqdm progress bar tracks the queue; migrations still run one at a time, in file order. Without --ask, apply queues every pending file up front and shows the same progress bar. --file <name> applies a single file (still through the same verify-and-track path) instead of walking all pending ones. Pass --yes to skip the "about to run migrations against ..." confirmation prompt (e.g. in CI).

After each file's DDL is applied, apply re-checks that every CREATE TABLE statement's target actually exists (via to_regclass) before recording the file as applied — catching a migration that silently rolled back. That check parses each statement with sqlglot and only falls back to a regex (run against comment-stripped SQL) for statements sqlglot's postgres dialect can't parse, so a CREATE TABLE mentioned only in a -- comment is never mistaken for a real one.

pgdevkit.migrate is also usable directly as a library — list_migration_files, applied_migrations, pending_migrations, and apply_migration are the same functions the CLI calls, so a project can script around them without shelling out. The database-touching ones take dialect="postgres" | "mssql".

pgdevkit.db — helpers for application code

Install with the db extra: pip install pgdevkit[db].

• TableModel (formerly PostgresTableModel, still importable under that name) — a pydantic.BaseModel base class for models that map 1:1 to a table row, for either engine. Implement get_table_name() (returns (schema, table)) and get_primary_key() on each model.
• PgPool — an async connection pool keyed off {env_prefix}HOST/PORT/DB/USER/PASSWORD env vars. Call await pool.open() once at startup, then use async with pool.connection() as con:. Pass entra_user to authenticate via Entra ID instead of a static password — same host-based auto-detection as pgdb compare's --entra-user. For Lakebase hosts, also set the {env_prefix}DATABRICKS_WORKSPACE_HOST and {env_prefix}DATABRICKS_INSTANCE env vars.
• fetch_all(query, params=None, *, model=None, row_mapper=None, con=None, pool=None, cancel=None) — run one custom query and get every row back as dicts, as validated Pydantic model instances, or through a row_mapper. Pass con to run on a connection you already hold (it is borrowed: never committed, closed or released); otherwise a connection is taken from pool or the pool registered with set_default_pool(). Set the cancel asyncio.Event to abort the query on the server (QueryCanceled); cancelling the awaiting task cancels it there too, and CancelledError propagates as usual. query must be a literal string, a sqlglot expression, a psycopg.sql composable or a t-string — never an f-string (a sqlglot expression is only as safe as the strings it was built from: pass user values as exp.Placeholder + params).
• CRUD functions — pg_retrieve, pg_retrieve_many, pg_insert, pg_insert_many, pg_update, pg_update_dict, pg_upsert, pg_upsert_dict, pg_upsert_many, pg_upsert_many_dict, pg_delete, pg_delete_dict — typed (TableModel-based) or dict-based CRUD against a table, built on psycopg for safe identifier/value handling. The mssql extra provides an mssql_*-prefixed mirror of the same functions in pgdevkit.db.mssql_crud, built on mssql-python (MERGE-based upsert, OUTPUT instead of RETURNING) — MSSQL has no composite/enum equivalent, so complex_helper is always None on that path. It does have a native json column type on current versions (and the older NVARCHAR(MAX)-plus-OPENJSON() convention works on any version), but mssql-python has no auto-serialization for dict/list parameter values (binding one raises TypeError) and no way to distinguish a json column from nvarchar on fetch — so every mssql_* write function serializes dict/list values to JSON text automatically (db.mssql_sql.json_encode_value), while reads always come back as plain str; deserialize with json.loads() yourself if you need the parsed value back.
• SqlLoader — loads and caches .sql files from {root}/<topic>/<name>.sql, for keeping hand-written queries out of Python source.

from pgdevkit.db import PgPool, PostgresTableModel, pg_retrieve, pg_upsert

class Widget(PostgresTableModel):
    id: int
    name: str

    @staticmethod
    def get_table_name() -> tuple[str, str]:
        return ("public", "widget")

    @staticmethod
    def get_primary_key() -> list[str]:
        return ["id"]

pool = PgPool(env_prefix="POSTGRES_")
await pool.open()
async with pool.connection() as con:
    widget = await pg_retrieve(con, Widget, {"id": 1})
    await pg_upsert(con, Widget(id=1, name="thing"), Widget)

Releasing

Bump version in pyproject.toml as part of your PR, same as any other change. Once that PR merges to main and the Python Test workflow passes for that commit, .github/workflows/auto-release.yml automatically tags it vX.Y.Z, cuts a GitHub Release (skipping if that version was already released, e.g. a merge that didn't touch the version), and dispatches python-publish.yml to publish it to PyPI — no manual release step, and no extra secret to configure. Two non-obvious GitHub Actions quirks shaped this (see the comments at the top of auto-release.yml for the full reasoning, since both were hit and confirmed the hard way):

• A release created with the default GITHUB_TOKEN does not trigger other workflows' release: published listeners (an anti-recursion safeguard) — workflow_dispatch is the documented exception, so auto-release.yml dispatches python-publish.yml directly (gh workflow run) instead of relying on the release to cascade into it.
• python-publish.yml deliberately stays a plain, directly-triggered top-level workflow rather than something auto-release.yml calls via workflow_call: PyPI's OIDC trusted publishing does not support reusable/called workflows and silently rejects the token in that shape.

workflow_dispatch (or an actual GitHub UI release) on python-publish.yml still works as a manual fallback if you ever need to re-publish a version without going through auto-release.yml.