From SQLDelight to Room in Kotlin Multiplatform Projects.

In this article, we will see what the transition from SQLDelight to Room looks like, two popular libraries for managing databases in Kotlin applications. Initially, it provides an overview of SQLDelight and Room, including their features, benefits, and usage examples. Subsequently, a detailed comparison between both libraries is presented, highlighting their similarities and differences. Finally, a step-by-step guide is provided for migrating a sample project from SQLDelight to Room, addressing aspects such as the definition of entities, the implementation of DAOs, and the database configuration.

What is SQLDelight?

SQLDelight is a library for SQL database access, designed for Kotlin Multiplatform (KMP). It is part of the ecosystem of tools developed by Cashapp and its objective is to simplify the management of SQLite databases in cross-platform applications. SQLDelight generates Kotlin code from SQL files, offering easy and secure integration with databases.

Features of SQLDelight

1. Code Generation: SQLDelight generates Kotlin code from SQL queries written in .sq files, which can be used directly in the application. This process minimizes the risk of errors in queries and facilitates their handling.
2. Multiplatform Compatibility: SQLDelight is fully compatible with Kotlin Multiplatform, allowing its use in projects that span various platforms such as Android, iOS, and more. This facilitates sharing data access logic between different platforms, reducing development and maintenance effort.
3. Safe Types: It generates safe type data classes, which improves the security and clarity of the code. Common errors such as misspelled column names or incorrect data types are detected during compilation.
4. Integration with Coroutines: SQLDelight integrates efficiently with Kotlin coroutines, allowing asynchronous database operations.

Benefits of Using SQLDelight in KMP

1. Code Reuse: SQLDelight allows sharing data access logic between different platforms, significantly reducing code duplication and development effort.
2. Simplified Maintenance: Having a single source of truth for SQL queries and data access logic makes database maintenance and updating simpler and less prone to errors.
3. Improved Performance: SQLDelight generates optimized code for database access, resulting in better performance compared to using generic data access libraries.
4. Security and Reliability: The generation of code from explicitly written SQL queries and their safe typing helps to create more secure and reliable applications.

Usage Example

Configuration

We need to configure the plugin and SQLDelight dependencies.

plugins {
    alias(libs.plugins.kotlinMultiplatform)
    alias(libs.plugins.androidLibrary)
    alias(libs.plugins.kotlinSerialization)
    alias(libs.plugins.skie)
    alias(libs.plugins.sqldelight) //Plugin of SQLDelight.
}

and then add the dependencies for both the common source and the platforms.

sourceSets {
    commonMain.dependencies {
        // common dependency for sqldelight
        implementation(libs.sqldelight.coroutines.extensions)
  
        //...
    }
    androidMain.dependencies {
        // sqldelight android driver
        implementation(libs.sqldelight.android.driver)
    }

    iosMain.dependencies {
      // sqldelight ios driver
        implementation(libs.sqldelight.ios.driver)
    }
}

Query Definition

A .sq file is defined with the necessary SQL queries.

import kotlin.Boolean;

CREATE TABLE CharacterEntity (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    image TEXT NOT NULL,
    favorite INTEGER AS Boolean DEFAULT 0
);

selectAllCharacter:
SELECT * FROM CharacterEntity;

selectFavoriteCharacters:
SELECT * FROM CharacterEntity WHERE favorite = 1;

insertCharacter:
INSERT INTO CharacterEntity(id,name,image)
VALUES(?, ?, ?);

selectCharacterById:
SELECT * FROM CharacterEntity
WHERE id = ?;

updateFavorite:
UPDATE CharacterEntity
SET favorite = ?
WHERE id = ? ;

deleteAll:
DELETE FROM CharacterEntity;

Code Generation

SQLDelight generates the corresponding Kotlin code to interact with the database.

First, we need to configure the Gradle plugin and add the following configuration.

sqldelight {
    databases {
        create("CharactersDatabase") {
            packageName.set("com.santimattius.kmp")
        }
    }

    linkSqlite.set(true)

}

Therefore, it will generate the CharactersDatabase class for us where we can configure depending on the platform controller.

//database.common.kt
const val DATABASE_NAME = "app_database.db"

expect class DriverFactory {
    fun createDriver(): SqlDriver
}

fun createDatabase(driver: SqlDriver): CharactersDatabase {
    return CharactersDatabase(driver)
}

//database.android.kt
actual class DriverFactory(private val context: Context) {
    actual fun createDriver(): SqlDriver {
        return AndroidSqliteDriver(
         CharactersDatabase.Schema, context, DATABASE_NAME
        )
    }
}

//database.ios.kt
actual class DriverFactory {
    actual fun createDriver(): SqlDriver {
        return NativeSqliteDriver(CharactersDatabase.Schema, DATABASE_NAME)
    }
}

Testing

To carry out tests on our database using SQLDelight, we simply have to define the driver type for each platform. In the case of Android and iOS, we will use an in-memory implementation.

//test.database.common.kt
expect fun testDbDriver(): SqlDriver

//test.database.android.kt
actual fun testDbDriver(): SqlDriver {
    return JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY)
        .also {
            CharactersDatabase.Schema.create(it)
        }
}
//test.database.ios.kt
actual fun testDbDriver(): SqlDriver {
    return inMemoryDriver(CharactersDatabase.Schema)
}

In the following article, you will learn how to test your code with SQLDelight.

KMP for Mobile Native Developers — Part.5: Testing

Multiplatform Use

This code can be used on the different platforms supported by the KMP project, ensuring a consistent implementation of database access.

SQLDelight is a powerful tool that simplifies database management in multiplatform applications developed with Kotlin, providing benefits in terms of security, performance, and code maintainability.

Getting Started - SQLDelight

What is Room?

Room is a data persistence library that is part of Android’s Jetpack toolkit. It is designed to provide an abstraction layer over SQLite, making the management of databases in Android applications easier through a robust and easy-to-use API. Room helps create and maintain SQLite databases in a safer and more efficient way, improving the developer experience and reducing the possibility of errors.

Room Features

1. SQLite Abstraction: Room provides an abstraction layer over SQLite, simplifying common database operations, such as queries, insertions, updates, and deletions.
2. Integration with Coroutines: Room natively integrates with Kotlin’s Coroutines, making it easier to perform asynchronous database operations and automatically updating the user interface when data changes.
3. Compile Time Query Verification: SQL queries and entities are verified at compile time, which helps detect errors in queries and data structures before running the application.
4. Migrations: Room facilitates the management of changes in the database schema through migrations, allowing the database structure to be updated without losing data.

In the latest version of Room currently in Alpha, the main feature added is KMP support.

Room release note

In this article, we will use this version to focus on Kotlin Multiplatform on Android and iOS.

Let’s see below what the components of Room are.

Main Components of Room

Entity: Represents a table in the database. Each entity is a data class annotated with @Entity

@Entity(tableName = "characters")
class CharacterEntity(
    @PrimaryKey @ColumnInfo(name = "id") val id: Long,
    @ColumnInfo(name = "name") val name: String,
    @ColumnInfo(name = "image") val image: String,
    @ColumnInfo(name = "favorite") val favorite: Boolean
)

DAO (Data Access Object): Define las operaciones de base de datos (consultas, inserciones, actualizaciones, eliminaciones) a través de métodos anotados con @Query, @Insert, @Update, y @Delete.

@Dao
interface CharacterDao {
    @Query("SELECT * FROM characters")
    fun getAll(): Flow<List<CharacterEntity>>

    @Query("SELECT * FROM characters WHERE favorite = true")
    fun getFavorite(): Flow<List<CharacterEntity>>

    @Query("SELECT * FROM characters WHERE id = :id")
    suspend fun findById(id: Long): CharacterEntity

    @Query("UPDATE characters SET favorite = :favorite WHERE id = :id")
    suspend fun updateFavorite(id: Long, favorite: Boolean)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(character: CharacterEntity)

    @Query("DELETE FROM characters")
    suspend fun deleteAll()

}

Database: Represents the application’s database and acts as the main access point to persisted data. It is defined by an abstract class that extends RoomDatabase and is annotated with @Database.

@Database(entities = [CharacterEntity::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun getCharacterDao(): CharacterDao
}

Example of use

Configuration

Before starting the implementation, we must configure the Room plugin and dependencies.

We will add the Room plugin and the Ksp one which is required for the Room compiler.

plugins {
    alias(libs.plugins.kotlinMultiplatform)
    alias(libs.plugins.androidLibrary)
    alias(libs.plugins.kotlinSerialization)
    alias(libs.plugins.skie)
    alias(libs.plugins.ksp)
    alias(libs.plugins.room)
}

In the dependencies of our commons module, we define the dependencies of room runtime and sqlite bundled.

sourceSets {
  //...
    commonMain.dependencies {
        //...
        implementation(libs.androidx.room.runtime)
        implementation(libs.sqlite.bundled)
    }
    //...
}

And to generate our code we will use the Room compiler by defining it in the dependencies at the general level of the gradle project.

dependencies {
    ksp(libs.androidx.room.compiler)
}

And finally, we will configure the scheme directory for our database.

room {
    schemaDirectory("$projectDir/schemas")
}

Important: To use SQLite on iOS, we need to indicate to our target that it includes the following linkOption.

listOf(
      iosX64(),
      iosArm64(),
      iosSimulatorArm64()
  ).forEach { iosTarget ->
      iosTarget.binaries.framework {
          baseName = "Shared"
          isStatic = true
          linkerOpts.add("-lsqlite3") // add sqlite
      }
  }

Now we are ready to start.

Database Definition

First, define the entities and the DAO, then create the database.

// Entity
@Entity(tableName = "characters")
class CharacterEntity(
    @PrimaryKey @ColumnInfo(name = "id") val id: Long,
    @ColumnInfo(name = "name") val name: String,
    @ColumnInfo(name = "image") val image: String,
    @ColumnInfo(name = "favorite") val favorite: Boolean
)

// DAO
@Dao
interface CharacterDao {
    @Query("SELECT * FROM characters")
    fun getAll(): Flow<List<CharacterEntity>>

    @Query("SELECT * FROM characters WHERE favorite = true")
    fun getFavorite(): Flow<List<CharacterEntity>>

    @Query("SELECT * FROM characters WHERE id = :id")
    suspend fun findById(id: Long): CharacterEntity

    @Query("UPDATE characters SET favorite = :favorite WHERE id = :id")
    suspend fun updateFavorite(id: Long, favorite: Boolean)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(character: CharacterEntity)

    @Query("DELETE FROM characters")
    suspend fun deleteAll()

}

// Database
@Database(entities = [CharacterEntity::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun getCharacterDao(): CharacterDao
}

Initialization and Use of the Database

An instance of the database is created and operations are performed through the DAO. But let’s see what our configuration would be like in Kotlin Multiplatform.

//room.common.kt
const val DATABASE_NAME = "app_database"

expect class RoomBuilder {
    fun builder(): RoomDatabase.Builder<AppDatabase>
}

fun getRoomDatabase(
    builder: RoomDatabase.Builder<AppDatabase>,
    dispatcher: CoroutineDispatcher = Dispatchers.IO
): AppDatabase {
    return builder
        .setQueryCoroutineContext(dispatcher)
        .build()
}

//room.android.kt
actual class RoomBuilder(
    private val appContext: Context
) {
    actual fun builder(): RoomDatabase.Builder<AppDatabase> {
        val appContext = appContext.applicationContext
        val dbFile = appContext.getDatabasePath(DATABASE_NAME)

        return Room.databaseBuilder<AppDatabase>(
            context = appContext,
            name = dbFile.absolutePath
        ).setDriver(BundledSQLiteDriver())
    }
}

//room.ios.kt
actual class RoomBuilder {
    actual fun builder(): RoomDatabase.Builder<AppDatabase> {
        val dbFilePath = NSHomeDirectory() + "/${DATABASE_NAME}"
        return Room.databaseBuilder<AppDatabase>(
            name = dbFilePath,
            factory = { AppDatabase::class.instantiateImpl() }
        ).setDriver(BundledSQLiteDriver())
    }
}

In KMP, to initialize Room, it is necessary to specify the specific SQLite Driver to use. For this, the Jetpack team developed a library that abstracts the implementation of SQLite to complement Room at this point.

SQLite (Kotlin Multiplatform) | Android Developers

Testing

Just like SQLDelight, we can use an in-memory implementation for tests in Room.

//test.room.common.kt
expect fun getInMemoryDataBase(): RoomDatabase.Builder<AppDatabase>

//test.room.android.kt
actual fun getInMemoryDataBase(): RoomDatabase.Builder<AppDatabase> {
    return Room.inMemoryDatabaseBuilder(
        ApplicationProvider.getApplicationContext(),
        AppDatabase::class.java
    )
}

//test.room.ios.kt
actual fun getInMemoryDataBase(): RoomDatabase.Builder<AppDatabase> {
    return Room.inMemoryDatabaseBuilder { AppDatabase::class.instantiateImpl() }
}

And replace this implementation during testing. You can see how to do this in the following article.

KMP for Mobile Native Developers — Part.5: Testing

Benefits of Using Room

1. Ease of Use: Simplifies database operations with a clear and concise API.
2. Security and Reliability: Verifies SQL queries at compile time, reducing common errors.
3. Integration with Android Components: Integrates seamlessly with LiveData and Coroutines, improving data management in the user interface.
4. Simple Maintenance: Facilitates schema migration management, ensuring a smooth database update.
5. Now features Multiplatform support. We can reuse our implementations that we have in Android with Room in iOS.

Room is a powerful and versatile tool for handling SQLite databases in Android applications and is now Multiplatform, providing a more efficient and secure way to interact with persisted data.

Room (Kotlin Multiplatform) | Android Developers

General Comparison

SQLDelight

• Origin and Ecosystem: Developed by Square. Part of the Square tool ecosystem.
• Multiplatform: Full support for Kotlin Multiplatform (KMP), allowing its use on Android, iOS, and other platforms.
• Code Generation: Generates Kotlin code from SQL files.
• Compilation Time Verification: SQL queries are verified at compile time.
• Integration with Coroutines: Support for Kotlin coroutines.

Room

• Origin and Ecosystem: Developed by Google as part of the Jetpack libraries.
• Multiplatform: Primarily designed for Android.
• SQLite Abstraction: Provides a high-level API for working with SQLite.
• Compilation Time Verification: SQL queries and entities are verified at compile time.
• Integration with LiveData and Coroutines: Native support for LiveData and Kotlin coroutines. LiveData no supported in KMP only Android.

Detailed Comparison

Similarities

• Compilation Time Verification: Both libraries verify the syntax and types of SQL queries at compile time, reducing runtime errors.
• Support for Coroutines: Both SQLDelight and Room support coroutines, facilitating the handling of database operations asynchronously.
• Abstraction over SQLite: They provide an abstraction layer over SQLite, simplifying common database operations.

Differences

• Multiplatform: SQLDelight has native support for Kotlin Multiplatform, allowing its use in applications that run on multiple platforms (Android, iOS, etc.), while Room is primarily focused on Android.
• Query API: SQLDelight allows defining SQL queries directly in .sq files, generating the corresponding code, while Room uses annotations in Kotlin classes to define queries and database operations.

Migration of the Sample Project.

Migrating from SQLDelight to Room involves several steps, including redefining entities, rewriting SQL queries, implementing DAOs, and configuring the database in Room. The process is detailed below with practical examples.

In our current example, we have the implementation of our LocalDataSource using SQLDelight.

interface CharacterLocalDataSource {

    val all: Flow<List<Character>>
    val favorites: Flow<List<Character>>
    suspend fun find(id: Long): Result<Character>
    suspend fun addToFavorite(id: Long): Result<Unit>
    suspend fun removeToFavorite(id: Long): Result<Unit>
    suspend fun insert(character: Character): Result<Unit>
    suspend fun clear(): Result<Unit>
}

internal class SQLDelightCharacterLocalDataSource(
    db: CharactersDatabase,
    private val dispatcher: CoroutineDispatcher = Dispatchers.IO,
) : CharacterLocalDataSource {

    private val queries = db.charactersDatabaseQueries

    override val all: Flow<List<Character>>
        get() = queries.selectAllCharacter()
            .asFlow()
            .mapToList(dispatcher)
            .map {
                it.asDomainsModels()
            }

    override val favorites: Flow<List<Character>>
        get() = queries.selectFavoriteCharacters()
            .asFlow()
            .mapToList(dispatcher)
            .map {
                it.asDomainsModels()
            }

    override suspend fun find(id: Long): Result<Character> {
        return runCatching {
            val entity = queries.selectCharacterById(id).executeAsOne()
            entity.asDomainModel()
        }
    }

    override suspend fun addToFavorite(id: Long): Result<Unit> = runCatching {
        queries.updateFavorite(favorite = true, id = id)
    }

    override suspend fun removeToFavorite(id: Long): Result<Unit> = runCatching {
        queries.updateFavorite(favorite = false, id = id)
    }

    override suspend fun insert(character: Character): Result<Unit> = runCatching {
        val (id, title, image) = character
        queries.insertCharacter(id, title, image)
    }

    override suspend fun clear(): Result<Unit> = runCatching {
        queries.deleteAll()
    }
}

Let’s see the step by step to migrate this implementation to Room as seen in the Room section.

Define Entities

In SQLDelight, tables and queries are defined in .sq files. To migrate to Room, you must first create the entities in Kotlin.

SQLDelight (.sq File)

import kotlin.Boolean;

CREATE TABLE CharacterEntity (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    image TEXT NOT NULL,
    favorite INTEGER AS Boolean DEFAULT 0
);

selectAllCharacter:
SELECT * FROM CharacterEntity;

selectFavoriteCharacters:
SELECT * FROM CharacterEntity WHERE favorite = 1;

insertCharacter:
INSERT INTO CharacterEntity(id,name,image)
VALUES(?, ?, ?);

selectCharacterById:
SELECT * FROM CharacterEntity
WHERE id = ?;

updateFavorite:
UPDATE CharacterEntity
SET favorite = ?
WHERE id = ? ;

deleteAll:
DELETE FROM CharacterEntity;

Room (Kotlin Entity)

@Entity(tableName = "characters")
class CharacterEntity(
    @PrimaryKey @ColumnInfo(name = "id") val id: Long,
    @ColumnInfo(name = "name") val name: String,
    @ColumnInfo(name = "image") val image: String,
    @ColumnInfo(name = "favorite") val favorite: Boolean
)

Define DAOs

Data Access Objects (DAO) in Room define the database operations through annotations in methods.

SQLDelight (Generated Kotlin Queries)

selectAllCharacter:
SELECT * FROM CharacterEntity;

selectFavoriteCharacters:
SELECT * FROM CharacterEntity WHERE favorite = 1;

insertCharacter:
INSERT INTO CharacterEntity(id,name,image)
VALUES(?, ?, ?);

selectCharacterById:
SELECT * FROM CharacterEntity
WHERE id = ?;

updateFavorite:
UPDATE CharacterEntity
SET favorite = ?
WHERE id = ? ;

deleteAll:
DELETE FROM CharacterEntity;

Room (Kotlin DAO)

@Dao
interface CharacterDao {
    @Query("SELECT * FROM characters")
    fun getAll(): Flow<List<LocalCharacter>>

    @Query("SELECT * FROM characters WHERE favorite = true")
    fun getFavorite(): Flow<List<LocalCharacter>>

    @Query("SELECT * FROM characters WHERE id = :id")
    suspend fun findById(id: Long): LocalCharacter

    @Query("UPDATE characters SET favorite = :favorite WHERE id = :id")
    suspend fun updateFavorite(id: Long, favorite: Boolean)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(character: LocalCharacter)

    @Query("DELETE FROM characters")
    suspend fun deleteAll()

}

Configure the Database

You must create a class that extends RoomDatabase to define the database.

SQLDelight (Database Initialization)

//database.common.kt
const val DATABASE_NAME = "app_database.db"

expect class DriverFactory {
    fun createDriver(): SqlDriver
}

fun createDatabase(driver: SqlDriver): CharactersDatabase {
    return CharactersDatabase(driver)
}

//database.android.kt
actual class DriverFactory(private val context: Context) {
    actual fun createDriver(): SqlDriver {
        return AndroidSqliteDriver(
         CharactersDatabase.Schema, context, DATABASE_NAME
        )
    }
}

//database.ios.kt
actual class DriverFactory {
    actual fun createDriver(): SqlDriver {
        return NativeSqliteDriver(CharactersDatabase.Schema, DATABASE_NAME)
    }
}

Room (Database Configuration)

//room.common.kt
const val DATABASE_NAME = "app_database"

expect class RoomBuilder {
    fun builder(): RoomDatabase.Builder<AppDatabase>
}

fun getRoomDatabase(
    builder: RoomDatabase.Builder<AppDatabase>,
    dispatcher: CoroutineDispatcher = Dispatchers.IO
): AppDatabase {
    return builder
        .setQueryCoroutineContext(dispatcher)
        .build()
}

//room.android.kt
actual class RoomBuilder(
    private val appContext: Context
) {
    actual fun builder(): RoomDatabase.Builder<AppDatabase> {
        val appContext = appContext.applicationContext
        val dbFile = appContext.getDatabasePath(DATABASE_NAME)

        return Room.databaseBuilder<AppDatabase>(
            context = appContext,
            name = dbFile.absolutePath
        ).setDriver(BundledSQLiteDriver())
    }
}

//room.ios.kt
actual class RoomBuilder {
    actual fun builder(): RoomDatabase.Builder<AppDatabase> {
        val dbFilePath = NSHomeDirectory() + "/${DATABASE_NAME}"
        return Room.databaseBuilder<AppDatabase>(
            name = dbFilePath,
            factory = { AppDatabase::class.instantiateImpl() }
        ).setDriver(BundledSQLiteDriver())
    }
}

Rewrite the Storage Logic

The logic that interacts with the database should be updated to use Room instead of SQLDelight.

SQLDelight (Storage Logic)

As we saw at the beginning our example has a specific implementation that uses SQLDelight:

internal class SQLDelightCharacterLocalDataSource(
    db: CharactersDatabase,
    private val dispatcher: CoroutineDispatcher = Dispatchers.IO,
) : CharacterLocalDataSource {

    private val queries = db.charactersDatabaseQueries

    override val all: Flow<List<Character>>
        get() = queries.selectAllCharacter()
            .asFlow()
            .mapToList(dispatcher)
            .map {
                it.asDomainsModels()
            }

    override val favorites: Flow<List<Character>>
        get() = queries.selectFavoriteCharacters()
            .asFlow()
            .mapToList(dispatcher)
            .map {
                it.asDomainsModels()
            }

    override suspend fun find(id: Long): Result<Character> {
        return runCatching {
            val entity = queries.selectCharacterById(id).executeAsOne()
            entity.asDomainModel()
        }
    }

    override suspend fun addToFavorite(id: Long): Result<Unit> = runCatching {
        queries.updateFavorite(favorite = true, id = id)
    }

    override suspend fun removeToFavorite(id: Long): Result<Unit> = runCatching {
        queries.updateFavorite(favorite = false, id = id)
    }

    override suspend fun insert(character: Character): Result<Unit> = runCatching {
        val (id, title, image) = character
        queries.insertCharacter(id, title, image)
    }

    override suspend fun clear(): Result<Unit> = runCatching {
        queries.deleteAll()
    }
}

By depending on the CharacterLocalDataSource interface, we can generate an implementation using Room for the migration

Room (Storage Logic)

Let’s see how our implementation of CharacterLocalDataSource with Room turns out.

class RoomCharacterLocalDataSource(
    private val characterDao: CharacterDao,
    private val dispatcher: CoroutineDispatcher = Dispatchers.IO
) : CharacterLocalDataSource {
    override val all: Flow<List<Character>>
        get() = characterDao.getAll()
            .map { it.toCharacters() }
            .flowOn(dispatcher)
    override val favorites: Flow<List<Character>>
        get() = characterDao.getFavorite()
            .map { it.toCharacters() }
            .flowOn(dispatcher)

    override suspend fun find(id: Long): Result<Character> = runSafe {
        characterDao.findById(id).toCharacter()
    }

    override suspend fun addToFavorite(id: Long): Result<Unit> = runSafe {
        characterDao.updateFavorite(id, true)
    }

    override suspend fun removeToFavorite(id: Long): Result<Unit> = runSafe {
        characterDao.updateFavorite(id, false)
    }

    override suspend fun insert(character: Character): Result<Unit> = runSafe {
        characterDao.insert(character.toLocalCharacter())
    }

    override suspend fun clear(): Result<Unit> = runSafe {
        characterDao.deleteAll()
    }

    private suspend fun <T> runSafe(block: suspend () -> T): Result<T> {
        return runCatching {
            withContext(dispatcher) { block() }
        }
    }
}

Once we have the implementation we can change it in our definition of dependencies, in this case we use Koin.

single<CharacterLocalDataSource>(named(DataBases.SQLDELIGHT)) {
    SQLDelightCharacterLocalDataSource(
        db = get<CharactersDatabase>()
    )
}
single<CharacterLocalDataSource>(named(DataBases.ROOM)) {
    RoomCharacterLocalDataSource(
        characterDao = get<AppDatabase>().getCharacterDao()
    )
}

single {
  // get<CharacterLocalDataSource>(named(DataBases.SQLDELIGHT))
    CharacterRepository(
        local = get<CharacterLocalDataSource>(named(DataBases.ROOM)),
        network = get<CharacterNetworkDataSource>()
    )
}

Handle Migrations

If your database schema has changed, you must handle migrations in Room to update the database structure without losing data.

SQLDelight (Automatic Migrations)

SQLDelight automatically generates the necessary migrations based on changes in .sq files.

Room (Manual Migrations)

In Room, you must manually define migrations if you change the schema.

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // migration code.
    }
}

//room.common.kt
const val DATABASE_NAME = "app_database"

expect class RoomBuilder {
    fun builder(): RoomDatabase.Builder<AppDatabase>
}

fun getRoomDatabase(
    builder: RoomDatabase.Builder<AppDatabase>,
    dispatcher: CoroutineDispatcher = Dispatchers.IO
): AppDatabase {
    return builder
        .setQueryCoroutineContext(dispatcher)
        .addMigrations(MIGRATION_1_2)
        .build()
}

Migration Process Summary

1. Define Entities: Convert SQLDelight table definitions to entities in Room.
2. Create the DAOs: Write Room DAOs to handle database operations.
3. Configure the Database: Create a RoomDatabase class and configure the database.
4. Update Storage Logic: Rewrite business logic to use Room instead of SQLDelight.
5. Handle Migrations: Define migrations if the database schema changes.

The migration from SQLDelight to Room may seem like a long process, but by following these steps and with clear examples, it can be done effectively.

Data layer | Android Developers

Conclusion

• SQLDelight is an excellent option if you are developing a multiplatform application with Kotlin Multiplatform and prefer to write SQL queries directly.
• Room is ideal if you are developing exclusively for Android or already have a developed application and plan to support other platforms at some point. This will allow you to reuse your existing data layer.

The choice between SQLDelight and Room will depend on your specific needs and the context of your project.

GitHub - santimattius/kmp-for-mobile-native-developers at extra-from-sqldelight-to-room

From Room to SQLDelight

If you are thinking about using SQLDelight, I recommend the following article that explains how to perform the migration in the other direction, so you can have another approach.

Migrate your Production app from Room DB to SQLDelight

References

• https://developer.android.com/kotlin/multiplatform/room
• https://cashapp.github.io/sqldelight/2.0.1/multiplatform_sqlite/
• https://developer.android.com/topic/architecture/data-layer