Migrating a SQLite Schema in Production Without Locking Writers

Short summary

SQLite's limited ALTER TABLE forces destructive changes to rebuild the entire table, which locks writers for seconds in production. The solution is phased: create new table with target schema (additive, no lock), dual-write new rows to both tables, backfill old rows in small batches with sleep between transactions, switch reads to the new table while maintaining sync, then stop dual-writing and drop the original. This eliminates writer stalls and provides safe rollback windows.

• •Most SQLite schema changes don't need full rebuilds—keep them additive with cheap native ALTER TABLE operations
• •For destructive changes, use phased migration: new table → dual-write → batch backfill with pauses → read switch → cleanup
• •Small batches (1000 rows) and sleep between transactions prevent multi-second writer stalls and provide safe rollback paths

Generated with AI, which can make mistakes.