Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 38 ยท Databases with Exposed

Querying with the Exposed DSL - Where, Joins, Aggregates

Write real queries with the Exposed DSL in a Ktor app - conditions, ordering, paging, joins, aggregates and batch inserts - and practise the same ideas in runnable Kotlin.

After tables and transactions the next skill is asking the database useful questions. Almost every API endpoint is one of four shapes: fetch one row by id, list rows with a filter, list with paging and sorting, or compute a summary (a count or a total). The Exposed DSL covers all four with code that reads like the SQL you would write by hand.

This lesson uses a small shop schema (customers and orders). The real Exposed code is read-only; the runnable examples reproduce the logic over plain Kotlin collections so you can experiment with filters, sorting, paging, joins and grouping.

The schema we use

Kotlin
object Customers : Table("customers") {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 80)
    val country = varchar("country", 2)
    override val primaryKey = PrimaryKey(id)
}

object Orders : Table("orders") {
    val id = integer("id").autoIncrement()
    val customerId = integer("customer_id").references(Customers.id)
    val totalCents = integer("total_cents")
    val status = varchar("status", 20)
    override val primaryKey = PrimaryKey(id)
}

Money is stored as integer cents. Floating-point numbers are a classic source of rounding bugs in totals, so integers (or decimal) are the right type.

Filtering with where

selectAll() starts a query and .where { } adds a condition. Conditions are built from infix functions on columns: eq, neq, less, lessEq, greater, greaterEq, like, inList, isNull, isNotNull, and between. Combine them with and, or and not:

Kotlin
val bigOpenOrders = Orders.selectAll().where {
    (Orders.status eq "open") and (Orders.totalCents greaterEq 10_000)
}.map { it[Orders.id] }

val someNames = Customers.selectAll().where {
    Customers.name like "A%"
}.map { it[Customers.name] }

Everything is parameterized: the value in eq "open" is sent to the database as a bound parameter, not pasted into the SQL text. That makes the DSL safe against SQL injection as long as you stay inside it. If you ever use raw SQL strings, bind parameters instead of interpolating user input.

Sorting, paging, counting

Kotlin
val page = Orders.selectAll()
    .where { Orders.status eq "paid" }
    .orderBy(Orders.totalCents to SortOrder.DESC)
    .limit(20)
    .offset(40)
    .map { it[Orders.id] to it[Orders.totalCents] }

val howMany = Orders.selectAll().where { Orders.status eq "paid" }.count()

limit and offset implement page-based listing, as in /orders?page=3&size=20. Offsets get slower on very large tables because the database still walks past the skipped rows. For feeds, keyset paging (where id < lastSeenId order by id desc limit 20) stays fast; the first runnable example below does both.

Kotlin Stand-in: filter, sort and page a list of rows

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 numbers are fully deterministic. Compare page with limit/offset above: drop((n - 1) * size).take(size) is exactly what the database computes.

Joins

A join combines rows from two tables on a matching column. Exposed's innerJoin and leftJoin use the declared foreign key automatically, or you can pass an explicit onColumn and otherColumn.

Kotlin
val rows = (Orders innerJoin Customers)
    .select(Customers.name, Orders.totalCents)
    .where { Orders.status eq "paid" }
    .map { it[Customers.name] to it[Orders.totalCents] }

An inner join returns only orders that have a matching customer. A left join returns every row from the left table and fills the right-side columns with null when there is no match, which is how you list customers including those with no orders.

Aggregates and grouping

Aggregate functions are extension functions on columns: sum(), count(), avg(), min(), max(). You group with groupBy and pick the columns, including aggregate expressions, in select:

Kotlin
val total = Orders.totalCents.sum()
val perCustomer = Orders
    .select(Orders.customerId, total)
    .where { Orders.status eq "paid" }
    .groupBy(Orders.customerId)
    .having { total greater 10_000 }
    .map { it[Orders.customerId] to it[total] }

Keep the aggregate expression in a variable (total) and reuse it so Exposed refers to the same expression in select and having. sum() is nullable because the sum of zero rows is NULL in SQL, so reading it gives Int?.

Kotlin Stand-in: inner join, left join and group by

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.

Writing many rows and updating in bulk

batchInsert sends many rows in one round trip and is far faster than inserting in a loop:

Kotlin
Orders.batchInsert(newOrders) { o ->
    this[Orders.customerId] = o.customerId
    this[Orders.totalCents] = o.totalCents
    this[Orders.status] = "open"
}

Orders.update({ Orders.status eq "open" and (Orders.totalCents less 100) }) {
    it[status] = "cancelled"
}

update and deleteWhere return the number of affected rows. Return that count from your route (404 when zero rows changed) so clients can tell "updated" from "nothing matched".

Mapping rows to your own types

Keep ResultRow inside the repository and expose data classes, so the rest of the app does not depend on Exposed:

Kotlin
fun ResultRow.toOrder() = OrderDto(
    id = this[Orders.id],
    customerId = this[Orders.customerId],
    totalCents = this[Orders.totalCents],
    status = this[Orders.status],
)

fun listPaid(): List<OrderDto> = transaction {
    Orders.selectAll().where { Orders.status eq "paid" }.map { it.toOrder() }
}

A tidy structure is a route calling a repository function, which calls transaction. Routes know HTTP, repositories know SQL, and tests can replace either side. See testing with fakes and databases.

Common mistakes

  • Building SQL by string concatenation with user input. Use the DSL or bound parameters.
  • N+1 queries: loading orders, then querying the customer for each order inside a loop. Use a join or one inList query.
  • Reading a nullable aggregate as non-null. sum() on no rows gives null; use ?: 0.
  • Paging without orderBy. Without a stable order, pages can overlap or skip rows.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which query shape keeps paging fast on a huge table?

    Keyset paging lets the database jump straight to the right place instead of walking past skipped rows.
  2. 2. What does a left join add compared with an inner join?

    A left join keeps every left row; missing right-side columns are null.
  3. 3. Why is Orders.totalCents.sum() nullable?

    SQL returns NULL when there are no rows to sum, so you should handle that case with a default.
  4. 4. How does the DSL protect against SQL injection?

    Parameter binding keeps data separate from the statement text.

Summary

  • selectAll().where { } filters; eq, like, inList, between build conditions; and/or combine them.
  • Sort with orderBy, page with limit/offset, or use keyset paging for large tables.
  • Inner joins keep matches only; left joins keep every left row.
  • Aggregates need groupBy and often having; sum() is nullable.
  • Map ResultRow to data classes inside the repository and avoid N+1 queries.

Next: The DAO Layer and Relations