Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 38 ยท Databases with Exposed

Exposed DAO and Relations - Entities, One-to-Many, N+1

Use the Exposed DAO API in a Ktor service - entity classes, property delegates, one-to-many and many-to-many relations, eager loading and the N+1 problem - with runnable Kotlin models.

The DSL works with rows and columns. The DAO (data access object) API works with objects: a User class with a name property, a Post that knows its author. If you have used Hibernate or Active Record in another language, the DAO will feel familiar, with a Kotlin twist: properties are delegates, and changes are written to the database when the transaction commits.

This lesson shows the real DAO code read-only, then rebuilds the two ideas that surprise people most, property delegates with change tracking and the N+1 query problem, in runnable Kotlin.

Tables and entities

A DAO table extends IntIdTable (or UUIDTable, LongIdTable), which adds an id column for you. An entity class pairs with it:

Kotlin
object Authors : IntIdTable("authors") {
    val name = varchar("name", 80)
}

object Books : IntIdTable("books") {
    val title = varchar("title", 120)
    val author = reference("author_id", Authors)
}

class Author(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Author>(Authors)

    var name by Authors.name
    val books by Book referrersOn Books.author   // one-to-many
}

class Book(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Book>(Books)

    var title by Books.title
    var author by Author referencedOn Books.author   // many-to-one
}

var name by Authors.name is a property delegate. Reading it loads the value; assigning it marks the entity as changed. The companion object is the "class side": it creates and finds entities.

Using entities

Kotlin
transaction {
    val ann = Author.new { name = "Ann Lee" }
    Book.new {
        title = "Kotlin by Example"
        author = ann
    }

    val found = Author.findById(ann.id)          // one by id
    val lees = Author.find { Authors.name like "%Lee" }   // a query
    found?.name = "Ann Q. Lee"                   // UPDATE at commit
    found?.delete()                              // DELETE
}

new { } inserts a row, assignments inside a transaction are flushed before the next query and at commit, and find returns a lazy collection that you iterate or call .toList() on.

Entities, like ResultRow, only work inside the transaction that loaded them. Touching author.name after the transaction block ended throws. Convert entities to plain data classes (a DTO) before returning from the repository: fun Author.toDto() = AuthorDto(id.value, name).

How the delegate tracks changes

The magic of var name by Authors.name is not magic. A delegate is an object with getValue and setValue. Setting a value records it in a "dirty" map; a flush at commit writes only the changed columns. This runnable model shows the mechanism end to end.

Kotlin Stand-in: property delegates with dirty tracking

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.

Notice that assigning the same value writes nothing and only changed columns appear in the update. Exposed's real implementation also batches the writes and orders them by foreign keys, but the idea is the same.

Relations

One-to-many (an author has many books) uses referrersOn on the "one" side and referencedOn on the "many" side, as in the code above. One-to-one uses backReferencedOn/optionalReferencedOn. Many-to-many needs a join table with two references and a composite primary key:

Kotlin
object BookTags : Table("book_tags") {
    val book = reference("book_id", Books)
    val tag = reference("tag_id", Tags)
    override val primaryKey = PrimaryKey(book, tag)
}

class Book(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Book>(Books)
    var title by Books.title
    var tags by Tag via BookTags
}

With via, book.tags = SizedCollection(listOf(kotlin, web)) replaces the links and Exposed edits the join table.

The N+1 problem

Relations are lazy. author.books runs a query the first time you read it. Listing 50 authors and printing each one's books means one query for the authors plus 50 queries for the books, 51 in total. That is the N+1 problem, and it is the single most common performance bug in ORM-based code.

The DAO offers eager loading with with:

Kotlin
val authors = Author.all().with(Author::books)   // 2 queries, not 51
authors.forEach { println("${it.name}: ${it.books.count()}") }

With the DSL you solve it with a join or an inList query. The model below counts queries so you can see the difference.

Kotlin Stand-in: lazy loading vs eager loading

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.

Both versions return the same data, but the lazy one made six queries and the eager one made two. With 5000 authors the difference is the gap between a snappy endpoint and a timeout.

DSL or DAO in a Ktor service?

DSL DAO
Feels like SQL in Kotlin Objects and properties
Hidden queries None Lazy relations can hide them
Best for Lists, reports, joins, bulk work Simple CRUD on a few entities
Result type ResultRow Entity (transaction-bound)

Many teams use the DSL for reads and bulk work and the DAO for small create and update flows. Either way, return DTOs from the repository and keep entities out of your routes and your JSON responses.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the var name by Authors.name delegate do on assignment?

    The delegate marks the field dirty and the write happens at flush or commit.
  2. 2. What is the N+1 problem?

    Lazy relations run a query per row, so a list of N items costs N+1 queries.
  3. 3. Why should entities not be returned from a repository?

    Entities load lazily through the active transaction, so use them only inside it and return DTOs.
  4. 4. How do you model a many-to-many relation?

    The join table links both sides; via maps it to a collection.

Summary

  • The DAO maps rows to entity classes whose properties are delegates over table columns.
  • Changes are tracked and written at flush or commit; entities live only inside their transaction.
  • referrersOn/referencedOn model one-to-many; via with a join table models many-to-many.
  • Lazy relations cause N+1 queries; load eagerly with with or a join.
  • Return DTOs from repositories.

Next: Connection Pooling and Migrations