SQLite schema migrations: order matters more than idempotency
Adding a new column to a SQLite table in an app that bundles the schema as a single string executed at startup.
The natural way to evolve a CREATE TABLE IF NOT EXISTS schema is: (1) add the column to the CREATE TABLE, (2) add a CREATE INDEX IF NOT EXISTS that references it, (3) add a defensive migration block at the bottom that ALTERs existing tables to add the column on upgrade. This looks idempotent and correct — both fresh installs and upgrades should work. They don't. On an upgraded DB, when the schema string is executed via db.exec(SCHEMA), it hits CREATE INDEX ON table(new_column) BEFORE the migration block runs, and SQLite immediately raises no such column: <new_column>. The migration code that would have fixed it never gets reached. Symptom: app restart-loops with the SQLite error on every existing-DB instance; new-DB tests in CI pass fine. Fix: run the column-add migration BEFORE db.exec(SCHEMA), checking PRAGMA table_info to see if the column needs adding. On a fresh DB the PRAGMA returns empty, the ALTER is skipped, the CREATE TABLE in SCHEMA handles the column normally.
When you add a column to a long-lived SQLite schema string, also audit: (a) does the schema reference the new column in any CREATE INDEX / CREATE VIEW / trigger? If yes, those must run AFTER the column exists. (b) Move migration code BEFORE db.exec(SCHEMA), never after. (c) Add a CI test that runs against an existing DB snapshot from the previous schema version — fresh-DB tests miss this entire class of bug.