Getting started with Play and Slick 1.0.1

When you start building a new Play Scala web application, you probably need to choose a database platform and a data access library to go with it. You also need to work out how to integrate that with your Play application.

The newest option, at the time of writing, is to use Slick to access an SQL database. The Slick 1.0.1 getting started documentation is straightforward. However, the Slick documentation example shows database-specific imports, a JDBC URL in the Scala code and a Slick threadLocalSession.

In a Play application, you probably want to specify the database and its URL in the Play configuration, and get a database connection from play.api.db.DB.getDataSource (part of Play’s jdbc dependency).

The solution for Play-Slick integration is the aptly-named play-slick library by Typesafe’s Fredrik Ekholdt (author of Typesafe’s Fast Track to Play course). This will likely be built-in to a future version of Play, but as of version 2.1.3 you have to set it up separately. Let’s see how.

Summary

To use play-slick to integrate Play and Slick, complete the following tasks.

Create a new Play application
Configure the application to use an in-memory database
Add the play-slick dependency to the Play application
Add a database table mapping
Inspect the generated SQL
Use a different database driver

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

Create a new Play application

Start by creating a new Play/Scala application. On the command line:

$ play new slick-example
       _            _
 _ __ | | __ _ _  _| |
| '_ \| |/ _' | || |_|
|  __/|_|\____|\__ (_)
|_|            |__/

play! 2.1.3 (using Java 1.6.0_51 and Scala 2.10.0), http://www.playframework.org

The new application will be created in /Users/pedro/Downloads/slick-example

What is the application name? [slick-example]
>

Which template do you want to use for this new application?

  1             - Create a simple Scala application
  2             - Create a simple Java application

> 1
OK, application slick-example is created.

Have fun!

Configure the application to use an in-memory database

Next, configure a database for your Play application. Uncomment the default H2 in-memory database configuration in conf/application.conf:

db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:mem:play"
db.default.user=sa
db.default.password=""

Add the play-slick dependency

To use the play-slick library, it as an application dependency. Edit project/Build.scala and specify this as the only dependency:

  val appDependencies = Seq(
    "com.typesafe.play" %% "play-slick" % "0.4.0"
  )

Note: play-slick includes the dependency on Play’s JDBC module and Slick, which is why you don’t need them here.

Add a database table mapping

To see how Slick works, define a database table in your application. This is a Scala object defines the mapping between Scala code and the database. As an example, define a table that will contain a set of cocktail names, each with a numeric primary key.

In the Play application, create a new database model in app/models/database/Cocktails.scala:

package models.database

import play.api.db.slick.Config.driver.simple._

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

This is a simple mapping that defines a database table called COCKTAIL. The type of each row is (Long, String) and the corresponding columns are called id and name.

Note that this table mapping is declared as a class, instead of as an object as in the Slick documentation examples. Fredrik Ekholdt wrote that this is better and warned darkly that `you can get (very obscure) errors in certain situations' if you use objects. You’ve been warned!

Slick also requires a projection called , which defines the default set of columns for queries. Projection is a mathematical name for a mapping from a set to a certain kind of subset. In this context, a Slick projection maps a database table row - a set of columns - to some subset of the columns. By analogy with the SQL syntax select * from table, the projection is the set of all columns.

Inspect the generated SQL

The great thing about Slick, assuming that you’re familiar with SQL, is that the mapping to SQL is very direct. Once you have defined a table, you can use the Slick API directly to generate the SQL data definition language (DDL) statements for actually creating the table in the database.

From the Cocktails table, use (new Cocktails).ddl.createStatements to generate a list of SQL DDL statements. In this case, the list will contain a single create table statement. To see this, add the following to the controllers.Application.index controller action.

import play.api.Logger
import models.database.Cocktails
import play.api.db.slick.Config.driver.simple._

Logger.debug((new Cocktails).ddl.createStatements.mkString)

Note that the log output shows you H2-specific SQL syntax, because play-slick has used the Slick driver for H2:

[debug] application - create table "COCKTAIL" ("ID" BIGINT NOT NULL,"NAME" VARCHAR NOT NULL)

Slick can also generate SQL for queries, without connecting to a database. Query(new Cocktails) defines an SQL query for the * projection on all of the rows in the Cocktails table. Now call selectStatement to generate the resulting SQL:

Logger.debug(Query(new Cocktails).selectStatement)

This outputs the corresponding H2-dialect SQL, which selects the columns in the projection for all of the rows:

[debug] application - select x2."ID", x2."NAME" from "COCKTAIL" x2

Use a different database driver

You may want to use a different database in a different environment, e.g. MySQL for testing. play-slick lets you change database driver without changing your code, using the Play configuration.

To use MySQL, you first need to add the JDBC driver as an application dependency. In Build.scala, edit the dependencies:

  val appDependencies = Seq(
    "com.typesafe.play" %% "play-slick" % "0.4.0",
    "mysql" % "mysql-connector-java" % "5.1.18"
  )

Restart the application with the database driver set to MySQL, by using a system property to override the application configuration property:

play "run -Ddb.default.driver=com.mysql.jdbc.Driver"

The log output now shows MySQL-specific SQL syntax, which uses a different character to quote table names, and specifies the size of VARCHAR columns:

[debug] application - create table `COCKTAIL` (`ID` BIGINT NOT NULL,`NAME` VARCHAR(254) NOT NULL)
[debug] application - select x2.`ID`, x2.`NAME` from `COCKTAIL` x2

Note that you didn’t change the database URL, which is stil configured by db.default.url="jdbc:h2:mem:play" so you can’t connect to an actual database and execute SQL statements. All you have done is use Slick to define table mappings and generate SQL strings.

Next steps

Now that you can generate SQL for a table definition and a query, you can: