Eager loading and N+1

Exposed's answer to N+1 is a good one: batch the references with one call. The difference is where that decision lives. In Exposed it is made per call site; in Storm it is made once, in the model, and every query inherits it.

01The task

Render 50 users with the name of the city each lives in. Two tables, one foreign key. The question every persistence library answers differently: when does the city load?

02The Exposed way

Exposed's DAO layer models the relation with a delegated property, and its laziness is deliberate: nothing loads until touched:

object Users : IntIdTable("user") {
    val name = varchar("name", 100)
    val city = reference("city_id", Cities)
}

class User(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<User>(Users)
    var name by Users.name
    var city by City referencedOn Users.city   // loads lazily, on first touch
}

Touched in a loop, each reference loads with its own query. The remedy is .with(), and credit where due: it batches all the references into a single IN query, which is efficient:

// Lazy by default: each city loads when first touched
val lines = transaction {
    User.all().map { "${it.name} lives in ${it.city.name}" }   // 1 + N
}

// The fix: opt into eager loading, one call site at a time
val fixed = transaction {
    User.all().with(User::city)
        .map { "${it.name} lives in ${it.city.name}" }
}

-- without with(): one query per city on first touch
SELECT ... FROM "user"
SELECT ... FROM city WHERE city.id = ?
SELECT ... FROM city WHERE city.id = ?
-- ...

-- with(User::city): the references batch into a second query
SELECT ... FROM "user"
SELECT ... FROM city WHERE city.id IN (?, ?, ?)

The catch is not the mechanism; it is the location of the decision. Eager loading is opted into per call site, so every new query that touches the relation is a fresh chance to forget it, and the query that forgets looks identical to the one that does not. Abstraction itself is not the issue; Storm's one-liners do not look like SQL either. The difference is that here two very different SQL behaviors, the batched path and the 1 + N path, hide behind nearly identical code, separated only by a call that is easy to omit. Loaded references are also meant to stay within the transaction that loaded them, so the pattern shapes how results move through the application.

03The Storm way

Storm moves the decision into the model. A relation declared with its plain type is always loaded, joined in the same query, for every caller. The code is as concise as an abstraction can be, and it is deterministic: the same call produces the same SQL, every time, for everyone:

// The loading policy is declared once, in the model
data class User(
    @PK val id: Int = 0,
    val name: String,
    @FK val city: City,   // plain field: joined in the same query, always
) : Entity<Int>

val lines = orm.entity<User>().findAll()
    .map { "${it.name} lives in ${it.city.name}" }   // no database access in the loop

-- one query, for every caller, with nothing to remember
SELECT u.id, u.name, c.id, c.name
FROM "user" u
INNER JOIN city c ON u.city_id = c.id

There is no eager-loading call to remember because there is no lazy default to escape. And when deferring the load is genuinely the right call, that decision is also made in the model, as a type:

// When laziness is what you want, it is a type, not a default
data class User(
    @PK val id: Int = 0,
    val name: String,
    @FK val city: Ref<City>,   // reads city_id, joins nothing
) : Entity<Int>

val user = orm.entity<User>().getById(1)
val city = user.city.fetch()   // loading is a visible, deliberate call

Refs that point to the same id share one instance inside a transaction, so fetching across a result list loads each distinct city once. The JPA edition of this tutorial covers the mechanics in more depth, and query counts are one-line assertions with SqlCapture, so "no N+1" can be a test rather than a review comment.

04Side by side

05Keep going

The reference documentation covers the mechanics in depth: