Schema Migrations | Relational Databases

In this chapter

GreenMart needs to add a genuinely required column to a live table — we see which schema changes SQLite handles directly (ADD COLUMN, RENAME COLUMN), discover it has no ALTER COLUMN at all, and use the real build-copy-swap pattern to add a constraint safely, backfilling first so no existing row is left violating it.

14–17 min

The Problem in Real Life

GreenMart's Customers table has run unchanged since Act 1 — but the support team now needs an email address on file, and Mike wants every customer's phone number to be genuinely required, not just usually filled in. The catch: the store never closes, the database never stops, and existing customer rows already exist with the old shape.

Sarah realizes this is a fundamentally different kind of change than anything so far. Every earlier chapter wrote queries against a schema that already existed. This one has to change the schema itself, live, without breaking a single row already in it.

We're not just querying the schema anymore. We have to change it — without breaking anything already living inside it.

Sarah

Changes That Are Always Safe vs. Ones That Need Real Care

Not every schema change is equally safe

Adding an optional column can never conflict with existing data; tightening a constraint genuinely can.

SQLite has no ALTER COLUMN at all

Changing a column's type or adding a constraint to it isn't just unsupported by SQLite — there's no syntax for it whatsoever.

Backfill has to happen before the constraint does

A NOT NULL constraint can only refuse new bad data — it can't retroactively fix rows that already violate it.

The rebuild pattern is the general-purpose fix

Build the table you actually want, copy the data across, then swap it into place — this handles anything SQLite's ALTER TABLE can't do directly.

Migrating a Live Schema Safely

Some schema changes are always safe, because they can never conflict with data that already exists. Others genuinely can — and SQLite is honest about which is which: it directly supports adding and renaming columns, but has no way at all to change an existing column's type or add a new constraint to it after the fact.

A schema migration is exactly this: a deliberate, ordered set of changes that gets an existing, live database from one schema to another, without losing or corrupting any data already inside it.

Adding a column with no constraint is always safe — every existing row simply gets NULL for it. Nothing about existing data can possibly conflict with a brand-new, optional column.

Always Safe: Adding a New, Nullable Column

ALTER TABLE Customers ADD COLUMN Email TEXT;

Every one of GreenMart's existing customers now has an Email column, currently empty, without a single row needing to change.

SQLite supports renaming a column directly — the data itself doesn't move or change, only the name used to refer to it.

Also Safe: Renaming a Column

ALTER TABLE Customers RENAME COLUMN Phone TO PhoneNumber;

Any query written against the old name Phone would now need updating — but the data underneath never moved.

Unlike adding a new column, tightening a constraint on a column that already exists could genuinely conflict with data already there — some existing customers may not have a phone number on file yet. SQLite doesn't even offer syntax for this; it has to be done a different way entirely.

Not Directly Possible: Adding a Constraint to an Existing Column

-- SQLite has no ALTER COLUMN at all -- this fails immediately:
ALTER TABLE Customers ALTER COLUMN PhoneNumber TEXT NOT NULL;
-- near "ALTER": syntax error

This snippet is shown for reference only — running it produces exactly the syntax error above, so it's left out of the runnable Playground below.

This four-step pattern — backfill, build the table you actually want, copy the data across, then swap it into place — is the real, general-purpose way to make a change SQLite's ALTER TABLE can't do directly.

The Real Way: Build, Copy, Swap

-- Backfill first -- a new constraint can't retroactively fix existing
-- data that would violate it
UPDATE Customers SET PhoneNumber = 'UNKNOWN' WHERE PhoneNumber IS NULL;

-- Build a new table with the schema you actually want
CREATE TABLE Customers_New (
  CustomerID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  PhoneNumber TEXT NOT NULL,
  Email TEXT,
  BalanceDue NUMERIC NOT NULL DEFAULT 0
);

-- Copy every row across
INSERT INTO Customers_New (CustomerID, Name, PhoneNumber, Email, BalanceDue)
SELECT CustomerID, Name, PhoneNumber, Email, BalanceDue FROM Customers;

-- Swap it into place
DROP TABLE Customers;
ALTER TABLE Customers_New RENAME TO Customers;

The constraint isn't a suggestion anymore — it's now a real, structural part of the Customers table, enforced the same way any NOT NULL column always has been.

Notice the order this had to happen in: backfill the bad data first, because a constraint can never retroactively fix rows that already violate it — it can only refuse new ones going forward. Skipping that step would mean the CREATE TABLE ... AS SELECT-style copy failing the moment it hit Maria's still-NULL phone number.

This exact pattern — safe changes done directly, unsafe ones done through a rebuild — is how real schema migrations work in production systems too, often with a migration tool automating the bookkeeping. The underlying idea never changes: never touch a live schema in a way that could leave existing data in an invalid, half-migrated state.

Key Takeaway

SQLite can add or rename a column directly, but it has no way to change a column's type or tighten its constraints after the fact — that always requires building the table you actually want, copying the data across, and swapping it into place, backfilling first so nothing already in the table would violate the new rule.

Why This Matters

Every real application's database schema changes over time, and getting a migration wrong — skipping a backfill, forgetting an index, dropping a column something still depends on — is one of the most common ways production systems suffer real data loss or downtime. Understanding which changes are trivially safe and which need a deliberate, ordered plan is a core operational skill.

GreenMart's Customers table now genuinely requires a phone number on every row, added without losing a single existing customer. The checkpoint ahead brings this together with the previous chapter's sharding plan for one final, real-world task.