alembic-postgresql-enum

alembic-postgresql-enum plugs a long-standing gap in Alembic’s autogenerate machinery: PostgreSQL native enum types. Out of the box, Alembic can detect new tables and columns but has no idea when a Python Enum backing a postgresql.ENUM column gains, loses, or reorders values in your SQLAlchemy models — that logic has traditionally been hand-written raw SQL copied between migrations. This library registers itself as an autogenerate comparator the moment it’s imported into env.py, then diffs the enum types declared in your SQLAlchemy metadata against the enum types actually defined in the connected PostgreSQL schema.

When it finds a difference, it emits a sync_enum_values() operation into the generated migration that recreates the PostgreSQL enum type under the hood (Postgres has no native ALTER TYPE ... DROP VALUE, so the library renames the old type, creates a new one with the right values, casts every affected column across schemas, and drops the temporary type) while preserving column defaults and any partial indexes whose WHERE clauses reference the enum. It also handles enum creation on CREATE TABLE/ADD COLUMN and cleans up orphaned enum types on DROP TABLE/DROP COLUMN, cases where Alembic’s own autogenerate silently leaves the enum type behind in the database.

What You Get

• Automatic detection of new, changed, and removed PostgreSQL enum types during alembic revision --autogenerate
• A sync_enum_values() migration operation that rebuilds the enum type (rename → create → cast columns → drop) since Postgres lacks in-place value removal
• Preservation of column server defaults across the enum rebuild, including renamed defaults when an old value is renamed to a new one
• Automatic detection and recreation of partial indexes whose WHERE clauses compare against the enum type, including support for offline migration mode
• Cleanup of orphaned enum types that Alembic’s own autogenerate leaves behind on DROP TABLE and DROP COLUMN
• A Config object (set_configuration) to toggle dropping unused enums, value-change detection, cross-dialect tolerance, and value ordering strictness

Common Use Cases

• Adding a new status/state value to an existing PostgreSQL-backed enum column without writing manual ALTER TYPE SQL
• Renaming an enum value (e.g. fixing a typo) while automatically keeping existing rows, defaults, and dependent partial indexes consistent
• Removing unused enum values or entire orphaned enum types left behind after a model is refactored or deleted
• Adding an enum column to a brand-new table and having both the table and the backing enum type created and dropped together correctly

Under The Hood

Architecture The library hooks into Alembic through its public extension points rather than monkeypatching: importing alembic_postgresql_enum registers compare_enums() with alembic.autogenerate.comparators.dispatch_for("schema") and registers a new sync_enum_values operation on alembic.operations.base.Operations. compare_dispatch.py is the entry point invoked on every autogenerate run — it walks get_enum_data/declared_enums.py (SQLAlchemy metadata) and get_enum_data/defined_enums.py (live Postgres catalog) per schema, then routes differences to detection_of_changes/enum_creation.py, enum_deletion.py, and enum_alteration.py, which append CreateEnumTypeOp/DropEnumTypeOp/SyncEnumValuesOp MigrateOperation subclasses onto Alembic’s UpgradeOps tree. operations/sync_enum_values.py implements the actual runtime SQL sequence (rename old type → create new type → add comparison operators so the cast succeeds → drop dependent indexes → cast every affected column → drop the temporary type → recreate indexes), with a dedicated render_sync_enum_value_op renderer producing the human-readable migration source Alembic writes to disk.

Tech Stack Pure Python, targeting SQLAlchemy 1.4 and 2.0 and Alembic 1.7+ (with a version branch for Alembic 1.18’s new dispatch-priority API) via pyproject.toml’s hatchling build backend. No runtime dependencies beyond SQLAlchemy and Alembic themselves; PostgreSQL is the only supported dialect (checked explicitly, with an opt-out force_dialect_support flag). Dev tooling is uv-managed with a dependency-group test matrix that runs the suite against each SQLAlchemy/Alembic combination, black for formatting, and Docker Compose for a local Postgres test instance.

Code Quality The tests/ tree is organized by concern (sync_enum_values/, test_enum_creation/, test_constraints/, get_enum_data/, test_without_schema_changes/) with roughly two dozen focused test modules covering array columns, exotic identifier names, cross-schema enums, TypeDecorator-wrapped enums, partial-index preservation, and both SQLAlchemy declarative styles. GitHub Actions runs the suite on push (test_on_push.yaml) plus a dedicated black.yml formatting check. The package ships a py.typed marker and uses dataclasses and type hints throughout its internal modules; error handling favors explicit, descriptive exceptions (e.g. re-raising DataError with guidance toward enum_values_to_rename when a cast fails on existing rows) over silent fallbacks.

What Makes It Unique Unlike libraries that only expose a manual helper function for renaming enum values, this one performs the diffing itself by comparing declared SQLAlchemy metadata against the live database schema, closing an autogenerate gap Alembic has never addressed natively. Its handling of partial indexes is notably thorough: it detects WHERE-clause dependencies on the enum type at migration-generation time (so the recreated migration works even in offline mode without a live database connection), transforms index definitions when values are renamed rather than just dropped, and restores them after the type swap — a level of care the project’s own docs/alternatives.md argues prior alternatives on PyPI lacked.