KMM Database layer


Swedish lake

This is the second in a multi-part series on Kotlin Multiplatform Mobile. This entry will cover configuring SQLDelight and implementing cache logic for the mobile iOS and Android app. Unlike the tutorial, I’m not using the SpaceX API, which provides public access to information about SpaceX rocket launches. Instead I’m using the Army’s brand new ODIN API for a reboot of my WEG iOS and Android applications. The ODIN API provides in-depth information about a wide array of military equipment.

Implementing a shared SQLDelight database & networking layer required the following dependencies:

Once those dependencies were added to the build.gradle.kts files in the project root directory and the KMM module directory, we could configure the DTOs (Data Transfer Objects). These objects were modelled after the responses from the ODIN API. Below is a sample of the data and should give you a good idea of the general structure:

import kotlinx.serialization.*

data class SearchResults (
    val batchcomplete: String? = null,
    val searchResultsContinue: Continue? = null,
    val query: Query? = null
) {
    fun asList(): List<SearchResult>? {
        return query?.searchResults
data class Continue (
    val sroffset: Long? = null,
    val continueString: String? = null

data class Query (
    val searchResults: List<SearchResult> = emptyList(),
    val totalHits: Long? = null

data class SearchResult (
    val title: String? = null,
    val id: Long = 0,
    val categories: List<String> = emptyList(),
    val images: List<Image> = emptyList()

That final DTO, SearchResult , forms the cornerstone of what we’ll display to the user. It includes: a title, so the user knows what they’re looking at, categories for determining what bottom navigation tab (Land, Sea, or Air) to display the equipment on, images for showing a preview of the equipment, and an id for querying the API for additional details when the equipment icon is tapped.

As you might have noticed, we have a couple of complex relationships that we need to store. Images are their own DTOs that have a one-to-many relationship with SearchResult. Categories are represented by strings, but they also need to be returned in a list format. Finally, ODIN doesn’t return SearchResult objects directly, but rather a SearchResults payload that contains a Query object (which has a list of our desired SearchResult objects) and a Continue object for pagination.

This brings us to the crux of this blog entry: how do we represent these relationships in SQL? To do this we need to create a SQLDelight app/shared/cache/AppDatabase.sq file with the following:

import kotlin.collections.List;

CREATE TABLE search_result (
title TEXT,
categories TEXT

INSERT INTO search_result(title, equipment_id, categories)

SELECT * FROM search_result
WHERE equipment_id = ?;

SELECT search_result.*, image.*
FROM search_result
LEFT JOIN image ON image.equipment_id == search_result.equipment_id;

DELETE FROM search_result;

equipment_id INTEGER NOT NULL,
name TEXT,
url TEXT

INSERT INTO image(equipment_id, name, url)

WHERE equipment_id = ?;


This covers all of our basic DB CRUD (Create, Read, Update, Destroy) operations for search results and images. Images are associated with search results through a left join. This means that we return all search results regardless of whether they have an associated image or not.

You might have noticed that the `SearchResults`, `Continue`, and `Query` objects aren’t represented at all in the database. The reason for this is threefold. First, because SQLDelight is multiplatform (iOS, Android, Desktop, etc.) the number of SQL operators available in common is a subset of those available across all those platforms. This limits the complexity of the relationships that you can model. Many-to-many relationships are particularly cumbersome. Second, even if it was straightforward model many-to-many relationships in SQLDelight, doing so would lead to inconsistent behavior. For example, if the user was offline, but had previously searched for all equipment beginning with “T-72”, they would receive results when they typed exactly “T-72”, but nothing for “T-7” or “T-72A”. Third, for reasons soon to be illustrated, `SearchResults`, `Continue`, and `Query` don’t actually need to be stored database.

These SQL queries can be invoked with the following KMM wrapper functions:

import com.jamesjmtaylor.weg.models.Image
import com.jamesjmtaylor.weg.models.SearchResult

 * SQLDelight database for caching REST API query results.
 * "internal" accessibility modifier means the Database is only accessible from within the
 * multiplatform module
internal class Database(databaseDriverFactory: DatabaseDriverFactory) {
    private val database = AppDatabase(databaseDriverFactory.createDriver())
    private val dbQuery = database.appDatabaseQueries

    internal fun clearDatabase() {
        dbQuery.transaction {

    internal fun getAllResults(): List<SearchResult> {
        return dbQuery.selectAllSearchResults(::mapSearchResultSelecting).executeAsList()

    internal fun insertSearchResults(results: List<SearchResult>) {
        dbQuery.transaction {
            results.forEach { result ->
                val image = dbQuery.selectImagesByEquipmentId(
                if (image == null) insertImage(result)

    private fun insertImage(result: SearchResult) {
        result.images.forEach { image ->
                name =,
                equipment_id =,
                url = image.url

    private fun insertResult(result: SearchResult) {
            title = result.title,
            equipment_id =,
            categories = result.categories.joinToString { it }

    private fun mapSearchResultSelecting(
        equipmentId: Long,
        title: String?,
        categories: String?,
        imageEquipmentId: Long?,
        imageName: String?,
        imageUrl: String?
    ) : SearchResult {
        return SearchResult(
            title = title,
            id = equipmentId,
            categories = categories?.split(",") ?: emptyList(),
            images = listOf(Image(imageName,imageUrl))

These functions map the results cached in the DB to the DTOs that we defined at the beginning of this blog entry. With the DB thus wrapped, we can invoke the DB from our client apps with the following repository pattern:

class EquipmentSDK(databaseDriverFactory: DatabaseDriverFactory) {
    private val db = Database(databaseDriverFactory)
    private val api = Api()

    suspend fun getEquipment(forceReload: Boolean): List<SearchResult>? {
        val cachedEquipment = db.getAllResults()
        return if (cachedEquipment.isNotEmpty() && !forceReload) {
        } else {
            api.getEquipment().asList().also { results ->
                results?.let {

Using this logic we can return all the cached results, or retrieve from the network directly and update the cache as needed. This also demonstrates why we don’t need to store SearchResults, Continue, and Query objects in the database. As long as the client determines whether or not to force reload from the network, the shared KMM logic can be simplified to either just returning everything that the DB has, or retrieving the data fresh.

This wraps up the DB portion of the KMM series. If you’re curious about any of the other objects (i.e. databaseDriverFactory) you can access the full repository here: Next time we’ll cover implementation of the KMM Networking layer and exactly how that api.getEquipment() function is implemented. Until then!

Photo by Simon Spring on Unsplash

comments powered by Disqus