Smooth operator with Slick 3

Or how to use custom / database specific operators in Slick 3.x.

Couple of days ago we had our company Xmas dinner. As we are a software company we obviously talked mostly about technology (surprise!). One of the conversation we had was about a comparison: Slick vs Quill. It was about difficulties of adding customization. Specifically, it was about a problem of adding custom operator that was required for performing certain calculations on database level. As I was recently having a talk about Slick 3.x on BeeScala (slides here) and had an opportunity to dig a little bit deeper into Slick building blocks I felt I could give this problem a try.

Problem

So the problem is: how do you add database specific operator to Slick. Mentioned operator is in this case binary ^ (or XOR). It may be useful for instance if you need to calculate some flavor of Hamming distance on your database for some binary strings (e.g. integers). At least this was the case that we discussed.

Things like that (custom extensions in DSL) are often considered to be hard in Slick. This is often put in contrast to Quill, where there is a lot of flexibility on how you can extend SQL generation. Yet, as I had spent some time digging a little bit deeper into Slick recently, I instantly had a feeling that this is something that should be doable (and without too much of hustle).

Laying out the groundwork

Before we commence:

• all the code is available on GitHub here (together with a small set of unit tests to present to working code).
• I will be using Slick 3.2.0-M2 (which changes few imports but alas not really significantly) and Scala 2.12 (which doesn’t change anything relevant, at least not in the context of our task here).

We need to start with something. To verify my solution works I created following table:

Database table definition

and mapped it into Slick definition (or in other words — created both mixed and unpacked type — as it is called in Slick terminology).

Slick table mapping

After that we are ready to experiment.

Workaround solution

I didn’t know actually how to start with overloading actual database operator (I had some thoughts though — about which later). I did know however that Slick allows you to add custom functions. You can read about it here (Slick docs).

The reasoning behind is this: even if you can’t support some specific database expressions in Slick DSL you often should be able to wrap it within function that you can call later on (assuming it is a scalar function — that is function that returns simple value; this is in contrast to function returning e.g. database cursor which Slick does not support easily within DSL).

Creating such a wrapping function is trivial:

Definition of database function performing XOR

I named it xorf to avoid clash with build-in xor function in MySQL.

The only thing we need to do on Scala side is this definition:

Slick mapping of our XOR function

Now if we put it somewhere in our scope, we can use it like this:

xor function in a projection clause

or like this:

xor function in a selection clause

This Slick query above generates exactly the SQL we would expect:

Cool, we got a fallback solution then. Well, kind of.

We still won’t be able to compile following query:

Query with Int and Option[Int]

It yields following compilation error:

Error:(66, 81) type mismatch;
 found : slick.lifted.Rep[Option[Int]]
 required: slick.lifted.Rep[Int]
 XorTestTable.map(x => xor(x.number1, x.maybeNumber3)).result

Reason? Our function takes two Ints as parameters and return another Int. However we tried to provide Option[Int] as one of the parameters above. But there is an easy fix. Let’s overload our function so we have a version that deals with Options.

Now we would only need to remember to wrap our Int parameter into Option[Int] in case we call xor with mixed parameters (one being pure Int and the other being Option[Int]).

In fact we don’t even need to remember it that much. In case we forget actual compiler error is pretty user-friendly:

Error:(66, 70) method columnToOptionColumn in trait API is deprecated (since 3.0): Use an explicit conversion to an Option column with `.?`
XorTestTable.map(x => xor(x.number1, x.maybeNumber3)).result

Neat!

Still it is only a workaround solution. Let’s try to dig deeper and hopefully introduce our custom operator into Slick DSL.

Proper solution

This one is trickier. I didn’t find much helpful information in Slick docs. However I did once answered a related question on SO (here). It was about UUID but forced me to tinker with profile extensions. And this is precisely where we should turn our attention to. Also it would be reasonable to take a look how existing Slick operators are implemented. Let’s take for example logical operators:

Implementation of logical operators in Slick

That is a nasty piece of Scala library code. Let’s decompose what’s going on here. This BooleanColumnExtensionMethods is used for implicit conversion between Rep[P1] (representation of our column) and a type that implements these new operators ( &&, || and ! in this case). All this is pulled into scope when you import driver/profile API like this:

import slick.jdbc.MySQLProfile.api._

Here is implementation of our Custom MySQL profile:

Extended MySQL profile containing new operator ^

This may be a little too involved so let’s once again decompose what we have here.

First this part below directly mimics what we have in MySQL driver for natively supported operations already mentioned (e.g. &&):

Our column representation will be wrapped with object of BaseIntExtendedIntMethods class which knows how to handle ^ operation.

As always, to add new operation to our type (aka pimp my library), we need to perform implicit conversion. This is done simply by this part:

The only question remaining is how do we bring all of this into scope automatically with our profile (so we don’t need to remember about additional imports every time we use this operation). To achieve this we need to add our implicit conversions to our API:

Parts of the code already discussed before are removed in above sample for clarity. Other than that, it is exactly the code we started with.

Now instead of using regular MySQL profile API import we would need to use our new one in all Slick-related source files:

import ExtendedMySQLProfile.api._

This basically wraps up our solutions.

Let’s see now how Slick query would look like:

Custom ^ operator in Slick

And generated query:

Generated SQL with ^ operator

Perfect! This is precisely what we wanted to achieve. We can even mix both plain Ints and Option[Int] without any modifications:

^ with multiple parameters (Int and Option[Int] mixed together)

Not surprisingly it would generate following SQL:

SQL generated from query with ^ and three parameters

Conclusion

We went through two variants of delivering required functionality:

• wrapping our operator within simple scala SQL function
• adding ^ operator to Slick DSL

Neither of these solutions required much coding nor sophisticated solutions (it is basically couple of lines here and there). It is also worth to emphasize here that they are still pretty much type-safe: our implementation of ^ will only work with Int or Option[Int] — anything else will result in compilation error.

Apparently, contrary perhaps to general opinion, you are able fairly easily to customize some parts of generated SQL in Slick.

Whole code together with unit tests is available here.