Schema Migrations and Change Management

Intermediate · 30 min

Why schema changes need to be versioned, repeatable code, and how the expand-contract pattern lets you deploy and migrate independently.

Before thisORMs, Query Builders, and Raw SQL

USENIX · 30 min

Schema Migrations and Change Management

The player loads from youtube-nocookie.com when you press play. Nothing reaches YouTube before that.

Taught by USENIXReport a problem with this video
Not started
Set this lesson's status

What you'll learn

  • A migration is a versioned, repeatable piece of code that changes your schema the same way every time, in every environment — which is the only way a team can trust that staging and production actually share a schema history.
  • Deploying new application code and migrating the schema are two separate events that happen at different times, so a safe migration has to work correctly both before and after the code deploy that depends on it.
  • The expand-contract pattern splits a schema change into backward-compatible steps specifically so old code and new code can both run correctly against the database during the rollout window.
  • A rollback plan has to be decided before you run a migration, not improvised after — some schema changes (dropping a column, deleting data) simply cannot be undone by reversing the steps.

Why “just run ALTER TABLE” stops working

On a side project, changing a database table is simple: stop the server, run the ALTER TABLE, start it back up. That approach fails almost immediately once more than one person touches the codebase, or the app has more than one environment, or it’s running while people are using it. Someone changes the schema on their machine and forgets to tell anyone. Staging and production drift apart because one got a manual tweak the other didn’t. A table with real traffic can’t just go offline for a change. None of these are edge cases — they’re what happens to every growing codebase within a few months.

The fix is to treat schema changes the same way you treat application code: as versioned, repeatable, checked-in files that describe exactly what changed and in what order. That’s what a migration is. A migration tool (Rails migrations, Django migrations, Flyway, Alembic, Prisma Migrate — the names vary, the idea doesn’t) keeps a table in the database itself recording which migrations have already run, so running the migration command always brings any environment — your laptop, staging, production — to the exact same schema state, regardless of its starting point.

This buys you two things a hand-run ALTER TABLE never gives you: a reviewable diff (a migration file goes through the same pull request and code review as everything else) and a reliable history (you can look at the migrations folder and see the entire evolution of the schema, in order, with no gaps).

Code deploys and migrations are two separate events

Here’s the part that trips people up even after they’ve adopted a migration tool: deploying new application code and running a schema migration are not the same moment, even if they’re part of the same release. A deploy rolls out gradually — old server instances finish handling their current requests while new ones spin up, a migration step runs as its own stage in the pipeline, sometimes a canary gets the new code hours before everyone else. During that window, you can have old code running against a migrated schema, or new code running against a not-yet-migrated schema, depending on the exact order your deploy pipeline runs things in.

A migration that renames a column and a code deploy that starts reading the new name look fine in isolation. Run them in the wrong order, or let them overlap even briefly, and you get a stretch of real user-facing errors — the renamed column doesn’t exist yet from the old code’s perspective, or the old column is gone from the new code’s perspective. This is the actual problem schema migrations have to solve: not just “change the schema,” but “change the schema in a way that survives being out of sync with the code for a while,” because it always will be, if only for a few seconds.

Expand-contract: the fix

The embedded talk walks through the standard answer to that problem: the expand-contract pattern (sometimes called parallel change). Instead of one migration that renames a column, you do it in three steps, each one safe on its own:

  1. Expand — add the new column alongside the old one. Nothing is removed, so every version of the application code, old and new, keeps working exactly as it did.
  2. Migrate — backfill the new column from the old one, and update the application to write to both (or just the new one, reading from whichever is populated) during the transition.
  3. Contract — once every instance of the application is confirmed to be using the new column exclusively, drop the old one.

The property that makes this safe is that at every individual step, both the previous version of the application and the next version can run against the database without errors. You never ship a migration that only works if the code deploy lands at the exact same instant — because it won’t. The same shape applies to adding a NOT NULL constraint (add it nullable first, backfill, then tighten it), splitting a table, or changing a column’s type. The pattern is always: add compatibly, migrate the data and the code, then remove what’s no longer used — never collapse that into one irreversible step.

Rollback: decide before you run it, not after

A migration tool’s “down” migration — the reverse of “up” — works cleanly for additive changes: if up adds a column, down drops it, no data lost. It does not work cleanly for destructive changes. If a migration drops a column or deletes rows, there is no script that can un-delete that data; the “down” migration can recreate the column, but it comes back empty. This is why experienced teams decide the rollback story before running a risky migration, not after it fails: a tested backup-restore path for truly destructive changes, or — more often in practice — treating migrations as forward-only and shipping a new, corrective migration if something goes wrong, rather than trying to reverse time on a production database. Deciding this upfront is also exactly why expand-contract matters: a migration that’s still in its expand or migrate phase is trivially safe to pause or reverse, because the old structure is still sitting right there.

Check yourself

  1. 01Why is a schema migration written as versioned, repeatable code instead of someone running ALTER TABLE by hand on production?

    Because a hand-run change isn't tracked anywhere, can't be reliably reproduced on another environment, and leaves no record of what ran, in what order, or whether it actually matches what's in staging — a migration file checked into version control fixes all three problems at once.

  2. 02Why can't you assume your new application code and your schema migration go live at the exact same instant?

    A real deployment rolls out gradually — multiple app instances, a migration step that runs separately from the code deploy, sometimes a canary release — so for some window of time, either old code is running against the new schema or new code is running against the old schema, and both have to work.

  3. 03What does the 'expand' phase of expand-contract actually do, and why does it come first?

    It adds the new column, table, or structure alongside the old one without removing anything, so that old code (which doesn't know about the new structure yet) keeps working exactly as before while new code can start using the new structure — nothing breaks because nothing was taken away.

  4. 04What does the 'contract' phase do, and why does it have to come last?

    It removes the old structure that's no longer needed, and it has to come last because removing it before every piece of code has switched to the new structure would break whatever hasn't been updated yet — contract only happens once you've confirmed nothing depends on the old shape anymore.

  5. 05Why doesn't a rollback plan always mean 'run the reverse migration'?

    Some operations destroy information — dropping a column, deleting rows — and a reverse script can recreate the structure but not the data that was in it, so for destructive changes the real rollback plan is usually a tested backup-restore path or a forward-only compensating migration, decided before the migration runs, not improvised after it fails.

Go deeper

Notes

Private to you

Esc
↑↓ navigate↵ open? shortcuts

Keyboard shortcuts

Press Esc to close

Anywhere

⌘K
Search and commands
/
Search
?
This sheet
Esc
Close

Reading a lesson

J
Next lesson
K
Previous lesson
B
Bookmark