Migration Strategy for Evolving Database Schemas

A migration is a controlled, scripted change to a database schema — adding a column, creating a table, changing a constraint — applied in a specific order so that every environment (a developer's machine, staging, production) ends up with the exact same schema history. Because a production database keeps running and serving traffic while a schema evolves, migration strategy is really about answering one question: how do you change the shape of the data underneath a live application without breaking it?

The core discipline is favoring additive, backward-compatible changes over destructive ones, and sequencing any genuinely breaking change across multiple deploys so the application and the database are never out of sync in a way that causes errors.

Why it matters

A live database cannot be dropped and recreated; its structure has to change while the application keeps running and the data stays safe. Renaming a column in one step breaks every running copy of the code that still uses the old name. Staged, reversible migrations are how teams change schemas without downtime.

Additive Changes First

Adding a new nullable column, adding a new table, or adding a new index are all changes that old application code can safely ignore — they don't break anything currently running. This is why the safe default in schema evolution is to prefer additive changes: introduce the new column or table alongside the old structure, deploy application code that can use it, and only remove or rename the old structure in a later, separate migration once nothing depends on it anymore.

Handling Genuinely Breaking Changes Safely

Some changes are inherently breaking — renaming a column, making a nullable column NOT NULL, dropping a column still read by running code. These need to be split into safe stages instead of a single destructive migration: for a rename, add the new column, backfill it from the old one, deploy application code that writes to both and reads from the new one, then in a later migration drop the old column once no code references it. This staged approach means at every single point in the rollout, both the old and new application code can run against the current schema without errors — important because deployments are rarely instantaneous across every server.

• Safe/additive: add nullable column, add table, add index, add a new constraint that existing data already satisfies.
• Needs staging: rename column/table, change a column's type, make a column NOT NULL, drop a column or table.
• Rule of thumb: a migration is safe if the previous version of the application still runs correctly against the new schema.

Up and Down Migrations, and Migration Tools

Tools like Flyway, Liquibase, and Prisma Migrate manage migrations as an ordered, version-controlled sequence of scripts, each one recorded in a migrations table inside the database itself so the tool always knows exactly which migrations have already run. Each migration conceptually has an "up" (apply the change) and often a "down" (reverse it) — though in practice, rolling forward with a new corrective migration is usually safer in production than rolling back, since a "down" migration can destroy data that was written after the "up" ran. Migrations should be small, single-purpose, and never edited after they have run in any shared environment; a mistake is fixed with a new migration, not by rewriting history.

Example

-- Migration 001: add the new column, additive and safe
ALTER TABLE customers ADD COLUMN full_name VARCHAR(150);

-- Migration 002: backfill existing rows, then have the app write to both
-- columns during the transition (application code change, not shown)
UPDATE customers SET full_name = name WHERE full_name IS NULL;
ALTER TABLE customers ALTER COLUMN full_name SET NOT NULL;

-- Migration 003 (run only after all app instances read/write full_name
-- exclusively, confirmed by deploy, monitoring, and time elapsed):
ALTER TABLE customers DROP COLUMN name;

At every point between these three migrations, both the old application
code (still using "name") and the new application code (using
"full_name") can run without errors, because migration 001 only adds a
column and migration 002 populates it without touching "name". Only
migration 003, run well after the rollout is confirmed complete, removes
the old column.

Exercise

Rename customers.name to full_name in the safe, staged way, as three separate steps: add the new column; copy the existing values into it; and, once nothing uses the old column any more, drop it. Then read the new column.

Expected output: full_name then Asha Rao

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers (id, name) VALUES (1, 'Asha Rao');

-- TODO: migration 1: add the column full_name

-- TODO: migration 2: backfill full_name from name

-- TODO: migration 3 (after the application has switched over): drop the column name

SELECT full_name FROM customers;

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers (id, name) VALUES (1, 'Asha Rao');

ALTER TABLE customers ADD COLUMN full_name TEXT;

UPDATE customers SET full_name = name;

ALTER TABLE customers DROP COLUMN name;

SELECT full_name FROM customers;

Quick quiz

Interview questions