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:
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:
Database drivers
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 build.gradle
or build.gradle.kts
:
iOS source sets
To connect the SQLite driver for iOS and other native platforms, add the following dependency:
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:
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:
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
:
iOS driver
On iOS, the SQLite driver implementation is the NativeSqliteDriver
class:
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.
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:
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:
The label 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:
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:
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:
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:
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:
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:
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 .sq
file:
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:
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:
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.