Free Blocks

Calling UDFs

In situations where you need to use a SQL UDF that is available directly on the database, or when you need to use custom SQL syntax that is not supported by ExoQuery, you can use a free block.

val q = sql {
  Table<Person>().filter { p -> free("mySpecialDatabaseUDF(${p.name})") == "Joe" }
}
//> SELECT p.id, p.name, p.age FROM Person p WHERE mySpecialDatabaseUDF(p.name) = 'Joe'

You can pass param-calls into the free-block as well.

val myRuntimeVar = ...
val q = sql {
  Table<Person>().filter { p -> p.name == free("mySpecialDatabaseUDF(${param(myRuntimeVar)})") }
}
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.name = mySpecialDatabaseUDF(?)

Enriching Queries

Free blocks are also useful for adding information before/after an entire query. For example:

val q = sql {
  free("${Table<Person>().filter { p -> p.name == "Joe" }} FOR UPDATE").asPure<SqlQuery<Person>>()
}
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.name = 'Joe' FOR UPDATE

This is technique is quite powerful when combined with SQL Fragment Functions to abstract out logic:

@SqlFragment
fun <T : Person> forUpdate(v: SqlQuery<T>) = sql {
    free("${v} FOR UPDATE").asPure<SqlQuery<T>>()
  }

val q = sql {
  forUpdate(Table<Person>().filter { p -> p.age > 21 })
}
//> (SELECT p.id, p.name, p.age FROM Person p WHERE p.age > 21) FOR UPDATE

Enriching Actions

Free blocks can even be used with Action (i.e. insert, update, delete) statements:

val q = sql {
  insert<Person> { setParams(Person(1, "Joe", "Bloggs", 111)) }
}
val qq = sql {
  free("SET IDENTITY_INSERT Person ON ${q} SET IDENTITY_INSERT Person OFF").asPure<SqlAction<Person, Long>>()
}
qq.build<SqlServerDialect>().runOn(ctx)
//> SET IDENTITY_INSERT Person ON  INSERT INTO Person (id, name, age, companyId) VALUES (?, ?, ?, ?) SET IDENTITY_INSERT Person OFF

free() Return Types by Context

The free() function behaves differently depending on where it's used:

Inside sql.select { } block

free(...).asPure<T>() returns T directly (not SqlExpression<T>):

val q = sql.select {
    val p = from(Table<Person>())
    val upper = free("UPPER(${p.name})").asPure<String>()  // upper is String
    groupBy(upper)  // Works directly, no .use needed
    Pair(upper, count())
}

Inside sql.expression { } block

free(...).asPure<T>() returns T which becomes part of the SqlExpression<T>:

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

// When used:
val month = monthOf(i.paidAt).use  // .use required to extract String

Inside @SqlFragment functions

Fragments returning SqlExpression<T> require .use at call sites:

@SqlFragment
fun coalesce(value: Long?, default: Long): SqlExpression<Long> = sql.expression {
    free("COALESCE(${value}, ${default})").asPure<Long>()
}

// Usage in sql.select:
val netAmount = grossAmount - coalesce(refund, 0L).use  // .use required

Quick Reference

Location	free().asPure<T>() returns	Need .use at call site?
sql.select { }	T	No
sql.expression { }	T (wrapped in SqlExpression)	Yes
@SqlFragment returning SqlExpression<T>	SqlExpression<T>	Yes
@SqlFragment returning SqlQuery<T>	SqlQuery<T>	No (use with from/join)