Schema Drift: How to Detect It and How to Prevent It

Schema drift is the gap between what your migration files say a database looks like and what a real database actually looks like. It creeps in through emergency fixes, ORMs left in auto-sync mode and environments that missed a deployment, and it surfaces as a migration that works everywhere except production. Detecting it means comparing real schemas against the source of truth on a schedule; preventing it means making migrations the only path to change.

What drift is, precisely#

There are two kinds, and they need different tools. Environment drift is production, staging and a developer laptop disagreeing with each other: a column that exists in one and not the others, an index with a different definition, a foreign key missing in the environment where it matters most. Source drift is the database disagreeing with the code that is supposed to describe it: the migrations directory, the ORM models, or both. The two often coexist, and the second is the more dangerous because it makes every future migration a guess.

How it creeps in#

• Hotfixes in production. An index added at two in the morning to stop a slow query, a NOT NULL dropped to unblock a release. Each is reasonable, none is in version control.
• Auto-sync ORMs. TypeORM's synchronize: true, Hibernate's hbm2ddl.auto=update, Sequelize's sync({ alter: true }). They change the schema to match the models without a migration file, differently per environment and per model version.
• Skipped or partial deployments. A migration that failed halfway on an engine without transactional DDL leaves a table created and its index missing, with the version table saying nothing happened.
• Edited migrations. Changing a migration file after it ran somewhere means the environments that ran the old version and those that will run the new one diverge for good.
• Manual data fixes with side effects. A script that recreated a table to reload it and forgot a constraint.
• Multiple writers. Two teams, a DBA and a service each owning a bit of the schema with no single migration history.

Why it hurts#

Drift does not fail loudly. It waits until a migration assumes a column that only exists in staging, until a query plan differs because production has an index nobody knows about, until a restore from backup is missing a constraint that the application relied on. Debugging any of these begins with the question "what does the schema actually look like there?", and if that question takes more than a minute to answer, drift has already cost you.

Detecting it#

Migration tool checks#

Every migration tool can tell you whether the migrations it knows about have been applied, and some can tell whether the files changed since. flyway validate compares checksums of applied migrations with the files on disk and fails on edits or missing files. liquibase status lists unrun changesets, and liquibase validate checks the changelog. Prisma's migrate status and Django's showmigrations do the same for their worlds. These checks catch edited and unapplied migrations; they do not see a column added by hand, because the tool never knew about it.

flyway -url=jdbc:postgresql://prod-replica/app -user=readonly validate
liquibase --url=jdbc:postgresql://prod-replica/app status --verbose
npx prisma migrate status
python manage.py showmigrations

Model-versus-migration checks#

Code-first tools can tell you whether the models have changed without a migration, which is source drift on the code side. Django's makemigrations --check exits non-zero when a migration would be generated; EF Core 8 added a check for pending model changes; Alembic users run alembic check, available since Alembic 1.9. All of these belong in CI so that a pull request cannot merge with a model change and no migration.

Schema comparison#

To catch manual changes you need to compare the actual schema with a reference. The reference is either another environment or a database freshly built from the migrations, which is the honest definition of "what the code says".

# PostgreSQL: dump both schemas without owners or privileges and diff them
pg_dump --schema-only --no-owner --no-privileges -h prod-replica app > prod.sql
pg_dump --schema-only --no-owner --no-privileges -h localhost app > fresh.sql
diff prod.sql fresh.sql

# Atlas: compare a live database with the state a migration directory produces
atlas schema diff --from "postgres://ro@prod-replica/app" --to "file://migrations" --dev-url "docker://postgres/17/dev"

# Liquibase: compare two databases
liquibase diff --url=jdbc:postgresql://prod-replica/app --reference-url=jdbc:postgresql://localhost/app

Dump diffs are noisy but universal. Tools such as Atlas, Liquibase diff and migra (PostgreSQL only) produce a structured or even executable difference, which is what you want when the gap has to be closed rather than just found. Run the comparison against a read replica or a nightly restore, never against the primary.

Comparing pictures#

For the human review that follows, a pair of ER diagrams is faster to read than a 400-line diff. Draw one from the migration files and one from the environment in question, put them side by side, and missing tables, extra columns and absent foreign keys are visible in seconds. The diagram from the migrations is also the artifact you want in the repository, so that the reference is versioned along with the code.

Preventing it#

1. Migrations are the only path. No schema-changing privileges for application accounts, for developers on production, or for ORMs. synchronize and hbm2ddl.auto=update are off outside a local sandbox.
2. Every change is a file in the repository, including the 2 a.m. index. The hotfix is applied by hand if it must be, and the same day a migration is written that recreates it idempotently, so the next environment gets it the normal way.
3. CI builds the schema from scratch on every pull request, on the same engine and version as production, and fails when a migration does not apply. This is where throw-away databases in Docker earn their keep.
4. CI checks models against migrations with makemigrations --check, alembic check or the equivalent.
5. A scheduled drift job diffs each long-lived environment against a fresh build and opens an issue when they differ.
6. Never edit an applied migration. Write a new one. Enforce it with flyway validate or checksum-equivalent checks at deploy time.
7. Reconcile, do not paper over. When drift is found, decide per difference whether the environment or the code is right, then either write the migration that brings the code up to date or the one that reverts the environment. Baseline only after that.

Where SchemaDrawer fits#

SchemaDrawer produces the reference picture. Upload the migration files of any of 22 tools, choose the engine and version production runs, and it really executes the migrations in Docker, reads the resulting schema over JDBC and returns an SVG ER diagram with tables, columns, types, primary keys and foreign keys. Its GitHub Action opens a pull request with the updated diagram whenever the migrations change, so the repository always holds a current drawing of what the code says the schema is. A diagram of what an environment actually has is then a dump and a second drawing away, and the two are compared by eye in less time than a diff takes to scroll. It draws constraints and columns rather than indexes or triggers, so index drift still needs one of the comparison tools above.

Key takeaways#

• Drift is the database disagreeing with the code or with other environments; both kinds hide until a deployment fails.
• Migration tool checks catch edited and unapplied migrations; only schema comparison catches manual changes.
• Build a fresh database from the migrations in CI; it is both a test and the reference for every diff.
• Remove every schema-changing path except migrations, including ORM auto-sync.
• Keep a current ER diagram of the migrations in the repository; comparing pictures is how humans find drift fast.