How to Write Unit Tests for SQLDelight on KMM

Question

I'm wondering how to write unit tests for SQLDelight on KMM. First of all, I can't even add the SQLDelight dependency correctly.

    val commonTest by getting {
        dependencies {
            implementation(kotlin("test-common"))
            implementation(kotlin("test-annotations-common"))
            // SQLDelight tests
            implementation("com.squareup.sqldelight:sqlite-driver:1.4.3")
        }
    }

After I added the dependency and then synced the project, the project didn't even build. Can someone please tell me if this is the correct way to add the sqlite driver dependency?

Any help would be greatly appreciated!

Answer 1

I was having issues with using Context for the tests and found it quicker to use an in memory database instead. This also has the benefit of not needing a device for the tests.

The way I have done it:

1. Add JdbcSqliteDriver to androidTest sourceset (build.gradle under "shared")

val androidTest by getting {
    dependencies {
        // ...
        implementation("com.squareup.sqldelight:sqlite-driver:1.4.4")
    }
}

2. Add expect/actual function to create the driver:

• In a file under "commonTest" directory (e.g. createTestSqlDriver.kt)

internal expect fun createTestSqlDriver(): SqlDriver

• In a file under "androidTest"

internal actual fun createTestSqlDriver(): SqlDriver {
    return JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY).apply {
        MyDatabase.Schema.create(this)
    }
}

3. Now I can create and use the database in your tests, under "commonTest". Something like:

internal class MyClassDbTests {

    private val sqlDriver = createTestSqlDriver()
    private val myDatabase = MyDatabase(sqlDriver)

    fun insert_addItems_verifyCorrectNumOfItemsInDb() {
        // GIVEN
        val myQueries = myDatabase.mydbQueries
        myQueries.deleteAllEvents()
        
        val numItemsBeforeInsertion = myQueries.selectAll().executeAsList().size

        // WHEN
        myQueries.insertItem(1, 2, 3)
        myQueries.insertItem(10, 20, 30)

        val numItemsAfterInsertion = myQueries.selectAll().executeAsList().size

        // THEN
        assertEquals(0, numItemsBeforeInsertion)
        assertEquals(2, numItemsAfterInsertion)
    }
}

I found the following posts useful:

• SQLDelight 1.x Quick Start Guide for Android
• SQLDelight Testing

Answer 2

Example

package com.viki.vikilitics_kmm

import com.squareup.sqldelight.sqlite.driver.JdbcDriver
import com.google.common.truth.Truth.assertThat
import com.squareup.sqldelight.db.SqlDriver
import com.squareup.sqldelight.sqlite.driver.JdbcSqliteDriver
import com.viki.vikiliticskmm.Event
import com.viki.vikiliticskmm.EventQueries
import org.junit.After
import org.junit.Before
import org.junit.Test
import java.sql.DriverManager
import java.sql.Connection


class AndroidEventDatabaseTest {
    private lateinit var queries: EventQueries

    // When your test needs a driver
    @Before
    fun before() {
        val driver = JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY)

        val database = EventDatabase(driver)

        EventDatabase.Schema.create(driver)
        queries = database.eventQueries
    }


    @Test
    fun `select all events`() {
        queries.insertEvent("1", "2", "{Click,Open}")
        queries.insertEvent("2", "2", "{Click,Close}")

        assertThat(queries.selectAllEvents().executeAsList())
            .containsExactly(
                Event(
                    as_counter = "1",
                    t_ms = "2",
                    event_map = "{Click,Open}"
                ),
                Event(
                    as_counter = "2",
                    t_ms = "2",
                    event_map = "{Click,Close}"
                )
            )
    }

    @Test
    fun `delete multiple events`() {

        queries.insertEvent("1", "1", "{Click,Open}")
        queries.insertEvent("1", "2", "{Click,Close},{Read,Open}")
        queries.insertEvent("1", "3", "{Click,Open}")
        queries.insertEvent("2", "3", "{Click,Open}")

        val event1 = listOf("1","3")
        val event2 = listOf("1","2")
        val event3 = listOf("1","4")
        val eventList = listOf(event1,event2,event3)
        for (event in eventList){
            queries.deleteEventListByKey(event.elementAt(0), event.elementAt(1))
        }

        assertThat(queries.selectAllEvents().executeAsList())
            .containsExactly(
                Event(
                    as_counter = "1",
                    t_ms = "1",
                    event_map = "{Click,Open}"
                ), Event(
                    as_counter = "2",
                    t_ms = "3",
                    event_map = "{Click,Open}"
                ),
            )

    }

    @Test
    fun `delete single event`() {

        queries.insertEvent("1", "1", "{Click,Open}")
        queries.insertEvent("1", "2", "{Click,Close},{Read,Open}")
        queries.insertEvent("1", "3", "{Click,Open}")
        queries.insertEvent("2", "3", "{Click,Open}")
        queries.deleteEventListByKey("1", "3")

        assertThat(queries.selectAllEvents().executeAsList())
            .containsExactly(
                Event(
                    as_counter = "1",
                    t_ms = "1",
                    event_map = "{Click,Open}"
                ), Event(
                    as_counter = "2",
                    t_ms = "3",
                    event_map = "{Click,Open}"
                ),
                Event(
                    as_counter = "1",
                    t_ms = "2",
                    event_map = "{Click,Close},{Read,Open}"
                )
            )

    }

    @Test
    fun `update events`() {

        queries.insertEvent("1", "2", "{Click,Open}")
        queries.insertEvent("1", "2", "{Click,Close}")
        assertThat(queries.selectAllEvents().executeAsList())
            .containsExactly(
                Event(
                    as_counter = "1",
                    t_ms = "2",
                    event_map = "{Click,Close}"
                )
            )


    }

}

References https://github.com/touchlab/KaMPKit/blob/main/shared/src/commonTest/kotlin/co/touchlab/kampkit/SqlDelightTest.kt

Answer 3

Thanks for the answer! I encountered another issue tho. "Expected function 'createDriver' has no actual declaration in module KMM.shared (test) for JVM". In the KaMPKit project, I didn't find anything JVM-related.

Getting Started on JVM with SQLite contains the necessary instructions.

You need to add a dependency

dependencies {
  implementation "com.squareup.sqldelight:sqlite-driver:1.5.0"
}

into your "jvmMain" sourceSet, next implement actual fun createDriver in your "jvmMain" module.

---

I appreciate Kevin's answer and add that tests using SqlDeLite should be placed in platform modules ("androidTest" and "iosTest"), but not in "commonTest".

You need provide to your SUT with an actual driver's implementation, use app context. For unit tests you need a substitute of context, for example look towards Robolectric.

Add a dependency

dependencies {
    implementation("org.robolectric:robolectric:4.4")
}

into "androidTest" sourceSet (i don't know what can be used for iOS), get the application context:

val context = ApplicationProvider.getApplicationContext<Context>()

and use it to get the platform implementation of your driver:

val driver = DatabaseDriverFactory(context).createDriver(Database.Schema, "test.db")

Answer 4

You can see a basic example in KaMPKit.

If you have sqldelight configured in your non-test code, you don't need the driver dependency on it's own in the commonTest.

In our test code, we have an expect that creates the db connection for test.

internal expect fun testDbConnection(): SqlDriver

Then in iOS and Android code, the actual definitions.

The dependency config looks (roughly) like this:

commonMain {
  implementation("com.squareup.sqldelight:runtime:1.4.4")
}

androidMain {
 implementation("com.squareup.sqldelight:android-driver:1.4.4")
}

iosMain {
  implementation("com.squareup.sqldelight:native-driver:1.4.4")
}

With that, you should be able to write sqldelight tests.