One model instead of three

Exposed earned its reputation: built by JetBrains, type-safe end to end, and the library that defined what a Kotlin SQL DSL should feel like. The difference is where knowledge lives. Exposed writes joins and mappings with every query; Storm declares them once, in the model, and that difference compounds as the schema grows.

01The task

Two tables, users and cities, one foreign key. List the users of a city with the city name, then a second query filtering on email, and return typed results from a service so a controller can serialize them. The everyday shape of a data layer.

02The Exposed way

Exposed pioneered the typed SQL DSL in Kotlin, and a decade of polish shows. The schema lives in table objects, every column reference is checked by the compiler, nothing about the SQL is hidden, and the library carries JetBrains' first-party commitment to the language. For this task it looks like this:

// The schema lives in table objects, fully type-safe ...
object Cities : Table("city") {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 50)
    override val primaryKey = PrimaryKey(id)
}

object Users : Table("user") {
    val id = integer("id").autoIncrement()
    val email = varchar("email", 255)
    val name = varchar("name", 100)
    val cityId = integer("city_id") references Cities.id
    override val primaryKey = PrimaryKey(id)
}

// ... the shape you pass around is a separate declaration ...
data class UserRow(val email: String, val name: String, val cityName: String)

// ... and each query bridges the two
val inSunnyvale = transaction {
    (Users innerJoin Cities)
        .selectAll()
        .where { Cities.name eq "Sunnyvale" }
        .map { UserRow(it[Users.email], it[Users.name], it[Cities.name]) }
}

// The next query writes the same join and the same mapping again
val atAcme = transaction {
    (Users innerJoin Cities)
        .selectAll()
        .where { Users.email like "%@acme.io" }
        .map { UserRow(it[Users.email], it[Users.name], it[Cities.name]) }
}

Nothing here is wrong, and all of it is type-safe. The observation is about where the knowledge lives: the join path and the row mapping belong to each query, so the second query restates both. Two queries mean two copies; a real schema with hundreds of queries means hundreds of copies, and adding a column to UserRow means visiting each one. The DAO layer removes the mapping but adds a third representation of the same data:

// The DAO layer trades the mapping for a third representation
class User(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<User>(Users)
    var email by Users.email
    var name by Users.name
    var city by City referencedOn Users.cityId
}

val lines = transaction {
    User.all().with(User::city)   // with() opts into eager loading per call site
        .map { "${it.name} lives in ${it.city.name}" }
}

DAO entities are comfortable inside the transaction that created them. References load lazily on first touch, with .with() as the per-call-site opt-in to eager loading, and by default a loaded reference is meant to be used within the same transaction. These are coherent choices for an active-record design; they do mean the data layer's outputs are not plain values, which is a constraint the rest of the application designs around. There is also a subtler cost: the SQL a line produces now depends on a modifier that is easy to omit, so User.all() with and without .with(User::city) look almost identical while behaving very differently, the kind of divergence the DSL was designed to avoid.

03The Storm way

Storm makes a different bet: relations are model knowledge, not query knowledge. The data class declares the join once, and every query reuses it:

// The data class is the schema mapping, the query target, and the value
data class City(
    @PK val id: Int = 0,
    val name: String,
) : Entity<Int>

data class User(
    @PK val id: Int = 0,
    val email: String,
    val name: String,
    @FK val city: City,   // the join is declared here, once
) : Entity<Int>

val inSunnyvale = orm.entity<User>().findAll(User_.city.name eq "Sunnyvale")

// The next query reuses the model; the join is never restated
val atAcme = orm.entity<User>().findAll(User_.email like "%@acme.io")

-- both queries derive the join and the column list from the model
SELECT u.id, u.email, u.name, c.id, c.name
FROM "user" u
INNER JOIN city c ON u.city_id = c.id
WHERE c.name = ?

SELECT u.id, u.email, u.name, c.id, c.name
FROM "user" u
INNER JOIN city c ON u.city_id = c.id
WHERE u.email LIKE ?

The second query never mentions the join because there is nothing to restate; User_.city.name is a compile-checked path through the one declaration that exists. Type safety is a tie between these two libraries. The difference is arithmetic: per-query joins and mappings grow with the number of queries, while a model grows only with the number of tables. On a large database, that is the whole argument.

04Results are values

The DSL's mapped rows are plain values too; it is the DAO's records that stay bound to their transaction. Storm's results are immutable data classes without the mapping step, so the data layer's output is ordinary Kotlin:

// Results are plain values: they leave the data layer as they are
fun usersIn(cityName: String): List<User> =
    orm.entity<User>().findAll(User_.city.name eq cityName)

// Serialize them, cache them, hand them to another thread; there is
// no session or transaction they depend on
val json = objectMapper.writeValueAsString(usersIn("Sunnyvale"))

Reads do not need a transaction { } wrapper; repositories manage connections per operation, and transactions come in where atomicity matters. Lazy loading exists in Storm too, but as a type: declare the field Ref<City> and loading becomes an explicit fetch() call, visible in code review. The N+1 tutorial covers that model in depth.

05Side by side

06Keep going

The reference documentation covers the mechanics in depth: