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 ctx.run 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 ctx.run 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 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.
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.
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.
See also
Authors
