Schema migrations in SQL

Problem

“no such column: phone”

A colleague adds a phone column to customers on their computer and writes the code that shows it. On their computer everything works. The new version goes to the site, and the “Customers” page fails with no such column: phone, because the site’s database has no such column. And a third computer on the team has two columns, because someone there added it by hand, under a different name.

The code is in git, and everyone has the same version. The database schema needs the same: written down, versioned and identical everywhere.

The idea

A migration: a numbered change

A migration is a small SQL file with a number. Every change to the schema is a new file, and the files sit in git next to the code:

migrations/
  001_create_shop.sql
  002_reviews.sql
  003_customers_phone.sql

The database remembers which ones have run in a table, usually schema_migrations. At startup (or when a new version is released) the tool runs, in order, only the files that are not in it yet. This database is at version 2:

SQLdatabase: shop

SELECT version, name, applied_at FROM schema_migrations ORDER BY version;

Migration 3 is the change plus its record, together in one transaction:

SQLdatabase: shop

BEGIN;
ALTER TABLE customers ADD COLUMN phone TEXT;
INSERT INTO schema_migrations VALUES (3, 'customers_phone', '2026-10-25');
COMMIT;

SELECT version, name FROM schema_migrations ORDER BY version;

Run it a second time in the same box and ALTER TABLE stops with “duplicate column name”. That is why the tool checks schema_migrations before it runs a file.

See it

Why in a transaction

If a migration without a transaction stops halfway, the first change stays and the version record is missing. The next attempt starts from the beginning again and trips over its own first step. Here the second statement is a mistake (the same column twice):

SQLdatabase: shop

BEGIN;
ALTER TABLE customers ADD COLUMN phone TEXT;
ALTER TABLE customers ADD COLUMN phone TEXT;
INSERT INTO schema_migrations VALUES (3, 'customers_phone', '2026-10-25');
COMMIT;

The transaction stays open with the first ALTER TABLE, and the COMMIT never reaches the database. Run ROLLBACK; in the box and the phone column disappears: in SQLite and PostgreSQL a schema change is part of the transaction. You fix the file and run it again, and the database is exactly as it was.

Try it

Migration 3: loyalty points

Write migration 3 for the database at version 2: a loyalty_points column in customers (an integer, required, 0 by default) and the record (3, 'loyalty_points', '2026-10-25') in schema_migrations, in one transaction.

SQLdatabase: shop

Solution

BEGIN;
ALTER TABLE customers ADD COLUMN loyalty_points INTEGER NOT NULL DEFAULT 0;
INSERT INTO schema_migrations VALUES (3, 'loyalty_points', '2026-10-25');
COMMIT;

The idea

The tools

Nobody writes the “which migration has run” check by hand. Every language has a tool that keeps the version table and runs the files in order:

Java: Flyway or Liquibase (V3__customers_phone.sql);
C#: EF Core Migrations (dotnet ef migrations add CustomersPhone) writes the migration from the change in the classes;
Python: Alembic (for SQLAlchemy) or Django migrations;
JavaScript: Prisma Migrate, Knex or node-pg-migrate.

Some also write a reverse step (down) that undoes the migration. On a site with real data, going back is rarely a DROP COLUMN: if the new column already holds data, the reverse step loses it. So “back” is usually a new migration forward.

See it

Changing a live database: in steps

The site does not stop while the new version is being deployed: for a while both the old and the new code are running. If the migration renames city to town, the old code fails that very second. So the change is made in three steps:

Expand. Add town and fill it from city. The old code does not notice it.
Migrate. The new code writes to both and reads town. You wait until the old code is gone everywhere.
Contract. A separate, later migration removes city.

SQLdatabase: shop

ALTER TABLE customers ADD COLUMN town TEXT;
UPDATE customers SET town = city;

SELECT name, city, town FROM customers ORDER BY id LIMIT 4;

Exercise

First and last name apart

The shop wants to greet customers by their first name (“Hello, Maria”). Do the first step (expand): add the columns first_name and last_name to customers and fill them from name (every name is two words separated by a space). The name column stays.

SQLdatabase: shop

Solution

ALTER TABLE customers ADD COLUMN first_name TEXT;
ALTER TABLE customers ADD COLUMN last_name TEXT;
UPDATE customers
SET first_name = substr(name, 1, instr(name, ' ') - 1),
    last_name = substr(name, instr(name, ' ') + 1);

Test

Check yourself

1. Migration 5 has already run on the site. It has a mistake in a column name. What do you do?Write migration 6, which fixes the nameDelete the schema_migrations tableFix the file of migration 5

2. Why does a migration run in a transaction?To make it fasterIt does not need toSo that if it stops halfway, the database is not left half-changed with no record of the version

3. The site runs without stopping, and the city column has to become town. What is the safe order?Add town and fill it, release the code that reads town, and only then remove cityRENAME COLUMN right awayRemove city, then add town

Schema migrations

Cookies