Are Slick queries generally isomorphic to the SQL queries?

290 views
Skip to first unread message

Joost de Vries

unread,
Feb 9, 2015, 3:15:18 AM2/9/15
to scala...@googlegroups.com
I'm evaluating Slick and I came across this blog post blog.scalac.io/2015/01/27/rough-experience-with-slick.html

I was surprised how different the structure of the Scala query and the resulting Sql query are.
This is the core of the Scala 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
  }


And this is the resulting Sql

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

1 select with 3 inner joins versus 7 selects with 3 inner joins.

One of the things that draws me to Slick is that it allows you to think about the sql that you're executing against the database. Instead of being a framework that abstracts over the Sql and pretends to know better. 

So my question is: what is the general stance of Slick in this regard? Does it put the power & responsibility for executing the right Sql structure in the hands of the developer or not?
The use of the name 'join' in the DSL suggests the former but this result points to the latter.

Christopher Vogt

unread,
Feb 9, 2015, 3:40:55 PM2/9/15
to scala...@googlegroups.com
In short: no.

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.

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. Improving that situation is
planned for 3.1<Y

Slick also allows SQL text-strings and from 3.0 forward type-checking
for SQL strings.

If you are looking for a type-safe SQL string builder api, jooq may be
better suited for you.

Chris

Joost de Vries

unread,
Feb 10, 2015, 2:23:36 AM2/10/15
to scala...@googlegroups.com
Tx Christopher. That answers my question.

Joost de Vries

unread,
Feb 10, 2015, 7:58:47 AM2/10/15
to scala...@googlegroups.com
On 2nd thought: I'm trying to understand the advantage of Slicks approach.

You mention reuse and composition. And the similarity to using Scala collections.

Is there somewhere a description of these advantages in more detail?
I found these slides but the points they make are rather terse http://www.slideshare.net/skillsmatter/patterns-for-slick-database-applications


On Monday, 9 February 2015 21:40:55 UTC+1, Christopher Vogt wrote:

Christopher Vogt

unread,
Feb 10, 2015, 12:46:44 PM2/10/15
to scala...@googlegroups.com
Advantages: Better maintainability of code. More safety. For people who
know scala well but sql so much, also familiarity.

These docs chapters:

http://slick.typesafe.com/doc/2.1.0/sql-to-slick.html
http://slick.typesafe.com/doc/2.1.0/orm-to-slick.html

Chris
> --
>
> ---
> You received this message because you are subscribed to the Google
> Groups "Slick / ScalaQuery" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to scalaquery+...@googlegroups.com
> <mailto:scalaquery+...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/scalaquery/97fd12c8-a730-4ba3-a012-ad5fd7eedffd%40googlegroups.com
> <https://groups.google.com/d/msgid/scalaquery/97fd12c8-a730-4ba3-a012-ad5fd7eedffd%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

Naftoli Gugenheim

unread,
Feb 10, 2015, 6:01:10 PM2/10/15
to scala...@googlegroups.com

I suspect he was asking for better illustration of ways you can compose in slick that you can't in say jooq.


> an email to scalaquery+unsubscribe@googlegroups.com
> <mailto:scalaquery+unsub...@googlegroups.com>.

> To view this discussion on the web visit
> https://groups.google.com/d/msgid/scalaquery/97fd12c8-a730-4ba3-a012-ad5fd7eedffd%40googlegroups.com
> <https://groups.google.com/d/msgid/scalaquery/97fd12c8-a730-4ba3-a012-ad5fd7eedffd%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/54DA4400.3030400%40gmail.com.

Christopher Vogt

unread,
Feb 10, 2015, 11:03:54 PM2/10/15
to scala...@googlegroups.com
I don't know jooq enough. In principle it could do something similar to
slick with a SQL inspired DSL. Not sure how far it takes
compositionality and type safety.

The slick patterns talk was actually by me. A better intro is our Scala
days 2013 talk. See http://slick.Typesafe.com/docs/

Chris

On 10.02.15 18:01, Naftoli Gugenheim wrote:
> I suspect he was asking for better illustration of ways you can compose
> in slick that you can't in say jooq.
>
>
> On Tue, Feb 10, 2015, 12:46 PM Christopher Vogt
> <jan.christ...@gmail.com <mailto:jan.christ...@gmail.com>>
> wrote:
>
> Advantages: Better maintainability of code. More safety. For people who
> know scala well but sql so much, also familiarity.
>
> These docs chapters:
>
> http://slick.typesafe.com/doc/__2.1.0/sql-to-slick.html
> <http://slick.typesafe.com/doc/2.1.0/sql-to-slick.html>
> http://slick.typesafe.com/doc/__2.1.0/orm-to-slick.html
> <http://slick.typesafe.com/doc/2.1.0/orm-to-slick.html>
>
> Chris
>
> On 10.02.15 07:58, Joost de Vries wrote:
> > On 2nd thought: I'm trying to understand the advantage of Slicks
> approach.
> >
> > You mention reuse and composition. And the similarity to using Scala
> > collections.
> >
> > Is there somewhere a description of these advantages in more detail?
> > I found these slides but the points they make are rather
> > terse
> http://www.slideshare.net/__skillsmatter/patterns-for-__slick-database-applications
> > an email to scalaquery+unsubscribe@__googlegroups.com
> <mailto:scalaquery%2Bunsu...@googlegroups.com>
> > <mailto:scalaquery+_...@googlegroups.com
> <mailto:scalaquery%2Bunsu...@googlegroups.com>>.
> > To view this discussion on the web visit
> >
> https://groups.google.com/d/__msgid/scalaquery/97fd12c8-__a730-4ba3-a012-ad5fd7eedffd%__40googlegroups.com
> <https://groups.google.com/d/msgid/scalaquery/97fd12c8-a730-4ba3-a012-ad5fd7eedffd%40googlegroups.com>
> >
> <https://groups.google.com/d/__msgid/scalaquery/97fd12c8-__a730-4ba3-a012-ad5fd7eedffd%__40googlegroups.com?utm_medium=__email&utm_source=footer
> <https://groups.google.com/d/msgid/scalaquery/97fd12c8-a730-4ba3-a012-ad5fd7eedffd%40googlegroups.com?utm_medium=email&utm_source=footer>>.
> > For more options, visit https://groups.google.com/d/__optout
> <https://groups.google.com/d/optout>.
>
> --
>
> ---
> You received this message because you are subscribed to the Google
> Groups "Slick / ScalaQuery" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to scalaquery+unsubscribe@__googlegroups.com
> <mailto:scalaquery%2Bunsu...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/__msgid/scalaquery/54DA4400.__3030400%40gmail.com
> <https://groups.google.com/d/msgid/scalaquery/54DA4400.3030400%40gmail.com>.
> For more options, visit https://groups.google.com/d/__optout
> <https://groups.google.com/d/optout>.
>
> --
>
> ---
> You received this message because you are subscribed to the Google
> Groups "Slick / ScalaQuery" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to scalaquery+...@googlegroups.com
> <mailto:scalaquery+...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/scalaquery/CANpg8PDOyj9rrcjU_yg734qCoTHsnSiyJFKq0arEdVAeBNYV4w%40mail.gmail.com
> <https://groups.google.com/d/msgid/scalaquery/CANpg8PDOyj9rrcjU_yg734qCoTHsnSiyJFKq0arEdVAeBNYV4w%40mail.gmail.com?utm_medium=email&utm_source=footer>.

Lukas Eder

unread,
Feb 11, 2015, 7:30:35 AM2/11/15
to scala...@googlegroups.com
I hope you don't mind me chiming in - since you're discussing jOOQ anyway. This is Lukas from Data Geekery, the company behind jOOQ.

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

Cheers,
Lukas

Christopher Vogt

unread,
Feb 11, 2015, 10:28:45 AM2/11/15
to scala...@googlegroups.com

Thx Lukas, I was hoping for you to comment . Chris
> > <jan.christ...@gmail.com <javascript:>
> <mailto:jan.christ...@gmail.com <javascript:>>>
> > wrote:
> >
> > Advantages: Better maintainability of code. More safety. For
> people who
> > know scala well but sql so much, also familiarity.
> >
> > These docs chapters:
> >
> > http://slick.typesafe.com/doc/__2.1.0/sql-to-slick.html
> <http://slick.typesafe.com/doc/__2.1.0/sql-to-slick.html>
> > <http://slick.typesafe.com/doc/2.1.0/sql-to-slick.html
> <http://slick.typesafe.com/doc/2.1.0/sql-to-slick.html>>
> > http://slick.typesafe.com/doc/__2.1.0/orm-to-slick.html
> <http://slick.typesafe.com/doc/__2.1.0/orm-to-slick.html>
> > <http://slick.typesafe.com/doc/2.1.0/orm-to-slick.html
> <http://slick.typesafe.com/doc/2.1.0/orm-to-slick.html>>
> >
> > Chris
> >
> > On 10.02.15 07:58, Joost de Vries wrote:
> > > On 2nd thought: I'm trying to understand the advantage of
> Slicks
> > approach.
> > >
> > > You mention reuse and composition. And the similarity to
> using Scala
> > > collections.
> > >
> > > Is there somewhere a description of these advantages in more
> detail?
> > > I found these slides but the points they make are rather
> > > terse
> >
> http://www.slideshare.net/__skillsmatter/patterns-for-__slick-database-applications
> <http://www.slideshare.net/__skillsmatter/patterns-for-__slick-database-applications>
>
> >
> <http://www.slideshare.net/skillsmatter/patterns-for-slick-database-applications
> <http://googlegroups.com>
> > <mailto:scalaquery%2Bunsu...@googlegroups.com <javascript:>>
> > > <mailto:scalaquery+_...@googlegroups.com <javascript:>
> > <mailto:scalaquery%2Bunsu...@googlegroups.com
> <javascript:>>>.
> > > To view this discussion on the web visit
> > >
> >
> https://groups.google.com/d/__msgid/scalaquery/97fd12c8-__a730-4ba3-a012-ad5fd7eedffd%__40googlegroups.com
> <https://groups.google.com/d/__msgid/scalaquery/97fd12c8-__a730-4ba3-a012-ad5fd7eedffd%__40googlegroups.com>
>
> >
> <https://groups.google.com/d/msgid/scalaquery/97fd12c8-a730-4ba3-a012-ad5fd7eedffd%40googlegroups.com
> <https://groups.google.com/d/msgid/scalaquery/97fd12c8-a730-4ba3-a012-ad5fd7eedffd%40googlegroups.com>>
>
> > >
> >
> <https://groups.google.com/d/__msgid/scalaquery/97fd12c8-__a730-4ba3-a012-ad5fd7eedffd%__40googlegroups.com?utm_medium=__email&utm_source=footer
> <https://groups.google.com/d/__msgid/scalaquery/97fd12c8-__a730-4ba3-a012-ad5fd7eedffd%__40googlegroups.com?utm_medium=__email&utm_source=footer>
>
> >
> <https://groups.google.com/d/msgid/scalaquery/97fd12c8-a730-4ba3-a012-ad5fd7eedffd%40googlegroups.com?utm_medium=email&utm_source=footer
> <https://groups.google.com/d/msgid/scalaquery/97fd12c8-a730-4ba3-a012-ad5fd7eedffd%40googlegroups.com?utm_medium=email&utm_source=footer>>>.
>
> > > For more options, visit https://groups.google.com/d/__optout
> <https://groups.google.com/d/__optout>
> > <https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>>.
> >
> > --
> >
> > ---
> > You received this message because you are subscribed to the
> Google
> > Groups "Slick / ScalaQuery" group.
> > To unsubscribe from this group and stop receiving emails from it,
> > send an email to scalaquery+unsubscribe@__googlegroups.com
> <http://googlegroups.com>
> > <mailto:scalaquery%2Bunsu...@googlegroups.com <javascript:>>.
> > To view this discussion on the web visit
> >
> https://groups.google.com/d/__msgid/scalaquery/54DA4400.__3030400%40gmail.com
> <https://groups.google.com/d/__msgid/scalaquery/54DA4400.__3030400%40gmail.com>
>
> >
> <https://groups.google.com/d/msgid/scalaquery/54DA4400.3030400%40gmail.com
> <https://groups.google.com/d/msgid/scalaquery/54DA4400.3030400%40gmail.com>>.
>
> > For more options, visit https://groups.google.com/d/__optout
> <https://groups.google.com/d/__optout>
> > <https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>>.
> >
> > --
> >
> > ---
> > You received this message because you are subscribed to the Google
> > Groups "Slick / ScalaQuery" group.
> > To unsubscribe from this group and stop receiving emails from it,
> send
> > an email to scalaquery+...@googlegroups.com <javascript:>
> > <mailto:scalaquery+...@googlegroups.com <javascript:>>.
> > To view this discussion on the web visit
> >
> https://groups.google.com/d/msgid/scalaquery/CANpg8PDOyj9rrcjU_yg734qCoTHsnSiyJFKq0arEdVAeBNYV4w%40mail.gmail.com
> <https://groups.google.com/d/msgid/scalaquery/CANpg8PDOyj9rrcjU_yg734qCoTHsnSiyJFKq0arEdVAeBNYV4w%40mail.gmail.com>
>
> >
> <https://groups.google.com/d/msgid/scalaquery/CANpg8PDOyj9rrcjU_yg734qCoTHsnSiyJFKq0arEdVAeBNYV4w%40mail.gmail.com?utm_medium=email&utm_source=footer
> <https://groups.google.com/d/msgid/scalaquery/CANpg8PDOyj9rrcjU_yg734qCoTHsnSiyJFKq0arEdVAeBNYV4w%40mail.gmail.com?utm_medium=email&utm_source=footer>>.
>
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
>
> ---
> You received this message because you are subscribed to the Google
> Groups "Slick / ScalaQuery" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to scalaquery+...@googlegroups.com
> <mailto:scalaquery+...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/scalaquery/47698351-0c4c-48f0-a515-72d0b6efb0e3%40googlegroups.com
> <https://groups.google.com/d/msgid/scalaquery/47698351-0c4c-48f0-a515-72d0b6efb0e3%40googlegroups.com?utm_medium=email&utm_source=footer>.

Joost de Vries

unread,
Feb 12, 2015, 3:40:01 AM2/12/15
to scala...@googlegroups.com
Interesting overview. Tx all!

Joost de Vries

tel: 06-29 52 45 86

privé: ᏠᎾᎾᏕᎢ@ᎠᎬ-ᏉᏒᏐᎬᏕ.ƝᎪᎷᎬ
privé: ᏠᎾᎾᏕᎢ@ᏃᏐᎬƝᎬᏒ.ᎾᏒᏀ

You received this message because you are subscribed to a topic in the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/scalaquery/K2tch9yxx60/unsubscribe.
To unsubscribe from this group and all its topics, send an email to scalaquery+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/54DB752A.5060702%40gmail.com.
Reply all
Reply to author
Forward
0 new messages