Alembic Utils Extended

Autogenerate Support for PostgreSQL Functions, Views, Materialized Views, Triggers, Policies, and Check Constraints

This is a fork of the much more popular alembic_utils package to extend the capabilities of Alembic, which adds support for autogenerating a larger number of PostgreSQL entity types, including functions, views, materialized views, triggers, and policies.

This repo adds additional support for defining indices for materialized views and autogenerating check constraints.

Quickstart

Visit the quickstart guide for usage instructions.

Entity Registration

# migrations/env.py

from alembic_utils_extended.pg_view import PGView
from alembic_utils_extended.replaceable_entity import register_entities

view = PGView(schema="public", signature="view", definition="SELECT 1")
register_entities([view])

Monitor Check Constraints

Check constraints defined in SQLAlchemy models can also be autogenerated. Note that check constraints must be named. Add to your env.py:

# migrations/env.py
from alembic import context

context.configure(
    # ... other configurations ...
    compare_check_constraints=True,
)

Monitor Native Enum Types

Alembic does not diff enum labels. Adding a member to a Python Enum mapped with sa.Enum(..., native_enum=True) produces no migration at all, passes every other check, and then fails on the first write with invalid input value for enum.

With compare_enum_values=True, alembic_utils_extended diffs every native enum type reachable from target_metadata against pg_enum:

# migrations/env.py
from alembic import context

context.configure(
    # ... other configurations ...
    compare_enum_values=True,
)

It emits two things, and nothing else:

• ALTER TYPE ... ADD VALUE IF NOT EXISTS for labels the models declare and the database lacks. New labels are positioned with BEFORE/AFTER so the PostgreSQL sort order — which is what ORDER BY on an enum column uses — keeps matching the declaration order. Anchors are chosen only from labels that actually exist, so this still works on a type whose overall order has already drifted.
• CREATE TYPE for a type that does not exist yet and is used by a table that does. This covers a real gap: op.add_column never emits CREATE TYPE (only op.create_table fires the event that does), so adding a native-enum column to an existing table otherwise fails with type ... does not exist.

Deliberately out of scope, because each needs an ACCESS EXCLUSIVE rewrite of every dependent table: rebuilding a type, removing a label (PostgreSQL has no DROP VALUE), reordering existing labels, and dropping a type that is no longer declared. A label present in the database but absent from the models raises EnumLabelRemovedError rather than generating anything, since it usually means a member was deleted out from under live rows.

Because labels cannot be dropped, though, a long-lived schema accumulates dead ones that no migration can clear. Vouch for those a type at a time:

context.configure(
    # ... other configurations ...
    compare_enum_values=True,
    ignore_enum_label_removal={"some_legacy_type"},
)

Listed types still get new labels added; they just stop raising on undeclared ones. Note this is per type, not per label — a newly removed label on a listed type is tolerated too.

native_enum defaults to True, so a column that never mentions it is still covered.

Monitor Indexes

Alembic's built-in autogenerate on SQLAlchemy 1.4 mishandles several PostgreSQL index shapes — function expressions (func.lower(col)), directional modifiers (desc(col), literal_column("col DESC")), postgresql_ops opclass hacks for direction, and mixed shapes routinely produce wrong / duplicated diffs.

With compare_indexes=True, alembic_utils_extended takes over autogen for all user-declared indexes, reading the DB side directly from pg_index and applying an identity-based diff. Consumers must also register an include_object filter in env.py returning False for type_ == "index" so stock Alembic's index dispatcher doesn't fire and duel with the fork. All indexes must be named.

# migrations/env.py
from alembic import context

def include_object(obj, name, type_, reflected, compare_to):
    # alembic-utils-extended's `compare_indexes` comparator owns all index autogeneration.
    # Skip stock Alembic's index dispatcher entirely to avoid dueling autogeneration.
    if type_ == "index":
        return False
    return True

context.configure(
    # ... other configurations ...
    include_object=include_object,
    compare_indexes=True,
)

Indexes backing PRIMARY KEY and UNIQUE constraints are excluded automatically (managed by stock Alembic's constraint diff).

NULLS NOT DISTINCT on unique indexes (PostgreSQL 15+). Declare it with the postgresql_nulls_not_distinct=True dialect option on a unique=True index — the same spelling SQLAlchemy 2.0 uses natively, so model code is forward-compatible:

Index("uq_widget_slug", table.c.slug, unique=True, postgresql_nulls_not_distinct=True)

SQLAlchemy 1.4 has no support for this at all (it rejects the kwarg and never emits the clause). On 1.4, alembic_utils_extended replicates SQLAlchemy 2.0's behavior: importing the package registers the dialect argument and installs a compiler hook that splices NULLS NOT DISTINCT into CREATE INDEX. On SQLAlchemy 2.x it defers entirely to native support. Two caveats on 1.4: import alembic_utils_extended before any model module that declares the kwarg, so the dialect argument is registered first; and this covers unique indexes only — UNIQUE constraints are managed by stock Alembic's constraint diff and are out of scope. NULLS NOT DISTINCT only affects uniqueness, so it is a no-op on a non-unique index; the comparator treats the flag without unique=True as a mistake and raises at autogenerate time.

Content changes under a stable name are not detected. Comparison is identity-only ((table_name, index_name) set diff). If the same index name exists in both the model and the database, the comparator treats it as unchanged. To evolve an index's columns, WHERE clause, opclass, INCLUDE list, or method, rename it (which produces a drop + create pair the fork will emit) or write a manual migration. This is a real trade-off vs. stock Alembic, which detects column- list changes for plain-column indexes — but stock Alembic's index handling has enough other bugs on SA 1.4 that identity-only-plus-rename is easier to reason about than any partial coverage.

Coverage is best-effort, not guaranteed. Indexes can drift out of prod (manual CREATE INDEX, out-of-band drops) in ways autogen against a local DB can never catch. This library closes the most common autogen bugs but does not guarantee every declared index actually exists in your database. Audit periodically with a direct pg_index query — see the auditing recipe below.

Common pitfalls the comparator catches at autogenerate time:

• func.X("col_name") anti-pattern — bare strings inside func.X(...) are treated as bound-parameter literal values, not column references. The resulting index is on the constant string, not the column. Use func.X(table.c.col_name) or func.X(literal_column("col_name")) instead. The comparator raises with a remediation hint when it detects this.

Auditing indexes against production

Run against a prod replica to catch drift the fork can't detect on its own (indexes declared in code but missing from prod, or vice versa):

-- Lists every user-declared index in prod (excludes PK/UNIQUE constraint indexes).
-- Cross-reference against your model's declared index set.
SELECT
    n.nspname AS schema_name,
    t.relname AS table_name,
    c.relname AS index_name,
    pg_get_indexdef(i.indexrelid) AS index_definition
FROM pg_index i
JOIN pg_class     c ON c.oid = i.indexrelid
JOIN pg_class     t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint con ON con.conindid = i.indexrelid
WHERE n.nspname = 'public'
  AND con.oid IS NULL
  AND NOT i.indisprimary
ORDER BY t.relname, c.relname;

Autogeneration

The next time you autogenerate a revision, Alembic will detect if your entities are new, updated, or removed and populate the migration script.

alembic revision --autogenerate -m 'message'

Contributing

If you have any issues with contributing, please reach out to justin@joincandidhealth.com so that we can work out any issues you are having! This is mostly just forked directly from alembic_utils, so it's possible something is misconfigured.

Testing

poetry install
poetry run pre-commit run --all-files
poetry run pytest