SQLite migrations: guard rebuilds against external ON DELETE CASCADE dependencies inside transactions #128

Problem

Please add an automatic safety guard for the known SQLite rebuild limitation described in PR #121 and the SQLite migration warning.

If a table has an external/unmodelled ON DELETE CASCADE reference, applying its generated rebuild inside a transaction can delete child rows. PRAGMA foreign_keys=OFF has no effect inside that transaction, and the rebuild drops the old parent. A parent-column default change is enough to trigger this; it need not intend to delete any rows.

This does not affect every rebuild. The reproducer needs a referenced parent, the external FK, enabled foreign keys, and transaction-wrapped execution.

Reproduction

Tested with published @maple-dev/effect-orm@0.8.0, effect@4.0.6, Node 24.18.0. This runs the package's actual schema diff and SQL renderer on in-memory node:sqlite. It is not a workerd or remote D1 test.

In an empty directory, save this as repro.mjs and run:

npm install --no-save @maple-dev/effect-orm@0.8.0 effect@4.0.6
node repro.mjs

import assert from "node:assert/strict";
import { DatabaseSync } from "node:sqlite";
import { Effect } from "effect";
import * as SQL from "@maple-dev/effect-orm/sqlite";
import * as Schema from "@maple-dev/effect-orm/schema";

const sqlite = new DatabaseSync(":memory:");
sqlite.exec(`
  PRAGMA foreign_keys=ON;
  CREATE TABLE parent(id TEXT PRIMARY KEY, value TEXT NOT NULL);
  CREATE TABLE child(
    id TEXT PRIMARY KEY,
    parentId TEXT REFERENCES parent(id) ON DELETE CASCADE
  );
  INSERT INTO parent VALUES ('p', 'old');
  INSERT INTO child VALUES ('c', 'p');
`);

// The child FK exists in the database, outside the ORM schema.
const before = SQL.table("parent", {
  columns: { id: SQL.text, value: SQL.text },
  primaryKey: ["id"],
});
const after = SQL.table("parent", {
  columns: { id: SQL.text, value: SQL.column(SQL.text, { default: "new" }) },
  primaryKey: ["id"],
});
const plan = Schema.diffSqliteSchemas(
  Effect.runSync(Schema.sqliteEntitiesOf([before])),
  Effect.runSync(Schema.sqliteEntitiesOf([after])),
);
const statements = plan.ops.flatMap(Schema.renderSqliteOp);
const count = () => sqlite.prepare("SELECT COUNT(*) AS n FROM child").get().n;
const childRowsBefore = count();
sqlite.exec("BEGIN");
for (const statement of statements) sqlite.exec(statement);
sqlite.exec("COMMIT");
const childRowsAfter = count();
console.log(JSON.stringify({ childRowsBefore, childRowsAfter }));
// These assertions pin the observed loss, not the desired behavior.
assert.equal(childRowsBefore, 1);
assert.equal(childRowsAfter, 0);
sqlite.close();

Actual output:

{"childRowsBefore":1,"childRowsAfter":0}

The generated statements turn foreign keys off, create/copy into the replacement table, drop the original parent, rename the replacement and turn foreign keys back on. In the transaction, the drop fires the cascade.

Expected behavior

Before destructive statements execute, either:

• reject this rebuild with a clear error and leave parent/child data unchanged; or
• apply a validated dependency-preserving plan and keep the child row and reference.

An offline diff cannot discover unmodelled live dependencies by itself. The solution may need a runtime dependency check or an explicit refusal path; this report does not prescribe a new migration runner. The regression contract should cover an externally created FK and transaction-wrapped execution.

For the D1 path, a documentation warning alone leaves a data-loss risk. Running outside a transaction is not a D1 remedy, and defer_foreign_keys does not prevent cascade deletes.

Context

We are evaluating effect-orm for MyRemotely's MariaDB → Cloudflare D1 move in a CAFE stack (Cloudflare, Alchemy, Foldkit, Effect). We can help with regression tests and a patch.