skip to content

In Room 3.0, how does writing a Migration or AutoMigrationSpec callback differ from Room 2.x, and how do you run SQL in it?

level: middleimportance: should knowfreq 35%

answer

  1. no SupportSQLite types
  2. suspend callback
  3. driver connection, prepared statements
  4. same change in database callbacks

basics

~10 s

In Room 3.0, Migration.migrate is an abstract suspend function that receives an androidx.sqlite SQLiteConnection instead of a SupportSQLiteDatabase. You run statements with execSQL and read with prepare and step; AutoMigrationSpec.onPostMigrate changed the same way.

solid answer

~40 s

Room 3.0 dropped the `SupportSQLite` APIs, so a migration now overrides `override suspend fun migrate(connection: SQLiteConnection)`, which is abstract, and `AutoMigrationSpec.onPostMigrate(connection)` and the `RoomDatabase.Callback` functions take a `SQLiteConnection` too. There is no `Cursor`, no `ContentValues` and no `db.insert(...)` helper: you run DDL and writes with `connection.execSQL(sql)` and read with `connection.prepare(sql).use { stmt -> stmt.step(); stmt.getLong(0) }`, binding arguments with `bindLong` or `bindText` from index 1. The callback still runs inside Room's migration transaction and still cannot touch DAOs. Room 2.7.0 already added a `SQLiteConnection` overload; without a driver its default forwards to the `SupportSQLiteDatabase` version, and once `setDriver()` is used only the connection form works. In 3.0 it is the only form.

code

kotlin · 24 lines
kotlin
import androidx.room3.migration.Migration
import androidx.sqlite.SQLiteConnection
import androidx.sqlite.execSQL

val MIGRATION_6_7 = object : Migration(6, 7) {
    override suspend fun migrate(connection: SQLiteConnection) {
        connection.execSQL(
            "CREATE TABLE IF NOT EXISTS goals (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                "kind TEXT NOT NULL, target INTEGER NOT NULL)"
        )
        val workoutCount = connection.prepare("SELECT COUNT(*) FROM workouts").use { stmt ->
            stmt.step()
            stmt.getLong(0)
        }
        if (workoutCount > 0) {
            connection.prepare("INSERT INTO goals (kind, target) VALUES (?, ?)").use { stmt ->
                stmt.bindText(1, "weekly_minutes")
                stmt.bindLong(2, 150)
                stmt.step()
            }
        }
    }
}

go deeper

for a junior

Remember that Room 3.0 migrations override a suspend migrate() taking a SQLiteConnection, not a SupportSQLiteDatabase.

for a middle

Explain execSQL versus prepare, step and the get functions, 1-based binding, and why the callbacks became suspend functions.

for a senior

Point out the 2.x trap: with setDriver() only the connection overload runs, so old migrations throw NotImplementedError until rewritten.

for a principal

Sequence the work: port migrations and callbacks to the connection form while still on 2.x with a driver, so the later 3.0 move only changes imports.

## Why the signature changed Room 3.0 (package `androidx.room3`) is built entirely on the **`SQLiteDriver` APIs** from `androidx.sqlite`. Every type that came from the older `SupportSQLite` layer, such as `SupportSQLiteDatabase` and Android's `Cursor`, is gone from Room's runtime API (a separate wrapper artifact keeps a bridge for app code). The release notes list the callbacks that changed as a result: the migration callbacks and the database callbacks now receive a **`SQLiteConnection`**. Because Room 3.0 also targets web platforms, where database work is asynchronous, those callbacks became **suspend functions**. ## The callbacks, side by side | Callback | Room 2.x | Room 3.0 | |---|---|---| | Hand-written migration | `open fun migrate(db: SupportSQLiteDatabase)`; since 2.7.0 also `open fun migrate(connection: SQLiteConnection)` | `abstract suspend fun migrate(connection: SQLiteConnection)` | | Lambda factory | `Migration(start, end) { db: SupportSQLiteDatabase -> }` | `Migration(start, end) { connection: SQLiteConnection -> }` with a suspend lambda | | Auto-migration hook | `AutoMigrationSpec.onPostMigrate(db)`; since 2.7.0 also a `SQLiteConnection` overload | `suspend fun onPostMigrate(connection: SQLiteConnection)` | | Database callbacks | `RoomDatabase.Callback.onCreate(db)`, `onOpen(db)`, `onDestructiveMigration(db)` | the same three, suspend, on a `SQLiteConnection` | What did **not** change: the constructor's `startVersion` and `endVersion`, `addMigrations()` on the builder, the path-finding rule, the transaction around the chain, and the schema validation afterwards. ## Running SQL on a SQLiteConnection The connection is a lower-level object than `SupportSQLiteDatabase`. The working vocabulary: - **`connection.execSQL(sql)`**, an extension in `androidx.sqlite`, runs a statement that returns no rows: `ALTER TABLE`, `CREATE TABLE`, `UPDATE`, `INSERT ... SELECT`. - **`connection.prepare(sql)`** returns a `SQLiteStatement`. You bind arguments with `bindLong`, `bindText`, `bindDouble`, `bindBlob` or `bindNull`, using 1-based indices, call `step()` to execute or advance a row, read columns with `getLong(i)` or `getText(i)`, and close it, usually with `use { }`. - For code that must also run on web targets, `androidx.sqlite` offers suspend variants such as `executeSQL`; on Android the synchronous forms work inside the suspend `migrate()`. There is no `query()` returning a `Cursor`, and no `insert(table, conflict, ContentValues)`: data fix-ups become plain SQL or prepared statements. ## The rules that stay 1. **No DAOs.** The KDoc still says a migration cannot access generated DAOs; they target the latest schema. 2. **Already in a transaction.** `migrate()` is called inside the transaction Room opened for the whole chain, so a thrown exception rolls back every step. 3. **Validation follows.** After the last step Room checks the tables against the entities and throws "Migration didn't properly handle" on a mismatch. ## The 2.x halfway house Room 2.7.0 added the `SQLiteConnection` overloads so apps could move to drivers before 3.0. The rule in 2.x is easy to trip over: - **No driver set:** Room passes a compatibility connection, and the default `migrate(SQLiteConnection)` forwards it to `migrate(SupportSQLiteDatabase)`, so existing migrations keep working. - **Driver set with `setDriver()`:** only `migrate(SQLiteConnection)` can do the work. A migration that overrides just the old function throws `NotImplementedError` ("Migration functionality with a provided SQLiteDriver requires overriding the migrate(SQLiteConnection) function"). So switching a 2.x app to a driver means rewriting its migrations to the connection form first, which is also the form Room 3.0 needs. ## Porting a migration - Change the import to `androidx.room3.migration.Migration` and the signature to `override suspend fun migrate(connection: SQLiteConnection)`. - Replace `db.execSQL(...)` with `connection.execSQL(...)`; the SQL text itself does not change. - Replace any `db.query(...)` cursor loop with `prepare()`, `step()` and the `get*` functions. - Move `onPostMigrate` and `RoomDatabase.Callback` overrides to the suspend connection form. - Re-run the migration tests; the schema files and versions are unaffected. ## Common porting mistakes - **Leaving a Room 2.x import.** A class extending `androidx.room.migration.Migration` is a different type from `androidx.room3.migration.Migration`, so a Room 3.0 builder's `addMigrations()` will not accept it. - **Not closing statements.** A `SQLiteStatement` from `prepare()` holds native resources; wrap it in `use { }` so it is closed even when a step throws. - **Expecting one `step()` to read everything.** `step()` advances to the next row; a multi-row read loops with `while (stmt.step()) { ... }`. - **Mixing up indices.** Binding starts at 1, while `getLong(0)` reads the first result column. - **Launching coroutines inside `migrate()`.** It is already a suspend function running within Room's transaction; work started in another scope may run outside it. Moving the rest of the app to `androidx.room3` (the driver on the builder, KSP, the `room3-sqlite-wrapper` bridge for code that still wants a `SupportSQLiteDatabase`) is a separate topic in the lesson on Room's entities and the 3.0 move.

  • A Room 2.8.5 app adds setDriver(BundledSQLiteDriver()) to its builder, and upgrading users now crash with NotImplementedError. Why?
    With a driver set, Room 2.x calls only `migrate(SQLiteConnection)`. The app's migrations override just `migrate(SupportSQLiteDatabase)`, and the default connection overload throws `NotImplementedError` when the connection is not the compatibility wrapper. Rewrite each migration to override the connection form; that is also the form Room 3.0 requires.
  • In Room 3.0, where do you put a data fix-up that must run after an auto-migration?
    In the `AutoMigrationSpec` referenced by the `@AutoMigration`, by overriding `suspend fun onPostMigrate(connection: SQLiteConnection)`. Room calls it after the generated migration's statements, still inside the migration transaction, and you run the fix-up with `connection.execSQL(...)` or prepared statements.

  • Room 3.0 migrations still receive a SupportSQLiteDatabase
  • You can read rows in a Room 3.0 migration with a Cursor
  • A suspend migrate() should launch its own coroutine scope for the SQL
  • Room 2.x migrations keep working unchanged after adding setDriver()
  • Room 3.0 changed how migration paths are chosen

Assumes Room 3.0 (androidx.room3), where migration and database callbacks are suspend functions on a SQLiteConnection; Room 2.x uses SupportSQLiteDatabase, with SQLiteConnection overloads since 2.7.0 that become required once a driver is set.