pg-quaestor

In ancient Rome, a quaestor was the official in charge of the treasury — here, it manages your PostgreSQL schema and data, your real treasure.

Lightweight PostgreSQL migration module with named migrations and dependency resolution.

Install

pip install pg-quaestor

Quick Start

import asyncio

import asyncpg
from pg_quaestor import MigrationRegistry

registry = MigrationRegistry()


@registry.register("create_users")
async def create_users(conn):
    await conn.execute("CREATE TABLE users (email text PRIMARY KEY)")


@registry.register("add_status", depends_on=["create_users"])
async def add_status(conn):
    await conn.execute(
        "ALTER TABLE users ADD COLUMN status text NOT NULL DEFAULT 'active'"
    )


async def main():
    conn = await asyncpg.connect("postgresql://localhost/myapp")
    applied = await registry.run(conn, "myapp")
    print(f"Applied migrations: {applied}")
    await conn.close()


asyncio.run(main())

API Reference

MigrationDefinition is not exported from pg_quaestor. A migration function is async def migration(conn: asyncpg.Connection) -> None. Pass a connection. Taking one from a pool is the caller's job.

How It Works

• Applied migrations are tracked in {prefix}_migrations in the schema named by the connection's search_path. The library does not change search_path.
• prefix is a bare identifier of 1 to 52 characters ([A-Za-z_][A-Za-z0-9_]*). The table name is quoted, so MyApp stays MyApp_migrations.
• Each row stores name (text, primary key) and applied_at (timestamptz, UTC, set by the runner).
• Each migration runs in one transaction on that connection. The runner inserts the tracking row in the same transaction and commits. A raised exception rolls the migration's SQL and the tracking row back together. Migrations already committed in that run stay applied. The next run retries the failed one.
• The migration must not COMMIT, ROLLBACK, or close the connection. The runner does not detect a migration that does.
• CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, VACUUM, REINDEX CONCURRENTLY, and CREATE DATABASE are outside this version. There is no flag to run a migration outside a transaction.
• run raises ValueError if the connection is already inside a transaction.
• A session advisory lock on hashtextextended(prefix, 0) is held for the run, so two runners for the same prefix take turns. Different prefixes lock independently.
• Dependencies are resolved with Kahn's algorithm. A missing or circular dependency raises ValueError and applies nothing further. Independent migrations run in alphabetical order.
• Several registries share one database by using different prefixes.
• Requires PostgreSQL 11 or newer.
• Progress and errors are logged to the pg_quaestor logger.