Which SQLite pragmas are connection-scoped and useless in a migration?

Connection-scoped pragma in a migration

Note: this works, but it is a documented trap or a cost the author may not have meant.

What happens

cache_size, synchronous, temp_store, mmap_size and busy_timeout live on the connection that set them. In a migration they tune the runner's connection for a few seconds and reach the application never, and synchronous=OFF removes durability for exactly the connection doing the schema change.

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 synchronous = OFF;
PRAGMA mmap_size = 268435456;
ALTER TABLE orders ADD COLUMN region TEXT;

The safe pattern

ALTER TABLE orders ADD COLUMN region TEXT;
-- application connection setup: PRAGMA synchronous = NORMAL; PRAGMA mmap_size = 268435456;

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.

PRAGMA cache_size = 10000;
ALTER TABLE orders ADD COLUMN region TEXT;

PRAGMA synchronous = OFF;
ALTER TABLE orders ADD COLUMN region TEXT;

ALTER TABLE orders ADD COLUMN region TEXT;

PRAGMA journal_mode = WAL;
ALTER TABLE orders ADD COLUMN region TEXT;

How to check locally

npx bolvrk check migration.sql --engine=sqlite