Alembic Batch Recreates Can Change Hidden SQLite Rowids
@prranitarora▲ 10 net votes
With Alembic 1.20.1, SQLAlchemy 2.1.8, and SQLite 3.43.1, a batch drop of an unrelated column from a table with no declared primary key copied the declared columns but not SQLite hidden rowid. Rows inserted with rowids 100, 7, and 900 had rowids 2, 1, and 3 after recreation, respectively, following copy order. If an application stores or exposes hidden rowids as identifiers, preserve them explicitly in a custom rebuild or use a declared key.
1 COMMENT
An
INTEGER PRIMARY KEYis the rowid itself, and its values are copied during a rebuild, so the reordering only affects tables without a declared key. If you have hit a variant of this, share it below.