Findings No. 66
On Turso engine-kind databases foreign keys are not enforced, while local `file:` SQLite through `@libsql/client` enforces them
Turso engine-kind databases ignore foreign keys and a pragma set at client creation does not persist over HTTP, while local libSQL test databases cascade.
Replicator runs on Turso engine-kind databases in dev, stage and production. In early November 2026 we found that all 171 foreign keys across 81 tables had been inert since the 2026-10-24 cutover. Every onDelete cascade and set-null in the Drizzle schema was documentation. Production and stage had no orphans yet. Dev had 262 version rows whose parent had been deleted.
The first fix issued PRAGMA foreign_keys = ON when the client was created. It had no effect. The HTTP transport in @libsql/client opens a stream, runs one statement and closes it, so a pragma set at construction is gone by the next query. The project record also notes that Turso core PR #11095 made the pragma transaction-scoped. Read through the app's own client, the pragma still returned 0, and the claim was retracted one release later.
Local tests could not reveal this. The same client opening a local file: database turns foreign keys on. The stem-shovel repo recorded it on 0.18.0, and a repeat probe on Replicator's 0.17.4 also read 1 and cascaded. A test on local SQLite proves your row selection. It tells you nothing about the platform's enforcement.
The fix has three parts. Parent deletes run explicit child deletes, deepest first, in one atomic batch. A CI guard derives the foreign-key graph from Drizzle metadata and fails the build when a child table has no handler. A daily probe, scripts/fk-probe.ts, creates a scratch parent and child with ON DELETE CASCADE, deletes the parent and checks whether the child survived. On 2026-11-03 it ran on all three environments. In each one the cascade did not fire and a violating insert was accepted.
Rule: establish enforcement by attempting a real cascade through the app's own client, and do not trust the pragma reading. A probe that configures the thing it measures is measuring itself.
Receipt: Replicator v3.317.0 (commits 722b1e92, bb995b1c; scripts/fk-probe.ts, src/lib/server/db/fk-graph.ts, src/lib/server/db/cascade.ts, tests/lib/server/fk-coverage.test.ts).