A SQL query DSL for Scala by ScalikeJDBC

There are a tremendous amount of SQL APIs natively written in Scala. Manuel Bernhardt has summarised a nice collection in his a post. Another collection of Scala SQL APIs can be seen in this Stack Overflow question.

One API that we want to focus on in particular is ScalikeJDBC (licensed ASL 2.0), which has recently published a SQL query DSL API similar to that of jOOQ. See the full documentation here:

http://scalikejdbc.org/documentation/query-dsl.html

A couple of examples:

val orders: List[Order] = withSQL {
  select
    .from(Order as o)
    .innerJoin(Product as p).on(o.productId, p.id)
    .leftJoin(Account as a).on(o.accountId, a.id)
    .where.eq(o.productId, 123)
    .orderBy(o.id).desc
    .limit(4)
    .offset(0)
  }.map(Order(o, p, a)).list.apply()

The above example looks very similar to jOOQ code, except that the SELECT DSL seems to be a bit more rigid than jOOQ’s. For instance, it is not immediately obvious how to connect several complex predicates in that WHERE clause, or if complex predicates are available at all.

What’s really nice, however, is their way of leveraging Scala language features to provide a very fluent way of constructing dynamic SQL, as can be seen in this example:

def findOrder(id: Long, accountRequired: Boolean) = 
withSQL {
  select
    .from[Order](Order as o)
    .innerJoin(Product as p).on(o.productId, p.id)
    .map { sql =>
      if (accountRequired) 
        sql.leftJoin(Account as a)
           .on(o.accountId, a.id)
      else 
        sql
    }.where.eq(o.id, 13)
  }.map { rs =>
    if (accountRequired) 
      Order(o, p, a)(rs) 
    else 
      Order(o, p)(rs)
  }.single.apply()

From how we understand things, the map method that is invoked in the middle of the SQL statement (between innerJoin and where) can transform the intermediate DSL state using a lambda expression that allows for appending a leftJoin if needed. Obviously, this can be done in a more procedural fashion as well, by assigning that intermediate DSL state to a local variable.

The need for SQL query DSLs

We’ve blogged about many of these similar SQL query DSLs in the past. The fact that they constantly pop up in various APIs is no coincidence. SQL is a very typesafe and composable language that is hard to use dynamically through string-based APIs such as JDBC, ODBC, etc.

Having a typesafe internal domain-specific language model SQL in a host language like Java or Scala brings great advantages. But the disadvantages may shine through quickly, when the DSL is not carefully crafted in a completely foreseeable way. Take the following ScalikeJDBC QueryDSL example, for instance:

val ids = withSQL {
  select(o.result.id).from(Order as o)
    .where(sqls.toAndConditionOpt(
      productId.map(id => sqls.eq(o.productId, id)),
      accountId.map(id => sqls.eq(o.accountId, id))
    ))
    .orderBy(o.id)
}.map(_.int(1)).list.apply()

This toAndConditionOpt method is really unexpected and doesn’t follow the principle of least astonishment.

This is why jOOQ’s API design is based on a formal BNF that closely mimicks SQL itself. Read more about that here.

Typesafe’s Slick is Not About SQL

We have stumbled upon an interesting thread on the Typesafe SLICK user group where Slick was compared to jOOQ. In that thread, Christopher Vogt has made a couple of interesting statements.

But let us have a look at the broader context, first.

Unifying Stuff

Ever since the proclamation of UDDI or RUP, we may think that the U for Unified is a clear and unmistakable indicator for what Joel Spolsky would call architecture astronautitis. In case you’ve missed those hilarious posts, here they are:

Today, many software vendors are again trying to unify database query languages. Erik Meijer’s LINQ was the most successful attempt at doing so, so far. But even LINQ doesn’t compare to Codd’s visions, which were about replacing the whole data model first by a rock-solid mathematical theory, and only then, thinking about appropriate languages to query such data models.

Flexible vs. rigid abstractions

We believe that unifying query languages to query RDBMS, XML, Objects, and NoSQL is a bad idea because such a unification is subject to either:

  • being a flexible abstraction
  • being a rigid abstraction

If an abstraction is flexible, then the heterogeneous implementation details of the abstracted data stores will inevitably leak into the query language and into your application. You don’t gain too much, for the price of adding more layers and boiler-plate.

If an abstraction is rigid, then the unified query language (LINQ, JPQL, etc.) may be concise, but it will inevitably abstract away 80% of all useful features of the underlying data store. LINQ cannot meet the expressivity of SQL. Neither can it match the power of XPath/XQuery/XSLT/XProc, which is the most appropriate tool chain for XML. Maybe, it cannot even match what Java 8 calls the Streams API, which is very likely to become the most appropriate tool chain for objects and collections in Java.

Typesafe’s SLICK is Not About SQL

We’ve already compared Slick with jOOQ in our manual’s preface. Now, Christopher Vogt has made a clear statement about what SLICK is supposed to be and what SQL is:

There are understandable mistakes when your mind is (still) set on SQL. […]

Good luck with jooq and check back if you are ever annoyed by SQL semantics and want Scala back :).

That is only an extract of what Christopher said, of course, and there’s certainly quite a bit of goodness in SLICK. SLICK’s mission is to provide Scala collection semantics when querying databases. That might be a desireable thing to have in the Scala platform, specifically when comparing SLICK with LINQ.

But we’ve mentioned it before, on our blog. SQL is not an undesirable language or technology. Like any legacy technology, SQL has its ways. We’ve blogged about that, too, lots of times. SQL is a standard that is constantly evolving and that is here to stay. In our opinion, any technology operating on RDBMS but at the same time aiming for hiding SQL or abstracting it away completely is against the inevitable trend imposed by the big elephants who will not let go of their best-selling technologies.

SQL is about 10 years ahead of alternative RDBMS querying methods – most specifically Java, Scala, C# collection-based ones. T-SQL has now entered the TIOBE Top 10 and is considered by TIOBE to be the language of the year 2013, PL/SQL isn’t too far behind. Don’t fight SQL any longer, embrace it. Or in Christopher Vogt’s words:

Check back with SQL/jOOQ, if you are ever annoyed by the increasing amount of leaky or rigid abstraction created by modern language architects!

Further reading: “Don’t Jump the SQL Ship Just Yet”.

Duck Typing in Scala: Structural Typing

Scala goes very far with its loads of features improving the JVM world. Sometimes, a bit too far, maybe. But this feature here is just plain awesome! Typesafe duck typing! An example:

def quacker(duck: {def quack(value: String): String}) {
  println (duck.quack("Quack"))
}

The above quacker method accepts any “duck” that can quack.

Read the full article here:
http://java.dzone.com/articles/duck-typing-scala-structural

SLICK, integrating SQL into Scala

Now it’s official – even if version numbers are still preceded by a “zero” major release: SLICK has been publicly announced by Typesafe:

SLICK stands for Scala Language-Integrated Connection Kit, which is more or less the Scala equivalent for LINQ-to-SQL. Note that I say LINQ-to-SQL, not LINQ in general, as Scala already has sufficient means of querying collections using the Scala language itself.

Here’s a sample of what SLICK code will look like (taken from the SLICK website):

object Coffees extends Table[(String, Int, Double)]("COFFEES") {
  def name = column[String]("COF_NAME", O.PrimaryKey)
  def supID = column[Int]("SUP_ID")
  def price = column[Double]("PRICE")
  def * = name ~ supID ~ price
}

Coffees.insertAll(
  ("Colombian",         101, 7.99),
  ("Colombian_Decaf",   101, 8.99),
  ("French_Roast_Decaf", 49, 9.99)
)

val q = for {
  c <- Coffees if c.supID === 101
  //                       ^ comparing Rep[Int] to Rep[Int]!
} yield (c.name, c.price)

println(q.selectStatement)

q.foreach { case (n, p) => println(n + ": " + p) }

As you can see, SLICK neatly integrates with Scala’s own syntax. As with LINQ-to-SQL, SLICK’s goal is to

“write your database queries in Scala instead of SQL”

This is quite orthogonal to what jOOQ is aiming for:

“SQL was never meant to be anything other than… SQL!”

As a reminder, see my previous blog post about how jOOQ integrates with Scala, and how you can write almost-SQL queries in Scala using jOOQ against 13 popular databases. It would be interesting to compare the two approaches side-by-side in an independent evaluation, to see the pro’s and con’s of each one, in terms of

  • Developer productivity
  • Maintainability
  • Performance
  • Feature scope
  • etc.

I think it’s time someone made that evaluation. An example can be seen here:

http://stackoverflow.com/questions/10537766/closest-equivalent-to-sqlalchemy-for-java-scala

SQL in Scala, where jOOQ could go

I have recently blogged about how simple it is to integrate jOOQ into Scala. See the full blog post here:

https://lukaseder.wordpress.com/2011/12/11/the-ultimate-sql-dsl-jooq-in-scala/

I’m more and more thrilled by that option, as Scala is one of the fastest emerging JVM languages nowadays. The plain integration of a Java library in Scala leaves some open questions. jOOQ knows the “val()” operator or method, to create bind values. See the manual here:

http://www.jooq.org/manual/JOOQ/BindValues/

This operator cannot be used in Scala, as Scala declares “val” as a reserved word. I’ve had similar issues in Java before, when trying to use “case” or “else” in the API, which is not possible either. The path of least resistance is to overload or re-name those methods. “val” was overloaded as “value” in jOOQ 2.0.1. “case” and “else” were re-named a long time ago as “decode” (from Oracle’s DECODE function), and “otherwise” (as in XSL).

So for a full-fledged integration in Scala, jOOQ should be wrapped in a new API called scOOQ ;-). This new API should take Scala’s language features into account in order to make working with jOOQ a lot easier. This could be the chance to re-engineer some of the API and make all API methods uppercase, as is usual with SQL. With Scala’s ability of omitting syntax elements, such as “.” and “()”, the API could then declare one-word methods, such as in this Java example:

MERGE().INTO(MY_TABLE)
       .USING(SOURCE_TABLE)
       .ON(MY_TABLE.ID.equal(SOURCE_TABLE.ID))
       .WHEN().MATCHED().THEN().UPDATE()
         .SET(ID, 1)
         .SET(DATA, "Data")
       .WHEN().NOT().MATCHED().THEN().INSERT(MY_TABLE.ID, MY_TABLE.DATA)
       .VALUES(1, "Data")

While in Java, this looks quite nasty and verbose, in Scala it could be very nice! The below statement should compile in Scala if the API was declared as such:

MERGE INTO MY_TABLE
      USING (SOURCE_TABLE)
      ON (MY_TABLE.ID equal SOURCE_TABLE.ID)
      WHEN MATCHED THEN UPDATE
        SET (ID, 1)
        SET (DATA, "Data")
      WHEN NOT MATCHED THEN INSERT (MY_TABLE.ID, MY_TABLE.DATA)
      VALUES (1, "Data")

Convinced? Contributions very welcome! :-)

The ultimate SQL-DSL: jOOQ in Scala

I’ve recently come across some advertising for the new upcoming version of Scala IDE for Eclipse, which made me remember my college programming lessons at the EPFL Laboratoire des Méthodes de Programmation (LAMP), the origin of the Scala language. Back then, Scala appeared quite freaky. Very elegant, a bit inefficient, somewhat dogmatic. It was much more functional than object oriented, from what I recall, and Martin Odersky had a hard time agreeing that the key to success is to combine the two paradigms. But Scala has come a long way in the last 8 years. So I was wondering if jOOQ was portable to Scala. The answer amazes me:

jOOQ is 100% Scala-ready !!

Obviously, this is not due to jOOQ’s fluent API alone. It’s mostly because of how Scala was built on top of Java. Check out this piece of sample code:

package org.jooq.scala

import java.sql.Connection
import java.sql.DriverManager

// This makes integration of Java into Scala easier
import scala.collection.JavaConversions._

// Import all relevant things from jOOQ
import org.jooq.impl.Factory._
import org.jooq.util.maven.example.mysql.Test2Factory
import org.jooq.util.maven.example.mysql.Tables._

object Test {
  def main(args: Array[String]) {

    // This is business as usual. I guess there's
    // also a "Scala way" to do this...?
    Class.forName("com.mysql.jdbc.Driver");
    val connection = DriverManager.getConnection(
      "jdbc:mysql://localhost/test", "root", "");
    val create = new Test2Factory(connection);

    // Fetch book titles and their respective authors into
    // a result, and print the result to the console. Wow!
    // If this doesn't feel like SQL to you...?
    val result = (create
      select (
          T_BOOK.TITLE as "book title",
          T_AUTHOR.FIRST_NAME as "author's first name",
          T_AUTHOR.LAST_NAME as "author's last name")
      from T_AUTHOR
      join T_BOOK on (T_AUTHOR.ID equal T_BOOK.AUTHOR_ID)
      where (T_AUTHOR.ID in (1, 2, 3))
      orderBy (T_AUTHOR.LAST_NAME asc) fetch)

    // Print the result to the console
    println(result)

    // Iterate over authors and the number of books they've written
    // Print each value to the console
    for (r <- (create
               select (T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME, count)
               from T_AUTHOR
               join T_BOOK on (T_AUTHOR.ID equal T_BOOK.AUTHOR_ID)
               where (T_AUTHOR.ID in (1, 2, 3))
               groupBy (T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME)
               orderBy (T_AUTHOR.LAST_NAME asc)
               fetch)) {

      // Accessing record data is just like in Java
      print(r.getValue(T_AUTHOR.FIRST_NAME))
      print(" ")
      print(r.getValue(T_AUTHOR.LAST_NAME))
      print(" wrote ")
      print(r.getValue(count))
      println(" books ")
    }
  }
}

As expected, the console contains this data

+------------+-------------------+------------------+
|book title  |author's first name|author's last name|
+------------+-------------------+------------------+
|O Alquimista|Paulo              |Coelho            |
|Brida       |Paulo              |Coelho            |
|1984        |George             |Orwell            |
|Animal Farm |George             |Orwell            |
+------------+-------------------+------------------+

Paulo Coelho wrote 2 books 
George Orwell wrote 2 books 

You get 2 in 1

With Scala, jOOQ’s fluent API looks even more like SQL than in Java. And you get 2 in 1:

  1. Typesafe querying, meaning that your SQL syntax is compiled
  2. Typesafe querying, meaning that your database schema is part of the code

The biggest drawback I can see so far is that Scala ships with new reserved words, such as val, a very important method in jOOQ. I guess that could be sorted out somehow. So Scala users and SQL enthusiasts! Please! Feedback :-)