Schema Versioning & Migrations

Reviewed & published by Brayan K

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.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

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.

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.

-- 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 (V2, V3, ...), never back in V1.

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.

-- 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 — 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_history gains a row for version 3.

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.

-- 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 NOW(),
    execution_time INT,                    -- milliseconds
    success        BOOLEAN
);

-- After running V1 and V2 it holds one row each:
SELECT version, description, success
FROM flyway_schema_history
ORDER BY installed_rank;

-- ✅ Result:
--   1 | create users     | true
--   2 | add phone to users | true
-- On the next deploy the tool sees these rows and skips V1 and V2.

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.

-- 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 REPLACE is idempotent —
-- running it twice leaves the same result.

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.

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.

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.

-- 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. Both are kept in sync.)

-- V6 (SWITCH): now the app reads only email_address. The old column is dead.

-- V7 (CONTRACT): once NOTHING uses "email" any more, drop it.
ALTER TABLE users DROP COLUMN email;

-- The golden rule: every step is backward-compatible with the app version
-- currently running, so you can deploy (or roll back) without downtime.

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 — 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 order:
--   1 = ADD COLUMN (expand)   2 = UPDATE backfill
--   3 = switch app reads      4 = DROP COLUMN (contract)

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 TABLE by 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 add NOT NULL in a later migration.
• Rollback silently deleted data: running a "down" DROP COLUMN in production wipes everything users stored there. Fix: roll forward with a new corrective migration instead.

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 — 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 files V4/V5/V6, each runnable on a live table
--    without a long lock, ending with status NOT NULL DEFAULT 'active'.

-- your migrations here

🎉 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

Practice quiz

Continue this course

• Previous: Triggers vs Application Logic — Architectural Best Practices
• Next: Database Design Patterns for Large Systems (OLTP vs OLAP) — Choose between OLTP and OLAP schema designs for your workload
• Quick reference: SQL cheat sheet

Related lessons

• CREATE TABLE
• ALTER TABLE