Our tasks vanish every time the server restarts. Time for a real database. We’ll use Exposed, JetBrains’ SQL framework for Kotlin — type-safe queries written in Kotlin instead of strings. And because we hid storage behind TaskRepository back in the CRUD post, the routes won’t change at all. That payoff is the whole point of the repository pattern.

Dependencies

Add Exposed, a connection pool, and a JDBC driver. We’ll use H2 (an in-memory database, zero setup) for now; switching to Postgres later is just a driver and a URL.

dependencies {
    implementation("org.jetbrains.exposed:exposed-core:0.58.0")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.58.0")
    implementation("com.zaxxer:HikariCP:6.2.1")
    implementation("com.h2database:h2:2.3.232")
}

(Versions move; the project generator can add Exposed for you, or check the latest. The shape is what matters.)

Tables are objects

In Exposed you describe a table as a Kotlin object, with a property per column:

import org.jetbrains.exposed.sql.Table

object Tasks : Table("tasks") {
    val id = integer("id").autoIncrement()
    val title = varchar("title", 255)
    val done = bool("done").default(false)

    override val primaryKey = PrimaryKey(id)
}

Each property is a typed column — integer, varchar, bool. Because the columns are real Kotlin properties, queries against them are checked by the compiler: misspell a column or compare it to the wrong type and the code won’t build. No string column names floating around.

Connecting and creating the schema

Connect once at startup through a Hikari connection pool, then create the table:

import com.zaxxer.hikari.*
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

fun Application.configureDatabase() {
    val pool = HikariDataSource(HikariConfig().apply {
        jdbcUrl = "jdbc:h2:mem:tasks;DB_CLOSE_DELAY=-1"
        driverClassName = "org.h2.Driver"
        maximumPoolSize = 3
    })
    Database.connect(pool)

    transaction {
        SchemaUtils.create(Tasks)
    }
}

Database.connect registers the pool as the default for the app. SchemaUtils.create issues CREATE TABLE IF NOT EXISTS from your Tasks object — convenient in dev, though for production you’ll graduate to real migrations (Flyway/Liquibase). Add configureDatabase() to module(), before the routes.

Transactions and coroutines

Every Exposed query runs inside a transaction { }. But JDBC is blocking, and Ktor handlers are coroutines — block the event loop and you’ll wreck throughput. The fix is newSuspendedTransaction, which runs the transaction on a background dispatcher and suspends instead of blocking:

import kotlinx.coroutines.Dispatchers
import org.jetbrains.exposed.sql.transactions.experimental.newSuspendedTransaction

suspend fun <T> dbQuery(block: suspend () -> T): T =
    newSuspendedTransaction(Dispatchers.IO) { block() }

Wrap every database call in dbQuery { } and your handlers stay non-blocking. One small helper, applied everywhere.

The repository, now backed by SQL

Here’s TaskRepository reimplemented against Exposed. Same method signatures as the in-memory version (now suspend), so the routes are untouched:

import org.jetbrains.exposed.sql.*

class TaskRepository {
    private fun toTask(row: ResultRow) = Task(
        id = row[Tasks.id],
        title = row[Tasks.title],
        done = row[Tasks.done],
    )

    suspend fun all(): List<Task> = dbQuery {
        Tasks.selectAll().map(::toTask)
    }

    suspend fun find(id: Int): Task? = dbQuery {
        Tasks.selectAll().where { Tasks.id eq id }.map(::toTask).singleOrNull()
    }

    suspend fun create(draft: NewTask): Task = dbQuery {
        val newId = Tasks.insert {
            it[title] = draft.title
            it[done] = draft.done
        } get Tasks.id
        Task(newId, draft.title, draft.done)
    }

    suspend fun update(id: Int, draft: NewTask): Task? = dbQuery {
        val rows = Tasks.update({ Tasks.id eq id }) {
            it[title] = draft.title
            it[done] = draft.done
        }
        if (rows == 0) null else Task(id, draft.title, draft.done)
    }

    suspend fun delete(id: Int): Boolean = dbQuery {
        Tasks.deleteWhere { Tasks.id eq id } > 0
    }
}

The Exposed DSL reads close to SQL but in Kotlin:

• Tasks.selectAll().where { Tasks.id eq id } is SELECT * FROM tasks WHERE id = ?. eq is Exposed’s typed equality.
• insert { it[title] = ... } get Tasks.id inserts and returns the generated id.
• update({ where }) { set... } and deleteWhere { } return the number of rows affected — which is exactly the “did anything match?” signal update and delete need to return null/false (and, via StatusPages, a 404).

The routes didn’t change

That’s the headline. The route handlers from the CRUD and error-handling posts call repo.find(id), repo.create(draft), and so on — and they still do. They never knew it was a hash map, so they don’t notice it’s now SQL. The one mechanical change is that the repository methods are suspend now, which Ktor handlers call without ceremony since they’re coroutines already.

Swapping H2 for Postgres in production is likewise just the pool config:

jdbcUrl = "jdbc:postgresql://localhost:5432/tasks"
driverClassName = "org.postgresql.Driver"

(plus the org.postgresql:postgresql dependency) — no repository or route changes.

Final thoughts

Exposed gives you SQL with Kotlin’s type checking instead of stringly-typed queries, and the discipline of routing every call through dbQuery { } keeps a blocking database from sabotaging your coroutines. But the lesson that outlasts the library is the repository boundary: because storage was always behind an interface, trading a map for a real database touched one class and zero routes. Design for that seam early and swapping infrastructure stays cheap.

Next: dependency injection — we’ve been passing repo around by hand, which is fine at this size but won’t scale once services depend on services. Time to wire it properly.