A quick tour of relational database access with Scala

Update: Added Squeryl, thanks to @JoeZulli for the heads-up
Update 2: Added link to latest Squeryl release for Scala 2.10 and 2.9.2

I recently started working with relational databases again from within Scala, after a while in JSON-land (mostly MongoDB and elasticsearch). During that time, I’d been happily working with lightweight libraries / DSLs that take care of case class serialization, such as the impeccable Salat and elastic4s.

So I started looking for a tool that would let me achieve the following:

  • library optimized for Scala (easy to add to an SBT workflow, ideally with a wrapper for the Play framework)
  • easy mapping to and from case classes
  • control over the resulting SQL
  • ability to take care of CRUD operations without too much dialogue
  • ability to build finders quickly (note: I’m talking about down-to-earth, readable finders, such as findById and findByEmail, not about monstruosities such as findByAgeBetweenEighteenAndTwentySevenAndWithColorBlue – I don’t really know where this comes from, but I’ve seen it, and it’s a terrible, terrible idea)
  • last but not least: a certain level of maturity, and perspective that it will be maintained in the future (unlike e.g. the once promising Cricumflex ORM which appears to be no longer maintained)

The trouble seems to be that just like for JSON libraries or HTTP Client libraries, it seems to be quite fashionable in Scala-land to roll your own Scala library for relational database access, each of those implementations having their own set of advantages and disadvantages.

I’ve spent some time looking around, and here are my findings (disclaimer: this is a quick tour, if you want an in-depth analysis, I suggest you go and try the libraries out for yourself). Also, sorry if I forgot a library, and please let me know so.

In alphabetical order:

Activate

The Activate framework is a framework that acts as a wrapper around a number of persistance technologies, including JDBC and MongoDB. What I found perculiarily interesting about its design is the use of Software transactional memory (or STM), which helps overcome tricky problems, such as concurrent bank transactions.

It rolls its own query language (but is compatible with Slick queries), has a Play integration and a solid documentation from which it is easy to get started.

Inserting:

Mapping:

Querying:

(it’s the extends Entity part which does most of the magic, as you may have guessed, and as explained here).

The development is very active.

Anorm

Anorm rocked the boat a while back by declaring that ORMs are a bad idea (it’s been done before) and proposing an alternative that goes back to the roots, really quite close to them in fact, by letting you write your own SQL (and not only a little, but a lot). One point of the argumentation that I think makes definitely sense is to say that going through the burden of abstracting both mappings and query langauge to be “database independent” may sound sexy in theory, but in practive, I’ve never once switched the underlying DBMS once a project started – on the other hand I’ve very often had to make use of specific features of a DBMS, and then any kind of abstraction layer was very much in the way of what I was trying to do.

At the moment, Anorm is still shiped as part of Play, but this will change, i.e. Anorm should get its own repository and the default for the upcoming Play release be Slick. I haven’t tried, but I think that it already is possible to use Anorm as a separate dependency – at least, the sources are separate.

So how does it look like? As promised, it involves some SQL:

Inserting:

Mapping:

Querying:

As you see, this is rather verbose (ok, maybe it’s not too visible here, but take a table with 20 columns, and you’ll see what I’m talking about). The good news is that there’s already a solution to some of the verbosity which makes use of string interpolation.

Also, the tool itself can easily be extended to support custom types and the like.

ScalikeJDBC

A library I didn’t know about previously is ScalikeJDBC, which very much in the fashion of Anorm is close to SQL. An interesting aspect is that it adds support for non-blocking access to databases in the JDBC fashion (for the time being, only PostgreSQL and MySQL are supported). It has a plugin for Play, code generation, a type-safe query DSL but also /plain.

Let’s have a look:

Inserting:

Mapping:

Querying:

Once you get over the use of apply(), the way of working is very straightforward.

The project is under active development and has a solid documentation.

Slick

Backed by Typesafe, Slick embraces functional programming as a means to access relational data. Very much like LINQ it integrates directly with the language, letting you write idiomatic (or quasi-idiomatic) Scala to get your data.

At the time of writing this post, Slick 2.0 just got released, bringing in some new features such as a code generator, an improved means of mapping to the * projection (which lets you map to case class hierarchies), a way to work around the 22 field limit of Scala’s case classes by means of HLists and more.

Inserting:

Mapping:

Querying:

As you can see, the mapping does take some effort. You’re really specifying a representation of a table, and can add a number of options on each column (O.PrimaryKey, O.AutoInc, O.NotNull in the example above). Once you’re trough this burden, though, you’re ready to go and use the type-safe query DSL.

Needless to say, Slick is under active development. There’s a Play module for it which also brings integration with Play’s evolution mechanism and generates DDL code.

Squeryl

One library that completely slipped my radar is Squeryl. I had looked into Squeryl some time ago, but for some reason during my recent survey it didn’t show up in any of the search results. To what I understand, Squeryl was one of the first libaries to promote the type-safe query DSL paradigm – at least, the argumentation on the webiste seems to point in that direction.

Let’s have a closer look:

Inserting:

Mapping:

Querying:

The documentation looks very complete, and there’s an extensive test suite where to find more examples. On the development side of things, my impression is that the project is more in maintenance mode now, as development seems to have quieted down over the past year and there doesn’t seem to be a stable release for Scala 2.10 out yet. The latest stable release is 0.9.5-6 and is available both for Scala 2.10 and 2.9.2. Also, the discussion group is quite active.

Great, now which one to pick?

Now that we’ve looked around a bit, the obvious question seems to be: which one to use for your project – which one did you pick?

Well, as I found shortcomings to all of the tools outlined above, I decided to write my own persistance library, RelaScala, which leverages Macro annotations and Type Lambdas for a better development experience. Nah, I’m just messing with you.

I’ve worked with Anorm and just can’t get over the verbosity it brings, so I wouldn’t want to work with it anymore. If you’re a little less worried about the projects not being maintained one day, I think the approaches taken by Activate or ScalikeJDBC are pretty interesting (although perhaps Activate is a bit too much on the abstract side of things for my taste).

For the projects I’m working on currently, I’ve ended up using Slick because of the long-term view (actually, I’ve just recently migrated a project over from Anorm to Slick 2). It took me some time to decide to recommend Slick, because I had worked with it before, and it also can get rather verbose in its own way. Also, for my level of knowledge of Scala, I find the source code to somewhat hard to grasp. That being said, the ability to map to hierarchies of case-classes makes things a lot easier, and there’s always the option to write plain SQL and thus to get back in control.

At this point, I should mention Renato’s work on a generic DAO for Slick which I plan on using in the future.

Bonus: jOOQ

One library that I haven’t mentioned above is an upcoming star in the Java space: jOOQ. Unlike any of the libraries presented here or that I’ve seen around, jOOQ both provides a type-safe DSL for writing queries and does not abstract over SQL. In other words, it provides database-specific DSLs for writing queries. An interesting side-effect when using jOOQ in Scala is that the dots can be ommited, leading to the following code:

(yes, this is actual code, even if it looks very close to plain SQL – more about it here).

The reason I didn’t include jOOQ in the comparison above is because I was looking for a means to map to case classes out-of-the-box in a type-safe manner (and is type-safe in regards to mapping). Let’s take a quick tour nonetheless:

Inserting:

Mapping:

Let’s take a look at the JPA-annotation-based mapping:

(more about mapping here)

Querying

jOOQ has an astoundingly complete documentation, I’ve rarely seen a project with such a good documentation and so many examples. It also lets you roll your own mapper, which lets me think it is only a matter of time before someone will implement a Scala macro for mapping to case classes.

In my opinion, jOOQ is a nicely fresh and realistic alternative for RDBMS persistance in the Java and Scala space, especially if you’re not taking a quiet stroll in the flowerly fields of a new project, but are deep in the trenches with an existing database schema, dealing with queries of 500+ lines of SQL (I’m trying hard to avoid working on such projects, but they are very real).

Conclusion

I hope this tour was useful. If you have any question, just leave a comment, I’ll try my best to reply.

20 Comments A quick tour of relational database access with Scala

  1. Renato Cavalcanti

    Hi Manu,

    Thanks for mentioning the slick-dao project. It’s a tiny project, but I find it useful for my use cases.

    That pushes me to write some more info on the README file and to work further on the project. :-)

  2. Simon Ochsenreither

    With Slick 2.0, none of this is needed anymore:

    import scala.slick.driver.MySQLDriver.simple._

    case class Person(id: Option[Long] = None, name: String, surname: String)

    class Persons(tag: Tag) extends Table[Person](tag, “person”) {
    def id = column[Long](“id”, O.PrimaryKey, O.AutoInc)
    def name = column[String](“name”, O.NotNull)
    def surname = column[String](“surname”, O.NotNull)

    def * = (id.?, name, surname) (Person.tupled, Person.unapply)
    }

    object Persons {
    lazy val persons = TableQuery[Persons]
    }

    Otherwise, great article!

  3. Chris Dinn

    Watch out for Slick. Licensing restrictions are ridiculous. Ask them how much per VM to connect to an Oracle DB and wait for your jaw to drop.

  4. Dave Whittaker

    I think you’re mistaking the fact that 0.9.6-RC2 is the most recent Squeryl published for Scala 2.10 as an indication that there isn’t a stable version. Squeryl 0.9.5-6, which is the most recent stable release, is published for both Scala 2.9 & 2.10 (http://mvnrepository.com/artifact/org.squeryl/squeryl_2.10).

    As far as development goes, new feature development has slowed a bit, in large part because the core contributors are pretty happy with the feature set for use in our every day work. Like you pointed out, we’re very active on the discussion list though and pretty quick to address any issues that pop up.

  5. Christian Papauschek

    Thanks for this article, Manuel, looking forward to hearing more about jOOQ as well.

    Working with Slick has to become easier than it is right now – I recently started developing a play-slick SBT plugin for automatic code generation with Play evolutions, because of the boilerplate you mentioned.

  6. Chris Stucchio

    Hi Manuel,

    I tend to agree with the philosophy of Anorm, but in practice I found it hard to use. I wrote my own Anorm-like library which addresses some of the problems I had with Anorm.

    https://github.com/stucchio/tiramisu

    The basic idea of Tiramisu is to treat a SQL query and it’s parameters as a single object. This makes dynamically constructing SQL (via string concatenation like methods) vastly easier.

  7. Pingback: A SQL query DSL for Scala by ScalikeJDBC | Java, SQL and jOOQ.

  8. Alvaro Carrasco

    If you like jooq, you will probably like scoop:
    https://devstack.io/scoop

    It brings features from a lot of libraries, but remains simple (parsers from anorm, general api inspired by squeryl, posos from slick, etc)

    I think it’s cleaner and more consistent than anything else out there.

    Disclosure: I wrote it :)

  9. Pingback: Movio Blog – Slick for Database Access in Scala

  10. Pingback: Slick for Database Access in Scala - Movio Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">