Why is INT PRIMARY KEY not the rowid in SQLite?
INT PRIMARY KEY is not a rowid alias
Note: this works, but it is a documented trap or a cost the author may not have meant.
What happens
Only a column declared exactly INTEGER PRIMARY KEY becomes an alias for the rowid. INT PRIMARY KEY (or BIGINT, or INTEGER PRIMARY KEY DESC) is an ordinary column with a separate unique index: every lookup by primary key is an extra B-tree hop, the value is not the rowid, and VACUUM may renumber the real rowid underneath it.
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 INT PRIMARY KEY, qty INTEGER);The safe pattern
Spell the type INTEGER, exactly, on the primary key column of a rowid table.
CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER);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)
CREATE TABLE orders (id INT PRIMARY KEY, qty INTEGER);CREATE TABLE orders (id INTEGER PRIMARY KEY DESC, qty INTEGER);Stays silent (3)
CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER);CREATE TABLE orders (id TEXT PRIMARY KEY, qty INTEGER);CREATE TABLE orders (id INT PRIMARY KEY, qty INTEGER) WITHOUT ROWID;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