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!

Kappenne

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

Giusto

Used this IN_MEMORY testing is faster – Warwick

I would recommend to never use this answer. Every platform must have implement their own SqlDriver otherwise you will get unexpected behavior between your tests and production code – Epitome

Answer 2

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.

Rockribbed

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. – Kappenne

And what happens after the tests? We must clean up after ourselves manually or is there any flag to pass so db is cleared once tests are done? – Epitome

The code in the sample needs to be updated. If you use an in-memory db on iOS and give it a name, you should explicitly close it. If you leave off the name, once the test finishes, it'll just go away because it's an in-memory db. In either case, they don't write to disk, so when test process stops you should be OK. – Rockribbed

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")

Argos

And why we should do it on JVM? Because I want my code to be safe in all platforms not only on JVM – Epitome

Answer 4

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

Brigand

KMM is not all about Android. I don't get why you all copy-paste the same answer and make it look like yours – Epitome