Write Safe Migrations

Safe migrations are boring, repeatable, and tested against real old data.

Workflow

1. Add the new schema version.
2. Write the migration in one transaction when the platform allows it.
3. Preserve data before dropping or rewriting columns.
4. Add fixture data for the old version.
5. Test old version to latest, not just previous to current.
6. Run integrity and foreign key checks.
7. Take a backup before migrating high-value data.

Room Checklist

@Database(
    entities = [TransactionEntity::class],
    version = 3,
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase()

• keep exported schemas in version control
• avoid using app constants inside migration SQL because old constants drift
• test data preservation, not only schema validation
• use destructive migration only for cache/prototype data

SQLite Checklist

BEGIN IMMEDIATE;
ALTER TABLE notes ADD COLUMN archived INTEGER NOT NULL DEFAULT 0;
PRAGMA foreign_key_check;
UPDATE app_meta SET value = '3' WHERE key = 'schema_version';
COMMIT;

• set or read PRAGMA user_version or app metadata
• prefer additive changes when possible
• rebuild derived indexes after migration
• run quick_check or integrity_check after risky migrations

IndexedDB Checklist

• open an old-version DB fixture in the browser
• perform the version upgrade
• assert object counts and transformed fields
• test blocked upgrades with two tabs open
• keep readers backward-compatible during gradual deploys