Detecting column type and server default changes in autogenerate

Pass compare_type=True and a custom compare_server_default callable to context.configure() in env.py, review every generated alter_column for a postgresql_using cast and a table rewrite, and run alembic check in CI so a model change without a migration fails the build. This guide belongs to autogenerating and reviewing migration scripts.

Quick Answer

A model change that autogenerate does not see produces an empty migration, and the database quietly keeps the old type or default. Two settings decide what it sees.

Before — defaults that miss changes on older Alembic, and no drift check:

# alembic/env.py (excerpt)
def do_run_migrations(connection) -> None:
    context.configure(connection=connection, target_metadata=Base.metadata)
    with context.begin_transaction():
        context.run_migrations()

# models.py: total_cents changed from Integer to BigInteger,
#            status gained server_default="pending"
# $ alembic revision --autogenerate -m "widen totals"
# -> upgrade() contains only `pass` on Alembic < 1.12, and never the default change

After — explicit comparison settings, with a tolerant default comparator:

# alembic/env.py (excerpt)
import re

from alembic import context

from shop.models import Base

_CAST = re.compile(r"::[a-z ]+(\[\])?$")


def _normalise(sql: str | None) -> str | None:
    if sql is None:
        return None
    text = sql.strip().strip("()")
    text = _CAST.sub("", text)                 # 'pending'::character varying -> 'pending'
    return text.lower().replace("current_timestamp", "now()")


def compare_server_default(context, inspected_column, metadata_column,
                           inspected_default, metadata_default, rendered_metadata_default):
    return _normalise(inspected_default) != _normalise(rendered_metadata_default)


def do_run_migrations(connection) -> None:
    context.configure(
        connection=connection,
        target_metadata=Base.metadata,
        compare_type=True,
        compare_server_default=compare_server_default,
    )
    with context.begin_transaction():
        context.run_migrations()

Now the revision contains an alter_column for the type and another for the default, and the comparator returns False for defaults that differ only in how PostgreSQL spells them.

Execution Context & Async Workflow Integration

Autogenerate builds a picture of the live schema with SQLAlchemy's inspector and compares it, object by object, with target_metadata. Tables, columns and nullability are always compared. Two things are governed by switches, because comparing them reliably is harder than it looks.

Types. The inspector reflects each column's type from the catalog — INTEGER, VARCHAR(50), TIMESTAMP WITH TIME ZONE — and Alembic compares it with the model's type. Since Alembic 1.12, compare_type defaults to True, and the built-in comparison handles standard types and their parameters well: Integer to BigInteger, String(50) to String(120), Numeric(10, 2) to Numeric(12, 2). Projects created before 1.12, or pinned below it, had it off by default, and many env.py files still carry an explicit compare_type=False from that era. Custom TypeDecorators compare by their impl unless you tell Alembic otherwise.

Server defaults. compare_server_default is still off by default. The database reports a default as the text of an expression, normalised by PostgreSQL: server_default="pending" in the model becomes 'pending'::character varying in the catalog, func.now() becomes now(), and text("0") becomes 0. Alembic's PostgreSQL implementation tries to compare by evaluating both expressions in a SELECT, which handles simple literals, and it cannot do better than guess for expressions whose values change between evaluations or that reference sequences. The result, with the switch on and no help, is a stream of spurious alter_column(server_default=...) operations in every revision — which is why many teams turned it off and then missed real changes.

A callable fixes both halves. Alembic calls it with the reflected default, the model default and the rendered SQL text; returning True means changed, False means unchanged, and None falls back to the built-in comparison. The normaliser in the quick answer strips casts and parentheses and unifies clock spellings, which covers most real schemas.

None of this depends on the driver. Under an async env.py, the comparison runs inside connection.run_sync() on a synchronous facade, and the inspector queries the catalog through asyncpg the same way it would through psycopg — the setup is covered in setting up Alembic env.py for asyncpg.

Resolving Warnings, Errors & Common Mistakes

Type changes deserve a second look in review even when autogenerate gets them right, because the generated operation is correct SQL with very different costs. On PostgreSQL, increasing a VARCHAR length or removing the length limit is a catalog change and effectively instant. Changing INTEGER to BIGINT, VARCHAR to INTEGER, or TIMESTAMP to TIMESTAMPTZ rewrites every row while holding an ACCESS EXCLUSIVE lock, which blocks reads as well as writes. The generated migration looks identical in both cases:

import sqlalchemy as sa
from alembic import op


def upgrade() -> None:
    # Instant: VARCHAR(50) -> VARCHAR(120)
    op.alter_column("customers", "email",
                    existing_type=sa.String(50), type_=sa.String(120), existing_nullable=False)

    # Full table rewrite: INTEGER -> BIGINT
    op.alter_column("orders", "total_cents",
                    existing_type=sa.Integer(), type_=sa.BigInteger(), existing_nullable=False)

    # Refused without an explicit cast: VARCHAR -> INTEGER
    op.alter_column("orders", "external_ref",
                    existing_type=sa.String(20), type_=sa.Integer(),
                    postgresql_using="external_ref::integer")

For large tables the rewrite should become an expand-and-contract sequence — add the new column, backfill in batches, switch reads, drop the old column — as in renaming a column without downtime.

Advanced: Custom Type Comparators and alembic check in CI

Custom types are where the built-in type comparison gives up. A TypeDecorator over String reflects from the database as VARCHAR, and whether that counts as "the same" as EncryptedString(255) depends on what the decorator means. Two hooks decide it.

On the type itself, compare_against_backend lets the type answer for itself wherever it is used:

import sqlalchemy as sa
from sqlalchemy.types import TypeDecorator


class EncryptedString(TypeDecorator):
    impl = sa.String
    cache_ok = True

    def compare_against_backend(self, dialect, conn_type):
        # Stored as VARCHAR of the same length: nothing to migrate.
        return isinstance(conn_type, sa.String) and conn_type.length == self.impl.length

In env.py, a compare_type callable handles project-wide rules, returning None for everything it does not care about so the built-in comparison still applies:

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


def compare_type(context, inspected_column, metadata_column, inspected_type, metadata_type):
    # Treat TEXT and unbounded VARCHAR as equivalent in this schema.
    if isinstance(metadata_type, sa.Text) and isinstance(inspected_type, sa.String) \
            and inspected_type.length is None:
        return False
    # JSON in the model is stored as JSONB by convention; do not flag it.
    if isinstance(metadata_type, sa.JSON) and isinstance(inspected_type, postgresql.JSONB):
        return False
    return None

With comparisons trustworthy, the last step is making drift impossible to merge. alembic check, available since Alembic 1.9, runs the autogenerate comparison against a database and exits non-zero if it would generate any operation — without writing a revision file. In CI, upgrade a disposable database to head and run it:

alembic upgrade head
alembic check
# FAILED: New upgrade operations detected: [('modify_type', None, 'orders', 'total_cents',
#   {'existing_nullable': False, ...}, INTEGER(), BigInteger())]

A failure means one of two things: someone changed a model without writing the migration, or a comparator is producing a false positive that should be fixed in env.py. Both are worth failing a build for. The surrounding pipeline — ordering migrations and deploys, and testing downgrades — is described in running Alembic migrations in CI/CD pipelines. Remember what alembic check still cannot see: enum labels, check constraint bodies and computed expressions, which managing enums, constraints and indexes in migrations covers.

Reviewing Generated Type and Default Changes

Once autogenerate detects type and default changes reliably, the review burden shifts from "did it notice?" to "is what it wrote safe to run?". A short checklist catches almost every problem, and it is worth pinning in the pull request template for migration changes.

Does the type change rewrite the table? On PostgreSQL, widening VARCHAR, dropping a length limit, and VARCHAR to TEXT are catalog-only. Almost everything else — integer widths, numeric precision reductions, timestamp to timestamptz, text to a structured type — rewrites every row under an ACCESS EXCLUSIVE lock. Check the table's size before approving.

Does it need a USING cast? PostgreSQL applies an implicit cast only when one exists. VARCHAR to INTEGER, TEXT to UUID, INTEGER to BOOLEAN all need postgresql_using, and the expression must handle every value actually in the column — an empty string does not cast to an integer. Run the cast as a SELECT against production data first.

Is the default change a data change? Changing server_default affects only future inserts; it never updates existing rows. If the intent is "all rows should now have this value", the migration also needs a batched UPDATE, and the review should ask whether that belongs in a data migration of its own, as in writing data migrations safely in Alembic.

Do the ORM and the default agree? A column with both a Python-side default= and a server_default gets its value from Python for ORM inserts and from the database for everything else. If they differ, rows inserted by a raw COPY or another service disagree with rows inserted by the application. Autogenerate compares only the server side.

Is the downgrade honest? Autogenerate writes the reverse alter_column, and for a narrowing type change that reverse can fail on data written after the upgrade — a BIGINT value too large for INTEGER. Either make the downgrade safe, or make it raise with a clear message rather than pretending to work.

A default change on a large, hot table has one more subtlety worth knowing: since PostgreSQL 11, adding a column with a constant default is instant, but changing an existing column's default is always instant too — the costly operation is only ever the backfill, never the ALTER. Reviews that treat default changes as dangerous can relax; reviews that treat backfills casually should not.

Frequently Asked Questions

Related

• Autogenerating and Reviewing Migration Scripts — The parent guide: what autogenerate compares and how to review its output.
• Excluding tables and schemas from Alembic autogenerate — Keeping objects Alembic should not manage out of the comparison.
• Naming constraints with a metadata naming convention — Names that make constraint comparison reliable too.
• Running Alembic migrations in CI/CD pipelines — Where alembic check fits in the pipeline.