Configure SQLDelight for data storage

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:

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

apply plugin: 'com.squareup.sqldelight' 

Database drivers

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

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

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

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

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

iosMain { 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 { database("AppDatabase") { packageName = "com.example.db" } } 

sqldelight { 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.

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.

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() }