Exposed: Testing / Mocking database

[matejdro]

Are there any examples on how would one properly test code using this library, particularly mocking out the database calls?

It seems like a recommended way to use most things (database object, transactions, table objects) is global singletons, which cannot be easily mocked. Also entity classes are tightly coupled to the table singletons, making it hard to convert table to non-singleton and also making it hard to create dummy entity objects with fake data.

[Tapac]

You are right, existing Exposed architecture is not very "mockable", but in the most cases you can replace mocking with light-weight embedded database (like H2 or SQLite).
It helps to make fast-fail checks of queries which never succeed or broken table mappings.

I agree that sometimes you want to use mocks, but I guess it's better to cover DAO layer interfaces with mocks. If you want to return and entities from its methods, then you can create new Entity and set _readValues field with ResultSet with expected values.

[matejdro]

Isn't creating test SQLite database a bit heavyweight for unit tests?

[Tapac]

It depends on your use-cases if you have to create a lot of tables with initial records before the test then it can take time. But you save the time which you have to spend to mocking/spying everything and you can concentrate on testing your business code which will be work the same in production (in place of working with Exposed).
JFYI: running Exposed 167 tests on SQLite only took 4s and there are a lot of create/drop tables in it.

[Wnzebkhan]

Has there been any progress over the last months on the aspect of mocking the database which Exposed is connecting to? Are there any improvements/adjustments on the Exposed Architecture to make it more mockable?

[StefanHub]

For integration testing with MySQL we are using https://github.com/wix/wix-embedded-mysql .
This works pretty well!

[mlthlschr]

I wonder why we have to mock a lightweight database, whilst there should be the possibility to use the tools found here? Unfortunately this package is not included in gradle builds.

[Ray-Eldath]

well, sometimes I just want to create an instance of my DAO and test the business logic solely, and still that's hard... the only constructor require an EntityId.

I dont know if there's something like IntEntityClass.mockNewInstance, if not, I think add this will hugely improve the test experience.

[guky-dhl]

Recently faced issue related to unit test code what is executed in exposed transaction:
Caused by: java.lang.IllegalStateException: Please call Database.connect() before using this code

So my solution based on mockk:

import io.mockk.every
import io.mockk.mockk
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.Transaction
import org.jetbrains.exposed.sql.transactions.TransactionManager

class TestTransactionManager : TransactionManager {
    override var defaultIsolationLevel = 0
    override var defaultRepetitionAttempts = 0
    private val mockedDatabase: Database = mockk(relaxed = true)

    override fun bindTransactionToThread(transaction: Transaction?) {

    }

    override fun currentOrNull(): Transaction? {
        return transaction()
    }

    private fun transaction(): Transaction {
        return mockk(relaxed = true) {
            every { db } returns mockedDatabase
        }
    }

    override fun newTransaction(isolation: Int, outerTransaction: Transaction?): Transaction {
        return transaction()
    }

    fun apply() {
        TransactionManager.registerManager(mockedDatabase, this@TestTransactionManager)
        Database.connect({ mockk(relaxed = true) }, { this })
    }

    fun reset() {
        TransactionManager.resetCurrent(null)
        TransactionManager.closeAndUnregister(mockedDatabase)
    }
}

val manager = TestTransactionManager()
fun mockDatabase() = manager.apply()
fun unmockDatabase() = manager.reset()

and usage on Junit5 would be like:

internal class MyTest {
    @BeforeEach
    internal fun setUp() {
        mockDatabase()
    }

    @AfterEach
    internal fun tearDown() {
        unmockDatabase()
    }
}

I believe it could easily translate to other mocking solutions.
But I haven't tried to use it with entity part of Exposed since I don't use it.

[YokiToki]

Hi there! Here is my solution for postgres.

import io.zonky.test.db.postgres.embedded.EmbeddedPostgres
import org.jetbrains.exposed.dao.IntIdTable
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.insert
import org.jetbrains.exposed.sql.transactions.transaction
import org.junit.jupiter.api.AfterAll
import org.junit.jupiter.api.BeforeAll
import org.junit.jupiter.api.Test
import javax.sql.DataSource

class SQLTest {

    @Test
    fun test() {
        transaction { 
            // DB requests
        }
    }

    companion object {
        private val embeddedPostgres: EmbeddedPostgres = EmbeddedPostgres.start()
        private val dataSource: DataSource = embeddedPostgres.postgresDatabase

        @JvmStatic
        @BeforeAll
        fun bootstrap() {
            Database.connect(dataSource)
            transaction {
                SchemaUtils.create(TestTable)

                TestTable.insert {
                    it[integerValue] = 1
                    it[varcharValue] = "Test String"
                }
            }
        }

        @JvmStatic
        @AfterAll
        fun shutdown() {
            embeddedPostgres.close()
        }
    }
}

object TestTable : IntIdTable() {
    val integerValue = integer("integer_value")
    val varcharValue = varchar("varchar_value", 50)
}

[Jadarma]

I've also encountered this issue, but decided not to use mocks, as the static nature of the library makes it expensive and cumbersome to use something like MockK's mockkObject().

Instead, I went for a simple, in-memory data base for my tests.
This has the added benefit that you also get to test the SQL calls you make against an actual database.
Of course, there is a bit of overhead, but not much.
On my machine, it's about 400ms per test class.

To help stub things I made this helper class.
I'm using H2 in memory, but I'm guessing you can use any stubbing method you like, like @YokiToki 's EmbeddedPostgress example.

Here's the code:

/**
 * A test helper that creates an in-memory database for the lifetime of the test.
 *
 * @property databaseName The name of the temporary database. Randomly generated by default.
 * @property tables An array of tables to initialize. Will be dropped and created before each individual test.
 */
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
abstract class DatabaseTest(
    private val databaseName: String = "test_db_${Random.nextInt(0, 9999)}",
    private val tables: Array<Table> = emptyArray(),
) {

    @Suppress("MemberVisibilityCanBePrivate")
    protected val database = Database.connect("jdbc:h2:mem:$databaseName;DB_CLOSE_DELAY=-1;IGNORECASE=true;")

    @BeforeEach
    private fun databaseSetUp() {
        transaction(database) {
            SchemaUtils.drop(*tables)
            SchemaUtils.create(*tables)
        }
    }

    @AfterAll
    private fun databaseTearDown() = TransactionManager.closeAndUnregister(database)

}

And a usage example where Users is just a LongIdTable:

class UserServiceTest : DatabaseTest("service_test_db", arrayOf(Users)) {

    private val userService = UserService(database)

    @Test
    fun `Can identify a user by credentials`() {
        User.new(1234L) { name = "John Doe", passwordHash = verySecureHash("password123") }
        val actual = userService.authenticate("John Doe", "password123")
        assertEquals(1234L, actual.id)
    }

}

Note that since the database is exposed in the test class, you can use it for dependency injection, in case your classes declare transactions explicitly. In a project with a single database, it shouldn't be necessary. With a bit of modification, we can also support multiple databases per test, but it's a bit more complicated:

@TestInstance(TestInstance.Lifecycle.PER_CLASS)
abstract class MultiDatabaseTest(databases: Map<String, Array<Table>>) {

    private val databases: Map<String, Pair<Database, Array<Table>>> =
        databases
            .mapValues { (databaseName, tables) ->
                Database.connect("jdbc:h2:mem:$databaseName;DB_CLOSE_DELAY=-1;IGNORECASE=true;") to tables
            }

    protected fun database(name: String) = databases.getValue(name).first

    @BeforeEach
    private fun databaseSetUp() {
        databases.values.forEach { (database, tables) ->
            transaction(database) {
                SchemaUtils.drop(*tables)
                SchemaUtils.create(*tables)
            }
        }
    }

    @AfterAll
    private fun databaseTearDown() =
        databases.values.forEach { TransactionManager.closeAndUnregister(it.first) }
}

Hope this helps.

[JuanCamiloRada]

A simple solution using mockK

    mockkStatic("org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt")
    every { transaction(dbMock, any<Transaction.() -> List<Category>>()) } answers {
        val execFunction: Transaction.() -> List<Category> = secondArg()
        dbTransactionMock.execFunction()
    }