Adding a value to a Postgres enum in Alembic

Write the migration by hand — autogenerate never detects new enum members — and run ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'refunded' inside op.get_context().autocommit_block(), so the new label is committed before anything tries to use it. This guide belongs to managing enums, constraints and indexes in migrations.

Quick Answer

Adding a member to a Python enum changes nothing in the database. The PostgreSQL type keeps its old labels until a migration alters it, and the first insert of the new value fails.

Before — the enum changed, the migration is empty:

import enum

from sqlalchemy import Enum
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class OrderStatus(enum.Enum):
    PENDING = "pending"
    PAID = "paid"
    SHIPPED = "shipped"
    REFUNDED = "refunded"          # new


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    status: Mapped[OrderStatus] = mapped_column(
        Enum(OrderStatus, name="order_status",
             values_callable=lambda members: [m.value for m in members])
    )

# $ alembic revision --autogenerate -m "add refunded status"
# def upgrade() -> None:
#     pass
#
# Later, at runtime:
# DBAPIError: invalid input value for enum order_status: "refunded"

After — a hand-written migration with an autocommit block:

"""add refunded order status

Revision ID: 3f9a1c2d7b10
Revises: 8e21b4a0c5d3
"""
from alembic import op

revision = "3f9a1c2d7b10"
down_revision = "8e21b4a0c5d3"


def upgrade() -> None:
    with op.get_context().autocommit_block():
        op.execute("ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'refunded' AFTER 'shipped'")


def downgrade() -> None:
    # PostgreSQL cannot drop a single enum label; see "Removing a value" below.
    pass

IF NOT EXISTS makes the migration safe to re-run after a partial failure, and AFTER 'shipped' controls sort order, which matters if anything orders by the column.

Execution Context & Async Workflow Integration

A PostgreSQL enum is a standalone type object, created once with CREATE TYPE and referenced by any number of columns. Alembic's autogenerate compares the tables in your metadata with the tables in the database — columns, types by name, indexes, constraints — and it does not look inside an enum type to compare its labels. From its point of view, status is still of type order_status on both sides, so there is nothing to generate.

That leaves the migration to you, and the transaction around it matters. Alembic runs each migration inside a transaction. Since PostgreSQL 12, ALTER TYPE ... ADD VALUE is permitted inside a transaction block, but the new label cannot be used until that transaction commits; a data migration in the same revision that writes the new value fails with unsafe use of new value "refunded". Before PostgreSQL 12, the ALTER itself is refused with ALTER TYPE ... ADD cannot run inside a transaction block.

op.get_context().autocommit_block() solves both. It commits the migration's transaction so far, runs its body with the connection in autocommit mode, and starts a new transaction for whatever follows. The label is committed the moment the statement finishes.

Under an async env.py this works unchanged, because migrations run inside connection.run_sync() on an ordinary synchronous Connection facade — the setup covered in setting up Alembic env.py for asyncpg. One setting helps: transaction_per_migration=True in context.configure(). Without it, all pending migrations share one transaction, and the autocommit block commits every earlier migration in the run at that point, which is surprising when a later one fails.

Deploy order matters too. The migration must run before application code that writes the new value, and application code that reads the column should tolerate labels it does not recognise if old and new versions run side by side. A Python OrderStatus("refunded") in an old release raises ValueError, and SQLAlchemy raises LookupError: 'refunded' is not among the defined enum values when it loads such a row. Releasing the migration and a tolerant reader first, and the writer second, avoids both.

Resolving Warnings, Errors & Common Mistakes

Two of these deserve more than a row.

type already exists usually appears in downgrade() testing. op.drop_table("orders") drops the table but not the enum type, so the next upgrade() tries to create a type that is still there. Drop the type explicitly in the downgrade:

import sqlalchemy as sa
from alembic import op


def downgrade() -> None:
    op.drop_table("orders")
    sa.Enum(name="order_status").drop(op.get_bind(), checkfirst=True)

And names versus values is a decision with no cheap reversal. SQLAlchemy's Enum persists the member name unless told otherwise, so OrderStatus.PENDING = "pending" lands in the database as PENDING. Teams usually notice when a report or another service reads the column. Changing it later means renaming every label with ALTER TYPE ... RENAME VALUE and deploying the mapping change at the same moment, so choose values_callable up front if values are what you mean.

Advanced: Removing a Value, and the CHECK Constraint Alternative

PostgreSQL has no ALTER TYPE ... DROP VALUE. Renaming a label is one statement — ALTER TYPE order_status RENAME VALUE 'paid' TO 'captured' — but removing one means building a new type and moving the column onto it.

from alembic import op


def upgrade() -> None:
    # 1. Rows must not hold the value being removed.
    op.execute("UPDATE orders SET status = 'pending' WHERE status = 'on_hold'")

    # 2. Swap the type underneath the column.
    op.execute("ALTER TYPE order_status RENAME TO order_status_old")
    op.execute("CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'refunded')")
    op.execute(
        "ALTER TABLE orders ALTER COLUMN status TYPE order_status "
        "USING status::text::order_status"
    )
    op.execute("DROP TYPE order_status_old")

The ALTER COLUMN ... TYPE rewrites the table under an ACCESS EXCLUSIVE lock. On a large, busy table that is an outage, and the only way around it is the expand-and-contract pattern: add a new column of the new type, dual-write, backfill in batches, switch reads, and drop the old column — the approach described in renaming a column without downtime.

Because of that cost, many teams choose not to use native PostgreSQL enums for values that change. Enum(OrderStatus, native_enum=False, create_constraint=True, length=20) stores a VARCHAR with a CHECK constraint. Adding a value becomes dropping and re-adding a constraint, which can be done without a long lock:

from alembic import op

ALLOWED = "('pending', 'paid', 'shipped', 'refunded')"


def upgrade() -> None:
    op.drop_constraint("ck_orders_order_status", "orders", type_="check")
    # NOT VALID skips the full-table scan; VALIDATE takes only a SHARE UPDATE EXCLUSIVE lock.
    op.execute(
        f"ALTER TABLE orders ADD CONSTRAINT ck_orders_order_status "
        f"CHECK (status IN {ALLOWED}) NOT VALID"
    )
    op.execute("ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_order_status")

A native enum is four bytes per row and self-documenting in \dT; a VARCHAR with a check is larger and needs the constraint to have a predictable name. That name comes from a metadata naming convention, without which the drop in the example above has nothing reliable to refer to.

Catching Enum Drift in CI

Because autogenerate is blind to enum labels, the reliable safeguard is a test that compares what Python believes with what the migrated database contains. It takes a few lines, runs in milliseconds, and turns "we forgot the migration" from a production error into a failed build.

The test upgrades a disposable database to head, then asks PostgreSQL for each enum type's labels with enum_range() and compares them to the Python enums registered in the metadata:

import enum

import pytest
from sqlalchemy import Enum, text
from sqlalchemy.ext.asyncio import AsyncEngine

from shop.models import Base


def _python_enums() -> dict[str, list[str]]:
    found: dict[str, list[str]] = {}
    for table in Base.metadata.tables.values():
        for column in table.columns:
            col_type = column.type
            if isinstance(col_type, Enum) and col_type.native_enum and col_type.name:
                found[col_type.name] = list(col_type.enums)
    return found


@pytest.mark.asyncio
async def test_enum_labels_match_database(migrated_engine: AsyncEngine) -> None:
    expected = _python_enums()
    async with migrated_engine.connect() as conn:
        for type_name, labels in expected.items():
            rows = await conn.execute(
                text("SELECT unnest(enum_range(NULL::" + type_name + "))::text")
            )
            actual = [row[0] for row in rows]
            missing = [label for label in labels if label not in actual]
            assert not missing, (
                f"{type_name} is missing {missing}; write an ALTER TYPE ... ADD VALUE migration"
            )

col_type.enums holds exactly the strings SQLAlchemy will send, so it already accounts for values_callable — if the model stores values, the test compares values, and if it stores names, names. Interpolating the type name is safe here because it comes from your own metadata, not from input.

The migrated_engine fixture is the same one used for any migration test: a fresh database, alembic upgrade head run against it, and the engine handed to the test. The guides on running Alembic migrations in CI/CD pipelines and on running tests against a Postgres testcontainer show how to build it.

The test deliberately checks only for missing labels. Extra labels in the database are normal — a value that was retired from Python but never removed from the type, because removal is expensive — and failing on them would push teams toward risky type swaps just to make a test pass. If you want to track them, report extras as a warning instead.

Frequently Asked Questions

Related

• Managing Enums, Constraints and Indexes in Migrations — The parent guide: schema objects autogenerate handles poorly.
• Naming constraints with a metadata naming convention — Predictable names for CHECK constraints you will need to drop.
• Creating indexes concurrently in Alembic migrations — The other migration that must run outside a transaction.
• Writing data migrations safely in Alembic — Updating rows to a new value after the type change.