Schema Versioning

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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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 .

The single most important rule is highlighted below — break it and your environments drift apart.

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.

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.

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.

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 .

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.

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.

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.

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.

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.

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary