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"

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