Executing database queries with Slick 1.0.1
This tutorial shows you how to execute database queries in a [Slick](http://slick.typesafe.com) application by building on the [previous Slick tutorials](https://blog.lunatech.com/tags/slick).
Once you’ve configured a Play application to use a database and defined a database table mapping, you don’t have to do much more to get a database connection and execute the query. The final ingredients are getting access to a database connection, and using the Slick API to execute a query on the database.
Summary
Perform the following tasks to complete this tutorial, and learn how to execute database queries.
-
Create a Play application with a database table mapping
-
Use Evolutions to create a database table and load test data
-
Execute a simple query
-
Add a finder to your application’s model layer
-
Execute queries directly from controller actions
Source code: https://github.com/lunatech-labs/play-slick-examples.
Creating a Play application with a database table
First, follow the instructions in Getting started with Play and Slick, to create a new application, configure a database and set-up play-slick.
In addition, add a database table mapping. For this example, keep things
simple by defining a trivial table, in
app/models/database/Cocktails.scala
:
package models.database
import play.api.db.slick.Config.driver.simple._
private[models] class Cocktails extends Table[(Long, String)]("COCKTAIL") {
def id = column[Long]("ID")
def name = column[String]("NAME")
def * = id ~ name
}
Structurally, it’s useful to think of the models.database
package as
the part of the application model that interfaces with the database.
Declaring this as private[models]
means that you will only be using
this within the models
package. In practice, however, both the
models.database
and models
packages will depend on each other, so
the separation is not completely clean.
Using Evolutions to create and populate a database
Once you have configured Play, Slick and a database to work together, and added a table mapping, you are ready to run queries. However, the query results won’t be very interesting if you don’t have any data.
To add data to the database, you could work out how to execute insert
statements if you haven’t already, but it’s actually easier to run SQL
scripts that load test data. To do this, follow the instructions in
http:s//blog.lunatech.com/posts/2013-08-29-play-slick-evolutions[Using Play
framework database evolutions with Slick] to run the following
conf/evolutions/default/1.sql
evolutions script:
# --- !Ups
create table "COCKTAIL" ("ID" BIGINT NOT NULL,"NAME" VARCHAR NOT NULL);
insert into COCKTAIL values (1, 'Margarita');
insert into COCKTAIL values (2, 'Caipirinha');
insert into COCKTAIL values (3, 'Piña colada');
# --- !Downs
drop table "COCKTAIL";
Executing a simple query
Now you can add a simple model layer that will act as a facade for data access, by adding finder methods.
In https://blog.lunatech.com/posts/2013-09-25 -play-slick-defining-queries[Defining database queries with Slick], you saw that you can define a simple query to select the name for each cocktail in the table:
val query = Query(new Cocktails).map(_.name)
Defining the query like this doesn’t execute the query - there is no connection to the database yet. This only happens when you use the Slick API to access results:
val names: List[String] = query.list
Note that since the query returns values for a single column, there is no need to think about how to map result rows to Scala objects. In this case, each row is just a string.
The previous example won’t compile yet: the call to list
fails with
the compilation error could not find implicit value for parameter
session: scala.slick.session.Session. This is reasonable, because this
is the point at which a database connection is needed, which is what a
scala.slick.session.Session
will provide. Specifically, list
requires an implicit Session
parameter.
To declare a Slick session, you would normally wrap the query execution
in a call to scala.slick.session.Database.withSession
. However, when
you’re using play-slick to handle the database configuration, you use
play.api.db.slick.DB.withSession
instead. play.api.db.slick.DB
is a
database helper that works the same way as the play.api.db.DB
helper
that provides access to database connections in a Play application.
Provide a Slick session to your query using play.api.db.slick.DB
:
import play.api.Play.current
val names = play.api.db.slick.DB.withSession { implicit session: scala.slick.session.Session =>
Query(Cocktail.table).map(_.name).list
}
You need the import statement to bring the current application into
scope - as an implicit parameter to play.api.db.slick.DB
, to provide
access to its database configuration.
Adding a finder to your application’s model layer
A good way to structure this in your application is to make this a
finder method in a model object. Create app/models/Cocktail.scala
:
package models
import models.database.Cocktails
import play.api.Play.current
import play.api.db.slick.Config.driver.simple._
import play.api.db.slick.DB
import scala.slick.session.Session
object Cocktail {
val table = new Cocktails
def findNames: List[String] = DB.withSession { implicit session: Session =>
Query(Cocktail.table).map(_.name).list
}
}
This approach allows you to separate the database table mapping in
models.database.Cocktails
from the higher-level data access layer that
models.Cocktails
provides.
Executing queries directly from controller actions
It would also be reasonable to consider this an unnecessary additional layer, and that the query API already provides a natural data access layer. In that case, especially for the small application, you could just execute queries in the controller layer directly.
def cocktails = Action {
DB.withSession { implicit session: Session =>
val names = Query(new Cocktails).map(_.name).list
Ok(views.html.cocktails(names))
}
}
There’s lots of clutter there, which is why play-slick provides a
DBAction
that combines the Action
and the call to DB.withSession
:
def cocktails = DBAction { implicit requestSession: DBSessionRequest =>
val names = Query(new Cocktails).map(_.name).list
Ok(views.html.cocktails(names))
}
The implicit DBSessionRequest
parameter is a wrapper for the
scala.slick.session.Session
together with the play.api.mvc.Request
.
Next steps
Now you can execute queries, you can:
-
define join queries
-
define aggregate and group-by queries
-
insert, update and delete data.