alembic-pg-autogen 0.0.2

Alembic autogenerate extension for PostgreSQL-specific objects

alembic-pg-autogen

Alembic autogenerate extension for PostgreSQL. Extends Alembic's --autogenerate to detect and emit migrations for PostgreSQL functions and triggers that Alembic doesn't handle out of the box.

How it works

You declare your desired functions and triggers as DDL strings. When you run alembic revision --autogenerate, the extension:

1. Inspects the current database catalog (pg_proc, pg_trigger)
2. Canonicalizes your DDL by executing it in a savepoint and reading back the catalog (then rolling back)
3. Diffs current vs. desired state, matching objects by identity
4. Emits CREATE, DROP, or CREATE OR REPLACE operations in dependency-safe order (drop triggers before functions, create functions before triggers)

Installation

pip install alembic-pg-autogen

Requires Python 3.10+ and SQLAlchemy 2.x. You provide your own PostgreSQL driver (psycopg, psycopg2, asyncpg, etc.).

Usage

In your env.py, import the extension and pass your DDL via process_revision_directives options:

import alembic_pg_autogen  # noqa: F401  # registers the comparator plugin

# Define your functions and triggers as DDL strings
PG_FUNCTIONS = [
    """
    CREATE OR REPLACE FUNCTION audit_trigger_func()
    RETURNS trigger LANGUAGE plpgsql AS $$
    BEGIN
        NEW.updated_at = now();
        RETURN NEW;
    END;
    $$
    """,
]

PG_TRIGGERS = [
    """
    CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON my_table
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_func()
    """,
]

Then in your run_migrations_online() function, pass them as context options:

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    opts={
        "pg_functions": PG_FUNCTIONS,
        "pg_triggers": PG_TRIGGERS,
    },
)

Run autogenerate as usual:

alembic revision --autogenerate -m "add audit trigger"

The generated migration will contain op.execute() calls with the appropriate CREATE, DROP, or CREATE OR REPLACE statements.

Development

make install     # Install dependencies (uses uv)
make lint        # Format (mdformat, codespell, ruff) then type-check (basedpyright)
make test        # Run full test suite (requires Docker for integration tests)
make test-unit   # Run unit tests only (no Docker needed)

License

MIT