Tutorials/Exposed vs Storm: queries

Queries and full SQL

The everyday queries translate almost line by line, and Exposed's are perfectly pleasant to write. The gap opens past the DSL, where a window function or a vendor feature is involved.

Series · Exposed to Storm5 min readKotlin

01The task

Three query shapes cover most of a data layer: a filter, an aggregate, and the occasional query that outgrows any DSL, here a window function ranking cities by population.

02The everyday queries

Exposed's DSL handles filters and aggregates with compile-checked columns:

UserService.kt Kotlin · Exposed

// Filtering: type-safe and readable
val atAcme = transaction {
    Users.selectAll()
        .where { Users.email like "%@acme.io" }
        .map { it[Users.email] to it[Users.name] }
}

// Aggregating: select specific columns, group, map the rows
val perCity = transaction {
    Users.select(Users.cityId, Users.id.count())
        .groupBy(Users.cityId)
        .map { it[Users.cityId] to it[Users.id.count()] }
}

Storm's versions will read familiarly; the mapping step disappears because results hydrate into your types directly, and the join in the aggregate comes from the model rather than the query:

UserService.kt Kotlin · Storm Show SQL

// Filtering: the same idea, one line, no mapping step
val atAcme = orm.findAll(User_.email like "%@acme.io")

// Aggregating: supply the select clause, keep the typed rest
data class CityCount(val city: City, val count: Long)

val perCity = orm.entity<User>()
    .select<CityCount, _, _> { "${City::class}, COUNT(*)" }
    .groupBy(User_.city)
    .resultList

↳ generated sql

-- the aggregate: City expands to its columns, the join comes from the model
SELECT c.id, c.name, c.population, c.country, COUNT(*)
FROM "user" u
INNER JOIN city c ON u.city_id = c.id
GROUP BY u.city_id

03Past the DSL

Every DSL has an edge. Exposed's escape hatch is exec(), which runs the SQL and hands you the raw JDBC ResultSet to read yourself:

ReportService.kt Kotlin · Exposed

// Past the DSL, exec() hands you the raw JDBC ResultSet
val ranked = transaction {
    exec("""
        SELECT name, population, RANK() OVER (ORDER BY population DESC) AS rank
        FROM city
    """) { resultSet ->
        buildList {
            while (resultSet.next()) {
                add(RankedCity(resultSet.getString(1), resultSet.getInt(2), resultSet.getLong(3)))
            }
        }
    }
}

Storm was built SQL-first, so dropping to full SQL keeps everything the DSL had: rows map positionally onto any data class, and interpolated Kotlin values compile to bind parameters instead of concatenation:

ReportService.kt Kotlin · Storm Show SQL

// Full SQL stays typed: rows map by position, values bind safely
data class RankedCity(val name: String, val population: Int, val rank: Long)

val ranked = orm.query { """
    SELECT name, population, RANK() OVER (ORDER BY population DESC)
    FROM city
    WHERE country = $country
""" }.resultList<RankedCity>()

↳ generated sql

-- $country compiles to a bind parameter
SELECT name, population, RANK() OVER (ORDER BY population DESC)
FROM city
WHERE country = ?

The template engine goes further than pass-through SQL: type references expand to column lists and joins, and metamodel paths are compile-checked inside the SQL text. The SQL templates tutorial covers that in depth.

04The translation table

Exposed	Storm
`selectAll().where { col like x }` plus `map`	`findAll(User_.email like x)`
`select(columns).groupBy(col)` plus `map`	`select<CityCount, _, _> { ... }.groupBy(...)`, typed rows
`exec(sql) { resultSet -> ... }`	`query { sql }.resultList<T>()`
Every query inside `transaction { }`	Repositories manage connections for reads

05Keep going

The reference documentation covers the mechanics in depth:

Queries SQL Templates Hydration Framework Comparison

Get started → Star on GitHub