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
To connect the SQLDelight plugin to a project, apply the SQLDelight Gradle plugin in your project’s build script (root
build.gradle.kts): First, add the plugin's
classpath to the build system:
$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.kts) in your shared multiplatform module:
Common source set
To work with database drivers in the common code, add the following dependency to the
commonMain source set:
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
iOS source sets
To connect the SQLite driver for iOS and other native platforms, add the following dependency:
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:
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
You can create an abstract factory using
Then provide actual implementations for this expected class:
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
On iOS, the SQLite driver implementation is the
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:
See the Networking & Data Storage Hands-on for the full example.
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.
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:
For this query, SQLDelight generates the
Language Kotlin interface with the specified fields. It will be used in functions that implement operations with the
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
deleteAllLanguages: in the first line declares the name for the Kotlin function that will execute this query.
To execute the
deleteAllLanguages query from your code, write the following:
You can use the
WHERE operator to delete certain rows from a table, for example:
SQLDelight will generate a Kotlin function with an argument:
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:
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: here defines the name of the corresponding Kotlin function that SQLDelight generates:
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:
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: here defines the name of the corresponding Kotlin function that SQLDelight generates:
The function takes two arguments matching the query parameters.
This is how you update a record in the table in your app’s code:
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
For this SQL query, SQlDelight will create the following functions:
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:
Most queries include selection conditions.If you want to display a record with a specific identifier, add the following request in the
For this query, SQLDelight will create the following functions:
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:
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:
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.