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:

map query result tuples to case classes
define join queries
define aggregate and group-by queries
insert, update and delete data.