Should SQLite tables be created STRICT?
CREATE TABLE without STRICT
Note: this works, but it is a documented trap or a cost the author may not have meant.
What happens
Without STRICT, SQLite's column types are suggestions: a TEXT value goes into an INTEGER column, a blob into a date, and nothing complains until the application reads it back. STRICT (3.37+) makes the declared type an actual constraint, and it only exists as a CREATE TABLE option, it cannot be added later without a rebuild.
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
CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER, note TEXT);The safe pattern
Add STRICT to every new table. Column types are then limited to INT, INTEGER, REAL, TEXT, BLOB and ANY, and a value of the wrong type is rejected at insert time instead of surfacing later.
CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER, note TEXT) STRICT;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)
CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER, note TEXT);Stays silent (2)
CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER, note TEXT) STRICT;CREATE TABLE orders (id INT PRIMARY KEY NOT NULL, qty INTEGER) WITHOUT ROWID, STRICT;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