Kotlin Multiplatform In-Memory SQLDelight Database for Integration and UI Testing on iOS and Android

Databases are an integral part of almost every mobile application, in this post I'll cover how to use SQLDelight to provide an in-memory database for test environments.

Why use in-memory databases?

Tests should be predictable, and the execution order of the tests should change the test results. If we were to use a real database for tests, then this database would need to be cleared before (or after) every test case execution.

Even if a real database is cleared between tests, it is not guaranteed. Sometimes the test can crash, or be aborted, this can result in the database being persisted between test runs, which might change the outcome of the next test.

In-memory databases have the advantage of being discarded after every test case, so the next test execution should have no way of re-using an old in-memory database instance.

App features

The application shown in this article is available on GitHub. It has three features which we will be testing:

Adding new items
Updating existing items
Showing the list of items

The functionality is pretty basic, but it is enough to show how you can verify correct app behavior using an in-memory database.

The App Architecture

I tried keeping the code as simple as possible because it is not a main focus of the article. Basically, there are 3 UseCases corresponding to the app features:

GetTimestampItems
AddTimestampItem
UpdateTimestampItem

The basic idea is that the Add and Update UseCases insert / update items in the database with the current timestamp:

internal class AddTimestampItem(
    private val tableQueries: TableQueries,
    private val timestampProvider: TimestampProvider,
) {

    suspend fun execute() = withContext(Dispatchers.Default) {
        tableQueries.insertItem(timestampProvider.getTimestampMilliseconds())
    }
}

Getting the items boils down to retrieving them from the database and converting them to a more UI friendly format.

internal class GetTimestampItems(
    private val tableQueries: TableQueries,
) {

    fun execute(): Flow<List<TimestampItem>> =
        tableQueries.selectAll()
            .asFlow()
            .mapToList()
            .map { items -> ... }
	
    ...
}

The two important things in this article is TableQueries and TimestampProvider. The TableQueries is an object that is generated by SQLDelight, in tests we will replace the production database with an in-memory one. TimestampProvider returns the current system timestamp, but in tests we will replace it with a test doubles that allows controlling the returned value (relying on real system time makes the test unpredictable).

Database Schema

CREATE TABLE TimestampItemEntity (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    time INTEGER NOT NULL,
    version INTEGER AS Int NOT NULL
);

selectAll:
SELECT * FROM TimestampItemEntity;

insertItem:
INSERT OR IGNORE INTO TimestampItemEntity(time, version)
VALUES (?, 1);

updateTimestamp:
UPDATE TimestampItemEntity SET time = ?, version = version + 1 WHERE id = ?;

Every item has:

An auto-generated ID
The timestamp which will be shown in the UI
A version which is increased when the item is updated

Integration tests

Before diving into the tests, I just want to point out that all tests use Koin for replacing the production dependencies and retrieving classes used in the test.

In this article I won't get into the details of using Koin in tests, if you want to learn more about this topic I invite you to my other article focused around this topic. All the test code is also available in the GitHub Repository if you want to just look at the source code.

Testing added items

class AddTimestampItemTest : KoinComponent {

    private val tableQueries: TableQueries by inject()
    private val mockTimestampProvider: MockTimestampProvider by inject()
    private val systemUnderTest: AddTimestampItem by inject()

    @BeforeTest
    fun setUp() {
        startTestKoin()
    }

    @AfterTest
    fun tearDown() {
        stopTestKoin()
    }

    @Test
    fun `Added item uses the current timestamp as the name`() = runTest {
        mockTimestampProvider.setNextTimestamp(123)

        systemUnderTest.execute()

        val result = tableQueries.selectAll().executeAsList().first()
        result.time shouldBe 123
    }

    @Test
    fun `Added item has a version equal to 1`() = runTest {
        systemUnderTest.execute()

        val result = tableQueries.selectAll().executeAsList().first()
        result.version shouldBe 1
    }
}

The test cases set a predefined value for the TimestampProvider, then invoke the UseCase and verifies that the database is in the correct state.

TimestampProvider test double

interface TimestampProvider {

    fun getTimestampMilliseconds(): Long
}

class MockTimestampProvider : TimestampProvider {
    private var timestampValues: MutableList<Long> = mutableListOf()
    private var lastValue: Long = 0

    fun setNextTimestamp(value: Long) {
        timestampValues.add(value)
    }

    override fun getTimestampMilliseconds(): Long {
        lastValue = timestampValues.firstOrNull() ?: lastValue
        timestampValues.removeFirstOrNull()
        return lastValue
    }
}

MockTimestampProvider allows setting predefined values that are returned when the getTimestampMilliseconds() is called. The reason for using a "Queue" for the values will be explained later, but it has to do iOS UI testing.

Setting up the in-memory database driver

The in-memory driver creation is done by an expect-actual function:

// commonMain
expect fun createInMemorySqlDriver(): SqlDriver

The Android in-memory function is pretty simple because it just uses the JVM JDBC driver:

// androidMain
actual fun createInMemorySqlDriver(): SqlDriver {
    val driver: SqlDriver = JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY)
    AppDatabase.Schema.create(driver)
    return driver
}

For iOS, it gets more complicated:

// iosMain
private var index = 0

actual fun createInMemorySqlDriver(): SqlDriver {
    index++
    val schema = AppDatabase.Schema
    return NativeSqliteDriver(
        DatabaseConfiguration(
            name = "test-$index.db",
            version = schema.version,
            create = { connection ->
                wrapConnection(connection) { schema.create(it) }
            },
            upgrade = { connection, oldVersion, newVersion ->
                wrapConnection(connection) { 
                    schema.migrate(it, oldVersion, newVersion) 
                }
            },
            inMemory = true
        )
    )
}

The base function was created by Touchlab in their KaMPKit starter. I just added the index variable, so the database name is different on each invocation. Without this, the same database is reused in each test case, making the tests unpredictable (running order changes the test outcome).

The createInMemorySqlDriver() function is then used by Koin to introduce the correct database driver to the dependency graph:

val testModule = module {
    single { MockTimestampProvider() }
    single<TimestampProvider> { get<MockTimestampProvider>() }
    single<SqlDriver> { createInMemorySqlDriver() }
}

Testing items retrieval

class GetTimestampItemsTest : KoinComponent {

    private val tableQueries: TableQueries by inject()
    private val systemUnderTest: GetTimestampItems by inject()

    @BeforeTest
    fun setUp() {
        startTestKoin()
    }

    @AfterTest
    fun tearDown() {
        stopTestKoin()
    }

    @Test
    fun `The date is correctly mapped`() = runTest {
        tableQueries.insertItem(1652980264000)
        tableQueries.insertItem(1652980224000)

        val result = systemUnderTest.execute().first()

        assertSoftly {
            result shouldHaveSize 2
            result[0].name shouldBe "2022-05-19T17:11:04"
            result[1].name shouldBe "2022-05-19T17:10:24"
        }
    }
}

In the set-up phase, two items are inserted to the database, then the UseCase retrieves the items and the test case verifies that the timestamp is correctly formatted.

Android UI Tests

Setting up the in-memory database driver

The significant difference with the in-memory database driver for Android UI tests is that the Android driver has to be used:

AndroidSqliteDriver(
    schema = AppDatabase.Schema,
    context = get(),
    name = null,
)

The Android OS does not support the JVM database driver, so the normal AndroidSqliteDriver is required, however passing null as the name of the database, makes the database an in-memory one:

/**
* @param name Name of the database file, or null for an in-memory database.
* @return This
*/
@NonNull
public Builder name(@Nullable String name) {
    mName = name;
    return this;
}

The test

The UI test cases focus on adding a new item and updating an existing one. As I stated previously, Koin usage for testing is covered in my other article.

class MatchScreenTest : KoinComponent {

    @get:Rule
    val composeTestRule = createAndroidComposeRule<MainActivity>()
    val mockTimestampProvider: MockTimestampProvider by inject()

    @Before
    fun setUp() {
        ...
    }

    @Test
    fun addingAnItemPopulatesTheListWithCurrentTimestamp() {
        mockTimestampProvider.setNextTimestamp(1653823433000)

        composeTestRule.onNodeWithText("Add").performClick()

        composeTestRule.waitUntilCatching(1000) {
            composeTestRule.onNodeWithText("2022-05-29T11:23:53").assertIsDisplayed()
        }
    }

    @Test
    fun refreshingAnItemUpdatesTheNameWithCurrentTimestamp() {
        mockTimestampProvider.setNextTimestamp(0)
        composeTestRule.onNodeWithText("Add").performClick()
        mockTimestampProvider.setNextTimestamp(1653823433000)

        composeTestRule.onNodeWithContentDescription("Refresh").performClick()

        composeTestRule.waitUntilCatching(1000) {
            composeTestRule.onNodeWithText("2022-05-29T11:23:53").assertIsDisplayed()
        }
    }

    private fun ComposeTestRule.waitUntilCatching(
        timeoutMillis: Long, assertion: () -> Unit
    ) = ...
}

The test cases set a pre-defined value for the TimestampProvider just as the integration test did, then it executes an action and verifies if the correct UI text is displayed.

iOS UI Tests

Setting up the in-memory database driver

The same createInMemorySqlDriver() can be used just like in the integration tests.

Setting up dependency injection

Using Koin in iOS UI testing is not as straight forward as for Android, so I'll quickly run through the steps.

fun setUpKoinForTesting() {
    val modules = listOf(
        *sharedModules,
        module {
            single<SqlDriver> { createInMemorySqlDriver() }
            single { MockTimestampProvider() }
            single<TimestampProvider> { get<MockTimestampProvider>() }
        }
    )
    unloadKoinModules(modules)
    loadKoinModules(modules)
}

The setUpKoinForTesting() function needs to be called between every iOS test case to "reload" the database.

object TestDoubleRetriever : KoinComponent {

    val mockTimestampProvider: MockTimestampProvider
        get() = get()
}

TestDoubleRetriever is just a helper object to make test double retrieval easier on iOS.

The test

The main difference between Android and iOS UI testing is that on iOS, we are not allowed to touch production code inside the test. So, calling setUpKoinForTesting() in the setUp function will not work.

The iOS testing framework allows sending so-called launch arguments:

app.launchArguments = ["enable-testing", "-mock-time", "0,1653823433000"]
app.launch()

These values can be used in the production code to set up the test doubles to the correct state:

#if DEBUG
if CommandLine.arguments.contains("enable-testing") {
    SetUpKoinForTestingKt.setUpKoinForTesting()
    UserDefaults.standard.string(forKey: "mock-time")?
        .split(separator: ",")
        .map { timeString in
            Int64(timeString)
        }
        .compactMap { $0 }
        .forEach { time in
            TestDoubleRetriever.shared.mockTimestampProvider
                .setNextTimestamp(value: time)
        }
}
#endif

The test cases are pretty much the same as for the Android app, just with a different test double set up:

class KaMPKitiOSUITests: XCTestCase {
    let app = XCUIApplication()

    func testAddingAnItemPopulatesTheListWithCurrentTimestamp() {
        app.launchArguments = ["enable-testing", "-mock-time", "1653823433000"]
        app.launch()
        
        app.staticTexts["Add"].tap()

        let itemExists = app.staticTexts["2022-05-29T11:23:53"].exists
        XCTAssert(itemExists)
    }
    
    func testRefreshingAnItemUpdatesTheNameWithCurrentTimestamp() {
        app.launchArguments = ["enable-testing", "-mock-time", "0,1653823433000"]
        app.launch()
        app.staticTexts["Add"].tap()
        
        app.buttons["Refresh"].tap()

        let itemExists = app.staticTexts["2022-05-29T11:23:53"].exists
        XCTAssert(itemExists)
    }
}

As you can see, the TimestampProvider values have to be defined before the app even launches. This limitation enforces that the Kotlin test doubles have to provide a way of defining multiple return values beforehand, and that is the reason for the "Queue" in the MockTimestampProvider.

Summary

I hope that this simple example shows how SQLDelight in-memory drivers can be helpful when testing the application.

Besides SQLDelight I've also covered Mocking Ktor network requests and Apollo GraphQL fake networking which might interest you. Additionally, I have an article series about what testing mistakes you should avoid if you want to improve your test suite.

Why use in-memory databases?

App features

The App Architecture

Database Schema

Integration tests

Testing added items

TimestampProvider test double

Setting up the in-memory database driver

Testing items retrieval

Android UI Tests

Setting up the in-memory database driver

The test

iOS UI Tests

Setting up the in-memory database driver

Setting up dependency injection

The test

Summary

The UI tests in action