Why can't a WITHOUT ROWID table use AUTOINCREMENT?

AUTOINCREMENT on a WITHOUT ROWID table

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

What happens

AUTOINCREMENT only exists for the rowid, and a WITHOUT ROWID table has none: SQLite refuses the CREATE TABLE with "AUTOINCREMENT not allowed on WITHOUT ROWID tables". The table is not created and the migration stops.

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 sessions (id INTEGER PRIMARY KEY AUTOINCREMENT, token TEXT) WITHOUT ROWID;

The safe pattern

CREATE TABLE sessions (id INTEGER PRIMARY KEY, token TEXT);

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.

CREATE TABLE sessions (id INTEGER PRIMARY KEY AUTOINCREMENT, token TEXT) WITHOUT ROWID;

CREATE TABLE sessions (id INTEGER PRIMARY KEY AUTOINCREMENT, token TEXT) STRICT;

CREATE TABLE sessions (id INTEGER PRIMARY KEY, token TEXT) WITHOUT ROWID;

How to check locally

npx bolvrk check migration.sql --engine=sqlite