Configure SQLDelight for data storage

In the world of mobile development, databases are often used for local data storage on client devices. One of the options for working with databases in Kotlin Mobile Multiplatform (KMM) projects is the SQLDelight library. It generates type-safe Kotlin APIs from SQL statements for various relational databases. SQLDelight also provides a multiplatform implementation of the SQLite driver. For a description of SQLDelight features and other details, see the SQLDelight documentation.

In this article, we’ll show how to start using databases in your KMM project with SQLDelight:

connect SQLDelight to your project
create a database driver
perform database queries using the API generated by SQLDelight

Connect and configure SQLDelight

Gradle plugin

To connect the SQLDelight plugin to a project, apply the SQLDelight Gradle plugin in your project’s build script (root build.gradle or build.gradle.kts ): First, add the plugin's classpath to the build system:

buildscript {
    repositories {
        google()
        mavenCentral()
    }
    dependencies {
        classpath "com.squareup.sqldelight:gradle-plugin:$sql_delight_version"
    }
}

buildscript {
    repositories {
        google()
        mavenCentral()
    }
    dependencies {
        classpath("com.squareup.sqldelight:gradle-plugin:$sql_delight_version")
    }
}

Instead of $sql_delight_version, use the version you need for your project.

Then apply the SQLDelight Gradle plugin by adding this line at the beginning of the build script (build.gradle or build.gradle.kts) in your shared multiplatform module:

apply plugin: 'com.squareup.sqldelight'

plugins {
    id("com.squareup.sqldelight")
}

Database drivers

Common source set

To work with database drivers in the common code, add the following dependency to the commonMain source set:

commonMain {
    dependencies {
        implementation "com.squareup.sqldelight:runtime:$sql_delight_version"
    }
}

val commonMain by getting {
    dependencies {
        implementation("com.squareup.sqldelight:runtime:$sql_delight_version")
    }
}

Android source sets

To connect the SQLite database driver for Android, add the following to the dependencies block of the corresponding source set in the module's build.gradle or build.gradle.kts:

androidMain {
    dependencies {
        implementation "com.squareup.sqldelight:android-driver:$sql_delight_version"
    }
}

val androidMain by getting {
    dependencies {
        implementation("com.squareup.sqldelight:android-driver:$sql_delight_version")
    }
}

iOS source sets

To connect the SQLite driver for iOS and other native platforms, add the following dependency:

iosMain {
    dependencies {
        implementation "com.squareup.sqldelight:native-driver:$sql_delight_version"
    }
}

val iosMain by getting {
    dependencies {
        implementation("com.squareup.sqldelight:native-driver:$sql_delight_version")
    }
}

Configuration

To configure the SQLDelight API generator, use the sqldelight top-level block of the build script. For example, to create a database named AppDatabase and specify the package name com.example.db for the generated Kotlin classes, use this configuration block:

sqldelight {
    AppDatabase {
        packageName = "com.example.db"
    }
}

sqldelight {
    database("AppDatabase") {
        packageName = "com.example.db"
    }
}

This SQLDelight configuration will be used for all the code examples listed below.

To learn what you can configure in SQLDelight and how to do it, see the SQLDelight documentation.

Create an SQLite driver

SQLDelight provides multiple platform-specific implementations of the SQLite driver, so you should create it for each platform separately. In the common code, you can refer to these drivers using the common SqlDriver interface.

You can create an abstract factory using expect/ actual mechanism:

expect class DatabaseDriverFactory {
    fun createDriver(): SqlDriver
}

Then provide actual implementations for this expected class:

Android driver

On Android, the SQLite driver is implemented by the AndroidSqliteDriver class. When you create its instance, pass the database information and the link to context to the constructor. For example, this code creates an SQLite driver for a database named AppDatabase:

actual class DatabaseDriverFactory(private val context: Context) {
    actual fun createDriver(): SqlDriver {
        return AndroidSqliteDriver(AppDatabase.Schema, context, "test.db")
    }
}

iOS driver

On iOS, the SQLite driver implementation is the NativeSqliteDriver class:

actual class DatabaseDriverFactory {
    actual fun createDriver(): SqlDriver {
        return NativeSqliteDriver(AppDatabase.Schema, "test.db")
    }
}

Now you can create the DatabaseDriverFactory instance in your applications' code and pass it to the common module. Then create an AppDatabase instance to perform database operations:

val database = AppDatabase(databaseDriverFactory.createDriver())

See the Networking & Data Storage Hands-on for the full example.

Table operations

The SQLDelight generator works as follows: you create a file with the .sq extension in which you provide all the required SQL queries to the database. The SQLDelight plugin generates the Kotlin code for execution of these queries. This way, SQLDelight automatically implements the interaction of your app with the database. This eliminates the need for manual implementation of entity classes and code that maps Kotlin classes onto a relational database model.

The syntax of the SQLDelight generator lets you implement all the basic SQLite commands, including cascading, indexes, triggers, and others.

Let's look at how to declare and use basic database operations.

Create

Typically, queries for creating all the necessary database tables are listed at the beginning of the .sq file. To create a table, use the SQL command CREATE TABLE. For example, this query creates a table with two fields:

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

For this query, SQLDelight generates the Language Kotlin interface with the specified fields. It will be used in functions that implement operations with the Language table.

Delete

SQL's DELETE operator is used to delete rows from database tables. For example, to delete all records from the table, declare the following query in the .sq file:

deleteAllLanguages:
DELETE FROM Language;

The label deleteAllLanguages: in the first line declares the name for the Kotlin function that will execute this query.

fun deleteAllLanguages()

To execute the deleteAllLanguages query from your code, write the following:

val database = AppDatabase(sqlDriver)
val appDatabaseQueries: AppDatabaseQueries = database.appDatabaseQueries

fun deleteAllLanguages() {
    appDatabaseQueries.deleteAllLanguages()
}

You can use the WHERE operator to delete certain rows from a table, for example:

deleteLanguageById:
DELETE FROM Language 
WHERE id = ?;

SQLDelight will generate a Kotlin function with an argument:

fun deleteLanguageById(id: Long)

To delete a specific database record with the deleteLanguageById() function, call it on the AppDatabaseQueries object and pass the id of the record to be deleted:

fun deleteLanguageById(id: Long) {
    appDatabaseQueries.deleteLanguageById(id)
}

Insert

To add a data record into a table, use the SQL command INSERT. A query for inserting entries into the Language table may look like this:

insertLanguage:
INSERT INTO Language(id, name)
VALUES(?, ?);

insertLanguage: here defines the name of the corresponding Kotlin function that SQLDelight generates:

fun insertLanguage(id: Long?, name: String)

The function takes two arguments that match the table fields specified in the query.

This is how you insert a new record into the table in your app’s code:

data class SystemLanguage(
    val id: Long,
    val name: String
)

val database = AppDatabase(sqlDriver)
val appDatabaseQueries: AppDatabaseQueries = database.appDatabaseQueries

fun insertLanguage(systemLanguage: SystemLanguage) {
    appDatabaseQueries.insertLanguage(systemLanguage.id, systemLanguage.name)
}

Update

The SQL command UPDATE changes the values of given fields of specific rows within tables. For example, this query changes the name of the record with the provided identifier:

updateLanguageName:
UPDATE Language
SET name = ?
WHERE id = ?;

updateLanguageName: here defines the name of the corresponding Kotlin function that SQLDelight generates:

fun updateLanguageName(name: String, id: Long)

The function takes two arguments matching the query parameters.

This is how you update a record in the table in your app’s code:

data class SystemLanguage(
    val id: Long,
    val name: String
)

val database = AppDatabase(sqlDriver)
val appDatabaseQueries: AppDatabaseQueries = database.appDatabaseQueries

fun updateLanguageName(id: Long, newName: String) {
    appDatabaseQueries.updateLanguageName(newName, id)
}

Select

To select records from tables, use the SELECT operator. For example, if you want to select all the records from a table, add the following query in the .sq file:

selectAllLanguages:
SELECT * FROM Language;

For this SQL query, SQlDelight will create the following functions:

fun selectAllLanguages(): Query<Language>
fun <T : Any> selectAllLanguages(mapper: (id: Long, name: String) -> T): Query<T>

As you can see, the first argument in the second selectAllLanguages function is the mapper lambda that converts data from the selection into objects of an arbitrary type T. For example, if you need to transform the query results into entities required for the app's business logic, write the following code:

val database = AppDatabase(sqlDriver)
val appDatabaseQueries: AppDatabaseQueries = database.appDatabaseQueries

data class SystemLanguage(
    val id: Long,
    val name: String
)

fun selectAllLanguages(): List<SystemLanguage> {
    return appDatabaseQueries.selectAllLanguages { id: Long, name: String ->
        SystemLanguage(id, name)
    }.executeAsList()
}

Most queries include selection conditions.If you want to display a record with a specific identifier, add the following request in the .sq file:

selectLanguageById:
SELECT * FROM Language
WHERE id = ?;

For this query, SQLDelight will create the following functions:

fun selectLanguageById(id: Long): Query<Language>
fun <T : Any> selectLanguageById(id: Long, mapper: (id: Long, name: String) -> T): Query<T>

Similar to the above example, you can create a function that will query the database and convert the result to an object of the required data class:

fun selectById(languageId: Long): SystemLanguage {
    return appDatabaseQueries.selectLanguageById(languageId) { id: Long, name: String ->
        SystemLanguage(id, name)
    }.executeAsOne()
}

Transactions

SQLDelight allows multiple SQL queries to be executed in a single transaction. For this purpose, the generated Kotlin interface with queries provides the transaction function for creating transactions.

To execute a database transaction with multiple queries, call the transaction function and pass the lambda with these queries. For example, the function for adding all elements of a list in a single transaction looks like this:

fun insertAllLanguages(languages: List<SystemLanguage>) {
    database.appDatabaseQueries.transaction {
        languages.forEach { language ->
            database.appDatabaseQueries.insertLanguage(language.id, language.name)
        }
    }
}

SQLDelight plugin for Android Studio

To simplify working with .sq generator files, SQLDelight provides a plugin for Android Studio. This plugin adds syntax highlighting, code completion, usage search, refactoring, displays compile-time errors, and much more.

To install the plugin in Android Studio, open Preferences | Plugins | Marketplace and enter SQLDelight in the search bar.

For more information about the plugin, see the SQLDelight documentation.

We'd like to thank the IceRock team for helping us write this article.

Last modified: 01 March 2021