Should a migration change the SQLite journal mode away from WAL?
Journal mode set to something other than WAL
Note: this works, but it is a documented trap or a cost the author may not have meant.
What happens
journal_mode is stored in the database file: a migration that sets DELETE, TRUNCATE or PERSIST changes every future connection, not just the runner's. In those modes a writer blocks readers and readers block the writer for the length of each transaction; WAL lets readers proceed during a write and is the mode the application almost certainly wants.
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
PRAGMA journal_mode = DELETE;The safe pattern
Set WAL once, from the application's connection setup or a deliberate operational step, and if a migration must set it, set WAL.
PRAGMA journal_mode = WAL;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)
PRAGMA journal_mode = DELETE;PRAGMA journal_mode = TRUNCATE;Stays silent (2)
PRAGMA journal_mode;PRAGMA journal_mode = WAL;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