The up-to-date test misses three kinds of drift: server defaults, CHECK constraints and enum members

[AlexeyShalaev]

Measured on 0.2.2 against PostgreSQL 17. Six kinds of drift between the migrated schema and the models, each a one-file change to a clean four-revision history, run through test_migrations_up_to_date:

drift_type              (String(100) in the migration, String(255) in the model)   failed   caught
drift_index_missing     (idx_orders_user_id never created)                         failed   caught
drift_extra_column      (a column the model does not have)                         failed   caught
drift_server_default    (server_default false in the migration, true in the model) passed   missed
drift_check_missing     (the amount check never created)                           passed   missed
drift_enum_value        (order_status without 'shipped')                           passed   missed

The three misses are Alembic's, not the test's: compare_metadata skips server defaults unless compare_server_default=True is in the context opts, never compares CHECK constraints, and never compares enum members. The test as written passes opts={"version_table_schema": ...} and nothing else, so a team that trusts "the diff is empty" is trusting it for half of what can drift, and the half it does not cover is the half that shows up as a production incident: a wrong default on insert, a check that is not there, a value the code writes and the type rejects.

Three checks close the gaps, written against the inspector and pg_enum; against the same six variants plus the clean history they catch the three misses and stay green on the clean one:

clean                  3 passed
drift_server_default   1 failed, 2 passed   (test_server_defaults_match)
drift_check_missing    1 failed, 2 passed   (test_check_constraints_match)
drift_enum_value       1 failed, 2 passed   (test_enum_values_match)

What I think it needs: a class attribute, say migration_diff_compare_server_default: ClassVar[bool] = False, that the up-to-date test passes through to compare_metadata (opt-in, because a default expressed two ways is a common false positive; the docs should say which spellings agree); and two new tests on MigrationConsistencyMixin, one comparing the CHECK constraints the metadata declares against get_check_constraints per table (by name, resolved through the metadata's naming convention the way the naming test already resolves rules), one comparing each Enum column's members against pg_enum in order. Both are PostgreSQL-only like the rest of the mixin. The hand-written versions are in the lab as tests/test_drift_extras.py; the enum one is a straight port, the check one needs the convention resolution done properly rather than my ad-hoc prefix guard.

Lab: https://github.com/bedrock-python/bedrock-python.github.io/tree/docs/production-python-series/docs/blog/lab/2026-09-07-five-alembic-migration-tests (migrations/versions/drift_*, tests/test_drift_extras.py, VARIANT=drift_enum_value python -m pytest tests/test_gauntlet.py tests/test_drift_extras.py).

[AlexeyShalaev]

I reproduced the three misses on 0.2.2 against your lab and I'm going with the shape you sketched, with two details settled differently after reading what SQLAlchemy and Alembic actually do.

The attribute: migration_diff_compare_server_default: ClassVar[bool] = False on MigrationConsistencyMixin, passed straight through as compare_server_default in the MigrationContext opts of test_migrations_up_to_date. Off by default, so nobody's suite changes. I measured which spellings agree on PostgreSQL 17 rather than guessing: Alembic's PostgreSQL comparison compares the text first and, when it differs, asks the server whether the two expressions are equal, so text("true"), sa.true() and "true" all agree with a column defaulted to true, func.now() agrees with now() and CURRENT_TIMESTAMP, "0" with 0, text("'{}'") with '{}'::jsonb, and gen_random_uuid() with itself. What it reports is a different value (your false vs true), two different volatile functions (clock_timestamp() vs now()), and a default present on one side only, including a model that has a Python-side default= where the migration has a server_default. That last one is why it stays opt-in; the docs will say all of this.

The check-constraint test: I'm not going to re-derive names from the convention. SQLAlchemy already does that when the constraint attaches to the table, so CheckConstraint("amount > 0", name="amount_positive") under your chk_%(table_name)s_%(constraint_name)s convention carries chk_orders_amount_positive as its name by the time we see the metadata, and rules_from_metadata only knows prefixes and suffixes, so it could never produce a name anyway. The two cases it does not settle are deferred names (Boolean(create_constraint=True), resolved only at DDL time) and names over 63 characters, which PostgreSQL truncates with a hash. Alembic's own _get_constraint_final_name handles both, and it is what autogenerate already uses for index and unique-constraint names, so the check test resolves names exactly the way the diff test does. Comparison is by name, per table, in both directions: a named constraint the model declares and the database lacks is reported, and so is one the database has that the model does not, except on a table where the model has an unnamed check constraint, because that one carries whatever name PostgreSQL gave it and the "unexpected" side would be a false positive. Unnamed constraints are never compared; a ck convention with %(constraint_name)s already forbids them.

The enum test: every Enum column with a native, named type, its labels read through the inspector's get_enums (ordered by enumsortorder) and compared as a list against Enum.enums, so a value added in the wrong position is reported too. A type in the database that no column uses is not this test's business.

Both tests go on MigrationConsistencyMixin, not a fourth mixin. They ask the same question as test_migrations_up_to_date with the same four fixtures, and MigrationTestBase would include a new mixin anyway, so a separate one would only add a name to learn. The five existing tests keep their names and behaviour; migration_diff_ignore_tables applies to both new tests. The comparison itself lives in utils/diff.py as two sync helpers next to is_ignored_diff_item, so they are unit-testable and usable from a custom test.

Rejected along the way: comparing check constraints by SQL text (PostgreSQL rewrites amount > 0 to (amount > (0)::numeric), so it would only ever match by luck), reading pg_enum by hand when the inspector already returns it in order, and turning the server-default comparison on by default.

Tests: a third sample app with a two-revision history in four variants (clean, wrong server default, missing check, enum without shipped) driven through MigrationTestBase subclasses, so the assertion is on the inherited test itself failing, plus unit tests for the helpers. I'll also run your lab's test_gauntlet.py against the branch for the four variants and put the lines in the PR.

[AlexeyShalaev]

Shipped in #38, as planned above, with one detail I want to flag.

What lands:

• migration_diff_compare_server_default: ClassVar[bool] = False on MigrationConsistencyMixin,
  passed through as compare_server_default in the MigrationContext opts of
  test_migrations_up_to_date. Nothing changes for anyone until they set it.
• test_check_constraints_match — named CHECK constraints, per table, both directions, names
  resolved through Alembic's _get_constraint_final_name, the same helper autogenerate uses for
  index and unique-constraint names, so a deferred Boolean(create_constraint=True) name and a name
  over 63 characters resolve the way the DDL would write them.
• test_enum_values_match — every native, named Enum column, labels read through the inspector's
  get_enums() and compared with Enum.enums as an ordered list.
• compare_check_constraints and compare_enums in alembic_gauntlet.utils.diff, both sync and
  both public, if you want them outside the mixin.

Your three lines from the lab, against the inherited suite on the branch — tests/test_gauntlet.py
alone, no hand-written checks:

VARIANT=clean                   7 passed
VARIANT=drift_server_default    7 passed            (attribute off, as documented)
VARIANT=drift_server_default    1 failed, 6 passed  (attribute on) — test_migrations_up_to_date
VARIANT=drift_check_missing     3 failed, 4 passed  — test_check_constraints_match names it
VARIANT=drift_enum_value        1 failed, 6 passed  — test_enum_values_match

drift_check_missing fails three rather than one because that variant's 0003 creates nothing on
upgrade but still drops the constraint on downgrade, so stairway and full-downgrade hit
constraint "chk_orders_amount_positive" ... does not exist on their own. The new test is the one
that says what actually drifted.

The detail: the lock moves alembic 1.19.1 → 1.19.2, and not as housekeeping. 1.19.0 shipped
name-based CHECK detection as an autogenerate plugin that was on by default, and 1.19.2 renamed it to
alembic.ext.checkconstraint_byname and dropped it from the default plugin set. So on 1.19.0 and
1.19.1 your drift_check_missing case is reported by the diff test as well — which is why the
lab measured a miss on 0.2.2 with an older alembic, and why pinning our CI to the version people
install today keeps the new test's subject unambiguous. Nothing in the change needs it: both
helpers behave identically on alembic==1.8.0 with sqlalchemy==2.0.0, the declared floor, which
I checked on a fresh venv.

Nothing to change on your side beyond opting into the server-default comparison where you want it.
Worth knowing when you do: the comparison is only as good as the naming. An unnamed
CheckConstraint("amount > 0") is never compared, and on a table that has one the test also stops
reporting database constraints your models lack, because PostgreSQL named that one itself and the
"unexpected" side would be noise. A ck template with %(constraint_name)s in it makes unnamed
constraints impossible, and that is the shape I would recommend in the post. Which spellings of a
server default agree — text("true"), sa.true() and "true" all agree with true, func.now()
with now() and CURRENT_TIMESTAMP — is a table in
Configuration.

Closing when #38 merges.