Introduction to Multiplatform Persistence with SQLDelight

Most mobile apps store data locally on the device. This could just be some local settings/preferences but frequently also includes caching of data returned from queries to back end services. For at least the latter case that data is typically stored in an SQLite database (and, importantly, SQLite is used on both iOS and Android). There are low level libraries available to access SQLite database but this is normally achieved using libraries that provide a higher level abstraction….these typically being Core Data on iOS and, at least more recently, Room Persistence Library on Android.

SQLDelight

I mentioned the GalwayBus project in a previous article. This started life as Android only (and still predominantly is) and it had used Room library for a number of basic persistence use cases. As some of the API request logic got migrated from Android module (using Retrofit) to shared code module (using Ktor) then the need also arose for associated Repository to be able to persist/cache data in local database….and the currently accepted way of doing that, in a multiplatform project, is using SQLDelight library. SQLDelight is a Kotlin Multiplatform library that “Generates typesafe Kotlin APIs from SQL” (taking a somewhat different approach to that used by Room and CoreData). There are currently SQLDelight drivers for Android, JVM and iOS.

I’m going to outline some key components of the SQLDelight based implementation using a somewhat simplified use case from the GalwayBus project (getting/storing Bus Stop information). The main purpose of this post is really to give a flavor of how this can be done without getting too lost in the weeds. I do have to say at this point that I’ve been really impressed with how relatively straighforward it has beeen to do this and have common persistence code that runs on both iOS and Android.

The starting point is use of com.squareup.sqldelight gradle plugin. This uses following information for example from build.gradle

sqldelight {
    MyDatabase {
        packageName = "com.surrus.galwaybus.db"
        sourceFolders = ["sqldelight"]
    }
}

and SQL such as following (in GalwayBus.sq) to generate a number of Kotlin source files which can then be used to create and query the associated SQLite database.

CREATE TABLE BusStop(
stop_id INTEGER NOT NULL PRIMARY KEY,
short_name TEXT NOT NULL,
irish_short_name TEXT NOT NULL
);

insertItem:
INSERT OR REPLACE INTO BusStop(stop_id, short_name, irish_short_name)VALUES(?,?,?);

selectAll:
SELECT * FROM BusStop;

selectById:
SELECT * FROM BusStop WHERE stop_id = ?;

This is relevant excerpt from GalwayBusRepository.kt (insertItem and selectAllapis used here were automatically generated from GalwayBus.sq

expect fun createDb() : MyDatabase

class GalwayBusRepository {

    private val galwayBusApi = GalwayBusApi()
    private val galwayBusDb = createDb()
    private val galwayBusQueries = galwayBusDb.galwayBusQueries

    init {
        GlobalScope.launch (ApplicationDispatcher) {
            fetchAndStoreBusStops()
        }
    }

    suspend fun fetchAndStoreBusStops() {
        val busStops = galwayBusApi.fetchBusStops()

        busStops.forEach {
            galwayBusQueries.insertItem(it.stop_id.toLong(), it.short_name, it.irish_short_name)
        }
    }

    suspend fun getBusStops(): List<BusStop> {
        val results = galwayBusQueries.selectAll(mapper = { stop_id, short_name, irish_short_name ->
            BusStop(stop_id.toInt(), short_name, irish_short_name)
        }).executeAsList()

        return results
    }

    fun getBusStops(success: (List<BusStop>) -> Unit) {
        GlobalScope.launch(ApplicationDispatcher) {
            success(getBusStops())
        }
    }

...
}

Note the use of expect fun createDb(), the actual versions of these are: for Android

actual fun createDb(): MyDatabase {
    val driver = AndroidSqliteDriver(MyDatabase.Schema, appContext, "galwaybus.db")
    return MyDatabase(driver)
}

and for iOS

actual fun createDb(): MyDatabase {
    val driver = NativeSqliteDriver(MyDatabase.Schema, "galwaybus.db")
    return MyDatabase(driver)
}

Swift code such as following can be used then to call in to Kotlin GalwayBusRepository to retrieve data (being read from database in this case).

let g = GalwayBusRepository()
g.getBusStops(success: { data in
    self.listStops = data
    self.tableView.reloadData()
    return KotlinUnit()
})

And finally the mandatory iOS screen shot showing the bus stop data retrieved by that code.

This post has, as mentioned, been intentionally short and has just described the high level components of the solution. Full details (including for example gradle dependencies used) can be found in GalwayBus repo.

To take this further would probably require for example looking in to adding use (or not) of Column Adaptors, supporting schema migrations and how to manage observing of data updates (some interesting work being done on extension that supports Kotlin Coroutines Flow

Would also recommend watching Jake Wharton and Alec Strong’s The Resurgence of SQL talk from droidcon NYC 2017 and also Multiplatform Persistence from Alec Strong at droidcon NYC 2018

UPDATE 28th August 2019

• Wrote a short follow on post exploring newly released Flow support
• If you want to look in to SQLDelight in more detail than would definitely recommend SQLDelight 1.x Quick Start Guide for Android by Sam Edwards. He also gave an excellent talk at droidcon NYC 2019 comparing SQLDelight and Room (really recommend watching it when video become avaiiable).

Featured in Kotlin Weekly Issue #151

Related tweet