Core Concepts

SQL Blocks

Understanding the sql construct and how ExoQuery captures your Kotlin code

The sql construct is both a tiny builder-DSL and a compile-time capture. When you enter an sql { ... } or sql.select { ... } or sql.expression { ... } block, the ExoQuery compiler plugin reifies the code you write into a SQL AST at compile time. This is how ExoQuery lets you use regular Kotlin constructs like if, when, ?:, and let in queries.

Types of SQL Blocks

There are a few different kinds of sql blocks you can start with:

Table Expression

A regular table-expression:

val people: SqlQuery<Person> = sql { Table<Person>() }
//> SELECT x.name, x.age FROM Person x
val joes: SqlQuery<Person> = sql { Table<Person>().where { p -> p.name == "Joe" } }
//> SELECT p.name, p.age FROM Person p WHERE p.name = 'Joe'
// (Notice how ExoQuery knows that 'p' should be the variable in this case?)

Table-Select Function

A table-select function. This is a special syntax for doing joins, groupBy, and other complex expressions.

val people: SqlQuery<Pair<Person, Address>> = sql.select {
  val p = from(people)
  val a = join(addresses) { a -> a.personId == p.id }
  p to a
}
//> SELECT p.id, p.name, p.age, a.personId, a.street, a.zip  FROM Person p JOIN Address a ON a.personId = p.id

(This is actually just a shortening of the sql { select { ... } } expression.)

val people: SqlQuery<Pair<Person, Address>> = sql {
  select {
    val p = from(people)
    val a = join(addresses) { a -> a.personId == p.id }
    p to a
  }
}
//> SELECT p.id, p.name, p.age, a.personId, a.street, a.zip  FROM Person p JOIN Address a ON a.personId = p.id

Expression Block

An arbitrary code snippet:

val nameIsJoe: SqlExpression<(Person) -> Boolean> = sql.expression {
  { p: Person -> p.name == "Joe" }
}

You can them use them with normal queries like this:

// The .use function changes it from SqlExpression<(Person) -> Boolean> to just (Person) -> Boolean
// you can only use it inside of a `sql` block.
sql { Table<Person>().filter { p -> nameIsJoe.use(p) } }

When to Use `.use`

The .use property extracts the value T from SqlExpression<T>.

Simple rule: Whenever you have SqlExpression<T> and need T, call .use.

Where `.use` IS Required

Context	Example
`groupBy()` arguments	`groupBy(monthOf(date).use, name)`
`sortBy()` arguments	`sortBy(monthOf(date).use to Ord.Asc)`
`filter()` / `where {}` with expression fragments	`filter { p -> isAdult(p.age).use }`
Arithmetic with expressions	`amount - coalesce(refund).use`
Storing in a `val` for reuse	`val month = monthOf(date).use`
Data class field in projection	`MyResult(monthOf(date).use, name)`

Where `.use` is NOT Needed

Context	Why
Inside `free()` string interpolation	`free("UPPER(${p.name})")` — values are interpolated as SQL text
Direct entity field access	`p.name`, `p.age` — these are already `T`, not `SqlExpression<T>`

Example: Correct Usage Pattern

@SqlFragment
fun monthOf(dateStr: String): SqlExpression<String> = sql.expression {
    free("strftime('%Y-%m', ${dateStr})").asPure<String>()
}

val q = sql.select {
    val i = from(Table<Invoice>())
    val month = monthOf(i.paidAt).use  // ← .use extracts String from SqlExpression<String>
    groupBy(month, i.status)           // ← month is now String, works in groupBy
    sortBy(month to Ord.Asc)           // ← also works in sortBy
    Result(month, count())             // ← and in the projection
}

Common Error

If you forget .use, you'll see errors like:

Return type mismatch: expected 'Boolean', actual 'SqlExpression<Boolean>'
Argument type mismatch: actual type is 'SqlExpression<String>', but 'String' was expected

The fix is always: add .use to extract the value from the expression.

What can sql.select return

The final expression in an sql.select block determines what the query returns. This can be any of the following:

Single Entity

Return an entire table entity:

val q = sql.select {
  val p = from(Table<Person>())
  where { p.age > 18 }
  p
}
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.age > 18

Note: Currently multiple where { ... } clauses are not supported. Combine all conditions into a single where clause using &&. If you try to use multiple where clauses, only the last one will be used. For example:
val q = sql.select {
 val p = from(Table<Person>())
 where { p.age > 18 }
 where { p.name.startsWith("J") }
 p
}
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.name LIKE 'J%'

Single Field

Return a single column:

val q = sql.select {
  val p = from(Table<Person>())
  p.name
}
//> SELECT p.name AS value FROM Person p

Pair

Use to or Pair() for two values:

val q = sql.select {
  val p = from(Table<Person>())
  val a = join(Table<Address>()) { a -> a.ownerId == p.id }
  p to a
}
//> SELECT p.id, p.name, p.age, a.id, a.ownerId, a.street, a.city FROM Person p INNER JOIN Address a ON a.ownerId = p.id

Triple

Use Triple() for three values:

val q = sql.select {
  val p = from(Table<Person>())
  val a = join(Table<Address>()) { a -> a.ownerId == p.id }
  val r = join(Table<Robot>()) { r -> r.ownerId == p.id }
  Triple(p, a, r)
}
//> SELECT p.id, p.name, p.age, a.id, a.ownerId, a.street, a.city, r.id, r.ownerId, r.name, r.model
//>   FROM Person p INNER JOIN Address a ON ... INNER JOIN Robot r ON ...

Custom Data Class

Project into any @Serializable data class:

@Serializable
data class PersonSummary(val name: String, val city: String, val orderCount: Int)

val q = sql.select {
  val p = from(Table<Person>())
  val a = join(Table<Address>()) { a -> a.ownerId == p.id }
  groupBy(p.name, a.city)
  PersonSummary(p.name, a.city, count())
}
//> SELECT p.name, a.city, count(*) AS orderCount FROM Person p INNER JOIN Address a ON ... GROUP BY p.name, a.city

Aggregations

An sql.select statement can return aggregate values directly:

val q = sql.select {
  val p = from(Table<Person>())
  count()
}
//> SELECT count(*) AS value FROM Person p

Available aggregate functions inside sql.select:

Function	Returns	Notes
`count()`	`Int`	Count all rows
`count(field)`	`Int`	Count non-null values
`countDistinct(field)`	`Int`	Count distinct values.
`sum(field)`	Same as input (`Long` for `Long`/`Int`)	Sum of values
`avg(field)`	`Double`	Average
`min(field)`	Same as input	Minimum
`max(field)`	Same as input	Maximum

See API Reference - Aggregate Functions for complete type signatures.

Multiple Aggregations

An sql.select statement can combine multiple aggregates in a data class:

@Serializable
data class Stats(val total: Int, val avgAge: Double, val maxAge: Int)

val q = sql.select {
  val p = from(Table<Person>())
  Stats(count(), avg(p.age), max(p.age))
}
//> SELECT count(*) AS total, avg(p.age) AS avgAge, max(p.age) AS maxAge FROM Person p

Conditional Expressions

An sql.select statement can use if, when, and ?: in return expressions:

@Serializable
data class PersonCategory(val name: String, val category: String)

val q = sql.select {
  val p = from(Table<Person>())
  PersonCategory(
    p.name,
    when {
      p.age >= 65 -> "senior"
      p.age >= 18 -> "adult"
      else -> "minor"
    }
  )
}
//> SELECT p.name, CASE WHEN p.age >= 65 THEN 'senior' WHEN p.age >= 18 THEN 'adult' ELSE 'minor' END AS category FROM Person p

Window Functions

An sql.select statement can include window function results:

val q = sql.select {
  val c = from(Table<Person>())
  Pair(c.name, over().partitionBy(c.membership).orderBy(c.name).avg(c.age))
}
//> SELECT c.name AS first, AVG(c.age) OVER (PARTITION BY c.membership ORDER BY c.name) AS second FROM Customer c

Nested Data Classes

An sql.select statement can return nested structures (columns are flattened in SQL, reconstructed on decode):

@Serializable
data class Name(val first: String, val last: String)

@Serializable
data class PersonName(val id: Int, val name: Name)

@Serializable
data class FullNamePerson(val id: Int, val firstName: String, val lastName: String)

val q = sql.select {
  val p = from(Table<FullNamePerson>())
  PersonName(p.id, Name(p.firstName, p.lastName))
}
//> SELECT p.id, p.first_name AS first, p.last_name AS last FROM FullNamePerson p

Custom SQL with free()

For database-specific functions or syntax not directly supported by ExoQuery, an sql.select statement can return free() calls to inject raw SQL. You must always specify the return type using .invoke<T>() or .asPure<T>(). ExoQuery cannot infer the type.

An sql.select statement can return a single value with .invoke<T>():

val q = sql.select {
  val p = from(Table<Person>())
  Pair(p.name, free("LENGTH(${p.name})").invoke<Int>()) // or free("LENGTH(${p.name})")<Int>() for short 
}
//> SELECT p.name AS first, LENGTH(p.name) AS second FROM Person p

An sql.select statement can return a single value using .asPure<T>() (equivalent for scalar values):

data class Customer(val name: String, val email: String)

val q = sql.select {
  val p = from(Table<Customer>())
  Pair(p.name, free("UPPER(${p.email})").asPure<String>())
}
//> SELECT p.name AS first, UPPER(p.email) AS second FROM Person p

In an sql.select statement you can combine free() with data class fields:

@Serializable
data class PersonWithUpper(val name: String, val upperName: String)

val q = sql.select {
  val p = from(Table<Person>())
  PersonWithUpper(p.name, free("UPPER(${p.name})")<String>())
}
//> SELECT p.name, UPPER(p.name) AS upperName FROM Person p

Note: free() is for single-value expressions only. To return multiple custom columns, use a data class with multiple free() calls, or combine free() values in a Pair/Triple. Do not attempt to return multiple columns from a single free() call.

Note: free() bypasses compile-time SQL validation. Use it sparingly and verify your SQL is correct for your target database.

Pagination and Distinct

In an sql.select statement you cannot use the distinct(), limit(), and offset() functions directly because they are called on SqlQuery<T> instances. Therefore they are not called inside sql.select { } blocks, they are chained after them.

Limit and Offset

After an sql.select block is complete you can use limit() and offset() to paginate results:

val q = sql {
  select { // or sql.select can go here but it is less efficient
    val p = from(Table<Person>())
    sortBy(p.name to Ord.Asc)
    p
  }.limit(10).offset(20)
}
//> SELECT p.id, p.name, p.age FROM Person p ORDER BY p.name ASC LIMIT 10 OFFSET 20

You can also use them directly on table-expressions:

val q = sql {
  Table<Person>().limit(10)
}
//> SELECT p.id, p.name, p.age FROM Person p LIMIT 10

val q = sql {
  Table<Person>().offset(20).limit(10)
}
//> SELECT p.id, p.name, p.age FROM Person p LIMIT 10 OFFSET 20

ExoQuery also supports Kotlin collection-style take() and drop() as aliases:

val q = sql {
  Table<Person>().drop(20).take(10)
}
//> SELECT p.id, p.name, p.age FROM Person p LIMIT 10 OFFSET 20

Distinct

After an sql.select block is complete you can use distinct() to remove duplicate rows: With sql.select:

val q = sql {
  select {
    val p = from(Table<Person>())
    val a = join(Table<Address>()) { a -> a.ownerId == p.id }
    a.city
  }.distinct()
}
//> SELECT DISTINCT a.city AS value FROM Person p INNER JOIN Address a ON a.owner_id = p.id

You can also use it directly on table-expressions:

val q = sql {
  Table<Person>().map { p -> p.name }.distinct()
}
//> SELECT DISTINCT p.name AS value FROM Person p

Common Mistake

These functions are not available inside sql.select { } blocks. The following will not compile:

// WRONG - will not compile
val q = sql.select {
  val p = from(Table<Person>())
  distinct()  // Error: Unresolved reference
  limit(10)   // Error: Unresolved reference
  p
}

This is intentional, allowing distinct() and limit(X) to be applied before sql.select block outputs (i.e. p) contradicts the ordering of statements that you would expect to see in an SQL query (e.g. SELECT DISTINCT ... LIMIT ...) which violates the spirit of the SQL paradigm.

Instead, wrap in sql { } and chain after:

// CORRECT
val q = sql {
  select { // or sql.select can go here but it is less efficient
    val p = from(Table<Person>())
    p
  }.distinct().limit(10)
}

Or use table expressions directly when you don't need joins/groupBy:

// CORRECT - simpler when no joins needed
val q = sql {
  Table<Person>().distinct().limit(10)
}