D1 Migrations for a Small Product: Additive Changes and a Local-First Workflow

An additive migration is a database change that only adds: a new table, or a new column with a default. It never drops, renames, or rewrites anything the running code reads. Our D1 migrations follow that one rule, and it makes deploys forgiving, because old code and new schema can overlap safely.

A small product doesn’t need a migration framework, but it does need a rule for changing the database without breaking the code that is already running. Our D1 migrations are a short list of numbered SQL files. This note describes the rule, the local-first workflow around it, and a few schema habits that have saved us trouble. Everything below was run with SQLite 3.45.1 (Python’s sqlite3) and wrangler 4.147.0 against a local D1 database. We did not run anything against a remote database.

The files

Migrations live in a migrations/ directory and are numbered in the order they were written. Ours so far are 0001_projects.sql, 0002_project_workspace.sql and 0003_ai_usage.sql. Wrangler applies them in order and records which have run, so each file executes once per database.

Each file starts with comments saying what the change is for. Someone reading the history months later can tell why a table exists without reconstructing it from code.

What does the additive rule allow?

A migration may create a table or add a column. It may not drop, rename, or rewrite anything that existing code reads.

-- Track an optional internal note for each project.
ALTER TABLE projects ADD COLUMN notes TEXT NOT NULL DEFAULT '';

-- A new table for a new feature. Safe to re-run.
CREATE TABLE IF NOT EXISTS project_files (
  id         INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL,
  name       TEXT    NOT NULL
);

The examples are illustrative, but the shape is the one we use. CREATE TABLE IF NOT EXISTS makes a table creation harmless if it runs twice. ALTER TABLE ... ADD COLUMN has no such guard in SQLite, which is one reason the migration tracking matters. When we add a column, we give it a sensible default so existing rows are valid immediately and older code that never mentions the column keeps working.

If a change really does need something destructive, it does not go in a migration. It becomes its own deliberate step, after the code that depended on the old shape has been gone for a while.

Which changes break old code?

We took a table, applied each kind of change, and then ran the statements old code would run: a SELECT of the original columns and an INSERT that does not mention the new ones.

import sqlite3
print("sqlite", sqlite3.sqlite_version)
BASE = "CREATE TABLE projects (id INTEGER PRIMARY KEY, name TEXT NOT NULL, budget_cents INTEGER NOT NULL DEFAULT 0); INSERT INTO projects (name, budget_cents) VALUES ('seed', 100);"
OLD_CODE = [
  "SELECT id, name, budget_cents FROM projects",
  "INSERT INTO projects (name, budget_cents) VALUES ('from old code', 5)",
]
CHANGES = [
  ("add column, NOT NULL DEFAULT ''",  "ALTER TABLE projects ADD COLUMN notes TEXT NOT NULL DEFAULT ''"),
  ("add column, nullable",             "ALTER TABLE projects ADD COLUMN notes TEXT"),
  ("add column, NOT NULL no default",  "ALTER TABLE projects ADD COLUMN notes TEXT NOT NULL"),
  ("create table IF NOT EXISTS",       "CREATE TABLE IF NOT EXISTS project_files (id INTEGER PRIMARY KEY, project_id INTEGER NOT NULL)"),
  ("rename column",                    "ALTER TABLE projects RENAME COLUMN budget_cents TO budget"),
  ("drop column",                      "ALTER TABLE projects DROP COLUMN budget_cents"),
  ("rename table",                     "ALTER TABLE projects RENAME TO jobs"),
]
for label, ddl in CHANGES:
    db = sqlite3.connect(":memory:")
    db.executescript(BASE)
    try:
        db.execute(ddl)
    except sqlite3.Error as e:
        print(f"{label:34} migration FAILED: {e}"); continue
    results = []
    for stmt in OLD_CODE:
        try: db.execute(stmt).fetchall(); results.append("ok")
        except sqlite3.Error as e: results.append(f"ERR {e}")
    print(f"{label:34} old code after migration: {' | '.join(results)}")

sqlite 3.45.1
add column, NOT NULL DEFAULT ''    old code after migration: ok | ok
add column, nullable               old code after migration: ok | ok
add column, NOT NULL no default    migration FAILED: Cannot add a NOT NULL column with default value NULL
create table IF NOT EXISTS         old code after migration: ok | ok
rename column                      old code after migration: ERR no such column: budget_cents | ERR table projects has no column named budget_cents
drop column                        old code after migration: ERR no such column: budget_cents | ERR table projects has no column named budget_cents
rename table                       old code after migration: ERR no such table: projects | ERR no such table: projects

Three results matter. Additive changes leave old code untouched. Renames and drops break both reads and writes the moment they run. And a NOT NULL column with no default is refused outright on a table that already has rows, which is why every column we add carries a default.

Why additive makes deploy order forgiving

Code and schema never ship at exactly the same moment. If a migration only adds, then old code running against the new schema still works, because everything it relies on is still there. That means we can apply the migration first, deploy the code second, and not worry about the gap between them. Rolling the code back is also safe, since the schema is a superset of what the older version needs.

A migration that renames a column breaks one of those two orderings no matter which you choose. Here is the add-a-column case in both orders:

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("CREATE TABLE projects (id INTEGER PRIMARY KEY, name TEXT NOT NULL);")
NEW_CODE = "INSERT INTO projects (name, notes) VALUES ('x', 'hello')"
print("order A, code first, migration second:")
try: db.execute(NEW_CODE)
except sqlite3.Error as e: print("  new code before migration ->", e)
print("order B, migration first, code second:")
db.execute("ALTER TABLE projects ADD COLUMN notes TEXT NOT NULL DEFAULT ''")
db.execute("INSERT INTO projects (name) VALUES ('written by old code')")   # old code still running
db.execute(NEW_CODE)
print("  rows:", db.execute("SELECT id, name, notes FROM projects").fetchall())

order A, code first, migration second:
  new code before migration -> table projects has no column named notes
order B, migration first, code second:
  rows: [(1, 'written by old code', ''), (2, 'x', 'hello')]

 migration first, then code        code first, then migration
 ---------------------------       ---------------------------
 old code + old schema   ok        old code + old schema   ok
 old code + new schema   ok        new code + old schema   FAIL
 new code + new schema   ok        new code + new schema   ok

Shipping the migration first means the only combination that ever exists in the gap is old code on the new schema, which the additive rule keeps working.

How do you run D1 migrations locally first?

We apply every migration to a local database before it touches production:

wrangler d1 migrations apply <database> --local
wrangler d1 migrations apply <database> --remote

For local runs we use wrangler pages dev dist. It keeps a local SQLite file under .wrangler/state and reads the DB binding from wrangler.jsonc, so the site runs against the migrated local database with no extra configuration. If a migration has a typo, we find out on a throwaway file, not on production data.

Wrangler records what it has applied in a d1_migrations table. On our local database its definition was:

CREATE TABLE "d1_migrations"(
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT UNIQUE,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
)

Tracking is by file name. That detail drives the next section.

Why must you never edit an applied migration?

Once a migration has been applied anywhere that matters, it is history. If it was wrong, we write a new migration that corrects it. Editing the old file leaves environments disagreeing about what that number did, and the tracking table cannot tell. The cost of a new file is a few lines. The cost of drift between databases is much harder to see.

We reproduced both failure modes with a scratch project containing the wrangler.jsonc binding and two migration files, 0001_projects.sql (creates projects) and 0002_project_notes.sql (adds notes), then ran wrangler d1 migrations apply demo-db --local and inspected the result. The commands were:

# apply, apply again, then append a column to the already-applied 0002 and apply
wrangler d1 migrations apply demo-db --local
wrangler d1 migrations apply demo-db --local
echo "ALTER TABLE projects ADD COLUMN owner TEXT NOT NULL DEFAULT '';" >> migrations/0002_project_notes.sql
wrangler d1 migrations apply demo-db --local
# then a new 0003 whose second statement repeats an existing column
wrangler d1 migrations apply demo-db --local
# then fix 0003 (keep only the first statement) and apply again
wrangler d1 migrations apply demo-db --local

We trimmed wrangler’s output to the lines that matter, and read the schema with PRAGMA table_info after each step:

$ apply (first time)
2 commands executed successfully.
2 commands executed successfully.
columns: ['id', 'name', 'budget_cents', 'notes']
applied: ['0001_projects.sql', '0002_project_notes.sql']
$ apply (again)
No migrations to apply!
$ edit applied 0002, apply
No migrations to apply!
columns: ['id', 'name', 'budget_cents', 'notes']
$ apply 0003 (second statement is a duplicate)
[ERROR] duplicate column name: notes: SQLITE_ERROR
columns: ['id', 'name', 'budget_cents', 'notes']
applied: ['0001_projects.sql', '0002_project_notes.sql']
$ fix 0003, apply
2 commands executed successfully.
columns: ['id', 'name', 'budget_cents', 'notes', 'status']
applied: ['0001_projects.sql', '0002_project_notes.sql', '0003_status.sql']

Failure modes

Edited history is silently ignored. The owner column we appended to 0002 never appeared, and wrangler said there was nothing to do. A teammate who creates a fresh database from the edited file would get a different schema than everyone else.
A repeated ADD COLUMN fails loudly. duplicate column name: notes is SQLite telling you the guard does not exist.
A failed multi-statement file left no partial change locally. In 0003, the first statement (add status) was valid and the second failed. Afterwards status was absent and 0003_status.sql was not recorded, so fixing the file and re-running worked. That is what we observed against a local database; we did not test remote, so check before you rely on it there.
A new NOT NULL column without a default is rejected by SQLite on a table with existing rows, as the compatibility table showed.

Schema habits

Two small conventions keep the columns boring:

Lists as JSON text. When a record holds a short list, we store it as JSON in a TEXT column. For data we read as a whole and don’t query inside, that is simpler than a join table.
Money as integer cents. Amounts are stored as whole numbers of cents, never as floating-point dollars, so arithmetic stays exact.

import sqlite3
db = sqlite3.connect(":memory:")
print("sqlite", sqlite3.sqlite_version)
print("REAL dollars: 0.1 + 0.2 =", db.execute("SELECT 0.1 + 0.2").fetchone()[0], "| equals 0.3?", bool(db.execute("SELECT 0.1 + 0.2 = 0.3").fetchone()[0]))
print("INTEGER cents: 10 + 20 =", db.execute("SELECT 10 + 20").fetchone()[0], "| equals 30?", bool(db.execute("SELECT 10 + 20 = 30").fetchone()[0]))
db.execute("CREATE TABLE t (tags TEXT)")
db.executemany("INSERT INTO t VALUES (?)", [('["a","b"]',), ('["b"]',), ('[]',)])
print("rows tagged 'b' via json_each:", db.execute("SELECT COUNT(*) FROM t, json_each(t.tags) WHERE json_each.value = 'b'").fetchone()[0])

sqlite 3.45.1
REAL dollars: 0.1 + 0.2 = 0.30000000000000004 | equals 0.3? False
INTEGER cents: 10 + 20 = 30 | equals 30? True
rows tagged 'b' via json_each: 2

The last line is the escape hatch: if you do need to look inside a JSON list later, SQLite’s json_each can, without a schema change.

When this doesn’t apply

The additive rule trades a bit of schema tidiness for safe deploys. Over time you accumulate columns nobody reads. For a small product that is a fine price, and a cleanup can be planned as its own piece of work. A team with many services reading the same database, or very large tables, needs more machinery than this. A one-database product with a single deploy path usually doesn’t.

Rate-limiting an AI endpoint with a single D1 table
Structuring an Astro Static Site with Cloudflare Pages Functions
A Runbook for a One-Person Operation: What to Write Down First

Frequently asked questions

What is an additive database migration?

An additive migration only adds: new tables, or new columns that have a default or allow NULL. It never drops, renames, or rewrites anything existing code reads. Because every old query still finds the columns it expects, the migration can run before, during, or after a code deploy without breaking the running version.

Which SQLite ALTER TABLE statements break code that is already running?

In our test on SQLite 3.45.1, RENAME COLUMN, DROP COLUMN and RENAME TO all made the old code's SELECT and INSERT fail with no such column or no such table. Adding a column with NOT NULL DEFAULT, adding a nullable column, and CREATE TABLE IF NOT EXISTS left old code working.

How do you apply D1 migrations locally before production?

Run wrangler d1 migrations apply with the database name and the --local flag, which applies the numbered SQL files to a SQLite file under .wrangler/state. Once the site runs correctly against it, run the same command with --remote. A typo then surfaces on a throwaway database instead of production data.

What happens if you edit a D1 migration after it has been applied?

Nothing, and that is the danger. In our local test with wrangler 4.147.0, appending a column to an applied file was ignored: wrangler reported no migrations to apply and the column never appeared. It tracks applied migrations by file name, so write a new numbered migration instead.

Why does ALTER TABLE ADD COLUMN fail when you run it twice?

SQLite has no IF NOT EXISTS guard for ADD COLUMN, so repeating it fails with 'duplicate column name'. CREATE TABLE IF NOT EXISTS is safe to repeat, but ADD COLUMN is not. That is one reason to rely on the migration tracking table so each numbered file runs once per database.