alembic_utils

Alembic Utils is an extension to Alembic, the migration tool for SQLAlchemy, that closes a long-standing gap: Alembic’s autogeneration only detects changes to table-shaped SQLAlchemy models, leaving views, functions, materialized views, triggers, and row-level security policies to be migrated by hand. This library registers those entity types with Alembic’s comparator hooks so alembic revision --autogenerate can detect when a PGFunction, PGView, PGMaterializedView, PGTrigger, PGPolicy, PGExtension, or PGGrantTable definition is new, changed, or removed, and emit the correct upgrade/downgrade operations automatically.

Under the hood it works by simulating each registered entity against a nested database transaction to capture its rendered, server-side definition, diffing that against what currently exists, and then resolving a safe creation order for entities that depend on one another (a view referencing another view, for example). The result is that PostgreSQL-specific database objects can live alongside ORM models in version control and be migrated with the same alembic upgrade/downgrade workflow developers already use.

What You Get

• Autogenerate support for PGFunction, PGView, PGMaterializedView, PGTrigger, PGPolicy, PGExtension, and PGGrantTable entities
• A register_entities() hook that plugs directly into an existing env.py with no changes to how alembic revision --autogenerate is invoked
• Automatic dependency resolution so entities that reference one another (e.g. a view built on another view) are created and dropped in a safe order
• Simulation-based diffing that creates each entity in a rolled-back transaction to capture its true rendered definition before comparing it to the live database
• from_sql()/from_path() constructors for building entities directly from raw SQL strings or .sql files
• Deprecated-parameter-aware filtering that still honors Alembic’s standard include_name/include_object hooks for schema and object inclusion

Common Use Cases

• Versioning PostgreSQL views and materialized views alongside SQLAlchemy table models in the same Alembic migration history
• Tracking row-level security (RLS) policies as code so access-control changes go through code review and migrations instead of manual SQL
• Autogenerating migrations for trigger functions that enforce business rules or maintain derived columns at the database level
• Managing PostgreSQL extensions (e.g. pgcrypto, pg_trgm) declaratively as part of the same migration pipeline as the rest of the schema
• Migrating existing hand-written SQL functions and views into a maintainable, autogenerated Alembic workflow by parsing them with from_sql()

Under The Hood

Architecture The library is organized around a single abstract base, ReplaceableEntity (src/alembic_utils/replaceable_entity.py), which every concrete entity type (PGFunction, PGView, PGMaterializedView, PGTrigger, PGPolicy, PGExtension, PGGrantTable) subclasses to supply entity-specific SQL rendering (to_sql_statement_create, to_sql_statement_drop, from_database, from_sql). A module-level ReplaceableEntityRegistry collects everything passed to register_entities() and is consulted by compare_registered_entities, a function registered against Alembic’s @comparators.dispatch_for("schema") hook, which is the sole integration point with Alembic’s autogenerate machinery. Ordering of interdependent entities is solved by depends.py’s solve_resolution_order, which repeatedly attempts to simulate each entity in a nested, rolled-back transaction (simulate.py) until all resolve or no more progress can be made. This simulate-then-diff pattern is the core mechanism: entities are speculatively created and dropped inside begin_nested() savepoints purely to observe PostgreSQL’s own rendered definition, which is then compared against what is live to decide between a no-op, a CreateOp, or a ReplaceOp.