myougaTheAxo

Posted on Mar 2

Room Database Migrations: Changing Your Schema Without Losing User Data

#android #kotlin #database #tutorial

Introduction

Room Database is Android's abstraction layer over SQLite, providing compile-time checked SQL queries and reactive data flow. As your app evolves, your database schema will inevitably change: adding new columns, changing data types, removing obsolete fields. This is where migrations come in.

Database migrations are one of the trickiest parts of app development. Mishandled migrations can corrupt user data, cause app crashes in production, or result in data loss. In this guide, we'll explore Room's migration system, from basic column additions to advanced schema changes, and how to test them properly.

Why Database Migrations Matter

When you release your app to production and users install it, their local databases are in a specific state defined by your app's schema. When you push an update with schema changes, Room needs to know how to transform the old database to the new one without losing user data.

Consider this scenario:

Version 1 of your app: Users table has id and name columns
Version 2 of your app: You want to add an email column

If you simply change your Entity definition, Room will see the schema mismatch and throw a crash on app startup. Without a migration, users can't upgrade your app. With a migration, their existing data stays intact while the schema evolves.

The Room Entity and Schema Version

Let's start with the basics. Every Room database has a schema version number:

@Database(
    entities = [User::class],
    version = 1,
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

When you update your schema, increment the version number. Room uses this number to determine which migrations to apply:

@Database(
    entities = [User::class],
    version = 2,  // Updated from 1 to 2
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

Important: Always set exportSchema = true. This generates JSON schema files in your build/ directory, which you should commit to version control. These serve as documentation and help you write accurate migrations.

Writing Your First Migration

A migration is a class that extends Migration and implements the upgrade logic:

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Add new email column with default value
        database.execSQL(
            "ALTER TABLE User ADD COLUMN email TEXT NOT NULL DEFAULT ''"
        )
    }
}

You then add this migration to your database instance:

val db = Room.databaseBuilder(
    context,
    AppDatabase::class.java,
    "myapp.db"
)
    .addMigrations(MIGRATION_1_2)
    .build()

Room will apply migrations automatically in order. When a user upgrades from version 1 to version 2, Room detects the schema mismatch, finds that a migration exists from 1→2, and applies it.

Common Migration Patterns

Adding a Column

The simplest migration:

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(
            "ALTER TABLE User ADD COLUMN phone TEXT DEFAULT NULL"
        )
    }
}

SQLite's ALTER TABLE is limited, but adding columns is always safe. Provide a DEFAULT value or allow NULL to avoid issues with existing rows.

Renaming a Column

SQLite's ALTER TABLE RENAME COLUMN was added in SQLite 3.25 (Room 2.1+). Older versions require a workaround:

val MIGRATION_3_4 = object : Migration(3, 4) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Create new table with renamed column
        database.execSQL("CREATE TABLE User_new (id INTEGER PRIMARY KEY, name TEXT, email_address TEXT)")
        // Copy data
        database.execSQL("INSERT INTO User_new SELECT id, name, email FROM User")
        // Drop old table and rename
        database.execSQL("DROP TABLE User")
        database.execSQL("ALTER TABLE User_new RENAME TO User")
    }
}

Removing a Column

Also requires the table recreation pattern:

val MIGRATION_4_5 = object : Migration(4, 5) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("CREATE TABLE User_new (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")
        database.execSQL("INSERT INTO User_new SELECT id, name, email FROM User")
        database.execSQL("DROP TABLE User")
        database.execSQL("ALTER TABLE User_new RENAME TO User")
    }
}

Adding a Foreign Key Constraint

val MIGRATION_5_6 = object : Migration(5, 6) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("CREATE TABLE Address (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, street TEXT, FOREIGN KEY(user_id) REFERENCES User(id))")
    }
}

Destructive Migrations

A destructive migration loses data. Examples:

Removing a column with important data
Truncating a table
Changing a column's data type (often requires recreating the table with conversion logic)

Room does NOT apply destructive migrations by default, throwing an error instead:

Schema is not a valid Room database. The following problems were detected:
Migration didn't properly handle...

If you're certain a destructive migration is acceptable (perhaps for development or a feature where data loss is acceptable), enable it:

val db = Room.databaseBuilder(
    context,
    AppDatabase::class.java,
    "myapp.db"
)
    .addMigrations(MIGRATION_5_6)
    .fallbackToDestructiveMigrationOnDowngrade()  // Beware!
    .build()

Warning: Never ship this to production without careful consideration. Users will lose data.

Automatic Migrations (Room 2.4+)

Room 2.4 introduced automatic migrations, which generate migration code for simple schema changes like adding columns:

@Database(
    entities = [User::class],
    version = 3,
    autoMigrations = [
        AutoMigration(from = 2, to = 3)
    ]
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

For complex changes, you can specify spec classes:

class MIGRATION_2_3 : AutoMigrationSpec {
    override fun onPostMigrate(database: SupportSQLiteDatabase) {
        // Custom logic after auto-generated SQL
        database.execSQL("UPDATE User SET updated_at = datetime('now')")
    }
}

@Database(
    entities = [User::class],
    version = 3,
    autoMigrations = [
        AutoMigration(from = 2, to = 3, spec = MIGRATION_2_3::class)
    ]
)
abstract class AppDatabase : RoomDatabase() { }

Automatic migrations reduce boilerplate but only work for non-destructive changes.

Testing Migrations

Always test migrations before shipping:

@RunWith(AndroidJUnit4::class)
class MigrationTest {
    private lateinit var testHelper: MigrationTestHelper

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

    @Test
    fun testMigrationFrom1To2() {
        // Create v1 database
        var db = helper.createDatabase(TEST_DB_NAME, 1)
        db.execSQL("INSERT INTO User VALUES (1, 'Alice')")
        db.close()

        // Run migration
        db = helper.runMigrationsAndValidate(
            TEST_DB_NAME,
            2,
            validateDroppedTables = true,
            MIGRATION_1_2
        )

        // Verify results
        val cursor = db.query("SELECT * FROM User WHERE id = 1")
        cursor.moveToFirst()
        assert(cursor.getString(1) == "Alice")  // Name preserved
        assert(cursor.isNull(2))  // Email is NULL
        db.close()
    }
}

This test:

Creates a v1 database with test data
Applies the migration to v2
Validates the schema matches your entities
Verifies data integrity

Best Practices

Version your schema: Always increment version when changing entities. Never skip versions.
Provide sensible defaults: When adding columns, use DEFAULT values to avoid NULL issues.
Test all migrations: Use MigrationTestHelper for every migration path. Test edge cases.
Commit schema JSON files: Check in schemas/ directory so you can generate migrations from diffs.
Chain migrations carefully: If jumping from v1 to v5, Room applies v1→2, v2→3, v3→4, v4→5 in order. Ensure they're backward compatible.
Document schema changes: Add comments explaining why a migration exists and what it does.
Avoid destructive changes in production: If you must remove a column, handle it gracefully. Consider deprecation periods.
Use transactions: Room applies all migrations in a transaction. If one fails, the entire set rolls back.

Conclusion

Database migrations are essential for shipping app updates with schema changes. Room provides a robust migration system through explicit Migration classes and (in 2.4+) automatic migrations. By testing migrations thoroughly and following best practices, you can evolve your app's data model confidently.

My 8 templates use Room with migration-ready schemas. https://myougatheax.gumroad.com

DEV Community