Adding .default(false) to a Drizzle schema deleted my production database. Postmortem.

So last Friday night I shipped what looked like the most boring schema change of my career. One line, one default value, a migration that drizzle-kit generated for me without complaining. I clicked the button. Five minutes later most of my database was gone.

If you run Drizzle on top of SQLite or Cloudflare D1 and any of your tables use ON DELETE CASCADE, this post is for you.

TL;DR: There is no in-framework safe way to apply migrations that trigger an SQLite table rebuild. The fix everyone references on the internet doesn't work for this case, and the only thing that actually preserves data requires you to bypass both drizzle-kit and wrangler entirely. I built a reproduction repo so you can confirm it yourself in two minutes. Repo and full matrix are at the end.

The original incident

Short version

I'm on Nuxt + Cloudflare D1 + Drizzle + Better Auth. The app is an open-source, self-hosted AI chat where users can bring their API keys (BYOK) to use multi-providers and different models. The schema has users at the top with a half-dozen child tables hanging off it via ON DELETE CASCADE—chats, messages, accounts, sessions, and the usual auth/app surface. Standard stuff.

A real user reported an auth issue to me. I delegated that responsibility to Claude. The fix was for Better Auth functionality. When Claude checked the docs, the auto-generated auth schema generated by the better auth CLI, and my actual schema, he discovered that I had forgotten to add the .default(false) to my emailVerified column in the users table. It was merely a suggestion. I don't blame Claude because I was responsible for the remaining actions.

I added the suggested part. Ran drizzle-kit generate. Got back a migration file that I skimmed and approved. It contained the SQLite "twelve-step" table rebuild—PRAGMA foreign_keys=OFF, create new table, copy data, drop the old table, and rename, PRAGMA foreign_keys=ON. I'd seen the pattern before. It looked fine. It is, in fact, what the SQLite docs tell you to do.

I looked into it locally. I created a PR, applied it to preview via GitHub Actions on CI, and manually tested the preview instance worker. Then I merged PR and production. Since the PR was about the authentication fix, I manually tested the user's steps on all environments in the same way: sign out, sign in, create a new chat, be redirected to the new chat dynamic slug page, and receive an AI response. I didn't go through the history page with all the recent chats because it was unrelated to the actual fix. Nothing seemed out of place.

Recovery was D1 Time Travel—binary-searching bookmarks by timestamp until row counts came back. Took about 5 min per database via Claude. I lived. The story is in the postmortem besidka/docs/postmortems/2026-05-15-postmortem-d1-cascade-wipe.md . I figured the lesson was "PRAGMA foreign_keys=OFF doesn't survive D1's per-statement HTTP transport; watch your migrations." Closed the laptop.

Then on Sunday I figured I should actually prove it.

Root cause—the schema change forced a table rebuild

SQLite cannot ALTER TABLE ... ALTER COLUMN ... SET DEFAULT. The only way to change a column default in SQLite is to rebuild the entire table. Drizzle automates this with the standard SQLite "12-step" pattern:

-- Step 1: try to suppress cascades
PRAGMA foreign_keys=OFF;

-- Step 2: new table with new schema
CREATE TABLE __new_users (...);

-- Step 3: copy data over
INSERT INTO __new_users SELECT ... FROM users;

-- Step 4: destroy old table  <- BOMB
DROP TABLE users;

-- Step 5: swap names
ALTER TABLE __new_users RENAME TO users;

-- Step 6: re-enable
PRAGMA foreign_keys=ON;

The same pattern is forced by ANY of these schema changes in SQLite:

• Adding / changing / removing a column DEFAULT
• Changing a column type, nullability, or constraint
• Renaming or dropping a column
• Changing a primary key
• Changing or removing a foreign key

So a single innocuous-looking .default(false) triggered the same SQL pattern that "remove a column" would.

What is PRAGMA, and why didn't it save me?

PRAGMA is an SQLite-specific command that controls engine settings. Examples:

• PRAGMA foreign_keys = ON|OFF — toggle foreign key enforcement (and therefore CASCADE behavior)
• PRAGMA journal_mode = WAL — set write-ahead-log mode
• PRAGMA synchronous = NORMAL — control durability

The catch: PRAGMA foreign_keys is a per-connection setting. It only applies within the same connection and only persists for the duration of that connection.

I guessed that in vanilla SQLite (e.g., better-sqlite3, a single embedded file), the whole migration runs in one connection, so PRAGMA foreign_keys=OFF at the top applies to the DROP TABLE four statements later. Cascades suppressed. Safe.

In Cloudflare D1, the migration file is split on --> statement-breakpoint markers and each statement is executed as a separate HTTP API call to D1's edge service. Whether those calls share a connection is implementation-defined and not guaranteed. The PRAGMA foreign_keys=OFF in statement 1 effectively had no effect on statement 4 (DROP TABLE users). When D1 executed DROP TABLE, foreign keys were enabled, and the cascade fired across every child table referencing users.

This is the load-bearing fact: D1's transport boundary breaks the PRAGMA contract that the SQLite 12-step rebuild pattern depends on for safety.

Why did users survive, but everything else die?

The most counterintuitive part. Look at the order:

-- Empty new table:
CREATE TABLE __new_users (...);

-- Users data is NOW also in __new_users
INSERT INTO __new_users SELECT * FROM users;

-- Cascade kills children, but __new_users is untouched
DROP TABLE users;

-- __new_users becomes users
ALTER TABLE __new_users RENAME TO users;

By the time DROP TABLE users ran, the user rows had already been copied into __new_users. So when the original users table was dropped:

• The rows in users were "gone"—but the same rows still existed in __new_users.
• Every cascade-child table received DELETE FROM <child> WHERE user_id IN (SELECT id FROM users) — those DELETEs ran against the rows in the table being dropped, fired before the drop completed. All children were wiped.
• The rename in step 5 brought users data back under the name users, but the children that cascade-deleted are not recovered—there's no equivalent backup of them anywhere in the migration.

Users data lived because it was copied. Everything else died because the cascade fired on the table being dropped, with no copy step protecting it.

Could this happen on Postgres?

I've heard a lot that SQLite DBs aren't for production. The reason I decided to stick with it is Cloudflare infrastructure. My app is deployed as a serverless Cloudflare Worker, file storage is a Cloudflare R2 bucket, and the DB is D1—also SQLite under the hood. When deciding on the DB, I wanted to stay within CF infra: Worker↔D1 calls go through bindings over Cloudflare's internal network instead of public HTTPS, and with read replication, replicas can sit close to the worker. You've probably already set up a DB and app with Docker and Nginx on a VPS—my setup wins on simplicity: no server to maintain, no connection pool to manage, and compute, DB, and storage all speak the same platform through bindings.

So Sunday was an ideal day to determine whether this is a D1 issue, an SQLite architectural issue, or if other database types have similar issues.

The reproduction

I scaffolded a tiny repo : three identical-ish schemas (SQLite, MySQL, and Postgres), each with users, chats(user_id CASCADE), messages(chat_id CASCADE). Step one creates the schema, applies the initial migration, and seeds 3 users + 6 chats + 18 messages. Step two adds .default(false) to a column on users and applies that. Verify counts at the end. If the cascade fires, children go to zero.

Started simple: just drizzle-kit migrate on local SQLite. That should be the "safe" path, right? Single connection, embedded better-sqlite3, no D1 transport in the picture. Different bug, different scenario, postmortem confirmed.

[verify] sqlite     users=3 chats=0 messages=0

Oh.

OK, so the bug is not D1-specific. There's a drizzle-kit. Local SQLite with better-sqlite3. No HTTP anywhere. Cascade still fires.

Why? Because PRAGMA foreign_keys=OFF is silently ignored when you set it inside an open transaction. SQLite docs say so plainly, in a paragraph that I had also definitely never read. And drizzle-kit's migrator wraps each migration file in a transaction. So the very first statement of the rebuild—the one whose entire purpose is to prevent the cascade—is a no-op.

The migration script then proceeds to drop the parent table. FK enforcement is on the whole time; cascade fires, children die. The PRAGMA foreign_keys=ON at the bottom is the SQL equivalent of locking your front door after you've already burgled the house.

This explains D1 too, just via a different mechanism. D1's transport sends each statement as a separate HTTP call, so even outside a transaction the PRAGMA never reaches the DROP statement because they're on different connections. Two completely different bugs, same fatal outcome.

"Just use defer_foreign_keys"

So I started clicking around. The NuxtHub docs reference Cloudflare's defer-foreign-keys page , which suggests PRAGMA defer_foreign_keys = on as the workaround for FK weirdness inside D1 migrations. The fix sounds obvious: have the migration use defer_foreign_keys = on instead of foreign_keys = OFF. Then it works inside transactions. Done, ship it. Why are we still talking?!

So I wrote a one-line patcher: read the latest migration file, regex-replace the PRAGMA, run the patched SQL through whatever applier you want, and restore the original file afterward so git stays clean. Added test commands for each apply path. Reset, run, verify.

[verify] sqlite     users=3 chats=0 messages=0

Tried wrangler d1 migrations apply --local. Same. Tried wrangler d1 execute --file --local. Same. Even tried wrapping the whole thing in an explicit BEGIN; PRAGMA defer_foreign_keys = on; ... COMMIT; via better-sqlite3, no framework in the picture. Same.

I sat with that for a minute. Then I went back and actually read the SQLite documentation on defer_foreign_keys, like a person who learns from his mistakes might. And there in the second paragraph it explains that defer_foreign_keys defers FK constraint validation—i.e., it lets you temporarily violate a constraint as long as you fix it before COMMIT. It does not, and was never intended to, defer cascade actions. ON DELETE CASCADE fires the moment the parent row goes away. No PRAGMA stops that except foreign_keys = OFF, which can only be set outside a transaction, which is exactly what you can't do inside any of the appliers we care about.

The Cloudflare doc and the NuxtHub doc are both correct for their actual use case (constraint validation ordering during seed-style operations). They just don't help here, and the issue title alone misleads you into thinking they do. The Drizzle issue referencing them inherits the same misunderstanding.

The bug is unfixable at the PRAGMA layer. There is no magic word.

What actually works (and why it doesn't matter)

For completeness, I built one more variant. Single better-sqlite3 connection, single db.exec() call on the entire migration file content, no transaction, no per-statement splitting:

const conn = new Database(file);

conn.exec(migrationFileContent); // all 6 statements, one call

conn.close();

Finally!

[verify] sqlite     users=3 chats=6 messages=18

Data lives. Because db.exec() doesn't wrap in BEGIN/COMMIT, better-sqlite3's connection defaults to FK off, the DROP doesn't cascade, the rename completes, and the trailing PRAGMA foreign_keys=ON puts everything back the way it was. The migration as Drizzle generated it is correct. It's the appliers that break it.

This is also completely useless in production:

• It's a hand-rolled script, not a drizzle-kit or wrangler feature.
• It bypasses drizzle's migration tracking (__drizzle_migrations). I had to manually insert hashes to keep the state consistent.
• No atomic rollback. If statement 3 fails, you have partial state and a sad on-call.
• It does not work on D1. The HTTP API has no "send file as one batch" endpoint. Every wrangler path splits.

So the working solution exists in the sense that a unicycle exists. You can technically get to work on it. You will not.

The full matrix

I wrapped the whole thing in pnpm matrix so anyone can reproduce it in two minutes. Here's what it prints:

Nine out of ten paths lose data. The tenth requires you to bypass the framework. MySQL and Postgres don't care because their ALTER COLUMN ... SET DEFAULT is a one-statement in-place metadata change—no rebuild, no DROP, no cascade.

What you should actually do

The repo demonstrates the bug. It does not solve it. There is no clever script you can adopt that makes this safe through drizzle-kit or wrangler today. The strategies that actually work all live above the framework:

• Don't trigger the rebuild in the first place. Drizzle has $defaultFn(() => false) that sets the value at insert time in JavaScript instead of in SQL. No ALTER COLUMN, no rebuild, no migration risk. For SQLite-backed apps with cascade children, this should be your default policy. Treat SQL-level defaults as a privilege earned on tables without FK relationships.
• The same advice extends further than you'd expect, because the rebuild is forced by any of the following: adding or removing a default, changing a column type, changing nullability, changing or removing a foreign key, renaming a column, or changing the primary key. The list of "safe" schema changes on SQLite tables with cascade children is approximately adding a brand new column with no default and no constraints, and that's basically it.
• Grep every generated SQLite migration for DROP TABLE before applying. Make it a pre-commit hook, a CI check, or a sticky note on your monitor. If DROP TABLE appears on a table with FK children, that migration is destructive until proven otherwise. In production, that means cross-referencing it with your full schema. In a small project, the grep alone catches everything.
• On D1 specifically, take a Time Travel bookmark before every --remote apply. It's wrangler d1 time-travel info <db>, the bookmark that is one paste, and it's the difference between a 30-second mistake and a 30-minute recovery. Save it in the same commit message as the deploy. The dashboard does not surface this feature; if you don't know about it, you'll find out the hard way.
• If you really must do a rebuild on a table with cascade children, write the migration by hand: drop the FK constraints first, do the rebuild, and re-add the FK constraints. Tedious. Boring. Not the thing drizzle-kit generates for you. But the only way to actually be safe is through the existing appliers.

Closing thought

The thing that bothers me most about this bug is how respectable each individual piece of it looks. Drizzle generates the SQL pattern that the SQLite docs themselves recommend. Drizzle-kit wraps migrations in transactions for atomicity, which is correct in 99% of cases. D1 splits statements over HTTP because that's how you build a distributed SQL service. SQLite's PRAGMA foreign_keys has its specific scope rules because changing FK semantics mid-transaction would itself be a footgun. Git hooks on commit and push. Codex review on PR open. Apply migrations on CI. Different workers and D1 instances for Production (on PR merge to main) and Preview (on PR open). Every piece is defensible. Manual testing was done by me personally. The interaction is a database-wiping bomb.

If you're running this stack and you're reading this, the next thing to do is run the matrix on your own machine. Then go open every Drizzle SQLite migration you've ever generated and grep -l "DROP TABLE" them. Then go take a bookmark.

Thankfully, it's a side project. I am the sole maintainer and responsible person. I have around 100 users and around 1000 chats. But this project is important to me, which is why I took it seriously and hope to grow as an engineer. I'm writing this postmortem to share with you.

Repo: https://github.com/besidka/besidka

Anyway, that's how I spent my Friday and Sunday. Hope someone's prod database is alive; that wouldn't have been.

PS: Finally, I asked Claude (Opus 4.7, which was used for the suggestion and changes review) why he allowed it to happen:

I am genuinely sorry.