Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 38 ยท Databases with Exposed

Exposed Basics - Tables, Transactions and CRUD

Connect a Ktor server to a database with JetBrains Exposed. Define tables in the Kotlin DSL, open transactions, and run insert, select, update and delete safely.

A server that forgets everything when it restarts is a toy. Real Ktor applications keep their data in a relational database, and the library most Ktor teams reach for is Exposed, a Kotlin SQL framework from JetBrains. Exposed lets you describe tables as Kotlin objects and write queries as Kotlin code, so the compiler checks column names and types for you instead of a runtime SQL error.

This lesson shows the real Exposed code (read-only, because the site cannot run a JDBC driver) and then gives you a small runnable stand-in written in plain Kotlin, so you can see exactly why the DSL looks the way it does. The next lessons build on it: queries, the DAO layer and relations, and pooling and migrations.

Two APIs in one library

Exposed has two styles that you can mix in one project:

  • The DSL maps almost one to one to SQL. You write Users.selectAll().where { Users.age greaterEq 18 } and get back rows.
  • The DAO maps rows to Kotlin entity classes with properties, so user.name = "Ann" becomes an UPDATE.

Most Ktor services use the DSL, often wrapped in a repository class, because it is explicit and there is no hidden lazy loading. The DAO is convenient for small admin-style code. Both share the same table objects.

Exposed 1.0 moved its packages under org.jetbrains.exposed.v1 (for example org.jetbrains.exposed.v1.core and org.jetbrains.exposed.v1.jdbc). Older tutorials import org.jetbrains.exposed.sql. Check the imports for the version you add to Gradle; the ideas below are identical.

Dependencies and the connection

In a Gradle Kotlin build you add the Exposed modules plus a JDBC driver. H2 is a good in-memory database for development and tests; PostgreSQL is typical in production.

Kotlin
dependencies {
    implementation("org.jetbrains.exposed:exposed-core:$exposedVersion")
    implementation("org.jetbrains.exposed:exposed-jdbc:$exposedVersion")
    implementation("org.jetbrains.exposed:exposed-java-time:$exposedVersion")
    implementation("com.h2database:h2:2.3.232")
}

You connect once, when the application starts. Database.connect does not open a query yet; it only remembers how to reach the database.

Kotlin
fun Application.configureDatabase() {
    Database.connect(
        url = "jdbc:h2:mem:app;DB_CLOSE_DELAY=-1",
        driver = "org.h2.Driver",
    )
    transaction {
        SchemaUtils.create(Users)
    }
}

SchemaUtils.create issues CREATE TABLE IF NOT EXISTS. It is fine for demos; for real projects use migrations.

Describing a table

A table is a Kotlin object that extends Table (or IntIdTable, which adds an auto-increment id). Each column is a property:

Kotlin
object Users : Table("users") {
    val id = integer("id").autoIncrement()
    val name = varchar("name", length = 80)
    val email = varchar("email", length = 120).uniqueIndex()
    val age = integer("age").nullable()
    override val primaryKey = PrimaryKey(id)
}

The property type carries the column type: Users.name is a Column<String>, Users.age a Column<Int?>. If you try to compare Users.name with a number, the code does not compile. That is the main reason to prefer a DSL over string SQL.

CRUD in the DSL

All database work happens inside a transaction. The block takes a connection from the pool, runs your statements, and commits when the block ends normally. If an exception escapes, Exposed rolls everything back.

Kotlin
transaction {
    // Create
    val newId = Users.insert {
        it[name] = "Ann"
        it[email] = "[email protected]"
        it[age] = 31
    } get Users.id

    // Read
    val adults = Users.selectAll()
        .where { Users.age greaterEq 18 }
        .map { row -> row[Users.name] }

    // Update
    Users.update({ Users.id eq newId }) {
        it[age] = 32
    }

    // Delete
    Users.deleteWhere { Users.id eq newId }
}

Notice it[name] = "Ann". Inside the insert lambda the receiver is the table, so name means Users.name, and it is the statement builder. This is a Kotlin lambda with receiver, and you can build the same idea yourself, as the next example shows.

A runnable stand-in: how the DSL works

The real library talks to JDBC, which is not available in this page. The code below is a tiny in-memory imitation. It is not Exposed, but it uses the same Kotlin features: objects as tables, typed columns, and lambdas with receivers.

Kotlin Stand-in: typed tables with an insert DSL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with readln()), it gets the next line from this box.

Uncomment the it[name] = 42 line and run again: the compiler rejects it because set is generic in T. Exposed gives you exactly that safety, at a much larger scale.

Transactions: all or nothing

A transaction groups statements so they succeed or fail together. Moving money between two accounts must never subtract without adding. The stand-in below snapshots the data, runs your block, and restores the snapshot if the block throws, which is the behavior you get from a real transaction { }.

Kotlin Stand-in: commit and rollback

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with readln()), it gets the next line from this box.

The failed transfer had already subtracted 500 from Bo inside the block, but the final balances are the same as before the call. Real databases do this with a write-ahead log instead of a map copy, but the contract you rely on is the same.

Blocking JDBC inside a coroutine server

Ktor handlers are coroutines, and JDBC is blocking. If you call transaction { } directly in a handler, the thread is stuck until the database answers. Two good options exist:

  • Wrap calls in withContext(Dispatchers.IO) { transaction { ... } }.
  • Use Exposed's suspendTransaction (in the R2DBC or coroutine-aware modules) so the thread is released.

Coroutines in Ktor explains why this matters when many requests arrive at once.

Common mistakes

  • Using the table outside a transaction. Every statement needs one; otherwise you get "No transaction in context".
  • Forgetting SchemaUtils.create in development and then wondering why the table is missing.
  • Returning rows from the transaction. ResultRow is bound to the transaction. Map rows to plain data classes inside the block and return those.
  • Opening a transaction per query in a loop. Open one around the whole unit of work.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Database.connect(...) do?

    It only configures the connection. Statements run inside transaction blocks, which borrow a connection when needed.
  2. 2. Why does it[name] = 42 fail to compile when name is varchar(...)?

    Columns are generic, so the value type must match the column type.
  3. 3. What happens when an exception escapes a transaction { } block?

    A transaction is all or nothing; a failure rolls back every statement in the block.
  4. 4. Which is the safe thing to return from a transaction block?

    Rows belong to the transaction. Copy the values you need into your own types before the block ends.

Summary

  • Exposed describes tables as Kotlin objects and queries as typed Kotlin code; it has a DSL and a DAO layer.
  • Connect once with Database.connect, and run all statements inside transaction { }.
  • Transactions commit on success and roll back on an exception.
  • JDBC blocks threads, so move database work off the request threads in a Ktor handler.
  • The stand-ins here are plain Kotlin imitations; the real calls are shown read-only.

Next: Querying with the Exposed DSL