Room database schema migration in Android app

Implementing Database Schema Migration (Room Migration) in Android Applications

User updates the app — and on first launch sees a white screen or crash. In Logcat: IllegalStateException: Room cannot verify the data integrity. Looks like you've changed schema but forgot to update the version number. Or worse: Migration didn't properly handle with loss of all local data. This is the classic mistake of improper Room migration implementation.

How Room Detects Schema Changes

Room stores a hash of the database schema. On each launch, it compares the hash of the compiled @Database with the hash stored in room_master_table. If they don't match — Room throws an exception if no suitable migration is found.

version in @Database — is not an arbitrary number. It's a contract: if schema changes, version must be incremented, and explicit Migration(fromVersion, toVersion) must be added.

Types of Changes and Their Migration

Adding a Column (Simple Case)

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE transactions ADD COLUMN category TEXT NOT NULL DEFAULT ''")
    }
}

NOT NULL DEFAULT '' — mandatory. SQLite doesn't allow adding a NOT NULL column without DEFAULT to an existing table with data.

Renaming a Column

SQLite doesn't support ALTER TABLE RENAME COLUMN until version 3.25.0. On Android API < 29 this is unavailable. Universal path — recreate the table:

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // 1. Create new table with correct column name
        db.execSQL("""
            CREATE TABLE transactions_new (
                id TEXT NOT NULL PRIMARY KEY,
                amount REAL NOT NULL,
                description TEXT NOT NULL DEFAULT '',
                created_at INTEGER NOT NULL
            )
        """)
        // 2. Copy data (old column 'note' → new 'description')
        db.execSQL("""
            INSERT INTO transactions_new (id, amount, description, created_at)
            SELECT id, amount, note, created_at FROM transactions
        """)
        // 3. Drop old table
        db.execSQL("DROP TABLE transactions")
        // 4. Rename new
        db.execSQL("ALTER TABLE transactions_new RENAME TO transactions")
    }
}

Table recreation — only reliable path for any structural changes across the entire Android API range.

Adding Table with Foreign Key

db.execSQL("""
    CREATE TABLE IF NOT EXISTS tags (
        id TEXT NOT NULL PRIMARY KEY,
        name TEXT NOT NULL,
        color INTEGER NOT NULL DEFAULT 0
    )
""")
db.execSQL("""
    CREATE TABLE IF NOT EXISTS transaction_tags (
        transaction_id TEXT NOT NULL,
        tag_id TEXT NOT NULL,
        PRIMARY KEY (transaction_id, tag_id),
        FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
        FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
    )
""")

Migration Chains and Skipped Versions

Room can build chains: if user hasn't updated from v1 to v3, Room will apply Migration(1,2) + Migration(2,3). But this only works if you registered all intermediate migrations.

Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .addMigrations(MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4)
    .build()

If you want to support direct 1→4 transition (faster, single SQL operation) — add Migration(1, 4) explicitly.

Testing Migrations

Room provides MigrationTestHelper for JUnit tests:

@RunWith(AndroidJUnit4::class)
class MigrationTest {
    @get:Rule val helper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        AppDatabase::class.java
    )

    @Test
    fun migrate1To2() {
        // Create v1 database
        helper.createDatabase(TEST_DB, 1).apply {
            execSQL("INSERT INTO transactions VALUES ('id1', 100.0, 'test', 1700000000)")
            close()
        }
        // Apply migration and check result
        val db = helper.runMigrationsAndValidate(TEST_DB, 2, true, MIGRATION_1_2)
        val cursor = db.query("SELECT description FROM transactions WHERE id = 'id1'")
        assertTrue(cursor.moveToFirst())
        assertEquals("", cursor.getString(0))
    }
}

Test for each migration — not optional, mandatory. MigrationTestHelper.runMigrationsAndValidate validates final schema against expected.

Exported JSON Schemas

Enable schema export in build.gradle:

android {
    defaultConfig {
        javaCompileOptions {
            annotationProcessorOptions {
                arguments += ["room.schemaLocation": "$projectDir/schemas".toString()]
            }
        }
    }
}

Room saves schemas/1.json, schemas/2.json — snapshots of schema for each version. They should be committed to repository. MigrationTestHelper uses them for validation. Without these files — migration testing is impossible.

Fallback to Destructive Migration

As last resort — only for dev builds or with explicit user consent:

.fallbackToDestructiveMigration() // erases all data and recreates database

In production this is unacceptable without user warning.

Work Scope

• Audit current schema and version history
• Write Migration objects for all schema changes
• Tests via MigrationTestHelper for each migration
• Setup JSON schema export
• Handle edge cases: empty tables, foreign keys, indexes, triggers

Timeline

1–2 simple migrations (adding columns): 0.5–1 day. Complex restructuring (renaming, table recreation, migration chains) with full test coverage: 2–3 days.