Compose Multiplatform in Action: Implementing SqlDelight for Cross-Platform Database in CMP

Compose Multiplatform (CMP)

In a CMP project
how can we implement cross-platform database operations?
SqlDelight offers a powerful solution

This article will introduce how to perform database operations
using SqlDelight in a cross-platform Android & iOS environment

To introduce SqlDelight to your project
add the following to your libs.version.toml file:

[versions]
sqldelight = "2.0.1"

[libraries]
sqldelight-android = { module = "app.cash.sqldelight:android-driver", version.ref = "sqldelight" }
sqldelight-native = { module = "app.cash.sqldelight:native-driver", version.ref = "sqldelight" }
sqldelight-coroutines-extensions = { module = "app.cash.sqldelight:coroutines-extensions", version.ref = "sqldelight" }
/** Choose one between extensions and runtime */
/** The main difference is that extensions provides commonly used flow and emit operations, while runtime doesn't */
sqldelight-runtime = { module = "app.cash.sqldelight:runtime", version.ref = "sqldelight" }

[plugins]
sqlDelight = { id = "app.cash.sqldelight", version.ref = "sqldelight" }

And add the plugin and dependencies in build.gradle.kts
This section requires implementations for both target platforms
CMP again provides a Kotlin-based solution
so you can directly add the corresponding dependencies
to androidMain, commonMain, and iosMain

androidMain.dependencies {

    implementation(libs.sqldelight.android)
}

commonMain.dependencies {

    implementation(libs.sqldelight.coroutines.extensions)
}

iosMain.dependencies {
    implementation(libs.sqldelight.native)
}

Also, in the same build.gradle.kts file
add the SqlDelight configuration under the kotlin section

This gradle configuration
can be understood as creating an operable AppDatabase class
in the test.your.package.db package
after compilation

kotlin {
      sqldelight {
        databases {
            create("AppDatabase") {
                packageName.set("test.your.package.db")
            }
        }
    }
}

Create .sq files in the commonMain/sqldelight/database directory:
You need to add a sqldelight folder in the above path
for the build process to successfully generate operable classes

The .sq files in SqlDelight
allow you to define tables using SQL commands
to create CRUD (Create, Read, Update, Delete) operations

Here’s an example .sq file:

CREATE TABLE VocabularyEntity (
 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL
 );

 insert:
 INSERT OR REPLACE INTO VocabularyEntity(id, name)
 VALUES(?,?);

 getAll:
 SELECT * FROM VocabularyEntity;

 updateName:
 UPDATE VocabularyEntity
 SET name = :name
 WHERE id IS :id;

 delete:
 DELETE FROM VocabularyEntity
 WHERE id IS :id;

As mentioned earlier
due to compatibility differences between Android and iOS platforms
table-related operation logic can be shared
but the DatabaseDriver for different targets may need separate implementations

Therefore
to create DatabaseDriver for different platforms
you can write it like this:

// in ../commonMain
expect class DatabaseDriverFactory {
    fun create(): SqlDriver
}

// in ../androidMain
actual class DatabaseDriverFactory(private val context: Context) : KoinComponent {

    actual fun create(): SqlDriver {
        return AndroidSqliteDriver(AppDatabase.Schema, context, "AppDataBase")
    }
}

// in ../iosMain
actual class DatabaseDriverFactory {
    actual fun create(): SqlDriver {
        return NativeSqliteDriver(AppDatabase.Schema, "AppDataBase")
    }
}

Key code explanation:

1. AppDatabase.Schema: This Schema is generated after configuring the above Build.gradle.kts
   and syncing.
   
   
2. AndroidSqliteDriver: The Android platform requires a context, so we include it in the actual class DatabaseDriverFactory constructor
   
   
3. NativeSqliteDriver: The SqliteDriver for iOS
   

As mentioned in previous days
when dealing with cross-platform content
you can create a platformModule to implement cross-platform injection needs
(If you’ve forgotten, go back to Using Koin for Dependency Injection in CMP)

Here’s an example:

// in ../commonMain
expect val platformModule: Module

// in ../androidMain
actual val platformModule: Module = module {

    single { DatabaseDriverFactory(get<Context>()) }
    single { AppDatabase(get<DatabaseDriverFactory>().create()) }
}

// in ../iosMain
actual val platformModule: Module = module {

    single { DatabaseDriverFactory() }
    single { AppDatabase(get<DatabaseDriverFactory>().create()) }
}

Key code explanation:

1. platformModule: In commonMain, we use expect to inform target platforms they need to implement the corresponding variable
   and in androidMain and iosMain, we need to implement the actual variable respectively
   
   

2. Therefore, in the target platforms android and iOS, we can use
   the previously completed AppDatabase and DatabaseDriverFactory
   along with koin for dependency injection
   

Here’s an example of obtaining AppDatabase and performing operations
SqlDelight will convert it into object-oriented methods for you to use

class LearningDataStore (private val db: AppDatabase) {
    private val queries = db.vocabularyEntityQueries

    fun insert(id: Long?, name: String) {
        queries.insert(id = id, name = name)
    }

    fun getAll() = queries.getAll().asFlow().mapToList(Dispatchers.IO)

    fun update(id: Long, name: String) {
        queries.updateName(id = id, name = name)
    }

    fun delete(id: Long) {
        queries.delete(id = id)
    }
}

This is an early solution provided by CMP for a local database
to share cross-platform db logic
However, it’s not very intuitive to use currently
as you need to write entire SQL commands yourself
But if it allows writing logic for both platforms at once
it’s still a good approach

Don’t worry, though
We’ll introduce a new approach using Room for cross-platform local databases later
which started being supported around May 2024
so you can check that out as well

In the next article
I’ll introduce another local database option, Room
After comparing both
you can decide which one to use

Compose Multiplatform in Action: Using ROOM for Cross-Platform Database Development & Troubleshooting