SQLDelight — Shared Data Storage in KMP Explained

You’ll learn how to set up SQLDelight in KMP projects, allowing you to share your data-storing logic among multiple platforms. “Write once, run everywhere”

Here's a link to the GitHub repo for those who want to analyze the code.

GitHub - AndroBrain/SQLDelightExample

1. Declare the dependencies

Open gradle/libs.versions.toml and required libraries along with the version, which you can find here: https://github.com/cashapp/sqldelight

[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-jvm = { module = "app.cash.sqldelight:sqlite-driver", version.ref = "sqldelight" }
sqldelight-coroutines = { module = "app.cash.sqldelight:coroutines-extensions", version.ref = "sqldelight" }

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

Sync the project and declare these libraries in your build.gradle.kts shared module. You need to declare a dependency for each platform.

plugins {
    alias(libs.plugins.sqlDelight)
}

androidMain.dependencies {
    implementation(libs.sqldelight.android)
}

commonMain.dependencies {
    implementation(libs.sqldelight.coroutines)
}

desktopMain.dependencies {
    implementation(libs.sqldelight.jvm)
}

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

2. Declare your databases

After syncing your project, you should be able to declare the database. In this example, I’m going to use a single database.

kotlin {  
    sqldelight {
        databases {
            create("YourDatabaseName") {
                packageName = "com.example"
            }
        }
    }
}

3. Install the SQLDelight plugin

SQLDeligh plugin by Square

4. Write your first SQL script

Create the following path:

Required path

Use the plugin to create SqlDelight File/Table

Choose Table to create a table

In the file, you’re able to write SQL, for example, declare table fields

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

It’s a place where you’ll also write all the operations. You can declare parameters using “:parameterName”. Here are examples of basic CRUD operations:

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

getAll:
SELECT * FROM ExampleEntity;

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

delete:
DELETE FROM ExampleEntity
WHERE id IS :id;

5. Use type-safe queries and entities

Then, you can use the SQLDelight queries inside the DataSource in the common main. To see the query, you might have to build the project (Crtl+F9 by default)

class ExampleDataSource(private val db: YourDatabaseName) {
    private val queries = db.exampleEntityQueries

    //    Set id = null to let SQLDelight autogenerate the id
    fun insert(id: Long?, name: String) {
        queries.insert(id = id, name = name)
    }

    // If you've added the coroutines extensions you'll be able to use asFlow()
    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)
    }
}

6. Create DatabaseDriverFactory

Then, in your common main, create a class responsible for creating the database.

import app.cash.sqldelight.db.SqlDriver

expect class DatabaseDriverFactory {
    fun create(): SqlDriver
}

Create actual classes for all the platforms:

// Android main
actual class DatabaseDriverFactory(private val context: Context) {
    actual fun create(): SqlDriver =
        AndroidSqliteDriver(YourDatabaseName.Schema, context, "DATABASE_NAME")
}

// Desktop main
actual class DatabaseDriverFactory {
    actual fun create(): SqlDriver {
        val driver: SqlDriver = JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY)
        YourDatabaseName.Schema.create(driver)
        return driver
    }
}

// iOS main
actual class DatabaseDriverFactory {
    actual fun create(): SqlDriver =
        NativeSqliteDriver(YourDatabaseName.Schema, "DATABASE_NAME")
}

7. Create the database

To create the database driver, you need to provide it from each platform. I recommend using some dependency injection.

// Shared
interface AppModule {
    fun provideExampleDataSource(): ExampleDataSource
}

// Android
class AppModule(
    private val context: Context,
) : AppModule {
    private val db by lazy {
        YourDatabaseName(
            driver = DatabaseDriverFactory(context).create()
        )
    }

    fun provideExampleDataSource() = ExampleDataSource(db)
)


// iOS, Desktop
class AppModule {
    private val db by lazy {
        YourDatabaseName(
            driver = DatabaseDriverFactory().create()
        )
    }
    
    fun provideExampleDataSource() = ExampleDataSource(db)
}

The initial setup is time-consuming, but once you’ve done it, you can use shared logic for data storage.

Congratulations! You’ve just learned to set up and use SQLDelight in KMP projects.

Get the full project code here:

GitHub - AndroBrain/SQLDelightExample

Based on:

Getting Started - SQLDelight