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.

[IP14Y3RI]

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?

[StefanLiebig]

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()
    }

[belovaf]

Working solution without any mocking frameworks.
Big advantage of this solution is that we don't touch actual singleton objects.

fun <ID : Comparable<ID>, T : Entity<ID>> EntityClass<ID, T>.newDetached(init: T.() -> Unit) = newDetached(null, init)

fun <ID : Comparable<ID>, T : Entity<ID>> EntityClass<ID, T>.newDetached(id: ID?, init: T.() -> Unit) =
    transaction(noOpDatabase) { new(id, init) }

private object NoOpTransactionManager : TransactionManager {
    override var defaultIsolationLevel = -1
    override var defaultRepetitionAttempts = -1
    override fun bindTransactionToThread(transaction: Transaction?) {}
    override fun currentOrNull() = NoOpTransaction
    override fun newTransaction(isolation: Int, outerTransaction: Transaction?) = NoOpTransaction
}

private val noOpDatabase = Database.connect(
    getNewConnection = { error("getNewConnection not implemented") },
    manager = { NoOpTransactionManager }
)

private object NoOpTransaction : Transaction(object : TransactionInterface {
    override val connection: ExposedConnection<*> get() = error("connection not implemented")
    override val db: Database = noOpDatabase
    override val outerTransaction = null
    override val transactionIsolation = -1
    override fun close() {}
    override fun commit() {}
    override fun rollback() {}
})

[greekZorba]

I didn't want to depend specific DB and only want to focus on business logic.

this is my solution without DB dependency by using mockk and I reference @guky-dhl 's code and Tapac's explanation.

hope this solution helps. (I am using exposed 0.37.3 version)

internal 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) }, null, { this })
    }

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

fun mockDatabase() = TestTransactionManager().apply()

internal class RestaurantServiceTest : BehaviorSpec({
    mockDatabase() // call mockTransactionManager
    mockkObject(Restaurant) // exposed's Entity
    val restaurantService = RestaurantService()

    Given("find restaurant") {
      When("by region") {
        val givenRegion = "seoul"
  
        every {Restaurant.findByRegion(givenRegion) } returns
        // here is initiate mocked Entity and Restaurants is exposed's table
        listOf(
            Restaurant(EntityID(1, Restaurants)).apply {
              this._readValues = ResultRow.createAndFillValues(
                mapOf(
                  Restaurants.region to "seoul",
                  Restaurants.regionId to 1,
                  Restaurants.name to "delicios bbq restaurant"
                  // .... other fields
                )
             )
           }
        )
  
       val restaurants = restaurantService.getByRegion(givenRegion)
      
       Then("matched restaurant") {
         assertEquals(1, restaurants.size)
         assertTrue(restaurants.all { it.region == givenRegion })
       }
     }
  }

})

[LeonidYavorskyi]

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.

@Tapac the approach with creating new Entity and setting _readValues field with ResultSet with expected values works fine, but how to set relations from the other tables if needed?

class MyEntity(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<MyEntity>(MyEntities)

    var name by Migs.name
    var unit by Migs.unit
    val relations by MyRelationEntity referrersOn MyRelations.id
}


val myFieldIndex: Map<Expression<*>, Int> = mapOf(
        MyEntities.id to 0,
        MyEntities.name to 1,
        MyEntities.unit to 2,
)

val myResultRowData = arrayOfNulls<Any?>(3)
myResultRowData[0]=id
myResultRowData[1]=name
myResultRowData[2]=unit

val e = MyEntity(EntityID(UUID.randomUUID(), Migs))
e._readValues = ResultRow(fieldIndex = myFieldIndex, data = myResultRowData)

// e.setRelations ???

[KerTakanov]

I have an issue with your implementation, I get a java.lang.IllegalStateException: connection not implemented error which is from the code you did :
override val connection: ExposedConnection<*> get() = error("connection not implemented")

This is when I try to read a value from my DBO
Any solution on this ?

Is there any better way to unit test services using repositories made with exposed ?

[delitescere]

I'm disappointed that it's 2022 and data access frameworks are still being created that don't have testability as a first-class feature. Please take into account principled developers who want to test their application properly.

[Prakkkmak]

It's 2023, I really want to just mockk the exposed framework and don't have to create fake db.

[tomhol]

I'm trying to use the solution provided by greekZorba above. But I'm not using ORM/DAO in my project (i.e. no Entities), but just DSL. I.e. in my code I have something like that:

UsersTable.select {
   UsersTable.name eq "Alice"
}.orderBy(UsersTable.surname).map(...)

Then I'd like to mockk the select, but I have no idea how... I tried few things, e.g.:

every {UsersTable.select(any<SqlExpressionBuilder.()->Op<Boolean>>()) } returns Query(UsersTable, null)
...

I tried several approaches, but getting out of ideas. Can someone provide an illustrative example of Unit Test based on mockk where only the select is mocked? (I hope I could work out the others then ...delete, insert, etc...)

Ideally I'd like to write the mock (for select) in a way it returns listOf<ResiultRow>

Thanks, Tomas.

[BreakBB]

Any progress on this?

[terickson87]

I was running into this same issue, and the way I was able to handle this was to create a new data class which I transformed into after reading the Entity item from the (postgres) DB. I also used a TestContainers container for the DB testing.

So for me, I create the Note data class in addition to the DbNote DAO entity which I had been trying to mock, and then directly went from the DbNote to the Note in the NotesAccessor implementation, allowing me to mock the NotesAccessor directly and in this way work around the lack of DAO entity mocking available in Exposed.

So the combination of this and the TestContainers library yields good test coverage.

interface NotesAccessor {
    fun createNote(noteRequest: NoteRequest): Note
    fun getAllNotes(): List<Note>
    fun getNoteById(id: Int): Note?
    fun updateNoteById(id: Int, noteRequest: NoteRequest): Note?
    fun deleteNoteById(id: Int): Boolean
}

data class Note(val id: Int, val createdAt: Instant, val updatedAt: Instant, val body: String)

class SqlNotesAccessor(private val database: Database) : NotesAccessor {

    companion object {
        val DB_ZONE_OFFSET_UTC: ZoneOffset = ZoneOffset.UTC
    }

    object NotesTable : IntIdTable() {
        val createdAt = datetime("created_at")
        val updatedAt = datetime("updated_at")
        val body = text("body")
    }

    class DbNote(id: EntityID<Int>): IntEntity(id){
        companion object: IntEntityClass<DbNote>(NotesTable)
        var createdAt by NotesTable.createdAt
        var updatedAt by NotesTable.updatedAt
        var body by NotesTable.body
    }

    private fun DbNote.toNote(): Note =
        Note(this.id.value,
            this.createdAt.toInstant(DB_ZONE_OFFSET_UTC),
            this.updatedAt.toInstant(DB_ZONE_OFFSET_UTC),
            this.body)

    override fun createNote(noteRequest: NoteRequest): Note =
        transaction(database) {
            DbNote.new {
                body = noteRequest.body
                createdAt = LocalDateTime.ofInstant(Instant.now(), DB_ZONE_OFFSET_UTC);
                updatedAt = LocalDateTime.ofInstant(Instant.now(), DB_ZONE_OFFSET_UTC);
            }
        }.toNote()

    override fun getAllNotes(): List<Note> =
        transaction(database) {
            DbNote.all().toList()
                .map { it.toNote() }
        }

    override fun getNoteById(id: Int): Note? =
        getDbNoteById(id)?.toNote()

    private fun getDbNoteById(id: Int): DbNote? =
        transaction(database) {
            DbNote.findById(id)
        }

    override fun updateNoteById(id: Int, noteRequest: NoteRequest): Note? =
        getDbNoteById(id)?.let {
            transaction(database) {
                it.body = noteRequest.body
                it.updatedAt = LocalDateTime.ofInstant(Instant.now(), DB_ZONE_OFFSET_UTC);
            }
        }?.let {
            getNoteById(id)
        }

    override fun deleteNoteById(id: Int): Boolean =
        transaction(database) {
            DbNote.findById(id)?.delete()?.let { true } ?: false
        }
}

class SqlNotesAccessorTest : AnnotationSpec() {

    companion object {
        private const val DB_NAME = "test-db"
        private const val DB_USER = "test-user"
        private const val DB_PASSWORD = "test-password"
        val testPostgresContainer: PostgreSQLContainer<Nothing> =
            PostgreSQLContainer<Nothing>("postgres:16").apply {
                withDatabaseName(DB_NAME)
                withUsername(DB_USER)
                withPassword(DB_PASSWORD)
            }
        const val TEST_NOTE_BODY = "Test Note Body"
        const val UPDATED_NOTE_BODY = "Updated Test Note Body"

        lateinit var sqlNotesAccessor: SqlNotesAccessor
    }

    @BeforeAll
    fun setup() {
        testPostgresContainer.start()

        val database = Database.connect(
            url = testPostgresContainer.jdbcUrl,
            user = testPostgresContainer.username,
            password = testPostgresContainer.password
        )

        transaction(database) {
            SchemaUtils.create(SqlNotesAccessor.NotesTable)
        }

        sqlNotesAccessor = SqlNotesAccessor(database)
    }

    @AfterAll
    fun teardown() {
        testPostgresContainer.stop()
    }

    @Test
    fun crudNote() = runBlocking {
        //Create
        val beforeCreate = Instant.now()
        val noteRequest = NoteRequest(TEST_NOTE_BODY)
        val newNote = sqlNotesAccessor.createNote(noteRequest)
        newNote.id.shouldBeTypeOf<Int>()
        newNote.body.shouldBe(TEST_NOTE_BODY)
        val afterCreate = Instant.now()
        newNote.createdAt.shouldBeAfter(beforeCreate)
        newNote.createdAt.shouldBeBefore(afterCreate)
        newNote.updatedAt.shouldBeAfter(beforeCreate)
        newNote.updatedAt.shouldBeBefore(afterCreate)

        //Read
        val readNote = sqlNotesAccessor.getNoteById(newNote.id)
        readNote.shouldNotBeNull().id.shouldBe(newNote.id)
        readNote.createdAt.shouldBeAfter(beforeCreate)
        readNote.createdAt.shouldBeBefore(afterCreate)
        readNote.updatedAt.shouldBeAfter(beforeCreate)
        readNote.updatedAt.shouldBeBefore(afterCreate)

        //Read All
        val readNotes = sqlNotesAccessor.getAllNotes()
        readNotes.size.shouldBe(1)
        readNotes[0].id.shouldBe(newNote.id)

        //Update
        val beforeUpdate = Instant.now()
        val updateRequest = NoteRequest(UPDATED_NOTE_BODY)
        val updatedDbNote = sqlNotesAccessor.updateNoteById(newNote.id, updateRequest)
        updatedDbNote.shouldNotBeNull().id.shouldBe(newNote.id)
        updatedDbNote.body.shouldBe(UPDATED_NOTE_BODY)
        val afterUpdate = Instant.now()
        updatedDbNote.createdAt.shouldBeAfter(beforeCreate)
        updatedDbNote.createdAt.shouldBeBefore(afterCreate)
        updatedDbNote.updatedAt.shouldBeAfter(beforeUpdate)
        updatedDbNote.updatedAt.shouldBeBefore(afterUpdate)

        //Delete
        val wasDeleted = sqlNotesAccessor.deleteNoteById(newNote.id)
        wasDeleted.shouldBeTrue()
        val deletedDbNote = sqlNotesAccessor.getNoteById(newNote.id)
        deletedDbNote.shouldBeNull()
    }

}

The mocking then works like this:

val notesAccessorMock: NotesAccessor = mockk()

val noteNow = Instant.now()
val testNote = Note(17, noteNow, noteNow, "test Note Body")

every { notesAccessorMock.getNoteById(id) } returns testNote

[lynheo]

Please provide a solution for mocking Entity.

[imulab]

I switched strategy after struggling with mocking DAO API entities.

While they do provide an extra level of convenience than the DSL API, it is not worth the burden of not being able to easily mock them, and the subsequent inability to write simple unit tests for functions that require them.

Now, I exclusively use the DSL API and manually write domain objects which can be constructed from ResultRow. This way, the entire data access layer functions can just be easily mocked (or just provide dummy implementations) since now I have complete control of the domain objects.

And at last, use actual integration tests that involve real databases to test the data access layer functions.