Defining database queries with Slick 1.0.1

:title:Defining database queries with Slick 1.0.1 :tags: [slick,playframework]

This tutorial for using [Slick](http://slick.typesafe.com) with a \ [Play Framework](http://www.playframework.com) application shows you how to define simple database queries

Slick is a Scala DSL for defining database queries that generates SQL. This means that part of learning how to use it is learning to combine parts of the syntax and understanding what SQL it will generate. A good way to do this, if you are familiar with SQL, is to use the Scala console to experiment with the query API and inspect the generated SQL.

Summary

You’ll learn how to complete the following tasks.

Selecting rows from a table
Selecting specific attributes for each row
Filtering rows with a where clause
Chaining queries
Paging rows

Source code: https://github.com/lunatech-labs/play-slick-examples.

Selecting rows from a table

Before you can generate SQL, you need a database table mapping that you can use to define queries. First, follow the instructions in Using the Scala console with Play and Slick, and paste the following table mapping into the Scala console.

scala> import scala.slick.driver.PostgresDriver.simple._
import scala.slick.driver.PostgresDriver.simple._

scala> class Cocktails extends Table[(Long, String)]("COCKTAIL") {
	 |   def id = column[Long]("ID")
	 |   def name = column[String]("NAME")
	 |
	 |   def * = id ~ name
	 | }
defined class Cocktails

Note that you should use the import statement to import the driver for the database that you are using, because otherwise the generated SQL will have the wrong syntax and look weird.

The simplest way to define a basic query is to pass an instance of the table definition to Query:

scala> val cocktails = new Cocktails
cocktails: Cocktails = Table COCKTAIL

scala> val query = Query(cocktails)
query: scala.slick.lifted.Query[Cocktails,(Long, String)] =
  scala.slick.lifted.NonWrappingQuery@74f7931f

scala> val sql = query.selectStatement
sql: String = select x2."ID", x2."NAME", x2."RECIPE" from "COCKTAIL" x2

By comparing the resulting SQL to the table definition, you can see that the query selects the ID and NAME columns from the COCKTAIL table. These columns are present in query because Query(cocktails) uses the default * projection.

You can also define the same query using a Scala for-comprehension:

scala> val query = for { c <- cocktails } yield c
query: scala.slick.lifted.Query[models.database.Cocktails,(Long, String)] =
  scala.slick.lifted.WrappingQuery@4dedb2f8

scala> val sql = query.selectStatement
sql: String = select x2."ID", x2."NAME" from "COCKTAIL" x2

The SQL is the same, but this defines a scala.slick.lifted.WrappingQuery instead of a scala.slick.lifted.NonWrappingQuery. At this stage, you can ignore this kind of API detail, unless of course you want to dive into the source code and figure out what it means.

Selecting specific attributes for each row

For some queries, you will only want to select a single column for each row. Do this with the Query syntax, using map:

scala> val sql = Query(cocktails).map(c => c.name).selectStatement
sql: String = select x2."NAME" from "COCKTAIL" x2

With the for-comprehension syntax, change the yield expression to c.name:

scala> val sql = (for { c <- cocktails } yield c.name).selectStatement
sql: String = select x2."NAME" from "COCKTAIL" x2

In both cases, you use Cocktails.name and the resulting SQL only selects the NAME column.

To select multiple attributes for each row, use the same syntax as for a single attribute and use the projection operator to select a c.id ~ c.name projection that combines attributes:

scala> val sql = Query(cocktails).map(c => c.id ~ c.name).selectStatement
sql: String = select x2."ID", x2."NAME" from "COCKTAIL" x2

scala> val sql = (for { c <- cocktails } yield c.id ~ c.name).selectStatement
sql: String = select x2."ID", x2."NAME" from "COCKTAIL" x2

Alternatively, you can map or yield a (c.id, c.name) tuple:

scala> val sql = Query(cocktails).map(c => (c.id, c.name)).selectStatement
sql: String = select x2."ID", x2."NAME" from "COCKTAIL" x2

scala> val sql = (for { c <- cocktails } yield (c.id, c.name)).selectStatement
sql: String = select x2."ID", x2."NAME" from "COCKTAIL" x2

Use this approach to query multiple columns without having to define the projection in the table mapping.

Filtering rows with a `where` clause

Once you have the hang of the basic syntax for queries, shown above, it is straightforward to filter result rows.

scala> val sql = Query(cocktails).filter(_.name === "Caipirinha").selectStatement
sql: String = select x2."ID", x2."NAME" from "COCKTAIL" x2 where x2."NAME" = 'Caipirinha'

scala> val sql = (for { c <- cocktails if c.name === "Caipirinha"} yield c).selectStatement
sql: String = select x2."ID", x2."NAME" from "COCKTAIL" x2 where x2."NAME" = 'Caipirinha'

Note that the expression uses the Slick === operator, instead of Scala’s ==. In these expressions, you can do the same things you can do with SQL by using the various operators that Slick defines in ExtensionMethods classes in scala.slick.lifted:

logical && and || let you combine expressions
isNotNull and isNull check for null values
<, ⇐, =!=, ===, >, >=, is, isNot, endsWith, like and startsWith let you compare with another value
between lets you compare with two values
in and notIn let you compare with the results of a sub-query
inSet lets you compare with the contents of a Scala set, while inSetBind does the same with a set of bind variables.

As before, try them out in the Scala console. Use the query expression operators to define some queries:

val query = Query(cocktails)
val q1 = query.filter(_.name === "Caipirinha")
val q2 = query.filter(_.name is "Caipirinha")
val q3 = query.filter(_.name =!= "Caipiriña")
val q4 = query.filter(_.name isNot "Caipirinha")
val q5 = query.filter(_.name < "C")
val q6 = query.filter(_.name <= "C")
val q7 = query.filter(_.name > "C")
val q8 = query.filter(_.name >= "C")
val q9 = query.filter(_.name startsWith "C")
val qA = query.filter(_.name like "%nh%")
val qB = query.filter(_.name endsWith "a")
val qC = query.filter(_.name between ("C", "D"))
val names = Query(cocktails).map(_.name)
val qD = query.filter(_.name in names)
val qE = query.filter(_.name notIn names)

Now use the Scala console, changing the default output length limit so that the SQL output is not truncated:

scala> :power
** Power User mode enabled - BEEP WHIR GYVE **

scala> vals.isettings.maxPrintString = 10000
vals.isettings.maxPrintString: Int = 10000

scala> val sql = List(q1,q2,q3,q4,q5,q6,q7,q8,q9,qA,qB,qC,qD,qE).map(_.selectStatement).mkString("\n")
sql: String =
select "ID", "NAME" from "COCKTAIL" where "NAME" = 'Caipirinha'
select "ID", "NAME" from "COCKTAIL" where "NAME" = 'Caipirinha'
select "ID", "NAME" from "COCKTAIL" where not ("NAME" = 'Caipiriña')
select "ID", "NAME" from "COCKTAIL" where not ("NAME" = 'Caipirinha')
select "ID", "NAME" from "COCKTAIL" where "NAME" < 'C'
select "ID", "NAME" from "COCKTAIL" where "NAME" <= 'C'
select "ID", "NAME" from "COCKTAIL" where "NAME" > 'C'
select "ID", "NAME" from "COCKTAIL" where "NAME" >= 'C'
select "ID", "NAME" from "COCKTAIL" where "NAME" like 'C%' escape '^'
select "ID", "NAME" from "COCKTAIL" where "NAME" like '%nh%'
select "ID", "NAME" from "COCKTAIL" where "NAME" like '%a' escape '^'
select "ID", "NAME" from "COCKTAIL" where "NAME" between 'C' and 'D'
select "ID", "NAME" from "COCKTAIL" where "NAME" in (select "NAME" from "COCKTAIL")
select "ID", "NAME" from "COCKTAIL" where not ("NAME" in (select "NAME" from "COCKTAIL"))

(Generated table aliases, e.g. x2, removed for readability.)

There are also functions that you can call on the column values, for string comparisons, such as length, ltrim, rtrim, toUpperCase and toLowerCase. Similarly, you can use abs, ceil, floor, sign, toDegrees and toRadians for numeric values. You need a numeric column to try these out, so redefine cocktails to have ratings:

class Cocktails extends Table[(Long, String, Double)]("COCKTAIL") {
  def id = column[Long]("ID")
  def name = column[String]("NAME")
  def rating = column[Double]("RATING")
  def * = id ~ name ~ rating
}

Now define sample queries as before:

val query = Query(new Cocktails)
val q1 = query.filter(_.name.length < 10)
val q2 = query.filter(_.name.ltrim.length < 10)
val q3 = query.filter(c => c.name =!= c.name.rtrim)
val q4 = query.filter(_.name.toLowerCase like "%caipi%")
val q5 = query.filter(_.rating.abs < 3d)
val q6 = query.filter(_.rating.ceil > 3d)
val q7 = query.filter(_.rating.floor > 3d)
val q8 = query.filter(_.rating.toDegrees > 180d)
val q9 = query.filter(_.rating > 180d.toRadians)

Inspect the generated SQL:

scala> val sql = List(q1,q2,q3,q4,q5,q6,q7,q8,q9).map(_.selectStatement).mkString("\n")
sql: String =
select "ID", "NAME", "RATING" from "COCKTAIL" where {fn length("NAME")} < 10
select "ID", "NAME", "RATING" from "COCKTAIL" where {fn length({fn ltrim("NAME")})} < 10
select "ID", "NAME", "RATING" from "COCKTAIL" where not ("NAME" = {fn rtrim("NAME")})
select "ID", "NAME", "RATING" from "COCKTAIL" where {fn lcase("NAME")} like '%caipi%'
select "ID", "NAME", "RATING" from "COCKTAIL" where {fn abs("RATING")} < 3.0
select "ID", "NAME", "RATING" from "COCKTAIL" where {fn ceiling("RATING")} > 3.0
select "ID", "NAME", "RATING" from "COCKTAIL" where {fn floor("RATING")} > 3.0
select "ID", "NAME", "RATING" from "COCKTAIL" where {fn degrees("RATING")} > 180.0
select "ID", "NAME", "RATING" from "COCKTAIL" where "RATING" > 3.141592653589793

Before you ask, we have no idea what a cocktail rating greater than π means either, but it’s probably at least half good.

Chaining queries

Sometimes you want to combine queries, to apply multiple conditions. One way is to combine Boolean expressions using the && operator, but since filter also returns a query, you can use it more than once instead:

scala> val sql = Query(cocktails).filter(_.name =!= "Caipirinha").filter(_.name < "P").selectStatement
sql: String = select x2."ID", x2."NAME" from "COCKTAIL" x2
  where (not (x2."NAME" = 'Caipirinha')) and (x2."NAME" < 'P')

As you can see, the syntax is close to the corresponding Scala syntax. You can also use the same approach to re-use an existing query:

scala> val names = Query(cocktails).map(_.name)
names: scala.slick.lifted.Query[scala.slick.lifted.Column[String],String] = scala.slick.lifted.WrappingQuery@43a2dc5e

scala> val spanishNames = names.filter(_ like "%ñ%").selectStatement
spanishNames: String = select x2."NAME" from "COCKTAIL" x2 where x2."NAME" like '%ñ%'

Note that the resulting query just adds a where clause, instead of using a subquery.

Paging rows

A different scenario for restricting the query result rows is when you want to control the number of rows, for paging, rather than filtering row values. Again, you can use a Scala-like syntax to modify queries. First, use take to limit the number of query results:

scala> val sql = query.take(100).selectStatement
sql: String = select x2.x3, x2.x4 from
  (select x5."ID" as x3, x5."NAME" as x4 from "COCKTAIL" x5 limit 100) x2

Similarly, you can implement paging by combining drop and take. If you have 100 records per page, you can query the third page with:

scala> val sql = query.drop(200).take(100).selectStatement
sql: String = select x2.x3, x2.x4 from
  (select x5."ID" as x3, x5."NAME" as x4 from "COCKTAIL" x5 limit 100 offset 200) x2

Next steps

Now you can define simple queries, you can: