The Cloudflare D1 PRAGMA foreign_keys trap: how one DROP TABLE nearly wiped a child table

While running a schema migration for my Fitness Tracker I stepped into a big one — it nearly wiped production data. Writing it down, because this behavior is completely different from local SQLite.

Background

The standard SQLite schema-change recipe looks like this:

PRAGMA foreign_keys = OFF;

DROP TABLE profiles;
CREATE TABLE profiles (...);
-- copy the data back from a backup table

PRAGMA foreign_keys = ON;

Turn off foreign key checks, rebuild the parent table, turn them back on. Runs perfectly in local sqlite3.

The problem

Running the same migration on D1, the child table’s data was gone. The sets table references profiles via a profile_id foreign key with ON DELETE CASCADE — the moment DROP TABLE profiles executed, the cascade fired and deleted every related training record.

The reason: D1 ignores PRAGMA foreign_keys = OFF across statements. Each statement executes independently on D1; the PRAGMA you wrote on line one is no longer in effect by the time line two’s DROP TABLE runs. Foreign key checks stayed on the whole time, and so did the cascade.

The whole incident in one picture: ignored PRAGMA → live CASCADE → silently vanishing data

The fix

Don’t rely on PRAGMA. Use migration patterns that never trigger the cascade:

1. Don’t DROP the parent table — prefer ALTER TABLE ... RENAME or additive column changes;
2. If you truly must rebuild, back up the child tables first (CREATE TABLE sets_backup AS SELECT * FROM sets), then restore after the rebuild;
3. Before any migration, take a full export with wrangler d1 export — this should be muscle memory.

Lesson

Passing locally in SQLite ≠ safe on D1. D1’s execution model is per-statement; anything that relies on “session state” (PRAGMAs, temporary settings) cannot be trusted.

wrangler d1 export is now a permanent item on the pre-deploy checklist. Data is priceless.