I've written before about why we run SQLite in production for HelperX, and about multi-tenant isolation and backup strategy. There's one topic I kept deferring because it's genuinely hard and I didn't want to hand out advice I wasn't sure of: schema migrations.
SQLite is famous for a painful migration story. ALTER TABLE is deliberately limited — you can rename a table, add a column, or rename a column, but you can't drop a column, change a column's type, or add a non-constant default without rebuilding the table. The classic answer ("create a new table, copy the data, swap") is easy to get wrong under concurrent writes.
This article is the migration approach we settled on after breaking things a few times. It works under live write load, never loses data, and degrades gracefully if something fails midway.
Why SQLite migrations are uniquely hard
In Postgres, a migration like "drop column X" is a one-liner and (with appropriate locking) runs while the app keeps writing. SQLite can't do that. The supported ALTER TABLE operations are intentionally minimal — the SQLite maintainers chose simplicity over migration ergonomics.
