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

### Report hasn't been filed before.

- [x] 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](https://www.sqlite.org/pragma.html#pragma_foreign_keys): *"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](https://www.sqlite.org/lang_droptable.html)). 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 #1813, #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

```ts
// 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(),
});
```

```ts
// 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](https://www.sqlite.org/lang_altertable.html#:~:text=Making%20Other%20Kinds%20Of%20Table%20Schema%20Changes)), **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 #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:

```ts
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)