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