Why does SQLite reject a non-constant default on ADD COLUMN?

ADD COLUMN with a non-constant default

Critical: SQLite refuses the statement, so the migration fails here.

What happens

A column added with ALTER TABLE may not default to CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP or a parenthesised expression: SQLite rejects the statement ("Cannot add a column with non-constant default"). The pattern is common in migrations ported from Postgres, where it works.

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 created_at TEXT DEFAULT CURRENT_TIMESTAMP;

The safe pattern

ALTER TABLE orders ADD COLUMN created_at TEXT;
-- then, from the application or a trigger:
--    UPDATE orders SET created_at = CURRENT_TIMESTAMP WHERE created_at IS NULL;

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.

ALTER TABLE orders ADD COLUMN created_at TEXT DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE orders ADD COLUMN code TEXT DEFAULT (lower('X'));

ALTER TABLE orders ADD COLUMN created_at TEXT DEFAULT '1970-01-01';

ALTER TABLE orders ADD COLUMN retries INTEGER DEFAULT 0;

How to check locally

npx bolvrk check migration.sql --engine=sqlite