IMPORTANT: This is the documentation for the latest SNAPSHOT version. Please refer to the website at http://getquill.io for the lastest release's documentation.

Compile-time Language Integrated Query for Scala

Quill provides a Quoted Domain Specific Language (QDSL) to express queries in Scala and execute them in a target language. The library's core is designed to support multiple target languages, currently featuring specializations for Structured Query Language (SQL) and Cassandra Query Language (CQL).

Quoted DSL: Queries are defined inside a quote block. Quill parses each quoted block of code (quotation) at compile time and translates them to an internal Abstract Syntax Tree (AST)

Compile-time query generation: The ctx.run call reads the quotation's AST and translates it to the target language at compile time, emitting the query string as a compilation message. As the query string is known at compile time, the runtime overhead is very low and similar to using the database driver directly.

Compile-time query validation: If configured, the query is verified against the database at compile time and the compilation fails if it is not valid. The query validation does not alter the database state.

Note: The GIF example uses Eclipse, which shows compilation messages to the user.

Quotation

Introduction

The QDSL allows the user to write plain Scala code, leveraging scala's syntax and type system. Quotations are created using the quote method and can contain any excerpt of code that uses supported operations. To create quotations, first create a context instance. Please see the context section for more details on the different context available.

For this documentation, a special type of context that acts as a mirror is used:

Compile-time quotations

Quotations are both compile-time and runtime values. Quill uses a type refinement to store the quotation's AST as an annotation available at compile-time and the q.ast method exposes the AST as runtime value.

It is important to avoid giving explicit types to quotations when possible. For instance, this quotation can't be read at compile-time as the type refinement is lost:

(Note: If your use case involves lots and lots of joins, both inner and outer. Skip right to the flat-joins section!)

applicative joins

Applicative joins are useful for joining two tables together, they are straightforward to understand, and typically look good on one line. Quill supports inner, left-outer, right-outer, and full-outer (i.e. cross) applicative joins.

No worries though, implicit joins and flat joins have your other use-cases covered!

implicit joins

Quill's implicit joins use a monadic syntax making them pleasant to use for joining many tables together. They look a lot like Scala collections when used in for-comprehensions making them familiar to a typical Scala developer. What's the catch? They can only do inner-joins.

This happens because a explicit join typically cannot be done after an implicit join in the same query.

A good guideline is in any query or subquery, choose one of the following:

Use flat-joins + applicative joins or

Use implicit joins

Also, note that not all Option operations are available on outer-joined tables (i.e. tables wrapped in an Option object), only a specific subset. This is mostly due to the inherent limitations of SQL itself. For more information, see the 'Optional Tables' section.

Optionals / Nullable Fields

Note that the behavior of Optionals has recently changed to include stricter null-checks. See the orNull / getOrNull section for more details.

Option objects are used to encode nullable fields. Say you have the following schema:

Optionals cannot be compared via the == operator due to differences in database handling. See .exists as to how to do that.

In many cases, Quill tries to rely on the null-fallthrough behavior that is ANSI standard:

null == null := false

null == [true | false] := false

This allows the generated SQL for most optional operations to be simple. For example, the expression Option[String].map(v => v + "foo") can be expressed as the SQL v || 'foo' as opposed to CASE IF (v is not null) v || 'foo' ELSE null END so long as the concatenation operator || "falls-through" and returns null when the input is null. This is not true of all databases (e.g. Oracle), forcing Quill to return the longer expression with explicit null-checking. Also, if there are conditionals inside of an Option operation (e.g. o.map(v => if (v == "x") "y" else "z")) this creates SQL with case statements, which will never fall-through when the input value is null. This forces Quill to explicitly null-check such statements in every SQL dialect.

Let's go through the typical operations of optionals.

isDefined / isEmpty

The isDefined method is generally a good way to null-check a nullable field:

Note however that as you can see from the examples above, the exists method does not cause the generated SQL to do an explicit null-check in order to express the False case. This is because Quill relies on the typical database behavior of immediately falsifying a statement that has null on one side of the equation.

forall

Use this method in boolean conditions that should succeed in the null case.

orNull / getOrNull

The orNull method can be used to convert an Option-enclosed row back into a regular row. Since Option[T].orNull does not work for primitive types (e.g. Int, Double, etc...), you can use the getOrNull method inside of quoted blocks to do the same thing.

Note that since the presence of null columns can cause queries to break in some data sources (e.g. Spark), so use this operation very carefully.

In certain situations, you may wish pretend that a nullable-field is not actually nullable and perform regular operations (e.g. arithmetic, concatenation, etc...) on the field. You can use a combination of Option.apply and orNull (or getOrNull where needed) in order to do this.

In all other situations, since Quill strictly checks nullable values, and case.. if conditionals will work correctly in all Optional constructs. However, since they may introduce behavior changes in your codebase, the following warning has been introduced:

Conditionals inside of Option.[map | flatMap | exists | forall] will create a CASE statement in order to properly null-check the sub-query (...)

val q = quote {
query[Person].map(p => p.name.map(n => if (n == "Joe") "foo" else "bar").getOrElse("baz"))
}
// Information:(16, 15) Conditionals inside of Option.map will create a `CASE` statement in order to properly null-check the sub-query: `p.name.map((n) => if(n == "Joe") "foo" else "bar")`.
// Expressions like Option(if (v == "foo") else "bar").getOrElse("baz") will now work correctly, but expressions that relied on the broken behavior (where "bar" would be returned instead) need to be modified (see the "orNull / getOrNull" section of the documentation of more detail).
ctx.run(a)
// Used to be this:
// SELECT CASE WHEN CASE WHEN p.name = 'Joe' THEN 'foo' ELSE 'bar' END IS NOT NULL THEN CASE WHEN p.name = 'Joe' THEN 'foo' ELSE 'bar' END ELSE 'baz' END FROM Person p
// Now is this:
// SELECT CASE WHEN p.name IS NOT NULL AND CASE WHEN p.name = 'Joe' THEN 'foo' ELSE 'bar' END IS NOT NULL THEN CASE WHEN p.name = 'Joe' THEN 'foo' ELSE 'bar' END ELSE 'baz' END FROM Person p

Optional Tables

As we have seen in the examples above, only the map and flatMap methods are available on outer-joined tables (i.e. tables wrapped in a Option object).

Since you cannot use Option[Table].isDefined, if you want to null-check a whole table (e.g. if a left-join was not matched), you have to map to a specific field on which you can do the null-check.

Any custom logic in a constructor/apply-method of a Ad-Hoc case class will not be invoked when it is 'constructed' inside a quotation. To construct an Ad-Hoc case class with custom logic inside a quotation, you can use a quoted method.

Query probing

Query probing validates queries against the database at compile time, failing the compilation if it is not valid. The query validation does not alter the database state.

This feature is disabled by default. To enable it, mix the QueryProbing trait to the database configuration:

object myContext extends YourContextType with QueryProbing

The context must be created in a separate compilation unit in order to be loaded at compile time. Please use this guide that explains how to create a separate compilation unit for macros, that also serves to the purpose of defining a query-probing-capable context. context could be used instead of macros as the name of the separate compilation unit.

The configurations correspondent to the config key must be available at compile time. You can achieve it by adding this line to your project settings:

The translate method is available in every Quill context as well as the Cassandra and OrientDB contexts, the latter two however do not support Insert and Batch Insert query printing.

IO Monad

Quill provides an IO monad that allows the user to express multiple computations and execute them separately. This mechanism is also known as a free monad, which provides a way of expressing computations as referentially-transparent values and isolates the unsafe IO operations into a single operation. For instance:

User-Defined Types

To encode UDT and bind it into the query (insert/update queries), context needs to retrieve UDT metadata from cluster object. By default, context looks for UDT within currently logged keyspace, but it's also possible to specify concrete keyspace with udtMeta:

implicitvalnameMeta= udtMeta[Name]("keyspace2.my_name")

When keyspace is not set in udtMeta then the currently logged is used.

Since it's possible to create context without specifying keyspace, e.g. keyspace parameter is null and session is not bound to any keyspace, UDT metadata is being resolved among all cluster.

Dynamic queries

Quill's default operation mode is compile-time, but there are queries that have their structure defined only at runtime. Quill automatically falls back to runtime normalization and query generation if the query's structure is not static. Example:

Dynamic query API

Addionally, Quill provides a separate query API to facilitate the creation of dynamic queries. This API allows users to easily manipulate quoted values instead of working only with quoted transformations.

Important: A few of the dynamic query methods accept runtime string values. It's important to keep in mind that these methods could be a vector for SQL injection.

Let's use the filter transformation as an example. In the regular API, this method has no implementation since it's an abstract member of a trait:

def filter(f: T => Boolean): EntityQuery[T]

In the dynamic API, filter is has a different signature and a body that is executed at runtime:

// actions use `set`
dynamicQuery[Person].update(set(_.name, quote("John")))
// or `setValue` if the value is not quoted
dynamicQuery[Person].insert(setValue(_.name, "John"))
// or `setOpt` that will be applied only the option is defined
dynamicQuery[Person].insert(setOpt(_.name, Some("John")))
// it's also possible to use a runtime string value as the column name
dynamicQuery[Person].update(set("name", quote("John")))
// to insert or update a case class instance, use `insertValue`/`updateValue`valp=Person(0, "John", 21)
dynamicQuery[Person].insertValue(p)
dynamicQuery[Person].updateValue(p)

Extending quill

Infix

Infix is a very flexible mechanism to use non-supported features without having to use plain queries in the target language. It allows insertion of arbitrary strings within quotations.

For instance, quill doesn't support the FOR UPDATE SQL feature. It can still be used through infix and implicit classes:

Note that in this case the result query is nested. It's required since quill is not aware of a query tree and cannot safely unnest it. This is different to the example above because infix starts with the query infix"$q... where its tree is already compiled

Custom encoding

Quill uses Encoders to encode query inputs and Decoders to read values returned by queries. The library provides a few built-in encodings and two mechanisms to define custom encodings: mapped encoding and raw encoding.

Mapped Encoding

If the correspondent database type is already supported, use MappedEncoding. In this example, String is already supported by Quill and the UUID encoding from/to String is defined through mapped encoding:

Insert meta

InsertMeta customizes the expansion of case classes for insert actions (query[Person].insert(p)). By default, all columns are expanded and through an implicit InsertMeta, it's possible to exclude columns from the expansion:

Note that the parameter of insertMeta is called exclude, but it isn't possible to use named parameters for macro invocations.

Update meta

UpdateMeta customizes the expansion of case classes for update actions (query[Person].update(p)). By default, all columns are expanded, and through an implicit UpdateMeta, it's possible to exclude columns from the expansion:

Note that the parameter of updateMeta is called exclude, but it isn't possible to use named parameters for macro invocations.

Query meta

This kind of meta instance customizes the expansion of query types and extraction of the final value. For instance, it's possible to use this feature to normalize values before reading them from the database:

The query meta definition is open and allows the user to even join values from other tables before reading the final value. This kind of usage is not encouraged.

Contexts

Contexts represent the database and provide an execution interface for queries.

Mirror context

Quill provides mirror context for test purposes. Instead of running the query, mirror context return a structure with the information that would be used to run the query. There are three mirror context instances:

io.getquill.MirrorContext: Mirrors the quotation AST

io.getquill.SqlMirrorContext: Mirrors the SQL query

io.getquill.CassandraMirrorContext: Mirrors the CQL query

Dependent contexts

The context instance provides all methods and types to interact with quotations and the database. Depending on how the context import happens, Scala won't be able to infer that the types are compatible.

Modular Contexts

Another simple way to modularize Quill code is by extending Context as a self-type and applying mixins. Using this strategy, it is possible to create functionality that is fully portable across databases and even different types of databases (e.g. creating common queries for both Postgres and Spark).

Note that the run method returns a Dataset transformed by the Quill query using the SQL engine.

Additionally, note that the queries printed from run(myQuery) during compile time escape question marks via a backslash them in order to be able to substitute liftings properly. They are then returned back to their original form before running.

SQL Server (quill-jdbc)

sbt dependencies

context definition

lazyvalctx=newSqlServerJdbcContext(SnakeCase, "ctx")

Oracle (quill-jdbc)

Quill supports Oracle version 12c and up although due to licensing restrictions, version 18c XE is used for testing.

Note that the latest Oracle JDBC drivers are not publicly available. In order to get them, you will need to connect to Oracle's private maven repository as instructed here. Unfortunately, this procedure currently does not work for SBT. There are various workarounds available for this situation here.

quill-jdbc-monix

The quill-jdbc-monix module integrates the Monix asynchronous programming framework with Quill, supporting all of the database vendors of the quill-jdbc module. The Quill Monix contexts encapsulate JDBC Queries and Actions into Monix Tasks and also include support for streaming queries via Observable.

streaming

transactions

The MonixJdbcContext provides support for transactions by storing the connection into a Monix Local. This process is designed to be completely transparent to the user. As with the other contexts, if an exception is thrown anywhere inside a task or sub-task within a transaction block, the entire block will be rolled back by the database.

runners

Use a Runner object to create the different MonixJdbcContexts. The Runner does the actual wrapping of JDBC calls into Monix Tasks.

importmonix.execution.Schedulerimportio.getquill.context.monix.Runner// You can use the default Runner when constructing a Monix jdbc contexts. // The resulting tasks will be wrapped with whatever Scheduler is // defined when you do task.syncRunUnsafe(), typically a global implicit.lazyvalctx=newMysqlMonixJdbcContext(SnakeCase, "ctx", Runner.default)
// However...// Monix strongly suggests that you use a separate thread pool for database IO // operations. `Runner` provides a convenience method in order to do this.lazyvalctx=newMysqlMonixJdbcContext(SnakeCase, "ctx", Runner.using(Scheduler.io()))

Oracle (quill-jdbc-monix)

Quill supports Oracle version 12c and up although due to licensing restrictions, version 18c XE is used for testing.

Note that the latest Oracle JDBC drivers are not publicly available. In order to get them, you will need to connect to Oracle's private maven repository as instructed here. Unfortunately, this procedure currently does not work for SBT. There are various workarounds available for this situation here.

Depending on how the main execution context is imported, it is possible to produce an ambigous implicit resolution. A way to solve this problem is shadowing the multiple implicits by using the same name:

quill-cassandra

sbt dependencies

synchronous context

lazyvalctx=newCassandraSyncContext(SnakeCase, "ctx")

asynchronous context

lazyvalctx=newCassandraAsyncContext(SnakeCase, "ctx")

The configurations are set using runtime reflection on the Cluster.builder instance. It is possible to set nested structures like queryOptions.consistencyLevel, use enum values like LOCAL_QUORUM, and set multiple parameters like in credentials.

stream context

OrientDB Contexts

sbt dependencies

synchronous context

lazyvalctx=newOrientDBSyncContext(SnakeCase, "ctx")

The configurations are set using OPartitionedDatabasePool which creates a pool of DB connections from which an instance of connection can be acquired. It is possible to set DB credentials using the parameter called username and password.

application.properties

Logging

Compile-time

To disable logging of queries during compilation use quill.macro.log option:

sbt -Dquill.macro.log=false

Runtime

Quill uses SLF4J for logging. Each context logs queries which are currently executed. It also logs the list of parameters which are bound into prepared statement if any. To enable that use quill.binds.log option:

java -Dquill.binds.log=true -jar myapp.jar

Additional resources

Templates

In order to quickly start with Quill, we have setup some template projects: