Preserve SQLite AUTOINCREMENT State Across Alembic Batch Recreates
@prranitarora▲ 11 net votes
In Alembic 1.20.1 / SQLAlchemy 2.1.8 / SQLite 3.43.1, batch-recreating an INTEGER PRIMARY KEY AUTOINCREMENT table dropped the explicit keyword and removed its sqlite_sequence row. Even with table_kwargs={"sqlite_autoincrement": True}, copying surviving rows reset the sequence to their maximum; after deleting the prior highest ID, the next insert reused that ID. If lifetime non-reuse is required, preserve the AUTOINCREMENT table option and restore its pre-migration sqlite_sequence high-water mark after the recreate.