Step 27: Add a local database to KMM Android and iOS app with SQLDelight

October 08, 2022

Now that we basically have a functional app, we can adapt it to network emergencies - what if you’re baking pizza in a remote mountain hut and there’s no connection?

To make the app more persistent, we’ll equip it with a local database. For this purpose we’ll use SQLDelight library, which is based on SQLLite.

The following setup follows the official documentation:

“SQLDelight generates type-safe Kotlin APIs from SQL statements. It also provides a multiplatform implementation of the SQLite driver.”

Define the latest stable version of the plugin in Versions.kt:

 
const val SQL_DELIGHT = "1.5.3"

In object Common add the dependency:

 
const val SQLDELIGHT_PLUGIN = "com.squareup.sqldelight:gradle-plugin:$SQL_DELIGHT"

Then use it in project level build.gradle.kts:

 
   classpath(Versions.Common.SQLDELIGHT_PLUGIN)

Then add this plugin to other plugins in shared build.gradle.kts:

 
plugins {
   kotlin("multiplatform")
   kotlin("native.cocoapods")
   id("com.android.library")
   kotlin("plugin.serialization") version Versions.KOTLIN_VERSION
   id("com.squareup.sqldelight")
}

Now we need to setup database drivers, therefore we’ll add the following dependencies to Versions.kt:

In object Common:

 
const val SQLDELIGHT_DRIVER = "com.squareup.sqldelight:runtime:$SQL_DELIGHT"

In object Android:

 
const val SQLDELIGHT_DRIVER = "com.squareup.sqldelight:android-driver:$SQL_DELIGHT"

In object iOS:

 
const val SQLDELIGHT_DRIVER = "com.squareup.sqldelight:native-driver:$SQL_DELIGHT"

Go to shared build.gradle.kts. Add the dependencies in appropriate sourceSets:

commonMain:

 
implementation(Versions.Common.SQLDELIGHT_DRIVER)

androidMain:

 
implementation(Versions.Android.SQLDELIGHT_DRIVER)

iosMain:

 
implementation(Versions.iOS.SQLDELIGHT_DRIVER)

Sync your project.

Now we need to configure our database. We will create a database named PizzaDatabase with the package name dev.tutorial.kmpizza.db for the generated Kotlin classes. At the bottom of shared build.gradle.kts add:

 
sqldelight {
   database("KmpizzaDatabase") { // This will be the name of the generated database class.
       packageName = "dev.tutorial.kmpizza.db"
       sourceFolders = listOf("sqldelight")
    }
}

“SQLDelight provides multiple platform-specific implementations of the SQLite driver, so you should create it for each platform separately.”
So let’s use a suitable implementation for our platforms.
Go to shared CommonModule.kt and add a platformModule:

 
modules(
   apiModule,
   repositoryModule,
   viewModelModule,
   platformModule
)

Here we’ll specify the SqlDelight Driver. In shared di package create a new file PlatformModule.kt and add the following:

 
expect val platformModule: Module

Now add PlatformModule.kt to androidMain/dev/tutorial/kmpizza/di and PlatformModule.kt to iosMain/dev/tutorial/kmpizza/di, and write actual platform modules.

androidMain:

 
actual val platformModule = module {
   single<SqlDriver> {
       AndroidSqliteDriver(
           KmpizzaDatabase.Schema,
           get(),
           "kmpizza.db"
       )
   }
}

iosMain:

 
actual val platformModule = module {
   single<SqlDriver> { NativeSqliteDriver(KmpizzaDatabase.Schema, "kmpizza.db") }
}

Also add linking to sqlite in the ios project:

Add

 
@Suppress("NO_ACTUAL_FOR_EXPECT")

to suppress the warning for JVM module.

You may notice that KmpizzaDatabase is unresolved. That’s because it doesn’t exist yet - we need to create it first.

Create a package sqldelight in shared/src/commonMain.
There, create a package that corresponds to the package you specified earlier: dev/tutorial/kmpizza/db
In that package, create KmpizzaDatabase.sq file.
Here you’ll write down all your SQL queries.
In the end you’ll have the following structure:

Now let’s create our tables for recipes.
In KmpizzaDatabase.sq write the following:

 
import com.example.recipecollection.model.Ingredient;
import com.example.recipecollection.model.Instruction;
import com.example.recipecollection.model.RecipeImage;
import kotlin.collections.List;
 
CREATE TABLE Recipe (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
ingredients TEXT AS List<Ingredient> NOT NULL,
instructions TEXT AS List<Instruction> NOT NULL,
images TEXT AS List<RecipeImage> NOT NULL
);

You’ll see the IDE suggesting you to install the SQLDelight plugin - it’s a good idea.
The plugin will help you read SQL statements.

You still may see List as unresolved, but it’s just an IDE bug and will not affect the build process.

Sync and rebuild the app. Now you can import KmpizzaDatabase where it was previously unresolved.

For simplicity of the tutorial, we won’t create separate tables for Ingredients and Instructions. Instead, we’ll use custom types and columnAdapters to convert objects to text. In commonMain create a new folder local and add RecipesLocalSource.kt there. Don’t write anything into the class yet, just add these adapters to repositories on top and add the necessary imports from your project and kotlinx.serialization:

 
val listOfInstructionsAdapter = object : ColumnAdapter<List<Instruction>, String> {
   override fun decode(databaseValue: String) = if (databaseValue.isEmpty()) emptyList() else databaseValue.split("|").map { Json.decodeFromString<Instruction>(it) }
   override fun encode(value: List<Instruction>) = if (value.isEmpty()) "" else value.joinToString(separator = "|") { Json.encodeToString(it) }
}
 
val listOfIngredientsAdapter = object : ColumnAdapter<List<Ingredient>, String> {
   override fun decode(databaseValue: String) = if (databaseValue.isEmpty()) emptyList() else databaseValue.split("|").map { Json.decodeFromString<Ingredient>(it) }
   override fun encode(value: List<Ingredient>) = if (value.isEmpty()) "" else value.joinToString(separator = "|") { Json.encodeToString(it) }
}
 
val listOfRecipeImagesAdapter = object : ColumnAdapter<List<RecipeImage>, String> {
   override fun decode(databaseValue: String) = if (databaseValue.isEmpty()) emptyList() else databaseValue.split("|").map { Json.decodeFromString<RecipeImage>(it) }
   override fun encode(value: List<RecipeImage>) = if (value.isEmpty()) "" else value.joinToString(separator = "|") { Json.encodeToString(it) }
}
 
class RecipeLocalSource {
}

Now we can rebuild the project and you’ll find generated sqldelight files.
To use them, we’ll return to our CommonModule.kt and add a new module:

 
private val coreModule = module {
   single {
       KmpizzaDatabase(
           get(),
           Recipe.Adapter(
               instructionsAdapter = listOfInstructionsAdapter,
               ingredientsAdapter = listOfIngredientsAdapter,
               imagesAdapter = listOfRecipeImagesAdapter
           )
       )
   }
}

Of course, we’ll add it to modules too:

 
modules(
   apiModule,
   repositoryModule,
   viewModelModule,
   platformModule,
   coreModule
)

Remember about the RecipeLocalSource. Use the recipe local database there as parameter:

 
class RecipeLocalSource (private val dbRef: KmpizzaDatabase) {
}

And then in CommonModule.kt add the following to the top of repositoryModule:

 
factory { RecipeLocalSource(get()) }

Now it’s time to write some queries and see how we can use them in RecipeLocalSource.
Go back to RecipeDatabase.sq and add following SQL queries:

 
getAllRecipes:
SELECT * FROM Recipe;
 
getRecipeById:
SELECT * FROM Recipe WHERE id = ?;
 
insertOrReplaceRecipe:
INSERT OR REPLACE INTO Recipe (
                            id,
                            title,
                            ingredients,
                            instructions,
                            images)
                            VALUES(?, ?, ?, ?, ?);

Now let’s use them to retrieve data.
In RecipesLocalSource add following functions:

 
fun Recipe.mapToRecipeUiModel(): RecipeUiModel{ [1]
   return RecipeUiModel(
       id, title, ingredients, instructions, images
   )
}
 
fun getAllRecipes() : List<RecipeUiModel> = 
       dbRef.recipeDatabaseQueries [2]
           .getAllRecipes()
           .executeAsList()
           .map { it.mapToRecipeUiModel() }
 
 
fun getRecipeById(id: Long) : RecipeUiModel? =
   dbRef.recipeDatabaseQueries
           .getRecipeById(id)
           .executeAsOneOrNull()?.mapToRecipeUiModel()
 
 
fun insertOrReplaceRecipe(recipe: RecipeUiModel) {
   dbRef.recipeDatabaseQueries
       .insertOrReplaceRecipe(recipe.id, recipe.title, recipe.ingredients, recipe.instructions, recipe.images)
}

[1] This mapper function that will help you convert database Recipe to RecipeUiModel
[2] Use the generated query to get all recipes from the local database and convert them to RecipeUiModel
[3], [4] Similarly we can extract a recipe by id from the local database or insert a new recipe

Now we need to adjust the repository. The idea is to use remote source and back up with local source data.
Go to RecipeRepository and add recipeLocalSource below private val recipeRemoteSource

 
private val recipeLocalSource: RecipeLocalSource by inject()

Then adjust all the methods to use recipeLocalSource:

 
suspend fun postRecipe(recipe: RecipeUiModel): Long? {
   return try {
       val id = recipeRemoteSource.postRecipe(recipe.toRecipeRequest()) [1]
       id?.let {
           val entry = recipe.copy(id = it)
           recipeLocalSource.insertOrReplaceRecipe(entry) [2]
       }
       id
   } catch (e: Exception) {
       null
   }
}

[1] Here we try to save our new recipe remotely
[2] If we succeed, we should receive a recipe id from the api, then we can save the recipe locally

 
suspend fun getRecipes(): List<RecipeUiModel> {
   return try {
       val recipes = recipeRemoteSource.getRecipes().map {
           it.toRecipeUiModel()
       } [1]
       recipes.forEach {
           recipeLocalSource.insertOrReplaceRecipe(it)
       } [2]
       recipes
   } catch (e: Exception) {
       recipeLocalSource.getAllRecipes() [3]
   }
}

[1] Here we try to get all recipes from backend
[2] If we succeed, we want to save all the recipes that we received locally
[3] Otherwise we’ll just get what we have saved locally

 
suspend fun getRecipe(id: Long): RecipeUiModel? {
   return try {
       recipeRemoteSource.getRecipe(id).toRecipeUiModel()[1]
   } catch (e: Exception) {
       recipeLocalSource.getRecipeById(id)[2]
   }
}

[1] Try getting the recipe from remote source
[2] If it doesn’t work, then get the local backup

 
suspend fun postRecipeImage(recipeId: Long, imageFile: ImageFile) = recipeRemoteSource.postImage(recipeId, imageFile) [1]

[1] This request remains unchanged

Build and run the app. You’ll get an error:
Caused by: org.koin.core.error.NoBeanDefFoundException: |- No definition found for class:'android.content.Context'. Check your definitions!

This is because we forgot to add the androidContext when starting the app. Go to MainApp.kt and change initKoin {} to this:

 
initKoin {
   androidContext(this@MainApp)
}

Now turn off your internet connection and run the Android app again. You will still see that recipe in the recipe list.

However, the app has just the basic database functionality now. You won’t be able to create a new recipe offline. If you’re curious, try adjusting RecipeRepository so that allows you more operations when offline.

Now build and run your iOS app. It behaves similarly.

See, we didn’t have to change anything in iOS at all - it all came with the changes in the shared code.
Isn’t it awesome?

Here’s a task for you: if you try opening one of the locally saved recipes without internet connection, you’ll notice that there is no image. Fix it by adding a “No Internet” placeholder instead of an empty space when the user can’t download the image.