alembic-pg-autogen
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.
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:
- The extension reads the live database catalog.
- The extension canonicalizes your DDL inside a temporary savepoint.
- The extension compares the current state against the declared state.
- 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 toalembic.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"incontext.configure()keeps one validating statement for every change. The default is"deferred".postgresql_not_valid=Trueon aCheckConstraintin your models keeps that constraintNOT 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
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)
| 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)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| 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.