What we learned from using Slick
At Powerspace, our stack is constantly evolving.
We are offering a web platform where our clients set up their advertising campaigns, as well as a set of distributed applications that serve non-intrusive ads in real time (Ad Servers).
Until now, these AdServers were reading from a NoSQL store, while the web platform wrote toPostgreSQL. It implied to keep them in sync, which was a common cause of errors. We wanted to improve this, and let AdServers read straight from PostgreSQL.
Then came Slick
Slick is a Scala library that executes SQL queries using Scala code. It provides static checking, compilation-time safety and compositionality to our queries. That way, no need to write into two databases anymore. The point of failure is removed.
Here is some Scala code to retrieve an entity:
It returns an Option[AdTemplate]. Notice we used .headOption instead of .head, because we know it can be null.
Set up Slick
These steps are also well described on Lightbend website for slick. If you have any troubles, you can refer to Lightbend’s getting started guide
Then we need to add some configuration to give Slick all database information. Also, we use slick-hikaricp to handle the connection pool (we set a maximum connection pool of 5). It is very recommended to use a connection pooling manager, as Slick tend to be slow without one. Also, opening and closing connection manually can be a pain.
With the configuration done, we need to write the database schema. This will be used by Slick to read/write from/to the database. This can be automaticaly generated via codegen, but we didn’t need this feature as we did not need the whole database for our use case.
And that’s all. You can now run your slick query, by passing a Database object. To open the connection, you just have to do:
val db = Database.forConfig(“db.config”)Joining with slick
Our first example was a pretty basic one. In reality, we need have a lot of relations in our database. We will now proceed with other examples using join.
One to One
In our previous example, we used the Template entity. This entity is a child of a Placement, which is a slot where our ads are served. Let’s say, for the sake of this example, that a position must have at most one template. Let’s see how we can get the placement and its template.
We need to create the database schema and the model for a placement
To modelize the join and make the template a child of a placement, we need to add def placement = foreignKey(“placement_fk”, placementId, placements)(_.id) as a field of the Template table
Here, we use the field positionId of Template to map it to a TableQuery[Placement] by it’s primary key id. This mapping is done on the query level, this will enable easy to understand query like this one:
Be careful though. If the Template does exist, but it has no placement, there will be no returned value.
Many to One
Now in reality, one placement can have multiple templates. Yet, we can still get them the same way with the matching placement. It would look like that.
But what if we want to get every Template for a given placement? There are 3 possibilities for that.
- Filter the result on the previous query. Not recommanded if the table are big because you get a sequence size of size(placement_table) * n, n being the number of Template by placement.
- Filter the previous query on the placement. This is quite easy to do however you won’t get the placement if there are no template.
- Do a left/right join query. This will return a Seq[(Placement, Option[Template])
Right and left joins
If you are not familiar with it, take a look at that tutorial. At the bottom of the page, you’ll find a nice schema of the different joins available. In my example, I will only do a LEF JOIN, but you can perfectly do it with a RIGHT JOIN too.
Pretty straightforward.
A more advanced query
Imagine now that we have a Website entity. This entity can have multiple placements. So what if we want to get an object that encapsulates the Website and the list of its templates?
As you can see, queries can quickly become difficult to read with this notation. When looking on GitHub for example, we found some really unreadeable one like this one.
Please note that this is Slick 2, not 3. In Slick 3, a joinLeft will now give an option, so you can’t do that anymore. There are implicits get/getOrElse method, but for some reason I can’t use them.
One thing to make clearer queries is to expand the expression. Taking our encapsulated query, we can also write it this way.
There are still a lot of parenthesis that you have to deal with, but at least it’s readable.
Yet, a better way is to separate this kind of queries, because the combination of multiple tables will drastically increase the number of results. For instance, the combination of placements and websites gives more than 4k results. I once had a query that returned more than 60k lines of result, and that is slower than just doing multiples queries. I eventually had to break it down to 3 different queries to get it to work fine and fast.
Debugging
To debug the queries, you can add this to your logback.xml
Testing
We use h2 for test. You just need to modify db.config to whatever driver you need, with the right URL. You can also create your database using a basic SQL string.
Quick tips
If you have trouble making a slick query, write it in SQL first
Sometimes you will understand that your query is way too complicated, and that you might need more than one to do the job
To optimize the query, always look at the generated sql
Slick generates excellent SQL code for easy queries, but it is rapidly unreadeable in more complex cases. The way Scala code is written can dramatically change the way the SQL is generated.
Use codegen if you need to write
In our use case, we did not need it, because we are only reading from it. But to be able to write, you need to respect all the rules of your database, which can be tedious to write by hand.
When seeking help, check the Slick version
Many times while browsing StackOverflow, I have found examples on previous versions of slick that were not compatible with 3.1 or higher.
Sometimes, Intellij gives bad typing
When trying to expand val templates = TableQuery[AdTemplate], IntelliJ will give you something like
val templates: QueryBase[Seq[AdTemplate]] = TableQuery[AdTemplate] which is wrong and won’t compile. The right type is val templates: TableQuery[Tables.AdTemplate] = TableQuery[AdTemplate]
I hope this will help you to bring types queries to your scala code !
We are looking for Scala profiles (realtime, data engineering) in Paris to work on cutting-edge technologies and infrastructure. Drop us an email if it sounds like a match: jobs@powerspace.com
