Skip to main content

alembic-pg-autogen

PyPI Python License: MIT CI Coverage Docs Downloads

Status: Beta. The core pipeline is stable. The test suite runs against a real PostgreSQL server. The API may still change before version 1.0. You can use the library in production.

Alembic autogenerate extension for PostgreSQL functions and triggers. You declare your DDL strings, and alembic revision --autogenerate writes the CREATE, DROP, and CREATE OR REPLACE statements.

alembic-pg-autogen logo

Background

alembic_utils was the first project to add autogenerate support for PostgreSQL objects. Many projects use it today. This project takes a different approach. The goal is faster autogenerate on large schemas that contain many functions and triggers.

How it works

You declare each function and trigger as a plain DDL string. Autogenerate then runs four steps:

  1. The extension reads the live database catalog.
  2. The extension canonicalizes your DDL inside a temporary savepoint.
  3. The extension compares the current state against the declared state.
  4. The extension emits migration operations in dependency 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,
    autogenerate_plugins=["alembic.autogenerate.*", "alembic_pg_autogen.*"],
    pg_functions=PG_FUNCTIONS,
    pg_triggers=PG_TRIGGERS,
)
alembic revision --autogenerate -m "add audit function and trigger"

Both wildcards are required

autogenerate_plugins replaces Alembic's default value of ["alembic.autogenerate.*"]. The option does not extend the default value. Your list must keep the built-in wildcard next to ours. This package only adds comparators. This package never replaces Alembic's comparators, which still handle every table, column, index, and constraint.

A wrong list fails silently in two directions. Neither direction raises an error:

  • Omitting alembic_pg_autogen.* keeps Alembic's default value in force. The same result follows when you never set the option. An import of the package registers the plugins but does not enable them. The extension then never compares functions, triggers, views, or check constraint expressions.
  • Omitting alembic.autogenerate.* makes every migration empty. This result covers Alembic's own features and this package's features. The comparator that drives the whole diff belongs to alembic.autogenerate.schemas. Alembic dispatches no comparator without it.

To confirm what a run loaded, set logging.getLogger("alembic.runtime.plugins").setLevel(logging.INFO). Each included plugin logs one setting up autogenerate plugin ... line. A correct configuration logs lines from both namespaces. It logs several alembic.autogenerate.* entries, above all schemas and tables, which drive the diff. It also logs alembic_pg_autogen.compare and alembic_pg_autogen.checkconstraints. Lines from one namespace only mean that your list is missing the other wildcard.

What gets managed

An object type becomes managed when you declare it. Inside a managed type, your declared set is the whole truth. The extension drops each object that it finds in the inspected schemas and that you did not declare. This rule is what makes the tool declarative.

A key that you never pass leaves that object type unmanaged. The extension inspects nothing, compares nothing, and emits nothing for that type. The example above declares functions and triggers, so views stay unchanged. You can adopt the library one object type at a time.

Pass the IGNORED sentinel to record the unmanaged object type in the configuration, or to set it conditionally. The sentinel means exactly what an absent key means:

from alembic_pg_autogen import IGNORED

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    autogenerate_plugins=["alembic.autogenerate.*", "alembic_pg_autogen.*"],
    pg_functions=PG_FUNCTIONS,
    pg_triggers=PG_TRIGGERS,
    pg_views=IGNORED,  # views are not managed yet, so keep them
)

An empty list means something else. pg_views=[] declares that the schema contains no views, and the extension drops every existing view. pg_views=IGNORED declares that this configuration does not manage views. Note the difference when you build the list at runtime. The expression collect_view_ddl() or IGNORED keeps an empty result from clearing your schema.

An unrecognized key also leaves its object type unmanaged. A misspelled key such as pg_view therefore manages nothing. The extension reports each misspelled key as a warning that names the key you meant.

Check constraints

Alembic detects a named CHECK constraint that you add to your models or remove from them. Alembic treats two constraints with the same name as equal, because it cannot normalize SQL expressions across backends. A change from amount >= 0 to amount > 0 therefore generates no migration, and the schema drifts.

This package closes that gap for PostgreSQL, and the gap is all that it closes. Our comparator augments Alembic's comparator. Keep alembic.autogenerate.checkconstraint_byname enabled, so that it continues its own work. Alembic 1.19.2 renamed that plugin to alembic.ext.checkconstraint_byname and no longer enables it through the alembic.autogenerate.* wildcard, so list it explicitly:

Situation Who handles it Result
Name in your models only alembic.autogenerate.checkconstraint_byname create_check_constraint
Name in the database only alembic.autogenerate.checkconstraint_byname drop_constraint
Name on both sides, expression possibly changed alembic_pg_autogen.checkconstraints drop_constraint and create_check_constraint
Name on both sides, set of allowed values changed alembic_pg_autogen.checkconstraints drop_constraint and create_check_constraint(..., postgresql_not_valid=True), then VALIDATE CONSTRAINT on the next run
Name on both sides, database holds NOT VALID alembic_pg_autogen.checkconstraints ALTER TABLE ... VALIDATE CONSTRAINT

The two sets of names are disjoint by construction. No operation is ever emitted twice, so you gain nothing when you disable Alembic's comparator. You lose the detection of added constraints and removed constraints, and our comparator reports nothing in their place. On PostgreSQL, Alembic's own expression check always reports equality: DefaultImpl.compare_check_constraint returns Equal(), and the PostgreSQL dialect does not override that method. That result is the gap our comparator fills, and the reason the two comparators never collide.

Remember one consequence. Your schema may drift only through constraints that you forgot to declare and constraints that you no longer want. This package then reports nothing, and Alembic emits every operation in the migration. That outcome is the intended division of work.

Declare 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"),)

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")

The comparator sends each expression through PostgreSQL. It adds the expression to the table as a throwaway NOT VALID constraint. It works inside a savepoint, and it reverts that savepoint. PostgreSQL therefore reports amount >= 0 and the catalog form amount >= 0::numeric as one constraint. A real change still appears as a change.

Non-native enums

A native PostgreSQL enum resists expand and contract deployment. ALTER TYPE ... DROP VALUE does not exist, and a value added inside a transaction is unusable until that transaction commits. SQLAlchemy offers a replacement. Enum(Status, native_enum=False, create_constraint=True) stores the column as varchar and derives a named CHECK constraint from the Python enum:

class Status(enum.Enum):
    new = "new"
    done = "done"


class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    status: Mapped[Status] = mapped_column(
        Enum(Status, native_enum=False, create_constraint=True, name="ck_orders_status", length=16)
    )

This package compares that constraint like any other named constraint. Alembic's checkconstraint_byname plugin skips every constraint that a type generates, and it therefore reports the database copy as removed. This package runs after that plugin, discards that drop, and adds the constraint when the database lacks it. A changed set of members therefore produces a migration here and nowhere else.

An added member is one migration. No existing row can violate a wider set, so the new constraint validates at once:

def upgrade() -> None:
    op.drop_constraint("ck_orders_status", "orders", type_="check")
    op.create_check_constraint("ck_orders_status", "orders", "status IN ('new', 'done', 'shipped')")

A removed member spans two revisions, because rows that hold the removed value need a backfill first. The first revision replaces the constraint as NOT VALID. PostgreSQL enforces the new constraint for every new row at once, and it holds ACCESS EXCLUSIVE for one millisecond instead of a full table scan. The migration says which values went away:

def upgrade() -> None:
    op.drop_constraint("ck_orders_status", "orders", type_="check")
    # ck_orders_status no longer allows status IN ('shipped'). Rows that hold a removed value fail validation.
    # Backfill those rows before the revision that validates ck_orders_status.
    op.create_check_constraint("ck_orders_status", "orders", "status IN ('new', 'done')", postgresql_not_valid=True)

The next alembic revision --autogenerate reads convalidated from the catalog and emits the validation. The scan runs under SHARE UPDATE EXCLUSIVE, which blocks no reads and no writes:

def upgrade() -> None:
    op.execute("ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_status")

The validation revision fails with a check violation until every row holds an allowed value. Write the backfill yourself. The package states the need and nothing more.

The downgrade of a validation revision does nothing, and it says why. PostgreSQL offers no statement that marks a validated constraint as NOT VALID again. Every other check constraint change keeps the single validating statement shown above, because only col IN (...) and col = ANY (ARRAY[...]) classify as value sets.

Two settings change this behavior:

  • pg_check_constraint_validation="immediate" in context.configure() keeps one validating statement for every change. The default is "deferred".
  • postgresql_not_valid=True on a CheckConstraint in your models keeps that constraint NOT VALID. The comparator then emits no validation for it.

Skipping drop_index for dropped tables

Alembic emits op.drop_index() for each index of a table that the same migration drops. The Alembic cookbook hook that removes those calls ships here as an opt-in Rewriter:

from alembic_pg_autogen import skip_drop_index_for_dropped_tables

context.configure(
    ...,
    process_revision_directives=skip_drop_index_for_dropped_tables,
)

Use skip_drop_index_for_dropped_tables.chain(my_hook) if you already pass a hook.

Installation

pip install alembic-pg-autogen

Requires Python 3.10+ and SQLAlchemy 2.x. Install a PostgreSQL driver yourself (psycopg, psycopg2, or asyncpg). This package depends on postgast for DDL parsing. postgast requires protobuf >= 5.27.

Documentation

The full documentation is at alembic-pg-autogen.readthedocs.io. It contains a quick start guide, migration instructions for alembic_utils users, and the 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

Metadata

Release files for alembic-pg-autogen 0.4.0

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for alembic-pg-autogen 0.4.0
File Size Uploaded Provenance Actions
alembic_pg_autogen-0.4.0.tar.gz 1.2 MB Without provenance Details for alembic_pg_autogen-0.4.0.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for alembic-pg-autogen 0.4.0
File Interpreter ABI Platform Actions
alembic_pg_autogen-0.4.0-py3-none-any.whl Python 3 none any Details for alembic_pg_autogen-0.4.0-py3-none-any.whl

Total release size: 1.2 MB

Release files / alembic_pg_autogen-0.4.0.tar.gz

Download URL alembic_pg_autogen-0.4.0.tar.gz
Size 1.2 MB
Tags Source
SHA-256 checksum
How to use checksums
f6a654e57a7ee4ed7591fce3f3bb6c95fc67bcae0c3c3f61ba12ecb6e05d4468
BLAKE2b-256 checksum
How to use checksums
6288f034e3f46e1f10a53723d81c502b73b039a32a9eee27173e708922e155cc
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / alembic_pg_autogen-0.4.0-py3-none-any.whl

Download URL alembic_pg_autogen-0.4.0-py3-none-any.whl
Size 36.1 kB
Tags Python 3
SHA-256 checksum
How to use checksums
f1cf79cf4c7b3918948295b88ee6a1f750c12bdbf13db196b50bdd2c213d6f13
BLAKE2b-256 checksum
How to use checksums
47cffaa7c71c9856f51d7e9e5a3331b27cfa9cedd21779caf23d6d16f42554c0
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via uv/0.10.2 {"installer":{"name":"uv","version":"0.10.2","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Security for alembic-pg-autogen 0.4.0

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

No provenance found

None of the files in this release were uploaded with provenance metadata.

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page