It’s hard to find an application which don’t need to remember your previous actions and choices. People don’t like to lose their progress, preferences or to be forced to sign in every time they open an app.

Introduction

It’s not surprising that the most frequently saved piece of persistent data is auth credentials because asking your customers to authorize too often is a sure way to lose those customers. This example is rather extreme, but it clearly shows how a sensible caching policy can make our applications more user-friendly.

Since saving state is such an important thing, it’s tempting to assume that there must be a standard way of doing that. Well, there is no golden standard but there are a few widely adopted approaches. In this article, I briefly describe different Android data storage approaches and the reasons why I think that SQLDelight is the best tool for the job. I also explain how to set it up and how it can help you to build clean, stable and maintainable apps.

Stateless Apps

OK, that “sign in every time” example is hard to argue with but why would we save any data except for auth credentials? Let’s say we have a native app and a bunch of REST API endpoints that provide all the data we need. We can simply call them every time a user opens an app and rest assured that the user is always presented with the latest data.

There are three main problems with this approach:

1. Reliability. Let’s say we need to fetch a list of ATMs in a particular town. In the real world, network requests take an unpredictable amount of time, and they might also fail, so you could easily end up with no data to show. Even if your request succeeds after all, it’s worth noting that people don’t like to be blocked by progress indicators, especially if the waiting time is unpredictable.

2. Latency. Fetching data from a remote server will always be slower than fetching it from a local cache. Blame the speed of light: this nasty phenomenon puts a strict limit on how fast the data can move from one point to another and time, indeed, is a precious commodity.

3. Redundancy. To continue our ATM example: it would be nice to be able to fetch only the ATMs that were added or updated since the last call. Those things tend to be pretty static, and it’s also true for many other use cases. Such an approach can reduce server workload and speed up the app, but it also means that we need to have a cache, and we also need to be able to manage its ever-growing complexity.

So, not having a cache is not the end of the world but let’s not pretend that it’s right and that the users wont notice such a shoddy job.

WebView

Hey, can’t we take an existing website and wrap it into a WebView? Such an “app” can use request caching and fetch all the caching instructions from our servers. That may seem pretty convenient but is it a good solution?

The main drawback of non-native UIs is poor performance: no one likes to see a slow and blurry interface on their new and shiny octa-core smartphone. High quality apps need native user interfaces and that means we can’t get away with simply loading an existing website inside a WebView and calling it a day. This practice is not banned (yet?) but those apps are garbage and the users know that.

Basic Types of Data

Where can we put our cache? First, let’s split the data we may receive in two categories, just for convenience’s sake:

1. Binary data such as images, songs, videos, etc
2. Textual data of certain structure that represents domain objects

We rarely need to mess around with images, so it’s almost always better to use an image handling library such as Picasso. This library can cache your images automatically, so you can have one less thing to worry about. When it comes to other kinds of binary data, it’s generally a good idea to store it somewhere outside your database.

Structured data that we may generate locally or fetch from the remote API is a different beast and that’s where databases shine.

SharedPreferences

The state of any app is just a bunch of data structures, and it can be (grossly inefficiently) expressed as a single String. The simplest way to persist strings on Android is to use SharedPreferences API which gives us a simple way to save the app state.

Is it a good solution? Let’s consult with the official documentation:

Frequently changing properties or properties where loss can be tolerated should use other mechanisms. ― SharedPreferences documentation

Looks like a reminder that you shouldn’t abuse SharedPreferences by putting large amounts of non-essential data into it. SharedPreferences is not a memory cache, after all, although it acts in a similar way. Also, don’t forget to use apply() instead of commit() when putting data there.

Let’s look at another excerpt:

Preferences: Store private, primitive data in key-value pairs. ― Data storage documentation

Primitive data structures is the ideal use case for SharedPreferences. For instance, it’s a good place to put your auth token, because it’s usually just a short string, and it’s not directly related to the rest of your data.

Databases: Store structured data in a private database using the Room persistence library. ― Data storage documentation

The recommendation is clear: structured data should be stored in a database. Is it a sane recommendation? Of course it is, it’s very hard to manage data when it lacks structure or when it’s structure is unconventional.

So, what about Room? Well, it’s not a bad tool for state persistence, but I think that we have a better option: SQLDelight. But first, let’s talk about SQL.

SQL and SQLite

SQL stands for Structured Query Language and it’s used pretty much everywhere, including Android. In fact, it powers most of the server databases. SQL is a simple language that allows us to interact with relational databases such as PostgreSQL and most of those database engines are fine-tuned for high load multi-user environments, and they require skilled system administrators in order to run smoothly.

So, SQL wasn’t meant for mobile and embedded systems, initially. It all changed about 20 years ago with the release of SQLite - a simple database engine that understands SQL and stores each database in a single file. In Android, you don’t have to do any set up and maintenance if you wish to use SQL. The Android platform provides you with a ready to use interface so utilizing the full power of SQL for your Android apps is very easy.

The only problem with Android’s SQLite API is the fact that it’s very verbose, which means that you have to write a lot of boilerplate code in order to use SQL in your projects. That’s why it’s better to use persistence libraries such as SQLDelight and Room.

Should I use Room or SQLDelight?

They’re both great. I like SQLDelight more because it removes more boilerplate and it’s also cross-platform. Room is an Android-only library, and it’s absolutely fine to use it if you’re not going to target other platforms. Personally, I prefer to be more platform-agnostic when I’m able to do it without performance or usability sacrifices.

Setting up SQLDelight

Enough theory, let’s set up SQLDelight in a native Android app. By the way, you can clone a fully functional example project here.

Step 1: Declare Version

We’ll need to specify which version of SQLDelight we want to use, and we have to do that in at least two different places:

1. When importing Gradle plugin
2. When importing the library itself
3. (Optional) When importing Coroutines or RxJava extensions
4. (Optional) Upon declaration of test specific dependencies
5. (Optional) Platform-specific imports, if you target multiple platforms

We can hard-code the same version number in all of those places, but it leads to code duplication, and it can lead to serious errors in the future. We always depend on a single version of SQLDelight so the exact version number is better to be declared in a single place. There are many ways to avoid duplication so feel free to use your favorite approach. I’m just going to add a following variable to Gradle properties file:

Path: ./gradle.properties

versions_sqldelight=1.4.0

Step 2: Add Gradle Plugin

Now we can open ./build.gradle and ./app/build.gradle and import SQLDelight Gradle plugin.

Path: ./build.gradle

buildscript {
    // --snip--

    dependencies {
        // --snip--
        classpath "com.squareup.sqldelight:gradle-plugin:$versions_sqldelight"
    }
}

Path: ./app/build.gradle

plugins {
    // --snip--
    id "com.squareup.sqldelight"
}

Step 3: Add Runtime Dependencies

Path: ./app/build.gradle

dependencies {
    // --snip--
    implementation "com.squareup.sqldelight:android-driver:$versions_sqldelight"
    testImplementation "com.squareup.sqldelight:sqlite-driver:$versions_sqldelight"
}

Step 4: Using SQLDelight With Coroutines

Path: ./app/build.gradle

implementation "com.squareup.sqldelight:coroutines-extensions:$versions_sqldelight"

Step 5: Files, Tables and Queries

SQLDelight assumes that you keep your table definitions and queries together, one file per each table. Let’s create an example table that can store currency data:

Path: ./app/src/main/sqldelight/com/example/sqldelight/sql/Currency.sq

CREATE TABLE Currency (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    code TEXT NOT NULL,
    crypto INTEGER as Boolean NOT NULL,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
);

insertOrReplace:
INSERT OR REPLACE
INTO Currency
VALUES ?;

selectAll:
SELECT *
FROM Currency;

select:
SELECT *
FROM Currency
WHERE id = ?;

selectCount:
SELECT COUNT(*)
FROM Currency;

selectMaxUpdatedAt:
SELECT MAX(updatedAt)
FROM Currency;

updateName:
UPDATE Currency
SET name = ?
WHERE id = ?;

delete:
DELETE
FROM Currency
WHERE id = ?;

SQLDelight will generate Currency class for us, based on the data we just provided. It will also generate a type-safe CRUD methods based on the queries we declared. No need to write any Kotlin or Java code, only plain old SQL.

Step 6: Install IntelliJ Plugin

When you first open an .sq file, your IDE of choice (well, I mean Android Studio and Idea exclusively) should ask if you want to install a special plugin that makes it easier to work with SQL. I highly recommend installing it.

Step 7: Create Database

Here is how you can create and initialize the database. The resulting object can give you an interface to every table you declared.

val driver = AndroidSqliteDriver(
    schema = Database.Schema,
    context = context,
    name = "data.db"
)

val database = Database(driver)

Step 8: Test Your Queries

Let’s create a unit test suite and write some tests in order to make sure everything works as expected:

package com.example.sqldelight.sql

import com.example.sqldelight.Database
import com.squareup.sqldelight.sqlite.driver.JdbcSqliteDriver
import org.joda.time.DateTime
import org.junit.Assert.assertEquals
import org.junit.Assert.assertNull
import org.junit.Before
import org.junit.Test
import java.util.*
import kotlin.random.Random

class CurrencyQueriesTests {

    lateinit var queries: CurrencyQueries

    @Before
    fun setUp() {
        val driver = JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY)
        Database.Schema.create(driver)
        val database = Database(driver)
        queries = database.currencyQueries
    }

    @Test
    fun insertOrReplace_insertsItem() {
        currency().apply {
            queries.insertOrReplace(this)
            assertEquals(this, queries.select(id).executeAsOneOrNull())
        }
    }

    @Test
    fun insertOrReplace_replacesItem_withNoDuplication() {
        currency().apply {
            queries.insertOrReplace(this)
            val updatedItem = copy(name = "Changed")
            queries.insertOrReplace(updatedItem)
            assertEquals(updatedItem, queries.select(id).executeAsOneOrNull())
            assertEquals(1L, queries.selectCount().executeAsOne())
        }
    }

    @Test
    fun selectAll_selectsAllItems() {
        listOf(currency(), currency()).apply {
            queries.transaction { forEach { queries.insertOrReplace(it) } }
            assertEquals(this, queries.selectAll().executeAsList())
        }
    }

    @Test
    @ExperimentalStdlibApi
    fun select_selectsCorrectItem() = queries.transaction {
        buildList<Currency> {
            repeat(100) { add(currency()) }
        }.apply {
            forEach { queries.insertOrReplace(it) }

            random().apply {
                assertEquals(this, queries.select(id).executeAsOne())
            }
        }
    }

    @Test
    @ExperimentalStdlibApi
    fun selectCount_returnsCorrectCount() = queries.transaction {
        val count = 1 + Random(System.currentTimeMillis()).nextInt(100)
        repeat(count) { queries.insertOrReplace(currency()) }
        assertEquals(count.toLong(), queries.selectCount().executeAsOne())
    }

    @Test
    fun selectMaxUpdatedAt_selectsCorrectItem() = queries.transaction {
        val count = 1 + Random(System.currentTimeMillis()).nextInt(5)
        repeat(count) { queries.insertOrReplace(currency()) }

        currency().apply {
            val updatedAt = DateTime.parse(updatedAt).plusYears(5).toString()
            queries.insertOrReplace(copy(updatedAt = updatedAt))
            assertEquals(updatedAt, queries.selectMaxUpdatedAt().executeAsOne().MAX)
        }
    }

    @Test
    fun updateName_updatesName() {
        currency().apply {
            queries.insertOrReplace(this)
            val newName = "new name"
            queries.updateName(newName, id)
            assertEquals(newName, queries.select(id).executeAsOne().name)
        }
    }

    @Test
    fun delete_deletesItem() {
        currency().apply {
            queries.insertOrReplace(this)
            queries.delete(id)
            assertNull(queries.select(id).executeAsOneOrNull())
        }
    }

    private fun currency() = Currency(
        id = UUID.randomUUID().toString(),
        name = "Testcoin",
        code = "TST",
        crypto = true,
        createdAt = DateTime.now().toString(),
        updatedAt = DateTime.now().toString()
    )
}

Now, let’s test our queries:

./gradlew clean testDebug

> Task :app:testDebugUnitTest
[CurrencyQueriesTests] select_selectsCorrectItem
Result: SUCCESS
[CurrencyQueriesTests] insertOrReplace_replacesItem_withNoDuplication
Result: SUCCESS
[CurrencyQueriesTests] selectCount_returnsCorrectCount
Result: SUCCESS
[CurrencyQueriesTests] updateName_updatesName
Result: SUCCESS
[CurrencyQueriesTests] selectMaxUpdatedAt_selectsCorrectItem
Result: SUCCESS
[CurrencyQueriesTests] delete_deletesItem
Result: SUCCESS
[CurrencyQueriesTests] selectAll_selectsAllItems
Result: SUCCESS
[CurrencyQueriesTests] insertOrReplace_insertsItem
Result: SUCCESS

Conclusion

It’s important to cache as much data as possible and there are many ways to do that. Using SharedPreferences is fine for a small amount of loosely structured data but SQL is the way to go if your state is more complex than a few primitive values. SQLite makes your state more transparent and easier to extract, and it also makes your data handling code more readable for people who are less familiar with Android-specific APIs. SQLDelight is a great tool for state management because it decouples Android-specific logic from data queries and eliminates the need to write a lot of boilerplate code.