Tutorials/Exposed vs Storm: queries

Queries and full SQL

Everyday Exposed queries translate to Storm almost line by line, and the row mapping step goes away. The larger difference is in queries written as SQL text.

Series · Exposed to Storm5 min readKotlin

01The task

Three query shapes cover most of a data layer: a filter, an aggregate, and a query the team writes as SQL text, here a report that ranks cities by population.

02The everyday queries

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

OwnerService.kt Kotlin · Exposed
1
2
3
4
5
6
7
8
9
10
11
12
13
// Filtering
val davises = transaction {
    Owners.selectAll()
        .where { Owners.lastName like "Dav%" }
        .map { it[Owners.firstName] to it[Owners.lastName] }
}

// Aggregating: select the columns, group, map the rows
val perOwner = transaction {
    Pets.select(Pets.ownerId, Pets.id.count())
        .groupBy(Pets.ownerId)
        .map { it[Pets.ownerId] to it[Pets.id.count()] }
}

The Storm versions read much the same. The rows hydrate into your types, so there is no mapping step, and the join in the aggregate comes from the @FK on Pet:

OwnerService.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
11
12
13
val owners = orm.entity(Owner::class)
val pets = orm.entity(Pet::class)

// Filtering: the rows come back as Owner values
val davises = owners.findAll(Owner_.lastName like "Dav%")

// Aggregating: your select clause, a typed filter and grouping
data class OwnerCount(val owner: Owner, val count: Long)

val perOwner = pets
    .select(OwnerCount::class) { "${Owner::class}, COUNT(*)" }
    .groupBy(Pet_.owner)
    .resultList
↳ generated sql
-- the aggregate: Owner expands to its columns, the join comes from the model
SELECT o.id, o.first_name, o.last_name, COUNT(*)
FROM pet p
INNER JOIN owner o ON p.owner_id = o.id
GROUP BY o.id

groupBy(Pet_.owner) groups by the owner's key, in the form each database accepts. For text search over columns, Storm has search and suggest predicates; see Search and suggest.

03SQL as text

Exposed's DSL covers window functions too. When a query is written as SQL text, Exposed runs it with exec(), which binds the arguments you pass as column type and value pairs and hands the JDBC ResultSet to your code:

ReportService.kt Kotlin · Exposed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// exec() binds the arguments and hands you the JDBC ResultSet
val ranked = transaction {
    exec(
        """
            SELECT name, population, RANK() OVER (ORDER BY population DESC)
            FROM city
            WHERE population >= ?
        """,
        listOf(LongColumnType() to minPopulation),
    ) { resultSet ->
        buildList {
            while (resultSet.next()) {
                add(RankedCity(resultSet.getString(1), resultSet.getLong(2), resultSet.getLong(3)))
            }
        }
    }
}

A Storm SQL template maps the rows by position onto a data class, and an interpolated Kotlin value becomes a bind parameter:

ReportService.kt Kotlin · Storm
1
2
3
4
5
6
7
data class RankedCity(val name: String, val population: Long, val rank: Long)

val ranked = orm.query { """
    SELECT name, population, RANK() OVER (ORDER BY population DESC)
    FROM city
    WHERE population >= $minPopulation
""" }.resultList<RankedCity>()
↳ generated sql
-- $minPopulation is a bind parameter
SELECT name, population, RANK() OVER (ORDER BY population DESC)
FROM city
WHERE population >= ?

The Storm compiler plugin, applied by the Storm Gradle plugin, turns each interpolation into a template value. A template also takes entity types, which expand to column lists and joins, and metamodel paths, which resolve to aliased columns. The SQL templates tutorial covers that.

04The translation table

ExposedStorm
selectAll().where { col like x } plus mapfindAll(Owner_.lastName like x)
select(columns).groupBy(col) plus mapselect(OwnerCount::class) { ... }.groupBy(...), typed rows
exec(sql, args) { resultSet -> ... }query { sql }.resultList<T>()
Every statement inside transaction { }Repositories read without one; transaction { } where atomicity matters

05Keep going

The reference documentation covers the mechanics in depth: