Sunday 9 October 2022

Kotlin / TDD: using JetBrains/Exposed to read from the DB


Earlier today I wrote "Kotlin / TDD: writing the tests for a small web service". I focused on learning how to make HTTP requests and test the results thereof. The last bit I needed to do for the exercise I had at hand was to write a test to verify my POST request was actually writing to the DB. To do this I'd need to work out how to actually make a DB call in Kotlin, and I've not got that far yet. I decided to leave that to the next article, and come back to it later. Then I got to thinking: everything else has proven to be really easy so far: I bet this will be too. And it started to bug me so I decided "to hell with it: let's find out". So here I am again. Spoilers: it was easy, and this will be a short article.

It saves the new object to the database

Full disclosure: no it doesn't. I have not modified the test web service to do anything of the sort: it does not touch the DB, it just responds saying "yeah, done", and I already have the test data in there. So the data is "mocked" I guess. All I'm doing here is testing that I can fetch something from the DB.

This test is more complicated. Firstly I have to define the entity that represents the data:

class TranslatedNumber(id: EntityID<Int>) : IntEntity(id) {
    object TranslatedNumbers : IntIdTable("numbers") {
        val en: Column<String> = varchar("en", 50)
        val mi: Column<String> = varchar("mi", 50)
    companion object : IntEntityClass<TranslatedNumber>(TranslatedNumbers)

    var en by TranslatedNumbers.en
    var mi by TranslatedNumbers.mi

I pretty much got all that from the docs: JetBrains/Exposed › Getting Started › Your first Exposed DAO, although I refactored things a bit.

There's two parts to this:

It's all pretty clear, I think?

Now the test:

    named = "MARIADB_PASSWORD",
    matches = ".*",
    disabledReason = "This test requires a MariaDB database, so it needs the password"
fun `It saves the new object to the database`() {
    val six = SerializableNumber(6, "six", "ono")
    runBlocking {
        HttpClient() {
            install(ContentNegotiation) {
        }.use { client ->
            val response = {

            response.status shouldBe HttpStatusCode.Created

            driver = "com.mysql.cj.jdbc.Driver",
            user = "user1",
            password = System.getenv("MARIADB_PASSWORD")
        transaction {

            TranslatedNumber.findById( {
                it shouldNotBe null
                it!!.en shouldBe six.en
                it.mi shouldBe six.mi

I've greyed-out the stuff that's mostly the same as the previous tests from the earlier article. The interesting bits are:

  • I don't want to hard-code my DB password here (especially as it's going into source control), so I use an environment variable. And if the env variable ain't set: skip the test. I was quite let down that the reason message doesn't display in the test output.
  • If I add that logger there, all the SQL statements echo to std out. This makes debugging issues easier.

    SQL: SELECT, numbers.en, numbers.mi FROM numbers WHERE = 6
  • Apparently all Exposed DB calls need to occur within one of these transaction blocks. That's not me deciding to run a single select query in a transaction, it's a hard requirement of Exposed.
  • The !! operator throws an exception at runtime if the expression is null. I guess we only need the one of them as it's implied that it's an all/nothing thing if the object has been initialised. Copilot wrote that code [cough]. However if I take it out, I get an error:

    Only safe (?.) or non-null asserted (!!.) calls are allowed on a nullable receiver of type TranslatedNumber?

    If I use ?. instead, then I need it on both en and mi.

One thing I am left wondering about here: there doesn't seem to be any direct coupling between the DB connection and the transaction block. What if I had multiple DB connections? I see there is a discussion on GitHub: Multiple databases #93, and I share the opinion that I am not in love with how they have resolved this: Transactions › Working with a multiple databases. Basically it's this:

val conn = Database.connect(
    // etc
transaction(conn) {
   // etc

I was expecting this sort of thing:

    // etc
).transaction {
   // etc

// or

val conn = Database.connect(
    // etc

// ...

conn.transaction {
   // etc

That looks more Kotlin-idiomatic to me (he says, having been using Kotlin for like a coupla weeks… ;-).

OK that was easy. One last observation: "Exposed" is a shit name. It reads like a red-banner-tabloid headline, and… it's a verb (this might have been lost on the JetBrains crew, as I don't think they're native English speakers). It's also not hugely google-able without prefixing it with "kotlin". Ah well.

And now I am giving up for the day. That's enough Kotlin. Code is here: WebServiceTest.kt and TranslatedNumber.kt. This latest tag also includes some housekeeping changes, but those are the important bits.