Schema Migration as Archaeology

The migration file is a time capsule. It records, in unambiguous SQL, what the team believed about the world on a specific day. Read the migrations in order and you can reconstruct the history of a product almost more reliably than from the commit log.

Context

I joined a merge-queue-heavy project where callers kept breaking on “small” schema changes. The application code looked fine. The migrations told the real story: three years of additive columns, a renamed enum buried in a single ALTER, a nullable field that became load-bearing without a backfill step anyone documented. At 2am, the on-call engineer does not have the oral history. They have the migration chain — if it was written for strangers.

The merge queue brief is apt. Schema is the contract the queue serializes against. When the contract drifts from caller expectations, the failure looks like a flaky test until it looks like an outage.

Mechanism

The schema is the skeleton; migrations are the strata. Application code is soft tissue — refactored, deleted, rewritten in a season. Migrations accumulate. Each file is a layer: here we split the user table; here we added the feature flag column; here we fixed the mistake from Q3 with a compensating migration that only makes sense if you know Q3 happened.

Martin Fowler and Pramod Sadalage’s evolutionary database design treats migrations as the primary narrative of data change — small, reversible steps, continuously integrated, never pretending the database started in its current shape yesterday.¹ That discipline exists because production data outlives every deploy branch.

Naming and commentary are forensic tools. 20240301_fix.sql is a crime scene without a report. 20240301_backfill_order_status_after_checkout_redesign is evidence. Comment the reasoning: why nullable, why now, what backfill ran, what rollback was rejected and why. The tired 2am mind is not smarter than the alert engineer at 3pm. It has less bandwidth. The migration must carry the context the engineer cannot reconstruct from caffeine and grep.

Squashing is a trade, not a virtue. Teams squash migrations for aesthetic origin stories — one clean initial schema, no embarrassing middle ages. They also erase the archaeology. You lose the sequence that explains why column seventeen exists, why two indexes overlap, why the constraint name references a vendor that left the stack in 2019. Squash for greenfield clones if you must; preserve the chain in the repo history that production actually ran.

Read migrations before code. When I onboard to a project, I read migrations first. They answer questions code hides: what is nullable because nobody trusted the backfill; what is denormalized because read latency mattered; what foreign keys were deferred because the merge queue could not tolerate lock duration. The schema is the accumulated decision log. The ORM models are a flattering portrait.

For caller-contract drift: we traced the break to a migration that changed a column default without updating the API serializer’s assumptions. The migration comment said “align with new spec.” The spec link was dead. Archaeology failed because the dig site was unlabeled.

Tradeoffs

Migration size vs deploy risk. Large migrations are easier to read as single stories; small migrations are easier to roll forward safely. Prefer small steps with clear comments over epic rewrites that lock tables through lunch.

Zero-downtime patterns vs complexity. Expand-contract-backfill patterns buy availability at the cost of multi-phase mental overhead. Document the phase in each file or the next maintainer deploys phase three before phase two.

When squashing helps. Early-stage products with no long-lived production data can reset cheaply. The rule: squash only when you are certain no environment still needs the intermediate strata for recovery or audit.

Tooling vs discipline. Flyway, Liquibase, Rails migrations, Prisma migrate — the tool enforces ordering, not intent. The comment field is still yours.

Close

When you join a project, read the migrations before the code. When you write a migration, write it for the stranger at 2am who inherits your assumptions without your memory.

The messiness is the evidence. Preserve it.

— JV · Dark Heart Labs.

References

Pramod J. Sadalage and Martin Fowler, Refactoring Databases: Evolutionary Database Design (Addison-Wesley, 2006). Fowler and Sadalage are the definitive reference for incremental schema change, migration sequencing, and treating database history as a first-class engineering artifact. ↩