Room Database Migration Strategies That Won't Break Production

fallbackToDestructiveMigration()

NOT NULL

@AutoMigration

// Migration from version 7 to 8: splitting user_preferences into two tables
val MIGRATION_7_8 = object : Migration(7, 8) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            CREATE TABLE user_settings_new (
                user_id INTEGER PRIMARY KEY NOT NULL,
                theme_mode INTEGER NOT NULL DEFAULT 0,
                notification_enabled INTEGER NOT NULL DEFAULT 1,
                language_code TEXT NOT NULL DEFAULT 'en',
                FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        """.trimIndent())

        database.execSQL("""
            INSERT INTO user_settings_new (user_id, theme_mode, notification_enabled, language_code)
            SELECT 
                id,
                CASE WHEN dark_mode = 1 THEN 2 ELSE 0 END,
                COALESCE(push_enabled, 1),
                COALESCE(locale, 'en')
            FROM users
        """.trimIndent())

        database.execSQL("DROP TABLE user_preferences")
        database.execSQL("ALTER TABLE user_settings_new RENAME TO user_settings")
    }
}

@RunWith(AndroidJUnit4::class)
class MigrationTest {
    private val TEST_DB = "migration-test"

    @get:Rule
    val helper: MigrationTestHelper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        AppDatabase::class.java.canonicalName,
        FrameworkSQLiteOpenHelperFactory()
    )

    @Test
    fun migrateAllVersions() {
        // Test every version jump from 1 to CURRENT_VERSION
        for (version in 1..AppDatabase.CURRENT_VERSION - 1) {
            val db = helper.createDatabase(TEST_DB, version).apply {
                // Seed realistic production data
                seedProductionData(this, version)
                close()
            }

            // Run migration
            val migratedDb = helper.runMigrationsAndValidate(
                TEST_DB, AppDatabase.CURRENT_VERSION, true, MIGRATIONS
            )

            // Verify data integrity post-migration
            assertDataIntegrity(migratedDb, version)
            migratedDb.close()
        }
    }

    private fun seedProductionData(db: SupportSQLiteDatabase, fromVersion: Int) {
        // Insert 10,000 rows with edge cases: nulls, max lengths, FK chains
        // This catches migrations that work on empty DBs but fail on real data
    }

    private fun assertDataIntegrity(db: SupportSQLiteDatabase, fromVersion: Int) {
        // Row counts match, no nulls in NOT NULL columns, FKs resolve
    }
}

val MIGRATION_5_6 = object : Migration(5, 6) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Check if migration already applied (idempotency)
        val columnExists = database.query("PRAGMA table_info(events)").use { cursor ->
            while (cursor.moveToNext()) {
                if (cursor.getString(1) == "sync_status") return@use true
            }
            false
        }

        if (!columnExists) {
            database.execSQL("ALTER TABLE events ADD COLUMN sync_status INTEGER NOT NULL DEFAULT 0")
            database.execSQL("CREATE INDEX idx_events_sync ON events(sync_status)")
        }
    }
}

val MIGRATION_8_9 = object : Migration(8, 9) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Phase 1: Create new structure, keep old data accessible
        database.execSQL("""
            CREATE TABLE analytics_events_v2 (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                event_name TEXT NOT NULL,
                properties_json TEXT NOT NULL DEFAULT '{}',
                timestamp_ms INTEGER NOT NULL,
                session_id TEXT,
                user_id INTEGER,
                FOREIGN KEY(user_id) REFERENCES users(id)
            )
        """.trimIndent())

        // Phase 2: Backfill with transformation
        database.execSQL("""
            INSERT INTO analytics_events_v2 (event_name, properties_json, timestamp_ms, session_id, user_id)
            SELECT 
                event_type,
                json_object('value', CAST(event_value AS TEXT), 'screen', screen_name),
                occurred_at,
                session_token,
                user_id
            FROM analytics_events
        """.trimIndent())

        // Phase 3: Atomic swap (SQLite doesn't support RENAME TABLE atomically with triggers)
        // So we keep both tables for one version, read from v2, write to v2
        // Phase 4 (v9→v10): Drop analytics_events after confirming zero reads
    }
}

locale TEXT

language_code TEXT NOT NULL DEFAULT 'en'

locale

language_code

val MIGRATION_12_13 = object : Migration(12, 13) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Step 1: Add new column with default
        database.execSQL("ALTER TABLE users ADD COLUMN language_code TEXT NOT NULL DEFAULT 'en'")

        // Step 2: Backfill with mapping logic
        database.execSQL("""
            UPDATE users SET language_code = CASE
                WHEN locale IN ('en', 'en_US', 'en_GB') THEN 'en'
                WHEN locale IN ('th', 'th_TH') THEN 'th'
                WHEN locale IN ('ja', 'ja_JP') THEN 'ja'
                WHEN locale IN ('zh', 'zh_CN', 'zh_TW') THEN 'zh'
                ELSE 'en'
            END
        """.trimIndent())

        // Step 3: Create index for new query patterns
        database.execSQL("CREATE INDEX idx_users_language ON users(language_code)")

        // Step 4: Keep locale column for v14 rollback safety
        // DROP COLUMN locale happens in MIGRATION_14_15
    }
}

locale

# .github/workflows/migration-check.yml
name: Migration Validation

on:
  pull_request:
    paths:
      - '**/db/**'
      - '**/Migration*.kt'

jobs:
  validate-migrations:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      
      - name: Set up JDK 21
        uses: actions/setup-java@v4
        with:
          distribution: 'temurin'
          java-version: '21'
      
      - name: Export current schema
        run: ./gradlew :app:exportSchema
      
      - name: Run migration tests
        run: ./gradlew :app:connectedDebugAndroidTest -Pandroid.testInstrumentationRunnerArguments.class=com.sudarshantechlabs.MigrationTest
      
      - name: Verify schema export committed
        run: |
          if ! git diff --quiet schemas/; then
            echo "::error::Schema export changed. Commit the new schema JSON."
            exit 1
          fi