Over a million developers have joined DZone.

SELECT This! Scala Data Access Library Review (Part 1)

DZone's Guide to

SELECT This! Scala Data Access Library Review (Part 1)

Accessing data from Scala? Isn't everybody? Find out the best ways to do it here.

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

At Threat Stack, we love databases, and as we started to expand our Scala code base, we quickly realized we wanted to standardize on a way to talk to the RDBMS within our Scala projects. As we explored the realm, we came across four projects that seemed to have the most potential: ScalikeJDBC, Quill, Jooq, and Doobie. We had already been playing with ScalikeJDBC for awhile, but these other libraries offered interesting features, so we began to investigate.

In this post, we'll take a look at some of the pros and cons of ScalikeJDBC and Quill. In a followup, we'll discuss Jooq and Doobie.

Our Requirements 

We were looking for a library that was going to be easy to pick up in the simplest scenarios and that would also be amenable to more complex queries and allow us to leave some heavier lifting, like aggregation and filter, in the database layer. All of these libraries are actively maintained at present, and have community support. We focused on the ease of use of the read APIs given that our insert and update cases are fairly straightforward.


ScalikeJDBC is a nice Scala wrapper around the JDBC library. It can communicate with MySQL and PostgreSQL as well as H2 and HSQLDB. It has a great DSL that is very natural to folks who’ve used SQL before. Writing simple queries is very easy:

case class Company(id: Long, name: String, employees: List[Employee])
case class Employee(id: Long, companyId: Long, name: String, age: Int)

val (c, e) = (Company.syntax(“c”), Employee.syntax(“e”))
withSQL {
    select(c.name, e.name)
              .from(Company as c)
              .innerJoin(Employee as e).on(e.id, e.companyId)
              .where.gt(e.id, 100)

As you can see, this is a very simple query, but we can easily read what’s happening, and future developers will be able to contribute and develop on top of this with minimal effort, if they have familiarity with SQL. Mapping the data back into usable Scala objects is simple as well:

case class Company(id: Long, name: String, employees: List[Employee])
case class Employee(id: Long, companyId: Long, name: String, age: Int)

val (c, e) = (Company.syntax(“c”), Employee.syntax(“e”))

withSQL {
              .from(Company as c)
              .innerJoin(Employee as e).on(e.id, e.companyId)
              .where.gt(c.id, 100)
      .map { (company, employee) => company.copy(employee = employee }

Now you have a nice list of objects with a one-to-many relationship. We’re able to use the SQL interpolation to inline actual SQL into this as well when we find that the DSL gets too verbose or hard to read:

select(sqls”””c.id, count(c.name)”””).from(Company as c)...

In fact, if you didn't want to use the DSL at all, that is also available to you. The following could be executed to run exactly like the query above using the DSL. It is safe from SQL injection, and you can use string interpolation as you would normally to insert variable data:

val minId = 100;
sql”select id   
    from company as c   
    inner join employee as e 
      on c.id = e.company_id 
      c.id > ${minId}”

ScalikeJDBC also has some nice features to debug your queries, with a built-in integration with ScalaTest and SLF4J hooks that give you very verbose logging, including outputting your queries.

While all of this works well, we have run into issues with ScalikeJDBC where we find ourselves needing to write mixed DSL and SQL string queries to produce better queries. Additionally, ScalikeJDBC won’t break if you pass a parameter of the wrong type at compile time, but rather at runtime, which is not ideal:

val id = “Not a Real Id”
case class Company(id: Long, name: String, employees: List[Employee])

val company = Company.syntax(“c”)
withSQL {
select.from(Company as c).where.eq(c.id, id)

The ResultSet object also has some confusing conventions. It is not always clear why your code isn’t working, and without a lot of experience with ScalikeJDBC, it may be difficult to debug or see your error:

case class Company(id: Long, name: String, value: Int, Option[List[Employees]])

val company = Company.syntax(“c”)
withSQL {
select(c.result.id).from(Company as c).where.eq(c.id, 1)
}.map(rs => {rs.get(c.resultName.id)}).toList.apply

Here this can be confusing because a.result.name needs to be used in the select context, but the resultName string needs to be used when traversing the resultset. In addition, these SQLSyntax objects are actually dynamics, so you lose type safety.

Finally, supporting more advanced types like custom types or even things like PostgreSQL jsonb fields can get a little arduous as well:

case class TableA(id: Long, data: Option[Data])
case class Data(name: String, value: Int, other: Boolean)


val a = TableA.syntax
withSQL {
.from(TableA as a)
.where.eq(a.id, id)
}.map(rs => TableA(

While this isn’t the worst thing, it can become cumbersome to read and deal with.


Quill was another library that we looked into quite a bit. One reason we looked into it is the fact that it sits on top of SQL and CQL (Cassandra). As a company that uses each of these datastores, it was highly appealing to be able to use one library to talk to both. One of the first things you see when you go to Quill’s website is very clean and verbose documentation. They have examples and very clear explanations as well as links to outside resources. Quill has definitely picked up some steam in the community recently.

One feature I really liked is the fact that the data model is completely defined by your case classes. This reduces the amount of boilerplate code you would use to map your schema to your object representation when you are working our simple CRUD operations. This means you are dealing with the objects, and then passing those to Quill in order to run your queries. This leads to a syntax that is far from SQL. When you want to select data likened to a simple select query, it will look something like this:

case class Company(id: Int, name: String)

val q = quote {
  query[Company].filter(p => p.id > 1000)

As you can see, this does not look like a database query, but more like just some other function program. The niceness here is that you get to leverage Scala syntax and typing. What helps offset this less conventional (for SQL libraries) syntax is that at compile time, Quill will transform this into a query. With the right IDE plugins, you can very quickly see what SQL is generated from your Quill Quotation and gain some much-needed readability.

Now let’s look at a more complex case of joining a couple of tables:

case class Company(id: Int, name: String)
case class Employee(fkCompanyId: Int, name: String, age: Int)

val q = quote {
  for {    c <- query[Company] if (c.id == 1000)    e <- query[Comapny] if (c.comapnyId == p.id} yield { (c.name, e.phone)}

This is a little more difficult to work with. While you can appreciate that raw data set that you can now form into your desired data set, getting back a list of tuples of objects isn’t always ideal. Quill becomes a little messy when it comes to more complex SQL queries.

What’s more, you give up a lot of control in terms of SQL performance when you use Quill. You are left to trust that the SQL Quill generates is the most performant, but that may not always be the case. However, you do get to quickly see what query is being generated with the right IDE support which can help you tune the performance of the query. Also the for-comprehension is a logical way to get the data in its rawest form, and it stays nicely within the Scala idioms, but becomes very difficult to read as the number of tables and conditions increases.

We haven’t evaluated the Cassandra library much at this point, but it seems that this may be a lot more useful for CQL. The complexity of schema and the types of queries you are doing are likely better suited to this type of framework. That being said, Quill seems to be very useful in the simple CRUD scenario and for CQL. It also appeals to folks who would much rather manipulate their data within the Scala syntax rather than a SQL-like DSL.

Stay tuned for Part 2 of this topic, where we discuss the Doobie and Jooq libraries.

Create flexible schemas using dynamic columns for semi-structured data. Learn how.

scala ,data access

Published at DZone with permission of Lucas DuBois, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}