Essential Slick

Richard Dallaway and Jonathan Ferguson

Preface

What is Slick?

Slick is a Scala library for working with relational databases. That means it allows you to model a schema, run queries, insert data, and update data.

Using Slick you write queries in Scala and they are type checked by the compiler. This makes working with a database like working with regular Scala collections.

We’ve seen that developers using Slick for the first time often need help getting the most from it. For example, key concepts that need to be known include:

We’ve produced Essential Slick as a guide for those who want to get started using Slick. This material is aimed at beginner-to-intermediate Scala developers. You need:

The material presented focuses on Slick version 3.2. Examples use H2 as the relational database.

How to Contact Us

You can provide feedback on this text via:

The Underscore Newsletter contains announcements regarding this and other publications from Underscore.

You can follow us on Twitter as @underscoreio.

Acknowledgements

Many thanks to Renato Cavalcanti, Dave Gurnell, Kevin Meredith, Joseph Ottinger, Yann Simon, Trevor Sibanda, Matthias Braun, Konstantine Gadyrka, and the team at Underscore for their invaluable contributions and proof reading.

And of course huge thanks to the Slick team for creating such a cool piece of software.

Backers

We’d also like to extend special thanks to our backers—fine people who helped fund the development of the book by buying a copy before we released it as open source. This book wouldn’t exist without you:

@moliware, Aaron remick, Aguinaldo Possatto, Akash Suresh, alfogator, Antonio Pacheco, ashugupt, Bar Shirtcliff, barabum, Brandon Hudgeons, Brian M. Clapper, Brian Schlining, Calvin Fernandes, Ceschiatti (@6qat), Chris Llanwarne, Craig Tataryn, Daniel Billsus, David Sinclair, Dennis Vriend, Dimitrios Liapis, Dirk Forier, Doug Clinton, Elvis5566, Enrique Rodríguez, Fabian, Ganesh Chand, Geoffrey Gilmore, George Ball, Graeme Ludwig, Harish Hurchurn, Igo Brilhante, Igor Lengvarsky, Ivano Pagano, James Shade, Jamoliddin Daliev, Jeff Gentry, Jeremy Smith, Jonathan Steinhart, Jules Ivanic, Karl-Aksel Puulmann, Keith Mannock, kipperjim, Kristof Jozsa, Marianudo (Mariano Navas), Martin Kneissl, DASGIP GmbH, Matthew Edwards, Matthew Pflueger, Matthias Braun, Matthias Trüb, Me in the book!, Miguel A. Baldi Hörlle, nanitous, Navid Mohaghegh, Nicolas F. Rouquette, Nicolas Sitbon, Nikita Moshensky, P7h, Pascal Rodriguez, Pawel Wlodarski from JUG Lodz, prasadm80@gmail.com, Puneet, Puneet Jhajj Bains, Remco Bos, Richard Opsal, Richard Searle, Robert Cheetham, Azavea, Robin Taylor (@badgermind), Ruslan Kharitonov, Sander Zuidema, Sarav Ramaswamy, Seoh Char, SJ Yoon, Sören Brunk, Spockz, Stein Fletcher, Thibault Fouache, Thomas Toye, Tobias Lutz, Tom Hobbs (Arxality Ltd), Tony Murphy, Vladimir Bacvanski, Wojciech Langiewicz, Zurab Kakabadze.

Conventions Used in This Book

This book contains a lot of technical information and program code. We use the following typographical conventions to reduce ambiguity and highlight important concepts:

Typographical Conventions

New terms and phrases are introduced in italics. After their initial introduction they are written in normal roman font.

Terms from program code, filenames, and file contents, are written in monospace font.

References to external resources are written as hyperlinks. References to API documentation are written using a combination of hyperlinks and monospace font, for example: scala.Option.

Source Code

Source code blocks are written as follows. Syntax is highlighted appropriately where applicable:

object MyApp extends App {
  println("Hello world!") // Print a fine message to the user!
}

REPL Output

We use Scala comments to show REPL output. For example:

2 * 13
// res0: Int = 26

If you’re following along with the REPL, and copy and paste from the book we hope this will be useful. It means if you accidentally copy more than you intended, the REPL will ignore the commented output.

We use the wonderful tut to compile the vast majority of code in this text. The REPL output is wrapped by LaTeX. This can be tricky to read, especially with long type signatures. So in some places we also duplicate and reformat the output. But the best way is to try the code out in the REPL for yourself.

Callout Boxes

We use three types of callout box to highlight particular content:

Tip callouts indicate handy summaries, recipes, or best practices.

Advanced callouts provide additional information on corner cases or underlying mechanisms. Feel free to skip these on your first read-through—come back to them later for extra information.

Warning callouts indicate common pitfalls and gotchas. Make sure you read these to avoid problems, and come back to them if you’re having trouble getting your code to run.

1 Basics

1.1 Orientation

Slick is a Scala library for accessing relational databases using an interface similar to the Scala collections library. You can treat queries like collections, transforming and combining them with methods like map, flatMap, and filter before sending them to the database to fetch results. This is how we’ll be working with Slick for the majority of this text.

Standard Slick queries are written in plain Scala. These are type safe expressions that benefit from compile time error checking. They also compose, allowing us to build complex queries from simple fragments before running them against the database. If writing queries in Scala isn’t your style, you’ll be pleased to know that Slick also allows you to write plain SQL queries.

In addition to querying, Slick helps you with all the usual trappings of relational database, including connecting to a database, creating a schema, setting up transactions, and so on. You can even drop down below Slick to deal with JDBC (Java Database Connectivity) directly, if that’s something you’re familiar with and find you need.

This book provides a compact, no-nonsense guide to everything you need to know to use Slick in a commercial setting:

Slick isn’t an ORM

If you’re familiar with other database libraries such as Hibernate or Active Record, you might expect Slick to be an Object-Relational Mapping (ORM) tool. It is not, and it’s best not to think of Slick in this way.

ORMs attempt to map object oriented data models onto relational database backends. By contrast, Slick provides a more database-like set of tools such as queries, rows and columns. We’re not going to argue the pros and cons of ORMs here, but if this is an area that interests you, take a look at the Coming from ORM to Slick article in the Slick manual.

If you aren’t familiar with ORMs, congratulations. You already have one less thing to worry about!

1.2 Running the Examples and Exercises

The aim of this first chapter is to provide a high-level overview of the core concepts involved in Slick, and get you up and running with a simple end-to-end example. You can grab this example now by cloning the Git repo of exercises for this book:

bash$ git clone git@github.com:underscoreio/essential-slick-code.git
Cloning into 'essential-slick-code'...

bash$ cd essential-slick-code

bash$ ls -1
README.md
chapter-01
chapter-02
chapter-03
chapter-04
chapter-05
chapter-06
chapter-07

Each chapter of the book is associated with a separate sbt project that provides a combination of examples and exercises. We’ve bundled everything you need to run sbt in the directory for each chapter.

We’ll be using a running example of a chat application similar to Slack, Gitter, or IRC. The app will grow and evolve as we proceed through the book. By the end it will have users, messages, and rooms, all modelled using tables, relationships, and queries.

For now, we will start with a simple conversation between two famous celebrities. Change to the chapter-01 directory now, use the sbt.sh script to start sbt, and compile and run the example to see what happens:

bash$ cd chapter-01

bash$ ./sbt.sh
# sbt log messages...

> compile
# More sbt log messages...

> run
Creating database table

Inserting test data

Selecting all messages:
Message("Dave","Hello, HAL. Do you read me, HAL?",1)
Message("HAL","Affirmative, Dave. I read you.",2)
Message("Dave","Open the pod bay doors, HAL.",3)
Message("HAL","I'm sorry, Dave. I'm afraid I can't do that.",4)

Selecting only messages from HAL:
Message("HAL","Affirmative, Dave. I read you.",2)
Message("HAL","I'm sorry, Dave. I'm afraid I can't do that.",4)

If you get output similar to the above, congratulations! You’re all set up and ready to run with the examples and exercises throughout the rest of this book. If you encounter any errors, let us know on our Gitter channel and we’ll do what we can to help out.

New to sbt?

The first time you run sbt, it will download a lot of library dependencies from the Internet and cache them on your hard drive. This means two things:

  • you need a working Internet connection to get started; and
  • the first compile command you issue could take a while to complete.

If you haven’t used sbt before, you may find the sbt Getting Started Guide useful.

1.3 Working Interactively in the sbt Console

Slick queries run asynchronously as Future values. These are fiddly to work with in the Scala REPL, but we do want you to be able to explore Slick via the REPL. So to get you up to speed quickly, the example projects define an exec method and import the base requirements to run examples from the console.

You can see this by starting sbt and then running the console command. Which will give output similar to:

> console
[info] Starting scala interpreter...
[info]
Welcome to Scala 2.12.1 (Java HotSpot(TM) 64-Bit Server VM, Java 1.8.0_112).
Type in expressions for evaluation. Or try :help.

scala> import slick.jdbc.H2Profile.api._
import Example._
import scala.concurrent.duration._
import scala.concurrent.Await
import scala.concurrent.ExecutionContext.Implicits.global
db: slick.jdbc.H2Profile.backend.Database = slick.jdbc.JdbcBackend$DatabaseDef@ac9a820
exec: [T](program: slick.jdbc.H2Profile.api.DBIO[T])T
res0: Option[Int] = Some(4)
scala>

Our exec helper runs a query and waits for the output. There is a complete explanation of exec and these imports later in the chapter. For now, here’s a small example which fetches all the message rows:

exec(messages.result)
// res1: Seq[Example.MessageTable#TableElementType] =
// Vector(Message(Dave,Hello, HAL. Do you read me, HAL?,1),
//       Message(HAL,Affirmative, Dave. I read you.,2),
//       Message(Dave,Open the pod bay doors, HAL.,3),
//       Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,4))

But we’re getting ahead of ourselves. We’ll work though building up queries and running them, and using exec, as we work through this chapter. If the above works for you, great—you have a development environment set up and ready to go.

1.4 Example: A Sequel Odyssey

The test application we saw above creates an in-memory database using H2, creates a single table, populates it with test data, and then runs some example queries. The rest of this section will walk you through the code and provide an overview of things to come. We’ll reproduce the essential parts of the code in the text, but you can follow along in the codebase for the exercises as well.

Choice of Database

All of the examples in this book use the H2 database. H2 is written in Java and runs in-process beside our application code. We’ve picked H2 because it allows us to forego any system administration and skip to writing Scala.

You might prefer to use MySQL, PostgreSQL, or some other database—and you can. In Appendix A we point you at the changes you’ll need to make to work with other databases. However, we recommend sticking with H2 for at least this first chapter so you can build confidence using Slick without running into database-specific complications.

1.4.1 Library Dependencies

Before diving into Scala code, let’s look at the sbt configuration. You’ll find this in build.sbt in the example:

name := "essential-slick-chapter-01"

version := "1.0.0"

scalaVersion := "2.12.3"

libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick"           % "3.2.1",
  "com.h2database"      % "h2"              % "1.4.185",
  "ch.qos.logback"      % "logback-classic" % "1.1.2"
)

This file declares the minimum library dependencies for a Slick project:

If we were using a separate database like MySQL or PostgreSQL, we would substitute the H2 dependency for the JDBC driver for that database.

1.4.2 Importing Library Code

Database management systems are not created equal. Different systems support different data types, different dialects of SQL, and different querying capabilities. To model these capabilities in a way that can be checked at compile time, Slick provides most of its API via a database-specific profile. For example, we access most of the Slick API for H2 via the following import:

import slick.jdbc.H2Profile.api._

Slick makes heavy use of implicit conversions and extension methods, so we generally need to include this import anywhere where we’re working with queries or the database. Chapter 5 looks how you can keep a specific database profile out of your code until necessary.

1.4.3 Defining our Schema

Our first job is to tell Slick what tables we have in our database and how to map them onto Scala values and types. The most common representation of data in Scala is a case class, so we start by defining a Message class representing a row in our single example table:

final case class Message(
  sender:  String,
  content: String,
  id:      Long = 0L)
// defined class Message

Next we define a Table object, which corresponds to our database table and tells Slick how to map back and forth between database data and instances of our case class:

final class MessageTable(tag: Tag) extends Table[Message](tag, "message") {

  def id      = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def sender  = column[String]("sender")
  def content = column[String]("content")

  def * = (sender, content, id).mapTo[Message]
}
// defined class MessageTable

MessageTable defines three columns: id, sender, and content. It defines the names and types of these columns, and any constraints on them at the database level. For example, id is a column of Long values, which is also an auto-incrementing primary key.

The * method provides a default projection that maps between columns in the table and instances of our case class. Slick’s mapTo macro creates a two-way mapping between the three columns and the three fields in Message.

We’ll cover projections and default projections in detail in Chapter 5. For now, all we need to know is that this line allows us to query the database and get back Messages instead of tuples of (String, String, Long).

The tag on the first line is an implementation detail that allows Slick to manage multiple uses of the table in a single query. Think of it like a table alias in SQL. We don’t need to provide tags in our user code—Slick takes case of them automatically.

1.4.4 Example Queries

Slick allows us to define and compose queries in advance of running them against the database. We start by defining a TableQuery object that represents a simple SELECT * style query on our message table:

val messages = TableQuery[MessageTable]
// messages: slick.lifted.TableQuery[MessageTable] = Rep(TableExpansion)

Note that we’re not running this query at the moment—we’re simply defining it as a means to build other queries. For example, we can create a SELECT * WHERE style query using a combinator called filter:

val halSays = messages.filter(_.sender === "HAL")
// halSays: slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq] = Rep(Filter @444067158)

Again, we haven’t run this query yet—we’ve defined it as a building block for yet more queries. This demonstrates an important part of Slick’s query language—it is made from composable elements that permit a lot of valuable code re-use.

Lifted Embedding

If you’re a fan of terminology, know that what we have discussed so far is called the lifted embedding approach in Slick:

  • define data types to store row data (case classes, tuples, or other types);
  • define Table objects representing mappings between our data types and the database;
  • define TableQueries and combinators to build useful queries before we run them against the database.
Lifted embedding is the standard way to work with Slick. We will discuss the other approach, called Plain SQL querying, in Chapter 7.

1.4.5 Configuring the Database

We’ve written all of the code so far without connecting to the database. Now it’s time to open a connection and run some SQL. We start by defining a Database object which acts as a factory for managing connections and transactions:

val db = Database.forConfig("chapter01")
// db: slick.jdbc.H2Profile.backend.Database = slick.jdbc.JdbcBackend$DatabaseDef@2d10e08f

The parameter to Database.forConfig determines which configuration to use from the application.conf file. This file is found in src/main/resources. It looks like this:

chapter01 {
  driver = "org.h2.Driver"
  url    = "jdbc:h2:mem:chapter01"
  keepAliveConnection = true
  connectionPool = disabled
}

This syntax comes from the Typesafe Config library, which is also used by Akka and the Play framework.

The parameters we’re providing are intended to configure the underlying JDBC layer. The driver parameter is the fully qualified class name of the JDBC driver for our chosen DBMS.

The url parameter is the standard JDBC connection URL, and in this case we’re creating an in-memory database called "chapter01".

By default the H2 in-memory database is deleted when the last connection is closed. As we will be running multiple connections in our examples, we enable keepAliveConnection to keep the data around until our program completes.

Slick manages database connections and transactions using auto-commit. We’ll look at transactions in Chapter 4.

JDBC

If you don’t have a background working with Java, you may not have heard of Java Database Connectivity (JDBC). It’s a specification for accessing databases in a vendor neutral way. That is, it aims to be independent of the specific database you are connecting to.

The specification is mirrored by a library implemented for each database you want to connect to. This library is called the JDBC driver.

JDBC works with connection strings, which are URLs like the one above that tell the driver where your database is and how to connect to it (e.g. by providing login credentials).

1.4.6 Creating the Schema

Now that we have a database configured as db, we can use it.

Let’s start with a CREATE statement for MessageTable, which we build using methods of our TableQuery object, messages. The Slick method schema gets the schema description. We can see what that would be via the createStatements method:

messages.schema.createStatements.mkString
// res0: String = create table "message" ("sender" VARCHAR NOT NULL,"content" VARCHAR NOT NULL,"id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT)

But we’ve not sent this to the database yet. We’ve just printed the statement, to check it is what we think it should be.

In Slick, what we run against the database is an action. This is how we create an action for the messages schema:

val action: DBIO[Unit] = messages.schema.create
// action: slick.jdbc.H2Profile.api.DBIO[Unit] = slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@72c479a9

The result of this messages.schema.create expression is a DBIO[Unit]. This is an object representing a DB action that, when run, completes with a result of type Unit. Anything we run against a database is a DBIO[T] (or a DBIOAction, more generally). This includes queries, updates, schema alterations, and so on.

DBIO and DBIOAction

In this book we will talk about actions as having the type DBIO[T].

This is a simplification. The more general type is DBIOAction, and specifically for this example, it is a DBIOAction[Unit, NoStream, Effect.Schema]. The details of all of this we will get to later in the book.

But DBIO[T] is a type alias supplied by Slick, and is perfectly fine to use.

Let’s run this action:

import scala.concurrent.Future
val future: Future[Unit] = db.run(action)
// future: scala.concurrent.Future[Unit] = Future(<not completed>)

The result of run is a Future[T], where T is the type of result returned by the database. Creating a schema is a side-effecting operation so the result type is Future[Unit]. This matches the type DBIO[Unit] of the action we started with.

Futures are asynchronous. That’s to say, they are placeholders for values that will eventually appear. We say that a future completes at some point. In production code, futures allow us to chain together computations without blocking to wait for a result. However, in simple examples like this we can block until our action completes:

import scala.concurrent.Await
import scala.concurrent.duration._
val result = Await.result(future, 2.seconds)
// result: Unit = ()

1.4.7 Inserting Data

Once our table is set up, we need to insert some test data. We’ll define a helper method to create a few test Messages for demonstration purposes:

def freshTestData = Seq(
  Message("Dave", "Hello, HAL. Do you read me, HAL?"),
  Message("HAL",  "Affirmative, Dave. I read you."),
  Message("Dave", "Open the pod bay doors, HAL."),
  Message("HAL",  "I'm sorry, Dave. I'm afraid I can't do that.")
)
// freshTestData: Seq[Message]

The insert of this test data is an action:

val insert: DBIO[Option[Int]] = messages ++= freshTestData
// insert: slick.jdbc.H2Profile.api.DBIO[Option[Int]] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$MultiInsertAction@44f4bf7d

The ++= method of message accepts a sequence of Message objects and translates them to a bulk INSERT query (freshTestData is a regular Scala Seq[Message]). We run the insert via db.run, and when the future completes our table is populated with data:

val result: Future[Option[Int]] = db.run(insert)
// result: scala.concurrent.Future[Option[Int]] = Future(<not completed>)

The result of an insert operation is the number of rows inserted. The freshTestData contains four messages, so in this case the result is Some(4) when the future completes:

val rowCount = Await.result(result, 2.seconds)
// rowCount: Option[Int] = Some(4)

The result is optional because the underlying Java APIs do not guarantee a count of rows for batch inserts—some databases simply return None. We discuss single and batch inserts and updates further in Chapter 3.

1.4.8 Selecting Data

Now our database has a few rows in it, we can start selecting data. We do this by taking a query, such as messages or halSays, and turning it into an action via the result method:

val messagesAction: DBIO[Seq[Message]] = messages.result
// messagesAction: slick.jdbc.H2Profile.api.DBIO[Seq[Message]] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1@2f1cfcf2

val messagesFuture: Future[Seq[Message]] = db.run(messagesAction)
// messagesFuture: scala.concurrent.Future[Seq[Message]] = Future(<not completed>)

val messagesResults = Await.result(messagesFuture, 2.seconds)
// messagesResults: Seq[Message] = Vector(Message(Dave,Hello, HAL. Do you read me, HAL?,1), Message(HAL,Affirmative, Dave. I read you.,2), Message(Dave,Open the pod bay doors, HAL.,3), Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,4))

We can see the SQL issued to H2 using the statements method on the action:

val sql = messages.result.statements.mkString
// sql: String = select "sender", "content", "id" from "message"

The exec Helper Method

In our applications we should avoid blocking on Futures whenever possible. However, in the examples in this book we’ll be making heavy use of Await.result. We will introduce a helper method called exec to make the examples easier to read:

def exec[T](action: DBIO[T]): T =
  Await.result(db.run(action), 2.seconds)
// exec: [T](action: slick.jdbc.H2Profile.api.DBIO[T])T

All exec does is run the supplied action and wait for the result. For example, to run a select query we can write:

exec(messages.result)

Use of Await.result is strongly discouraged in production code. Many web frameworks provide direct means of working with Futures without blocking. In these cases, the best approach is simply to transform the Future query result to a Future of an HTTP response and send that to the client.

If we want to retrieve a subset of the messages in our table, we can run a modified version of our query. For example, calling filter on messages creates a modified query with a WHERE expression that retrieves the expected rows:

messages.filter(_.sender === "HAL").result.statements.mkString
// res3: String = select "sender", "content", "id" from "message" where "sender" = 'HAL'

To run this query, we convert it to an action using result, run it against the database with db.run, and await the final result with exec:

exec(messages.filter(_.sender === "HAL").result)
// res4: Seq[MessageTable#TableElementType] = Vector(Message(HAL,Affirmative, Dave. I read you.,2), Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,4))

We actually generated this query earlier and stored it in the variable halSays. We can get exactly the same results from the database by running this variable instead:

exec(halSays.result)
// res5: Seq[MessageTable#TableElementType] = Vector(Message(HAL,Affirmative, Dave. I read you.,2), Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,4))

Notice that we created our original halSays before connecting to the database. This demonstrates perfectly the notion of composing a query from small parts and running it later on.

We can even stack modifiers to create queries with multiple additional clauses. For example, we can map over the query to retrieve a subset of the columns. This modifies the SELECT clause in the SQL and the return type of the result:

halSays.map(_.id).result.statements.mkString
// res6: String = select "id" from "message" where "sender" = 'HAL'

exec(halSays.map(_.id).result)
// res7: Seq[Long] = Vector(2, 4)

1.4.9 Combining Queries with For Comprehensions

Query is a monad. It implements the methods map, flatMap, filter, and withFilter, making it compatible with Scala for comprehensions. For example, you will often see Slick queries written in this style:

val halSays2 = for {
  message <- messages if message.sender === "HAL"
} yield message
// halSays2: slick.lifted.Query[MessageTable,Message,Seq] = Rep(Bind)

Remember that for comprehensions are aliases for chains of method calls. All we are doing here is building a query with a WHERE clause on it. We don’t touch the database until we execute the query:

exec(halSays2.result)
// res8: Seq[Message] = Vector(Message(HAL,Affirmative, Dave. I read you.,2), Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,4))

1.4.10 Actions Combine

Like Query, DBIOAction is also a monad. It implements the same methods described above, and shares the same compatibility with for comprehensions.

We can combine the actions to create the schema, insert the data, and query results into one action. We can do this before we have a database connection, and we run the action like any other. To do this, Slick provides a number of useful action combinators. We can use andThen, for example:

val actions: DBIO[Seq[Message]] = (
  messages.schema.create       andThen
  (messages ++= freshTestData) andThen
  halSays.result
)
// actions: slick.jdbc.H2Profile.api.DBIO[Seq[Message]] = slick.dbio.SynchronousDatabaseAction$FusedAndThenAction@606e754b

What andThen does is combine two actions so that the result of the first action is thrown away. The end result of the above actions is the last action in the andThen chain.

If you want to get funky, >> is another name for andThen:

val sameActions: DBIO[Seq[Message]] = (
  messages.schema.create       >>
  (messages ++= freshTestData) >>
  halSays.result
)
// sameActions: slick.jdbc.H2Profile.api.DBIO[Seq[Message]] = slick.dbio.SynchronousDatabaseAction$FusedAndThenAction@f1ee53f

Combining actions is an important feature of Slick. For example, one reason for combining actions is to wrap them inside a transaction. In Chapter 4 we’ll see this, and also that actions can be composed with for comprehensions, just like queries.

Queries, Actions, Futures… Oh My!

The difference between queries, actions, and futures is a big point of confusion for newcomers to Slick 3. The three types share many properties: they all have methods like map, flatMap, and filter, they are all compatible with for comprehensions, and they all flow seamlessly into one another through methods in the Slick API. However, their semantics are quite different:

  • Query is used to build SQL for a single query. Calls to map and filter modify clauses to the SQL, but only one query is created.

  • DBIOAction is used to build sequences of SQL queries. Calls to map and filter chain queries together and transform their results once they are retrieved in the database. DBIOAction is also used to delineate transactions.

  • Future is used to transform the asynchronous result of running a DBIOAction. Transformations on Futures happen after we have finished speaking to the database.

In many cases (for example select queries) we create a Query first and convert it to a DBIOAction using the result method. In other cases (for example insert queries), the Slick API gives us a DBIOAction immediately, bypassing Query. In all cases, we run a DBIOAction using db.run(...), turning it into a Future of the result.

We recommend taking the time to thoroughly understand Query, DBIOAction, and Future. Learn how they are used, how they are similar, how they differ, what their type parameters represent, and how they flow into one another. This is perhaps the single biggest step you can take towards demystifying Slick 3.

1.5 Take Home Points

In this chapter we’ve seen a broad overview of the main aspects of Slick, including defining a schema, connecting to the database, and issuing queries to retrieve data.

We typically model data from the database as case classes and tuples that map to rows from a table. We define the mappings between these types and the database using Table classes such as MessageTable.

We define queries by creating TableQuery objects such as messages and transforming them with combinators such as map and filter. These transformations look like transformations on collections, but they are used to build SQL code rather than manipulate the results returned.

We execute a query by creating an action object via its result method. Actions are used to build sequences of related queries and wrap them in transactions.

Finally, we run the action against the database by passing it to the run method of the database object. We are given back a Future of the result. When the future completes, the result is available.

The query language is the one of the richest and most significant parts of Slick. We will spend the entire next chapter discussing the various queries and transformations available.

1.6 Exercise: Bring Your Own Data

Let’s get some experience with Slick by running queries against the example database. Start sbt using sbt.sh and type console to enter the interactive Scala console. We’ve configured sbt to run the example application before giving you control, so you should start off with the test database set up and ready to go:

bash$ ./sbt.sh
# sbt logging...

> console
# More sbt logging...
# Application runs...

scala>

Start by inserting an extra line of dialog into the database. This line hit the cutting room floor late in the development of the film 2001, but we’re happy to reinstate it here:

Message("Dave","What if I say 'Pretty please'?")
// res9: Message = Message(Dave,What if I say 'Pretty please'?,0)

You’ll need to insert the row using the += method on messages. Alternatively you could put the message in a Seq and use ++=. We’ve included some common pitfalls in the solution in case you get stuck.

Here’s the solution:

exec(messages += Message("Dave","What if I say 'Pretty please'?"))
// res10: Int = 1

The return value indicates that 1 row was inserted. Because we’re using an auto-incrementing primary key, Slick ignores the id field for our Message and asks the database to allocate an id for the new row. It is possible to get the insert query to return the new id instead of the row count, as we shall see next chapter.

Here are some things that might go wrong:

If you don’t pass the action created by += to db to be run, you’ll get back the Action object instead.

messages += Message("Dave","What if I say 'Pretty please'?")
// res11: slick.sql.FixedSqlAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction@5ad598f1

If you don’t wait for the future to complete, you’ll see just the future itself:

val f = db.run(messages += Message("Dave","What if I say 'Pretty please'?"))
// f: scala.concurrent.Future[Int] = Future(<not completed>)

Now retrieve the new dialog by selecting all messages sent by Dave. You’ll need to build the appropriate query using messages.filter, and create the action to be run by using its result method. Don’t forget to run the query by using the exec helper method we provided.

Again, we’ve included some common pitfalls in the solution.

Here’s the code:

exec(messages.filter(_.sender === "Dave").result)
// res20: Seq[MessageTable#TableElementType] = Vector(Message(Dave,Hello, HAL. Do you read me, HAL?,1), Message(Dave,Open the pod bay doors, HAL.,3), Message(Dave,What if I say 'Pretty please'?,5))

If that’s hard to read, we can print each message in turn. As the Future will evaluate to a collection of Message, we can foreach over that with a function of Message => Unit, such as println:

val result: Seq[Message] = exec(messages.filter(_.sender === "Dave").result)
// result: Seq[Message] = Vector(Message(Dave,Hello, HAL. Do you read me, HAL?,1), Message(Dave,Open the pod bay doors, HAL.,3), Message(Dave,What if I say 'Pretty please'?,5))

result.foreach(println)
// Message(Dave,Hello, HAL. Do you read me, HAL?,1)
// Message(Dave,Open the pod bay doors, HAL.,3)
// Message(Dave,What if I say 'Pretty please'?,5)

Here are some things that might go wrong:

Note that the parameter to filter is built using a triple-equals operator, ===, not a regular ==. If you use == you’ll get an interesting compile error:

exec(messages.filter(_.sender == "Dave").result)
// Nothing <: Boolean?
// true
// Boolean <: slick.lifted.Rep[_]?
// false
// <console>:24: error: inferred type arguments [Boolean] do not conform to method filter's type parameter bounds [T <: slick.lifted.Rep[_]]
//        exec(messages.filter(_.sender == "Dave").result)
//                      ^
// <console>:24: error: type mismatch;
//  found   : MessageTable => Boolean
//  required: MessageTable => T
//        exec(messages.filter(_.sender == "Dave").result)
//                                      ^
// MessageTable => Boolean <: MessageTable => T?
// false
// <console>:24: error: Type T cannot be a query condition (only Boolean, Rep[Boolean] and Rep[Option[Boolean]] are allowed
//        exec(messages.filter(_.sender == "Dave").result)
//                            ^

The trick here is to notice that we’re not actually trying to compare _.sender and "Dave". A regular equality expression evaluates to a Boolean, whereas === builds an SQL expression of type Rep[Boolean] (Slick uses the Rep type to represent expressions over Columns as well as Columns themselves). The error message is baffling when you first see it but makes sense once you understand what’s going on.

Finally, if you forget to call result, you’ll end up with a compilation error as exec and the call it is wrapping db.run both expect actions:

exec(messages.filter(_.sender === "Dave"))
// <console>:24: error: type mismatch;
//  found   : slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq]
//     (which expands to)  slick.lifted.Query[MessageTable,Message,Seq]
//  required: slick.jdbc.H2Profile.api.DBIO[?]
//     (which expands to)  slick.dbio.DBIOAction[?,slick.dbio.NoStream,slick.dbio.Effect.All]
//        exec(messages.filter(_.sender === "Dave"))
//                            ^
// slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq] <: slick.jdbc.H2Profile.api.DBIO[?]?
// false

Query types tend to be verbose, which can be distracting from the actual cause of the problem (which is that we’re not expecting a Query object at all). We will discuss Query types in more detail next chapter.

2 Selecting Data

The last chapter provided a shallow end-to-end overview of Slick. We saw how to model data, create queries, convert them to actions, and run those actions against a database. In the next two chapters we will look in more detail at the various types of query we can perform in Slick.

This chapter covers selecting data using Slick’s rich type-safe Scala reflection of SQL. Chapter 3 covers modifying data by inserting, updating, and deleting records.

Select queries are our main means of retrieving data. In this chapter we’ll limit ourselves to simple select queries that operate on a single table. In Chapter 6 we’ll look at more complex queries involving joins, aggregates, and grouping clauses.

2.1 Select All The Rows!

The simplest select query is the TableQuery generated from a Table. In the following example, messages is a TableQuery for MessageTable:

import slick.jdbc.H2Profile.api._
final case class Message(
  sender:  String,
  content: String,
  id:      Long = 0L)
// defined class Message

final class MessageTable(tag: Tag) extends Table[Message](tag, "message") {

  def id      = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def sender  = column[String]("sender")
  def content = column[String]("content")

  def * = (sender, content, id).mapTo[Message]
}
// defined class MessageTable

lazy val messages = TableQuery[MessageTable]
// messages: slick.lifted.TableQuery[MessageTable] = <lazy>

The type of messages is TableQuery[MessageTable], which is a subtype of a more general Query type that Slick uses to represent select, update, and delete queries. We’ll discuss these types in the next section.

We can see the SQL of the select query by calling result.statements:

messages.result.statements.mkString
// res0: String = select "sender", "content", "id" from "message"

Our TableQuery is the equivalent of the SQL select * from message.

Query Extension Methods

Like many of the methods discussed below, the result method is actually an extension method applied to Query via an implicit conversion. You’ll need to have everything from H2Profile.api in scope for this to work:

import slick.jdbc.H2Profile.api._

2.2 Filtering Results: The filter Method

We can create a query for a subset of rows using the filter method:

messages.filter(_.sender === "HAL")
// res1: slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq] = Rep(Filter @78414481)

The parameter to filter is a function from an instance of MessageTable to a value of type Rep[Boolean] representing a WHERE clause for our query:

messages.filter(_.sender === "HAL").result.statements.mkString
// res2: String = select "sender", "content", "id" from "message" where "sender" = 'HAL'

Slick uses the Rep type to represent expressions over columns as well as individual columns. A Rep[Boolean] can either be a Boolean-valued column in a table, or a Boolean expression involving multiple columns. Slick can automatically promote a value of type A to a constant Rep[A], and provides a suite of methods for building expressions as we shall see below.

2.3 The Query and TableQuery Types

The types in our filter expression deserve some deeper explanation. Slick represents all queries using a trait Query[M, U, C] that has three type parameters:

In the examples above, messages is of a subtype of Query called TableQuery. Here’s a simplified version of the definition in the Slick codebase:

trait TableQuery[T <: Table[_]] extends Query[T, T#TableElementType, Seq] {
  // ...
}

A TableQuery is actually a Query that uses a Table (e.g. MessageTable) as its mixed type and the table’s element type (the type parameter in the constructor, e.g. Message) as its unpacked type. In other words, the function we provide to messages.filter is actually passed a parameter of type MessageTable:

messages.filter { messageTable: MessageTable =>
  messageTable.sender === "HAL"
}
// res3: slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq] = Rep(Filter @697556478)

This makes sense: messageTable.sender is one of the columns we defined in MessageTable above, and messageTable.sender === "HAL" creates a Scala value representing the SQL expression message.sender = 'HAL'.

This is the process that allows Slick to type-check our queries. Querys have access to the type of the Table used to create them, allowing us to directly reference the columns on the Table when we’re using combinators like map and filter. Every column knows its own data type, so Slick can ensure we only compare columns of compatible types. If we try to compare sender to an Int, for example, we get a type error:

scala> messages.filter(_.sender === 123)
<console>:20: error: Cannot perform option-mapped operation
      with type: (String, Int) => R
  for base type: (String, String) => Boolean
       messages.filter(_.sender === 123)
                                ^
<console>:20: error: ambiguous implicit values:
 both value BooleanColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Boolean]]
 and value BooleanOptionColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Option[Boolean]]]
 match expected type slick.lifted.CanBeQueryCondition[Nothing]
       messages.filter(_.sender === 123)
                      ^

Constant Queries

So far we’ve built up queries from a TableQuery, and this is the common case we use in most of this book. However you should know that you can also construct constant queries, such as select 1, that are not related to any table.

We can use the Query companion object for this. So…

Query(1)

will produce this query:

Query(1).result.statements.mkString
// res6: String = select 1

The apply method of the Query object allows us to lift a scalar value to a Query.

A constant query such as select 1 can be used to confirm we have database connectivity. This could be a useful thing to do as an application is starting up, or a heartbeat system check that will consume minimal resources.

We’ll see another example of using a from-less query in Chapter 3.

2.4 Transforming Results

exec

Just as we did in Chapter 1, we’re using a helper method to run queries in the REPL:

import scala.concurrent.{Await,Future}
import scala.concurrent.duration._

val db = Database.forConfig("chapter02")
// db: slick.jdbc.H2Profile.backend.Database = slick.jdbc.JdbcBackend$DatabaseDef@74d88cfb

def exec[T](action: DBIO[T]): T =
  Await.result(db.run(action), 2.seconds)
// exec: [T](action: slick.jdbc.H2Profile.api.DBIO[T])T

This is included in the example source code for this chapter, in the main.scala file. You can run these examples in the REPL to follow along with the text.

We have also set up the schema and sample data:

def freshTestData = Seq(
  Message("Dave", "Hello, HAL. Do you read me, HAL?"),
  Message("HAL",  "Affirmative, Dave. I read you."),
  Message("Dave", "Open the pod bay doors, HAL."),
  Message("HAL",  "I'm sorry, Dave. I'm afraid I can't do that.")
)
// freshTestData: Seq[Message]

exec(messages.schema.create andThen (messages ++= freshTestData))
// res7: Option[Int] = Some(4)

2.4.1 The map Method

Sometimes we don’t want to select all of the columns in a Table. We can use the map method on a Query to select specific columns for inclusion in the results. This changes both the mixed type and the unpacked type of the query:

messages.map(_.content)
// res8: slick.lifted.Query[slick.lifted.Rep[String],String,Seq] = Rep(Bind)

Because the unpacked type (second type parameter) has changed to String, we now have a query that selects Strings when run. If we run the query we see that only the content of each message is retrieved:

val query = messages.map(_.content)
// query: slick.lifted.Query[slick.lifted.Rep[String],String,Seq] = Rep(Bind)

exec(query.result)
// res9: Seq[String] = Vector(Hello, HAL. Do you read me, HAL?, Affirmative, Dave. I read you., Open the pod bay doors, HAL., I'm sorry, Dave. I'm afraid I can't do that.)

Also notice that the generated SQL has changed. Slick isn’t cheating: it is actually telling the database to restrict the results to that column in the SQL:

messages.map(_.content).result.statements.mkString
// res10: String = select "content" from "message"

Finally, notice that the mixed type (first type parameter) of our new query has changed to Rep[String]. This means we are only passed the content column when we filter or map over this query:

val pods = messages.
  map(_.content).
  filter{content:Rep[String] => content like "%pod%"}
// pods: slick.lifted.Query[slick.lifted.Rep[String],String,Seq] = Rep(Filter @1835138224)

exec(pods.result)
// res11: Seq[String] = Vector(Open the pod bay doors, HAL.)

This change of mixed type can complicate query composition with map. We recommend calling map only as the final step in a sequence of transformations on a query, after all other operations have been applied.

It is worth noting that we can map to anything that Slick can pass to the database as part of a select clause. This includes individual Reps and Tables, as well as Tuples of the above. For example, we can use map to select the id and content columns of messages:

messages.map(t => (t.id, t.content))
// res12: slick.lifted.Query[(slick.lifted.Rep[Long], slick.lifted.Rep[String]),(Long, String),Seq] = Rep(Bind)

The mixed and unpacked types change accordingly, and the SQL is modified as we might expect:

messages.map(t => (t.id, t.content)).result.statements.mkString
// res13: String = select "id", "content" from "message"

We can even map sets of columns to Scala data structures using mapTo:

case class TextOnly(id: Long, content: String)
// defined class TextOnly

val contentQuery = messages.
  map(t => (t.id, t.content).mapTo[TextOnly])
// contentQuery: slick.lifted.Query[slick.lifted.MappedProjection[TextOnly,(Long, String)],TextOnly,Seq] = Rep(Bind)

exec(contentQuery.result)
// res14: Seq[TextOnly] = Vector(TextOnly(1,Hello, HAL. Do you read me, HAL?), TextOnly(2,Affirmative, Dave. I read you.), TextOnly(3,Open the pod bay doors, HAL.), TextOnly(4,I'm sorry, Dave. I'm afraid I can't do that.))

We can also select column expressions as well as single columns:

messages.map(t => t.id * 1000L).result.statements.mkString
// res15: String = select "id" * 1000 from "message"

This all means that map is a powerful combinator for controling the SELECT part of your query.

Query’s flatMap Method

Query also has a flatMap method with similar monadic semantics to that of Option or Future. flatMap is mostly used for joins, so we’ll cover it in Chapter 6.

2.4.2 exists

Sometimes we are less interested in the contents of a queries result than if results exist at all. For this we have exists, which will return true if the result set is not empty and false otherwise.

Let’s look at quick example to show how we can use an existing query with the exists keyword:

val containsBay = for {
  m <- messages
  if m.content like "%bay%"
} yield m
// containsBay: slick.lifted.Query[MessageTable,Message,Seq] = Rep(Bind)

val bayMentioned: DBIO[Boolean] =
  containsBay.exists.result
// bayMentioned: slick.jdbc.H2Profile.api.DBIO[Boolean] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$3@100059a0

The containsBay query returns all messages that mention “bay”. We can then use this query in the bayMentioned expression to determine what to execute.

The above will generate SQL which looks similar to this:

select exists(
  select "sender", "content", "id"
  from "message"
  where "content" like '%bay%'
)

We will see a more useful example in Chapter 3.

2.5 Converting Queries to Actions

Before running a query, we need to convert it to an action. We typically do this by calling the result method on the query. Actions represent sequences of queries. We start with actions representing single queries and compose them to form multi-action sequences.

Actions have the type signature DBIOAction[R, S, E]. The three type parameters are:

In many cases we can simplify the representation of an action to just DBIO[T], which is an alias for DBIOAction[T, NoStream, Effect.All].

Effects

Effects are not part of Essential Slick, and we’ll be working in terms of DBIO[T] for most of this text.

However, broadly speaking, an Effect is a way to annotate an action. For example, you can write a method that will only accept queries marked as Read or Write, or a combination such as Read with Transactional.

The effects defined in Slick under the Effect object are:

  • Read for queries that read from the database.
  • Write for queries that have a write effect on the database.
  • Schema for schema effects.
  • Transactional for transaction effects.
  • All for all of the above.

Slick will infer the effect for your queries. For example, messages.result will be:

DBIOAction[Seq[String], NoStream, Effect.Read]

In the next chapter we will look at inserts and updates. The inferred effect for an update in this case is: DBIOAction[Int, NoStream, Effect.Write].

You can also add your own Effect types by extending the existing types.

2.6 Executing Actions

To execute an action, we pass it to one of two methods on our db object:

In this book we will deal exclusively with materialized queries. db.run returns a Future of the final result of our action. We need to have an ExecutionContext in scope when we make the call:

import scala.concurrent.ExecutionContext.Implicits.global
// import scala.concurrent.ExecutionContext.Implicits.global

val futureMessages = db.run(messages.result)
// futureMessages: scala.concurrent.Future[Seq[MessageTable#TableElementType]] = Future(<not completed>)

Streaming

In this book we will deal exclusively with materialized queries. Let’s take a quick look at streams now, so we are aware of the alternative.

Calling db.stream returns a DatabasePublisher object instead of a Future. This exposes three methods to interact with the stream:

  • subscribe which allows integration with Akka;
  • mapResult which creates a new Publisher that maps the supplied function on the result set from the original publisher; and
  • foreach, to perform a side-effect with the results.

Streaming results can be used to feed reactive streams, or Akka streams or actors. Alternatively, we can do something simple like use foreach to println our results:

db.stream(messages.result).foreach(println)

…which will eventually print each row.

If you want to explore this area, start with the Slick documentation on streaming.

2.7 Column Expressions

Methods like filter and map require us to build expressions based on columns in our tables. The Rep type is used to represent expressions as well as individual columns. Slick provides a variety of extension methods on Rep for building expressions.

We will cover the most common methods below. You can find a complete list in ExtensionMethods.scala in the Slick codebase.

2.7.1 Equality and Inequality Methods

The === and =!= methods operate on any type of Rep and produce a Rep[Boolean]. Here are some examples:

messages.filter(_.sender === "Dave").result.statements
// res16: Iterable[String] = List(select "sender", "content", "id" from "message" where "sender" = 'Dave')

messages.filter(_.sender =!= "Dave").result.statements.mkString
// res17: String = select "sender", "content", "id" from "message" where not ("sender" = 'Dave')

The <, >, <=, and >= methods can operate on any type of Rep (not just numeric columns):

messages.filter(_.sender < "HAL").result.statements
// res18: Iterable[String] = List(select "sender", "content", "id" from "message" where "sender" < 'HAL')

messages.filter(m => m.sender >= m.content).result.statements
// res19: Iterable[String] = List(select "sender", "content", "id" from "message" where "sender" >= "content")
Rep comparison methods. Operand and result types should be interpreted as parameters to Rep[_].
Scala Code Operand Types Result Type SQL Equivalent
col1 === col2 A or Option[A] Boolean col1 = col2
col1 =!= col2 A or Option[A] Boolean col1 <> col2
col1 < col2 A or Option[A] Boolean col1 < col2
col1 > col2 A or Option[A] Boolean col1 > col2
col1 <= col2 A or Option[A] Boolean col1 <= col2
col1 >= col2 A or Option[A] Boolean col1 >= col2

2.7.2 String Methods

Slick provides the ++ method for string concatenation (SQL’s || operator):

messages.map(m => m.sender ++ "> " ++ m.content).result.statements.mkString
// res20: String = select ("sender"||'> ')||"content" from "message"

and the like method for SQL’s classic string pattern matching:

messages.filter(_.content like "%pod%").result.statements.mkString
// res21: String = select "sender", "content", "id" from "message" where "content" like '%pod%'

Slick also provides methods such as startsWith, length, toUpperCase, trim, and so on. These are implemented differently in different DBMSs—the examples below are purely for illustration:

String column methods. Operand (e.g., col1, col2) must be String or Option[String]. Operand and result types should be interpreted as parameters to Rep[_].
Scala Code Result Type SQL Equivalent
col1.length Int char_length(col1)
col1 ++ col2 String col1 || col2
c1 like c2 Boolean c1 like c2
c1 startsWith c2 Boolean c1 like (c2 || '%')
c1 endsWith c2 Boolean c1 like ('%' || c2)
c1.toUpperCase String upper(c1)
c1.toLowerCase String lower(c1)
col1.trim String trim(col1)
col1.ltrim String ltrim(col1)
col1.rtrim String rtrim(col1)

2.7.3 Numeric Methods

Slick provides a comprehensive set of methods that operate on Reps with numeric values: Ints, Longs, Doubles, Floats, Shorts, Bytes, and BigDecimals.

Numeric column methods. Operand and result types should be interpreted as parameters to Rep[_].
Scala Code Operand Column Types Result Type SQL Equivalent
col1 + col2 A or Option[A] A col1 + col2
col1 - col2 A or Option[A] A col1 - col2
col1 * col2 A or Option[A] A col1 * col2
col1 / col2 A or Option[A] A col1 / col2
col1 % col2 A or Option[A] A mod(col1, col2)
col1.abs A or Option[A] A abs(col1)
col1.ceil A or Option[A] A ceil(col1)
col1.floor A or Option[A] A floor(col1)
col1.round A or Option[A] A round(col1, 0)

2.7.4 Boolean Methods

Slick also provides a set of methods that operate on boolean Reps:

Boolean column methods. Operand and result types should be interpreted as parameters to Rep[_].
Scala Code Operand Column Types Result Type SQL Equivalent
col1 && col2 Boolean or Option[Boolean] Boolean col1 and col2
col1 || col2 Boolean or Option[Boolean] Boolean col1 or col2
!col1 Boolean or Option[Boolean] Boolean not col1

2.7.5 Option Methods and Type Equivalence

Slick models nullable columns in SQL as Reps with Option types. We’ll discuss this in some depth in Chapter 5. However, as a preview, know that if we have a nullable column in our database, we declare it as optional in our Table:

final class PersonTable(tag: Tag) /* ... */ {
  // ...
  def nickname = column[Option[String]]("nickname")
  // ...
}

When it comes to querying on optional values, Slick is pretty smart about type equivalence.

What do we mean by type equivalence? Slick type-checks our column expressions to make sure the operands are of compatible types. For example, we can compare Strings for equality but we can’t compare a String and an Int:

scala> messages.filter(_.id === "foo")
<console>:25: error: Cannot perform option-mapped operation
      with type: (Long, String) => R
  for base type: (Long, Long) => Boolean
       messages.filter(_.id === "foo")
                            ^
<console>:25: error: ambiguous implicit values:
 both value BooleanColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Boolean]]
 and value BooleanOptionColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Option[Boolean]]]
 match expected type slick.lifted.CanBeQueryCondition[Nothing]
       messages.filter(_.id === "foo")
                      ^

Interestingly, Slick is very finickity about numeric types. For example, comparing an Int to a Long is considered a type error:

scala> messages.filter(_.id === 123)
<console>:25: error: Cannot perform option-mapped operation
      with type: (Long, Int) => R
  for base type: (Long, Long) => Boolean
       messages.filter(_.id === 123)
                            ^
<console>:25: error: ambiguous implicit values:
 both value BooleanColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Boolean]]
 and value BooleanOptionColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Option[Boolean]]]
 match expected type slick.lifted.CanBeQueryCondition[Nothing]
       messages.filter(_.id === 123)
                      ^

On the flip side of the coin, Slick is clever about the equivalence of optional and non-optional columns. As long as the operands are some combination of the types A and Option[A] (for the same value of A), the query will normally compile:

messages.filter(_.id === Option(123L)).result.statements
// res24: Iterable[String] = List(select "sender", "content", "id" from "message" where "id" = 123)

However, any optional arguments must be strictly of type Option, not Some or None:

scala> messages.filter(_.id === Some(123L)).result.statements
<console>:25: error: type mismatch;
 found   : Some[Long]
 required: slick.lifted.Rep[?]
       messages.filter(_.id === Some(123L)).result.statements
                                    ^
Some[Long] <: slick.lifted.Rep[?]?
false
<console>:25: error: ambiguous implicit values:
 both value BooleanColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Boolean]]
 and value BooleanOptionColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Option[Boolean]]]
 match expected type slick.lifted.CanBeQueryCondition[Nothing]
       messages.filter(_.id === Some(123L)).result.statements
                      ^

If you find yourself in this situation, remember you can always provide a type ascription to the value:

messages.filter(_.id === (Some(123L): Option[Long]) )
// res26: slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq] = Rep(Filter @2018099928)

2.8 Controlling Queries: Sort, Take, and Drop

There are a trio of functions used to control the order and number of results returned from a query. This is great for pagination of a result set, but the methods listed in the table below can be used independently.

Methods for ordering, skipping, and limiting the results of a query.
Scala Code SQL Equivalent
sortBy ORDER BY
take LIMIT
drop OFFSET

We’ll look at each in turn, starting with an example of sortBy. Say we want want messages in order of the sender’s name:

exec(messages.sortBy(_.sender).result).foreach(println)
// Message(Dave,Hello, HAL. Do you read me, HAL?,1)
// Message(Dave,Open the pod bay doors, HAL.,3)
// Message(HAL,Affirmative, Dave. I read you.,2)
// Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,4)

Or the reverse order:

exec(messages.sortBy(_.sender.desc).result).foreach(println)
// Message(HAL,Affirmative, Dave. I read you.,2)
// Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,4)
// Message(Dave,Hello, HAL. Do you read me, HAL?,1)
// Message(Dave,Open the pod bay doors, HAL.,3)

To sort by multiple columns, return a tuple of columns:

messages.sortBy(m => (m.sender, m.content)).result.statements
// res29: Iterable[String] = List(select "sender", "content", "id" from "message" order by "sender", "content")

Now we know how to sort results, perhaps we want to show only the first five rows:

messages.sortBy(_.sender).take(5)
// res30: slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq] = Rep(Take)

If we are presenting information in pages, we’d need a way to show the next page (rows 6 to 10):

messages.sortBy(_.sender).drop(5).take(5)
// res31: slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq] = Rep(Take)

This is equivalent to:

select "sender", "content", "id"
from "message"
order by "sender"
limit 5 offset 5

Sorting on Null columns

We had a brief introduction to nullable columns earlier in the chapter when we looked at Option Methods and Type Equivalence. Slick offers three modifiers which can be used in conjunction with desc and asc when sorting on nullable columns: nullFirst, nullsDefault and nullsLast. These do what you expect, by including nulls at the beginning or end of the result set. The nullsDefault behaviour will use the SQL engines preference.

We don’t have any nullable fields in our example yet. But here’s a look at what sorting a nullable column is like:

users.sortBy(_.name.nullsFirst)

The generated SQL for the above quiery would be:

select "name", "email", "id"
from "user"
order by "name" nulls first

We cover nullable columns in Chapter 5 and include an example of sorting on nullable columns in example project the code is in nulls.scala in the folder chapter-05.

2.9 Take Home Points

Starting with a TableQuery we can construct a wide range of queries with filter and map. As we compose these queries, the types of the Query follow along to give type-safety throughout our application.

The expressions we use in queries are defined in extension methods, and include ===, =!=, like, && and so on, depending on the type of the Rep. Comparisons to Option types are made easy for us as Slick will compare Rep[T] and Rep[Option[T]] automatically.

We’ve seen that map acts like a SQL select, and filter is like a WHERE. We’ll see the Slick representation of GROUP and JOIN in Chapter 6.

We introduced some new terminology:

We run queries by converting them to actions using the result method. We run the actions against a database using db.run.

The database action type constructor DBIOAction takes three arguments that represent the result, streaming mode, and effect. DBIO[R] simplifies this to just the result type.

What we’ve seen for composing queries will help us to modify data using update and delete. That’s the topic of the next chapter.

2.10 Exercises

If you’ve not already done so, try out the above code. In the example project the code is in main.scala in the folder chapter-02.

Once you’ve done that, work through the exercises below. An easy way to try things out is to use triggered execution with SBT:

$ cd example-02
$ ./sbt.sh
> ~run

That ~run will monitor the project for changes, and when a change is seen, the main.scala program will be compiled and run. This means you can edit main.scala and then look in your terminal window to see the output.

2.10.1 Count the Messages

How would you count the number of messages? Hint: in the Scala collections the method length gives you the size of the collection.

val results = exec(messages.length.result)
// results: Int = 4

You could also use size, which is an alias for length.

2.10.2 Selecting a Message

Using a for comprehension, select the message with the id of 1. What happens if you try to find a message with an id of 999?

Hint: our IDs are Longs. Adding L after a number in Scala, such as 99L, makes it a long.

val query = for {
  message <- messages if message.id === 1L
} yield message
// query: slick.lifted.Query[MessageTable,Message,Seq] = Rep(Bind)

val results = exec(query.result)
// results: Seq[Message] = Vector(Message(Dave,Hello, HAL. Do you read me, HAL?,1))

Asking for 999, when there is no row with that ID, will give back an empty collection.

2.10.3 One Liners

Re-write the query from the last exercise to not use a for comprehension. Which style do you prefer? Why?

val results = exec(messages.filter(_.id === 1L).result)
// results: Seq[MessageTable#TableElementType] = Vector(Message(Dave,Hello, HAL. Do you read me, HAL?,1))

2.10.4  Checking the SQL

Calling the result.statements methods on a query will give you the SQL to be executed. Apply that to the last exercise. What query is reported? What does this tell you about the way filter has been mapped to SQL?

The code you need to run is:

val sql = messages.filter(_.id === 1L).result.statements
// sql: Iterable[String] = List(select "sender", "content", "id" from "message" where "id" = 1)

println(sql.head)
// select "sender", "content", "id" from "message" where "id" = 1

From this we see how filter corresponds to a SQL where clause.

2.10.5 Is HAL Real?

Find if there are any messages by HAL in the database, but only return a boolean value from the database.

That’s right, we want to know if HAL exists:

val query = messages.filter(_.sender === "HAL").exists
// query: slick.lifted.Rep[Boolean] = Rep(Apply Function exists)

exec(query.result)
// res35: Boolean = true

The query will return true as we do have records from HAL, and Slick will generate the following SQL:

query.result.statements.head
// res37: String = select exists(select "sender", "content", "id" from "message" where "sender" = 'HAL')

2.10.6 Selecting Columns

So far we have been returning Message classes, booleans, or counts. Now we want to select all the messages in the database, but return just their content columns.

Hint: think of messages as a collection and what you would do to a collection to just get back a single field of a case class.

Check what SQL would be executed for this query.

val query = messages.map(_.content)
// query: slick.lifted.Query[slick.lifted.Rep[String],String,Seq] = Rep(Bind)

exec(query.result)
// res38: Seq[String] = Vector(Hello, HAL. Do you read me, HAL?, Affirmative, Dave. I read you., Open the pod bay doors, HAL., I'm sorry, Dave. I'm afraid I can't do that.)

You could have also said:

val query = for { message <- messages } yield message.content
// query: slick.lifted.Query[slick.lifted.Rep[String],String,Seq] = Rep(Bind)

The query will return only the content column from the database:

query.result.statements.head
// res39: String = select "content" from "message"

2.10.7 First Result

The methods head and headOption are useful methods on a result. Find the first message that HAL sent.

What happens if you use head to find a message from “Alice” (note that Alice has sent no messages).

val msg1 = messages.filter(_.sender === "HAL").map(_.content).result.head
// msg1: slick.sql.SqlAction[String,slick.dbio.NoStream,slick.dbio.Effect.Read] = slick.jdbc.StreamingInvokerAction$HeadAction@223dfc68

You should get an action that produces “Affirmative, Dave. I read you.”

For Alice, head will throw a run-time exception as we are trying to return the head of an empty collection. Using headOption will prevent the exception.

exec(messages.filter(_.sender === "Alice").result.headOption)
// res40: Option[Message] = None

2.10.8 Then the Rest

In the previous exercise you returned the first message HAL sent. This time find the next five messages HAL sent. What messages are returned?

What if we’d asked for HAL’s tenth through to twentieth message?

It’s drop and take to the rescue:

val msgs = messages.filter(_.sender === "HAL").drop(1).take(5).result
// msgs: slick.jdbc.H2Profile.StreamingProfileAction[Seq[MessageTable#TableElementType],MessageTable#TableElementType,slick.dbio.Effect.Read] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1@becd6ef

HAL has only two messages in total. Therefore our result set should contain one messages

Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,4)

And asking for any more messages will result in an empty collection.

val msgs = exec(
            messages.
              filter(_.sender === "HAL").
              drop(10).
              take(10).
              result
          )
// msgs: Seq[MessageTable#TableElementType] = Vector()

2.10.9 The Start of Something

The method startsWith on a String tests to see if the string starts with a particular sequence of characters. Slick also implements this for string columns. Find the message that starts with “Open”. How is that query implemented in SQL?

messages.filter(_.content startsWith "Open")
// res43: slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq] = Rep(Filter @2095963672)

The query is implemented in terms of LIKE:

messages.filter(_.content startsWith "Open").result.statements.head
// res44: String = select "sender", "content", "id" from "message" where "content" like 'Open%' escape '^'

2.10.10 Liking

Slick implements the method like. Find all the messages with “do” in their content.

Can you make this case insensitive?

If you have familiarity with SQL like expressions, it probably wasn’t too hard to find a case-sensitive version of this query:

messages.filter(_.content like "%do%")
// res45: slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq] = Rep(Filter @803631166)

To make it case sensitive you could use toLowerCase on the content field:

messages.filter(_.content.toLowerCase like "%do%")
// res46: slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq] = Rep(Filter @1174653481)

We can do this because content is a Rep[String] and that Rep has implemented toLowerCase. That means, the toLowerCase will be translated into meaningful SQL.

There will be three results: “Do you read me”, “Open the pod bay doors”, and “I’m afraid I can’t do that”.

2.10.11 Client-Side or Server-Side?

What does this do and why?

exec(messages.map(_.content + "!").result)

The query Slick generates looks something like this:

select '(message Ref @421681221).content!' from "message"

That is a select expression for a strange constant string.

The _.content + "!" expression converts content to a string and appends the exclamation point. What is content? It’s a Rep[String], not a String of the content. The end result is that we’re seeing something of the internal workings of Slick.

This is an unfortunate effect of Scala allowing automatic conversion to a String. If you are interested in disabling this Scala behaviour, tools like WartRemover can help.

It is possible to do this mapping in the database with Slick. We need to remember to work in terms of Rep[T] classes:

messages.map(m => m.content ++ LiteralColumn("!"))
// res49: slick.lifted.Query[slick.lifted.Rep[String],String,Seq] = Rep(Bind)

Here LiteralColumn[T] is type of Rep[T] for holding a constant value to be inserted into the SQL. The ++ method is one of the extension methods defined for any Rep[String].

Using ++ will produce the desired query:

select "content"||'!' from "message"

You can also write:

messages.map(m => m.content ++ "!")
// res50: slick.lifted.Query[slick.lifted.Rep[String],String,Seq] = Rep(Bind)

…as "!" will be lifted to a Rep[String].

This exercise highlights that inside of a map or filter you are working in terms of Rep[T]. You should become familiar with the operations available to you. The tables we’ve included in this chapter should help with that.

3 Creating and Modifying Data

In the last chapter we saw how to retrieve data from the database using select queries. In this chapter we will look at modifying stored data using insert, update, and delete queries.

SQL veterans will know that update and delete queries share many similarities with select queries. The same is true in Slick, where we use the Query monad and combinators to build the different kinds of query. Ensure you are familiar with the content of Chapter 2 before proceeding.

3.1 Inserting Rows

As we saw in Chapter 1, adding new data looks like an append operation on a mutable collection. We can use the += method to insert a single row into a table, and ++= to insert multiple rows. We’ll discuss both of these operations below.

3.1.1 Inserting Single Rows

To insert a single row into a table we use the += method. Note that, unlike the select queries we’ve seen, this creates a DBIOAction immediately without an intermediate Query:

val action =
  messages += Message("HAL", "No. Seriously, Dave, I can't let you in.")
// action: slick.sql.FixedSqlAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction@225e2e0a

exec(action)
// res1: Int = 1

We’ve left the DBIO[Int] type annotation off of action, so you’ll see the specific type Slick is using. It’s not important for this discussion, but worth knowing that Slick has a number of different kinds of DBIOAction classes in use under the hood.

The result of the action is the number of rows inserted. However, it is often useful to return something else, such as the primary key generated for the new row. We can get this information using a method called returning. Before we get to that, we first need to understand where the primary key comes from.

3.1.2 Primary Key Allocation

When inserting data, we need to tell the database whether or not to allocate primary keys for the new rows. It is common practice to declare auto-incrementing primary keys, allowing the database to allocate values automatically if we don’t manually specify them in the SQL.

Slick allows us to allocate auto-incrementing primary keys via an option on the column definition. Recall the definition of MessageTable from Chapter 1, which looked like this:

final class MessageTable(tag: Tag) extends Table[Message](tag, "message") {

  def id      = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def sender  = column[String]("sender")
  def content = column[String]("content")

  def * = (sender, content, id).mapTo[Message]
}
// defined class MessageTable

The O.AutoInc option specifies that the id column is auto-incrementing, meaning that Slick can omit the column in the corresponding SQL:

action.statements.head
// res2: String = insert into "message" ("sender","content")  values (?,?)

As a convenience, in our example code we put the id field at the end of the case class and gave it a default value of 0L. This allows us to skip the field when creating new objects of type Message:

case class Message(
  sender:  String,
  content: String,
  id:      Long = 0L
)
Message("Dave", "You're off my Christmas card list.")
// res3: Message = Message(Dave,You're off my Christmas card list.,0)

There is nothing special about our default value of 0L—it doesn’t signify anything in particular. It is the O.AutoInc option that determines the behaviour of +=.

Sometimes we want to override the database’s default auto-incrementing behaviour and specify our own primary key. Slick provides a forceInsert method that does just this:

val forceInsertAction = messages forceInsert Message(
   "HAL",
   "I'm a computer, what would I do with a Christmas card anyway?",
   1000L)
// forceInsertAction: slick.sql.FixedSqlAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction@451f9d68

Notice that the SQL generated for this action includes a manually specified ID, and that running the action results in a record with the ID being inserted:

forceInsertAction.statements.head
// res4: String = insert into "message" ("sender","content","id")  values (?,?,?)

exec(forceInsertAction)
// res5: Int = 1

exec(messages.filter(_.id === 1000L).result)
// res6: Seq[MessageTable#TableElementType] = Vector(Message(HAL,I'm a computer, what would I do with a Christmas card anyway?,1000))

3.1.3 Retrieving Primary Keys on Insert

When the database allocates primary keys for us it’s often the case that we want get the key back after an insert. Slick supports this via the returning method:

val insert: DBIO[Long] =
  messages returning messages.map(_.id) += Message("Dave", "Point taken.")
// insert: slick.jdbc.H2Profile.api.DBIO[Long] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction@6c389a82

val pk: Long = exec(insert)
// pk: Long = 1001

The argument to messages returning is a Query over the same table, which is why messages.map(_.id) makes sense here. The query specifies what data we’d like the database to return once the insert has finished.

We can demonstrate that the return value is a primary key by looking up the record we just inserted:

exec(messages.filter(_.id === 1001L).result.headOption)
// res8: Option[Message] = Some(Message(Dave,Point taken.,1001))

For convenience, we can save a few keystrokes and define an insert query that always returns the primary key:

lazy val messagesReturningId = messages returning messages.map(_.id)
// messagesReturningId: slick.jdbc.H2Profile.ReturningInsertActionComposer[MessageTable#TableElementType,Long] = <lazy>

exec(messagesReturningId += Message("HAL", "Humans, eh."))
// res9: messagesReturningId.SingleInsertResult = 1002

Using messagesReturningId will return the id value, rather than the count of the number of rows inserted.

3.1.4 Retrieving Rows on Insert

Some databases allow us to retrieve the complete inserted record, not just the primary key. For example, we could ask for the whole Message back:

exec(messages returning messages +=
  Message("Dave", "So... what do we do now?"))

Not all databases provide complete support for the returning method. H2 only allows us to retrieve the primary key from an insert.

If we tried this with H2, we get a runtime error:

exec(messages returning messages +=
  Message("Dave", "So... what do we do now?"))
// slick.SlickException: This DBMS allows only a single column to be returned from an INSERT, and that column must be an AutoInc column.
//   at slick.jdbc.JdbcStatementBuilderComponent$JdbcCompiledInsert.buildReturnColumns(JdbcStatementBuilderComponent.scala:67)
//   at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.x$13$lzycompute(JdbcActionComponent.scala:635)
//   at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.x$13(JdbcActionComponent.scala:635)
//   at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.keyColumns$lzycompute(JdbcActionComponent.scala:635)
//   at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.keyColumns(JdbcActionComponent.scala:635)
//   at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.preparedInsert(JdbcActionComponent.scala:638)
//   at slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction.run(JdbcActionComponent.scala:506)
//   at slick.jdbc.JdbcActionComponent$SimpleJdbcProfileAction.run(JdbcActionComponent.scala:29)
//   at slick.jdbc.JdbcActionComponent$SimpleJdbcProfileAction.run(JdbcActionComponent.scala:26)
//   at slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:242)
//   at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:242)
//   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
//   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
//   at java.lang.Thread.run(Thread.java:748)

This is a shame, but getting the primary key is often all we need.

Profile Capabilities

The Slick manual contains a comprehensive table of the capabilities for each database profile. The ability to return complete records from an insert query is referenced as the jdbc.returnInsertOther capability.

The API documentation for each profile also lists the capabilities that the profile doesn’t have. For an example, the top of the H2 Profile Scaladoc page points out several of its shortcomings.

If we want to get a complete populated Message back from a database without jdbc.returnInsertOther support, we retrieve the primary key and manually add it to the inserted record. Slick simplifies this with another method, into:

val messagesReturningRow =
  messages returning messages.map(_.id) into { (message, id) =>
    message.copy(id = id)
  }
// messagesReturningRow: slick.jdbc.H2Profile.IntoInsertActionComposer[MessageTable#TableElementType,Message] = slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl@540fe9fe

val insert: DBIO[Message] =
  messagesReturningRow += Message("Dave", "You're such a jerk.")
// insert: slick.jdbc.H2Profile.api.DBIO[Message] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction@7f16226e

exec(insert)
// res11: Message = Message(Dave,You're such a jerk.,1003)

The into method allows us to specify a function to combine the record and the new primary key. It’s perfect for emulating the jdbc.returnInsertOther capability, although we can use it for any post-processing we care to imagine on the inserted data.

3.1.5 Inserting Specific Columns

If our database table contains a lot of columns with default values, it is sometimes useful to specify a subset of columns in our insert queries. We can do this by mapping over a query before calling insert:

messages.map(_.sender).insertStatement
// res12: String = insert into "message" ("sender")  values (?)

The parameter type of the += method is matched to the unpacked type of the query:

messages.map(_.sender)
// res13: slick.lifted.Query[slick.lifted.Rep[String],String,Seq] = Rep(Bind)

… so we execute this query by passing it a String for the sender:

exec(messages.map(_.sender) += "HAL")

The query fails at runtime because the content column is non-nullable in our schema. No matter. We’ll cover nullable columns when discussing schemas in Chapter 5.

3.1.6 Inserting Multiple Rows

Suppose we want to insert several Messages at the same time. We could just use += to insert each one in turn. However, this would result in a separate query being issued to the database for each record, which could be slow for large numbers of inserts.

As an alternative, Slick supports batch inserts, where all the inserts are sent to the database in one go. We’ve seen this already in the first chapter:

val testMessages = Seq(
  Message("Dave", "Hello, HAL. Do you read me, HAL?"),
  Message("HAL",  "Affirmative, Dave. I read you."),
  Message("Dave", "Open the pod bay doors, HAL."),
  Message("HAL",  "I'm sorry, Dave. I'm afraid I can't do that.")
)
// testMessages: Seq[Message] = List(Message(Dave,Hello, HAL. Do you read me, HAL?,0), Message(HAL,Affirmative, Dave. I read you.,0), Message(Dave,Open the pod bay doors, HAL.,0), Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,0))

exec(messages ++= testMessages)
// res15: Option[Int] = Some(4)

This code prepares one SQL statement and uses it for each row in the Seq. In principle Slick could optimize this insert further using database-specific features. This can result in a significant boost in performance when inserting many records.

As we saw earlier this chapter, the default return value of a single insert is the number of rows inserted. The multi-row insert above is also returning the number of rows, except this time the type is Option[Int]. The reason for this is that the JDBC specification permits the underlying database driver to indicate that the number of rows inserted is unknown.

Slick also provides a batch version of messages returning..., including the into method. We can use the messagesReturningRow query we defined last section and write:

exec(messagesReturningRow ++= testMessages)
// res16: messagesReturningRow.MultiInsertResult = Vector(Message(Dave,Hello, HAL. Do you read me, HAL?,1008), Message(HAL,Affirmative, Dave. I read you.,1009), Message(Dave,Open the pod bay doors, HAL.,1010), Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,1011))

3.1.7 More Control over Inserts

At this point we’ve inserted fixed data into the database. Sometimes you need more flexibility, including inserting data based on another query. Slick supports this via forceInsertQuery.

The argument to forceInsertQuery is a query. So the form is:

 insertExpression.forceInsertQuery(selectExpression)

Our selectExpression can be pretty much anything, but it needs to match the columns required by our insertExpression.

As an example, our query could check to see if a particular row of data already exists, and insert it if it doesn’t. That is, an “insert if doesn’t exist” function.

Let’s say we only want the director to be able to say “Cut!” once. The SQL would end up like this:

insert into "messages" ("sender", "content")
  select 'Stanley', 'Cut!'
where
  not exists(
    select
      "id", "sender", "content"
    from
      "messages" where "sender" = 'Stanley'
                 and   "content" = 'Cut!')

That looks quite involved, but we can build it up gradually.

The tricky part of this is the select 'Stanley', 'Cut!' part, as there is no FROM clause there. We saw an example of how to create that in Chapter 2, with Query.apply. For this situation it would be:

val data = Query(("Stanley", "Cut!"))
// data: slick.lifted.Query[(slick.lifted.ConstColumn[String], slick.lifted.ConstColumn[String]),(String, String),Seq] = Rep(Pure $@1903197403)

data is a constant query that returns a fixed value—a tuple of two columns. It’s the equivalent of running SELECT 'Stanley', 'Cut!'; against the database, which is one part of the query we need.

We also need to be able to test to see if the data already exists. That’s straightforward:

val exists =
  messages.
   filter(m => m.sender === "Stanley" && m.content === "Cut!").
   exists
// exists: slick.lifted.Rep[Boolean] = Rep(Apply Function exists)

We want to use the data when the row doesn’t exist, so combine the data and exists with filterNot rather than filter:

val selectExpression = data.filterNot(_ => exists)
// selectExpression: slick.lifted.Query[(slick.lifted.ConstColumn[String], slick.lifted.ConstColumn[String]),(String, String),Seq] = Rep(Filter @423762619)

Finally, we need to apply this query with forceInsertQuery. But remember the column types for the insert and select need to match up. So we map on messages to make sure that’s the case:

val action =
  messages.
    map(m => m.sender -> m.content).
    forceInsertQuery(selectExpression)
// action: slick.sql.FixedSqlAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$InsertQueryAction@1b3a8b02

exec(action)
// res17: Int = 1

exec(action)
// res18: Int = 0

The first time we run the query, the message is inserted. The second time, no rows are affected.

In summary, forceInsertQuery provides a way to build-up more complicated inserts. If you find situations beyond the power of this method, you can always make use of Plain SQL inserts, described in Chapter 7.

3.2 Deleting Rows

Slick lets us delete rows using the same Query objects we saw in Chapter 2. That is, we specify which rows to delete using the filter method, and then call delete:

val removeHal: DBIO[Int] =
  messages.filter(_.sender === "HAL").delete
// removeHal: slick.jdbc.H2Profile.api.DBIO[Int] = slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4@48dd8ee6

exec(removeHal)
// res19: Int = 9

The return value is the number of rows affected.

The SQL generated for the action can be seen by calling delete.statements:

messages.filter(_.sender === "HAL").delete.statements.head
// res20: String = delete from "message" where "message"."sender" = 'HAL'

Note that it is an error to use delete in combination with map. We can only call delete on a TableQuery:

messages.map(_.content).delete
// <console>:21: error: value delete is not a member of slick.lifted.Query[slick.lifted.Rep[String],String,Seq]
//        messages.map(_.content).delete
//                                ^

3.3 Updating Rows

So far we’ve only looked at inserting new data and deleting existing data. But what if we want to update existing data without deleting it first? Slick lets us create SQL UPDATE actions via the kinds of Query values we’ve been using for selecting and deleting rows.

Restoring Data

In the last section we removed all the rows for HAL. Before continuing with updating rows, we should put them back:

exec(messages.delete andThen (messages ++= freshTestData) andThen messages.result)
// res22: Seq[MessageTable#TableElementType] = Vector(Message(Dave,Hello, HAL. Do you read me, HAL?,1013), Message(HAL,Affirmative, Dave. I read you.,1014), Message(Dave,Open the pod bay doors, HAL.,1015), Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,1016))

Action combinators, such as andThen, are the subject of the next chapter.

3.3.1 Updating a Single Field

In the Messages we’ve created so far we’ve referred to the computer from 2001: A Space Odyssey as “HAL”, but the correct name is “HAL 9000”. Let’s fix that.

We start by creating a query to select the rows to modify, and the columns to change:

val updateQuery =
  messages.filter(_.sender === "HAL").map(_.sender)
// updateQuery: slick.lifted.Query[slick.lifted.Rep[String],String,Seq] = Rep(Bind)

We can use update to turn this into an action to run. Update requires the new values for the column we want to change:

exec(updateQuery.update("HAL 9000"))
// res23: Int = 2

We can retrieve the SQL for this query by calling updateStatment instead of update:

updateQuery.updateStatement
// res24: String = update "message" set "sender" = ? where "message"."sender" = 'HAL'

Let’s break down the code in the Scala expression. By building our update query from the messages TableQuery, we specify that we want to update records in the message table in the database:

val messagesByHal = messages.filter(_.sender === "HAL")
// messagesByHal: slick.lifted.Query[MessageTable,MessageTable#TableElementType,Seq] = Rep(Filter @492834578)

We only want to update the sender column, so we use map to reduce the query to just that column:

val halSenderCol = messagesByHal.map(_.sender)
// halSenderCol: slick.lifted.Query[slick.lifted.Rep[String],String,Seq] = Rep(Bind)

Finally we call the update method, which takes a parameter of the unpacked type (in this case String):

val action: DBIO[Int] = halSenderCol.update("HAL 9000")
// action: slick.jdbc.H2Profile.api.DBIO[Int] = slick.jdbc.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$8@413a0db7

Running that action would return the number of rows changed.

3.3.2 Updating Multiple Fields

We can update more than one field at the same time by mapping the query to a tuple of the columns we care about…

// 1016 is "I'm sorry, Dave...."
val query = messages.
    filter(_.id === 1016L).
    map(message => (message.sender, message.content))
// query: slick.lifted.Query[(slick.lifted.Rep[String], slick.lifted.Rep[String]),(String, String),Seq] = Rep(Bind)

…and then supplying the tuple values we want to used in the update:

val action: DBIO[Int] =
  query.update(("HAL 9000", "Sure, Dave. Come right in."))
// action: slick.jdbc.H2Profile.api.DBIO[Int] = slick.jdbc.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$8@db8109c

exec(action)
// res27: Int = 1

exec(messages.filter(_.sender === "HAL 9000").result)
// res28: Seq[MessageTable#TableElementType] = Vector(Message(HAL 9000,Affirmative, Dave. I read you.,1014), Message(HAL 9000,Sure, Dave. Come right in.,1016))

Again, we can see the SQL we’re running using the updateStatement method. The returned SQL contains two ? placeholders, one for each field as expected:

messages.
  filter(_.id === 1016L).
  map(message => (message.sender, message.content)).
  updateStatement
// res29: String = update "message" set "sender" = ?, "content" = ? where "message"."id" = 1016

We can even use mapTo to use case classes as the parameter to update:

case class NameText(name: String, text: String)
// defined class NameText

val newValue = NameText("Dave", "Now I totally don't trust you.")
// newValue: NameText = NameText(Dave,Now I totally don't trust you.)

messages.
  filter(_.id === 1016L).
  map(m => (m.sender, m.content).mapTo[NameText]).
  update(newValue)
// res30: slick.jdbc.H2Profile.ProfileAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.jdbc.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$8@422ab64b

3.3.3 Updating with a Computed Value

Let’s now turn to more interesting updates. How about converting every message to be all capitals? Or adding an exclamation mark to the end of each message? Both of these queries involve expressing the desired result in terms of the current value in the database. In SQL we might write something like:

update "message" set "content" = CONCAT("content", '!')

This is not currently supported by update in Slick, but there are ways to achieve the same result. One such way is to use Plain SQL queries, which we cover in Chapter 7. Another is to perform a client-side update by defining a Scala function to capture the change to each row:

def exclaim(msg: Message): Message =
  msg.copy(content = msg.content + "!")
// exclaim: (msg: Message)Message

We can update rows by selecting the relevant data from the database, applying this function, and writing the results back individually. Note that approach can be quite inefficient for large datasets—it takes N + 1 queries to apply an update to N results.

You may be tempted to write something like this:

def modify(msg: Message): DBIO[Int] =
  messages.filter(_.id === msg.id).update(exclaim(msg))
// modify: (msg: Message)slick.jdbc.H2Profile.api.DBIO[Int]

// Don't do it this way:
for {
  msg <- exec(messages.result)
} yield exec(modify(msg))
// res32: Seq[Int] = Vector(1, 1, 1, 1)

This will have the desired effect, but at some cost. What we have done there is use our own exec method which will wait for results. We use it to fetch all rows, and then we use it on each row to modify the row. That’s a lot of waiting. There is also no support for transactions as we db.run each action separately.

A better approach is to turn our logic into a single DBIO action using action combinators. This, together with transactions, is the topic of the next chapter.

However, for this particular example, we recommend using Plain SQL (Chapter 7) instead of client-side updates.

3.4 Take Home Points

For modifying the rows in the database we have seen that:

Auto-incrementing values are inserted by Slick, unless forced. The auto-incremented values can be returned from the insert by using returning.

Databases have different capabilities. The limitations of each profile is listed in the profile’s Scala Doc page.

3.5 Exercises

The code for this chapter is in the GitHub repository in the chapter-03 folder. As with chapter 1 and 2, you can use the run command in SBT to execute the code against a H2 database.

Where Did My Data Go?

Several of the exercises in this chapter require you to delete or update content from the database. We’ve shown you above how to restore you data, but if you want to explore and change the schema you might want to completely reset the schema.

In the example code we provide a populate method you can use:

exec(populate)

This will drop, create, and populate the messages table with known values.

Populate is defined as:

import scala.concurrent.ExecutionContext.Implicits.global
// import scala.concurrent.ExecutionContext.Implicits.global

def populate: DBIOAction[Option[Int], NoStream, Effect.All] =
  for {    
    // Drop table if it already exists, then create the table:
    _  <- messages.schema.drop.asTry andThen messages.schema.create
    // Add some data:
    count <- messages ++= freshTestData
  } yield count
// populate: slick.jdbc.H2Profile.api.DBIOAction[Option[Int],slick.jdbc.H2Profile.api.NoStream,slick.jdbc.H2Profile.api.Effect.All]

We’ll meet asTry and andThen in the next chapter.

3.5.1 Get to the Specifics

In Inserting Specific Columns we looked at only inserting the sender column:

messages.map(_.sender) += "HAL"

This failed when we tried to use it as we didn’t meet the requirements of the message table schema. For this to succeed we need to include content as well as sender.

Rewrite the above query to include the content column.

The requirements of the messages table is sender and content can not be null. Given this, we can correct our query:

val query = messages.map { m => (m.sender, m.content) }
// query: slick.lifted.Query[(slick.lifted.Rep[String], slick.lifted.Rep[String]),(String, String),Seq] = Rep(Bind)

val action = query += ( ("HAL","Helllllo Dave") )
// action: slick.sql.FixedSqlAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction@7443b410

exec(action)
// res34: Int = 1

We have used map to create a query that works on the two columns we care about. To insert using that query, we supply the two field values.

In case you’re wondering, we’ve out the extra parentheses around the column values to be clear it is a single value which is a tuple of two values.

3.5.2 Bulk All the Inserts

Insert the conversation below between Alice and Bob, returning the messages populated with ids.

val conversation = List(
  Message("Bob",  "Hi Alice"),
  Message("Alice","Hi Bob"),
  Message("Bob",  "Are you sure this is secure?"),
  Message("Alice","Totally, why do you ask?"),
  Message("Bob",  "Oh, nothing, just wondering."),
  Message("Alice","Ten was too many messages"),
  Message("Bob",  "I could do with a sleep"),
  Message("Alice","Let's just to to the point"),
  Message("Bob",  "Okay okay, no need to be tetchy."),
  Message("Alice","Humph!"))

For this we need to use a batch insert (++=) and into:

val messagesReturningRow =
  messages returning messages.map(_.id) into { (message, id) =>
    message.copy(id = id)
  }
// messagesReturningRow: slick.jdbc.H2Profile.IntoInsertActionComposer[MessageTable#TableElementType,Message] = slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl@2e95c3a2

exec(messagesReturningRow ++= conversation).foreach(println)
// Message(Bob,Hi Alice,1018)
// Message(Alice,Hi Bob,1019)
// Message(Bob,Are you sure this is secure?,1020)
// Message(Alice,Totally, why do you ask?,1021)
// Message(Bob,Oh, nothing, just wondering.,1022)
// Message(Alice,Ten was too many messages,1023)
// Message(Bob,I could do with a sleep,1024)
// Message(Alice,Let's just to to the point,1025)
// Message(Bob,Okay okay, no need to be tetchy.,1026)
// Message(Alice,Humph!,1027)

3.5.3 No Apologies

Write a query to delete messages that contain “sorry”.

The pattern is to fine a query to select the data, and then use it with delete:

messages.filter(_.content like "%sorry%").delete
// res36: slick.jdbc.H2Profile.ProfileAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4@74c59b6d

3.5.4 Update Using a For Comprehension

Rewrite the update statement below to use a for comprehension.

val rebootLoop = messages.
  filter(_.sender === "HAL").
  map(msg => (msg.sender, msg.content)).
  update(("HAL 9000", "Rebooting, please wait..."))
// rebootLoop: slick.jdbc.H2Profile.ProfileAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.jdbc.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$8@63634424

Which style do you prefer?

We’ve split this into a query and then an update:

val halMessages = for {
  message <- messages if message.sender === "HAL"
} yield (message.sender, message.content)
// halMessages: slick.lifted.Query[(slick.lifted.Rep[String], slick.lifted.Rep[String]),(String, String),Seq] = Rep(Bind)

val rebootLoop = halMessages.update(("HAL 9000", "Rebooting, please wait..."))
// rebootLoop: slick.jdbc.H2Profile.ProfileAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.jdbc.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$8@39bed875

3.5.5 Selective Memory

Delete HALs first two messages. This is a more difficult exercise.

You don’t know the IDs of the messages, or the content of them. But you do know the IDs increase.

Hints:

We’ve selected HAL’s message IDs, sorted by the ID, and used this query inside a filter:

val selectiveMemory =
  messages.filter{
   _.id in messages.
      filter { _.sender === "HAL" }.
      sortBy { _.id.asc }.
      map    {_.id}.
      take(2)
  }.delete
// selectiveMemory: slick.jdbc.H2Profile.ProfileAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4@142faf1a

selectiveMemory.statements.head
// res37: String = delete from "message" where "message"."id" in (select "id" from "message" where "sender" = 'HAL' order by "id" limit 2)

4 Combining Actions

At some point you’ll find yourself writing a piece of code made up of multiple actions. You might need a simple sequence of actions to run one after another; or you might need something more sophisticated where one action depends on the results of another.

In Slick you use action combinators to turn a number of actions into a single action. You can then run this combined action just like any single action. You might also run these combined actions in a transaction.

This chapter focuses on these combinators. Some, such as map, fold, and zip, will be familiar from the Scala collections library. Others, such as sequence and asTry may be less familiar. We will give examples of how to use many of them in this chapter.

This is a key concept in Slick. Make sure you spend time getting comfortable with combining actions.

4.1 Combinators Summary

The temptation with multiple actions might be to run each action, use the result, and run another action. This will require you to deal with multiple Futures. We recommend you avoid that whenever you can.

Instead, focus on the actions and how they combine together, not on the messy details of running them. Slick provides a set of combinators to make this possible.

Before getting into the detail, take a look at the two tables below. They list out the key methods available on an action, and also the combinators available on DBIO.

Combinators on action instances of DBIOAction, specifically a DBIO[T]. Types simplified. (EC) Indicates an execution context is required.
Method Arguments Result Type
map (EC) T => R DBIO[R]
flatMap (EC) T => DBIO[R] DBIO[R]
filter (EC) T => Boolean DBIO[T]
named String DBIO[T]
zip DBIO[R] DBIO[(T,R)]
asTry DBIO[Try[T]]
andThen or >> DBIO[R] DBIO[R]
andFinally DBIO[_] DBIO[T]
cleanUp (EC) Option[Throwable]=>DBIO[_] DBIO[T]
failed DBIO[Throwable]
Combinators on DBIO object, with types simplified. (EC) Indicates an execution context is required.
Method Arguments Result Type
sequence TraversableOnce[DBIO[T]] DBIO[TraversableOnce[T]]
seq DBIO[_]* DBIO[Unit]
from Future[T] DBIO[T]
successful V DBIO[V]
failed Throwable DBIO[Nothing]
fold (EC) (Seq[DBIO[T]], T) (T,T)=>T DBIO[T]

4.2 Combinators in Detail

4.2.1 andThen (or >>)

The simplest way to run one action after another is perhaps andThen. The combined actions are both run, but only the result of the second is returned:

val reset: DBIO[Int] =
  messages.delete andThen messages.size.result
// reset: slick.jdbc.H2Profile.api.DBIO[Int] = slick.dbio.SynchronousDatabaseAction$FusedAndThenAction@3bbd198a

exec(reset)
// res1: Int = 0

The result of the first query is ignored, so we cannot use it. Later we will see how flatMap allows us to use the result to make choices about which action to run next.

Combined Actions Are Not Automatically Transactions

By default, when you combine actions together you do not get a single transaction. At the end of this chapter we’ll see that it’s very easy to run combined actions in a transaction with:

db.run(actions.transactionally)

4.2.2 DBIO.seq

If you have a bunch of actions you want to run, you can use DBIO.seq to combine them:

val reset: DBIO[Unit] =
  DBIO.seq(messages.delete, messages.size.result)
// reset: slick.jdbc.H2Profile.api.DBIO[Unit] = slick.dbio.DBIOAction$$anon$4@32cd2727

This is rather like combining the actions with andThen, but even the last value is discarded.

4.2.3 map

Mapping over an action is a way to set up a transformation of a value from the database. The transformation will run on the result of the action when it is returned by the database.

As an example, we can create an action to return the content of a message, but reverse the text:

// Restore the data we deleted in the previous section
exec(messages ++= freshTestData)
// res3: Option[Int] = Some(4)

import scala.concurrent.ExecutionContext.Implicits.global
// import scala.concurrent.ExecutionContext.Implicits.global

val text: DBIO[Option[String]] =
  messages.map(_.content).result.headOption
// text: slick.jdbc.H2Profile.api.DBIO[Option[String]] = slick.jdbc.StreamingInvokerAction$HeadOptionAction@7a619d2d

val backwards: DBIO[Option[String]] =
  text.map(optionalContent => optionalContent.map(_.reverse))
// backwards: slick.jdbc.H2Profile.api.DBIO[Option[String]] = FlatMapAction(slick.jdbc.StreamingInvokerAction$HeadOptionAction@7a619d2d,slick.dbio.DBIOAction$$Lambda$8669/1964225174@5e67755e,scala.concurrent.impl.ExecutionContextImpl@7c202862)

exec(backwards)
// res4: Option[String] = Some(?LAH ,em daer uoy oD .LAH ,olleH)

Here we have created an action called backwards that, when run, ensures a function is applied to the result of the text action. In this case the function is to apply reverse to an optional String.

Note that we have made three uses of map in this example:

Combinators everywhere!

This example transformed an Option[String] to another Option[String]. As you may expect if map changes the type of a value, the type of DBIO changes too:

text.map(os => os.map(_.length))
// res5: slick.dbio.DBIOAction[Option[Int],slick.dbio.NoStream,slick.dbio.Effect.All] = FlatMapAction(slick.jdbc.StreamingInvokerAction$HeadOptionAction@7a619d2d,slick.dbio.DBIOAction$$Lambda$8669/1964225174@408e8bd3,scala.concurrent.impl.ExecutionContextImpl@7c202862)

Note that the first type parameter on the DBIOAction is now Option[Int] (as length returns an Int), not Option[String].

Execution Context Required

Some methods require an execution context and some don’t. For example, map does, but andThen does not. What gives?

The reason is that map allows you to call arbitrary code when joining the actions together. Slick cannot allow that code to be run on its own execution context, because it has no way to know if you are going to tie up Slicks threads for a long time.

In contrast, methods such as andThen which combine actions without custom code can be run on Slick’s own execution context. Therefore, you do not need an execution context available for andThen.

You’ll know if you need an execution context, because the compiler will tell you:

Cannot find an implicit ExecutionContext. You might pass
  an (implicit ec: ExecutionContext) parameter to your method
  or import scala.concurrent.ExecutionContext.Implicits.global.

The Slick manual discusses this in the section on Database I/O Actions.

4.2.4 DBIO.successful and DBIO.failed

When combining actions you will sometimes need to create an action that represents a simple value. Slick provides DBIO.successful for that purpose:

val v: DBIO[Int] = DBIO.successful(100)
// v: slick.jdbc.H2Profile.api.DBIO[Int] = SuccessAction(100)

We’ll see an example of this when we discuss flatMap.

And for failures, the value is a Throwable:

val v: DBIO[Nothing] =
  DBIO.failed(new RuntimeException("pod bay door unexpectedly locked"))
// v: slick.jdbc.H2Profile.api.DBIO[Nothing] = FailureAction(java.lang.RuntimeException: pod bay door unexpectedly locked)

This has a particular role to play inside transactions, which we cover later in this chapter.

4.2.5 flatMap

Ahh, flatMap. Wonderful flatMap. This method gives us the power to sequence actions and decide what we want to do at each step.

The signature of flatMap should feel similar to the flatMap you see elsewhere:

// Simplified:
def flatMap[S](f: R => DBIO[S])(implicit e: ExecutionContext): DBIO[S]

That is, we give flatMap a function that depends on the value from an action, and evaluates to another action.

As an example, let’s write a method to remove all the crew’s messages, and post a message saying how many messages were removed. This will involve an INSERT and a DELETE, both of which we’re familiar with:

val delete: DBIO[Int] =
  messages.delete
// delete: slick.jdbc.H2Profile.api.DBIO[Int] = slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4@25ff515a

def insert(count: Int) =
  messages += Message("NOBODY", s"I removed ${count} messages")
// insert: (count: Int)slick.sql.FixedSqlAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write]

The first thing flatMap allows us to do is run these actions in order:

import scala.concurrent.ExecutionContext.Implicits.global
// import scala.concurrent.ExecutionContext.Implicits.global

val resetMessagesAction: DBIO[Int] =
  delete.flatMap{ count => insert(count) }
// resetMessagesAction: slick.jdbc.H2Profile.api.DBIO[Int] = FlatMapAction(slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4@25ff515a,$$Lambda$8674/239665330@6d30fffc,scala.concurrent.impl.ExecutionContextImpl@7c202862)

exec(resetMessagesAction)
// res6: Int = 1

The 1 we see is the result of insert, which is the number of rows inserted.

This single action produces the two SQL expressions you’d expect:

delete from "message";
insert into "message" ("sender","content")
  values ('NOBODY', 'I removed 4 messages');

Beyond sequencing, flatMap also gives us control over which actions are run. To illustrate this we will change resetMessagesAction to not insert a message if no messages were removed in the first step:

val resetMessagesAction: DBIO[Int] =
  delete.flatMap {
    case 0 => DBIO.successful(0)
    case n => insert(n)
  }
// resetMessagesAction: slick.jdbc.H2Profile.api.DBIO[Int] = FlatMapAction(slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4@25ff515a,$$Lambda$8676/585358004@5ef7d795,scala.concurrent.impl.ExecutionContextImpl@7c202862)

We’ve decided a result of 0 is right if no message was inserted. But the point here is that flatMap gives us arbitrary control over how actions can be combined.

Occasionally the compiler will complain about a flatMap and need your help to figuring out the types. Recall that DBIO[T] is an alias for DBIOAction[T,S,E], encoding streaming and effects. When mixing effects, such as inserts and selects, you may need to explicitly specify the type parameters to apply to the resulting action:

query.flatMap[Int, NoStream, Effect.All] { result => ... }

…but in many cases the compiler will figure these out for you.

Do it in the database if you can

Combining actions to sequence queries is a powerful feature of Slick. However, you may be able to reduce multiple queries into a single database query. If you can do that, you’re probably better off doing it.

As an example, you could implement “insert if not exists” like this:

// Not the best way:
def insertIfNotExists(m: Message): DBIO[Int] = {
  val alreadyExists =
    messages.filter(_.content === m.content).result.headOption
  alreadyExists.flatMap {
    case Some(m) => DBIO.successful(0)
    case None    => messages += m
  }
}
// insertIfNotExists: (m: Message)slick.jdbc.H2Profile.api.DBIO[Int]

…but as we saw earlier in “More Control over Inserts” you can achieve the same effect with a single SQL statement.

One query can often (but doesn’t always) perform better than a sequence of queries. Your mileage may vary.

4.2.6 DBIO.sequence

Despite the similarity in name to DBIO.seq, DBIO.sequence has a different purpose. It takes a sequence of DBIOs and gives back a DBIO of a sequence. That’s a bit of a mouthful, but an example may help.

Let’s say we want to reverse the text of every message (row) in the database. We start with this:

def reverse(msg: Message): DBIO[Int] =
  messages.filter(_.id === msg.id).
  map(_.content).
  update(msg.content.reverse)
// reverse: (msg: Message)slick.jdbc.H2Profile.api.DBIO[Int]

That’s a straightforward method that returns an update action for one message. We can apply it to every message….

// Don't do this
val updates: DBIO[Seq[DBIO[Int]]] =
  messages.result.
  map(msgs => msgs.map(reverse))
// updates: slick.jdbc.H2Profile.api.DBIO[Seq[slick.jdbc.H2Profile.api.DBIO[Int]]] = FlatMapAction(slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1@6572aee9,slick.dbio.DBIOAction$$Lambda$8669/1964225174@718c4fb2,scala.concurrent.impl.ExecutionContextImpl@7c202862)

…which will give us an action that returns actions! Note the crazy type signature.

You can find yourself in this awkward situation when you’re trying to do something like a join, but not quite. The puzzle is how to run this kind of a beast.

This is where DBIO.sequence saves us. Rather than produce many actions via msgs.map(reverse) we use DBIO.sequence to return a single action:

val updates: DBIO[Seq[Int]] =
  messages.result.
  flatMap(msgs => DBIO.sequence(msgs.map(reverse)))
// updates: slick.jdbc.H2Profile.api.DBIO[Seq[Int]] = FlatMapAction(slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1@33cb2548,$$Lambda$8678/660386720@55807052,scala.concurrent.impl.ExecutionContextImpl@7c202862)

The difference is:

The end result is a sane type which we can run like any other action.

Of course this one action turns into many SQL statements:

select "sender", "content", "id" from "message"
update "message" set "content" = ? where "message"."id" = 1
update "message" set "content" = ? where "message"."id" = 2
update "message" set "content" = ? where "message"."id" = 3
update "message" set "content" = ? where "message"."id" = 4

4.2.7 DBIO.fold

Recall that many Scala collections support fold as a way to combine values:

List(3,5,7).fold(1) { (a,b) => a * b }
// res9: Int = 105

1 * 3 * 5 * 7
// res10: Int = 105

You can do the same kind of thing in Slick: when you need to run a sequence of actions, and reduce the results down to a value, you use fold.

As an example, suppose we have a number of reports to run. We want to summarize all these reports to a single number.

// Pretend these two reports are complicated queries
// that return Important Business Metrics:
val report1: DBIO[Int] = DBIO.successful(41)
// report1: slick.jdbc.H2Profile.api.DBIO[Int] = SuccessAction(41)

val report2: DBIO[Int] = DBIO.successful(1)
// report2: slick.jdbc.H2Profile.api.DBIO[Int] = SuccessAction(1)

val reports: List[DBIO[Int]] =
  report1 :: report2 :: Nil
// reports: List[slick.jdbc.H2Profile.api.DBIO[Int]] = List(SuccessAction(41), SuccessAction(1))

We can fold those reports with a function.

But we also need to consider our starting position:

val default: Int = 0
// default: Int = 0

Finally we can produce an action to summarize the reports:

val summary: DBIO[Int] =
  DBIO.fold(reports, default) {
    (total, report) => total + report
}
// summary: slick.jdbc.H2Profile.api.DBIO[Int] = FlatMapAction(FlatMapAction(SuccessAction(0),slick.dbio.DBIOAction$$$Lambda$8682/341176915@7fcb5478,scala.concurrent.impl.ExecutionContextImpl@7c202862),slick.dbio.DBIOAction$$$Lambda$8682/341176915@750d23cb,scala.concurrent.impl.ExecutionContextImpl@7c202862)

exec(summary)
// res13: Int = 42

DBIO.fold is a way to combine actions, such that the results are combined by a function you supply. As with other combinators, your function isn’t run until we execute the action itself. In this case all our reports are run, and the sum of the values reported.

4.2.8 zip

We’ve seen how DBIO.seq combines actions and ignores the results. We’ve also seen that andThen combines actions and keeps one result. If you want to keep both results, zip is the combinator for you:

val zip: DBIO[(Int, Seq[Message])] =
  messages.size.result zip messages.filter(_.sender === "HAL").result
// zip: slick.jdbc.H2Profile.api.DBIO[(Int, Seq[Message])] = slick.dbio.SynchronousDatabaseAction$$anon$5@526278e6

// Make sure we have some messages from HAL:
exec(messages ++= freshTestData)
// res15: Option[Int] = Some(4)

exec(zip)
// res16: (Int, Seq[Message]) = (5,Vector(Message(HAL,Affirmative, Dave. I read you.,11), Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,13)))

The action returns a tuple representing the results of both queries: a count of the total number of messages, and the messages from HAL.

4.2.9 andFinally and cleanUp

The two methods cleanUp and andFinally act a little like Scala’s catch and finally.

cleanUp runs after an action completes, and has access to any error information as an Option[Throwable]:

// An action to record problems we encounter:
def log(err: Throwable): DBIO[Int] =
  messages += Message("SYSTEM", err.getMessage)
// log: (err: Throwable)slick.jdbc.H2Profile.api.DBIO[Int]

// Pretend this is important work which might fail:
val work = DBIO.failed(new RuntimeException("Boom!"))
// work: slick.dbio.DBIOAction[Nothing,slick.dbio.NoStream,slick.dbio.Effect] = FailureAction(java.lang.RuntimeException: Boom!)

val action: DBIO[Int] = work.cleanUp {
  case Some(err) => log(err)
  case None      => DBIO.successful(0)
}
// action: slick.jdbc.H2Profile.api.DBIO[Int] = CleanUpAction(FailureAction(java.lang.RuntimeException: Boom!),$$Lambda$8701/1124714329@2c2219f5,true,scala.concurrent.impl.ExecutionContextImpl@7c202862)

The result of running this action is still the original exception…

java.lang.RuntimeException: Boom!
... 45 elided

…but cleanUp has produced a side-effect for us:

exec(messages.filter(_.sender === "SYSTEM").result)
// res20: Seq[MessageTable#TableElementType] = Vector(Message(SYSTEM,Boom!,14))

Both cleanUp and andFinally run after an action, regardless of whether it succeeds or fails. cleanUp runs in response to a previous failed action; andFinally runs all the time, regardless of success or failure, and has no access to the Option[Throwable] that cleanUp sees.

4.2.10 asTry

Calling asTry on an action changes the action’s type from a DBIO[T] to a DBIO[Try[T]]. This means you can work in terms of Scala’s Success[T] and Failure instead of exceptions.

Suppose we had an action that might throw an exception:

val work = DBIO.failed(new RuntimeException("Boom!"))
// work: slick.dbio.DBIOAction[Nothing,slick.dbio.NoStream,slick.dbio.Effect] = FailureAction(java.lang.RuntimeException: Boom!)

We can place this inside Try by combining the action with asTry:

exec(work.asTry)
// res22: scala.util.Try[Nothing] = Failure(java.lang.RuntimeException: Boom!)

And successful actions will evaluate to a Success[T]:

exec(messages.size.result.asTry)
// res23: scala.util.Try[Int] = Success(6)

4.3 Logging Queries and Results

With actions combined together, it’s useful to see the queries that are being executed.

We’ve seen how to retrieve the SQL of a query using insertStatement and similar methods on a query, or the statements method on an action. These are useful for experimenting with Slick, but sometimes we want to see all the queries when Slick executes them. We can do that by configuring logging.

Slick uses a logging interface called SLF4J. We can configure this to capture information about the queries being run. The build.sbt files in the exercises use an SLF4J-compatible logging back-end called Logback, which is configured in the file src/main/resources/logback.xml. In that file we can enable statement logging by turning up the logging to debug level:

<logger name="slick.jdbc.JdbcBackend.statement" level="DEBUG"/>

This causes Slick to log every query, including modifications to the schema:

DEBUG slick.jdbc.JdbcBackend.statement - Preparing statement:
  delete from "message" where "message"."sender" = 'HAL'

We can change the level of various loggers, as shown in the table below.

Slick loggers and their effects.
Logger Will log…
slick.jdbc.JdbcBackend.statement SQL sent to the database.
slick.jdbc.JdbcBackend.parameter Parameters passed to a query.
slick.jdbc.StatementInvoker.result The first few results of each query.
slick.session Session events such as opening/closing connections.
slick Everything!

The StatementInvoker.result logger, in particular, is pretty cute. Here’s an example from running a select query:

result - /--------+----------------------+----\
result - | sender | content              | id |
result - +--------+----------------------+----+
result - | HAL    | Affirmative, Dave... | 2  |
result - | HAL    | I'm sorry, Dave. ... | 4  |
result - \--------+----------------------+----/

The combination of parameter and statement can show you the values bound to ? placeholders. For example, when adding rows we can see the values being inserted:

statement - Preparing statement: insert into "message" 
   ("sender","content")  values (?,?)
parameter - /--------+---------------------------\
parameter - | 1      | 2                         |
parameter - | String | String                    |
parameter - |--------+---------------------------|
parameter - | Dave   | Hello, HAL. Do you rea... |
parameter - | HAL    | I'm sorry, Dave. I'm a... |
parameter - \--------+---------------------------/

4.4 Transactions

So far each of the changes we’ve made to the database have run independently of the others. That is, each insert, update, or delete query we run can succeed or fail independently of the rest.

We often want to tie sets of modifications together in a transaction so that they either all succeed or all fail. We can do this in Slick using the transactionally method.

As an example, let’s re-write the movie script. We want to make sure the script changes all complete or nothing changes. We can do this by finding the old script text and replacing it with some new text:

def updateContent(old: String) =
  messages.filter(_.content === old).map(_.content)
// updateContent: (old: String)slick.lifted.Query[slick.lifted.Rep[String],String,Seq]

exec {
  (updateContent("Affirmative, Dave. I read you.").update("Wanna come in?") andThen
   updateContent("Open the pod bay doors, HAL.").update("Pretty please!") andThen
   updateContent("I'm sorry, Dave. I'm afraid I can't do that.").update("Opening now.") ).transactionally
}
// res24: Int = 1

exec(messages.result).foreach(println)
// Message(NOBODY,I removed 4 messages,9)
// Message(Dave,Hello, HAL. Do you read me, HAL?,10)
// Message(HAL,Wanna come in?,11)
// Message(Dave,Pretty please!,12)
// Message(HAL,Opening now.,13)
// Message(SYSTEM,Boom!,14)

The changes we make in the transactionally block are temporary until the block completes, at which point they are committed and become permanent.

To manually force a rollback you need to call DBIO.failed with an appropriate exception.

val willRollback = (
  (messages += Message("HAL",  "Daisy, Daisy..."))                   >>
  (messages += Message("Dave", "Please, anything but your singing")) >>
  DBIO.failed(new Exception("agggh my ears"))                        >>
  (messages += Message("HAL", "Give me your answer do"))
  ).transactionally
// willRollback: slick.dbio.DBIOAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write with slick.dbio.Effect.Write with slick.dbio.Effect with slick.dbio.Effect.Write with slick.dbio.Effect.Transactional] = slick.dbio.SynchronousDatabaseAction$$anon$11@1886b2eb

exec(willRollback.asTry)
// res26: scala.util.Try[Int] = Failure(java.lang.Exception: agggh my ears)

The result of running willRollback is that the database won’t have changed. Inside of transactional block you would see the inserts until DBIO.failed is called.

If we removed the .transactionally that is wrapping our combined actions, the first two inserts would succeed, even though the combined action failed.

4.5 Take Home Points

Inserts, selects, deletes and other forms of Database Action can be combined using flatMap and other combinators. This is a powerful way to sequence actions, and make actions depend on the results of other actions.

Combining actions avoid having to deal with awaiting results or having to sequence Futures yourself.

We saw that the SQL statements executed and the result returned from the database can be monitored by configuring the logging system.

Finally, we saw that actions that are combined together can also be run inside a transaction.

4.6 Exercises

4.6.1 And Then what?

In Chapter 1 we created a schema and populated the database as separate actions. Use your newly found knowledge to combine them.

This exercise expects to start with an empty database. If you’re already in the REPL and the database exists, you’ll need to drop the table first:

val drop:     DBIO[Unit]        = messages.schema.drop
// drop: slick.jdbc.H2Profile.api.DBIO[Unit] = slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$7@759e9925

val create:   DBIO[Unit]        = messages.schema.create
// create: slick.jdbc.H2Profile.api.DBIO[Unit] = slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@401ff8e8

val populate: DBIO[Option[Int]] = messages ++= freshTestData
// populate: slick.jdbc.H2Profile.api.DBIO[Option[Int]] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$MultiInsertAction@158c2460

exec(drop)

Using the values we’ve provided, you can create a new database with a single action:

exec(drop andThen create andThen populate)
// res29: Option[Int] = Some(4)

If we don’t care about any of the values we could also use DBIO.seq:

val allInOne = DBIO.seq(drop,create,populate)
// allInOne: slick.dbio.DBIOAction[Unit,slick.dbio.NoStream,slick.dbio.Effect.All] = slick.dbio.DBIOAction$$anon$4@6cecc2bc

val result = exec(allInOne)
// result: Unit = ()

4.6.2 First!

Create a method that will insert a message, but if it is the first message in the database, automatically insert the message “First!” before it.

Your method signature should be:

def insert(m: Message): DBIO[Int] = ???
// insert: (m: Message)slick.jdbc.H2Profile.api.DBIO[Int]

Use your knowledge of the flatMap action combinator to achieve this.

There are two elements to this problem:

  1. being able to use the result of a count, which is what flatMap gives us; and

  2. combining two inserts via andThen.

import scala.concurrent.ExecutionContext.Implicits.global
// import scala.concurrent.ExecutionContext.Implicits.global

def insert(m: Message): DBIO[Int] =
  messages.size.result.flatMap {
    case 0 =>
      (messages += Message(m.sender, "First!")) andThen (messages += m)
    case n =>
      messages += m
    }
// insert: (m: Message)slick.jdbc.H2Profile.api.DBIO[Int]

// Throw away all the messages:
exec(messages.delete)
// res31: Int = 4

// Try out the method:
exec {
  insert(Message("Me", "Hello?"))
}
// res33: Int = 1

// What's in the database?
exec(messages.result).foreach(println)
// Message(Me,First!,5)
// Message(Me,Hello?,6)

4.6.3 There Can be Only One

Implement onlyOne, a method that guarantees that an action will return only one result. If the action returns anything other than one result, the method should fail with an exception.

Below is the method signature and two test cases:

def onlyOne[T](ms: DBIO[Seq[T]]): DBIO[T] = ???
// onlyOne: [T](ms: slick.jdbc.H2Profile.api.DBIO[Seq[T]])slick.jdbc.H2Profile.api.DBIO[T]

You can see that onlyOne takes an action as an argument, and that the action could return a sequence of results. The return from the method is an action that will return a single value.

In the example data there is only one message that contains the word “Sorry”, so we expect onlyOne to return that row:

val happy = messages.filter(_.content like "%sorry%").result
// happy: slick.jdbc.H2Profile.StreamingProfileAction[Seq[MessageTable#TableElementType],MessageTable#TableElementType,slick.dbio.Effect.Read] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1@12dee546

// We expect... 
// exec(onlyOne(happy))
// ...to return a message.

However, there are two messages containing the word “I”. In this case onlyOne should fail:

val boom  = messages.filter(_.content like "%I%").result
// boom: slick.jdbc.H2Profile.StreamingProfileAction[Seq[MessageTable#TableElementType],MessageTable#TableElementType,slick.dbio.Effect.Read] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1@4383845c

// If we run this...
// exec(onlyOne(boom))
// we want a failure, such as:
// java.lang.RuntimeException: Expected 1 result, not 2

Hints:

The basis of our solution is to flatMap the action we’re given into a new action with the type we want:

def onlyOne[T](action: DBIO[Seq[T]]): DBIO[T] = action.flatMap { ms =>
  ms match {
    case m +: Nil => DBIO.successful(m)
    case ys       => DBIO.failed(
        new RuntimeException(s"Expected 1 result, not ${ys.length}")
      )
  }
}
// onlyOne: [T](action: slick.jdbc.H2Profile.api.DBIO[Seq[T]])slick.jdbc.H2Profile.api.DBIO[T]

If you’ve not seen +: before: it is “cons” for Seq (a standard part of Scala, equivalent to :: for List).

Our flatMap is taking the results from the action, ms, and in the case it is a single message, we return it. In the case it’s something else, we fail with an informative message.

exec(populate)
// res43: Option[Int] = Some(4)

exec(onlyOne(boom))
// java.lang.RuntimeException: Expected 1 result, not 2
//   at .$anonfun$onlyOne$1(<console>:33)
//   at slick.basic.BasicBackend$DatabaseDef.$anonfun$runInContext$1(BasicBackend.scala:147)
//   at scala.concurrent.Future.$anonfun$flatMap$1(Future.scala:302)
//   at scala.concurrent.impl.Promise.$anonfun$transformWith$1(Promise.scala:37)
//   at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:60)
//   at scala.concurrent.impl.ExecutionContextImpl$AdaptedForkJoinTask.exec(ExecutionContextImpl.scala:140)
//   at java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289)
//   at java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056)
//   at java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692)
//   at java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:157)

exec(onlyOne(happy))
// res45: MessageTable#TableElementType = Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,10)

4.6.4 Let’s be Reasonable

Some fool is throwing exceptions in our code, destroying our ability to reason about it. Implement exactlyOne which wraps onlyOne encoding the possibility of failure using types rather than exceptions.

Then rerun the test cases.

There are several ways we could have implemented this. Perhaps the simplest is using asTry:

import scala.util.Try
// import scala.util.Try

def exactlyOne[T](action: DBIO[Seq[T]]): DBIO[Try[T]] = onlyOne(action).asTry
// exactlyOne: [T](action: slick.jdbc.H2Profile.api.DBIO[Seq[T]])slick.jdbc.H2Profile.api.DBIO[scala.util.Try[T]]

exec(exactlyOne(happy))
// res46: scala.util.Try[MessageTable#TableElementType] = Success(Message(HAL,I'm sorry, Dave. I'm afraid I can't do that.,10))

exec(exactlyOne(boom))
// res47: scala.util.Try[MessageTable#TableElementType] = Failure(java.lang.RuntimeException: Expected 1 result, not 2)

4.6.5 Filtering

There is a DBIO filter method, but it produces a runtime exception if the filter predicate is false. It’s like Future’s filter method in that respect. We’ve not found a situation where we need it.

However, we can create our own kind of filter. It can take some alternative action when the filter predicate fails.

The signature could be:

def myFilter[T](action: DBIO[T])(p: T => Boolean)(alternative: => T) = ???
// myFilter: [T](action: slick.jdbc.H2Profile.api.DBIO[T])(p: T => Boolean)(alternative: => T)Nothing

If you’re not comfortable with the [T] type parameter, or the by name parameter on alternative, just use Int instead:

def myFilter(action: DBIO[Int])(p: Int => Boolean)(alternative: Int) = ???
// myFilter: (action: slick.jdbc.H2Profile.api.DBIO[Int])(p: Int => Boolean)(alternative: Int)Nothing

Go ahead and implement myFilter.

We have an example usage from the ship’s marketing department. They are happy to report the number of chat messages, but only if that number is at least 100:

myFilter(messages.size.result)( _ > 100)(100)

This is a fairly straightforward example of using map:

def myFilter[T](action: DBIO[T])(p: T => Boolean)(alternative: => T) =
  action.map {
    case t if p(t) => t
    case _         => alternative
  }
// myFilter: [T](action: slick.jdbc.H2Profile.api.DBIO[T])(p: T => Boolean)(alternative: => T)slick.dbio.DBIOAction[T,slick.dbio.NoStream,slick.dbio.Effect.All]

4.6.6 Unfolding

This is a challenging exercise.

We saw that fold can take a number of actions and reduce them using a function you supply. Now imagine the opposite: unfolding an initial value into a sequence of values via a function. In this exercise we want you to write an unfold method that will do just that.

Why would you need to do something like this? One example would be when you have a tree structure represented in a database and need to search it. You can follow a link between rows, possibly recording what you find as you follow those links.

As an example, let’s pretend the crew’s ship is a set of rooms, one connected to just one other:

final case class Room(name: String, connectsTo: String)
// defined class Room

final class FloorPlan(tag: Tag) extends Table[Room](tag, "floorplan") {
  def name       = column[String]("name")
  def connectsTo = column[String]("next")
  def * = (name, connectsTo).mapTo[Room]
}
// defined class FloorPlan

lazy val floorplan = TableQuery[FloorPlan]
// floorplan: slick.lifted.TableQuery[FloorPlan] = <lazy>

exec {
  (floorplan.schema.create) >>
  (floorplan += Room("Outside",     "Podbay Door")) >>
  (floorplan += Room("Podbay Door", "Podbay"))      >>
  (floorplan += Room("Podbay",      "Galley"))      >>
  (floorplan += Room("Galley",      "Computer"))    >>
  (floorplan += Room("Computer",    "Engine Room"))
}
// res48: Int = 1

For any given room it’s easy to find the next room. For example:

SELECT
  "connectsTo"
FROM
  "foorplan"
WHERE
  "name" = 'Podbay'

-- Returns 'Galley'

Write a method unfold that will take any room name as a starting point, and a query to find the next room, and will follow all the connections until there are no more connecting rooms.

The signature of unfold could be:

def unfold(
  z: String,
  f: String => DBIO[Option[String]]
): DBIO[Seq[String]] = ???
// unfold: (z: String, f: String => slick.jdbc.H2Profile.api.DBIO[Option[String]])slick.jdbc.H2Profile.api.DBIO[Seq[String]]

…where z is the starting (“zero”) room, and f will lookup the connecting room (an action for the query to find the next room).

If unfold is given "Podbay" as a starting point it should return an action which, when run, will produce: Seq("Podbay", "Galley", "Computer", "Engine Room").

You’ll want to accumulate results of the rooms you visit. One way to do that would be to use a different signature:

def unfold(
  z: String,
  f: String => DBIO[Option[String]],
  acc: Seq[String] = Seq.empty
): DBIO[Seq[String]] = ???
// unfold: (z: String, f: String => slick.jdbc.H2Profile.api.DBIO[Option[String]], acc: Seq[String])slick.jdbc.H2Profile.api.DBIO[Seq[String]]

The trick here is to recognize that:

  1. this is a recursive problem, so we need to define a stopping condition;

  2. we need flatMap to sequence queries ; and

  3. we need to accumulate results from each step.

In code…

def unfold(
  z: String,
  f: String => DBIO[Option[String]],
  acc: Seq[String] = Seq.empty
): DBIO[Seq[String]] =
  f(z).flatMap {
    case None    => DBIO.successful(acc :+ z)
    case Some(r) => unfold(r, f, acc :+ z)
  }
// unfold: (z: String, f: String => slick.jdbc.H2Profile.api.DBIO[Option[String]], acc: Seq[String])slick.jdbc.H2Profile.api.DBIO[Seq[String]]

The basic idea is to call our action (f) on the first room name (z). If there’s no result from the query, we’re done. Otherwise we add the room to the list of rooms, and recurse starting from the room we just found.

Here’s how we’d use it:

def nextRoom(roomName: String): DBIO[Option[String]] =
  floorplan.filter(_.name === roomName).map(_.connectsTo).result.headOption
// nextRoom: (roomName: String)slick.jdbc.H2Profile.api.DBIO[Option[String]]

val path: DBIO[Seq[String]] = unfold("Podbay", nextRoom)
// path: slick.jdbc.H2Profile.api.DBIO[Seq[String]] = FlatMapAction(slick.jdbc.StreamingInvokerAction$HeadOptionAction@6f96c2a3,$$Lambda$8755/1929444694@54ed3868,scala.concurrent.impl.ExecutionContextImpl@7c202862)

exec(path)
// res49: Seq[String] = List(Podbay, Galley, Computer, Engine Room)

5 Data Modelling

We can do the basics of connecting to a database, running queries, and changing data. We turn now to richer models of data and how our application hangs together.

In this chapter we will:

To do this, we’ll expand the chat application schema to support more than just messages.

5.1 Application Structure

So far, all of our examples have been written in a single Scala file. This approach doesn’t scale to larger application codebases. In this section we’ll explain how to split up application code into modules.

Until now we’ve also been exclusively using Slick’s H2 profile. When writing real applications we often need to be able to switch profiles in different circumstances. For example, we may use PostgreSQL in production and H2 in our unit tests.

An example of this pattern can be found in the example project, folder chapter-05, file structure.scala.

5.1.1 Abstracting over Databases

Let’s look at how we can write code that works with multiple different database profiles. When we previously wrote…

import slick.jdbc.H2Profile.api._
// import slick.jdbc.H2Profile.api._

…we were locking ourselves into H2. We want to write an import that works with a variety of profiles. Fortunately, Slick provides a common supertype for profiles—a trait called JdbcProfile:

import slick.jdbc.JdbcProfile
// import slick.jdbc.JdbcProfile

We can’t import directly from JdbcProfile because it isn’t a concrete object. Instead, we have to inject a dependency of type JdbcProfile into our application and import from that. The basic pattern we’ll use is as follows:

Here’s a simple form of this pattern:

trait DatabaseModule {
  // Declare an abstract profile:
  val profile: JdbcProfile

  // Import the Slick API from the profile:
  import profile.api._

  // Write our database code here...
}
// defined trait DatabaseModule

object Main extends App {
  // Instantiate the database module, assigning a concrete profile:
  val databaseLayer = new DatabaseModule {
    val profile = slick.jdbc.H2Profile
  }
}
// defined object Main

In this pattern, we declare our profile using an abstract val. This is enough to allow us to write import profile.api._. The compiler knows that the val is going to be an immutable JdbcProfile even if we haven’t yet said which one. When we instantiate the DatabaseModule we bind profile to our profile of choice.

5.1.2 Scaling to Larger Codebases

As our applications get bigger, we need to split our code up into multiple files to keep it manageable. We can do this by extending the pattern above to a family of traits:

trait Profile {
  val profile: JdbcProfile
}
// defined trait Profile

trait DatabaseModule1 { self: Profile =>
  import profile.api._

  // Write database code here
}
// defined trait DatabaseModule1

trait DatabaseModule2 { self: Profile =>
  import profile.api._

  // Write more database code here
}
// defined trait DatabaseModule2

// Mix the modules together:
class DatabaseLayer(val profile: JdbcProfile) extends
  Profile with
  DatabaseModule1 with
  DatabaseModule2
// defined class DatabaseLayer

// Instantiate the modules and inject a profile:
object Main extends App {
  val databaseLayer = new DatabaseLayer(slick.jdbc.H2Profile)
}
// defined object Main

Here we factor out our profile dependency into its own Profile trait. Each module of database code specifies Profile as a self-type, meaning it can only be extended by a class that also extends Profile. This allows us to share the profile across our family of modules.

To work with a different database, we inject a different profile when we instantiate the database code:

val anotherDatabaseLayer = new DatabaseLayer(slick.jdbc.PostgresProfile)
// anotherDatabaseLayer: DatabaseLayer = DatabaseLayer@46c99bda

This basic pattern is reasonable way of structuring your application.

5.2 Representations for Rows

In previous chapters we modelled rows as case classes. Although this is a common usage pattern, and the one we recommend, there are several representation options available, including tuples, case classes, and HLists. Let’s investigate these by looking in more detail at how Slick relates columns in our database to fields in our classes.

5.2.1 Projections, ProvenShapes, mapTo, and <>

When we declare a table in Slick, we are required to implement a * method that specifies a “default projection”:

final class MyTable(tag: Tag) extends Table[(String, Int)](tag, "mytable") {
  def column1 = column[String]("column1")
  def column2 = column[Int]("column2")
  def * = (column1, column2)
}
// defined class MyTable

Expose Only What You Need

We can hide information by excluding it from our row definition. The default projection controls what is returned, in what order, and is driven by our row definition.

For example, we don’t need to map everything in a table with legacy columns that aren’t being used.

Projections provide mappings between database columns and Scala values. In the code above, the definition of * is mapping column1 and column2 from the database to the (String, Int) tuples defined in the extends Table clause.

If we look at the definition of * in the Table class, we see something confusing:

abstract class Table[T] {
  def * : ProvenShape[T]
}

The type of * is actually something called a ProvenShape, not a tuple of columns as we specified in our example. There is clearly something else going on here. Slick is using implicit conversions to build a ProvenShape object from the columns we provided.

The internal workings of ProvenShape are certainly beyond the scope of this book. Suffice to say that Slick can use any Scala type as a projection provided it can generate a compatible ProvenShape. If we look at the rules for ProvenShape generation, we will get an idea about what data types we can map. Here are the three most common use cases:

  1. Single column definitions produce shapes that map the column contents to a value of the column’s type parameter. For example, a column of Rep[String] maps a value of type String:

    final class MyTable(tag: Tag) extends Table[String](tag, "mytable") {
      def column1 = column[String]("column1")
      def * = column1
    }
  2. Tuples of database columns map tuples of their type parameters. For example, (Rep[String], Rep[Int]) is mapped to (String, Int):

    final class MyTable(tag: Tag) extends Table[(String, Int)](tag, "mytable") {
      def column1 = column[String]("column1")
      def column2 = column[Int]("column2")
      def * = (column1, column2)
    }
  3. If we have a ProvenShape[A], we can convert it to a ProvenShape[B] using the “projection operator” <>. In this example we know we can get ProvenShape[A] when the A is the String and Int tuple (from the previous example). We supply functions to convert each way between A and B and Slick builds the resulting shape. Here our B is the User case class:

    case class User(name: String, id: Long)
    
    final class UserTable(tag: Tag) extends Table[User](tag, "user") {
      def id   = column[Long]("id", O.PrimaryKey, O.AutoInc)
      def name = column[String]("name")
      def * = (name, id) <> (User.tupled, User.unapply)
    }

The projection operator <> is the secret ingredient that allows us to map a wide variety of types. As long as we can convert a tuple of columns to and from some type B, we can store instances of B in a database.

We’ve not seen <> until now because the mapTo macro builds a projection for us. In most situations mapTo is both more convenient and more efficient to use than <>. However, <> is available and worth knowing about if we need more control over the mapping. It will also be a method you see a great deal in code bases created before Slick 3.2.

The two arguments to <> are:

We can supply these functions by hand if we want:

def intoUser(pair: (String, Long)): User =
  User(pair._1, pair._2)
// intoUser: (pair: (String, Long))User

def fromUser(user: User): Option[(String, Long)] =
  Some((user.name, user.id))
// fromUser: (user: User)Option[(String, Long)]

and write:

final class UserTable(tag: Tag) extends Table[User](tag, "user") {
  def id   = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def * = (name, id) <> (intoUser, fromUser)
}
// defined class UserTable

In the User example, the case class supplies these functions via User.tupled and User.unapply, so we don’t need to build them ourselves. However it is useful to remember that we can provide our own functions for more elaborate packaging and unpackaging of rows. We will see this in one of the exercises in this chapter.

In this section we’ve looked at the details of projections. In general, though, the mapTo macro is sufficient for many situations.

5.2.2 Tuples versus Case Classes

We’ve seen how Slick is able to map case classes and tuples of values. But which should we use? In one sense there is little difference between case classes and tuples—both represent fixed sets of values. However, case classes differ from tuples in two important respects.

First, case classes have field names, which improves code readability:

val user = User("Dave", 0L)
// user: User = User(Dave,0)

user.name // case class field access
// res7: String = Dave

val tuple = ("Dave", 0L)
// tuple: (String, Long) = (Dave,0)

tuple._1 // tuple field access
// res8: String = Dave

Second, case classes have types that distinguish them from other case classes with the same field types:

case class Dog(name: String, id: Long)
// defined class Dog

val user = User("Dave", 0L)
// user: User = User(Dave,0)

val dog  = Dog("Lassie", 0L)
// dog: Dog = Dog(Lassie,0)
user == dog // different types
// <console>:19: warning: comparing case class values of types User and Dog using `==' will always yield false
//        user == dog // different types
//             ^
// error: No warnings can be incurred under -Xfatal-warnings.

As a general rule, we recommend using case classes to represent database rows for these reasons.

5.2.3 Heterogeneous Lists

We’ve seen how Slick can map database tables to tuples and case classes. Scala veterans identify a key weakness in this approach—tuples and case classes don’t run into limitations at 22 fields1.

Many of us have heard horror stories of legacy tables in enterprise databases that have tens or hundreds of columns. How do we map these rows? Fortunately, Slick provides an HList implementation to support tables with very large numbers of columns.

To motivate this, let’s consider a poorly-designed legacy table for storing product attributes:

case class Attr(id: Long, productId: Long /* ...etc */)
// defined class Attr

final class AttrTable(tag: Tag) extends Table[Attr](tag, "attrs") {
  def id        = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def productId = column[Long]("product_id")
  def name1     = column[String]("name1")
  def value1    = column[Int]("value1")
  def name2     = column[String]("name2")
  def value2    = column[Int]("value2")
  def name3     = column[String]("name3")
  def value3    = column[Int]("value3")
  def name4     = column[String]("name4")
  def value4    = column[Int]("value4")
  def name5     = column[String]("name5")
  def value5    = column[Int]("value5")
  def name6     = column[String]("name6")
  def value6    = column[Int]("value6")
  def name7     = column[String]("name7")
  def value7    = column[Int]("value7")
  def name8     = column[String]("name8")
  def value8    = column[Int]("value8")
  def name9     = column[String]("name9")
  def value9    = column[Int]("value9")
  def name10    = column[String]("name10")
  def value10   = column[Int]("value10")
  def name11    = column[String]("name11")
  def value11   = column[Int]("value11")
  def name12    = column[String]("name12")
  def value12   = column[Int]("value12")

  def * = ??? // we'll fill this in below
}
// defined class AttrTable

Hopefully you don’t have a table like this at your organization, but accidents do happen.

This table has 26 columns—too many to model using flat tuples. Fortunately, Slick provides an alternative mapping representation that scales to arbitrary numbers of columns. This representation is called a heterogeneous list or HList2.

An HList is a sort of hybrid of a list and a tuple. It has an arbitrary length like a List, but each element can be a different type like a tuple. Here are some examples:

import slick.collection.heterogeneous.{HList, HCons, HNil}
import slick.collection.heterogeneous.syntax._
val emptyHList = HNil
// emptyHList: slick.collection.heterogeneous.HNil.type = HNil

val shortHList: Int :: HNil = 123 :: HNil
// shortHList: Int :: slick.collection.heterogeneous.syntax.HNil = 123 :: HNil

val longerHList: Int :: String :: Boolean :: HNil =
  123 :: "abc" :: true :: HNil
// longerHList: Int :: (String :: (Boolean :: slick.collection.heterogeneous.syntax.HNil)) = 123 :: abc :: true :: HNil

HLists are constructed recursively like Lists, allowing us to model arbitrarily large collections of values:

Notice the types and values of each HList mirror each other: the longerHList comprises values of types Int, String, and Boolean, and its type comprises the types Int, String, and Boolean as well. Because the element types are preserved, we can write code that takes each precise type into account.

Slick is able to produce ProvenShapes to map HLists of columns to HLists of their values. For example, the shape for a Rep[Int] :: Rep[String] :: HNil maps values of type Int :: String :: HNil.

5.2.3.1 Using HLists Directly

We can use an HList to map the large table in our example above. Here’s what the default projection looks like:

import slick.collection.heterogeneous.{ HList, HCons, HNil }
import slick.collection.heterogeneous.syntax._
import scala.language.postfixOps
type AttrHList =
  Long :: Long ::
  String :: Int :: String :: Int :: String :: Int ::
  String :: Int :: String :: Int :: String :: Int ::
  String :: Int :: String :: Int :: String :: Int ::
  String :: Int :: String :: Int :: String :: Int ::
  HNil
// defined type alias AttrHList

final class AttrTable(tag: Tag) extends Table[AttrHList](tag, "attrs") {
  def id        = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def productId = column[Long]("product_id")
  def name1     = column[String]("name1")
  def value1    = column[Int]("value1")
  def name2     = column[String]("name2")
  def value2    = column[Int]("value2")
  def name3     = column[String]("name3")
  def value3    = column[Int]("value3")
  def name4     = column[String]("name4")
  def value4    = column[Int]("value4")
  def name5     = column[String]("name5")
  def value5    = column[Int]("value5")
  def name6     = column[String]("name6")
  def value6    = column[Int]("value6")
  def name7     = column[String]("name7")
  def value7    = column[Int]("value7")
  def name8     = column[String]("name8")
  def value8    = column[Int]("value8")
  def name9     = column[String]("name9")
  def value9    = column[Int]("value9")
  def name10    = column[String]("name10")
  def value10   = column[Int]("value10")
  def name11    = column[String]("name11")
  def value11   = column[Int]("value11")
  def name12    = column[String]("name12")
  def value12   = column[Int]("value12")

  def * = id :: productId ::
          name1 :: value1 :: name2 :: value2 :: name3 :: value3 ::
          name4 :: value4 :: name5 :: value5 :: name6 :: value6 ::
          name7 :: value7 :: name8 :: value8 :: name9 :: value9 ::
          name10 :: value10 :: name11 :: value11 :: name12 :: value12 ::
          HNil
}
// defined class AttrTable

val attributes = TableQuery[AttrTable]
// attributes: slick.lifted.TableQuery[AttrTable] = Rep(TableExpansion)

Writing HList types and values is cumbersome and error prone, so we’ve introduced a type alias of AttrHList to help us.

Working with this table involves inserting, updating, selecting, and modifying instances of AttrHList. For example:

import scala.concurrent.ExecutionContext.Implicits.global
// import scala.concurrent.ExecutionContext.Implicits.global

val program: DBIO[Seq[AttrHList]] = for {
  _ <- attributes.schema.create
  _ <- attributes += 0L :: 100L ::
        "name1"  :: 1  :: "name2"  :: 2  :: "name3"  :: 3  ::
        "name4"  :: 4  :: "name5"  :: 5  :: "name6"  :: 6  ::
        "name7"  :: 7  :: "name8"  :: 8  :: "name9"  :: 9  ::
        "name10" :: 10 :: "name11" :: 11 :: "name12" :: 12 ::
        HNil
  rows <- attributes.filter(_.value1 === 1).result
} yield rows
// program: slick.jdbc.H2Profile.api.DBIO[Seq[AttrHList]] = FlatMapAction(slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@7c919a59,$$Lambda$4796/994794916@1dd65e12,scala.concurrent.impl.ExecutionContextImpl@7c202862)

val myAttrs: AttrHList = exec(program).head
// myAttrs: AttrHList = 1 :: 100 :: name1 :: 1 :: name2 :: 2 :: name3 :: 3 :: name4 :: 4 :: name5 :: 5 :: name6 :: 6 :: name7 :: 7 :: name8 :: 8 :: name9 :: 9 :: name10 :: 10 :: name11 :: 11 :: name12 :: 12 :: HNil

We can extract values from our query results HList using pattern matching or a variety of type-preserving methods defined on HList, including head, apply, drop, and fold:

val id: Long = myAttrs.head
// id: Long = 1

val productId: Long = myAttrs.tail.head
// productId: Long = 100

val name1: String = myAttrs(2)
// name1: String = name1

val value1: Int = myAttrs(3)
// value1: Int = 1

5.2.3.2 Using HLists and Case Classes

In practice we’ll want to map an HList representation to a regular class to make it easier to work with. Slick’s <> operator works with HList shapes as well as tuple shapes. To use it we’d have to produce our own mapping functions in place of the case class apply and unapply, but otherwise this approach is the same as we’ve seen for tuples.

However, the mapTo macro will generate the mapping between an HList and a case class for us:

// A case class for our very wide row:
case class Attrs(id: Long, productId: Long,
  name1: String, value1: Int, name2: String, value2: Int /* etc */)
// defined class Attrs

final class AttrTable(tag: Tag) extends Table[Attrs](tag, "attributes") {
  def id        = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def productId = column[Long]("product_id")
  def name1     = column[String]("name1")
  def value1    = column[Int]("value1")
  def name2     = column[String]("name2")
  def value2    = column[Int]("value2")
  /* etc */

  def * = (
    id :: productId ::
    name1 :: value1 :: name2 :: value2 /* etc */ ::
    HNil
  ).mapTo[Attrs]
}
// defined class AttrTable

val attributes = TableQuery[AttrTable]
// attributes: slick.lifted.TableQuery[AttrTable] = Rep(TableExpansion)

Notice the pattern is:

 def * = (some hlist).mapTo[case class with the same fields]

With this in place our table is defined on a plain Scala case class. We can query and modify the data as normal using case classes:

val program: DBIO[Seq[Attrs]] = for {
  _    <- attributes.schema.create
  _    <- attributes += Attrs(0L, 100L, "n1", 1, "n2", 2 /* etc */)
  rows <- attributes.filter(_.productId === 100L).result
} yield rows
// program: slick.jdbc.H2Profile.api.DBIO[Seq[Attrs]] = FlatMapAction(slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@4e2ad666,$$Lambda$5118/1938156774@758ffad8,scala.concurrent.impl.ExecutionContextImpl@7c202862)

exec(program)
// res11: Seq[Attrs] = Vector(Attrs(1,100,n1,1,n2,2))

Code Generation

Sometimes your code is the definitive description of the schema; other times it’s the database itself. The latter is the case when working with legacy databases, or database where the schema is managed independently of your Slick application.

When the database is considered the source truth in your organisation, the Slick code generator is an important tool. It allows you to connect to a database, generate the table definitions, and customize the code produced. For tables with wide rows, it produces an HList representation.

Prefer it to manually reverse engineering a schema by hand.

5.3 Table and Column Representation

Now we know how rows can be represented and mapped, let’s look in more detail at the representation of the table and the columns it comprises. In particular we’ll explore nullable columns, foreign keys, more about primary keys, composite keys, and options you can apply to a table.

5.3.1 Nullable Columns

Columns defined in SQL are nullable by default. That is, they can contain NULL as a value. Slick makes columns non-nullable by default—if you want a nullable column you model it naturally in Scala as an Option[T].

Let’s create a variant of User with an optional email address:

case class User(name: String, email: Option[String] = None, id: Long = 0L)
// defined class User

class UserTable(tag: Tag) extends Table[User](tag, "user") {
  def id    = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name  = column[String]("name")
  def email = column[Option[String]]("email")

  def * = (name, email, id).mapTo[User]
}
// defined class UserTable

lazy val users = TableQuery[UserTable]
// users: slick.lifted.TableQuery[UserTable] = <lazy>

lazy val insertUser = users returning users.map(_.id)
// insertUser: slick.jdbc.H2Profile.ReturningInsertActionComposer[UserTable#TableElementType,Long] = <lazy>

We can insert users with or without an email address:

val program = (
  users.schema.create >>
  (users += User("Dave", Some("dave@example.org"))) >>
  (users += User("HAL"))
)
// program: slick.dbio.DBIOAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Schema with slick.dbio.Effect.Write with slick.dbio.Effect.Write] = slick.dbio.SynchronousDatabaseAction$FusedAndThenAction@50ef4102

exec(program)
// res12: Int = 1

and retrieve them again with a select query:

exec(users.result).foreach(println)
// User(Dave,Some(dave@example.org),1)
// User(HAL,None,2)

So far, so ordinary. What might be a surprise is how you go about selecting all rows that have no email address. You might expect the following to find the one row that has no email address:

// Don't do this
val none: Option[String] = None
// none: Option[String] = None

val myUsers = exec(users.filter(_.email === none).result)
// myUsers: Seq[UserTable#TableElementType] = Vector()

Despite the fact that we do have one row in the database no email address, this query produces no results.

Veterans of database administration will be familiar with this interesting quirk of SQL: expressions involving null themselves evaluate to null. For example, the SQL expression 'Dave' = 'HAL' evaluates to false, whereas the expression 'Dave' = null evaluates to null.

Our Slick query above amounts to:

SELECT * FROM "user" WHERE "email" = NULL

The SQL expression "email" = null evaluates to null for any value of "email". SQL’s null is a falsey value, so this query never returns a value.

To resolve this issue, SQL provides two operators: IS NULL and IS NOT NULL, which are provided in Slick by the methods isEmpty and isDefined on any Rep[Option[A]]:

Optional column methods. Operand and result types should be interpreted as parameters to Rep[_]. The ? method is described in the next section.
Scala Code Operand Column Types Result Type SQL Equivalent
col.? A Option[A] col
col.isEmpty Option[A] Boolean col is null
col.isDefined Option[A] Boolean col is not null

We can fix our query by replacing our equality check with isEmpty:

val myUsers = exec(users.filter(_.email.isEmpty).result)
// myUsers: Seq[UserTable#TableElementType] = Vector(User(HAL,None,2))

which translates to the following SQL:

SELECT * FROM "user" WHERE "email" IS NULL

5.3.2 Primary Keys

We had our first introduction to primary keys in Chapter 1, where we started setting up id fields using the O.PrimaryKey and O.AutoInc column options:

def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

These options do two things:

In Chapter 1 we combined O.AutoInc with a case class that has a default ID of 0L, knowing that Slick will skip the value in insert statements:

case class User(name: String, id: Long = 0L)
// defined class User

While we like the simplicity of this style, some developers prefer to wrap primary key values in Options:

case class User(name: String, id: Option[Long] = None)
// defined class User

In this model we use None as the primary key of an unsaved record and Some as the primary key of a saved record. This approach has advantages and disadvantages:

Let’s look at the changes we need to make to our UserTable to make this work:

case class User(id: Option[Long], name: String)
// defined class User

class UserTable(tag: Tag) extends Table[User](tag, "user") {
  def id     = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name   = column[String]("name")

  def * = (id.?, name).mapTo[User]
}
// defined class UserTable

lazy val users = TableQuery[UserTable]
// users: slick.lifted.TableQuery[UserTable] = <lazy>

lazy val insertUser = users returning users.map(_.id)
// insertUser: slick.jdbc.H2Profile.ReturningInsertActionComposer[UserTable#TableElementType,Long] = <lazy>

The key thing to notice here is that we don’t want the primary key to be optional in the database. We’re using None to represent an unsaved value—the database assigns a primary key for us on insert, so we can never retrieve a None via a database query.

We need to map our non-nullable database column to an optional field value. This is handled by the ? method in the default projection, which converts a Rep[A] to a Rep[Option[A]].

5.3.3 Compound Primary Keys

There is a second way to declare a column as a primary key:

def id = column[Long]("id", O.AutoInc)
def pk = primaryKey("pk_id", id)

This separate step doesn’t make much of a difference in this case. It separates the column definition from the key constraint, meaning the schema will include:

ALTER TABLE "user" ADD CONSTRAINT "pk_id" PRIMARY KEY("id")

The primaryKey method is more useful for defining compound primary keys that involve two or more columns.

Let’s look at this by adding the ability for people to chat in rooms. First we need a table for storing rooms, which is straightforward:

// Regular table definition for a chat room:
case class Room(title: String, id: Long = 0L)
// defined class Room

class RoomTable(tag: Tag) extends Table[Room](tag, "room") {
 def id    = column[Long]("id", O.PrimaryKey, O.AutoInc)
 def title = column[String]("title")
 def * = (title, id).mapTo[Room]
}
// defined class RoomTable

lazy val rooms = TableQuery[RoomTable]
// rooms: slick.lifted.TableQuery[RoomTable] = <lazy>

lazy val insertRoom = rooms returning rooms.map(_.id)
// insertRoom: slick.jdbc.H2Profile.ReturningInsertActionComposer[RoomTable#TableElementType,Long] = <lazy>

Next we need a table that relates users to rooms. We’ll call this the occupant table. Rather than give this table an auto-generated primary key, we’ll make it a compound of the user and room IDs:

case class Occupant(roomId: Long, userId: Long)
// defined class Occupant

class OccupantTable(tag: Tag) extends Table[Occupant](tag, "occupant") {
  def roomId = column[Long]("room")
  def userId = column[Long]("user")

  def pk = primaryKey("room_user_pk", (roomId, userId))

  def * = (roomId, userId).mapTo[Occupant]
}
// defined class OccupantTable

lazy val occupants = TableQuery[OccupantTable]
// occupants: slick.lifted.TableQuery[OccupantTable] = <lazy>

We can define composite primary keys using tuples or HLists of columns (Slick generates a ProvenShape and inspects it to find the list of columns involved). The SQL generated for the occupant table is:

CREATE TABLE "occupant" (
  "room" BIGINT NOT NULL,
  "user" BIGINT NOT NULL
)

ALTER TABLE "occupant"
ADD CONSTRAINT "room_user_pk" PRIMARY KEY("room", "user")

Using the occupant table is no different from any other table:

val program: DBIO[Int] = for {
   _        <- rooms.schema.create
   _        <- occupants.schema.create
  elenaId   <- insertUser += User(None, "Elena")
  airLockId <- insertRoom += Room("Air Lock")
  // Put Elena in the Room:
  rowsAdded <- occupants += Occupant(airLockId, elenaId)
} yield rowsAdded
// program: slick.jdbc.H2Profile.api.DBIO[Int] = FlatMapAction(slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@1a70e264,$$Lambda$5166/1336768470@5afcfece,scala.concurrent.impl.ExecutionContextImpl@7c202862)

exec(program)
// res16: Int = 1

Of course, if we try to put Dave in the Air Lock twice, the database will complain about duplicate primary keys.

5.3.4 Indices

We can use indices to increase the efficiency of database queries at the cost of higher disk usage. Creating and using indices is the highest form of database sorcery, different for every database application, and well beyond the scope of this book. However, the syntax for defining an index in Slick is simple. Here’s a table with two calls to index:

class IndexExample(tag: Tag) extends Table[(String,Int)](tag, "people") {
  def name = column[String]("name")
  def age  = column[Int]("age")

  def * = (name, age)

  def nameIndex = index("name_idx", name, unique=true)
  def compoundIndex = index("c_idx", (name, age), unique=true)
}
// defined class IndexExample

The corresponding DDL statement produced due to nameIndex will be:

CREATE UNIQUE INDEX "name_idx" ON "people" ("name")

We can create compound indices on multiple columns just like we can with primary keys. In this case (compoundIndex) the corresponding DDL statement will be:

CREATE UNIQUE INDEX "c_idx" ON "people" ("name", "age")

5.3.5 Foreign Keys

Foreign keys are declared in a similar manner to compound primary keys.

The method foreignKey takes four required parameters:

We’ll step through this by using foreign keys to connect a message to a user. We do this by changing the definition of message to reference the id of its sender instead of their name:

case class Message(
  senderId : Long,
  content  : String,
  id       : Long = 0L)
// defined class Message

class MessageTable(tag: Tag) extends Table[Message](tag, "message") {
  def id       = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def senderId = column[Long]("sender")
  def content  = column[String]("content")

  def * = (senderId, content, id).mapTo[Message]

  def sender = foreignKey("sender_fk", senderId, users)(_.id)
}
// defined class MessageTable

lazy val messages = TableQuery[MessageTable]
// messages: slick.lifted.TableQuery[MessageTable] = <lazy>

The column for the sender is now a Long instead of a String. We have also defined a method, sender, providing the foreign key linking the senderId to a user id.

The foreignKey gives us two things. First, it adds a constraint to the DDL statement generated by Slick:

ALTER TABLE "message" ADD CONSTRAINT "sender_fk"
  FOREIGN KEY("sender") REFERENCES "user"("id")
  ON UPDATE NO ACTION
  ON DELETE NO ACTION

On Update and On Delete

A foreign key makes certain guarantees about the data you store. In the case we’ve looked at there must be a sender in the user table to successfully insert a new message.

So what happens if something changes with the user row? There are a number of referential actions that could be triggered. The default is for nothing to happen, but you can change that.

Let’s look at an example. Suppose we delete a user, and we want all the messages associated with that user to be removed. We could do that in our application, but it’s something the database can provide for us:

class AltMsgTable(tag: Tag) extends Table[Message](tag, "message") {
  def id       = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def senderId = column[Long]("sender")
  def content  = column[String]("content")

  def * = (senderId, content, id).mapTo[Message]

  def sender = foreignKey("sender_fk", senderId, users)(_.id, onDelete=ForeignKeyAction.Cascade)
}
// defined class AltMsgTable

Providing Slick’s schema command has been run for the table, or the SQL ON DELETE CASCADE action has been manually applied to the database, the following action will remove HAL from the users table, and all of the messages that HAL sent:

users.filter(_.name === "HAL").delete

Slick supports onUpdate and onDelete for the five actions:

Action Description
NoAction The default.
Cascade A change in the referenced table triggers a change in the referencing table. In our example, deleting a user will cause their messages to be deleted.
Restrict Changes are restricted, triggered a constraint violation exception. In our example, you would not be allowed to delete a user who had posted a message.
SetNull The column referencing the updated value will be set to NULL.
SetDefault The default value for the referencing column will be used. Default values are discussion in Table and Column Modifiers, later in this chapter.

Second, the foreign key gives us a query that we can use in a join. We’ve dedicated the next chapter to looking at joins in detail, but here’s a simple join to illustrate the use case:

val q = for {
  msg <- messages
  usr <- msg.sender
} yield (usr.name, msg.content)
// q: slick.lifted.Query[(slick.lifted.Rep[String], slick.lifted.Rep[String]),(String, String),Seq] = Rep(Bind)

This is equivalent to the query:

SELECT u."name", m."content"
FROM "message" m, "user" u
WHERE "id" = m."sender"

…and once we have populated the database…

def findUserId(name: String): DBIO[Option[Long]] =
  users.filter(_.name === name).map(_.id).result.headOption
// findUserId: (name: String)slick.jdbc.H2Profile.api.DBIO[Option[Long]]

def findOrCreate(name: String): DBIO[Long] =
  findUserId(name).flatMap { userId =>
    userId match {
      case Some(id) => DBIO.successful(id)
      case None     => insertUser += User(None, name)
    }
}
// findOrCreate: (name: String)slick.jdbc.H2Profile.api.DBIO[Long]

// Populate the messages table:
val setup = for {
  daveId <- findOrCreate("Dave")
  halId  <- findOrCreate("HAL")

  // Add some messages:
  _         <- messages.schema.create
  rowsAdded <- messages ++= Seq(
    Message(daveId, "Hello, HAL. Do you read me, HAL?"),
    Message(halId,  "Affirmative, Dave. I read you."),
    Message(daveId, "Open the pod bay doors, HAL."),
    Message(halId,  "I'm sorry, Dave. I'm afraid I can't do that.")
  )
} yield rowsAdded
// setup: slick.dbio.DBIOAction[Option[Int],slick.dbio.NoStream,slick.dbio.Effect.All with slick.dbio.Effect.All with slick.dbio.Effect.Schema with slick.dbio.Effect.Write] = FlatMapAction(FlatMapAction(slick.jdbc.StreamingInvokerAction$HeadOptionAction@5835ac52,$$Lambda$5235/698629801@50ec908b,scala.concurrent.impl.ExecutionContextImpl@7c202862),$$Lambda$5236/698336084@18945294,scala.concurrent.impl.ExecutionContextImpl@7c202862)

exec(setup)
// res20: Option[Int] = Some(4)

…our query produces the following results, showing the sender name (not ID) and corresponding message:

exec(q.result).foreach(println)
// (Dave,Hello, HAL. Do you read me, HAL?)
// (HAL,Affirmative, Dave. I read you.)
// (Dave,Open the pod bay doors, HAL.)
// (HAL,I'm sorry, Dave. I'm afraid I can't do that.)

Save Your Sanity With Laziness

Defining foreign keys places constraints on the order in which we have to define our database tables. In the example above, the foreign key from MessageTable to UserTable requires us to place the latter definition above the former in our Scala code.

Ordering constraints make complex schemas difficult to write. Fortunately, we can work around them using def and lazy val.

As a rule, use lazy val for TableQuerys and def foreign keys (for consistency with column definitions).

5.3.6 Column Options

We’ll round off this section by looking at modifiers for columns and tables. These allow us to tweak the default values, sizes, and data types for columns at the SQL level.

We have already seen two examples of column options, namely O.PrimaryKey and O.AutoInc. Column options are defined in ColumnOption, and as you have seen are accessed via O.

The following example introduces four new options: O.Length, O.SqlType, O.Unique, and O.Default.

case class PhotoUser(
  name   : String,
  email  : String,
  avatar : Option[Array[Byte]] = None,
  id     : Long = 0L)
// defined class PhotoUser

class PhotoTable(tag: Tag) extends Table[PhotoUser](tag, "user") {
  def id     = column[Long]("id", O.PrimaryKey, O.AutoInc)

  def name   = column[String](
                 "name",
                 O.Length(64, true),
                 O.Default("Anonymous Coward")
                )

  def email = column[String]("email", O.Unique)

  def avatar = column[Option[Array[Byte]]]("avatar", O.SqlType("BINARY(2048)"))

  def * = (name, email, avatar, id).mapTo[PhotoUser]
}
// defined class PhotoTable

In this example we’ve done four things:

  1. We’ve used O.Length to give the name column a maximum length. This modifies the type of the column in the DDL statement. The parameters to O.Length are an Int specifying the maximum length, and a Boolean indicating whether the length is variable. Setting the Boolean to true sets the SQL column type to VARCHAR; setting it to false sets the type to CHAR.

  2. We’ve used O.Default to give the name column a default value. This adds a DEFAULT clause to the column definition in the DDL statement.

  3. We added a uniqueness constraint on the email column.

  4. We’ve used O.SqlType to control the exact type used by the database. The values allowed here depend on the database we’re using.

5.4 Custom Column Mappings

We want to work with types that have meaning to our application. This means converting data from the simple types the database uses to something more developer-friendly.

We’ve already seen Slick’s ability to map tuples and HLists of columns to case classes. However, so far the fields of our case classes have been restricted to simple types such as Int and String,

Slick also lets us control how individual columns are mapped to Scala types. For example, perhaps we’d like to use Joda Time’s DateTime class for anything date and time related. Slick doesn’t provide native support for Joda Time, but it’s painless for us to implement it via Slick’s ColumnType type class:

import java.sql.Timestamp
import org.joda.time.DateTime
import org.joda.time.DateTimeZone.UTC
object CustomColumnTypes {
  implicit val jodaDateTimeType =
    MappedColumnType.base[DateTime, Timestamp](
      dt => new Timestamp(dt.getMillis),
      ts => new DateTime(ts.getTime, UTC)
    )
}
// defined object CustomColumnTypes

What we’re providing here is two functions to MappedColumnType.base:

Once we have declared this custom column type, we are free to create columns containing DateTimes:

case class Message(
  senderId  : Long,
  content   : String,
  timestamp : DateTime,
  id        : Long = 0L)
// defined class Message

class MessageTable(tag: Tag) extends Table[Message](tag, "message") {

  // Bring our implicit conversions into scope:
  import CustomColumnTypes._

  def id        = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def senderId  = column[Long]("sender")
  def content   = column[String]("content")
  def timestamp = column[DateTime]("timestamp")

  def * = (senderId, content, timestamp, id).mapTo[Message]
}
// defined class MessageTable

lazy val messages = TableQuery[MessageTable]
// messages: slick.lifted.TableQuery[MessageTable] = <lazy>

lazy val insertMessage = messages returning messages.map(_.id)
// insertMessage: slick.jdbc.H2Profile.ReturningInsertActionComposer[MessageTable#TableElementType,Long] = <lazy>

Reset Your Database

If you’ve been following along in the REPL, by now you’re going to have a bunch of tables and rows. Now is a good time to remove all of that.

You can exit the REPL and restart it. H2 is holding the data in memory, so turning it on and off again is one way to reset your database.

Alternatively, you can use an action:

val schemas = (users.schema ++
  messages.schema          ++
  occupants.schema         ++
  rooms.schema)
// schemas: slick.jdbc.H2Profile.DDL = slick.sql.SqlProfile$DDL$$anon$1@9d7ec033

exec(schemas.drop)

Our modified definition of MessageTable allows us to work directly with Messages containing DateTime timestamps, without having to do cumbersome type conversions by hand:

val program = for {
  _ <- messages.schema.create
  _ <- users.schema.create
  daveId <- insertUser += User(None, "Dave")
  msgId  <- insertMessage += Message(
    daveId,
    "Open the pod bay doors, HAL.",
    DateTime.now)
} yield msgId
// program: slick.dbio.DBIOAction[insertMessage.SingleInsertResult,slick.dbio.NoStream,slick.dbio.Effect.Schema with slick.dbio.Effect.Schema with slick.dbio.Effect.Write with slick.dbio.Effect.Write] = FlatMapAction(slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@6bbaf645,$$Lambda$5305/1649178118@595413d,scala.concurrent.impl.ExecutionContextImpl@7c202862)

val msgId = exec(program)
// msgId: insertMessage.SingleInsertResult = 1

Fetching the database row will automatically convert the timestamp field into the DateTime value we expect:

exec(messages.filter(_.id === msgId).result)
// res23: Seq[MessageTable#TableElementType] = Vector(Message(1,Open the pod bay doors, HAL.,2017-09-26T11:22:46.974Z,1))

This model of working with semantic types is immediately appealing to Scala developers. We strongly encourage you to use ColumnType in your applications, to help reduce bugs and let Slick take care of the type conversions.

5.4.1 Value Classes

We are currently using Longs to model primary keys. Although this is a good choice at a database level, it’s not great for our application code.

The problem is we can make silly mistakes, such as trying to look up a User by primary key using the primary key from a Message. They are both Longs, but trying to compare them makes no sense. And yet the code would compile, and could possibly return a result. But it’s likely to be the wrong result.

We can prevent these kinds of problems using types. The essential approach is to model primary keys using value classes:

case class MessagePK(value: Long) extends AnyVal
// defined class MessagePK

case class UserPK(value: Long) extends AnyVal
// defined class UserPK

A value class is a compile-time wrapper around a value. At run time, the wrapper goes away, leaving no allocation or performance overhead3 in our running code.

To use a value class we need to provide Slick with ColumnTypes to use these types with our tables. This is the same process we used for Joda Time DateTimes:

implicit val messagePKColumnType =
  MappedColumnType.base[MessagePK, Long](_.value, MessagePK(_))
// messagePKColumnType: slick.jdbc.H2Profile.BaseColumnType[MessagePK] = MappedJdbcType[MessagePK -> Long']

implicit val userPKColumnType =
   MappedColumnType.base[UserPK, Long](_.value, UserPK(_))
// userPKColumnType: slick.jdbc.H2Profile.BaseColumnType[UserPK] = MappedJdbcType[UserPK -> Long']

Defining all these type class instances can be time consuming, especially if we’re defining one for every table in our schema. Fortunately, Slick provides a short-hand called MappedTo to take care of this for us:

case class MessagePK(value: Long) extends AnyVal with MappedTo[Long]
// defined class MessagePK

case class UserPK(value: Long) extends AnyVal with MappedTo[Long]
// defined class UserPK

When we use MappedTo we don’t need to define a separate ColumnType. MappedTo works with any class that:

Value classes are a great fit for the MappedTo pattern.

Let’s redefine our tables to use our custom primary key types. We will convert User

case class User(name: String, id: UserPK = UserPK(0L))
// defined class User

class UserTable(tag: Tag) extends Table[User](tag, "user") {
  def id   = column[UserPK]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def * = (name, id).mapTo[User]
}
// defined class UserTable

lazy val users = TableQuery[UserTable]
// users: slick.lifted.TableQuery[UserTable] = <lazy>

lazy val insertUser = users returning users.map(_.id)
// insertUser: slick.jdbc.H2Profile.ReturningInsertActionComposer[UserTable#TableElementType,UserPK] = <lazy>

…and Message:

case class Message(
  senderId : UserPK,
  content  : String,
  id       : MessagePK = MessagePK(0L))
// defined class Message

class MessageTable(tag: Tag) extends Table[Message](tag, "message") {
  def id        = column[MessagePK]("id", O.PrimaryKey, O.AutoInc)
  def senderId  = column[UserPK]("sender")
  def content   = column[String]("content")

  def * = (senderId, content, id).mapTo[Message]

  def sender = foreignKey("sender_fk", senderId, users) (_.id, onDelete=ForeignKeyAction.Cascade)
}
// defined class MessageTable

lazy val messages      = TableQuery[MessageTable]
// messages: slick.lifted.TableQuery[MessageTable] = <lazy>

lazy val insertMessage = messages returning messages.map(_.id)
// insertMessage: slick.jdbc.H2Profile.ReturningInsertActionComposer[MessageTable#TableElementType,MessagePK] = <lazy>

Notice how we’re able to be explicit: the User.id and Message.senderId are UserPKs, and the Message.id is a MessagePK.

We can lookup values if we have the right kind of key:

users.filter(_.id === UserPK(0L))
// res24: slick.lifted.Query[UserTable,UserTable#TableElementType,Seq] = Rep(Filter @82454670)

…but if we accidentally try to mix our primary keys, we’ll find we cannot:

users.filter(_.id === MessagePK(0L))
// <console>:42: Error typechecking MappedTo expansion: class type required but Product with Serializable with slick.lifted.MappedTo[Long] with AnyVal found
// <console>:42: error: Cannot perform option-mapped operation
//       with type: (UserPK, MessagePK) => R
//   for base type: (UserPK, UserPK) => Boolean
//        users.filter(_.id === MessagePK(0L))
//                          ^
// <console>:42: error: ambiguous implicit values:
//  both value BooleanColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Boolean]]
//  and value BooleanOptionColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Option[Boolean]]]
//  match expected type slick.lifted.CanBeQueryCondition[Nothing]
//        users.filter(_.id === MessagePK(0L))
//                    ^

Values classes are a low-cost way to make code safer and more legible. The amount of code required is small, however for a large database it can still be an overhead. We can either use code generation to overcome this, or generalise our primary key type by making it generic:

// Inside an object to compile in the REPL:
object GenericPKModule {
  final case class PK[A](value: Long) extends AnyVal with MappedTo[Long]

  case class User(
    name : String,
    id   : PK[UserTable])

  class UserTable(tag: Tag) extends Table[User](tag, "user") {
    def id    = column[PK[UserTable]]("id", O.AutoInc, O.PrimaryKey)
    def name  = column[String]("name")

    def * = (name, id).mapTo[User]
  }

  lazy val users = TableQuery[UserTable]

  val exampleQuery =
    users.filter(_.id === PK[UserTable](0L))
}
// defined object GenericPKModule

With this approach we achieve type safety without the boiler plate of many primary key type definitions. Depending on the nature of your application, this may be convenient for you.

The general point is that we can use the whole of the Scala type system to represent primary keys, foreign keys, rows, and columns from our database. This is enormously valuable and should not be overlooked.

5.4.2 Modelling Sum Types

We’ve used case classes extensively for modelling data. Using the language of algebraic data types, case classes are “product types” (created from conjunctions of their field types). The other common form of algebraic data type is known as a sum type, formed from a disjunction of other types. We’ll look at modelling these now.

As an example let’s add a flag to our Message class to model messages as important, offensive, or spam. The natural way to do this is establish a sealed trait and a set of case objects:

sealed trait Flag
// defined trait Flag

case object Important extends Flag
// defined object Important

case object Offensive extends Flag
// defined object Offensive

case object Spam extends Flag
// defined object Spam

case class Message(
  senderId : UserPK,
  content  : String,
  flag     : Option[Flag] = None,
  id       : MessagePK = MessagePK(0L))
// defined class Message

There are a number of ways we could represent the flags in the database. For the sake of the argument, let’s use characters: !, X, and $. We need a new custom ColumnType to manage the mapping:

implicit val flagType =
  MappedColumnType.base[Flag, Char](
    flag => flag match {
      case Important => '!'
      case Offensive => 'X'
      case Spam      => '$'
    },
    code => code match {
      case '!' => Important
      case 'X' => Offensive
      case '$' => Spam
    })
// flagType: slick.jdbc.H2Profile.BaseColumnType[Flag] = MappedJdbcType[Flag -> Char']

We like sum types because the compiler can ensure we’ve covered all the cases. If we add a new flag (OffTopic perhaps), the compiler will issue warnings until we add it to our Flag => Char function. We can turn these compiler warnings into errors by enabling the Scala compiler’s -Xfatal-warnings option, preventing us shipping the application until we’ve covered all bases.

Using Flag is the same as any other custom type:

class MessageTable(tag: Tag) extends Table[Message](tag, "flagmessage") {
  def id       = column[MessagePK]("id", O.PrimaryKey, O.AutoInc)
  def senderId = column[UserPK]("sender")
  def content  = column[String]("content")
  def flag     = column[Option[Flag]]("flag")

  def * = (senderId, content, flag, id).mapTo[Message]

  def sender = foreignKey("sender_fk", senderId, users)(_.id, onDelete=ForeignKeyAction.Cascade)
}
// defined class MessageTable

lazy val messages = TableQuery[MessageTable]
// messages: slick.lifted.TableQuery[MessageTable] = <lazy>

exec(messages.schema.create)

We can insert a message with a flag easily:

val halId = UserPK(1L)
// halId: UserPK = UserPK(1)

exec(
  messages += Message(
    halId,
    "Just kidding - come on in! LOL.",
    Some(Important)
  )
)
// res28: Int = 1

We can also query for messages with a particular flag. However, we need to give the compiler a little help with the types:

exec(
  messages.filter(_.flag === (Important : Flag)).result
)
// res29: Seq[MessageTable#TableElementType] = Vector(Message(UserPK(1),Just kidding - come on in! LOL.,Some(Important),MessagePK(1)))

The type annotation here is annoying. We can work around it in two ways:

First, we can define a “smart constructor” method for each flag that returns it pre-cast as a Flag:

object Flags {
  val important : Flag = Important
  val offensive : Flag = Offensive
  val spam      : Flag = Spam

  val action = messages.filter(_.flag === Flags.important).result
}
// defined object Flags

Second, we can define some custom syntax to build our filter expressions:

implicit class MessageQueryOps(message: MessageTable) {
  def isImportant = message.flag === (Important : Flag)
  def isOffensive = message.flag === (Offensive : Flag)
  def isSpam      = message.flag === (Spam      : Flag)
}
// defined class MessageQueryOps

messages.filter(_.isImportant).result.statements.head
// res30: String = select "sender", "content", "flag", "id" from "flagmessage" where "flag" = '!'

5.5 Take Home Points

In this Chapter we covered a lot of Slick’s features for defining database schemas. We went into detail about defining tables and columns, mapping them to convenient Scala types, adding primary keys, foreign keys, and indices, and customising Slick’s DDL SQL. We also discussed writing generic code that works with multiple database back-ends, and how to structure the database layer of your application using traits and self-types.

The most important points are:

Slick’s philosophy is to keep models simple. We model rows as flat case classes, ignoring joins with other tables. While this may seem inflexible at first, it more than pays for itself in terms of simplicity and transparency. Database queries are explicit and type-safe, and return values of convenient types.

In the next chapter we will build on the foundations of primary and foreign keys and look at writing more complex queries involving joins and aggregate functions.

5.6 Exercises

5.6.1 Filtering Optional Columns

Imagine a reporting tool on a web site. Sometimes you want to look at all the users in the database, and sometimes you want to only see rows matching a particular value.

Working with the optional email address for a user, write a method that will take an optional value, and list rows matching that value.

The method signature is:

def filterByEmail(email: Option[String]) = ???
// filterByEmail: (email: Option[String])Nothing

Assume we only have two user records: one with an email address and one with no email address:

case class User(name: String, email: Option[String], id: Long = 0)

class UserTable(tag: Tag) extends Table[User](tag, "filtering") {
  def id     = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name   = column[String]("name")
  def email  = column[Option[String]]("email")

  def * = (name, email, id).mapTo[User]
}

lazy val users = TableQuery[UserTable]

val setup = DBIO.seq(
  users.schema.create,
  users += User("Dave", Some("dave@example.org")),
  users += User("HAL ", None)
)

exec(setup)

We want filterByEmail(Some("dave@example.org")) to produce one row, and filterByEmail(None) to produce two rows:

Tip: it’s OK to use multiple queries.

We can decide on the query to run in the two cases from inside our application:

def filterByEmail(email: Option[String]) =
  email.isEmpty match {
    case true  => users
    case false => users.filter(_.email === email)
  }
// filterByEmail: (email: Option[String])slick.lifted.Query[UserTable,UserTable#TableElementType,Seq]

You don’t always have to do everything at the SQL level.

exec(
  filterByEmail(Some("dave@example.org")).result
).foreach(println)
// User(Dave,Some(dave@example.org),1)

exec(
  filterByEmail(None).result
).foreach(println)
// User(Dave,Some(dave@example.org),1)
// User(HAL ,None,2)

5.6.2 Matching or Undecided

Not everyone has an email address, so perhaps when filtering it would be safer to exclude rows that don’t match our filter criteria. That is, keep NULL addresses in the results.

Add Elena to the database…

exec(
  users += User("Elena", Some("elena@example.org"))
)
// res38: Int = 1

…and modify filterByEmail so when we search for Some("elena@example.org") we only exclude Dave, as he definitely doesn’t match that address.

This time you can do this in one query.

Hint: if you get stuck thinking about this in terms of SQL, think about it in terms of Scala collections. E.g.,

List(Some("dave"), Some("elena"), None).filter( ??? ) == List(Some("elena", None))

This problem we can represent in SQL, so we can do it with one query:

def filterByEmail(email: Option[String]) =
  users.filter(u => u.email.isEmpty || u.email === email)
// filterByEmail: (email: Option[String])slick.lifted.Query[UserTable,UserTable#TableElementType,Seq]

In this implementation we’ve decided that if you search for email addresses matching None, we only return NULL email address. But you could switch on the value of email and do something different, as we did in previous exercises.

5.6.3 Enforcement

What happens if you try adding a message for a user ID of 3000?

For example:

messages += Message(UserPK(3000L), "Hello HAL!")

Note that there is no user in our example with an ID of 3000.

We get a runtime exception as we have violated referential integrity. There is no row in the user table with a primary id of 3000.

val action = messages += Message(UserPK(3000L), "Hello HAL!")
// action: slick.sql.FixedSqlAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction@18fc59d

exec(action.asTry)
// res42: scala.util.Try[Int] =
// Failure(org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "sender_fk: PUBLIC.""flagmessage"" FOREIGN KEY(""sender"") REFERENCES PUBLIC.""user""(""id"") (3000)"; SQL statement:
// insert into "flagmessage" ("sender","content","flag")  values (?,?,?) [23506-185])

5.6.4 Mapping Enumerations

We can use the same trick that we’ve seen for DateTime and value classes to map enumerations.

Here’s a Scala Enumeration for a user’s role:

object UserRole extends Enumeration {
  type UserRole = Value
  val Owner   = Value("O")
  val Regular = Value("R")
}
// defined object UserRole

Modify the user table to include a UserRole. In the database store the role as a single character.

The first step is to supply an implicit to and from the database values:

object UserRole extends Enumeration {
  type UserRole = Value
  val Owner   = Value("O")
  val Regular = Value("R")
}
// defined object UserRole

import UserRole._
// import UserRole._

implicit val userRoleMapper =
  MappedColumnType.base[UserRole, String](_.toString, UserRole.withName(_))
// userRoleMapper: slick.jdbc.H2Profile.BaseColumnType[UserRole.UserRole] = MappedJdbcType[scala.Enumeration$Value -> String']

Then we can use the UserRole in the table definition:

case class User(
  name     : String,
  userRole : UserRole = Regular,
  id       : UserPK = UserPK(0L)
)
// defined class User

class UserTable(tag: Tag) extends Table[User](tag, "user_with_role") {
  def id   = column[UserPK]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def role = column[UserRole]("role", O.Length(1,false))

  def * = (name, role, id).mapTo[User]
}
// defined class UserTable

lazy val users = TableQuery[UserTable]
// users: slick.lifted.TableQuery[UserTable] = <lazy>

We’ve made the role column exactly 1 character in size.

5.6.5 Alternative Enumerations

Modify your solution to the previous exercise to store the value in the database as an integer.

If you see an unrecognized user role value, default it to a UserRole.Regular.

The only change to make is to the mapper, to go from a UserRole and String, to a UserRole and Int:

implicit val userRoleMapper =
  MappedColumnType.base[UserRole, Int](
    _.id,
    v => UserRole.values.find(_.id == v) getOrElse Regular
  )
// userRoleMapper: slick.jdbc.H2Profile.BaseColumnType[UserRole.UserRole] = MappedJdbcType[scala.Enumeration$Value -> Int']

5.6.6 Custom Boolean

Messages can be high priority or low priority.

The database is a bit of a mess:

Go ahead and model this with a sum type.

This is similar to the Flag example above, except we need to handle multiple values from the database.

sealed trait Priority
// defined trait Priority

case object HighPriority extends Priority
// defined object HighPriority

case object LowPriority  extends Priority
// defined object LowPriority

implicit val priorityType =
  MappedColumnType.base[Priority, String](
    flag => flag match {
      case HighPriority => "y"
      case LowPriority  => "n"
    },
    str => str match {
      case "Y" | "y" | "+" | "high"         => HighPriority
      case "N" | "n" | "-" | "lo"   | "low" => LowPriority
  })
// priorityType: slick.jdbc.H2Profile.BaseColumnType[Priority] = MappedJdbcType[Priority -> String']

The table definition would need a column[Priority].

5.6.7 Turning a Row into Many Case Classes

Our HList example mapped a table with many columns. It’s not the only way to deal with lots of columns.

Use custom functions with <> and map UserTable into a tree of case classes. To do this you will need to define the schema, define a User, insert data, and query the data.

To make this easier, we’re just going to map six of the columns. Here are the case classes to use:

case class EmailContact(name: String, email: String)
case class Address(street: String, city: String, country: String)
case class User(contact: EmailContact, address: Address, id: Long = 0L)

You’ll find a definition of UserTable that you can copy and paste in the example code in the file chapter-05/src/main/scala/nested_case_class.scala.

In our huge legacy table we will use custom functions with <>

final class LegacyUserTable(tag: Tag) extends Table[User](tag, "legacy") {
  def id           = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name         = column[String]("name")
  def age          = column[Int]("age")
  def gender       = column[Char]("gender")
  def height       = column[Float]("height")
  def weight       = column[Float]("weight_kg")
  def shoeSize     = column[Int]("shoe_size")
  def email        = column[String]("email_address")
  def phone        = column[String]("phone_number")
  def accepted     = column[Boolean]("terms")
  def sendNews     = column[Boolean]("newsletter")
  def street       = column[String]("street")
  def city         = column[String]("city")
  def country      = column[String]("country")
  def faveColor    = column[String]("fave_color")
  def faveFood     = column[String]("fave_food")
  def faveDrink    = column[String]("fave_drink")
  def faveTvShow   = column[String]("fave_show")
  def faveMovie    = column[String]("fave_movie")
  def faveSong     = column[String]("fave_song")
  def lastPurchase = column[String]("sku")
  def lastRating   = column[Int]("service_rating")
  def tellFriends  = column[Boolean]("recommend")
  def petName      = column[String]("pet")
  def partnerName  = column[String]("partner")

  // The tuple representation we will use:
  type Row = (String, String, String, String, String, Long)

  // One function from Row to User
  def pack(row: Row): User = User(
    EmailContact(row._1, row._2),
    Address(row._3, row._4, row._5),
    row._6
  )

  // Another method from User to Row:
  def unpack(user: User): Option[Row] = Some(
    (user.contact.name, user.contact.email, user.address.street,
     user.address.city, user.address.country, user.id)
  )

  def * = (name, email, street, city, country, id) <> (pack, unpack)
}
// defined class LegacyUserTable

lazy val legacyUsers = TableQuery[LegacyUserTable]
// legacyUsers: slick.lifted.TableQuery[LegacyUserTable] = <lazy>

We can insert and query as normal:

exec(legacyUsers.schema.create)

exec(
  legacyUsers += User(
    EmailContact("Dr. Dave Bowman", "dave@example.org"),
    Address("123 Some Street", "Any Town", "USA")
   )
)
// res45: Int = 1

And we can fetch results:

exec(legacyUsers.result)
// res46: Seq[LegacyUserTable#TableElementType] = Vector(User(EmailContact(Dr. Dave Bowman,dave@example.org),Address(123 Some Street,Any Town,USA),1))

You can continue to select just some fields:

exec(legacyUsers.map(_.email).result)
// res47: Seq[String] = Vector(dave@example.org)

However, notice that if you used legacyUsers.schema.create, only the columns defined in the default projection were created in the H2 database:

legacyUsers.schema.createStatements.foreach(println)
// create table "legacy" ("name" VARCHAR NOT NULL,"email_address" VARCHAR NOT NULL,"street" VARCHAR NOT NULL,"city" VARCHAR NOT NULL,"country" VARCHAR NOT NULL,"id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT)

6 Joins and Aggregates

Wrangling data with joins and aggregates can be painful. In this chapter we’ll try to ease that pain by exploring:

6.1 Two Kinds of Join

There are two styles of join in Slick. One, called applicative, is based on an explicit join method. It’s a lot like the SQL JOINON syntax.

The second style of join, monadic, makes use of flatMap as a way to join tables.

These two styles of join are not mutually exclusive. We can mix and match them in our queries. It’s often convenient to create an applicative join and use it in a monadic join.

6.2 Chapter Schema

To demonstrate joins we will need at least two tables. We will store users in one table, and messages in a separate table, and we will join across these tables to find out who sent a message.

We’ll start with User

import slick.jdbc.H2Profile.api._
import scala.concurrent.ExecutionContext.Implicits.global
case class User(name: String, id: Long = 0L)
// defined class User

class UserTable(tag: Tag) extends Table[User](tag, "user") {
  def id    = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name  = column[String]("name")

  def * = (name, id).mapTo[User]
}
// defined class UserTable

lazy val users = TableQuery[UserTable]
// users: slick.lifted.TableQuery[UserTable] = <lazy>

lazy val insertUser = users returning users.map(_.id)
// insertUser: slick.jdbc.H2Profile.ReturningInsertActionComposer[UserTable#TableElementType,Long] = <lazy>

…and add Message:

// Note that messages have senders, which are references to users
final case class Message(
  senderId : Long,
  content  : String,
  id       : Long = 0L)
// defined class Message

class MessageTable(tag: Tag) extends Table[Message](tag, "message") {
  def id       = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def senderId = column[Long]("senderId")
  def content  = column[String]("content")

  def sender = foreignKey("sender_fk", senderId, users)(_.id)
  def * = (senderId, content, id).mapTo[Message]
}
// defined class MessageTable

lazy val messages = TableQuery[MessageTable]
// messages: slick.lifted.TableQuery[MessageTable] = <lazy>

lazy val insertMessages = messages returning messages.map(_.id)
// insertMessages: slick.jdbc.H2Profile.ReturningInsertActionComposer[MessageTable#TableElementType,Long] = <lazy>

We’ll populate the database with the usual movie script:

def freshTestData(daveId: Long, halId: Long) = Seq(
  Message(daveId, "Hello, HAL. Do you read me, HAL?"),
  Message(halId,  "Affirmative, Dave. I read you."),
  Message(daveId, "Open the pod bay doors, HAL."),
  Message(halId,  "I'm sorry, Dave. I'm afraid I can't do that.")
)
// freshTestData: (daveId: Long, halId: Long)Seq[Message]

val setup = for {
  _         <- (users.schema ++ messages.schema).create
  daveId    <- insertUser += User("Dave")
  halId     <- insertUser += User("HAL")
  rowsAdded <- messages ++= freshTestData(daveId, halId)
} yield rowsAdded
// setup: slick.dbio.DBIOAction[Option[Int],slick.dbio.NoStream,slick.dbio.Effect.Schema with slick.dbio.Effect.Write with slick.dbio.Effect.Write with slick.dbio.Effect.Write] = FlatMapAction(slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@202d7c00,$$Lambda$7336/1372741102@635378c6,scala.concurrent.impl.ExecutionContextImpl@7c202862)

exec(setup)
// res1: Option[Int] = Some(4)

Later in this chapter we’ll add more tables for more complex joins.

6.3 Monadic Joins

We have seen an example of monadic joins in the previous chapter:

val q = for {
  msg <- messages
  usr <- msg.sender
} yield (usr.name, msg.content)
// q: slick.lifted.Query[(slick.lifted.Rep[String], slick.lifted.Rep[String]),(String, String),Seq] = Rep(Bind)

Notice how we are using msg.sender which is defined as a foreign key in the MessageTable definition. (See Foreign Keys in Chapter 5 to recap this topic.)

We can express the same query without using a for comprehension:

val q =
  messages flatMap { msg =>
    msg.sender.map { usr =>
      (usr.name, msg.content)
    }
  }
// q: slick.lifted.Query[(slick.lifted.Rep[String], slick.lifted.Rep[String]),(String, String),Seq] = Rep(Bind)

Either way, when we run the query Slick generates something like the following SQL:

select
  u."name", m."content"
from
  "message" m, "user" u
where
  u."id" = m."sender"

That’s the monadic style of query, using foreign key relationships.

Run the Code

You’ll find the example queries for this section in the file joins.sql over at the associated GitHub repository.

From the chapter-06 folder start SBT and at the SBT > prompt run:

runMain JoinsExample

Even if we don’t have a foreign key, we can use the same style and control the join ourselves:

val q = for {
  msg <- messages
  usr <- users if usr.id === msg.senderId
} yield (usr.name, msg.content)
// q: slick.lifted.Query[(slick.lifted.Rep[String], slick.lifted.Rep[String]),(String, String),Seq] = Rep(Bind)

Note how this time we’re using msg.senderId, not the foreign key sender. This produces the same query when we joined using sender.

You’ll see plenty of examples of this style of join. They look straightforward to read, and are natural to write. The cost is that Slick has to translate the monadic expression down to something that SQL is capable of running.

6.4 Applicative Joins

An applicative join is where we explicitly write the join in code. In SQL this is via the JOIN and ON keywords, which are mirrored in Slick with the following methods:

We will work through examples of each of these methods. But as a quick taste of the syntax, here’s how we can join the messages table to the users on the senderId:

val q: Query[(MessageTable, UserTable), (Message, User), Seq] =
  messages join users on (_.senderId === _.id)
// q: slick.jdbc.H2Profile.api.Query[(MessageTable, UserTable),(Message, User),Seq] = Rep(Join Inner)

As you can see, this code produces be a query of (MessageTable, UserTable). If we want to, we can be more explicit about the values used in the on part:

val q: Query[(MessageTable, UserTable), (Message, User), Seq] =
  messages join users on ( (m: MessageTable, u: UserTable) =>
    m.senderId === u.id
   )
// q: slick.jdbc.H2Profile.api.Query[(MessageTable, UserTable),(Message, User),Seq] = Rep(Join Inner)

We can also write the join condition using pattern matching:

val q: Query[(MessageTable, UserTable), (Message, User), Seq] =
  messages join users on { case (m, u) =>  m.senderId === u.id }
// q: slick.jdbc.H2Profile.api.Query[(MessageTable, UserTable),(Message, User),Seq] = Rep(Join Inner)

Joins like this form queries that we convert to actions the usual way:

val action: DBIO[Seq[(Message, User)]] = q.result
// action: slick.jdbc.H2Profile.api.DBIO[Seq[(Message, User)]] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1@263e5ecd

exec(action)
// res2: Seq[(Message, User)] = Vector((Message(1,Hello, HAL. Do you read me, HAL?,1),User(Dave,1)), (Message(2,Affirmative, Dave. I read you.,2),User(HAL,2)), (Message(1,Open the pod bay doors, HAL.,3),User(Dave,1)), (Message(2,I'm sorry, Dave. I'm afraid I can't do that.,4),User(HAL,2)))

The end result of Seq[(Message, User)] is each message paired with the corresponding user.

6.4.1 More Tables, Longer Joins

In the rest of this section we’ll work through a variety of more involved joins. You may find it useful to refer to figure 6.1, which sketches the schema we’re using in this chapter.