Why we used Quill in RealWorld project?

Introduction

In today's world, relational databases play a crucial role in most projects. To effectively communicate with them, the SQL language is commonly used, but this communication can lead to issues with generating multiple side effects, which is undesirable in functional languages. Therefore, in the Scala ecosystem, higher-level libraries are often used, such as Slick, Doobie, or Quill, which provide tools to address these problems.

In the realword template implementation using ZIO, we utilized Quill, which proved an excellent choice, significantly streamlining our database operations. This article will primarily delve into the benefits of leveraging this library for non-trivial type-safe SQL queries. The project's overarching requirement was to develop an application enabling users to create articles accompanied by comments and tags. Furthermore, users should be able to follow one another and like articles.

Complex queries

In this text, I would like to focus particularly on one complex query that we spent the most time on. It allowed us to list all the articles while incorporating filtering by author, favorite author, or tag, as well as pagination support. For each retrieved article, we needed to find information about the author, tags, and the number of people who liked it. Optionally, we also wanted to include information about the viewer’s preferences, such as whether they like the article and whether they follow the author.

As you can see, the description itself is already quite extensive. Implementing these requirements in pure SQL would make the code less readable, and it might be impossible to fit everything within a single query. In this situation, Quill proves to be valuable for composing statically-checked, modular queries, utilizing a syntax similar to for-comprehension in Scala, making the code more readable, even when dealing with large queries, which can be presented in a more accessible way.

Raw queries

The flexibility of Quill is evident in its capability to combine built-in structures with plain SQL, providing usefulness in various scenarios. One of the advantages of employing raw queries is the effortless integration they provide with new projects. In such a situation, one can use plain SQL, and Quill will handle the analysis and mapping of the database results to the appropriate Scala classes. It is particularly useful when someone wants to launch a project using this library quickly or when gradually migrating from a legacy project with existing raw queries. In realworld-tapir-zio, we wanted to showcase such usage. The article query was a great candidate, it combines a complex raw SELECT query with multiple joins and filters with additional Quill DSL extensions.

val articleRow: Quoted[Query[ArticleRow]] = quote {
 sql"""
          SELECT a.article_id, a.slug, a.title, a.description, a.body, a.created_at, a.updated_at, a.author_id
          FROM articles a
          LEFT JOIN users authors ON authors.user_id = a.author_id
          LEFT JOIN favorites_articles fa ON fa.article_id = a.article_id
          LEFT JOIN users fu ON fu.user_id = fa.profile_id
          LEFT JOIN tags_articles ta ON a.article_id = ta.article_id
          WHERE (${lift(tagFilter)} = '' OR ${lift(tagFilter)} = ta.tag)
             AND (${lift(favoritedFilter)} = '' 
             OR ${lift(favoritedFilter)} = fu.username)
             AND (${lift(authorFilter)} = ''
             OR ${lift(authorFilter)} = authors.username)
      GROUP BY a.slug, a.title, a.description, a.body, a.created_at, a.updated_at, a.author_id
        """
   .as[Query[ArticleRow]]
   .drop(lift(pagination.offset))
   .take(lift(pagination.limit))
   .sortBy(ar => ar.slug)
}

Another benefit of using raw queries is the ease of integration with a new project. In such a situation, one can use plain SQL, and Quill will handle the analysis and mapping of the database results to the appropriate Scala classes. It is particularly useful when someone wants to launch a project using this library quickly or when gradually migrating from a legacy project with existing raw queries. Therefore, having clean SQL queries is not an issue when creating a new project based on Quill.

Reusability

The ability to reuse code is a significant advantage. In our project, we had two instances where this was beneficial. Firstly, it was useful when dealing with optional fields. If those fields were filled, an additional function was called to expand the Quill query and include the relevant values. With plain SQL, we would have had to write two separate queries. It can be observed in the example below; the function buildArticleQueryWithFavoriteAndFollowing is called when the optional fields isFavorite and isFollowing are filled. It utilizes another function that generates an SQL query, thereby eliminating the need for code duplication.

private def buildArticleQueryWithFavoriteAndFollowing(arq: Quoted[Query[ArticleRow]], viewerId: Int) =
 quote {
   for {
     as <- buildArticleQuery(arq)
     isFavorite = queryUser
       .join(queryFavoriteArticle)
       .on((u, f) => u.userId == lift(viewerId) && (f.articleId ==            as.articleRow.articleId) && (f.profileId == u.userId))
       .map(_ => 1)
       .nonEmpty
     isFollowing = queryFollower
       .filter(f => (f.userId == as.profileRow.userId) && (f.followerId == lift(viewerId)))
       .map(_ => 1)
       .nonEmpty
   } yield ArticleQueryBuildSupport(
     articleRow = as.articleRow,
     profileRow = as.profileRow,
     tagsOpt = as.tagsOpt,
     favoritedCountOpt = as.favoritedCountOpt,
     isFavorite = isFavorite,
     isFollowing = isFollowing
   )
 }

In the second case, we could easily replace the fragment responsible for filtering articles in order to adjust it to the requirements. The fix only replaced that specific fragment, while the rest of the code could be used without any changes.

def listArticlesByFollowedUsers(
   pagination: Pagination,
   viewerId: Int
): IO[SQLException, List[Article]] = {
 val articleRow: Quoted[Query[ArticleRow]] = quote {
   sql"""
            SELECT DISTINCT * FROM articles a
            WHERE a.author_id IN (SELECT f.user_id FROM followers f
                                  WHERE f.follower_id = ${lift(viewerId)})
          """
     .as[Query[ArticleRow]]
     .drop(lift(pagination.offset))
     .take(lift(pagination.limit))
     .sortBy(ar => ar.slug)
 }

 val articleQuery = buildArticleQueryWithFavoriteAndFollowing(articleRow, viewerId)

 run(articleQuery).map(_.map(article))
}

By using Quill, we were able to effectively utilize existing code in different contexts, which accelerated our work and made it easier to customize queries to our needs. Splitting the query into smaller parts also facilitates testing and improves code readability.

Simple mapping

Quill also provides an easy mapping of query results to user-defined classes. You simply need to define the appropriate class, and Quill will automatically assign the results to the corresponding fields of that class. This solution is very readable and makes working with query results easier when you want to represent them as specific classes.

case class ArticleFavoriteRow(profileId: Int, articleId: Int)
case class ArticleTagRow(tag: String, articleId: Int)
case class ArticleRow(
   articleId: Int,
   slug: String,
   title: String,
   description: String,
   body: String,
   createdAt: Instant,
   updatedAt: Instant,
   authorId: Int
)

There are no issues with column ordering.

Another interesting fact is that when using raw SQL in Quill, there is no need to be concerned about the field order in the SELECT query. For example, selecting "a.article_id" first and then "a.description", or vice versa, does not impact the compatibility of the results. The obtained results are correctly mapped to the fields in the class (by name), eliminating the need to care about the exact order of fields. This convenient feature puts a programmer at ease and provides greater flexibility in constructing queries.

SELECT a.article_id, a.slug, a.title, a.description, a.body, a.created_at, a.updated_at, a.author_id FROM articles a

But why not Slick?

While there are other notable libraries like Slick for creating DSLs in Scala for query building, Quill stands out as the preferred choice for our project. One of the key advantages of Quill is its seamless data mapping to user-defined case classes, eliminating the need for special data types that can be hard to develop and maintain in the case of Slick. Additionally, Quill's compatibility with Scala 3 sets it apart from Slick, which lacks this support. As a result, Quill effectively fills the void left by Slick in Scala 3, offering a multitude of benefits and streamlining DSL query creation.

Summary

In summary, based on our experiences, we believe that Quill is the best choice for our case. It provided convenient and type-safe query typing, and its wide range of functionality made coding easy. We highly recommend it to anyone who would like to give it a try.