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.

https://gist.github.com/anonymous/27a2a66115883d8c112a069c342a1a6d

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

https://gist.github.com/anonymous/6e76a6e046e739dff5564967121a8c7f

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

https://gist.github.com/anonymous/2dea7d8a3629839d283676a68a29ec30

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.

https://gist.github.com/anonymous/ad15bc595d4b599fa46f9e5f9aa91126

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.

https://gist.github.com/anonymous/c9cd93dc9cbe1a80ca753e276c0364ed

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 ctx.run method it is translated to the target database language at compile time. The simplest quotation is just quote(query[User]) which generates

https://gist.github.com/anonymous/004f1d3bcc6e5507744369998bea3f73

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.

https://gist.github.com/anonymous/0252d9c81860a79af6a20ddccad09624

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

https://gist.github.com/anonymous/7b2c3b83201cb051b4baab621dab64cf

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

https://gist.github.com/anonymous/ba14e5df3d2c10326e22f4214acd7954

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.

https://gist.github.com/anonymous/d1c8f725af3ec882a7313b635e41f97d

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 getquill.io 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.

https://gist.github.com/anonymous/53f64df29a7131fb93233739a3faa841

It is transformed to the following internal AST tree.

https://gist.github.com/mbilski/50f640de13a2b5e9a02a63dd79d5bd19

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

https://gist.github.com/anonymous/30771719bd7c2f60a8b231508fdd6fde

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.

https://gist.github.com/anonymous/ea0ec05cf86cd39dad928c5a1dccd37e

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.

https://gist.github.com/anonymous/fa6ed700648ace0aac1942b43b918e34

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.

https://gist.github.com/anonymous/64da1b8df3309db08811c113736353c5

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

Summary

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.

Links

• https://getquill.io/
• https://github.com/mbilski/play-quill-async
• https://www.infoq.com/presentations/theory-language-integrated-query

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

https://gist.github.com/anonymous/d16173b039cbf2290da41feb1e313644

See also

• The Rough Experience with Slick
• A Quick Overview of Slick 3.0
• Creating Domain Specific Languages with Scala – Part 1
• Making ZIO, Akka and Slick play together nicely
• Async/await – how it works
• Using Akka HTTP with Sangria as GraphQL backend