My experience using JOOQ with Scala and Postgres

I've recently picked up JOOQ for building complex queries in Book of Revenue. I previously constructed raw SQLs using Slick. Slick is nice enough to support binding values and concatenating portions of SQL safely.

Still, writing raw SQLs is friable. Reusing a subquery of SQLs in multiple queries makes the code hideous. JOOQ solves this. I can put a subquery in a variable and reuse it elsewhere. It is relatively clean.

The biggest improvement actually comes from the SELECT * EXCEPT (...) support. Postgres doesn't support this in their SQL, but JOOQ simulates it. This capability saves tons of code.

Please note that the experience might be specific to Postgres because that's the database I'm using.

While JOOQ is excellent overall, four specific pain points stand out:

1. scala.Long doesn't work with JOOQ

JOOQ actually uses java.lang.Number and its subclasses e.g. java.lang.Long. But scala.Long doesn't inherit from java.lang.Number.

Therefore, a simple SQL portion like when(table.field("some_field") === 0, 0) doesn't work. We have to change to when[java.lang.Long]( ... ) to make it work. For me, this ends up with specifying a lot of java.lang.Long here and there.

2. Using Scala collections will compile but generate an incorrect SQL

This impacts the column IN (value1, value2, ...). For example, if you do table.field("some_field").in(listOfThings), it'll render the SQL some_field IN (Seq(...)). Therefore, you must do table.field("some_field").in(listOfThings.asJava) instead.

3. It doesn't dependency graph and specific WITH automatically

Since JOOQ doesn't do that, many of my queries have to invoke WITH first like below:

name("subquery").as(
  `with`(previousQuery)
    .select(asterisk())
    .from(previousQuery)
)

When we have many subqueries, it can be tiring.

I wish JOOQ can auto-resolve subqueries and put them in the WITH clause automatically.

4. It double-quotes some fields automatically but not the other. This can be a problem

I've found that JOOQ only add double quotes when we do alias. For example, table.field("some_field").as("New_Name") is translated to some_field as "New_Name". Notice that some_field isn't double-quoted.

In Postgres, New_Name wouldn't match the column "New_Name". This is because, without double quotes, Postgres will lowercase the name from New_Name to new_name.

Imagine your next query has table2.field("New_Name"). Postgres will complain that it cannot find a field named new_name.

The error message confused me for quite a while.

Conclusion

With those pain points, JOOQ is still far better than writing long SQLs. I'm glad I've discovered it!