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 can write queries in Scala, giving you typed-checked database access. The style of queries makes working with a database similar to 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, you need to know a few key concepts, such as:

• queries: which compose using combinators such as map, flatMap, and filter;

• actions: the things you can run against a database, which themselves compose; and

• futures: which are the result of actions, and also support a set of combinators.

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:

• a working knowledge of Scala (we recommend Essential Scala or an equivalent book);

• experience with relational databases (familiarity with concepts such as rows, columns, joins, indexes, SQL);

• an installed JDK 8 or later, along with a programmer’s text editor or IDE; and

• the sbt build tool.

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

How to Contact Us

You can provide feedback on this text via:

• issues and pull requests on the source repository for this text;

• our Gitter channel; or

• email to hello@underscore.io using the subject line of “Essential Slick”.

Getting help using Slick

If you have questions about using Slick, ask a question on the Slick Gitter channel or use the “slick” tag at Stackoverflow.

Acknowledgements

Many thanks to the following people for their contributions to this book: Renato Cavalcanti, Dave Gurnell, Kevin Meredith, Joseph Ottinger, Yann Simon, Trevor Sibanda, Matthias Braun, Konstantine Gadyrka, Sabrina.

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 mdoc to compile the 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:

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:

• Chapter 1 provides an abbreviated overview of the library as a whole, demonstrating the fundamentals of data modelling, connecting to the database, and running queries.
• Chapter 2 covers basic select queries, introducing Slick’s query language and delving into some of the details of type inference and type checking.
• Chapter 3 covers queries for inserting, updating, and deleting data.
• Chapter 4 discusses data modelling, including defining custom column and table types.
• Chapter 5 looks at actions and how you combine multiple actions together.
• Chapter 6 explores advanced select queries, including joins and aggregates.
• Chapter 7 provides a brief overview of Plain SQL queries—a useful tool when you need fine control over the SQL sent to your database.

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 command to start sbt, and compile and run the example to see what happens:

bash$ cd chapter-01

bash$ sbt
# 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.

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 through 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.

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.13.3"

libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick"           % "3.3.3",
  "com.h2database"      % "h2"              % "1.4.200",
  "ch.qos.logback"      % "logback-classic" % "1.2.3"
)

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

• Slick itself;

• the H2 database; and

• a logging library.

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:

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

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:

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]
}

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 care 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: 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: Query[MessageTable, MessageTable#TableElementType, Seq] = Rep(Filter @1096756488)

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.

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@4e090b3d

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.

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: DBIO[Unit] = slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@3197c0d6

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.

Let’s run this action:

import scala.concurrent.Future
val future: Future[Unit] = db.run(action)
// future: Future[Unit] = Future(Success(()))

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)

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.")
)

The insert of this test data is an action:

val insert: DBIO[Option[Int]] = messages ++= freshTestData
// insert: DBIO[Option[Int]] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$MultiInsertAction@2c9160a9

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 insertAction: Future[Option[Int]] = db.run(insert)
// insertAction: Future[Option[Int]] = Future(Success(Some(4)))

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(insertAction, 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: DBIO[Seq[Message]] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$2@64ecf1d

val messagesFuture: Future[Seq[Message]] = db.run(messagesAction)
// messagesFuture: Future[Seq[Message]] = Future(Success(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))))

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

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\""

def exec[T](action: DBIO[T]): T =
  Await.result(db.run(action), 2.seconds)

exec(messages.result)

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.", 2L),
//   Message("HAL", "I'm sorry, Dave. I'm afraid I can't do that.", 4L)
// )

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.", 2L),
//   Message("HAL", "I'm sorry, Dave. I'm afraid I can't do that.", 4L)
// )

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(2L, 4L)

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: 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.", 2L),
//   Message("HAL", "I'm sorry, Dave. I'm afraid I can't do that.", 4L)
// )

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: DBIO[Seq[Message]] = slick.dbio.SynchronousDatabaseAction$FusedAndThenAction@187c0a

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: DBIO[Seq[Message]] = slick.dbio.SynchronousDatabaseAction$FusedAndThenAction@f869daf

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.

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 the sbt command 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
# 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'?", 0L)

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.

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

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

val f = db.run(messages += Message("Dave","What if I say 'Pretty please'?"))
// f: Future[Int] = Future(Success(1))

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.

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

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

exec(messages.filter(_.sender == "Dave").result)
// 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)
//      ^^^^^^^^^^^^^^^
// error: type mismatch;
//  found   : repl.Session.App.MessageTable => Boolean
//  required: repl.Session.App.MessageTable => T
// exec(messages.filter(_.sender == "Dave").result)
//                      ^^^^^^^^^^^^^^^^^^
// error: Type T cannot be a query condition (only Boolean, Rep[Boolean] and Rep[Option[Boolean]] are allowed
// exec(messages.filter(_.sender == "Dave").result)
//      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

exec(messages.filter(_.sender === "Dave"))
// error: type mismatch;
//  found   : slick.lifted.Query[repl.Session.App.MessageTable,repl.Session.App.MessageTable#TableElementType,Seq]
//     (which expands to)  slick.lifted.Query[repl.Session.App.MessageTable,repl.Session.App.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").result)
//      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

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._

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

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]
}

lazy val messages = TableQuery[MessageTable]

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.

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: Query[MessageTable, MessageTable#TableElementType, Seq] = Rep(Filter @1343173084)

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:

• M is called the mixed type. This is the function parameter type we see when calling methods like map and filter.

• U is called the unpacked type. This is the type we collect in our results.

• C is called the collection type. This is the type of collection we accumulate results into.

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: Query[MessageTable, MessageTable#TableElementType, Seq] = Rep(Filter @314342174)

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:

messages.filter(_.sender === 123)
// error: Cannot perform option-mapped operation
//       with type: (String, Int) => R
//   for base type: (String, String) => Boolean
// messages.filter(_.sender === "Dave").result.statements
//                 ^^^^^^^^^^^^
// 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]
// exec(messages.schema.create andThen (messages ++= freshTestData))
//      ^

Query(1)

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

2.4 Transforming Results

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

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

def exec[T](action: DBIO[T]): T =
  Await.result(db.run(action), 4.seconds)

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.")
)

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: Query[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: Query[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: Query[Rep[String], String, Seq] = Rep(Filter @1479612989)

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: Query[(Rep[Long], 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)

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

exec(contentQuery.result)
// res14: Seq[TextOnly] = Vector(
//   TextOnly(1L, "Hello, HAL. Do you read me, HAL?"),
//   TextOnly(2L, "Affirmative, Dave. I read you."),
//   TextOnly(3L, "Open the pod bay doors, HAL."),
//   TextOnly(4L, "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 controlling the SELECT part of your query.

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 a 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: Query[MessageTable, Message, Seq] = Rep(Bind)

val bayMentioned: DBIO[Boolean] =
  containsBay.exists.result
// bayMentioned: DBIO[Boolean] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$3@15fdf21

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:

• R is the type of data we expect to get back from the database (Message, Person, etc);

• S indicates whether the results are streamed (Streaming[T]) or not (NoStream); and

• E is the effect type and will be inferred.

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].

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

2.6 Executing Actions

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

• db.run(...) runs the action and returns all the results in a single collection. These are known as a materialized result.

• db.stream(...) runs the action and returns its results in a Stream, allowing us to process large datasets incrementally without consuming large amounts of memory.

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

val futureMessages = db.run(messages.result)
// futureMessages: Future[Seq[MessageTable#TableElementType]] = Future(Success(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))))

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

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\""
// )

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:

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.

2.7.4 Boolean Methods

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

2.7.5 Date and Time Methods

Slick provides column mappings for: Instant, LocalDate, LocalTime, LocalDateTime, OffsetTime, OffsetDateTime, and ZonedDateTime. That means you can use all of those types as columns in your table definitions.

How your columns are mapped will depend on the database you’re using, as different databases have different capabilities when it comes to time and date. The table below shows the SQL types used for three common databases:

Unlike the String and Boolean types, there are no special methods for the java.time types. However, as all types have the equality methods, you can use ===, >, <=, and so on with date and time types as you’d expect.

2.7.6 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:

messages.filter(_.id === "foo")
// error: Cannot perform option-mapped operation
//       with type: (Long, String) => R
//   for base type: (Long, Long) => Boolean
// messages.filter(_.id === "foo")
//                 ^^^^^^^^^^^^^^
// 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:

messages.filter(_.id === 123)
// error: Cannot perform option-mapped operation
//       with type: (Long, Int) => R
//   for base type: (Long, Long) => Boolean
// messages.filter(_.id === "foo")
//                 ^^^^^^^^^^^^
// 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")
// ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

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:

messages.filter(_.id === Some(123L)).result.statements
// error: type mismatch;
//  found   : Some[Long]
//  required: slick.lifted.Rep[?]
// messages.filter(_.id === Some(123L)).result.statements
//                          ^^^^^^^^^^
// 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: Query[MessageTable, MessageTable#TableElementType, Seq] = Rep(Filter @121409787)

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.

We’ll look at each in turn, starting with an example of sortBy. Say we 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: 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: Query[MessageTable, MessageTable#TableElementType, Seq] = Rep(Take)

This is equivalent to:

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

users.sortBy(_.name.nullsFirst)

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

2.9 Conditional Filtering

So far we’ve seen query operations such as map, filter, and take, and in later chapters we’ll see joins and aggregations. Much of your work with Slick will likely be with just these few operations.

There are two other methods, filterOpt and filterIf, that help with dynamic queries, where you may (or may not) want to filter rows based on some condition.

For example, suppose we want to give our user the option to filter by crew member (message sender). That is, if you don’t specify a crew member, you’ll get everyone’s messages.

Our first attempt at this might be:

def query1(name: Option[String]) =
  messages.filter(msg => msg.sender === name)

That’s a valid query, but if you feed it None, you’ll get no results, rather than all results. We could add more checks to the query, such as also adding || name.isEmpty. But what we want to do is only filter when we have a value. And that’s what filterOpt does:

def query2(name: Option[String]) =
  messages.filterOpt(name)( (row, value) => row.sender === value )

You can read this query as: we’re going to optionally filter on name, and if name has a value, we can use the value to filter the rows in the query.

The upshot of that is, when there’s no crew member provided, there’s no condition on the SQL:

query2(None).result.statements.mkString
// res32: String = "select \"sender\", \"content\", \"id\" from \"message\""

And when there is, the condition applies:

query2(Some("Dave")).result.statements.mkString
// res33: String = "select \"sender\", \"content\", \"id\" from \"message\" where \"sender\" = 'Dave'"

def queryShortHand(name: Option[String]) =
  messages.filterOpt(name)(_.sender === _)

filterIf is a similar capability, but turns a where condition on or off. For example, we can give the user an option to exclude “old” messages:

val hideOldMessages = true
// hideOldMessages: Boolean = true
val queryIf = messages.filterIf(hideOldMessages)(_.id > 100L)
// queryIf: Query[MessageTable, MessageTable#TableElementType, Seq] = Rep(Filter @857727675)
queryIf.result.statements.mkString
// res34: String = "select \"sender\", \"content\", \"id\" from \"message\" where \"id\" > 100"

Here we see a condition of ID > 100 added to the query because hideOldMessages is true. If it were false, the query would not contain the where clause.

The great convenience of filterIf and filterOpt is that you can chain them one after another to build up concise dynamic queries:

val person = Some("Dave")
// person: Some[String] = Some("Dave")

val queryToRun = messages.
  filterOpt(person)(_.sender === _).
  filterIf(hideOldMessages)(_.id > 100L)
// queryToRun: Query[MessageTable, MessageTable#TableElementType, Seq] = Rep(Filter @1079419349)

queryToRun.result.statements.mkString
// res35: String = "select \"sender\", \"content\", \"id\" from \"message\" where (\"sender\" = 'Dave') and (\"id\" > 100)"

2.10 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:

• unpacked type, which is the regular Scala types we work with, such as String; and

• mixed type, which is Slick’s column representation, such as Rep[String].

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.11 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
> ~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.11.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

2.11.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 id1query = for {
  message <- messages if message.id === 1L
} yield message
// id1query: Query[MessageTable, Message, Seq] = Rep(Bind)

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

2.11.3 One Liners

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

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

2.11.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?

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

2.11.5 Is HAL Real?

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

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

exec(queryHalExists.result)
// res39: Boolean = true

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

2.11.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 contents = messages.map(_.content)
// contents: Query[Rep[String], String, Seq] = Rep(Bind)
exec(contents.result)
// res42: 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."
// )

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

altQuery.result.statements.head
// res43: String = "select \"content\" from \"message\""

2.11.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, NoStream, Effect.Read] = slick.jdbc.StreamingInvokerAction$HeadAction@2779ebca

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

2.11.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?

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

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

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

2.11.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")
// res47: Query[MessageTable, MessageTable#TableElementType, Seq] = Rep(Filter @1047805176)

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

2.11.10 Liking

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

Can you make this case insensitive?

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

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

2.11.11 Client-Side or Server-Side?

What does this do and why?

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

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

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

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

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

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 insertAction =
  messages += Message("HAL", "No. Seriously, Dave, I can't let you in.")
// insertAction: slick.sql.FixedSqlAction[Int, NoStream, Effect.Write] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction@4d92d1b2

exec(insertAction)
// 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:

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]
}

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

insertAction.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.", 0L)

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)

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?",
//     1000L
//   )
// )

3.1.3 Retrieving Primary Keys on Insert

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

val insertDave: DBIO[Long] =
  messages returning messages.map(_.id) += Message("Dave", "Point taken.")
// insertDave: DBIO[Long] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction@3aa667b

val pk: Long = exec(insertDave)
// pk: Long = 1001L

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.", 1001L))

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)

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

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$17$lzycompute(JdbcActionComponent.scala:657)
//  at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.x$17(JdbcActionComponent.scala:657)
//  at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.keyColumns$lzycompute(JdbcActionComponent.scala:657)
//  at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.keyColumns(JdbcActionComponent.scala:657)
//  at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.preparedInsert(JdbcActionComponent.scala:660)
//  at slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction.run(JdbcActionComponent.scala:517)
//  at slick.jdbc.JdbcActionComponent$SimpleJdbcProfileAction.run(JdbcActionComponent.scala:28)
//  at slick.jdbc.JdbcActionComponent$SimpleJdbcProfileAction.run(JdbcActionComponent.scala:25)
//  at slick.basic.BasicBackend$DatabaseDef$$anon$3.liftedTree1$1(BasicBackend.scala:276)
//  at slick.basic.BasicBackend$DatabaseDef$$anon$3.run(BasicBackend.scala:276)
//  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.

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@7be193f7

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

exec(insertMessage)
// res10: Message = Message("Dave", "You're such a jerk.", 1003L)

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
// res11: String = "insert into \"message\" (\"sender\")  values (?)"

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

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

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

exec(messages.map(_.sender) += "HAL")
// org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "content"; SQL statement:
// insert into "message" ("sender")  values (?) [23502-200]
//  at org.h2.message.DbException.getJdbcSQLException(DbException.java:459)
//  at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
//  at org.h2.message.DbException.get(DbException.java:205)
//  at org.h2.message.DbException.get(DbException.java:181)
//  at org.h2.table.Column.validateConvertUpdateSequence(Column.java:374)
//  at org.h2.table.Table.validateConvertUpdateSequence(Table.java:845)
//  at org.h2.command.dml.Insert.insertRows(Insert.java:187)
//  at org.h2.command.dml.Insert.update(Insert.java:151)
//  at org.h2.command.CommandContainer.update(CommandContainer.java:198)
//  at org.h2.command.Command.executeUpdate(Command.java:251)
//  at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:191)
//  at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:152)
//  at slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction.$anonfun$run$15(JdbcActionComponent.scala:520)
//  at slick.jdbc.JdbcBackend$SessionDef.withPreparedStatement(JdbcBackend.scala:425)
//  at slick.jdbc.JdbcBackend$SessionDef.withPreparedStatement$(JdbcBackend.scala:420)
//  at slick.jdbc.JdbcBackend$BaseSession.withPreparedStatement(JdbcBackend.scala:489)
//  at slick.jdbc.JdbcActionComponent$InsertActionComposerImpl.preparedInsert(JdbcActionComponent.scala:511)
//  at slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction.run(JdbcActionComponent.scala:517)
//  at slick.jdbc.JdbcActionComponent$SimpleJdbcProfileAction.run(JdbcActionComponent.scala:28)
//  at slick.jdbc.JdbcActionComponent$SimpleJdbcProfileAction.run(JdbcActionComponent.scala:25)
//  at slick.basic.BasicBackend$DatabaseDef$$anon$3.liftedTree1$1(BasicBackend.scala:276)
//  at slick.basic.BasicBackend$DatabaseDef$$anon$3.run(BasicBackend.scala:276)
//  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)

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?", 0L),
//   Message("HAL", "Affirmative, Dave. I read you.", 0L),
//   Message("Dave", "Open the pod bay doors, HAL.", 0L),
//   Message("HAL", "I'm sorry, Dave. I'm afraid I can't do that.", 0L)
// )

exec(messages ++= testMessages)
// res13: 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)
// res14: messagesReturningRow.MultiInsertResult = Vector(
//   Message("Dave", "Hello, HAL. Do you read me, HAL?", 1008L),
//   Message("HAL", "Affirmative, Dave. I read you.", 1009L),
//   Message("Dave", "Open the pod bay doors, HAL.", 1010L),
//   Message("HAL", "I'm sorry, Dave. I'm afraid I can't do that.", 1011L)
// )

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: Query[(ConstColumn[String], ConstColumn[String]), (String, String), Seq] = Rep(Pure $@162816701)

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

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)

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 forceAction =
  messages.
    map(m => m.sender -> m.content).
    forceInsertQuery(selectExpression)
// forceAction: slick.sql.FixedSqlAction[Int, NoStream, Effect.Write] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$InsertQueryAction@32655b91

exec(forceAction)
// res15: Int = 1

exec(forceAction)
// res16: 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: DBIO[Int] = slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4@467fba43

exec(removeHal)
// res17: 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
// res18: 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
// error: value delete is not a member of slick.lifted.Query[slick.lifted.Rep[String],String,Seq]
//   messages.filter(_.sender === "HAL").map(_.sender)
//   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^

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.

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

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: Query[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"))
// res21: Int = 2

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

updateQuery.updateStatement
// res22: 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: Query[MessageTable, MessageTable#TableElementType, Seq] = Rep(Filter @2086717507)

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: Query[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: DBIO[Int] = slick.jdbc.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$10@686fb722

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: Query[(Rep[String], Rep[String]), (String, String), Seq] = Rep(Bind)

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

val updateAction: DBIO[Int] =
  query.update(("HAL 9000", "Sure, Dave. Come right in."))
// updateAction: DBIO[Int] = slick.jdbc.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$10@3ed213de

exec(updateAction)
// res24: Int = 1

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

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
// res26: 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)

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)
// res27: slick.jdbc.H2Profile.ProfileAction[Int, NoStream, Effect.Write] = slick.jdbc.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$10@61b9133d

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 + "!")

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))

// Don't do it this way:
for {
  msg <- exec(messages.result)
} yield exec(modify(msg))
// res28: 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:

• inserts are via a += or ++= call on a table;

• updates are via an update call on a query, but are somewhat limited when you need to update using the existing row value; and

• deletes are via a delete call to a query.

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 an H2 database.

exec(populate)

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

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.

val senderAndContent = messages.map { m => (m.sender, m.content) }
// senderAndContent: Query[(Rep[String], Rep[String]), (String, String), Seq] = Rep(Bind)
val insertSenderContent = senderAndContent += ( ("HAL","Helllllo Dave") )
// insertSenderContent: slick.sql.FixedSqlAction[Int, NoStream, Effect.Write] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction@417e3c77
exec(insertSenderContent)
// res30: Int = 1

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 get to the point"),
  Message("Bob",  "Okay okay, no need to be tetchy."),
  Message("Alice","Humph!"))

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

exec(messageRows ++= 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 get 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”.

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

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, NoStream, Effect.Write] = slick.jdbc.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$10@53d624ea

Which style do you prefer?

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

val rebootLoopUpdate = halMessages.update(("HAL 9000", "Rebooting, please wait..."))
// rebootLoopUpdate: slick.jdbc.H2Profile.ProfileAction[Int, NoStream, Effect.Write] = slick.jdbc.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$10@637b2763

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:

• First write a query to select the two messages. Then see if you can find a way to use it as a subquery.

• You can use in in a query to see if a value is in a set of values returned from a query.

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

selectiveMemory.statements.head
// res33: 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.

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: DBIO[Int] = slick.dbio.SynchronousDatabaseAction$FusedAndThenAction@5ab4c69a

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.

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 resetSeq: DBIO[Unit] =
  DBIO.seq(messages.delete, messages.size.result)

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)
// res2: Option[Int] = Some(4)

import scala.concurrent.ExecutionContext.Implicits.global

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

val backwards: DBIO[Option[String]] =
  text.map(optionalContent => optionalContent.map(_.reverse))
// backwards: DBIO[Option[String]] = FlatMapAction(
//   slick.jdbc.StreamingInvokerAction$HeadOptionAction@5850f06b,
//   slick.dbio.DBIOAction$$Lambda$8775/330815249@1281da14,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 1, active = 0, running = 0, steals = 111, tasks = 0, submissions = 0]
// )

exec(backwards)
// res3: 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:

• an Option map to apply reverse to our Option[String] result;
• a map on a query to select just the content column; and
• map on our action so that the result will be transform when the action is run.

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))
// res4: DBIOAction[Option[Int], NoStream, Effect.All] = FlatMapAction(
//   slick.jdbc.StreamingInvokerAction$HeadOptionAction@5850f06b,
//   slick.dbio.DBIOAction$$Lambda$8775/330815249@241367d9,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 1, active = 0, running = 0, steals = 111, tasks = 0, submissions = 0]
// )

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

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

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 ok: DBIO[Int] = DBIO.successful(100)

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

And for failures, the value is a Throwable:

val err: DBIO[Nothing] =
  DBIO.failed(new 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

def insert(count: Int) =
  messages += Message("NOBODY", s"I removed ${count} messages")

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

import scala.concurrent.ExecutionContext.Implicits.global

val resetMessagesAction: DBIO[Int] =
  delete.flatMap{ count => insert(count) }
// resetMessagesAction: DBIO[Int] = FlatMapAction(
//   slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4@586ff5d7,
//   <function1>,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 1, active = 0, running = 0, steals = 111, tasks = 0, submissions = 0]
// )

exec(resetMessagesAction)
// res5: 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 produce a variation of resetMessagesAction which will not insert a message if no messages were removed in the first step:

val logResetAction: DBIO[Int] =
  delete.flatMap {
    case 0 => DBIO.successful(0)
    case n => insert(n)
  }

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.

// 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
  }
}

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)

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 manyUpdates: DBIO[Seq[DBIO[Int]]] =
  messages.result.
  map(msgs => msgs.map(reverse))

…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 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)))

The difference is:

• we’ve wrapped the Seq[DBIO] with DBIO.sequence to give a single DBIO[Seq[Int]]; and
• we use flatMap to combine the sequence with the original query.

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 }
// res6: Int = 105

1 * 3 * 5 * 7
// res7: 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)
val report2: DBIO[Int] = DBIO.successful(1)

val reports: List[DBIO[Int]] =
  report1 :: report2 :: Nil

We can fold those reports with a function.

But we also need to consider our starting position:

val 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: DBIO[Int] = FlatMapAction(
//   FlatMapAction(
//     SuccessAction(0),
//     slick.dbio.DBIOAction$$$Lambda$9231/331080430@7e39519,
//     scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 1, active = 0, running = 0, steals = 111, tasks = 0, submissions = 0]
//   ),
//   slick.dbio.DBIOAction$$$Lambda$9231/331080430@1baeae8b,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 1, active = 0, running = 0, steals = 111, tasks = 0, submissions = 0]
// )

exec(summary)
// res8: 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: DBIO[(Int, Seq[Message])] = slick.dbio.SynchronousDatabaseAction$$anon$5@243c3f88

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

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

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)

// Pretend this is important work which might fail:
val work = DBIO.failed(new RuntimeException("Boom!"))

val action: DBIO[Int] = work.cleanUp {
  case Some(err) => log(err)
  case None      => DBIO.successful(0)
}

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

exec(action)
// java.lang.RuntimeException: Boom!
//  at repl.Session$App.<init>(4-combining-actions.md:241)
//  at repl.Session$.app(4-combining-actions.md:3)

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

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

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 tryAction = DBIO.failed(new RuntimeException("Boom!"))

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

exec(tryAction.asTry)
// res13: util.Try[Nothing] = Failure(java.lang.RuntimeException: Boom!)

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

exec(messages.size.result.asTry)
// res14: 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.

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 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)

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
}
// res15: 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: DBIOAction[Int, NoStream, Effect.Write with Effect.Write with Effect with Effect.Write with Effect.Transactional] = slick.dbio.SynchronousDatabaseAction$$anon$11@188ab1ba

exec(willRollback.asTry)
// res17: 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: DBIO[Unit] = slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$8@76be9ed6
val create:   DBIO[Unit]        = messages.schema.create
// create: DBIO[Unit] = slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@33bb3ff6
val populate: DBIO[Option[Int]] = messages ++= freshTestData
// populate: DBIO[Option[Int]] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$MultiInsertAction@650bc00b

exec(drop)

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

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

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 prefixFirst(m: Message): DBIO[Int] = ???

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

import scala.concurrent.ExecutionContext.Implicits.global

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

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

// Try out the method:
exec {
  prefixFirst(Message("Me", "Hello?"))
}
// res22: 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] = ???

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, Effect.Read] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$2@2a0bc981

// 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, Effect.Read] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$2@40a507b9

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

Hints:

• The signature of onlyOne is telling us we will take an action that produces a Seq[T] and return an action that produces a T. That tells us we need an action combinator here.

• That fact that the method may fail means we want to use DBIO.successful and DBIO.failed in there somewhere.

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}")
      )
  }
}

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

exec(onlyOne(boom))
// java.lang.RuntimeException: Expected 1 result, not 2
//  at repl.Session$App$$anonfun$onlyOne$1.apply(4-combining-actions.md:403)
//  at repl.Session$App$$anonfun$onlyOne$1.apply(4-combining-actions.md:399)
//  at slick.basic.BasicBackend$DatabaseDef.$anonfun$runInContextInline$1(BasicBackend.scala:172)
//  at scala.concurrent.impl.Promise$Transformation.run(Promise.scala:433)
//  at scala.concurrent.BatchingExecutor$AbstractBatch.runN(BatchingExecutor.scala:134)
//  at scala.concurrent.BatchingExecutor$AsyncBatch.apply(BatchingExecutor.scala:163)
//  at scala.concurrent.BatchingExecutor$AsyncBatch.apply(BatchingExecutor.scala:146)
//  at scala.concurrent.BlockContext$.usingBlockContext(BlockContext.scala:107)
//  at scala.concurrent.BatchingExecutor$AsyncBatch.run(BatchingExecutor.scala:154)
//  at java.util.concurrent.ForkJoinTask$RunnableExecuteAction.exec(ForkJoinTask.java:1402)
//  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))
// res25: MessageTable#TableElementType = Message(
//   "HAL",
//   "I'm sorry, Dave. I'm afraid I can't do that.",
//   10L
// )

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.

import scala.util.Try
def exactlyOne[T](action: DBIO[Seq[T]]): DBIO[Try[T]] = onlyOne(action).asTry

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

exec(exactlyOne(boom))
// res27: 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) = ???

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) = ???

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)

def myFilter[T](action: DBIO[T])(p: T => Boolean)(alternative: => T) =
  action.map {
    case t if p(t) => t
    case _         => alternative
  }

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:

case class Room(name: String, connectsTo: String)

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

lazy val floorplan = TableQuery[FloorPlan]

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"))
}
// res28: 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]] = ???

…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]] = ???

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)
  }

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

val path: DBIO[Seq[String]] = unfold("Podbay", nextRoom)
// path: DBIO[Seq[String]] = FlatMapAction(
//   slick.jdbc.StreamingInvokerAction$HeadOptionAction@5ea2a31f,
//   <function1>,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 1, active = 0, running = 0, steals = 111, tasks = 0, submissions = 0]
// )

exec(path)
// res29: 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:

• understand how to structure an application;

• look at alternatives to modelling rows as case classes;

• store richer data types in columns; and

• expand on our knowledge of modelling tables to introduce optional values and foreign keys.

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._

…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

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:

• isolate our database code into a trait (or a few traits);

• declare the Slick profile as an abstract val and import from that; and

• extend our database trait to make the profile concrete.

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...
}

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

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
}

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

  // Write database code here
}

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

  // Write more database code here
}

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

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

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 = repl.Session$App$DatabaseLayer@2e774b40

This basic pattern is a 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”:

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

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:

   class MyTable1(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):

   class MyTable2(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)
   
   class UserTable3(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:

• a function from A => B, which converts from the existing shape’s unpacked row-level encoding (String, Long) to our preferred representation (User); and

• a function from B => Option[A], which converts the other way.

We can supply these functions by hand if we want:

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

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

and write:

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)
}

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 dave = User("Dave", 0L)
// dave: User = User("Dave", 0L)
dave.name // case class field access
// res1: String = "Dave" // case class field access

val tuple = ("Dave", 0L)
// tuple: (String, Long) = ("Dave", 0L)
tuple._1 // tuple field access
// res2: 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)

val user = User("Dave", 0L)
val dog  = Dog("Lassie", 0L)


// Different types (a warning, but when compiled -Xfatal-warnings....)
user == dog
// error: No warnings can be incurred under -Werror.

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 run into limitations at 22 fields¹.

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 */)

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
}

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 HList².

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: HNil.type = HNil

val shortHList: Int :: HNil = 123 :: HNil
// shortHList: Int :: HNil = (123)

val longerHList: Int :: String :: Boolean :: HNil =
  123 :: "abc" :: true :: HNil
// longerHList: Int :: String :: Boolean :: HNil = (123, "abc", true)

HLists are constructed recursively like Lists, allowing us to model arbitrarily large collections of values:

• an empty HList is represented by the singleton object HNil;

• longer HLists are formed by prepending values using the :: operator, which creates a new list of a new type.

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

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
}

val attributes = TableQuery[AttrTable]
// attributes: 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

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: DBIO[Seq[AttrHList]] = FlatMapAction(
//   slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@36a4d20b,
//   <function1>,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 0, active = 0, running = 0, steals = 74, tasks = 0, submissions = 0]
// )


val myAttrs: AttrHList = exec(program).head
// myAttrs: AttrHList = (
//   1L,
//   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
// )

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 = 1L
val productId: Long = myAttrs.tail.head
// productId: Long = 100L
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 */)

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]
}

val attributes = TableQuery[AttrTable]

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: DBIO[Seq[Attrs]] = FlatMapAction(
//   slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@6ceaf331,
//   <function1>,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 0, active = 0, running = 0, steals = 74, tasks = 0, submissions = 0]
// )

exec(program)
// res6: Seq[Attrs] = Vector(Attrs(1L, 100L, "n1", 1, "n2", 2))

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)

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]
}

lazy val users = TableQuery[UserTable]
lazy val insertUser = users returning users.map(_.id)

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: DBIOAction[Int, NoStream, Effect.Schema with Effect.Write with Effect.Write] = slick.dbio.SynchronousDatabaseAction$FusedAndThenAction@217cb04

exec(program)
// res8: 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 badQuery = exec(users.filter(_.email === none).result)
// badQuery: 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]]:

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, 2L))

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:

• they modify the SQL generated for DDL statements;

• O.AutoInc removes the corresponding column from the SQL generated for INSERT statements, allowing the database to insert an auto-incrementing value.

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)

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)

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:

• on the positive side it’s easier to identify unsaved records;

• on the negative side it’s harder to get the value of a primary key for use in a query.

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)

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]
}

lazy val users = TableQuery[UserTable]
lazy val insertUser = users returning users.map(_.id)

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)

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]
}

lazy val rooms = TableQuery[RoomTable]
lazy val insertRoom = rooms returning rooms.map(_.id)

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)

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]
}

lazy val occupants = TableQuery[OccupantTable]

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: DBIO[Int] = FlatMapAction(
//   slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@58b64347,
//   <function1>,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 0, active = 0, running = 0, steals = 74, tasks = 0, submissions = 0]
// )

exec(program)
// res11: 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)
}

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:

• a name;

• the column, or columns, that make up the foreign key;

• the TableQuery that the foreign key belongs to; and

• a function on the supplied TableQuery[T] taking the supplied column(s) as parameters and returning an instance of T.

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)

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)
}

lazy val messages = TableQuery[MessageTable]

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

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)
}

users.filter(_.name === "HAL").delete

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: Query[(Rep[String], 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

def findOrCreate(name: String): DBIO[Long] =
  findUserId(name).flatMap { userId =>
    userId match {
      case Some(id) => DBIO.successful(id)
      case None     => insertUser += User(None, name)
    }
}

// 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: DBIOAction[Option[Int], NoStream, Effect.All with Effect.All with Effect.Schema with Effect.Write] = FlatMapAction(
//   FlatMapAction(
//     slick.jdbc.StreamingInvokerAction$HeadOptionAction@683d2efb,
//     <function1>,
//     scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 0, active = 0, running = 0, steals = 74, tasks = 0, submissions = 0]
//   ),
//   <function1>,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 0, active = 0, running = 0, steals = 74, tasks = 0, submissions = 0]
// )

exec(setup)
// res14: 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.)

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)

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]
}

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)
    )
}

What we’re providing here is two functions to MappedColumnType.base:

• one from a DateTime to a database-friendly java.sql.Timestamp; and

• one that does the reverse, taking a Timestamp and converting it to a DateTime.

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)

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]
}

lazy val messages = TableQuery[MessageTable]
lazy val insertMessage = messages returning messages.map(_.id)

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: DBIOAction[insertMessage.SingleInsertResult, NoStream, Effect.Schema with Effect.Schema with Effect.Write with Effect.Write] = FlatMapAction(
//   slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@2f06a3e5,
//   <function1>,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 0, active = 0, running = 0, steals = 74, tasks = 0, submissions = 0]
// )

val msgId = exec(program)
// msgId: insertMessage.SingleInsertResult = 1L

Fetching the database row will automatically convert the timestamp field into the DateTime value we expect:

exec(messages.filter(_.id === msgId).result)
// res18: Seq[MessageTable#TableElementType] = Vector(
//   Message(1L, "Open the pod bay doors, HAL.", 2021-05-07T17:40:37.248Z, 1L)
// )

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
case class UserPK(value: Long) extends AnyVal

A value class is a compile-time wrapper around a value. At run time, the wrapper goes away, leaving no allocation or performance overhead⁴ 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[repl.Session$App19$MessagePK -> Long']

implicit val userPKColumnType =
   MappedColumnType.base[UserPK, Long](_.value, UserPK(_))
// userPKColumnType: slick.jdbc.H2Profile.BaseColumnType[UserPK] = MappedJdbcType[repl.Session$App19$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]
case class UserPK(value: Long) extends AnyVal with MappedTo[Long]

When we use MappedTo we don’t need to define a separate ColumnType. MappedTo works with any class that:

• has a method called value that returns the underlying database value; and

• has a single-parameter constructor to create the Scala value from the database value.

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))

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]
}

lazy val users = TableQuery[UserTable]
lazy val insertUser = users returning users.map(_.id)

…and Message:

case class Message(
  senderId : UserPK,
  content  : String,
  id       : MessagePK = MessagePK(0L))

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)
}

lazy val messages      = TableQuery[MessageTable]
lazy val insertMessage = messages returning messages.map(_.id)

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))
// res22: Query[UserTable, UserTable#TableElementType, Seq] = Rep(Filter @1657704246)

…but if we accidentally try to mix our primary keys, we’ll find we cannot:

users.filter(_.id === MessagePK(0L))
// error: Cannot perform option-mapped operation
//       with type: (repl.Session.App8.UserPK, repl.Session.App8.MessagePK) => R
//   for base type: (repl.Session.App8.UserPK, repl.Session.App8.UserPK) => Boolean
//   messages.filter(_.flag === (Important : Flag)).result
//                   ^
// 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]
// lazy val users = TableQuery[UserTable]
//          ^

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:

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))
// exampleQuery: Query[UserTable, UserTable#TableElementType, Seq] = Rep(Filter @1409785989)

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
case object Important extends Flag
case object Offensive extends Flag
case object Spam extends Flag

case class Message(
  senderId : UserPK,
  content  : String,
  flag     : Option[Flag] = None,
  id       : MessagePK = MessagePK(0L))

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[repl.Session$App25$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)
}

lazy val messages = TableQuery[MessageTable]

exec(messages.schema.create)

We can insert a message with a flag easily:

val halId = UserPK(1L)
// halId: UserPK = UserPK(1L)

exec(
  messages += Message(
    halId,
    "Just kidding - come on in! LOL.",
    Some(Important)
  )
)
// res27: 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
)
// res28: Seq[MessageTable#TableElementType] = Vector(
//   Message(
//     UserPK(1L),
//     "Just kidding - come on in! LOL.",
//     Some(Important),
//     MessagePK(1L)
//   )
// )

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
}

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)
}

messages.filter(_.isImportant).result.statements.head
// res29: 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:

• We can separate the specific profile for our database (H2, Postgres, etc) from our tables. We assemble a database layer from a number of traits, leaving the profile as an abstract field that can be implemented at runtime.

• We can represent rows in a variety of ways: tuples, HLists, and arbitrary classes and case classes via the mapTo macro.

• If we need more control over a mapping from columns to other data structures, the <> method is available.

• We can represent individual values in columns using arbitrary Scala data types by providing ColumnTypes to manage the mappings. We’ve seen numerous examples supporting typed primary keys such as UserPK, sealed traits such as Flag, and third party classes such as DateTime.

• Nullable values are typically represented as Options in Scala. We can either define columns to store Options directly, or use the ? method to map non-nullable columns to optional ones.

• We can define simple primary keys using O.PrimaryKey and compound keys using the primaryKey method.

• We can define foreignKeys, which gives us a simple way of linking tables in a join. More on this next chapter.

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]) = ???

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_3") {
  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.

def filterByEmail(email: Option[String]) =
  email.isEmpty match {
    case true  => users
    case false => users.filter(_.email === email)
  }

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"))
)
// res36: 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))

def filterByEmail(email: Option[String]) =
  users.filter(u => u.email.isEmpty || u.email === email)

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.

val action = messages += Message(UserPK(3000L), "Hello HAL!")
// action: slick.sql.FixedSqlAction[Int, NoStream, Effect.Write] = slick.jdbc.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction@6040675
exec(action.asTry)
// res41: util.Try[Int] = Failure(
//   org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Referential integrity constraint violation: "sender_fk2: PUBLIC.msg_table FOREIGN KEY(sender) REFERENCES PUBLIC.user(id) (3000)"; SQL statement:
// insert into "msg_table" ("sender","content")  values (?,?) [23506-200]
// )

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")
}

Modify the user table to include a UserRole. In the database store the role as a single character.

object UserRole extends Enumeration {
  type UserRole = Value
  val Owner   = Value("O")
  val Regular = Value("R")
}

import UserRole._
implicit val userRoleMapper =
  MappedColumnType.base[UserRole, String](_.toString, UserRole.withName(_))
// userRoleMapper: slick.jdbc.H2Profile.BaseColumnType[UserRole] = MappedJdbcType[scala.Enumeration$Value -> String']

case class User(
  name     : String,
  userRole : UserRole = Regular,
  id       : UserPK = UserPK(0L)
)

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]
}

lazy val users = TableQuery[UserTable]

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.

implicit val userRoleIntMapper =
  MappedColumnType.base[UserRole, Int](
    _.id,
    v => UserRole.values.find(_.id == v) getOrElse Regular
  )
// userRoleIntMapper: slick.jdbc.H2Profile.BaseColumnType[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:

• The database value for high priority messages will be: y, Y, +, or high.

• For low priority messages the value will be: n, N, -, lo, or low.

Go ahead and model this with a sum type.

sealed trait Priority
case object HighPriority extends Priority
case object LowPriority  extends Priority

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[repl.Session$App44$Priority -> String']

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.

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)
}

lazy val legacyUsers = TableQuery[LegacyUserTable]

exec(legacyUsers.schema.create)

exec(
  legacyUsers += User(
    EmailContact("Dr. Dave Bowman", "dave@example.org"),
    Address("123 Some Street", "Any Town", "USA")
   )
)
// res46: Int = 1

exec(legacyUsers.result)
// res47: Seq[LegacyUserTable#TableElementType] = Vector(
//   User(
//     EmailContact("Dr. Dave Bowman", "dave@example.org"),
//     Address("123 Some Street", "Any Town", "USA"),
//     1L
//   )
// )

exec(legacyUsers.map(_.email).result)
// res48: Seq[String] = Vector("dave@example.org")

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:

• different styles of join (monadic and applicative);

• different ways to join (inner, outer and zip); and

• aggregate functions and grouping.

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 JOIN … ON 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)

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]
}

lazy val users = TableQuery[UserTable]
lazy val insertUser = users returning users.map(_.id)

…and add Message:

// Note that messages have senders, which are references to users
case class Message(
  senderId : Long,
  content  : String,
  id       : Long = 0L)

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]
}

lazy val messages = TableQuery[MessageTable]
lazy val insertMessages = messages returning messages.map(_.id)

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.")
)

val setup = for {
  _         <- (users.schema ++ messages.schema).create
  daveId    <- insertUser += User("Dave")
  halId     <- insertUser += User("HAL")
  rowsAdded <- messages ++= freshTestData(daveId, halId)
} yield rowsAdded
// setup: DBIOAction[Option[Int], NoStream, Effect.Schema with Effect.Write with Effect.Write with Effect.Write] = FlatMapAction(
//   slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@5ae97b92,
//   <function1>,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 1, active = 0, running = 0, steals = 89, tasks = 0, submissions = 0]
// )

exec(setup)
// res0: 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 monadicFor = for {
  msg <- messages
  usr <- msg.sender
} yield (usr.name, msg.content)
// monadicFor: Query[(Rep[String], 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 monadicDesugar =
  messages flatMap { msg =>
    msg.sender.map { usr =>
      (usr.name, msg.content)
    }
  }
// monadicDesugar: Query[(Rep[String], 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.

runMain JoinsExample

Even if we don’t have a foreign key, we can use the same style and control the join ourselves:

val monadicFilter = for {
  msg <- messages
  usr <- users if usr.id === msg.senderId
} yield (usr.name, msg.content)
// monadicFilter: Query[(Rep[String], 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:

• join — an inner join,

• joinLeft — a left outer join,

• joinRight — a right outer join,

• joinFull — a full outer join.

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 applicative1: Query[(MessageTable, UserTable), (Message, User), Seq] =
  messages join users on (_.senderId === _.id)
// applicative1: Query[(MessageTable, UserTable), (Message, User), Seq] = Rep(Join Inner)

As you can see, this code produces a query of (MessageTable, UserTable). If we want to, we can be more explicit about the values used in the on part:

val applicative2: Query[(MessageTable, UserTable), (Message, User), Seq] =
  messages join users on ( (m: MessageTable, u: UserTable) =>
    m.senderId === u.id
   )
// applicative2: Query[(MessageTable, UserTable), (Message, User), Seq] = Rep(Join Inner)

We can also write the join condition using pattern matching:

val applicative3: Query[(MessageTable, UserTable), (Message, User), Seq] =
  messages join users on { case (m, u) =>  m.senderId === u.id }
// applicative3: 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)]] = applicative3.result
// action: DBIO[Seq[(Message, User)]] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$2@98bf3be

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

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.

The database schema for this chapter. Find this code in the chat-schema.scala file of the example project on GitHub. A message can have a sender, which is a join to the user table. Also, a message can be in a room, which is a join to the room table.

For now we will add one more table. This is a Room that a User can be in, giving us channels for our chat conversations:

case class Room(title: String, id: Long = 0L)

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]
}

lazy val rooms = TableQuery[RoomTable]
lazy val insertRoom = rooms returning rooms.map(_.id)

And we’ll modify a message so it can optionally be attached to a room:

case class Message(
  senderId : Long,
  content  : String,
  roomId   : Option[Long] = None,
  id       : Long = 0L)

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 roomId   = column[Option[Long]]("roomId")

  def sender = foreignKey("sender_fk", senderId, users)(_.id)
  def * = (senderId, content, roomId, id).mapTo[Message]
}

lazy val messages = TableQuery[MessageTable]
lazy val insertMessages = messages returning messages.map(_.id)

We’ll reset our database and populate it with some messages happening in the “Air Lock” room:

exec(messages.schema.drop)

val daveId = 1L
// daveId: Long = 1L
val halId  = 2L
// halId: Long = 2L

val setup = for {

  // Create the modified and new tables:
  _ <- (messages.schema ++ rooms.schema).create

  // Create one room:
  airLockId <- insertRoom += Room("Air Lock")

  // Half the messages will be in the air lock room...
  _ <- insertMessages += Message(daveId, "Hello, HAL. Do you read me, HAL?", Some(airLockId))
  _ <- insertMessages += Message(halId, "Affirmative, Dave. I read you.",   Some(airLockId))

  // ...and half will not be in room:
  _ <- insertMessages += Message(daveId, "Open the pod bay doors, HAL.")
  _ <- insertMessages += Message(halId, "I'm sorry, Dave. I'm afraid I can't do that.")

  // See what we end up with:
  msgs <- messages.result
} yield (msgs)
// setup: DBIOAction[Seq[MessageTable#TableElementType], NoStream, Effect.Schema with Effect.Write with Effect.Write with Effect.Write with Effect.Write with Effect.Write with Effect.Read] = FlatMapAction(
//   slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$5@32fef27e,
//   <function1>,
//   scala.concurrent.impl.ExecutionContextImpl$$anon$3@25bc3124[Running, parallelism = 2, size = 1, active = 0, running = 0, steals = 89, tasks = 0, submissions = 0]
// )

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

Now let’s get to work and join across all these tables.

6.4.2 Inner Join

An inner join selects data from multiple tables, where the rows in each table match up in some way. Typically, the matching up is done by comparing primary keys. If there are rows that don’t match up, they won’t appear in the join results.

Let’s look up messages that have a sender in the user table, and a room in the rooms table:

val usersAndRooms =
  messages.
  join(users).on(_.senderId === _.id).
  join(rooms).on{ case ((msg,user), room) => msg.roomId === room.id }
// usersAndRooms: Query[((MessageTable, UserTable), RoomTable), ((MessageTable#TableElementType, UserTable#TableElementType), RoomTable#TableElementType), Seq] = Rep(Join Inner)

We’re joining messages to users, and messages to rooms. We use a binary function on the first call to on and a pattern matching function on our second call, to illustrate two styles.

Because each join results in a query of a tuple, successive joins result in nested tuples. Pattern matching is our preferred syntax for unpacking these tuples because it explicitly clarifies the structure of the query. However, you may see this more concisely expressed as a binary function for both joins:

val usersAndRoomsBinaryFunction =
  messages.
  join(users).on(_.senderId  === _.id).
  join(rooms).on(_._1.roomId === _.id)
// usersAndRoomsBinaryFunction: Query[((MessageTable, UserTable), RoomTable), ((MessageTable#TableElementType, UserTable#TableElementType), RoomTable#TableElementType), Seq] = Rep(Join Inner)

The result is the same either way.

6.4.2.1 Mapping Joins

We can turn this query into an action as it stands:

val usersAndRoomQuery: DBIO[Seq[((Message, User), Room)]] =
  usersAndRooms.result
// usersAndRoomQuery: DBIO[Seq[((Message, User), Room)]] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$2@bf09ed6

…but our results will contain nested tuples. That’s OK, if that’s what you want. But typically we want to map over the query to flatten the results and select the columns we want.

Rather than returning the table classes, we can pick out just the information we want. Perhaps the message, the name of the sender, and the title of the room:

val usersAndRoomTitles =
  messages.
  join(users).on(_.senderId  === _.id).
  join(rooms).on { case ((msg,user), room) => msg.roomId === room.id }.
  map { case ((msg, user), room) => (msg.content, user.name, room.title) }
// usersAndRoomTitles: Query[(Rep[String], Rep[String], Rep[String]), (String, String, String), Seq] = Rep(Bind)

val action: DBIO[Seq[(String, String, String)]] = usersAndRoomTitles.result
// action: DBIO[Seq[(String, String, String)]] = slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$2@4f1e8c4

exec(action).foreach(println)
// (Hello, HAL. Do you read me, HAL?,Dave,Air Lock)
// (Affirmative, Dave. I read you.,HAL,Air Lock)

6.4.2.2 Filter with Joins

As joins are queries, we can transform them using the combinators we learned in previous chapters. We’ve already seen an example of the map combinator. Another example would be the filter method.

As an example, we can use our usersAndRooms query and modify it to focus on a particular room. Perhaps we want to use our join for the Air Lock room:

// The query we've already seen...
val usersAndRooms =
  messages.
  join(users).on(_.senderId === _.id).
  join(rooms).on { case ((msg,user), room) => msg.roomId === room.id }