Exposed batches lazy references with one call, .with(), made at each call site. Storm decides loading once, in the model, and every query follows it.
Render 50 pets with the last name of each pet's owner. Two tables, one foreign key. The question is when the owner loads.
Exposed's DAO layer models the relation with a delegated property. The reference is lazy by design and loads when first read:
object Pets : IntIdTable("pet") { val name = varchar("name", 30) val owner = reference("owner_id", Owners) } class Pet(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<Pet>(Pets) var name by Pets.name var owner by Owner referencedOn Pets.owner // loads lazily, on first read }
Read in a loop, each reference loads with its own query. The remedy is .with(), which loads all the references in one IN query:
// Lazy by default: each owner loads when first read val lines = transaction { Pet.all().map { "${it.name} belongs to ${it.owner.lastName}" } // 1 + N } // The fix: eager loading, added at this call site val fixed = transaction { Pet.all().with(Pet::owner) .map { "${it.name} belongs to ${it.owner.lastName}" } }
-- without with(): one query per owner on first read SELECT ... FROM pet SELECT ... FROM owner WHERE owner.id = ? SELECT ... FROM owner WHERE owner.id = ? -- ... -- with(Pet::owner): the references batch into a second query SELECT ... FROM pet SELECT ... FROM owner WHERE owner.id IN (?, ?, ?)
The mechanism is efficient. The decision sits at the call site: each query that reads the relation has to add .with(), and a query without it looks almost the same as one with it, while one runs two statements and the other 1 + N. References loaded this way are cached in the transaction that loaded them, so results that leave the transaction need their data read first.
Storm decides in the model. A relation declared with the entity type is joined in the same query, for every caller, and the same call produces the same SQL each time:
// The loading policy is declared once, in the model data class Pet( @PK val id: Int = 0, val name: String, @FK val owner: Owner, // entity field: joined in the same query ) : Entity<Int> val lines = orm.entity(Pet::class).findAll() .map { "${it.name} belongs to ${it.owner.lastName}" } // no database access in the loop
-- one query, for every caller SELECT p.id, p.name, o.id, o.first_name, o.last_name FROM pet p INNER JOIN owner o ON p.owner_id = o.id
There is no eager-loading call to add, because the field is not lazy. Where deferring the load is the right choice, that is also a field type:
// Laziness, where you want it, is a field type data class Pet( @PK val id: Int = 0, val name: String, @FK val owner: Ref<Owner>, // reads owner_id, joins nothing ) : Entity<Int> val pet = orm.entity(Pet::class).getById(1) val owner = pet.owner.fetch() // loads the owner here, one query
A loop that fetches a Ref per row is an N+1 you can see in the code. A query that needs the owners resolves them in the same statement with select().fetch(Pet_.owner), and in development the advice log reports a loop of single fetches (Statement advice). The JPA edition of this tutorial covers both in more depth, and query counts are one-line assertions with SqlCapture.
| Exposed DAO | Storm | |
|---|---|---|
| Default | Lazy; each reference loads on first read | Joined in the same query |
| Eager loading | .with() per call site, one batched IN query | The field type: an entity field is always joined |
| Choosing laziness | The default | A type: Ref<T>, loaded by fetch() or by the query's fetch(...) |
| Result scope | Loaded references are cached in their transaction | Plain values, usable anywhere |
The reference documentation covers the mechanics in depth: