How to write a SQL query on Slick and not open the portal to hell

Slick is not only the name of one of the greatest soloists of all time, but also the name of the popular Scala framework for working with databases. This framework professes "functional relational mapping", implements reactive patterns and has the official support of Lightbend. However, developers' reviews about it are, frankly, mixed - many consider it unreasonably complex, and this is partly justified. In this article, I will share my impressions of what you should pay attention to when using it to a novice Scala developer, so that in the process of writing requests you do not accidentally open a portal to hell.

The Slick framework, as often happens in the Scala world, relatively recently underwent a significant redesign - version 3 was sharpened for reactivity and greatly changed the API, making it even more functional than before - and now a large number of articles and responses on StackOverflow designed for version 2, became irrelevant. The framework documentation is rather laconic and is rather a list of examples; conceptual things (in particular, the active use of monads) are explained rather superficially in it. It is assumed that many aspects of functional programming on Scala and advanced features of the language to the developer are already well known.

The result was similar questions on StackOverflow , for which I am now a little ashamed: there I fought over non-compiled code, because I did not understand the architecture of the framework and those monadic patterns that were laid in it. I would like to tell you about these patterns and their use in Slick in this article: it is possible that they will save many hours of torment in trying to write something more complicated than the simplest query.
')

Monads and Query Builder

One of the important components of any type-safe library for working with databases is the query builder, which allows you to form an untyped SQL string from typed code in a programming language. Here is an example of building a query using Slick, taken from the documentation, from the section on “monadic joins” :

val monadicInnerJoin = for { c <- coffees s <- suppliers if c.supID === s.id } yield (c.name, s.name) // compiles to SQL: // select x2."COF_NAME", x3."SUP_NAME" // from "COFFEES" x2, "SUPPLIERS" x3 // where x2."SUP_ID" = x3."SUP_ID" 

I admit, for a newcomer to Scala, this looked rather strange. If you meditate on this code for a long time, you can see the correspondences between this cunning syntactic construction and the SQL query below, into which it is transformed. It seems that something becomes clear: to the right of the arrows in the table, to the left are aliases, after if is a condition, and yield is the fields selected for the projection. Looks like a SQL query turned inside out. But why is the builder implemented that way? What does it have to do with? Is there any iteration over the contents of the tables? Indeed, at this moment we are still not fulfilling the request, but only building it.

Without an understanding of how this construct works, of course, one can get used to such syntax and rivet similar queries by analogy. But when trying to write something more complicated, we risk running into a wall of misunderstanding and spending a lot of time cursing the compiler on what the world is worth, as it was with me at the time. To understand what is hidden behind this magic, and why the query builder is implemented this way, you have to make a small lyrical digression about for-inclusions and monads.

Monads

What is characteristic, in Martin Oderski’s Programming in Scala, the word “monad” is used in one single place - just at the very end of the chapter on for-inclusion, as if in between cases. Most of this chapter is a description of how you can use the syntax for to iterate over a collection, several collections, for filtering. And only at the very end it is said that there is such a thing as a “monad”, which is also convenient to work with using for-inclusion, but there is no detailed explanation of what it is and why. Meanwhile, the use of for-inclusions for operating monads is a very effective and at the same time incomprehensible syntactic construct for the novice look.

I will not give here a full-fledged tutorial on monads, especially since there are a lot of them , and their authors will explain the topic better than me. I can recommend a good video explaining this concept in Scala. For the purposes of this article, we will assume that a monad is a parameterized type, something like a functional wrapper, which has two basic operations with certain properties:

• the return operation wraps (or “lifts”, “lifts”) the value into some context represented by this type;
• bind operation - performs some transforming function on the value in this context.

From the point of view of the authors of the Scala language, in OOP, the return operation is essentially implemented by the instance constructor that takes a value (the constructor just allows to wrap the transferred value into the object), and the bind operation corresponds to the flatMap method. In fact, monads in Scala are not exactly monads in the understanding of classical functional languages ​​like Haskell, but rather “monads in an Oder way”. And although in the classic books on Scala avoid the term "monad", and even in the standard library you can hardly find a mention of this word, the Slick developers feel free to use it in the documentation and code, assuming that the reader already knows what it is.

for-inclusion

In fact, for-comprehension is, of course, not a loop, and the for keyword can be confusing at first. By the way, I tried to figure out how “for-comprehension” translates into Russian - there are options, but there is no generally accepted one. Some controversy on this topic can be read here , here and here .

I dwelt on the term “for-inclusion” because it usually describes the inclusion of elements in the output set according to certain rules. Although, if we consider for-comprehension as monadic comprehension, such a translation becomes not so obvious. Due to the small amount of literature on FP and category theory in Russian, the term is currently not settled.

The irony is that, according to the authors of Programming in Scala, one of the best uses for for-inclusion is combinatorial puzzles:



All this is great and useful, but what about real use cases?

It turns out that the power of the monad pattern, especially in combination with for-inclusion, lies in the fact that it allows you to perform a high-level composition of individual actions in a rather complex context, in other words, to build more complex structures from small cubes (bind / flatMap operations). The for-inclusion syntax makes it possible to build in a sequential chain such actions that cannot actually be performed sequentially. Usually, the complexity of their implementation lies in the presence of a complex context. For example, one of the most commonly used monads in Scala is the List:

  //  val people = List("", "", "") val positions = List("", "", "") //      for-: val peoplePositions = for { person <- people position <- positions } yield s"$person, $position" 

Using for-inclusion on individual instances of the List monad, you can perform a Cartesian product, i.e. composition lists. The monad at the same time hides from us the complexity of the context (iteration over the set of values).

In fact, for-inclusion is just syntactic sugar with strictly defined transformation rules. In particular, all arrows except the last turn into flatMap calls for identifiers on the right, and the last arrow into a map call. The identifiers on the left are transformed into function arguments for the flatMap methods, and the content of the yield is what is returned from the last function.

Therefore, you can write the same thing using the direct call of the flatMap and map methods, but it looks a little less clear, especially if the size and nesting of these structures are several times larger:

  //      flatMap  map: val peoplePositions2 = people.flatMap {person => positions.map { position => s"$person, $position" } } 

Similarly, the monadic implementation of Future allows you to build actions on values ​​in chains, hiding from us the complexity of the context (asynchronous execution of actions and the fact that the calculation of values ​​is deferred):

  //       def getFuture1 = Future { "1337" } //       def getFuture2(string: String) = Future { string.toInt } //  ,    for- val composedFuture = for { result1 <- getFuture1 result2 <- getFuture2(result1) } yield result2 

If we need to pass a parameter to the futur, this can be done using a closure, as shown above: we wrap the futur in a function with an argument and use the argument inside the futur. Due to this, it will be possible to associate separate futures with each other. Accordingly, the “desaccharified” code will look like a set of nested flatMap calls that end with a map call:

  //  ,    flatMap  map val composedFuture2 = getFuture1.flatMap { result1 => getFuture2(result1).map { result2 => result2 } } 

for-inclusion, monads and query building

So, the operation flatMap is a means of composition of monadic objects, or the construction of complex structures from simple bricks. As for the SQL language, there is also a tool for composition there — this is a JOIN clause. If we now return to for-inclusion and its use for building queries, it becomes obvious that flatMap and JOIN have a lot in common, and the mapping of one to another is quite meaningful and reasonable. Let's look again at the example of building a query with an internal join, which was cited at the beginning of the article. Now the idea embedded in this syntax should become somewhat clearer:

 val monadicInnerJoin = for { c <- coffees s <- suppliers if c.supID === s.id } yield (c.name, s.name) 

But here is one of the rough edges of this approach: in SQL there are still left and right joins, and these features do not fit very well on monadic inclusion: there are no syntactic means to express these types of joins in for-inclusion, and for left and right Right joins are encouraged to use alternative syntax - applicative joins . This, by the way, is a big and serious problem of many approaches in Scala, when complex concepts are modeled by means of a language - any means of language have limitations that this concept sooner or later rests on. But about this feature of Scala - some other time.

Moreover, in Slick monads are already used at two levels - in the query designer (as separate query components that can be combined) and when composing actions with the database (they can be combined into complex actions, which are then wrapped into a transaction). Honestly, at first it gave me a lot of problems, because with the help of for-inclusion you can combine both monadic requests and monadic actions, and for a long time I kept my eyes fixed until I learned how to distinguish one monad from another. Monadic actions are the topic of the next chapter ...

Monads and composition of database actions

Enough theory, let's get down to hardcore. Let's try writing something more useful on Slick than a simple query. Let's start again with a query with an internal join:

  val monadicInnerJoin = for { ph <- phones pe <- persons if ph.personId === pe.id } yield (pe.name, ph.number) 

From the result attribute of the resulting value, you can retrieve an object of type DBIOAction - another monad, but already intended for the composition of individual actions performed with databases.

 //    DBIO- val action1 = monadicInnerJoin.result 

Any action, including composite, can be performed as part of a transaction:

 val transactionalAction1 = action1.transactionally 

But what if we need to wrap up a few separate actions in a transaction, some of which are not related to the database at all? The DBIO.successful method will help us with this:

 //  DBIO-  -   val action2 = DBIO.successful { println(" -    ...") } 

By the way, if you wrap the creation of an action into a function with an argument, you can, as is the case with the futures above, parameterize this action, but we will not do that. Instead, we simply add a couple more DBIO actions to the mix to insert data into the tables and put all this into a composite action using for-inclusion:

 //   DBIO-... val action3 = persons += (1, "Grace") val action4 = phones += (1, 1, "+1 (800) FUC-KYOU") //        val compositeAction = for { result <- action1 _ <- action2 personCount <- action3 phoneCount <- action4 } yield personCount + phoneCount 

Please note - if the result of the action does not interest us (it is performed for the sake of a side effect), then an underscore can be put to the left of the arrow. Now we wrap the composite action in a transaction and create a futur based on it:

 //           val actionFuture = db.run(compositeAction.transactionally) 

Finally, we will build this future with another future with the help of the omnipotent for and wait for it to be executed using Await.result (by the way, this approach is only suitable for tests, do not repeat this in production - use end-to-end asynchrony)

 val databaseFuture = for { i <- actionFuture _ <- Future { println(s" : $i") } } yield () Await.result(databaseFuture, 1 second) 

That's how simple it is.

Conclusion

Monads and for-inclusion syntax are often used in various Scala-libraries to build large structures of small bricks. In Slick alone, they can be used in at least three different places — to assemble tables into a query, assemble actions into one large action, and assemble futures into one large futur. Understanding the Slick philosophy and facilitating working with it is very helpful in understanding how for-inclusion works, what monads are, and how for-inclusion facilitates working with monads.

I hope this article will help newcomers to Scala and Slick not to despair and curb the full power of this framework. The source code for the article is available on GitHub .