Schemas Change: ALTER and Migrations

The Marquee starts selling snack vouchers, and suddenly the schema — which this chapter has treated as the product of careful, finished thought — needs something it doesn't have: a place to store each customer's voucher balance. The four tables were designed well, and the business changed anyway. It always does. Data outlives every plan made for it, and this final page of the chapter is about changing a schema's shape while the data goes on living inside it.

The right picture is renovating an occupied house. You can absolutely add a room while the family lives there — builders do it every day — but the order of operations is the entire craft: walls go up before the old roof comes off, and nobody knocks out a load-bearing wall because it "looks unused". One command and one discipline carry that craft into databases: ALTER TABLE, and the practice called migrations. Analogy mapped; real terms from here.

ALTER's Everyday Moves

ALTER TABLE changes the shape of a live table. Its most common move, by far, is adding a column:

ALTER TABLE customers ADD COLUMN voucher_balance INTEGER DEFAULT 0;

The immediate question is the right one: what about the 800 customers already in the table? They were inserted before voucher_balance existed and never supplied a value for it. That is what the DEFAULT 0 is doing there — every existing row gets a zero balance, every future row starts at zero unless told otherwise, and the table never passes through a state where the column holds nonsense. Without a default, the existing rows would simply hold NULL: honest "unknown", but rarely what a balance should say.

Two more moves complete the everyday set. RENAME changes a column's or table's name — mechanically trivial, and we will see in a moment why it is anything but trivial in practice. And DROP COLUMN removes a column, which deserves a genuine pause: the column's data goes with it, immediately and completely. DELETE removes rows you chose with a WHERE; DROP COLUMN removes a fact from every row at once, and no WHERE softens it.

Existing Rows Are the Whole Problem

Everything interesting about schema change comes down to one fact: the table is not empty. Suppose Lora decides voucher balances must always be present — a NOT NULL column. A brand-new table could just declare it. But you cannot slap NOT NULL onto a column where 800 existing rows might hold NULL: the database checks, finds violations, and refuses. The rule would be a lie about the data already there.

So the professionals do it as a dance, in three steps, each one leaving the table in an honest state:

-- Step 1: ADD, nullable — the column exists, old rows hold NULL for now
ALTER TABLE customers ADD COLUMN voucher_balance INTEGER;

-- Step 2: BACKFILL — give every existing row an honest value
UPDATE customers SET voucher_balance = 0 WHERE voucher_balance IS NULL;

-- Step 3: TIGHTEN — now the rule is true, so it may be declared
ALTER TABLE customers ALTER COLUMN voucher_balance SET NOT NULL;

Read the shape of it: make the change possible, make the data comply, then declare the rule. Step three only succeeds because step two made it true — the constraint arrives after the facts, never before. One engine-honesty note: the spelling of step three above is PostgreSQL's, and SQLite makes you rebuild the table to tighten a column, but the dance itself — add nullable, backfill, tighten — is universal choreography you will see on every team.

Migrations: Change as a Paper Trail

Now zoom out from one change to a living project. The Marquee's database exists in more than one copy: the real one behind the website, and the developer's practice copy on a laptop. If schema changes happen by someone typing ALTER at whichever copy is nearby, the copies drift apart within a week — a column here that doesn't exist there, and queries that work in one place and fail in the other.

The industry's answer is the migration: every schema change saved as a small, numbered script — 014_add_voucher_balance.sql — applied strictly in order, never edited after the fact. Any copy of the database, old or brand new, replays the numbered history and arrives at exactly the same shape. The schema stops being "whatever the tables happen to look like" and becomes something with a biography you can read. Tools like Flyway and Alembic exist to run these histories automatically; the names are all you need today — the idea is the numbered scripts.

What Doesn't Change Lightly

The renovation has load-bearing walls. RENAME is the deceptive one: the database renames a column in an instant, and every query, report, and app that spelled the old name breaks in the same instant. A name in a schema is a contract with everyone who ever read it, so renames need coordination measured by readers, not by the speed of the command.

And one wall never moves: a primary key's values never change meaning. Topic 07 promised that keys are stable, and here is the promise with consequences — customer_id 214 is Anna's number forever, because bookings point at it, exports carry it, and nothing that was ever written down un-learns it. Some doors close behind you the moment data walks through them. Design the keys boring, and this wall never bothers you; that was the point of boring all along.