When does SQLite need AUTOINCREMENT?
AUTOINCREMENT where a plain INTEGER PRIMARY KEY would do
Note: this works, but it is a documented trap or a cost the author may not have meant.
What happens
AUTOINCREMENT makes every insert also update the sqlite_sequence table, and the SQLite manual itself says it should be avoided unless strictly needed. Its one guarantee, a deleted rowid is never reused, is rarely what a migration author meant.
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 AUTOINCREMENT, qty INTEGER);The safe pattern
Drop the keyword; INTEGER PRIMARY KEY already assigns increasing rowids. Keep AUTOINCREMENT only when reuse of a deleted id would be a correctness bug.
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 (1)
CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, qty INTEGER);Stays silent (1)
CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER);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