Should a SQLite migration ATTACH another database?
ATTACH DATABASE in a migration
Warning: it works, but holds the single write lock or breaks the previous release.
What happens
ATTACH opens a second database file on the runner's connection, by a path that only makes sense on one machine, for the life of that connection. In a migration it is almost always an import script that got committed: it fails on any other host, and while it works it can read from or write to a file the migration never declared.
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
ATTACH DATABASE '/var/backups/legacy.db' AS legacy;
INSERT INTO orders SELECT * FROM legacy.orders;The safe pattern
Keep imports out of migrations: run them as an operational job with the file path as a parameter, and leave the migration to the schema.
-- operational import job, not a migration:
-- sqlite3 app.db "ATTACH '/path/legacy.db' AS legacy; INSERT INTO orders SELECT * FROM legacy.orders; DETACH legacy;"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 (1)
ATTACH DATABASE '/var/backups/legacy.db' AS legacy;
INSERT INTO orders SELECT * FROM legacy.orders;Stays silent (1)
INSERT INTO orders (id, qty) VALUES (1, 2);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