migrate --dialect d1: the sqlite table-rebuild's PRAGMA foreign_keys OFF is a no-op on remote D1, so any parent-table rebuild fails with FOREIGN KEY constraint failed

[dmealing]

Split out of #225 (the reporter offered the standalone repro there; filing so it is not lost). Independent of the verify --db work that shipped in 0.19.1.

The defect

renderD1 delegates the whole rebuild to the SQLite emitter — server/typescript/packages/migrate-ts/src/emit/d1.ts:2,10:

import { renderSqlite } from "./sqlite.js";
...
const sqliteResult = renderSqlite(changes, expectedSchema, actualMeta);

So a D1 migration inherits the SQLite 12-step table rebuild verbatim (emit/sqlite.ts:165-182):

PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;
CREATE TABLE "__new_x" (...);
INSERT INTO "__new_x" (...) SELECT ... FROM "x";
DROP TABLE "x";
ALTER TABLE "__new_x" RENAME TO "x";
COMMIT;
PRAGMA foreign_keys = ON;
PRAGMA foreign_key_check;

That sequence is correct for local SQLite and wrong for remote D1. D1 runs statements inside its own implicit transaction, and SQLite ignores PRAGMA foreign_keys while a transaction is open — so the OFF never takes effect. DROP TABLE "x" then executes with FK enforcement live. When x is a parent table with existing child rows, the drop orphans them and the statement fails:

FOREIGN KEY constraint failed

Against an empty remote database nothing is orphaned, so this is invisible until a table has data.

The trigger is ordinary: SQLite cannot add a CHECK constraint via ALTER, so the emitter chooses a full rebuild. Adding a CHECK to a parent table is enough to produce an un-appliable migration. field.enum @values changes take the same path.

defer_foreign_keys = on does not rescue this. It defers enforcement to COMMIT, which is exactly right for a child-table rebuild — rows are reinserted before commit, so the deferred check passes. A parent drop is different: the children are still orphaned when COMMIT runs, so the deferred check fails there instead. Same failure, later.

applyD1SafetyPass does not catch it either — emit/d1-safety-pass.ts rejects only ATTACH/DETACH and VACUUM, and has no PRAGMA awareness (grep for PRAGMA in that file returns nothing). So the unusable SQL is emitted and only fails at apply time, against production.

Related, same root cause: the emitted BEGIN TRANSACTION; / COMMIT; are themselves questionable inside D1's implicit transaction and deserve a decision in the same pass.

Why it matters

The failure lands at wrangler d1 migrations apply --remote — i.e. after the migration has been generated, reviewed and committed, against the real database. And it is silent until a table holds rows, so it will typically first appear on a populated production database rather than in a local test.

Direction (not prescribing)

The D1 emitter needs its own rebuild strategy rather than inheriting SQLite's, and applyD1SafetyPass should refuse to emit a statement sequence it knows cannot apply — a loud generation-time failure is far better than a runtime failure against production. Whether the right rebuild is drop-and-recreate children, an ordered teardown, or something else is a real design question; worth deciding deliberately rather than patching the PRAGMA.

Whatever lands should be gated the way the other migrate defects now are: emit -> apply to a real engine -> introspect -> re-diff must be EMPTY. A unit test asserting the emitted text would not have caught this, since the SQL is textually correct — it is only wrong in D1's execution context.

Environment

Present on main as of 0.19.1 / 7.11.1. D1 is TS-only, so this is migrate-ts + CLI surface; no cross-port impact.