Compile-time Queries with Quill

Compile-time Queries with Quill

Compile-time Queries with Quill

Scala is all about type-safety and making the compiler work for you. But what if we need to use SQL which is not a part of Scala? The compiler is not able to validate and type check raw queries. The solution for that problem is Domain Specific Language (DSL). We already have Slick that provides DSL for SQL and allows to work with a database just like with Scala collections.

However, Quill is going even further and supports compile-time query generation and validation. In this post I take a closer look at Quill and show an example application.

What is Quill?

Quill is a library that provides a Quoted Domain Specific Language (QDSL) for Scala which simplifies working with relational databases. It is an alternative for Slick.

The advantage of Quill is support for Compile-Time Language Integrated Queries which allows for database access similar to Scala collections and enables compile-time generated queries. This feature minimizes the runtime overhead of queries. Moreover, it allows query validation during compile-time.

There are other things that make Quill unique among Scala database libraries. First of all, the library is designed to support multiple target languages (at the moment besides SQL it supports Cassandra Query Language). What’s more, the boilerplate is reduced to the minimum. The database schema is mapped using simple case classes.

Furthermore, it provides fully asynchronous non-blocking database access (using mysql-async). The client is not just an asynchronous wrapper on top of JDBC blocking client. It is based on netty.

The library is inspired by Philip Wadler’s talk A practical theory of language-integrated query. The development started a year ago, the current version is 0.8.0. The author of most of the code is Flavio W. Brasil who also built other libraries like clump and activate.

Getting started

Here I show how to create a simple application using Quill. MySQL will be used as a database. First, add this dependency to build.sbt

In order to configure asynchronous SQL client add the following config to the application.conf. Make sure to provide a valid user and database name.

Now, let’s create two tables: users and devices. A user may have multiple devices.

After that, it’s time to create a context which represents the database and provides an execution interface for queries.

Here I am using MySQL asynchronous client and snake naming strategy for translating table and column names to SQL. There are other context types and naming strategies. See quill contexts for a reference.

In Quill we just need a simple case class to represent a table in Scala.

Although Quill is boilerplate free there is a way to provide explicit names for identities or generated keys using schema function. Let’s define id columns as auto-generated values. An important note is that it currently accepts only Longvalues.

In Quill queries are written as quotations using the quote method. The quotation is a block of code that at compile time is translated to an internal Abstract Syntax Tree (AST). The quotation can contain only supported operations (for example recursion is not available). When quotation as internal AST is passed to method it is translated to the target database language at compile time. The simplest quotation is just quote(query[User]) which generates

In the following snippet, you can see a quotation that finds a user by id and joins his devices. The runtime value id is lifted to a quotation through the method lift. Notice that there is no explicit type given to the quotation. Otherwise, the type refinement is lost and Quill falls back to runtime query generation.

The quotations can be run using database context. The returns Future[A] as the async client is used.

It is possible to generate and run queries for insertions, updates and removals.

The feature that validates queries against the database at compile time (known as query probing), is disabled by default. To enable it, add QueryProbing trait to the context definition. However, this feature is still considered as experimental.

With this feature turned on you will get compile errors if query probing fails, for example if a column does not exist. It requires a database instance to be running. In addition, Quill prints all generates SQL queries during compile-time.

You can find the sources of an example play application that use Quill for database access at mbilski/play-quill-async. See for complete Quill reference and more examples.

How does query generation work?

Quill is using whitebox macros to generate and validate queries during the compile-time. Consider the quotation from the previous example.

It is transformed to the following internal AST tree.

And then normalization rules are applied, the AST is reduced and transformed to the SQL query.

For comprehensive explanation I refer you to A Practical Theory of Language-Integrated Query and Everything Old Is New Again: Quoted Domain-Specific Languages white papers.

Quill vs Slick

Slick is a mature library for database access created and maintained by Lightbend. Because of its popularity, I think it is a good point of reference. Both Slick and Quill represent database rows as case classes without nested data and provide a type-safe query DSL.

Slick requires explicit type definition which produces a lot of boilerplate code. In Quill mapping is done using simple case classes. It is possible to select a naming strategy for table names and columns.

Slick generates SQL queries at runtime. Slick’s Compiled mechanism can be used to cache the generated SQL query for future usages. Quill generates queries at compile-time unless it is a dynamic query.

Quill offers fully asynchronous non-blocking database client. In Slick it is an asynchronous wrapper on top of JDBC with a separate thread pool.

If a database specific feature is missing in Slick the only way around that is to write raw SQL and it is not type safe. In Quill there is an infix mechanism which allows extending base DSL.

Quill provides Quoted DSL, in compare with Slick’s Embedded DSL. See QDSL or QDSL versus EDSL for more details.


Quill is a new and promising library for database access in Scala. It is built on solid research in the scope of Query Language Integrated Queries. Although the query generation works great, the most innovative feature (compile-time query validation) is considered as experimental. The library is based on whitebox macros which are supposed to be deleted in the future version of Scala.

The open question is the authors plan regarding that. I am looking forward to the stable release and refined query probing feature.


Do you like this post? Want to stay updated? Follow us on Twitter or subscribe to our Feed.

See also

Download e-book:

Scalac Case Study Book

Download now


Mateusz Bilski

Latest Blogposts

08.05.2024 / By  Scalac Team

Figma to React: Design to Code Conversion

From Figma to React

From Figma to React: Introduction Recently, within our team, a discussion emerged about converting designs made in Figma into React code. This conversation was sparked due to the constant evolution of tools available for developers. While the possibility of converting design to code has existed for some time, its implementation has been, frankly speaking, suboptimal. […]

29.04.2024 / By  Matylda Kamińska

Scalendar May 2024

scalendar may 2024

Event-driven Newsletter Welcome to our May 2024 edition of Scalendar! As we move into a bustling spring, this issue brings you a compilation of the most anticipated frontend and software architecture events around the globe. With a particular focus on Scala conferences in May 2024, our newsletter serves as your guide to keep you updated […]

23.04.2024 / By  Bartosz Budnik

Kalix tutorial: Building invoice application

Kalix app building.

Scala is well-known for its great functional scala libraries which enable the building of complex applications designed for streaming data or providing reliable solutions with effect systems. However, there are not that many solutions which we could call frameworks to provide every necessary tool and out-of-the box integrations with databases, message brokers, etc. In 2022, Kalix was […]

software product development

Need a successful project?

Estimate project