Which PRAGMAs do nothing inside a SQLite transaction?
PRAGMA that is a no-op inside a transaction
Warning: it works, but holds the single write lock or breaks the previous release.
What happens
PRAGMA foreign_keys and PRAGMA journal_mode do nothing while a transaction is open, SQLite does not error, it silently leaves the setting as it was. A rebuild that switches foreign keys off after BEGIN runs with them on, and everything SL009 warns about applies.
Why it is dangerous on a populated table
SQLite holds one write lock for the whole database, so anything that rebuilds a table blocks every writer for as long as the copy takes, and that time grows with the table.
Fires on
BEGIN;
PRAGMA foreign_keys = OFF;
DROP TABLE orders;
COMMIT;The safe pattern
Issue the pragma before BEGIN (and re-enable after COMMIT). If the migration runner wraps every file in a transaction, the pragma has to be set by the runner itself.
PRAGMA foreign_keys = OFF;
BEGIN;
DROP TABLE orders;
COMMIT;
PRAGMA foreign_keys = ON;Fixtures
The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.
Fires (2)
BEGIN;
PRAGMA foreign_keys = OFF;
DROP TABLE orders;
COMMIT;BEGIN TRANSACTION;
PRAGMA journal_mode = WAL;
COMMIT;Stays silent (2)
BEGIN;
PRAGMA defer_foreign_keys = ON;
DROP TABLE orders;
COMMIT;PRAGMA foreign_keys = OFF;
BEGIN;
DROP TABLE orders;
COMMIT;
PRAGMA foreign_keys = ON;How to check locally
SQLite support is in beta: this rule runs locally in the free CLI, static only, and not in the hosted service yet. No install, nothing leaves your machine:
npx bolvrk check migration.sql --engine=sqlite