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 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

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

Authors

Mateusz Bilski

Latest Blogposts

28.03.2024 / By  Matylda Kamińska

Scalendar April 2024

scala conferences april 2024

Event-driven Newsletter Another month full of packed events, not only around Scala conferences in April 2024 but also Frontend Development, and Software Architecture—all set to give you a treasure trove of learning and networking opportunities. There’re online and real-world events that you can join in order to meet colleagues and experts from all over the […]

14.03.2024 / By  Dawid Jóźwiak

Implementing cloud VPN solution using AWS, Linux and WireGuard

Implementing cloud VPN solution using AWS, Linux and WireGuard

What is a VPN, and why is it important? A Virtual Private Network, or VPN in short, is a tunnel which handles all the internet data sent and received between Point A (typically an end-user) and Point B (application, server, or another end-user). This is done with security and privacy in mind, because it effectively […]

07.03.2024 / By  Bartosz Puszczyk

Building application with AI: from concept to prototype

Blogpost About Building an application with the power of AI.

Introduction – Artificial Intelligence in Application Development When a few years ago the technological world was taken over by the blockchain trend, I must admit that I didn’t hop on that train. I couldn’t see the real value that this technology could bring to someone designing application interfaces. However, when the general public got to […]

software product development

Need a successful project?

Estimate project