Why does SQLite refuse ADD COLUMN NOT NULL without a default?
ADD COLUMN NOT NULL without a default
Critical: SQLite refuses the statement, so the migration fails here.
What happens
SQLite refuses to add a NOT NULL column unless it has a default other than NULL, the statement fails with "Cannot add a NOT NULL column with default value NULL", so the migration stops here in every environment, including the one that matters.
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
ALTER TABLE orders ADD COLUMN region TEXT NOT NULL;The safe pattern
Give the column a non-NULL default, or add it nullable, backfill, and enforce NOT NULL by rebuilding the table later. In SQLite a default on ADD COLUMN is free: existing rows are not rewritten, the default is read from the schema.
ALTER TABLE orders ADD COLUMN region TEXT NOT NULL DEFAULT 'eu';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)
ALTER TABLE orders ADD COLUMN region TEXT NOT NULL DEFAULT NULL;ALTER TABLE orders ADD COLUMN region TEXT NOT NULL;Stays silent (3)
ALTER TABLE orders ADD COLUMN region TEXT;CREATE TABLE orders (id INTEGER PRIMARY KEY);
ALTER TABLE orders ADD COLUMN region TEXT NOT NULL;ALTER TABLE orders ADD COLUMN region TEXT NOT NULL DEFAULT 'eu';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