Schema and Migrations — Change the Database in Numbered Steps
Hoplink's links table needs a new title column. You could open the dashboard and run ALTER TABLE by hand. It works — on that one database. Your teammate's local copy, the preview database and the next new environment know nothing about it, and three weeks later nobody remembers exactly what was run where.
The rule
Every schema change is a numbered file, applied once, in order, everywhere.
A migration is a SQL file describing one change. Wrangler creates them in a migrations/ folder with an increasing number:
npx wrangler d1 migrations create hop-db create_links
# → migrations/0001_create_links.sql-- migrations/0001_create_links.sql
CREATE TABLE links (
code TEXT PRIMARY KEY,
url TEXT NOT NULL,
owner TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);Later changes get their own file, never an edit to an old one:
-- migrations/0002_add_title.sql
ALTER TABLE links ADD COLUMN title TEXT;You apply them with one command, choosing the target:
npx wrangler d1 migrations list hop-db --local # what is still pending?
npx wrangler d1 migrations apply hop-db --local # your machine
npx wrangler d1 migrations apply hop-db --remote # the real databaseD1 records each applied file in a tracking table (d1_migrations by default). Running apply again only runs files it has not seen, so every environment ends up with the same schema by walking the same numbered steps.
There is still a place for one-off commands. wrangler d1 execute hop-db --remote --command "SELECT COUNT(*) FROM links" is perfect for looking at data. The rule of thumb: reading and investigating can be one-off; anything that changes the shape of the database goes into a migration.
The schema — the collection of tables, columns and constraints — is therefore not something you edit; it is the sum of all migrations. Reading the files in order tells the full history of the database.
The shortcut
Before writing a migration, run the old-code test: "If the new schema is live but the old Worker is still running, does anything break?" Deploys are not instant everywhere, so for a short time both versions meet the same database.
Create the file
wrangler d1 migrations create hop-db add_title→0002_add_title.sql.
Traps: editing 0001_...sql after it has been applied (environments that already ran it never see the edit), and adding a NOT NULL column without a default, which SQLite rejects outright.
Drills
Answer fast — trust the rule.
Migration 0001 is already applied everywhere, and you need a new column. What do you do?
How does D1 know which migrations a database still needs?
Which command updates the real, deployed database?
Why does ALTER TABLE links ADD COLUMN owner_email TEXT NOT NULL; fail?
Remember
- Every schema change is a numbered file, applied once, in order, everywhere.
- Shortcut: the old-code test — prefer additive changes old code survives.
- Trap: editing applied migrations and NOT NULL columns with no default.