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: