Migrations — Changing a Schema Safely
The bookshop's books table already holds 5,000 books, and now every book needs an ISBN. You could log in to the production database and type an ALTER TABLE. Then your laptop, your teammate's laptop, the test server and production would each have a slightly different schema, and nobody would be sure which. Migrations prevent that.
The rule
Change the schema only through numbered scripts, in order, never rewritten.
A migration is a small file that moves the database from one version of the schema to the next. Migrations are:
- numbered or timestamped, so they run in a fixed order
- committed to Git with the code that needs them
- recorded by the migration tool in a table inside the database, so each one runs exactly once per database
- often reversible: an "up" step applies the change and a "down" step undoes it
migrations/
0001_create_authors.sql
0002_create_books.sql
0003_add_reviews.sql-- 0004_add_isbn_to_books.sql
ALTER TABLE books ADD COLUMN isbn TEXT;Tools such as Alembic (Python), Flyway (Java), Prisma Migrate or Knex (JavaScript) and the migration commands built into Rails and Django all follow this pattern. You run one command, and the tool applies every migration the database has not seen yet.
Two rules keep this reliable:
- Never edit a migration that has already run somewhere shared. That database will not run it again, so the edit silently diverges. Write a new migration instead.
- Change in steps the running code can survive. While you deploy, old and new code may both be running against the same database.
The shortcut
For risky changes, use expand, move, contract: first add the new thing in a form nothing breaks on, then move the data and code over, and only then remove or tighten the old thing.
Expand
Migration 0004 adds
isbnas a nullable column. Existing rows getNULL, and the old code ignores the new column.
The trap is doing it in one step: ADD COLUMN isbn TEXT NOT NULL fails on a table that already has rows (unless you supply a default), and a made-up default ISBN would be worse than no value.
Drills
Answer fast — trust the rule.
Migration 0004 has already run in production, and you spot a typo in it. What do you do?
How does a migration tool know which migrations a database still needs?
Why does ALTER TABLE books ADD COLUMN isbn TEXT NOT NULL; fail on a table with 5,000 rows?
Where should migration files live?
Remember
- Change the schema only through numbered scripts, in order, never rewritten.
- Expand, move, contract: add safely, migrate code and data, then tighten.
- A mistake in an applied migration is fixed by a new migration.