Schema migrations

Summary#

There is no migration system, no migrations/ directory and no wrangler d1 migrations step. Schema is created on demand at request time. Every feature owns an ensure…Schema(db) function that runs CREATE TABLE IF NOT EXISTS plus CREATE INDEX IF NOT EXISTS, adds later columns with ALTER TABLE … ADD COLUMN, and swallows the error a duplicate ALTER throws. Handlers call it before their first query.

The upside is that a deploy is only ever a code deploy: there is no ordering problem between shipping the worker and running a migration, and a rollback of the code never leaves the database ahead of it. The downside is that there is no schema version anywhere, drift is invisible, a failed DDL statement is silent, and several hot paths pay DDL round-trips on every request.

Purpose#

The pattern exists because of the deployment shape. This is a Cloudflare Pages project with no build step and a deploy that is a cp loop followed by wrangler pages deploy. There is no place in that pipeline to run a migration step, no ordering guarantee between the migration and the code that needs it, and no way to run one against a preview deployment on a branch.

Creating schema from the code that uses it removes the whole coordination problem. A new table ships with the handler that needs it and appears the first time somebody calls that handler. The rationale is stated in the code at _worker.js:23286-23289:

Architecture#

The canonical shape#

// _worker.js:3484-3491
let __dfsCacheReady = false;
async function ensureDfsCacheSchema(db) {
  await db.prepare('CREATE TABLE IF NOT EXISTS dfs_cache (...)').run().catch(() => null);
  if (!__dfsCacheReady) {
    // Idempotent: this throws once the column already exists, which we ignore.
    await db.prepare('ALTER TABLE dfs_cache ADD COLUMN tool TEXT').run().catch(() => null);
    __dfsCacheReady = true;
  }
}

Three properties define the pattern:

1. Idempotent by construction. CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS are no-ops once the object exists. ALTER TABLE … ADD COLUMN is not idempotent, so it is wrapped in a .catch() or a try/catch and the duplicate-column error is discarded.
2. Called from the handler, not from boot. There is no startup hook in a Worker isolate that can be relied on, so each handler calls the ensure function for the tables it is about to touch.
3. Failure is silent. Almost every ensure* body is try { … } catch (e) { /* exists */ }. That is intentional for the duplicate ALTER, and it is also why a genuinely broken CREATE produces no signal.

Where the schema functions live#

Twenty functions follow the canonical ensure*Schema name:

Fifteen more do the same job under a different name:

And eight tables are created inline, with no function at all:

Components#

Per-isolate memoisation#

Four schema paths cache "already done" in module scope, which means per Worker isolate, reset on every cold start and every deploy:

The blog case is the instructive one. Its comment records that the schema used to run all 18 statements on every call from five places, roughly 36 sequential D1 round trips per request. mvbRunSchema now prefers a single db.batch(...) and falls back to a per-statement loop only if the batch fails.

Self-sweeping schema functions#

Three schema functions do double duty as retention jobs, because they already run on every call:

This is cheap and reliable, but it means those deletes only happen while the feature is being used.

Forward-fill instead of rename#

There is no ALTER TABLE … RENAME and no destructive change anywhere. When the blog needed a different primary key, the answer was a new table plus a one-time forward-fill:

blog_sites      (user_email PRIMARY KEY, ...)     -- v1, kept untouched
blog_sites_v2   (PRIMARY KEY (user_email, site_id)) -- v2
INSERT OR IGNORE INTO blog_sites_v2 SELECT ... FROM blog_sites

The comment states the reason: the v1 table is left in place and its rows are copied forward once, so a rollback to the old worker still works. That is the pattern to copy for any structural change.

Data flow#

What actually happens on a first request after a deploy:

1. A handler runs and calls its ensure*Schema(db).
2. D1 executes CREATE TABLE IF NOT EXISTS. On an existing database this is a no-op that still costs a round trip.
3. Any ALTER TABLE … ADD COLUMN statements run. Each throws duplicate column name on an existing database, and each error is swallowed.
4. If the function has a __ready flag, it is set, and subsequent requests in the same isolate skip straight to the query.
5. The handler runs its real query.

On a brand-new database the same sequence creates the objects for real. A table therefore does not exist until the first request that needs it, which is why almost every read is wrapped in a try/catch that returns an empty result. The in-code comments say so directly: "table may not exist on first call", "blog tables may not exist yet", "table may not exist for new users".

Failure modes#

1. There is no schema version, so drift is invisible#

Nothing records what has been applied. Production may carry extra columns or whole tables from earlier deploys that the current code no longer creates, and there is no way to diff "what the code creates" against "what the database has" without reading the database directly.

Treat the code as a description of the minimum schema, never as a complete description of production.

2. Columns are only ever added#

There is no DROP COLUMN, no RENAME, and no cleanup path. Retired columns stay forever. metrics_hourly.tenant_id and usage_counters.plan are both live examples: declared, defaulted and never used as intended.

3. A failed CREATE is silent#

The same catch that discards a harmless duplicate-ALTER error also discards a genuine failure. If a CREATE TABLE fails for any reason, the handler proceeds to a query against a table that does not exist, that query throws, and the handler's own try/catch returns an empty result. The customer sees an empty screen and there is nothing in the logs saying why.

When you add a table, verify it exists after the first request rather than assuming the ensure function worked.

4. Hot paths pay DDL on every request#

ensureUsageSchema has no __ready flag. It runs two CREATE TABLE IF NOT EXISTS statements plus one ALTER on every quota check, which is essentially every metered request. That is three D1 round trips before any real work happens.

If you are adding an ensure function to a hot path, add a module-scope flag. If you are optimizing, this is the obvious first target.

5. .catch(() => null) hides your typo too#

A CREATE TABLE with a syntax error behaves exactly like a CREATE TABLE that already exists. Test a new schema function against an empty local database before shipping it, because production will not tell you.

6. Rollback leaves the database ahead of the code#

Reverting a commit reverts the code, not the schema. New tables and columns remain. That is usually harmless, and it is the reason the forward-fill pattern above exists, but it means "rolled back" never means "restored".

Rules for adding a table#

1. Write one ensure*Schema(db) function next to the feature that owns it.
2. Use CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS for everything you can, and ALTER TABLE … ADD COLUMN wrapped in a catch for anything added later.
3. Call it at the top of every handler that touches those tables. There is no central place that will do it for you.
4. Add a module-scope __ready flag if the path is hot.
5. Wrap every read in a try/catch that returns an empty result, because the table genuinely may not exist yet.
6. Never rename or drop. Add a new table and forward-fill.
7. Decide the retention story at the same time. Only four tables have automatic cleanup today; the rest grow forever. See the retention section of Data model.
8. Pick a timestamp unit and state it in a comment. The database is already inconsistent between seconds and milliseconds; do not add to that without saying which you chose.