alembic-pg-autogen

Status: Beta — the core pipeline is stable and tested against real PostgreSQL. The API may still evolve before 1.0, but the library is suitable for production use.

Alembic autogenerate extension for PostgreSQL functions and triggers. Declare your DDL strings and let alembic revision --autogenerate figure out the CREATE, DROP, and CREATE OR REPLACE for you.

Background

alembic_utils pioneered autogenerate support for PostgreSQL objects and has been hugely helpful to the community. This project takes a different approach aimed at faster performance on large schemas with many functions and triggers.

How it works

You declare your desired functions and triggers as plain DDL strings. At autogenerate time, the extension inspects the live database catalog, canonicalizes your DDL via a temporary savepoint, diffs current vs. desired state, and emits migration ops in dependency-safe order.

Quick example

import alembic_pg_autogen  # noqa: F401  # registers the comparator plugin

PG_FUNCTIONS = [
    """
    CREATE OR REPLACE FUNCTION set_updated_at()
    RETURNS trigger LANGUAGE plpgsql AS $$
    BEGIN
        NEW.updated_at = now();
        RETURN NEW;
    END;
    $$
    """,
]

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

# in run_migrations_online():
context.configure(
    connection=connection,
    target_metadata=target_metadata,
    pg_functions=PG_FUNCTIONS,
    pg_triggers=PG_TRIGGERS,
)

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

What gets managed

An object type becomes managed when you declare it. Within a managed type the declared set is the whole truth, so objects found in the inspected schemas that you did not declare are dropped — that is what makes the tool declarative.

A key you never pass leaves that object type alone entirely: nothing is inspected, diffed, or emitted for it. The example above declares functions and triggers, so views are untouched, and adopting the library one object type at a time is safe.

To record the opt-out at the configuration site — or to set it conditionally — pass the IGNORED sentinel, which means exactly what omitting the key means:

from alembic_pg_autogen import IGNORED

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    pg_functions=PG_FUNCTIONS,
    pg_triggers=PG_TRIGGERS,
    pg_views=IGNORED,  # not ready to manage views yet — don't drop them
)

An empty list is a different thing again: pg_views=[] declares "there should be no views" and drops every existing one, while pg_views=IGNORED declares "views are not managed here". Watch for this if you build the list dynamically — collect_view_ddl() or IGNORED keeps an empty result from clearing your schema.

Because an unrecognized key leaves its type unmanaged, a misspelled one (pg_view for pg_views) would quietly manage nothing; those are reported as a warning naming the key you meant.

Check constraints

Alembic detects when a named CHECK constraint is added to or removed from your models, but two constraints that share a name are always presumed equivalent — normalizing SQL expressions across backends is not something Alembic can do. So tightening amount >= 0 to amount > 0 in a model generates nothing, and the schema drifts.

This package closes that gap for PostgreSQL. Keep declaring constraints in SQLAlchemy metadata as usual:

class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    amount: Mapped[Decimal]

    __table_args__ = (CheckConstraint("amount > 0", name="ck_orders_amount"),)

and a changed expression now produces a migration:

def upgrade() -> None:
    op.drop_constraint("ck_orders_amount", "orders", type_="check")
    op.create_check_constraint("ck_orders_amount", "orders", "amount > 0")

There is nothing to configure. Each expression is round-tripped through PostgreSQL — added to the table as a throwaway NOT VALID constraint inside a savepoint that is rolled back — so amount >= 0 and the catalog's amount >= 0::numeric are recognized as the same constraint, and a real change is recognized as a real change.

Installation

pip install alembic-pg-autogen

Requires Python 3.10+ and SQLAlchemy 2.x. Bring your own PostgreSQL driver (psycopg, psycopg2, asyncpg, etc.). This package depends on postgast for DDL parsing, which requires protobuf >= 5.27.

Documentation

Full documentation is available at alembic-pg-autogen.readthedocs.io, including a quick-start guide, migration instructions for alembic_utils users, and API reference.

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