Every framework introduces a new compromise. A compromise that is introduced because the framework makes some assumptions about how you’d like to interact with your software infrastructure.
An example of where this compromise has struck users recently is the discussion “Are Slick queries generally isomorphic to the SQL queries?“. And, of course, the answer is: No. What appears to be a simple Slick query:
val salesJoin = sales
join purchasers
join products
join suppliers on {
case (((sale, purchaser), product), supplier) =>
sale.productId === product.id &&
sale.purchaserId === purchaser.id &&
product.supplierId === supplier.id
}… turns into a rather large monster with tons of derived tables that are totally unnecessary, given the original query (formatting is mine):
select x2.x3, x4.x5, x2.x6, x2.x7
from (
select x8.x9 as x10,
x8.x11 as x12,
x8.x13 as x14,
x8.x15 as x7,
x8.x16 as x17,
x8.x18 as x3,
x8.x19 as x20,
x21.x22 as x23,
x21.x24 as x25,
x21.x26 as x6
from (
select x27.x28 as x9,
x27.x29 as x11,
x27.x30 as x13,
x27.x31 as x15,
x32.x33 as x16,
x32.x34 as x18,
x32.x35 as x19
from (
select x36."id" as x28,
x36."purchaser_id" as x29,
x36."product_id" as x30,
x36."total" as x31
from "sale" x36
) x27
inner join (
select x37."id" as x33,
x37."name" as x34,
x37."address" as x35
from "purchaser" x37
) x32
on 1=1
) x8
inner join (
select x38."id" as x22,
x38."supplier_id" as x24,
x38."name" as x26
from "product" x38
) x21
on 1=1
) x2
inner join (
select x39."id" as x40,
x39."name" as x5,
x39."address" as x41
from "supplier" x39
) x4
on ((x2.x14 = x2.x23)
and (x2.x12 = x2.x17))
and (x2.x25 = x4.x40)
where x2.x7 >= ?Christopher Vogt, a former Slick maintainer and still actively involved member of the Slick community, explains the above in the following words:
This means that Slick relies on your database’s query optimizer to be able to execute the sql query that Slick produced efficiently. Currently that is not always the case in MySQL
One of the main ideas behind Slick, according to Christopher, is:
Slick is not a DSL that allows you to build exactly specified SQL strings. Slick’s Scala query translation allows for re-use and composition and using Scala as the language to write your queries. It does not allow you to predict the exact sql query, only the semantics and the rough structure.
Slick vs. jOOQ
Since Christopher later on also compared Slick with jOOQ, I allowed myself to chime in and to add my two cents:
From a high level (without actual Slick experience) I’d say that Slick and jOOQ embrace compositionality equally well. I’ve seen crazy queries of several 100s of lines of [jOOQ] SQL in customer code, composed over several methods. You can do that with both APIs.
On the other hand, as Chris said: Slick has a focus on Scala collections, jOOQ on SQL tables.
- From a conceptual perspective (= in theory), this focus shouldn’t matter.
- From a type safety perspective, Scala collections are easier to type-check than SQL tables and queries because SQL as a language itself is rather hard to type-check given that the semantics of various of the advanced SQL clauses alter type configurations rather implicitly (e.g. outer joins, grouping sets, pivot clauses, unions, group by, etc.).
- From a practical perspective, SQL itself is only an approximation of the original relational theories and has attained a life of its own. This may or may not matter to you.
I guess in the end it really boils down to whether you want to reason about Scala collections (queries are better integrated / more idiomatic with your client code) or about SQL tables (queries are better integrated / more idiomatic with your database).
At this point, I’d like to add another two cents to the discussion. Customers don’t buy the product that you’re selling. They never do. In the case of Hibernate, customers and users were hoping to be able to forget SQL forever. The opposite is true. As Gavin King himself (the creator of Hibernate) had told me:
Because customers and users had never listened to Gavin (and to other ORM creators), we now have what many call the object-relational impedance mismatch. A lot of unjustified criticism has been expressed against Hibernate and JPA, APIs which are simply too popular for the limited scope they really cover.
With Slick (or C#’s LINQ, for that matter), a similar mismatch is impeding integrations, if users abuse these tools for what they believe to be a replacement for SQL. Slick does a great job at modelling the relational model directly in the Scala language. This is wonderful if you want to reason about relations just like you reason about collections. But it is not a SQL API. To illustrate how difficult it is to overcome these limitations, you can browse the issue tracker or user group to learn about:
We’ll simply call this:
The Functional-Relational Impedance Mismatch
SQL is much more
Markus Winand (the author of the popular SQL Performance Explained) has recently published a very good presentation about “modern SQL”, an idea that we fully embrace at jOOQ:
We believe that APIs that have been trying to hide the SQL language from general purpose languages like Java, Scala, C# are missing out on a lot of the very nice features that can add tremendous value to your application. jOOQ is an API that fully embraces the SQL language, with all its awesome features (and with all its quirks). You obviously may or may not agree with that.
We’ll leave this article open ended, hoping you’ll chime in to discuss the benefits and caveats of each approach. Of staying close to Scala vs. staying close to SQL.
As a small teaser, however, I’d like to announce a follow-up article showing that there is no such thing as an object-relational impedance mismatch. You (and your ORM) are just not using SQL correctly. Stay tuned!
| Reference: | jOOQ vs. Slick – Pros and Cons of Each Approach from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
