Our Database Ignored Every ON DELETE CASCADE We Wrote

Two of our production apps talk to Turso through the same client library and the same ORM, and they reached opposite conclusions about the same question: does the database enforce foreign keys?

One app, Stem Shovel, concluded in September that it does not. "Turso runs with PRAGMA foreign_keys = 0, so the schema's onDelete rules never fired." An artist's orphaned credits had crashed a song page. The team responded the hard way: 441 lines of hand-written, deepest-first cascade deletes, plus a sweep script that derives the foreign keys from the schema and hunts for orphans.

The other app, Replicator, concluded in October that it does. Its migration probe reported cascade, set-null and violation-rejection all passing, recorded the verdict "the application sets PRAGMA foreign_keys nowhere and does not need to," and shipped a schema with 159 foreign-key actions depending on that.

At least one of them had to be wrong. So we did what we now do with every disagreement that has real engineering riding on it: wrote a pre-registered study brief, built an instrument that proves itself before measuring anything, and ran the matrix on throwaway databases.

The Instrument Comes fFrst

The probe is small: read PRAGMA foreign_keys first, then measure three behaviors on throwaway tables it creates and drops itself. Does deleting a parent cascade to its children? Does set-null fire? Is an insert pointing at a missing parent rejected? The reading and the behaviors are recorded separately, because the real question is whether the reading predicts the behavior.

Before the probe touched anything remote, its tests had to show it detects both states on a local file database, where SQLite's pragma semantics are a known quantity, under both client versions our apps pin. An instrument that has never seen the negative state cannot be trusted to report it.

Then the matrix: both database kinds Turso offers, both client versions, four connection usages (a shared client, a fresh client per statement, statements wrapped in batches, interactive transactions), three pragma arms (never set it, set it ON in a prior call, set it OFF in a prior call), three repetitions of everything, plus a first-connection axis across twelve freshly created databases. 148 cells and 48 first-connection probes, zero errors, every database destroyed afterward.

One finding arrived before the study even ran: Turso's platform API cannot create the newer engine kind of database at all. Only the CLI's --tursodb flag can. Our first complete run silently measured the classic kind twice, and only an empirical kind check, never trusting the creation call, caught it.

The Kinds are Opposites

On the classic libsql kind, foreign keys are enforced, period. The pragma reads 1 on every connection. All three behaviors fire in every usage. And here is the first surprise: PRAGMA foreign_keys = OFF does not stick. It reads back 1 and the database keeps enforcing, even inside an interactive transaction.

On the engine kind, foreign keys are OFF by default. The pragma reads 0 and means it: no cascade, no set-null, violating inserts accepted. Not a cold-start quirk, not a first-connection artifact. Twenty-four probes across six freshly created engine databases read 0 and enforced nothing, on the first connection and every one after.

And the second surprise, the one that explains everything: on the engine kind, PRAGMA foreign_keys = ON persists at the database level. We set it through one connection and then watched it hold through every subsequent fresh connection, under both client versions, including an arm where the pragma and the probe never share a connection at all. Flip the switch once and that database enforces from then on.

Both Apps were Right, about Different Databases

Rerun both apps' stories through that one mechanism and the contradiction dissolves.

Stem Shovel's September observation is simply the engine default. The pragma read 0 because it was 0, and the onDelete rules never fired because nothing was enforcing them. Those 441 lines of hand-written cascades are not redundant paranoia. They are load-bearing.

Replicator's October verdict has a more uncomfortable anatomy. Its own probe script runs PRAGMA foreign_keys = ON before creating the test tables, and on an engine database that ON persists. The probe flipped the switch, then measured the switch, then concluded no switch was needed. The "six subsequent fresh connections read 1" in its verdict note was the probe's own footprint. A measurement that mutates the thing it measures will happily confirm whatever it just did.

Which leaves the exposure: an app running engine databases in production with 159 foreign-key actions and no pragma anywhere in the application code. The check is one read-only line per environment, and the fix is nearly as small. We recommend running PRAGMA foreign_keys = ON at client construction: on the engine kind it turns enforcement on, on the classic kind it is a no-op, and it covers the one question this study left unmeasured, whether the persistent flip survives engine restarts.

What We Got Wrong, and What to Trust

The bench registered predictions before running, and scored one of three. We predicted enforcement would be on everywhere in steady state, with Stem Shovel's orphans traced to its own maintenance scripts. Wrong winner. We predicted Replicator's first-connection artifact would reproduce. It does not exist. We predicted a pragma set in a prior call would not carry across the remote protocol. It carries, and on the engine kind it carries database-wide, which is stronger than anything we imagined.

The thing that never lied, in any of the 196 probes, was the pragma reading itself. Every cell where PRAGMA foreign_keys read 1 enforced everything; every cell where it read 0 enforced nothing. The failures in both apps' stories were assumptions layered on top of readings: that a reading taken after setting the pragma describes the default, that an orphan proves the database never enforces, that a database kind is an implementation detail.

So the portable lesson is one line long. Ask your own database PRAGMA foreign_keys and believe the answer, because the answer depends on which kind of database you are holding, and the two kinds ship with opposite defaults.

The registration, the self-proving probe, the full matrix results and the handoff to both app teams live in the barkup-bench repository (BRIEF-AW.md, REPORT.md, results/aw.json). The analyst model behind the bench is Claude Fable 5; every cell in this study was measured by committed mechanical code with no model anywhere in the loop.