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
andisNull
check for null values -
<
,⇐
,=!=
,===
,>
,>=
,is
,isNot
,endsWith
,like
andstartsWith
let you compare with another value -
between
lets you compare with two values -
in
andnotIn
let you compare with the results of a sub-query -
inSet
lets you compare with the contents of a Scala set, whileinSetBind
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:
-
insert, update and delete data.