pgdeclare

Your database schema should be code you can read.

With migration files it is not. The shape of a table is the sum of every ALTER ever written against it, so answering "what columns does account have, and which are nullable?" means replaying the history in your head or asking a live database. Reviewers read deltas, not the schema. An LLM reading the repository sees the same deltas and has to reconstruct the state too, and gets it wrong in the same places people do. Managing state in code is hard enough; when the code is the state, written as a pile of changes, it is rough.

pgdeclare keeps the schema as declarations: one CREATE TABLE per table, in plain SQL files, always describing the database as it is now. You change a table by editing its definition. pgdeclare builds the declared schema in a scratch database, diffs it against the live one through pg_catalog, and applies the difference.

A diff cannot infer everything, such as a rename or a backfill. For those you write small imperative @migrate-before / @migrate-after blocks next to the DDL they belong to. Each runs once per database and is recorded in a hash ledger, so an edit after it ran is refused rather than silently ignored. Destructive changes on tables that hold data need an explicit @drop directive.

schema files ──build──▶ plan database ──introspect──┐
                                                    ├─▶ diff ─▶ apply ─▶ re-diff (must be empty)
live database ─────────────────────introspect───────┘

Status: 0.1.0, alpha. The engine has run the production schema of Powertime since it went into service on 10 December 2025. The public API may change before 1.0.

Why

There are two common ways to evolve a Postgres schema, and both work:

• Migration files (a numbered sequence of up/down scripts) give you full control and an exact history. The cost is readability: the current schema exists only by replaying the files, so it cannot be read, reviewed or reasoned about from the code alone, and every change is written twice, once as "the table should look like this" in your head and once as the ALTER that gets it there.
• Pure diff tools compare a desired schema with a live one and generate the ALTERs. The schema is readable in one place. The cost is that a diff sees only structure: it cannot know that a dropped column and a new one are a rename, or that a new NOT NULL column needs a backfill first.

pgdeclare takes the diff approach, so the schema files are the readable source of truth, and adds the missing piece: the imperative steps live as inline blocks beside the declarations, run at a defined point (before or after the diff), exactly once, with their hash recorded. History still exists, in git, where history belongs.

Two design choices worth knowing:

• Postgres parses your SQL. The desired schema is not reconstructed from a parser's model of your files. pgdeclare executes them in a scratch "plan" database and introspects the result, so defaults, implicit constraints and type normalisation come out exactly as Postgres would produce them.
• Postgres orders the work. Statements, and the changes themselves, run retry-until-stable: anything that fails on a missing dependency is retried after the rest, until everything succeeds or no progress is made. You do not maintain a dependency order.

Install

pip install pgdeclare
# or
uv add pgdeclare

Requires Python 3.12+ and a PostgreSQL 14 to 18 server.

Quickstart

A schema tree:

sql/
└── schemas/
    ├── 01_types.sql
    └── 02_note.sql

-- sql/schemas/01_types.sql
CREATE TYPE mood AS ENUM ('calm', 'loud');

-- sql/schemas/02_note.sql
CREATE TABLE IF NOT EXISTS note (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author text NOT NULL,
    body   text NOT NULL,
    mood   mood NOT NULL DEFAULT 'calm'
);

CREATE INDEX IF NOT EXISTS note_author_idx ON note (author);

Command line

export DATABASE_URL=postgresql://app:secret@localhost:5432/app

pgdeclare diff                 # what would change, as a summary plus SQL
pgdeclare diff --json          # the same, machine-readable
pgdeclare diff --exit-code     # exit 2 when anything is pending (for CI)
pgdeclare apply --dry-run      # plan only, nothing written
pgdeclare apply                # converge the database

Now add a column and a backfill:

-- sql/schemas/02_note.sql
CREATE TABLE IF NOT EXISTS note (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author text NOT NULL,
    body   text NOT NULL,
    mood   mood NOT NULL DEFAULT 'calm',
    title  text
);

CREATE INDEX IF NOT EXISTS note_author_idx ON note (author);

-- @migrate-after:2026-01-15_note_titles
UPDATE note SET title = left(body, 40) WHERE title IS NULL;
-- @end-migrate

pgdeclare apply adds the column, then runs the block once and records it.

Options shared by both commands:

apply also takes --dry-run, --offline (allow changes that lock or rewrite tables) and --force (bypass @drop checks, re-run edited blocks, continue despite non-convergence).

Exit codes: 0 success or nothing to do, 1 error, 2 changes pending (diff --exit-code only).

The CLI also creates the managed schemas in the target database and installs any CREATE EXTENSION your schema files declare, in both the plan and the target database. The library leaves both to the caller.

Python

from pathlib import Path

from pgdeclare import (
    PgDeclareConfig,
    apply_declared_schema,
    build_desired_schema,
    compute_diff,
    format_changes_summary,
    introspect_schema,
)

url = "postgresql://app:secret@localhost:5432/app"
config = PgDeclareConfig(schemas=("public",), managed_roles=("app_reader",))

# Preview: build the declared schema in a plan database (it must exist) and diff.
desired = build_desired_schema(Path("sql"), url, "app_plan", config=config)
actual = introspect_schema(url, config=config)
changes = compute_diff(desired, actual, schemas=list(config.schemas), config=config)
print(format_changes_summary(changes))

# Converge: before-blocks, diff, apply, after-blocks, DO blocks, convergence check.
apply_declared_schema(url, Path("sql"), "app_plan", config=config)

apply_declared_schema raises SchemaConvergenceError if a second diff after applying still finds work, and a plain Exception with the reason for any other failure. Progress goes to the standard logging logger pgdeclare, and user-facing lines to config.report.

Directives

Directives are SQL comments inside the schema files.

@migrate-before / @migrate-after

-- @migrate-before:2026-02-01_rename_body
ALTER TABLE note RENAME COLUMN body TO content;
-- @end-migrate

• @migrate-before blocks run before the diff. Use them for renames, and for anything the diff must not see as drop-plus-add.
• @migrate-after blocks run after the diff. Use them for backfills on columns the diff just created.
• The id after the colon is the ledger key. It must be unique; a date prefix keeps them sorted and readable.
• A block runs once per database. Its SQL is hashed (SHA-256, first 16 hex digits). Editing a block after it ran is an error until you re-run with --force.
• Blocks are stripped before the schema is built, so the plan database never sees them.
• On a fresh database (no tables in the managed schemas) blocks are recorded as satisfied without running: the schema is built straight to its final state, and the upgrade path does not apply.
• Comment lines inside a block are not part of its SQL.

@drop

Dropping a table or a column that contains data needs explicit consent, written inside a block:

-- @migrate-before:2026-03-01_drop_legacy
-- @drop note.legacy_flag
-- @drop old_table
SELECT 1;
-- @end-migrate

Columns are named table.column or schema.table.column; tables table or schema.table. Empty tables and columns can be dropped without one. --force overrides the check.

DO blocks

Top-level DO $$ ... $$; blocks in the schema files run on every apply, after the diff. They must be idempotent.

Layout

Under the SQL root (--sql-dir, or the sql_dir argument):

File order does not matter for correctness: loading is retry-until-stable.

What it manages

Within the managed schemas:

Not managed: extensions, sequences outside identity columns, domains, comments, ownership, and anything outside the managed schemas. Tables whose names start with _ are ignored. Objects owned by an extension are never introspected.

Grants on a recreated view or function are replayed inside the same statement as the recreate, so no execution order can leave the object without its ACL. When a set of SECURITY DEFINER functions is passed as locked_functions, their default PUBLIC EXECUTE is revoked atomically with their creation.

Configuration

Everything project-specific is a field of the frozen PgDeclareConfig, passed to the entry points. The defaults describe a plain database.

The plan database must be on the same server as the target and must already exist; the library rebuilds plan_recreate_schemas in it on every run. CREATE EXTENSION statements in schema files are skipped when building the plan, so provision extensions in the plan database yourself (or list them in bootstrap_sql_files).

Safety

• Preview first. pgdeclare diff and apply --dry-run write nothing to the target database. A dry run cannot show the effect of pending @migrate-before blocks, and says so.
• Online, offline, blocked. Every change is classified. Online changes apply directly. Offline changes (SET NOT NULL, column type changes, a new generation expression) lock or rewrite tables and need --offline (offline=True). Blocked changes (drops of data) need @drop.
• One migration at a time. The DDL phase takes a session advisory lock on the target database and fails fast if another run holds it.
• Transactions. Enum changes commit first (Postgres requires it); the rest of the DDL runs in one transaction with savepoints, so a stuck run changes nothing. Index builds run CONCURRENTLY afterwards; an invalid index left by a failed build is dropped and retried.
• Names are data. Every identifier written into SQL is quoted with its embedded quotes doubled, every literal escaped, and DO blocks use a dollar tag their body cannot contain, so an object name chosen by a less privileged role cannot inject SQL into the migrator's session.
• Passwords are not shown. A role password declared in the schema files is executed but masked as PASSWORD '***' in diff, diff --json, dry-run output and logs (Change.to_display_sql()). Roles are cluster-wide, so building the plan database on the same server creates declared roles there too.
• Convergence check. After applying, the database is diffed again. Anything left over fails the run, because one run must reach the declared schema.

Supported PostgreSQL versions

The suite runs against PostgreSQL 14, 15, 16, 17 and 18 in CI.

• security_invoker views need PostgreSQL 15 or later.
• Changing the expression of a generated column uses ALTER COLUMN ... SET EXPRESSION, which needs PostgreSQL 17 or later. On 14 to 16, do that change by hand in a @migrate-before block.
• PostgreSQL 18 records NOT NULL as a constraint; pgdeclare models it on the column on every version, so plans are identical.

Contributing

Issues and pull requests are welcome on GitHub. See CONTRIBUTING.md for running the tests, and SECURITY.md for reporting vulnerabilities.

License

Apache License 2.0. See LICENSE and NOTICE.

Extracted from Powertime, where it manages the application's schema.