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

G'day:

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:

• The bit that represents the object to the application code.
• The bit that handles the wiring back to the DB. I look at the companion object stuff in an earlier article, in case a refresher would help: Kotlin: there's no such thing as static, apparently.

It's all pretty clear, I think?

Now the test:

@Test
@EnabledIfEnvironmentVariable(
    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) {
                json()
            }
        }.use { client ->
            val response = client.post(webserviceUrl) {
                contentType(ContentType.Application.Json)
                setBody(six)
            }

            response.status shouldBe HttpStatusCode.Created

        }
        Database.connect(
            "jdbc:mysql://localhost:3308/db1",
            driver = "com.mysql.cj.jdbc.Driver",
            user = "user1",
            password = System.getenv("MARIADB_PASSWORD")
        )
        transaction {
            addLogger(StdOutSqlLogger)

            TranslatedNumber.findById(six.id).asClue {
                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.id, numbers.en, numbers.mi FROM numbers WHERE numbers.id = 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:

Database.connect(
    // 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.

Righto.

--
Adam