Lesson 43 • Advanced Track
Schema Versioning, Migrations & CI/CD
By the end of this lesson you'll be able to evolve a production database the way professional teams do — with numbered, forward-only migration files that any environment can replay, a history table that records what ran, and the expand/contract pattern that lets you rename or reshape columns with zero downtime while a live app keeps serving traffic.
What You'll Learn
- ✓Write numbered, forward-only migration files (V1, V2, …)
- ✓Use a migration-history table the way Flyway and Liquibase do
- ✓Tell versioned migrations apart from repeatable ones
- ✓Roll back safely — and why you usually roll forward instead
- ✓Run the expand/contract pattern for zero-downtime schema changes
- ✓Wire migrations into a CI/CD pipeline so deploys stay automated
1. What Is a Migration?
A migration is a single SQL file that makes one change to your schema and carries a version number in its name. You run them in order — V1, then V2, then V3 — and a tool records which ones it has already applied. That ordered, recorded sequence is your schema's history.
🏗️ Real-world analogy
Think of migrations as the numbered blueprints for a building. You don't rub out blueprint #1 to move a wall — you issue blueprint #2 with the change. Anyone can rebuild the whole structure by following the blueprints in order, and you always know exactly what was done and when.
The opposite — logging into production and typing ad-hoc ALTER TABLE by hand — leaves dev, staging, and production subtly different, with no record of what changed. Migrations fix that: the schema is now code in Git.
V1 — the first migration
One numbered file that creates a table.
-- A migration is one numbered, forward-only SQL file.
-- File: V1__create_users.sql (the "V1" is its version number)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Rules of a migration:
-- • It is checked into Git, like any other code.
-- • Once it has run on a shared database, you NEVER edit it.
-- • The next change goes in a NEW file (
...The single most important rule is highlighted below — break it and your environments drift apart.
2. Adding the Next Change
Need another change? Create the next-numbered file. Here V2 adds a column. A fresh database becomes correct by replaying every file in order, which is why every environment ends up identical.
V2 — the next migration
A new file adds a column; V1 is left untouched.
-- The next change is a brand-new file with the next number.
-- File: V2__add_phone_to_users.sql
-- Add a nullable column — this is a fast, safe change.
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Flyway/Liquibase run V1 then V2, in order, exactly once each.
-- A fresh database can rebuild itself by replaying V1, V2, V3...
-- so every environment (dev, staging, prod) ends up identical.Your Turn: write V3 safely
Fill in the blanks to write the next migration that adds a bio column. The expected file and contents are in the comments so you can check yourself.
🎯 Your Turn: the next migration
Name the V3 file and add a nullable column.
-- 🎯 YOUR TURN — write the NEXT numbered migration.
-- V1 and V2 already exist. Add a "bio" column to users SAFELY.
-- 👉 1) Name the file with the next version number:
-- File: ___ (e.g. V3__add_bio_to_users.sql)
-- 👉 2) Add a NULLABLE text column (nullable = safe, no table lock):
ALTER TABLE users ADD COLUMN ___ ___; -- column name, then the type
-- ✅ Expected: a new file V3__add_bio_to_users.sql containing
-- ALTER TABLE users ADD COLUMN bio TEXT;
-- After deploy, flyway_schema
...3. The Migration-History Table
How does a tool know V1 and V2 already ran? It keeps a history table inside your database with one row per applied migration. On each deploy it compares the files on disk against this table and runs only the new ones.
It also stores a checksum — a hash of each file's contents. If you edit an already-applied migration, the checksum no longer matches and the tool refuses to run, which is exactly how it enforces the forward-only rule for you.
The history table
Flyway's flyway_schema_history, recreated by hand.
-- Migration tools keep a HISTORY TABLE so they know what ran.
-- This is what Flyway creates (Liquibase calls it DATABASECHANGELOG).
CREATE TABLE flyway_schema_history (
installed_rank INT PRIMARY KEY, -- order applied
version VARCHAR(50), -- "1", "2", ...
description VARCHAR(200),
script VARCHAR(1000), -- the file name
checksum INT, -- hash of the file's contents
installed_on TIMESTAMPTZ DEFAULT NO
...Result — 2 rows:
| version | description | success |
|---|---|---|
| 1 | create users | true |
| 2 | add phone to users | true |
4. Versioned vs. Repeatable Migrations
Versioned migrations (named V…) run once, in order — perfect for tables and columns, where running the same CREATE TABLE twice would error. Repeatable migrations (named R…) re-run automatically whenever their file changes — perfect for things you redefine wholesale, like views, functions, and seed data, using CREATE OR REPLACE.
CREATE OR REPLACE rather than plain CREATE.A repeatable (R__) migration
Re-applied whenever the file changes.
-- VERSIONED migrations (V__) run ONCE, in order. Good for tables.
-- REPEATABLE migrations (R__) re-run whenever their file CHANGES.
-- Use them for things you redefine wholesale: views, functions, seed data.
-- File: R__active_users_view.sql (note the R and the double underscore)
CREATE OR REPLACE VIEW active_users AS
SELECT id, email
FROM users
WHERE status = 'active';
-- Edit this file and the tool re-applies it (the checksum changed).
-- That is allowed here precisely because CREATE OR
...5. Rolling Back — and Why Pros Roll Forward
A rollback is the reverse of a migration: the undo of ADD COLUMN bio is DROP COLUMN bio. Some tools let you write a paired "down" script for this. It sounds tidy, but there's a catch most teams learn the hard way.
DROP COLUMN deletes all of that data. In production the safer move is to roll forward: write a new migration (V4) that corrects the mistake, so the history stays append-only and nothing is silently destroyed.Keep down-scripts for fast feedback in dev and CI, but in production, prefer a new forward migration over an in-place rollback.
6. Expand / Contract — Zero-Downtime Changes
Here's the hardest problem in this lesson. You can't rename a column in one step on a live system, because for a moment the running app still expects the old name — and a request hitting it during the change would crash. The fix is the expand/contract pattern (also called parallel change): change the schema and the app in small, separately-deployed steps where every step is compatible with the code already running.
🚿 Real-world analogy
Replacing a water pipe in a busy building: you fit the new pipe alongside the old one (expand), connect everything to it, then remove the old pipe (contract). The water never stops flowing because both pipes work during the overlap.
The sequence is always the same four phases: add the new column (nullable), backfill existing rows into it, switch the app to use it, then drop the old column once nothing references it.
Backward compatibility means a new schema still works with the old app; forward compatibility means the old schema still works with the new app. Expand/contract works precisely because the overlap period keeps both true at once.
Expand → backfill → switch → contract
Rename a column on a live database with no downtime.
-- ZERO-DOWNTIME rename of users.email -> users.email_address
-- using the EXPAND / CONTRACT (parallel change) pattern.
-- Each migration ships separately, with the app updated in between.
-- V4 (EXPAND): add the new column, nullable. Old app still works.
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
-- V5 (BACKFILL): copy existing data into the new column.
UPDATE users SET email_address = email WHERE email_address IS NULL;
-- (Deploy app version that READS/WRITES BOTH columns. Bot
...Your Turn: order the steps
The four expand/contract steps for a rename are shuffled below. Number them 1–4 in the comments. The correct order is in the comments so you can check yourself.
🎯 Your Turn: sequence the rename
Put expand/backfill/switch/contract in order.
-- 🎯 YOUR TURN — put the expand/contract steps in the RIGHT order.
-- Goal: rename "phone" to "phone_number" with zero downtime.
-- Below are the four steps SHUFFLED. Number them 1-4 in the comments.
-- 👉 Step ___ : DROP COLUMN phone; (contract)
-- 👉 Step ___ : ADD COLUMN phone_number VARCHAR(20); (expand)
-- 👉 Step ___ : deploy app that reads only phone_number; (switch)
-- 👉 Step ___ : UPDATE users SET phone_number = phone; (backfill)
-- ✅ Expected
...7. CI/CD for Databases
Because migrations live in Git, your deployment pipeline can run them automatically. A typical flow: on every pull request, CI spins up a throwaway database and applies all migrations from scratch to prove they replay cleanly; on merge to production, the deploy step runs flyway migrate (or liquibase update) before the new app starts.
A migration step in a CI/CD pipeline
1. Build — package the app and its migration files together.
2. Test — apply every migration to a fresh disposable DB, then run the test suite.
3. Migrate — run flyway migrate against production (it skips already-applied files via the history table).
4. Release — start the new app version, which now matches the migrated schema.
Because expand/contract keeps each step backward-compatible, you can run the migration before the new app rolls out and still serve traffic the whole time — that's what makes the deploy zero-downtime.
Common Errors (and the fix)
- "Migration checksum mismatch for version 2" — you edited an already-applied migration. Fix: revert the file to its original contents and put your change in a new, higher-numbered migration instead.
- Destructive change in one step:
ALTER TABLE users RENAME COLUMN email TO email_address;on a live system breaks the running app mid-deploy. Fix: use expand/contract — add, backfill, switch, then drop across separate migrations. - No migration history: applying ad-hoc
ALTER TABLEby hand means dev/staging/prod drift and "works on my machine" bugs. Fix: put every change in a versioned file checked into Git and let the tool track it. - Long-locking ALTER on a big table:
ADD COLUMN status VARCHAR NOT NULL(no default) can lock millions of rows and time out the deploy. Fix: add the column nullable, backfill in batches, then addNOT NULLin a later migration. - Rollback silently deleted data: running a "down"
DROP COLUMNin production wipes everything users stored there. Fix: roll forward with a new corrective migration instead.
Quick Reference
| Concept | What it means |
|---|---|
| V1__name.sql | Versioned migration — runs once, in number order |
| R__name.sql | Repeatable migration — re-runs when the file changes |
| Forward-only | Never edit an applied migration; add a new one |
| History table | Records which migrations have run (+ checksums) |
| Rollback | Reverse of a migration; in prod, prefer rolling forward |
| Expand/contract | Add → backfill → switch → drop for zero downtime |
| Backfill | Copy data from the old column into the new one |
| Tool | Migrations written in | Best for |
|---|---|---|
| Flyway | Plain SQL files | Simple, SQL-first migrations |
| Liquibase | XML / YAML / JSON / SQL | Complex, multi-database environments |
| Alembic | Python | SQLAlchemy projects |
| Knex / Prisma | JavaScript / TypeScript | Node.js applications |
Frequently Asked Questions
Q: Why can't I just edit an old migration to fix a typo?
Because it has already run on other people's databases and on production. The tool's checksum would mismatch and refuse to run. Add a new migration that corrects it instead — that keeps every environment reproducible.
Q: Flyway vs. Liquibase — which should I pick?
If your team is comfortable writing plain SQL, Flyway is simpler. If you need database-agnostic changes (the same change description running on Postgres, MySQL, and Oracle) or richer rollback support, Liquibase's XML/YAML format is built for that.
Q: Do I really need expand/contract for a tiny app?
If you can tolerate a few seconds of downtime during a deploy, a direct RENAME COLUMN is fine. Expand/contract matters once you have live traffic that must not see errors while the schema and app are mid-change.
Q: How do migrations stay in sync with the app deploy?
Run the migration step in CI/CD before starting the new app. As long as each migration is backward-compatible (the old app still works against the new schema), the order is safe and you avoid downtime.
Mini-Challenge: Make a NOT NULL Column Safely
Put it all together — a brief, a blank canvas, and the expected outcome in the comments. Write the migrations, then copy them into a playground to confirm.
🎯 Mini-Challenge
Split a NOT NULL column into three safe migrations.
-- 🎯 MINI-CHALLENGE — make a risky change safe.
-- The schema already has: V1 (users), V2 (phone), V3 (bio).
-- A teammate wants users to have a NOT NULL "status" column.
--
-- Adding a NOT NULL column with no default LOCKS and can fail on a big table.
-- Split it into safe, separately-numbered migrations:
-- 1. V4: add status as a NULLABLE column with a sensible default
-- 2. V5: backfill existing rows so none are NULL
-- 3. V6: tighten the column to NOT NULL
--
-- ✅ Expected: three file
...🎉 Lesson Complete
- ✅ Migrations are numbered, forward-only SQL files checked into Git
- ✅ A history table records what ran; checksums stop you editing applied files
- ✅ Versioned (
V__) run once; repeatable (R__) re-run when changed - ✅ In production, roll forward with a new migration rather than dropping data
- ✅ Expand/contract (add → backfill → switch → drop) gives zero-downtime changes
- ✅ CI/CD applies migrations automatically, keeping every environment in sync
- ✅ Next: database design patterns — OLTP vs OLAP and how to model them
Sign up for free to track which lessons you've completed and get learning reminders.