Full SQL without giving up safety

Every ORM eventually meets a query it cannot express. JPA answers with native queries and untyped rows. Storm treats full SQL as a first-class citizen with the same safety as the rest of the framework.

01The task

A report needs a window function: rank cities by population within a country. No entity abstraction is going to express RANK() OVER, and it should not have to. The question is what dropping to SQL costs you.

02The JPA way

JPA's escape hatch is the native query, and Spring Data can map its rows onto an interface projection, so the untyped Object[] days are mostly behind us:

// Spring Data maps native rows onto an interface projection by alias
interface RankedCity {
    val name: String
    val population: Int
    val rank: Long
}

@Query(
    value = """
        SELECT name, population,
               RANK() OVER (ORDER BY population DESC) AS rank
        FROM city
        WHERE country = :country
    """,
    nativeQuery = true,
)
fun rankedCities(country: String): List<RankedCity>

This works, with a contract attached. Every property must line up with a column alias, and that contract is verified at runtime, per query. What comes back is a proxy behind an interface, not a real class. If you want an actual class, there is no direct mapping for native queries: constructor expressions are JPQL-only, so you are into @NamedNativeQuery plus @SqlResultSetMapping territory. And the moment the query needs a dynamic clause, string concatenation appears, and with it the possibility of injection.

03The Storm way

Storm was built SQL-first, so full SQL is not an escape hatch; it is the same query API. Interpolated Kotlin values compile to bind parameters, and rows map positionally onto any data class:

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   -- a Kotlin value, bound safely
""" }.resultList<RankedCity>()

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

There is no string concatenation to get wrong: $country is a Kotlin expression, and the template turns it into a ? placeholder with a bound value. The result type is an ordinary data class defined next to the query that fills it.

04Templates that know your model

The same engine understands your entities. Reference a type and it expands to its column list and its FROM clause with the joins your @FK fields imply; reference a metamodel path and it resolves to the right column on the right alias, checked by the compiler:

// The template engine knows your model: types and columns expand
val users = orm.query { """
    SELECT ${User::class}
    FROM ${User::class}
    WHERE ${User_.city.name} = $cityName
""" }.resultList<User>()

-- User expands to its columns, FROM gains the @FK auto-joins,
-- and the metamodel path resolves to the right alias
SELECT u.id, u.email, u.name, c.id, c.name, c.population, c.country
FROM "user" u
INNER JOIN city c ON u.city_id = c.id
WHERE c.name = ?

This is the middle ground JPA never had: hand-written SQL for the parts that need it, generated columns and joins for the parts that do not, in one statement.

05One-off result shapes

Reports rarely deserve an entity. Mark a result type with Data and it gains template expansion and auto-joins without any repository machinery:

// A single-use result shape: Data enables templates, no repository needed
@DbTable("pet")
data class PetWithOwner(
    val name: String,
    val birthDate: LocalDate?,
    @FK val owner: Owner,
) : Data

val pets = orm.query { """
    SELECT ${PetWithOwner::class}
    FROM ${PetWithOwner::class}
    WHERE ${Owner_.city} = $city
""" }.resultList<PetWithOwner>()

This completes a small ladder of result shapes. A plain data class like RankedCity from section 03 needs no marker interface at all: you write the whole SELECT, rows map by position, case by case (see Typed query results). Marking a class Data adds template expansion and auto-joins for the type, still with no repository. And when a shape deserves reuse across the codebase, with its own repository, nesting, and predicates, it has become a read model: make it a Projection, the subject of the Projections tutorial.

06Side by side

07Keep going

The reference documentation covers the mechanics in depth: