Skip to main content
Version: Next

Queries

All Storm queries are type-safe: the generated metamodel (User_, City_, etc.) catches errors at compile time rather than at runtime.

Key features:

  • Compile-time checked -- field references are validated by the metamodel
  • No string-based queries -- no risk of typos in column names
  • Single-query loading -- related entities load in JOINs, not N+1 queries
  • Two styles -- quick methods for simple cases, fluent builder for complex queries

Choosing a Query Approach​

Storm offers three ways to query data, each suited to different complexity levels:

ApproachBest forType safetyFlexibility
Repository findBy / findAllByLookups by primary key or any single fieldFull compile-timeLow (single-field equality or IN)
Query DSLFiltering, ordering, pagination with type-safe conditionsFull compile-timeMedium (AND/OR predicates, joins, ordering)
SQL TemplatesComplex joins, subqueries, CTEs, window functions, database-specific SQLColumn references checked at compile time, SQL structure at runtimeHigh (full SQL control)

Start with the simplest approach that meets your needs. Use findById, findBy, or findAllBy for straightforward lookups. Move to the query builder when you need compound filters or pagination. Use SQL templates when you need SQL features the DSL does not cover.

Quick Queries​

Storm for Kotlin offers two complementary query styles; use whichever fits best.

For simple queries, use methods directly on the ORM template:

// Find single entity with predicate
val user: User? = orm.find(User_.email eq email)

// Find all matching
val users: List<User> = orm.findAll(User_.city eq city)

// Find by field value
val user: User? = orm.findBy(User_.email, email)

// Check existence
val exists: Boolean = orm.existsBy(User_.email, email)

Repository Queries​

For more complex operations, use the repository:

val users = orm.entity(User::class)

// Find by ID
val user: User? = users.findById(userId)

// Find with predicate
val user: User? = users.find(User_.email eq email)

// Find all matching
val usersInCity: List<User> = users.findAll(User_.city eq city)

// Count
val count: Long = users.count()

// Exists
val exists: Boolean = users.existsById(userId)

Filtering with Predicates​

Predicate paths traverse the entity graph (User_.city.country.name), and they may also navigate through a Ref foreign key. Storm adds the join for the referenced table on demand, only for a query that references a column beyond the foreign key. This holds for every clause that names a column: where, orderBy, groupBy, having, and custom selected columns. See Querying Through Refs.

Combine conditions with and and or:

// AND condition
val users = orm.findAll(
    (User_.city eq city) and (User_.birthDate less LocalDate.of(2000, 1, 1))
)

// OR condition
val users = orm.findAll(
    (User_.role eq adminRole) or (User_.role eq superUserRole)
)

// Complex conditions
val users = orm.entity(User::class)
    .select()
    .where(
        (User_.city eq city) and (
            (User_.role eq adminRole) or (User_.birthDate greaterEq LocalDate.of(1990, 1, 1))
        )
    )
    .resultList

Operators​

OperatorDescription
eqEquals
neqNot equals
lessLess than
lessEqLess than or equals
greaterGreater than
greaterEqGreater than or equals
likeLIKE pattern match
notLikeNOT LIKE
contains / notContainsContains the text, matched literally
startsWith / notStartsWithStarts with the text, matched literally
endsWith / notEndsWithEnds with the text, matched literally
eqIgnoreCase / neqIgnoreCaseEquals the text, ignoring case
containsIgnoreCase / notContainsIgnoreCaseContains the text, matched literally and ignoring case
startsWithIgnoreCase / notStartsWithIgnoreCaseStarts with the text, matched literally and ignoring case
endsWithIgnoreCase / notEndsWithIgnoreCaseEnds with the text, matched literally and ignoring case
isNullIS NULL
isNotNullIS NOT NULL
inListIN (list)
notInListNOT IN (list)

contains, startsWith and endsWith take text rather than a pattern. Storm escapes the characters the database reads as wildcards and adds the wildcards itself, so text a user typed, such as 50% or a_b, matches exactly that text on every database. Use like when you write the pattern yourself.

eq, contains, startsWith and endsWith compare text as the column's collation does, which ignores case on some databases and not on others. Their IgnoreCase forms ignore case on every database: the database lowers the column and the value alike, LOWER(name) LIKE LOWER(?) ESCAPE '!', so one rule decides what a letter's lower case is. They ignore case, and accents follow the column's collation: MySQL and MariaDB ignore them under their default collations, the other databases compare them as they are, and SQLite lowers ASCII letters only unless it is built with ICU. A plain index does not serve a lowered column, so index the lowered expression where the comparison must be served by one, and keep the plain operators on a column whose collation ignores case already.

val users = orm.findAll(User_.email like "%@example.com")
val users = orm.findAll(User_.email contains searchText)
val users = orm.findAll(User_.name containsIgnoreCase searchText)
val user = orm.find(User_.email eqIgnoreCase email)
val users = orm.findAll(User_.deletedAt.isNull())
val users = orm.findAll(User_.role inList listOf(adminRole, userRole))

Comparing Two Columns​

A predicate compares a column with a value. It compares a column with another column of the query the same way: the other side is a path instead of a value, and both resolve as any path does, to a table of the query or to a path joined on demand. A path on a table outside the query is refused when the query is built. A foreign key compares with a foreign key to the same table, by its key columns, so a compound key compares column by column. The visits where the treating vet is the pet's regular vet:

val visits = orm.entity(Visit::class)
    .select()
    .where(Visit_.vet eq Visit_.pet.regularVet)
    .resultList

neq, less, lessEq, greater and greaterEq take a path the same way.

SELECT ...
FROM visit v
INNER JOIN pet p ON v.pet_id = p.id
WHERE v.vet_id = p.regular_vet_id

The key of a table reached through a foreign key is the foreign key column itself: Pet_.owner.id is p.owner_id, wherever the pet is in the query, so comparing it joins nothing.

The same comparison goes in having and in a join condition, on a semi-join or anti-join as well, where the condition is placed in the join's subquery and a column of the query's own table names the row the subquery is correlated on. A predicate also states the relation of a join as a whole, in place of the table to join on: an invoice per pet and vet, read next to the visits, is the one sharing both keys with the visit. See Join Conditions.

val visits = orm.entity(Visit::class)
    .select()
    .leftJoin<Invoice>().on((Invoice_.pet eq Visit_.pet) and (Invoice_.vet eq Visit_.vet))
    .resultList
SELECT ...
FROM visit v
LEFT JOIN invoice i ON i.pet_id = v.pet_id AND (i.vet_id = v.vet_id)

Comparing the Row Itself​

A foreign key path compares by key and takes the entity: Pet_.owner inList owners needs no ids. The root metamodel names the row itself and compares it the same way, by its primary key. In Kotlin the generated Pet_ is the root metamodel; in Java it is Metamodel.root(Pet.class). The pets of the given owners, leaving out the pets the caller already shows:

val pets = orm.entity(Pet::class)
    .select()
    .where((Pet_.owner inList owners) and (Pet_ notInList shown))
    .resultList
SELECT ...
FROM pet p
WHERE p.owner_id IN (?, ?) AND p.id NOT IN (?, ?)

eq, neq, inList and notInList take entities, inRefs and notInRefs take refs; in Java, EQUALS, NOT_EQUALS, IN and NOT_IN take entities, and where(path, ref) and whereRef(path, refs) take refs. The row compares with a path to the same table as a foreign key does: Pet_ neq outer(Pet_) in a subquery names a pet other than the enclosing one, and Visit_.pet eq Pet_ in a join predicate is a key match. orderBy(Pet_) and groupBy(Pet_) order and group by the primary key. A compound primary key compares column by column. where(records) matches the same rows as Pet_ inList records. A table without a primary key has nothing to compare the row by, and a comparison on its root is refused with an error naming the table.

IN Lists​

An IN list, from inList and IN, findAllById or whereId, binds as one array parameter on PostgreSQL, id = ANY(?): one statement for every length of the list, so the database parses, plans and caches one statement for a lookup that takes a varying number of keys rather than one per length, and a long list binds one parameter rather than one per value, which is also faster. Measured on a lookup of 1 to 200 ids in turn, the array form ran 13% faster than a placeholder per value. Elsewhere the list renders a placeholder per value. A list compared through a SQL function renders placeholders on every database, since the function is applied to each value.

storm.in_list selects the shape, as a system property or in the StormConfig a template is built with: auto, the default; padded, a placeholder per value on every dialect, padded to the next power of two by repeating the last value, so lengths 1 to 1,000 render as 11 statements instead of 1,000, for a database whose plan cache fills with the lengths, such as Oracle's shared pool or SQL Server's plan cache; exact, one placeholder per value everywhere, for a query whose plan turns out worse under the array. Padding changes no result, since membership of a set is not changed by a repeated member, for IN, NOT IN and a list holding NULL alike. It binds the repeated values, which cost more than they saved on PostgreSQL, where the array form is the better shape, so padded and exact both forgo the array: they are for a database without one, or a query that does worse under it. A padded list never exceeds what the database allows in a list or a statement; where the next power of two would, the list keeps its length. A template that inlines its parameters renders every value as a literal and is not shaped. A statement whose list has rendered with eight different lengths as placeholders carries advice, in the advice log in development and on its slow line in production, where the line also says in how many lengths the shape has run; a database that keeps no plan per statement text, as MySQL and MariaDB with their drivers' client-side prepared statements, gets none, since every length costs it the same there.

A list that renders a placeholder per value counts against the bind parameters a statement may carry, and SQL Server accepts at most 2,098 per statement. The repository methods that take a list of keys, such as findAllById, countById, removeById and removeByRef, split the keys into statements of 1000 keys by default, or 100 for a compound key, within the limit. A chunk size you choose, or an IN list in a query you build yourself, that binds more than 2,098 values fails with the server's parameter limit error; for a larger set, filter through a join or a subquery instead of a list of values.

Composing Multiple Filters​

Multiple where() calls on the same query builder are combined with AND. This lets you build up filters incrementally, which is useful when conditions are added conditionally in application code.

val results = orm.entity(User::class)
    .select()
    .where(User_.email like "%@example.com")
    .where(User_.city eq city)           // AND-combined with previous where
    .resultList

Builder-style where() calls (with and/or predicates) compose with other where() calls in the same way:

val results = orm.entity(User::class)
    .select()
    .where(User_.email like "%@example.com")
    .where(                              // AND-combined with the email filter above
        (User_.role eq adminRole) or (User_.role eq superUserRole)
    )
    .resultList

SQL Functions​

Some comparisons need the column normalized first: an email imported with stray spaces, a search that should ignore accents, a code stored in mixed formats. Doing that in the application means a second, normalized copy of the column, or normalizing the input in code and hoping it matches what the database stored. A SqlFunction has the database do it instead: it applies a database function of one argument to a column, wherever a query takes the column: every operator, search, suggest, orderFirst, orderBy, groupBy and a template placeholder. A comparison applies the function to both sides, the column and the value, so the database computes both and the application computes neither.

The call renders exactly as written, so an expression index built on the same call serves the query: the column keeps one copy of its data, and the index holds the normalized form.

// Emails imported with stray spaces still match, through an index on trim(email).
val trim = SqlFunction.of<String>("trim")

val user = orm.entity(User::class)
    .select()
    .where(trim(User_.email) eq email)    // trim(u.email) = trim(?)
    .singleResult
  • The name is a SQL identifier, optionally qualified by a schema; anything else is refused when the function is declared.
  • The column is a single column: a path through a relation joins its table as for the column itself, and a path spanning several columns is refused.
  • For the text operators the value the function receives is the pattern, escaped and with its wildcards, so the function must leave %, _, SQL Server's [ and the escape character ! alone.
  • A call is computed by the database, so a record holds no value for it. A keyset Scrollable refuses it as key or sort field; orderBy and Pageable sort by it.
  • Calls nest: lower(foldAccents(Owner_.lastName)) (Java: lower.apply(foldAccents.apply(Owner_.lastName))) renders lower(fold_accents(o.last_name)).

The most common function is one that removes accents, so a search ignores them; see Ignoring Accents.

Searching Text​

For a search box, pass what the user typed to search with the columns to look in. Every word of the input must match one of the columns, ignoring case and matching the input literally, so berg madison finds an owner named Van den Berg in Madison through the last name and the city. A type-ahead box that queries as the user types uses suggest, which stays served by the index however little has been typed:

val owners = orm.entity(Owner::class)
    .select()
    .where(search(input, Owner_.lastName, Owner_.address.city.name))
    .resultList

See Search for how the input is split into words, suggestions, searching related rows, ranking the matches, and the indexes that serve a search.

Ordering​

Use orderBy to control result ordering. Pass multiple fields as arguments to sort by more than one column. Use orderByDescending for descending order on a single field.

val users = orm.entity(User::class)
    .select()
    .orderBy(User_.name)
    .resultList

// Descending
val users = orm.entity(User::class)
    .select()
    .orderByDescending(User_.createdAt)
    .resultList

// Multiple fields (all ascending)
val users = orm.entity(User::class)
    .select()
    .orderBy(User_.lastName, User_.firstName)
    .resultList

Multiple orderBy and orderByDescending calls can be chained to build multi-column sort clauses with mixed directions. Each call appends to the existing ORDER BY clause rather than replacing it, so you can mix ascending and descending columns freely.

// Mixed sort directions: last name ascending, first name descending
val users = orm.entity(User::class)
    .select()
    .orderBy(User_.lastName)
    .orderByDescending(User_.firstName)
    .resultList

When a path passed to orderBy, orderByDescending, or groupBy resolves to multiple columns, it expands to those columns in order, resolved exactly as a predicate on that path would be. An inline record (embedded component) expands into its component columns: if User_.fullName is an inline record with lastName and firstName fields, orderBy(User_.fullName) produces ORDER BY last_name, first_name. A foreign key expands to its foreign key column(s) on the referencing table, without joining the referenced table, so orderBy(Visit_.pet) orders by visit.pet_id and a foreign key to a table with a compound primary key contributes every foreign key column once. Grouping states an identity rather than a column, so it resolves differently: see Grouping by an entity. With orderByDescending, DESC follows every expanded column.

For full control over the ORDER BY clause (for example, to use SQL expressions or database-specific syntax), use the template overload. Metamodel fields are resolved to their column names automatically.

// Mixed sort directions (template)
val users = orm.entity(User::class)
    .select()
    .orderBy { "${User_.lastName}, ${User_.firstName} DESC" }
    .resultList

orderFirst sorts by a condition: the rows matching the predicate come before the rows that do not, and the keys after it order each part. It appends a key like orderBy, and takes any predicate a WHERE clause takes.

// Admins first, then by name
val users = orm.entity(User::class)
    .select()
    .orderFirst(User_.role eq adminRole)
    .orderBy(User_.name)
    .resultList

orderFirst renders CASE WHEN <predicate> THEN 0 ELSE 1 END, the same on every database. A keyset scroll follows it, its keys leading the Scrollable's (see Ranking Before the Sort Fields); a distinct query cannot be ranked. See Ranking the Matches for ranking search results.

Aggregation​

To perform GROUP BY queries with aggregate functions like COUNT, SUM, or AVG, define a result data class with the desired columns and pass a custom SELECT expression. Interpolating an entity or projection type generates the column list automatically, so you do not have to enumerate columns manually.

data class CityCount(val city: City, val count: Long)

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

Grouping by an entity​

A grouping states what one row stands for. groupBy accepts the entity you want one row of, and Storm emits whichever columns express that on the database in hand:

.groupBy(User_.city)      // one row per city
.groupBy(User_.city.id)   // the same relationship, so the same grouping
.groupBy(User_.id)        // one row per user
.groupBy(User_.birthDate) // one row per distinct birth date: a value, not an identity

The first three name an identity. The relationship, the key beyond it, and the query root's own key all say the same thing, and a reference says it too. The last names a value, and groups by that value literally.

Selecting an entity selects the tables its foreign keys reach, so when the selected entity has foreign keys of its own the grouping has to cover those tables too. Storm adds their keys. Every foreign key is to-one, so a grouped key already fixes them, and naming them cannot move a row into a different group.

One query, every dialect​

How many columns an identity takes is a property of the database, not of the query. The SQL standard lets a grouped key stand for the columns it determines, and products disagree on whether they implement it:

GROUP BY emitted
PostgreSQL, MySQL, SQLite, H2the key
SQL Server, Oracleevery selected column of that table

Storm generates the form each product accepts, from the same source. Counting pets per pet type:

data class PetTypeCount(val type: PetType, val count: Long)

orm.entity(Pet::class)
    .select(PetTypeCount::class) { "${PetType::class}, COUNT(*)" }
    .groupBy(Pet_.type)
-- PostgreSQL, MySQL, SQLite, H2
SELECT pt.id, pt.name, COUNT(*) FROM pet p INNER JOIN pet_type pt ON p.type_id = pt.id GROUP BY pt.id

-- SQL Server, Oracle
... GROUP BY pt.id, pt.name

Writing the second form by hand is what portability used to cost: a column list that exists only for the strictest product, that has to be revisited whenever the entity gains a field, and that says nothing about intent. Stating the identity says what the query means once, and leaves the spelling to the dialect.

Grouping in a template​

A template is the SQL you wrote, so a metamodel interpolated into one resolves to its column and nothing is added:

orm.query(RAW."""
        SELECT \{City.class}, COUNT(*)
        FROM \{User.class}
        GROUP BY \{City_.id}""")
    .getResultList(CityCount.class);

The grouping has to name every table the select list carries. Where the selected entity has foreign keys of its own, that means their keys as well: the builder adds them for you, a template does not, because it renders what it is given. Reach for the builder when the grouping should follow the entity, and for a template when you want the clause exactly as written.

Filtering Groups​

having() filters the groups that groupBy() produces, the way where() filters rows. A condition on a grouped column takes a predicate; a condition on an aggregate takes a template, because no metamodel path names a computed value.

// Grouped column: a predicate
val counts: List<Long> = orm.entity(User::class)
    .selectCount()
    .groupBy(User_.city)
    .having((User_.city eq amsterdam) or (User_.city eq rotterdam))
    .resultList

// Aggregate: a template
val busy = orm.entity(User::class)
    .select(CityCount::class) { "${City::class}, COUNT(*)" }
    .groupBy(User_.city)
    .having { "COUNT(*) > $minimum" }
    .resultList

Consecutive having() calls are AND-combined, each clause parenthesized, the same way consecutive where() calls are. In Kotlin a disjunction stays in code: compose the predicate with infix or. In Java a predicate cannot be built outside a where() lambda, so a HAVING disjunction uses the template form.

A joined entity's column goes through the same having() call: a join widens the query, so every clause accepts paths from any entity in it. The predicate form takes the predicate directly rather than a builder: a HAVING clause filters groups, so the id, ref and record matching that where()'s builder offers does not carry over.

Data Retrieval Strategies​

When working with large result sets, Storm reads them in parts: a slice, a page, a window, or a stream of windows. offset() and limit() on the query builder remain the manual baseline they build on.

ReadMethodResult typeCount queryTypical use
Sliceslice(pageable)Slice<R>no"load more" without a total, queries without a unique key
Pagepage(pageable)Page<R>yesnumbered pages, reports
Scrollscroll(scrollable)Window<R>noinfinite scroll, REST cursors
Windowswindows(size)Stream<Window<R>> or Flow<Window<R>>nobatch jobs that write while they read

Slice and Page navigate by offset, which the database pays for by scanning the skipped rows, so both degrade on large tables; a page adds a total count, a slice does not. Scroll and Windows navigate by keyset for constant cost at any depth, but move forward or backward from the current window only, and need a unique key.

For detailed usage, sorting, composite scrolling, Window type parameters, GROUP BY with scrolling, and REST cursor support, see Pagination and Scrolling.

Quick examples​

// Offset and limit
val results = orm.entity(User::class).select()
    .orderBy(User_.createdAt)
    .offset(20).limit(10)
    .resultList

// Slice: a page without the count query
val slice: Slice<User> = orm.entity(User::class).select()
    .where(User_.email like "%@example.com")
    .slice(Pageable.ofSize(10))

// Pagination
val page: Page<User> = orm.entity(User::class).select()
    .where(User_.email like "%@example.com")
    .page(Pageable.ofSize(10))

// Scrolling
val window: Window<User> = userRepository.scroll(Scrollable.of(User_.id, 20))
// next() is non-null when the window has content.
// hasNext is informational; the developer decides whether to follow the cursor.
val next = userRepository.scroll(window.next())

Distinct Results​

Add .distinct() to eliminate duplicate rows from the result set. This is useful when selecting a related entity type from a query that could produce duplicates due to one-to-many relationships.

val cities = orm.entity(User::class)
    .select(City::class)
    .distinct()
    .resultList

Grouped Results​

Group results by a related record, typically the parent entity of a foreign key. The metamodel path names the group key; the result is a map from parent to its children:

// Load cities with their users in one query
val usersByCity: Map<City, List<User>> = orm.entity(User::class)
    .select()
    .where(User_.email like "%@example.com")
    .resultGroupedBy(User_.city)

The SQL is not affected by the grouping: the same select is executed and the results are grouped during hydration, so the whole graph loads in a single query. Hydration does not pay for the duplication in the join result: repeated group records are materialized once and grouped by instance identity, not by comparing record fields. The returned map and its lists are unmodifiable and insertion-ordered: groups appear in the order their first result is encountered and results in row order within each group, which without an orderBy() is the order the database returns them in. The grouping does not depend on that order, since a group collects its results wherever they fall in the result set, so order by what you want ordered rather than by the group key: orderBy(User_.city.name, User_.email) gives the cities alphabetically with their users by email. Because duplicate entities within a result set share the same instance, each result's reference to its group key is the map key itself.

The where clause keeps its normal meaning: it filters the results, and a group appears only when at least one of its results matches. The path must resolve to a non-null record for every result; narrow queries over nullable foreign keys with a where() clause first. See Relationships for the one-to-many loading pattern.

resultGroupedBy reads the group key from each hydrated record, so its path must be a value node from the eagerly-loaded graph. A path beyond a Ref is navigation-only and cannot be a group key: it does not compile. Group by the reference itself with resultGroupedByRef, which takes the foreign key without hydrating the target. See Navigating Through Refs.

The ref-based variant resultGroupedByRef (Java: getResultGroupedByRef) returns Map<Ref<V>, List<T>> instead. Refs are compared by primary key, keeping map lookups constant-cost regardless of the size of the group record. For eagerly fetched entity paths the keys are loaded refs: getOrNull() returns the record the query already materialized, combining primary-key lookups with direct access to the data. The path may also reference a Ref field, in which case the group is taken directly from the foreign key without fetching the referenced record; such refs remain unloaded unless the query resolved the reference with fetch(...), and findAllByRef(map.keys) fetches them in a single query when needed:

// Group visits by pet without fetching the pets
val visitsByPet: Map<Ref<Pet>, List<Visit>> = orm.entity(Visit::class)
    .select()
    .resultGroupedByRef(Visit_.pet)

Streaming​

For large result sets, use select().resultFlow, which returns a Kotlin Flow<T>. Rows are fetched lazily from the database as you collect, so memory usage stays constant regardless of result set size. Flow also handles resource cleanup automatically when collection completes or is cancelled.

While rows remain to be emitted, the flow is one open statement and its connection is consume-only: a query, a Ref.fetch() or a write from inside the collector is refused inside a transaction, on every database. A loop that needs the database iterates in windows with select().windows(size), one closed statement per window; see Batch Processing & Streaming.

val users: Flow<User> = orm.entity(User::class).select().resultFlow

// Process each
users.collect { user -> process(user) }

// Transform and collect
val emails: List<String> = users.map { it.email }.toList()

// Count
val count: Int = users.count()

Joins​

Storm automatically joins entities referenced by @FK fields. When you need to join entities that are not directly referenced in the result type (for example, filtering through a many-to-many join table), use explicit innerJoin or leftJoin calls. The on clause specifies which existing entity in the query the joined table relates to.

A join widens the query: from the join onward, every clause accepts paths from any entity in the query, so the joined table's fields go through the same where, orderBy, groupBy and having calls as the root's.

val roles = orm.entity(Role::class)
    .select()
    .innerJoin<UserRole>().on<Role>()
    .where(UserRole_.user eq user)
    .resultList

The widening trades the compile-time root check for query-time resolution: a path on an entity that is not part of the query fails when the query is built, with an error naming the entity and the root. Root-relative operations are affected by the wider type: fetch(...) comes before any join, and a grouped terminal such as resultGroupedBy needs the root back: narrow<Role>() restores it, verified against the query's FROM table. The counterpart widen() widens without a join, admitting short-form references to the entities already in the query's graph.

The infix and/or combinators follow the same discipline: a combination roots at its operands' least common root, and written directly in a where(...) call the builder's root drives the inference. On the widened query above, .where((UserRole_.user eq user) and (Role_.name eq name)) composes predicates across the joined entities in one clause, while the same expression on a narrow, join-less builder is rejected at the call site.

Join Conditions​

on derives the join condition from the foreign key. A condition of your own goes in a block after the table it joins on, and the join adds it to the derived one. On a left join, it decides which related rows join, so an owner without a matching pet is still returned, where the same condition in where would drop the owner:

val owners = orm.entity(Owner::class)
    .select()
    .leftJoin<Pet>().on(Owner::class, Pet_.name eq "Leo")
    .resultList
SELECT ...
FROM owner o
LEFT JOIN pet p ON p.owner_id = o.id AND (p.name = ?)

The condition after the table is a filter on the joined rows. A term that equates a key states a relation, and a relation is stated as a whole, as a predicate in place of the table. With a Transfer(pet: Pet, from: Owner, to: Owner), the transfers to an owner of the pet the query joined are innerJoin<Transfer>().on((Transfer_.to.id eq Owner_.id) and (Transfer_.pet.id eq Pet_.id)), not the table with the second key as its condition, and a filter on top goes in the same predicate, and (Transfer_.date greaterEq since). In Java, on(it -> it.where(Transfer_.to.id, EQUALS, Owner_.id).and(it.where(Transfer_.pet.id, EQUALS, Pet_.id))).

When a table holds more than one foreign key to the table it is joined on, on cannot tell which one the join follows and refuses the join, naming the keys. on then takes a predicate stating the relation instead of the table. With a Transfer(pet: Pet, from: Owner, to: Owner) that records a pet changing owners, the owners who received a pet:

val owners = orm.entity(Owner::class)
    .select()
    .innerJoin<Transfer>().on(Transfer_.to.id eq Owner_.id)
    .resultList
SELECT ...
FROM owner o
INNER JOIN transfer t ON t.to_id = o.id

The predicate states the relation as a whole, and the keys are read off it when the query is built. A term equating a key of the joined table with a key of another table is the key the join resolves by: a foreign key with the primary key it references, written through the key beyond the foreign key, which is the foreign key column itself, so Transfer_.to.id eq Owner_.id renders as t.to_id = o.id; or two foreign keys referencing one table, where neither table references the other: innerJoin<Transfer>().on(Transfer_.pet eq Visit_.pet) joins the transfers of the pet a visit was for. The same term names the key from the other side, innerJoin<Owner>().on(Transfer_.to.id eq Owner_.id) from a query on transfers. The remaining terms are the join's condition, as the condition after the table is, on((Transfer_.to.id eq Owner_.id) and (Transfer_.date greater since)), and a relation of two pairs of keys states both, on((Invoice_.pet eq Visit_.pet) and (Invoice_.vet eq Visit_.vet)). An inner or left join takes any predicate: one equating no keys is the ON clause it reads as. In Java the predicate is a where condition, on(it -> it.where(Transfer_.to.id, EQUALS, Owner_.id)), and the block DSL takes the predicate in place of the table: innerJoin<Transfer>(Transfer_.to.id eq Owner_.id).

A semi-join or anti-join takes a predicate equating a key of the joined table with a key of the table it is joined on, and follows one key, so a table that holds two keys to the query's table is filtered by both with a join per key. The owners no transfer names, as sender or as receiver, are the rows neither key refers to:

orm.selectFrom<Owner>()
    .antiJoin<Transfer>().on(Transfer_.from.id eq Owner_.id)
    .antiJoin<Transfer>().on(Transfer_.to.id eq Owner_.id)

Two semi-joins keep the rows both keys refer to, the owners who sent a pet and received one. The rows either key refers to are a correlated subquery, whose condition names the query's own table through outer, since Owner_.id inside the subquery names the transfer's own owners: whereExists { subquery<Transfer>().where((Transfer_.from.id eq outer(Owner_.id)) or (Transfer_.to.id eq outer(Owner_.id))) }. See Correlated Subqueries.

A join to the many side of a relation returns a row per related row: an owner of two pets that match the condition comes back twice, and returning each owner once takes distinct. semiJoin filters by the related table without joining its rows. The query keeps the rows that have a related row matching the conditions that name the table, and returns each of them once:

val owners = orm.entity(Owner::class)
    .select()
    .semiJoin<Pet>().on<Owner>()
    .where(Pet_.name eq "Leo")
    .resultList
SELECT ...
FROM owner o
WHERE EXISTS (SELECT 1 FROM pet p WHERE p.owner_id = o.id AND p.name = ?)

on resolves the foreign key between the two tables as it does for the other joins. The conditions on the semi-joined table are answered by its related rows:

  • Conditions joined by and hold for the same related row. (Pet_.name eq "Leo") and (Pet_.birthDate gt date) finds the owners of a Leo born after the date, not the owners of a Leo and of some other pet born after it.
  • A part of the condition that names only the query's own tables holds without a related row, so (Owner_.lastName eq "Davis") or (Pet_.name eq "Leo") also finds a Davis without pets.
  • An or across two semi-joined tables holds where either side holds, each answered by a related row of its own table: with semiJoin<Visit>().on<Pet>() and semiJoin<PetExtension>().on<Pet>(), (Visit_.description eq "neutered") or (PetExtension_.notes eq "Indoor cat") finds the pets with either, as one IN over a UNION with a branch per table. Where the database runs an IN over a UNION once per outer row rather than once (SqlDialect.flattensInOverUnion()), the or renders as an EXISTS per table instead, each correlated through its foreign key. A term comparing columns of two semi-joined tables is refused, since no row of either answers it.
  • search matches each word on its own, so its words may match different related rows, of one semi-joined table or of several; see Searching Related Rows.

The semi-joined table filters the query but adds no columns to it: ordering, grouping or selecting by its columns is refused when the query is built. Without a condition that names its table, a semi-join keeps the rows that have any related row: semiJoin<Pet>().on<Owner>() finds the owners with a pet, the complement of antiJoin<Pet>().on<Owner>(). A condition stated on the join keeps that symmetry, so a semi-join and an anti-join with the same condition on the join split the rows between them. It also states which related rows count at all: every condition that names the table, each word of a search included, is answered by a related row that matches it.

antiJoin keeps the rows without a matching related row. Its condition goes on the join, where the negation is visible; without one, the query keeps the rows without any related row:

// Pets that never had a rabies shot.
val pets = orm.entity(Pet::class)
    .select()
    .antiJoin<Visit>().on(Pet::class, Visit_.description eq "rabies shot")
    .resultList
SELECT ...
FROM pet p
WHERE NOT EXISTS (SELECT 1 FROM visit v WHERE v.pet_id = p.id AND v.description = ?)

NOT EXISTS, unlike NOT IN, keeps its meaning when a related row's foreign key is null. A condition in where that names the anti-joined table is refused when the query is built, pointing at the join's condition.

Two semi-joins or anti-joins on one table are two subqueries, each with its own condition on the join. antiJoin<Visit>().on(Pet::class, Visit_.description eq "rabies shot") followed by semiJoin<Visit>().on<Pet>() finds the pets that had a visit but never a rabies shot, and two semi-joins with different conditions find the rows with a related row matching each, where one semi-join with both conditions asks for a single related row matching both. A condition in where that names the table could not tell the joins apart and is refused; each condition goes on its join.

A table the query joins may be filtered by as well. The owners with their pets, leaving out the owners who keep a snake:

val owners = orm.entity(Owner::class)
    .select()
    .innerJoin<Pet>().on<Owner>()
    .antiJoin<Pet>().on(Owner::class, Pet_.type.name eq "snake")
    .orderBy(Pet_.name)
    .resultList
SELECT ...
FROM owner o
INNER JOIN pet p ON p.owner_id = o.id
WHERE NOT EXISTS (
    SELECT 1 FROM pet p1 INNER JOIN pet_type pt ON p1.type_id = pt.id
    WHERE p1.owner_id = o.id AND pt.name = ?)
ORDER BY p.name

The condition on the anti-join names the pets its subquery looks for. Everything else that names the table names the joined pets: a condition in where, orderBy, groupBy, the select, and the on of a later join. A semiJoin on a table the query joins states its condition on the join for the same reason, and is refused without one. Inside the condition, outer names the joined row: antiJoin<Pet>().on(Owner::class, Pet_.id neq outer(Pet_.id)) keeps the pets whose owner has no other pet.

Correlated Subqueries​

whereExists and whereNotExists take a subquery for a condition a foreign key does not express. The subquery is correlated in its condition: outer names a column of the enclosing query, and a comparison relates a column of the subquery to it. The owners with a pet that had at least a given number of visits:

val owners = orm.entity(Owner::class)
    .select()
    .whereExists {
        subquery<Visit>()
            .where(Visit_.pet.owner.id eq outer(Owner_.id))
            .groupBy(Visit_.pet)
            .having { "COUNT(*) >= $minimum" }
    }
    .resultList
SELECT ...
FROM owner o
WHERE EXISTS (
    SELECT 1 FROM visit v INNER JOIN pet p ON v.pet_id = p.id
    WHERE p.owner_id = o.id
    GROUP BY v.pet_id
    HAVING COUNT(*) >= ?)

A path without outer names the subquery's own tables, the tables it joins included: Visit_.pet.owner.id eq Owner_.id compares the owner of the visit's pet with itself and correlates nothing. outer names the nearest enclosing query that holds the table, also where the subquery reads the same table as the enclosing query, Pet_.owner.id eq outer(Pet_.owner.id) for the other pets of a pet's owner. In Java, outer is Metamodel.outer. It is the other side of a comparison only, with or without a SqlFunction applied to it, in either order: upper.apply(outer(Owner_.firstName)) or outer(upper.apply(Owner_.firstName)). Everything else that takes a path of the query refuses it when the query is built: a comparison with a value, orderBy, groupBy, search, fetch, set, a scroll. In a template, outer(Owner_.id) renders the outer column; see ResolveScope.

Filtering Through Two Relations​

on also names a semi-joined table, to filter through two to-many relations. The owners with a pet that had a visit this year:

val owners = orm.entity(Owner::class)
    .select()
    .semiJoin<Pet>().on<Owner>()
    .semiJoin<Visit>().on<Pet>()
    .where(Visit_.visitDate greaterEq LocalDate.of(2026, 1, 1))
    .resultList
SELECT ...
FROM owner o
WHERE EXISTS (
    SELECT 1 FROM pet p
    WHERE p.owner_id = o.id
      AND EXISTS (SELECT 1 FROM visit v WHERE v.pet_id = p.id AND v.visit_date >= ?))

The visit's subquery nests in the pet's, and the rules of a semi-join hold at each level: a condition naming Visit goes into the visit's subquery, and conditions joined by and on Pet and Visit hold for the same pet. Each owner still comes back once. An anti-join on a semi-joined table nests as a NOT EXISTS: semiJoin<Pet>().on<Owner>() followed by antiJoin<Visit>().on<Pet>() finds the owners with a pet that never had a visit. A semi-join on an anti-joined table decides which related rows exclude a row, so like the anti-join it takes its condition on the join.

on also names a table that a semi-joined table references through a foreign key, such as the table a link row points at. With a link table PetSitting(sitter: Owner, pet: Pet), semiJoin<PetSitting>().on<Owner>() followed by semiJoin<Visit>().on<Pet>() finds the owners who sat a pet that had a visit. The visit relates to the link row through the link's foreign key, without reading the pet table:

WHERE EXISTS (
    SELECT 1 FROM pet_sitting ps
    WHERE ps.sitter_id = o.id
      AND EXISTS (SELECT 1 FROM visit v WHERE v.pet_id = ps.pet_id))

A table the query reaches itself, as its own table, a joined table or a table its foreign keys join, stays the query's.

on also names a table that neither references nor is referenced by the semi-joined table, when both hold a foreign key to the same table. With PetNickname(pet: Pet, nickname: String) and Vaccination(pet: Pet, vaccine: String), the pets found by their own name, or by a nickname when the pet is vaccinated:

val pets = orm.entity(Pet::class)
    .select()
    .semiJoin<PetNickname>().on<Pet>()
    .semiJoin<Vaccination>().on<PetNickname>()
    .where((Pet_.name like "$input%") or (PetNickname_.nickname like "$input%"))
    .resultList
SELECT ...
FROM pet p
WHERE p.id IN (
    SELECT p.id FROM pet p WHERE p.name LIKE ?
    UNION
    SELECT n.pet_id FROM pet_nickname n
    WHERE n.nickname LIKE ?
      AND EXISTS (SELECT 1 FROM vaccination v WHERE v.pet_id = n.pet_id))

The vaccination relates to the nickname through the pet both reference, v.pet_id = n.pet_id, without reading the pet table, and its subquery sits inside the nickname's: a pet found by its own name needs no vaccination. semiJoin<Vaccination>().on<Pet>() would relate the vaccination to the query's pet instead and require one on every branch. When the two tables reference the same table through more than one pair of foreign keys, the key to relate them on is ambiguous and the join is refused; on then takes the predicate equating the pair, semiJoin<Vaccination>().on(Vaccination_.pet eq PetNickname_.pet), as it takes a key equated with the key it references where a table holds more than one key to the table it is joined on, semiJoin<Transfer>().on(Transfer_.to.id eq Owner_.id). A semi-join of the query's own table equates a key of the table with itself, the joined row's with the query row's, where the table form is ambiguous: semiJoin<Transfer>().on(Transfer_.to eq Transfer_.to) finds the transfers to the same owner as the query's transfer. See Join Conditions.

The same rule relates a semi-joined table to the query's own table. With Tenant(owner: Owner) and Pet(owner: Owner), semiJoin<Pet>().on<Tenant>() finds the tenants whose owner has a matching pet, p.owner_id = t.owner_id, and an or across Tenant_.name and Pet_.name branches as a UNION like any other: the pets found by name give owner keys, through which the tenants are read.

Filtering by Other Rows of the Same Table​

A semi-join on the query's own table finds the rows that reference the same row of another table as the query's row, and on names that table. The visits since a date of the pets that had a rabies shot:

val visits = orm.entity(Visit::class)
    .select()
    .semiJoin<Visit>().on(Pet::class, Visit_.description eq "rabies shot")
    .where(Visit_.visitDate greaterEq LocalDate.of(2026, 1, 1))
    .resultList
SELECT ...
FROM visit v
WHERE v.visit_date >= ?
  AND EXISTS (SELECT 1 FROM visit v1 WHERE v1.pet_id = v.pet_id AND v1.description = ?)

The condition on the join names the other visit, and a condition in where names the query's own. The two relate through the foreign key both hold to the table on names, so on takes a table the query's table references; on<Visit> is refused, as is a table that references the query's table instead. antiJoin finds the rows whose referenced row has no other such row: the visits of the pets that never had a rabies shot. The query orders, groups and selects by its own table as before.

Semi-joins and anti-joins filter a delete() and an update() as well, nested ones included.

Deleting Rows by Condition​

delete() builds a DELETE that removes the rows its conditions select, in one statement and without reading the rows. Its conditions, joins, semi-joins and anti-joins are the ones a select takes. remove(entity) removes entities you read; the builder removes rows by what they hold.

// Block DSL, like select { }.
visits.delete {
    where(Visit_.visitDate less cutoff)
}.executeUpdate()

// Chained.
visits.delete()
    .where(Visit_.visitDate less cutoff)
    .executeUpdate()

// Through a related table: the owners without pets.
owners.delete()
    .antiJoin<Pet>().on<Owner>()
    .executeUpdate()

executeUpdate() returns the number of rows deleted. orm.deleteFrom(Visit::class) (Java: orm.deleteFrom(Visit.class)) and, in Kotlin, orm.delete<Visit>() create the same builder. removeBy, removeAllBy and removeAll(predicate) are shorthands for it.

How it behaves:

  • A condition is required. A DELETE without a WHERE clause, semi-join or anti-join is refused unless unsafe() is called; see Why does my DELETE without a WHERE clause throw an exception?.
  • Entity callbacks do not fire. The statement carries no entity to pass to beforeRemove or afterRemove; see Remove Callbacks.
  • The transaction forgets the entities of the type. Once the statement runs, the entities of the deleted type observed in the current transaction are evicted from its entity cache, so a later read does not return a row the statement removed.
  • DISTINCT, LIMIT, OFFSET and locking are refused, as is reading results, scrolling, or using the builder as a subquery.
  • A Joined Table entity is refused, its sealed type and each subtype: its rows span the base table and an extension table, and a DELETE removes the rows of one. remove and removeById delete from every table; see Joined Table Inheritance.

A condition through a path, an explicit join, a semi-join or an anti-join reaches the rows through other tables. On MySQL, MariaDB and SQL Server the statement joins those tables itself, as MySQL refuses a subquery that reads the table being changed; on PostgreSQL, Oracle, H2 and SQLite it matches the rows by primary key in a subquery. The builder reads the same on every database.

Updating Rows by Condition​

update() builds an UPDATE that sets columns on the rows its conditions select, in one statement and without reading the rows. It is the counterpart of delete(): the conditions, joins, semi-joins and anti-joins are the ones a select takes, and set names the columns to write through the metamodel, so the table's own entity serves the write and no second type is needed for it.

// Block DSL, like select { } and delete { }.
pets.update {
    set(Pet_.owner, newOwner)
    where(Pet_.owner eq previousOwner)
}.executeUpdate()

// Chained.
visits.update()
    .set(Visit_.description, "Rescheduled")
    .where(Visit_.visitDate eq closedDay)
    .executeUpdate()

// A value computed from the row.
owners.update {
    set(Owner_.lastName) { "UPPER(${Owner_.lastName})" }
    where(Owner_.address.city eq city)
}.executeUpdate()

executeUpdate() returns the number of rows updated. orm.updateFrom(Owner::class) (Java: orm.updateFrom(Owner.class)) and, in Kotlin, orm.update<Owner>() create the same builder.

What set takes:

  • A value typed by the field. A nullable field takes null (Java: setNull(field)); a field that is not nullable refuses it.
  • A foreign key takes the referenced entity or a Ref to it, and writes its key.
  • An inline record takes a record of its type and writes each of its columns; a field inside it, such as Owner_.address.city, writes its own.
  • An expression, a template computing the value of a single column from the row. A change computed from the column's current value, such as a stock level, a balance or a counter, belongs here: one statement that reads nothing first cannot lose a concurrent writer's change, where a read, a change in code and update(entity) can. See Choosing the Remedy for a Lost Update.

set refuses, with a message naming the field: the primary key and each part of a compound key, a field declared @Persist(updatable = false), the @Version field, a path into a related table such as Owner_.address.city.name, and a field set twice. An UPDATE without any set is refused when it runs.

How it behaves:

  • A condition is required. As for delete(), an UPDATE without a WHERE clause, semi-join or anti-join is refused unless unsafe() is called.
  • The version moves. On an entity with a @Version field the statement increments it, or sets it to the current timestamp for a timestamp version, as update(entity) does. An entity read before the statement then fails its optimistic lock check when it is updated afterwards, rather than writing its stale values over the new ones.
  • Entity callbacks do not fire. The statement carries no entity to pass to beforeUpdate or afterUpdate, as delete() carries none for the remove callbacks; see Entity Lifecycle. Bookkeeping a callback does on update, such as an updated-at timestamp, is set in the builder.
  • The transaction forgets the entities of the type. Once the statement runs, the entities of the updated type observed in the current transaction are evicted from its entity cache, so a later read returns the new values. An entity read before the statement still holds its old values: re-read the entities the statement touched before writing them back. An update(entity) of a copy read before the statement writes its full row, since dirty checking has no observed state to compare against, and so puts its stale values back over the columns the statement set; a versioned entity fails its optimistic lock check instead.
  • DISTINCT, LIMIT, OFFSET and locking are refused, as is reading results, scrolling, or using the builder as a subquery.
  • A Joined Table entity is refused, its sealed type and each subtype, as for delete(): an UPDATE writes the columns of one table. update(entity) writes each table of the hierarchy.

Rows reached through other tables take the same form as for delete(): the statement joins the tables itself on MySQL, MariaDB and SQL Server, and matches the rows by primary key in a subquery elsewhere.

Result Classes​

Query result classes can be:

  • Plain records -- Storm maps columns to fields (you write all SQL)
  • Data implementations -- enable SQL template helpers like ${Class::class}
  • Entity/Projection -- full repository support with CRUD operations

Choose the simplest option that meets your needs. See SQL Templates for details.

Compound Fields in Queries​

When an inline record (embedded component) is used in a query clause, Storm automatically expands it into its constituent columns. This applies to WHERE, ORDER BY, and GROUP BY clauses. A foreign key expands the same way in WHERE and ORDER BY, into its foreign key column(s) on the referencing table; in GROUP BY it states an identity, described under Grouping by an entity.

WHERE Clauses​

Inline records expand differently depending on the operator:

Equality (eq / neq, Java EQUALS / NOT_EQUALS) generates per-column AND conditions:

val owner = orm.entity(Owner::class)
    .select()
    .where(Owner_.address eq address)
    .singleResult
WHERE o.address = ? AND o.city_id = ?

For inequality (neq, Java NOT_EQUALS), the condition is wrapped in NOT:

WHERE NOT (o.address = ? AND o.city_id = ?)

Comparison operators (greater, greaterEq, less, lessEq; Java GREATER_THAN, GREATER_THAN_OR_EQUAL, LESS_THAN, LESS_THAN_OR_EQUAL) generate lexicographic comparisons using nested OR/AND. This preserves the natural multi-column ordering:

val owners = orm.entity(Owner::class)
    .select()
    .where(Owner_.address greater address)
    .resultList
WHERE (o.address > ? OR (o.address = ? AND o.city_id > ?))

For the inclusive variants (greaterEq / lessEq, Java GREATER_THAN_OR_EQUAL / LESS_THAN_OR_EQUAL), only the last column uses the inclusive operator:

WHERE (o.address > ? OR (o.address = ? AND o.city_id >= ?))

Some databases (PostgreSQL, MySQL, MariaDB, Oracle) support native tuple comparison syntax, which Storm uses automatically when available:

WHERE (o.address, o.city_id) > (?, ?)

Unsupported operators. like, notLike, inList, and notInList (Java LIKE, NOT_LIKE, IN, NOT_IN) do not have a meaningful multi-column interpretation and throw a PersistenceException when used with inline records. The operators that take text, contains, startsWith, endsWith and the ones that ignore case, their negations included, compare a single column and are refused the same way, with a message naming the path. To filter on a sub-field, reference it directly:

val owners = orm.entity(Owner::class)
    .select()
    .where(Owner_.address.address like "%Main%")
    .resultList

ORDER BY​

Passing an inline record to orderBy or orderByDescending expands it into its leaf columns. For example, if Owner_.address is an inline record with address and city fields:

val owners = orm.entity(Owner::class)
    .select()
    .orderBy(Owner_.address)
    .resultList
ORDER BY o.address, o.city_id

Using orderByDescending applies DESC to each expanded column:

ORDER BY o.address DESC, o.city_id DESC

GROUP BY​

Inline records expand in GROUP BY the same way, and a foreign key groups by its foreign key column(s) on the referencing table: groupBy(Order_.city) produces GROUP BY o.city_id, without joining the referenced table. Grouping by a column makes it unique in the result set, which is particularly useful in combination with scrolling. Wrap the metamodel with .key() to indicate it can serve as a cursor:

data class CityOrderCount(val city: City, val count: Long)

val orders = orm.entity(Order::class)
val window = orders.select(CityOrderCount::class) { "${City::class}, COUNT(*)" }
    .groupBy(Order_.city)
    .scroll(Scrollable.of(Order_.city.key(), 20))

See Scrolling: GROUP BY for details.

Optimizer Hints​

Add .hint(...) to pass an optimizer hint to the database, for the rare query that needs one to keep its plan, for example across an upgrade that changes how the optimizer treats a derived table. The hint is the database's own text and Storm passes it through as is; the dialect decides where it goes.

val users = orm.entity(User::class)
    .select()
    .where(User_.email contains searchText)
    .hint("MAX_EXECUTION_TIME(1000)")
    .resultList
DatabaseWhere the hint goes
MySQL, MariaDB 12+, OracleA /*+ ... */ comment right after SELECT, UPDATE or DELETE
SQL ServerAn OPTION (...) clause at the end of the statement
PostgreSQL, H2, SQLiteLeft out: these databases have no hint syntax

Several calls add several hints to the same comment or clause. A hint never changes what a query returns, so a database without hint syntax runs the query without it, and code tested on H2 runs unchanged in production. SQL Server takes its query hints once per statement, so there a hint belongs on the outer query rather than a subquery. A hint containing */ or ; is refused, since it would end the comment that carries it or the statement.

Common Patterns​

Checking Existence​

Use existsBy, exists(...) on a repository, or exists() on the query builder to check whether a matching row exists. The statement stops at the first matching row: it selects a constant, limited to one row, so the database reads no row beyond the first and may answer from an index alone. A count would visit every matching row. A query whose shape decides whether a row comes back (GROUP BY, HAVING, a limit or an offset, or a select clause of your own) is tested as a derived table.

val taken: Boolean = orm.existsBy(User_.email, email)

val vaccinated: Boolean = orm.entity(Pet::class)
    .select()
    .where(Pet_.owner eq owner)
    .semiJoin<Visit>().on(Pet::class, Visit_.description eq "rabies shot")
    .exists()

Use exists() rather than comparing resultCount with zero.

Count with Filter​

Combine where with resultCount (Kotlin) or getResultCount() (Java) to count rows matching a condition without loading the entities themselves. Storm translates this to a SELECT COUNT(*) query.

val count: Long = orm.entity(User::class)
    .select()
    .where(User_.city eq city)
    .resultCount

Finding a Single Result​

When you expect at most one matching row, use find (Kotlin, returns null if not found) or getOptionalResult (Java, returns Optional). These methods throw if more than one row matches.

val user: User? = orm.find(User_.email eq email)

Tips​

  1. Use the metamodel -- User_.email catches typos at compile time; see Metamodel
  2. Kotlin: choose your style -- quick queries (orm.find, orm.findAll) for simple cases, query builder for complex operations
  3. Java: DSL or Templates -- DSL for type-safe conditions, SQL Templates for complex SQL like CTEs, window functions, or database-specific features
  4. Entity graphs load in one query -- related entities marked with @FK are JOINed automatically, so the declared graph costs one statement and nothing loads behind your back
  5. Close Java streams -- always use try-with-resources with Stream results
  6. Combine conditions freely -- use and / or in Kotlin, it.where().and() / .or() in Java to build complex predicates
  7. Always use the returned builder -- QueryBuilder is immutable; methods like where(), orderBy(), and limit() return a new instance. Ignoring the return value silently loses the change. Chain calls or reassign the variable.