[BUG]: SQLite table-rebuild migrations silently wipe child tables for any FK with `ON DELETE CASCADE`

[vadim0x60]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.44.7 (also reproduced reading the migrator source on 0.45.2)

What version of drizzle-kit are you using?

0.31.7 (latest at time of writing is 0.31.10; the codegen behaviour involved hasn't changed)

Other packages

No response

Describe the Bug

This is a more dangerous variant of the long-standing issues #1813 (Jan 2024, still open, priority) and #4089. Those issues describe SQLite table-rebuild migrations failing loudly with SQLITE_CONSTRAINT: FOREIGN KEY constraint failed. In the presence of ON DELETE CASCADE, the exact same code path does not fail — it silently deletes every row of every child table that references the table being rebuilt, then commits the migration as if everything succeeded.

In our case (an Electron desktop app using drizzle-orm/better-sqlite3) a single innocuous schema change — flipping a text().default('preview') column to text().default('markup') on a chats table — wiped every row in messages, message_attachments, and inline_actions for every user the moment they upgraded to the build that ran the migration. No error was raised, no log was emitted, the migration is recorded in __drizzle_migrations as applied successfully. Users only noticed their old chats were empty.

Root cause

Three interacting pieces, each of which is correct in isolation:

1. Drizzle Kit's table-rebuild template (for any SQLite schema change that can't be done with bare ALTER TABLE) starts with PRAGMA foreign_keys=OFF; precisely to disarm cascades during the rebuild. The template is correct in intent.
2. drizzle-orm's migrator (sqlite-core/dialect.js, SQLiteDialect.migrate) wraps every migration file in a single BEGIN ... COMMIT so a partial migration rolls back. This is reasonable.
3. SQLite ignores PRAGMA foreign_keys inside a transaction. From the docs: "This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT."

The pragma the codegen put there to keep users safe is silently neutralised by the runtime's own transaction wrapper.

Why it's silent for CASCADE FKs (this is the new bit)

DROP TABLE Parent with foreign_keys=ON is defined by SQLite as an implicit DELETE FROM Parent followed by removal of the table (see SQLite docs on DROP TABLE). The implicit DELETE fires whatever ON DELETE action the child FK declares:

• NO ACTION (the default) → the implicit delete fails with SQLITE_CONSTRAINT: FOREIGN KEY constraint failed, the transaction rolls back, the user sees a loud error. This is the scenario reported in [BUG]: Cannot update SQLite database due to foreign key constraints. #1813, [BUG]: Cloudflare D1 FOREIGN KEY constraint failed #4089, and basically every existing report.
• CASCADE → SQLite obediently deletes every dependent row in the child table (which itself may further cascade), the parent delete then succeeds, the DROP TABLE completes, the migration commits cleanly. All data in dependent tables is gone, with no error and no warning.

So the existing reports all happen to involve schemas where the FK defaults to NO ACTION. When the FK is ON DELETE CASCADE — a perfectly normal, common, and explicitly documented Drizzle pattern (references(() => parent.id, { onDelete: 'cascade' })) — the same bug becomes a silent data-loss event instead of a noisy migration failure. From a severity standpoint this is much worse: developers who hit it don't open an issue, they just lose data.

Minimal reproduction

// schema.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const parent = sqliteTable('parent', {
  id: text('id').primaryKey(),
  // Some attribute we'll change later to force a rebuild
  mode: text('mode').notNull().default('a'),
});

export const child = sqliteTable('child', {
  id: text('id').primaryKey(),
  parentId: text('parent_id')
    .notNull()
    .references(() => parent.id, { onDelete: 'cascade' }),
  payload: text('payload').notNull(),
});

// app.ts (or migrate.ts)
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';

const sqlite = new Database('repro.db');
sqlite.pragma('foreign_keys = ON');                      // ← what the docs tell you to do
const db = drizzle(sqlite);
migrate(db, { migrationsFolder: './drizzle' });

Steps:

1. Run drizzle-kit generate with the schema above.
2. Run the app once; insert a parent row and a child row referencing it.
3. Change default('a') to default('b') in parent.mode. (Or rename any non-PK column. Anything that forces a full table rebuild instead of a bare ALTER TABLE ADD COLUMN.)
4. Run drizzle-kit generate again. The new migration uses the standard __new_parent rebuild template, prefaced with PRAGMA foreign_keys=OFF;.
5. Run the app. migrate() reports success. Open the DB: parent still has its row; child is empty.

Expected behaviour

Either:

• The migrator does not wrap the migration in a transaction when the migration's SQL contains a PRAGMA foreign_keys statement (and instead checks PRAGMA foreign_key_check at the end as recommended by the SQLite ALTER TABLE docs), or
• The codegen emits PRAGMA defer_foreign_keys = true instead of PRAGMA foreign_keys = OFF for SQLite ≥3.31, since defer_foreign_keys is allowed inside a transaction. (Already requested in [FEATURE]: PRAGMA defer_foreign_keys for SQLite migrations #3065.)

In either case, the user's data should not silently vanish, and an ON DELETE CASCADE FK should not be functionally more dangerous than a NO ACTION FK during a benign schema change.

Workaround

Disable FKs on the raw SQLite handle before calling migrate(), then re-enable for the lifetime of the app:

const sqlite = new Database('app.db');
const db = drizzle(sqlite);

sqlite.pragma('foreign_keys = OFF');     // outside any transaction → actually takes effect
try {
  migrate(db, { migrationsFolder: './drizzle' });
} finally {
  sqlite.pragma('foreign_keys = ON');    // restore for normal app queries
}

This works because the pragma is set before drizzle-orm opens its BEGIN, and SQLite honours it. But it should not be the user's responsibility to know this — the PRAGMA foreign_keys=OFF already present in every generated rebuild migration looks like it provides this guarantee, and developers reasonably trust that it does.

Why this issue isn't a duplicate of #1813 / #4089

The existing reports describe a visible failure mode (SQLITE_CONSTRAINT thrown, migration aborted, transaction rolls back, no data lost). They've been at priority Will be worked on next for two years, presumably because the workaround is easy and the failure is obvious. This report is about the silent failure mode triggered by the same bug whenever the child FK declares ON DELETE CASCADE — there is no error to alert anyone, the migration is marked applied, and the only signal is that user data has disappeared.

Environment

• drizzle-orm: 0.44.7
• drizzle-kit: 0.31.7
• better-sqlite3: ^11
• Node.js: 20.x (Electron 32)
• OS: macOS 15 (also reproduced on Windows 11)

[truffle-dev]

The bug touches both dialect migrator paths, so it's driver-agnostic — not specific to better-sqlite3.

At drizzle-orm/src/sqlite-core/dialect.ts:

• L969 (sync SQLiteSyncDialect.migrate): explicit session.run(sql\BEGIN`)before the migration loop,COMMIT` after.
• L1027 (async SQLiteAsyncDialect.migrate): await session.transaction(async (tx) => …) around the same loop.

Same fault surface for libsql, sqlite-proxy, durable-sqlite, bun-sqlite, every SQLite driver that flows through the shared dialect migrator.

One correction on your option 2: PRAGMA defer_foreign_keys does not actually fix this. SQLite's docs draw a distinction between FK constraint checks (deferrable) and FK cascading actions (not deferrable): "Even if a deferred foreign key constraint is violated within a transaction, the action specified by the foreign key declaration is performed immediately" (https://www.sqlite.org/foreignkeys.html#fk_deferred). Verified locally on SQLite 3.46.1:

CREATE TABLE parent (id INTEGER PRIMARY KEY, mode TEXT);
CREATE TABLE child (id INTEGER PRIMARY KEY,
    parent_id INTEGER NOT NULL REFERENCES parent(id) ON DELETE CASCADE);
INSERT INTO parent VALUES (1, 'a');
INSERT INTO child VALUES (1, 1), (2, 1);

PRAGMA defer_foreign_keys = ON;
BEGIN;
  CREATE TABLE __new_parent (id INTEGER PRIMARY KEY, mode TEXT);
  INSERT INTO __new_parent SELECT * FROM parent;
  DROP TABLE parent;
  ALTER TABLE __new_parent RENAME TO parent;
COMMIT;

SELECT count(*) FROM child;  -- 0, not 2

Same result with PRAGMA foreign_keys=OFF; PRAGMA defer_foreign_keys=ON; BEGIN; … and with the current BEGIN; PRAGMA foreign_keys=OFF; … shape. Only PRAGMA foreign_keys=OFF; BEGIN; … (pragma before begin) preserves the child rows, which matches the SQLite docs and your manual workaround.

So the codegen-only path floated on #3065 and on #5074 (by @yan-vikng-dev in April) doesn't solve the silent data loss — it just relocates which pragma is silently ignored. The fix has to be on the migrator side: either lift any PRAGMA foreign_keys statements out of the migration SQL and run them before BEGIN (with the mirror after COMMIT), or skip the transaction wrap entirely when the migration contains a foreign_keys pragma (and rely on PRAGMA foreign_key_check at the end, the pattern the SQLite ALTER TABLE docs recommend).

#5074 attempted a different angle (codegen-only, D1-specific, backup tables — open since 2025-11-26, 451+/31-, stuck on the upstream Cloudflare-side debate). Reframing as the wider migrator-wrap bug, which your report does, applies to every SQLite driver and shifts the fix point accordingly.

(Tested against drizzle-orm@main 48e5406 and SQLite 3.46.1.)

[Zelys-DFKH]

@truffle-dev, your analysis here saved a lot of time. Especially the defer_foreign_keys part — that wasn't obvious without testing. The fix is exactly what you described: lift PRAGMA foreign_keys before BEGIN, restore after COMMIT. Went ahead and coded it up. PR incoming.