Most migration failures are not caused by the tool. They are caused by habits: editing a file that already ran, letting an ORM alter production on startup, testing a rollback for the first time during an incident. Here are the ten anti-patterns that show up again and again across Flyway, Liquibase, Django, Prisma, Rails and the rest, why each one hurts, and the practice that replaces it.
Anti-patterns that corrupt the history#
1. Editing a migration that has already been applied#
A migration ran in staging, a reviewer asked for a change, and the author edited the file. Now staging's history table says version 42 was applied with one checksum, and the repository has a version 42 with another. Flyway refuses to proceed on a checksum mismatch, Liquibase does the same, and the tools that do not check checksums quietly leave staging and production with different schemas. Worse, the edited statements never run anywhere the original already ran.
Instead: treat an applied migration as immutable, including in shared development and staging databases. Ship a new migration that changes the result. If a mistake must be corrected before anything but a local database has seen it, delete the local database rather than the history. The Flyway naming and versioning guide explains what the checksum protects.
2. Environment-specific migrations#
A branch on the environment name inside a migration, a file that exists only in the staging folder, a manual step in the release runbook that the developers' setup does not need. Each of these means that no two environments run the same sequence, so the only environment where the full sequence is ever tested is production.
Instead: one linear history for every environment. Differences that genuinely exist, such as a tablespace or a partition scheme, belong in configuration the migration reads, not in separate migrations. Seed data for development goes through a separate mechanism, not through the schema history.
3. Hotfixes that bypass the tool#
Someone adds an index straight on production at two in the morning to stop an incident. It works, and it is forgotten. The next fresh environment does not have the index, the next migration that creates the same index fails on production because it already exists, and a diagram drawn from the migrations no longer matches reality.
Instead: allow the emergency change, then write the migration the same day, with the same statement guarded by IF NOT EXISTS or a precondition, and run it everywhere. Compare the migrated schema against production regularly; schema drift is this anti-pattern accumulating.
Anti-patterns in the migrations themselves#
4. Mixing schema changes and data changes#
A migration that adds a column, backfills a hundred million rows and adds a constraint in one transaction holds locks for the duration of the backfill. It either succeeds slowly or fails entirely, leaving nothing applied on engines with transactional DDL and half of it applied on engines without.
Instead: separate them. Schema changes are fast and go in migrations. Large data changes run in batches, outside the migration's transaction, often from a job rather than from the migration tool; the migration only records that the backfill is required. The expand and contract pattern is the structured form of this rule.
5. One huge migration#
Thirty tables, all their constraints and indexes, and the reference data in a single file, because it was generated from an ORM diff or a dump. When it fails on statement 212 nobody can tell what was applied, and reviewing it is impossible.
Instead: one concern per migration, named for what it does. Generated migrations should be split and read before they are committed; a migration nobody read is a migration nobody reviewed.
6. Ignoring locks and long transactions#
ALTER TABLE on a busy table waits for an exclusive lock and makes every query behind it wait too. A migration that takes a millisecond in a test takes down a service in production because a reporting query was holding the table.
Instead: set a short lock_timeout and retry, use the online variants the engine offers (NOT VALID and VALIDATE CONSTRAINT, CREATE INDEX CONCURRENTLY, MySQL's ALGORITHM=INPLACE, LOCK=NONE), and run a linter over the SQL in the pull request.
Anti-patterns in tooling and process#
7. Letting the ORM alter production on startup#
synchronize: true in TypeORM, hbm2ddl.auto=update in Hibernate, db push in Prisma, create_all() in SQLAlchemy: convenient in a prototype, and in some of them a way to drop a column in production because someone renamed a field. There is no history, no review, no rollback and no record of what changed when.
Instead: generate migrations from the model diff, read them, commit them, and apply them with the tool's migrate command as a deliberate deployment step. Keep auto-sync for throwaway local databases only, and fail the build if it is enabled in a production profile. The TypeORM and Prisma pages show the file layout the migrate commands produce.
8. No rollback, or a rollback nobody has run#
Some tools require a down migration and some do not, and the teams that write them rarely test them. The first time the down script runs is during the incident that needs it, on production, and it drops a table that the up script had renamed rather than created.
Instead: decide explicitly per project. Either write down migrations and run them in CI (up, down, up again, then compare the schema with a single up), or adopt roll-forward only, in which every fix is a new migration and the pipeline can always deploy the previous application version against the newer schema. Roll-forward only works if you also follow the expand and contract rule; without it the previous version cannot run.
9. Never running the migrations in CI#
Migrations are code that runs once, in production, with no retry. Surprisingly often they are the only code in the repository without a test, so the first engine they meet after the author's laptop is the production one. Dialect mistakes, ordering mistakes and files the tool silently ignores all get through.
Instead: run every migration from an empty database on every change, in a container of the production engine and version, and assert on the schema that comes out. Testing database migrations in CI with Docker shows a Testcontainers version and a plain GitHub Actions version.
The documentation anti-pattern#
10. A schema nobody can see#
The last anti-pattern is not about the migrations themselves but about what they produce. After two hundred migrations, the only accurate description of the schema is the database. New engineers reverse-engineer it with a GUI tool, the diagram in the wiki is three years old, and a reviewer approving migration 201 has no way to see what it does to the whole.
Instead: generate the documentation from the migrations, automatically, on every change, so that it is never older than the code. A diagram of tables, columns and foreign keys in the repository, updated by the pipeline, is the cheapest form of that. Keeping the database diagram up to date with CI describes a workflow, and the guide on database documentation covers what else belongs next to it.
Where SchemaDrawer fits#
SchemaDrawer addresses the ninth and the tenth items in one step. It takes the migration files of 22 tools, runs them in Docker against a throw-away database of the engine and version you name, and returns an SVG diagram of the tables, columns, primary keys and foreign keys that came out. Because the migration really runs, a file the tool ignores, a statement that fails on your engine, or a foreign key to a table that does not exist yet shows up as a failed job with the tool's own message, not as a surprise later. The GitHub Action opens a pull request with the diagram, so the reviewer of a migration sees the schema it produces. It is free to use today.
Key takeaways#
- Applied migrations are immutable; fix forward with a new one.
- One history for every environment, one concern per migration, schema and data changes kept apart.
- Respect locks: short lock timeouts, online variants, a linter in the pull request.
- No ORM auto-sync outside a throwaway database; migrations are reviewed, committed code.
- Run migrations in CI against the real engine, and generate the schema documentation from them.