Migrating an Android SQLite Database to Room Without Data Loss

Android applications often keep records in a local SQLite database, especially when data must remain available without a network connection. A legacy SQLiteOpenHelper implementation can work well, but schema changes become risky when the app must preserve existing rows.

Room adds compile-time checks and a structured data access layer. Migrating from SQLite to Room does not require replacing every query immediately: the existing database file can become the backing store for a versioned Room database.

This example upgrades a version 1 tasks table to version 2 by adding a priority column. It then connects read, update, delete operations etc. to a Room DAO, giving the interface a predictable API.

Australian conditions make careful migration especially relevant. Apps used in Sydney, Melbourne, Brisbane or regional areas may encounter intermittent connectivity, while local users expect familiar date formats, Australian English and prices in AUD. A release should preserve offline data and handle those conventions without guesswork.

Why controlled migration matters

A schema evolution changes the structure beneath an application while users may still have valuable records on their devices. Adding a column, changing nullability or introducing another table can therefore affect code that previously worked without warning.

Changing the database version without registering a migration can trigger a destructive fallback. Room’s fallbackToDestructiveMigration() is useful for disposable caches, but it can erase tasks, preferences or synchronisation state. Production data normally needs an explicit upgrade path.

Before coding, record the current schema, expected row counts and important business rules. Confirm how new records should receive default values and whether deleted items must remain available for cloud synchronisation. These checks turn migration into a verifiable operation rather than a hopeful database replacement.

Match Room entities to SQLite

Inspect the legacy database before writing the Room layer. Use PRAGMA table_info(tasks) to check column types, nullability and defaults, and inspect primary keys, foreign keys and indices. The Room entity must match the migrated schema exactly, including column names and affinity.

A version 2 entity might look like this:

@Entity(tableName = "tasks")
data class Task(
    @PrimaryKey val id: Long,
    val title: String,
    val done: Boolean,
    val priority: Int
)

Room maps Kotlin Boolean to an integer column, while strings and numbers retain predictable SQLite types. The new priority field requires a default of 0 in both the entity and migration. Review every existing index and foreign key, because an ALTER TABLE statement does not automatically reproduce those database features.

Define Room data access operations

A DAO provides the CRUD boundary between the interface and stored records. It also lets Room verify SQL against the entity while the application is being compiled.

@Dao
interface TaskDao {
    @Query("SELECT * FROM tasks ORDER BY priority DESC, done ASC, id ASC")
    fun observeAll(): Flow<List<Task>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(task: Task): Long

    @Query(
        "UPDATE tasks SET title = :title, priority = :priority, " +
            "done = :done WHERE id = :id"
    )
    suspend fun update(id: Long, title: String, priority: Int, done: Boolean): Int

    @Query("DELETE FROM tasks WHERE id = :id")
    suspend fun delete(id: Long): Int
}

Place the DAO behind a repository when the interface needs caching, validation or synchronisation rules. Keep writes inside suspend functions so Room can perform them safely away from the main thread. Return the affected row count from updates and deletes, allowing the interface to distinguish success from an attempt against a missing record.

Register the schema migration path

Increment the Room database version and provide a Migration for every supported upgrade path. The following migration adds the column required by the version 2 entity:

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL(
            "ALTER TABLE tasks ADD COLUMN priority INTEGER NOT NULL DEFAULT 0"
        )
    }
}

Register it when building the database:

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

Avoid destructive fallback for persistent application data. If some devices could upgrade from version 1 and others from version 2, define both migrations so Room can construct the required path. Export Room schemas during the build and commit the generated JSON files; they provide the expected structure for migration tests and reveal unintended differences.

Read data after the upgrade

Use a Flow query when the interface needs to react whenever the table changes. This works well with a ListView, LazyColumn or another observable task list, and it avoids polling the database.

Collect the flow in a lifecycle-aware component, using collectAsStateWithLifecycle() in the interface layer. Stop collection when the screen is no longer active, while retaining the latest state in the ViewModel. Room performs the query asynchronously, so large tables do not block rendering.

For a one-time lookup, expose a suspending function and await its result. Apply filtering, sorting and pagination in SQL where practical. Read operations should also account for records created by older versions, particularly when a migration introduces a non-null column with a meaningful default.

Update and delete with predictable results

Updates should be narrow and idempotent. Changing a title, completion flag or priority through a query that targets the primary key avoids replacing an entire record and accidentally overwriting concurrent changes. Check the returned row count before reporting success.

Deletion requires an explicit product decision. A local-only task can be removed with the DAO’s delete() method. A synchronised task may need a tombstone or deletedAt field so other devices can receive the deletion. Adding that field requires another version, entity change and tested migration rather than an improvised database edit.

Use a Room transaction when an update affects multiple tables or must succeed as one unit. For example, marking a task complete might also write an activity record. A failed transaction then leaves both tables consistent instead of leaving a task updated without its associated history.

Migration testing and release recommendations

Use MigrationTestHelper to create a real version 1 database, insert representative records and run the migration into version 2. Validate both the resulting schema and the preserved values. Instrumented tests should cover empty tables, normal records, Unicode titles, completed tasks and the default assigned to new priorities.

Local release checks should include Australian date and currency behaviour. Store timestamps in UTC and format them for the relevant Australian time zone; Australia/Perth differs from Sydney and Melbourne during daylight-saving changes. Monetary values should use AUD and clearly represent Australian GST-inclusive pricing. Preserve this formatting when importing or synchronising records.

• Export and version-control the Room schema files for every database version.
• Automate migration tests from each supported version into the current release.
• Compare row counts and key values before and after each upgrade.
• Store timestamps in UTC while displaying dates in the user’s selected Australian zone.
• Test AUD and GST-inclusive prices separately from integer cents stored in SQLite.
• Stage the Android release through Google Play and monitor migration-related crashes.

A migration is ready for production when existing records survive, the Room schema matches SQLite, and read, update and delete behaviour remains consistent across supported Android devices and Australian locales.