Text or blob primary key on a rowid table

Note — this works, but it is a documented trap or a cost the author may not have meant.

What it catches

On a rowid table a TEXT or BLOB primary key is not the table's key: rows live in a B-tree keyed by the hidden rowid, and the primary key is a separate unique index. Every lookup by key walks the index and then the table, and every row is stored twice over (key in the index, key in the row). WITHOUT ROWID makes the primary key the table's own B-tree — the manual recommends it exactly for this shape.

Fires on

CREATE TABLE sessions (token TEXT PRIMARY KEY NOT NULL, user_id INTEGER) STRICT;

Do this instead

CREATE TABLE sessions (token TEXT PRIMARY KEY NOT NULL, user_id INTEGER) WITHOUT ROWID, STRICT;