SQL Blocks

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.)

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) } }