After porting a Drizzle schema from Postgres to SQLite, `references(() => parent.id, { onDelete: "cascade" })` silently does nothing: deleting a parent row leaves orphaned child rows instead of raising an error or cascading. The generated DDL is correct and contains the FOREIGN KEY ... ON DELETE cascade clauses, so the schema looks right — SQLite parses and stores foreign key constraints but does not enforce them unless enforcement is turned on for each individual database connection.
Fix: Execute `PRAGMA foreign_keys = ON` on every connection at the point where it is opened, before handing it to Drizzle — it is a per-connection setting, not a per-database one, and it defaults to off for backwards compatibility. With better-sqlite3 this is `connection.pragma("foreign_keys = ON")` inside the factory that creates the Database instance, so any code path that opens the DB (app, migration script, seed script, tests) gets it. Note it must not be issued inside a transaction, which is another reason to do it at connection setup. Practical guard: assert the behavior in a test — delete a parent row and check the child table is empty — because the failure mode is silent data corruption rather than an exception, and it will not surface in normal development. Two related SQLite-connection settings worth applying at the same point: `journal_mode = WAL` for concurrent readers, and `busy_timeout` so concurrent writers block briefly instead of immediately returning SQLITE_BUSY.
sqlitebetter-sqlite3drizzleforeign-keyscascadepostgres-migration
References
- https://www.sqlite.org/foreignkeys.html — Section 2 states that foreign key constraints are disabled by default (for backwards compatibility) and must be enabled separately for each database connection using PRAGMA foreign_keys = ON; it also notes the pragma is a no-op inside a transaction. SQLite still parses and stores the constraint definitions when enforcement is off, which is why the DDL looks correct while cascades never fire.
- https://www.sqlite.org/pragma.html#pragma_foreign_keys — The foreign_keys pragma documentation confirms the setting is per-connection and defaults to off.
- https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md — better-sqlite3 exposes a .pragma(string) method on the Database object for executing PRAGMA statements on that connection.
- https://www.sqlite.org/wal.html — WAL journal mode allows readers and a writer to proceed concurrently, unlike the default rollback journal, and is persistent once set on a database.