Migrate your Production app from Room to SQLDelight

8 min readSep 18, 2023

Kotlin multiplatform (KMP), previously known as (KMM), is an interesting topic for many Android developers looking forward to making their Android app run on other platforms like iOS.

But one of the first issues they encounter is the popular libraries that are used in almost every Android application that turns out to not support KMP, which have to be replaced with pure kotlin libraries to be able to start your KMP journey, one of those libraries is Room which is used to easily manage your local database.

So in this case, your only option is to migrate to another pure kotlin library, and one of the good options out there is SQLDelight.

This Blog is your guide to migrating a production app with a real local database from Room to SQLDelight without losing the saved data.

Coming from a real experience of doing so in my open source project Prayer Companion

1. Implement SQLDelight

First, you should implement SQLDelight dependencies into your project.

In your app build.gradle.kts add these lines
refer to SQLDelight documentation for the correct versions

plugins {
  id("app.cash.sqldelight") version "2.0.0"
}

dependencies {
  implementation("app.cash.sqldelight:android-driver:2.0.0")
  implementation("app.cash.sqldelight:coroutines-extensions:2.0.0")
}

Then sync the project, and under dependencies add the following code to initiate the Database class, the database name can be any name you find suitable, this will be the name of the generated class that you will interact with in your code, and then your app package name

Note: name is not the name of the database file, this will be set later

sqldelight {
    databases {
        create("MyAppDatabase") {
            packageName.set("com.domain.app")
        }
    }
}

The package name can be found in the same grade file as the value of applicationId in the defaultConfig block

2. Create your tables

SQLDelight uses a different way of creating tables than Room, instead of creating an Entity class and the table is autogenerated, here you have to write the SQL code yourself, but it's simple and you get auto-complete.

Luckily coming from Room makes it easier, we already have the tables creation schema generated somewhere, to find it, search in your project double shift³ for a class named [your database class name]_Impl.java the database class name is the class that inherits RoomDatabase() in your project.

You will have to build your project first to see the generated class, and it will be under java (generated) directory

opening the generated class you’ll find all the table creation schemas!
In my case here is what I see for my project

Make sure to write the schemas in SQLDelight exactly as they are in Room, otherwise, you’ll most likely get errors.

Now to write the creation schemas for SQLDelight, open the project view, then under app -> src -> maincreate a new directory called sqldelight and then a directory inside called tables

In tables directory create a new file for each table with .sq extension.

The file will be for the table creation and the queries of that table example of a file name my_table.sq, the file will look something like this:

CREATE TABLE PrayersInfo (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    prayer TEXT NOT NULL,
    dateTime TEXT NOT NULL,
    status TEXT
);

insert:
INSERT INTO PrayersInfo VALUES(:id,:prayer,:dateTime,:status);

getPrayers:
SELECT * FROM PrayersInfo WHERE dateTime >= :startDateTime 
AND dateTime <= :endDateTime;

getPrayer:
SELECT * FROM PrayersInfo WHERE prayer = :prayer 
AND dateTime >= :startOfDay AND dateTime <= :endOfDay;

getPrayersStatusesByDate:
SELECT status FROM PrayersInfo WHERE dateTime >= :startDateTime 
AND dateTime <= :endDateTime AND prayer != :excludedPrayer;

updatePrayerStatus:
UPDATE PrayersInfo SET status = :status WHERE dateTime >= :startOfDay 
AND dateTime <= :endOfDay AND prayer = :prayer;

delete:
DELETE FROM PrayersInfo WHERE dateTime >= :startDateTime 
AND dateTime <= :endDateTime;

Table creation schema
Will only run once, you don’t need to worry about that
all your queries, in the below form
- Note: keywords are all caps, from won’t be recognized, it has to be written as FROM
- Note2: SQLDelight will generate kotlin functions to use in your project for all these queries

functionName:
Query;

Then build the project and you should be good to start replacing room usage.

2.1. Before replacing Room

if you’re working on a production app, you’re probably in a version number more than 1 now.

in SQLDelight, we don’t specify the version number like in Room,

SQLDelight generates the version number based on how many migration files there are + 1

To work that out, in the same sqldelight directory that we created above, create a new directory called migrations and create a file for each version that you’ve migrated from, in the following naming form

[version your migrating from].sqm

So if you are on version 3, you have to create 2 migration files 1.sqm and 2.sqmand SQLDelight will understand that you’re on version 3 now.

Make sure to write your migration statements in those files, similar to how manual migration worked with Room, except if you used AutoMigration in Room, then you will have to write that out as well

Refer to SQLDelight documentation if you had difficulties writing the migration

In my case, I created a new table when I migrated to version 2, so this is how 1.sqm was for me

3. Replace Database initialization

Find the place where you build your room databaseBuilder and replace it with SQLDelight, make sure to implement the Database class from the generated files (the Database class name will be the same name you wrote in build.gradle.kts file), not your old Room Database one

IMPORTANT: use the same exact database name (in quotation e.g. “prayer-companion”) as this is your database file name, otherwise SQLDelight will create a new one with empty data.

val driver: SqlDriver = AndroidSqliteDriver(PrayerCompanionDatabase.Schema, applicationContext, "prayer-companion")
return PrayerCompanionDatabase(driver)

Then delete your RoomDatabase class, you don’t need this anymore.

3.1. Migrate the Dao interface

for me, I wanted to keep everything in the project the same, so instead of replacing the interface with a class, I just created a new Dao class inheriting from my Dao interface

So for this Dao @Dao interface PrayersInfoDao I created a new class class PrayersInfoDaoImpl: PrayersInfo then I generate all the functions exactly as they are in the interface

in the constructor, you can inject/pass the generated Database class

class PrayersInfoDaoImpl @Inject constructor(
    db: PrayerCompanionDatabase
) : PrayersInfoDao { ... }

then get a reference for the table queries which is generated for you by SQLDelight

class PrayersInfoDaoImpl @Inject constructor(
    db: PrayerCompanionDatabase
) : PrayersInfoDao { 

    private val queries = db.prayersInfoQueries
}

then using queries is simple, the queries you wrote in .sq file are now ready to be used, to insert a row for example you will write something like this


override fun insert(prayerInfo: PrayerInfoEntity) {
    queries.insert(
        id = null,
        prayer = prayerInfo.prayer.name,
        dateTime = converters.localDateToString(prayerInfo.dateTime),
        status = prayerInfo.status.name
    )
}

Note: id is null so we can let SQLDelight do the auto-incrementing

But, there are some complications when working with queries

a. Reading rows

reading rows might not be intuitive, in the below case, calling getPrayers


override fun getPrayers(
    startDateTime: LocalDateTime,
    endDateTime: LocalDateTime
): List<PrayerInfoEntity> {
    return queries.getPrayers(
        startDateTime = converters.localDateToString(startDateTime),
        endDateTime = converters.localDateToString(endDateTime)
    )
}

will return a Query<PrayersInfo> while the expected result is a List<PrayerInfoEntity>, to first map the data class to my entity class, there are 2 query functions for each query, one like the above with only the needed parameters, and one with an extra mapper function parameter which you can use to map the data class to your entity class

*I always prefer not to use generated data classes as it becomes harder to change things in the future or add any helper functions

Using the second function will look something like this:
a normal mapper function to your preferred data class

override fun getPrayers(
    startDateTime: LocalDateTime,
    endDateTime: LocalDateTime
): List<PrayerInfoEntity> {
    return queries.getPrayers(
        startDateTime = converters.localDateToString(startDateTime),
        endDateTime = converters.localDateToString(endDateTime)
    ) { id, prayer, dateTime, status ->
        // mapping PrayersInfo to PrayerInfoEntity
        PrayerInfoEntity(
            id = id.toInt(),
            prayer = Prayer.valueOf(prayer),
            dateTime = converters.stringToLocalDate(dateTime),
            status = status?.let { status -> PrayerStatus.valueOf(status) }
                ?: PrayerStatus.None
        )
    }
}

And to turn Query<T> into a List<T> we call .executeAsList()


override fun getPrayers(
    startDateTime: LocalDateTime,
    endDateTime: LocalDateTime
): List<PrayerInfoEntity> {
    return queries.getPrayers(
        startDateTime = converters.localDateToString(startDateTime),
        endDateTime = converters.localDateToString(endDateTime)
    ) { id, prayer, dateTime, status ->
        PrayerInfoEntity(
            id = id.toInt(),
            prayer = Prayer.valueOf(prayer),
            dateTime = converters.stringToLocalDate(dateTime),
            status = status?.let { status -> PrayerStatus.valueOf(status) }
                ?: PrayerStatus.None
        )
    }.executeAsList()
    // transforming Query into List 👆
}

b. Read rows as Flow

To read rows from a table as flow make sure you’ve implemented the SQLDelight coroutines extensions dependency

implementation("app.cash.sqldelight:coroutines-extensions:2.0.0")

then instead of calling executeAsList we call asFlow() and then mapToList(DispatcherContext)

override fun getPrayersFlow(
    startDateTime: LocalDateTime,
    endDateTime: LocalDateTime
): Flow<List<PrayerInfoEntity>> {
    return queries.getPrayers(
        startDateTime = converters.localDateToString(startDateTime),
        endDateTime = converters.localDateToString(endDateTime)
    ) { id, prayer, dateTime, status ->
        PrayerInfoEntity(
            id = id.toInt(),
            prayer = Prayer.valueOf(prayer),
            dateTime = converters.stringToLocalDate(dateTime),
            status = status?.let { PrayerStatus.valueOf(it) } ?: PrayerStatus.None
        )
    }.asFlow().mapToList(Dispatchers.IO)
    // transform into a flow list 👆
}

c. Insert a list

we usually have an insertAll function to insert multiple rows at once, unfortunately, SQLDelight doesn’t support that out of the box, so we have to handle it ourselves.

In this case, we use a queries.transaction { } to insert all the rows or none in case of an error, which will look something like this:

override fun insertAll(prayersInfo: List<PrayerInfoEntity>) {
    queries.transaction {
        prayersInfo.forEach {
            queries.insert(
                id = null,
                prayer = it.prayer.name,
                dateTime = converters.localDateToString(it.dateTime),
                status = it.status.name
            )
        }
    }
}

And if you want to be informed about an error, you can add this lin

override fun insertAll(prayersInfo: List<PrayerInfoEntity>) {
    queries.transaction {
        // if the transaction failed and rolled back, throw an exception
        afterRollback { throw Exception("Failed to inserting prayers info ") }
        prayersInfo.forEach {
            queries.insert(
                id = null,
                prayer = it.prayer.name,
                dateTime = converters.localDateToString(it.dateTime),
                status = it.status.name
            )
        }
    }
}

Then all you have to do is

Remove all Room Annotations from your Dao interfaces
Remove all Room Annotations from your Entities data classes
Remove all Room dependencies from your gradle.build.kts files
inject/pass the new class [Dao]Impl to the repository that used to use the Dao interface, and everything should work exactly the same.

Run the app and make sure that all your data is preserved!

For the full code of the real app migration, here is a link to my PrayerCompanion² project PullRequest of migrating from Room to SQLDelight