Using the Scala console with Play and Slick 1.0.1
This tutorial shows you how to use a [Play Framework](http://www.playframework.com) application’s Scala console to experiment with [Slick](http://slick.typesafe.com).
Getting started with Play and Slick shows how to configure a new Play application to use Slick, and use log statements to inspect the SQL generated by Slick. Logging isn’t always the best approach.
When you’re learning Slick, it often turns out to be more useful to inspect SQL in the Scala console, to translate your Scala code to SQL so you can see what it does. This way, you can get more immediate feedback when you experiment with the API for table definitions and queries.
Summary
You’ll need to know how to complete the following tasks.
-
Importing a database driver
-
Pasting a table definition into the console
-
Using table definitions defined in the application model
-
Starting the Play application in the console
-
Inspecting generated SQL for application models
Source code: https://github.com/lunatech-labs/play-slick-examples.
Importing a database driver
First you need to be able to start a console in a project with the required dependencies. Getting started with Play and Slick shows how to configure a new Play application to use Slick. If you haven’t already done so, create a Play application with the play-slick dependency.
On the command line, start the console:
play console
To start experimenting with Slick, you can paste a simple table definition into the Scala console. However, this isn’t going to work until you’ve specified which database-specific SQL dialect you want. The solution is a plain import:
scala> import scala.slick.driver.MySQLDriver.simple._
Note: if you get the error object slick is not a member of package scala then you probably need to make Slick available by adding the play-slick dependency, as described in the getting started instructions.
This example uses the MySQL driver; for a different database, use one of the other drivers. The current list is:
-
scala.slick.driver.AccessDriver
- Microsoft Access -
scala.slick.driver.DerbyDriver
- Derby/JavaDB -
scala.slick.driver.H2Driver
- H2 -
scala.slick.driver.HsqldbDriver
- HyperSQL (version 2.0 onwards) -
scala.slick.driver.MySQLDriver
- MySQL -
scala.slick.driver.PostgresDriver
- PostgreSQL -
scala.slick.driver.SQLiteDriver
- SQLite -
scala.slick.driver.SQLServerDriver
- Microsoft SQL Server
Note: the Slick web site explains that DB2 and Oracle drivers are part of the commercially-available Slick Extensions library.
Pasting a table definition into the console
Once you have imported a Slick database driver, use a table definition to generate SQL. Paste the Scala code into the console:
scala> class Cocktails extends Table[(Long, String)]("COCKTAIL") {
| def id = column[Long]("ID")
| def name = column[String]("NAME")
| def * = id ~ name
| }
defined module Cocktails
Note: the Scala console handles pasting a Scala console transcript: you
can paste the above lines including the leading scala>
, the line
continuation characters and the defined module Cocktails
output.
Next, use the console to inspect the MySQL dialect SQL DDL for the table definition, and SQL for a query:
scala> (new Cocktails).ddl.createStatements.mkString
res0: String = create table `COCKTAIL` (`ID` BIGINT NOT NULL,`NAME` VARCHAR(254) NOT NULL)
scala> Query(new Cocktails).selectStatement
res1: String = select x2.`ID`, x2.`NAME` from `COCKTAIL` x2
Using table definitions defined in the application model
In practice it is more convenient to import table definitions from your
Play application, instead of pasting them (and their dependencies) into
the console. Add app/models/database/Cocktails.scala
to your
application, with the play-slick import to use the driver specified by
the application configuration, and the table definition:
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
}
If you import this in the console and try to inspect SQL it will fail:
scala> import models.database.Cocktails
import models.database.Cocktails
scala> (new Cocktails).ddl.createStatements.mkString
java.lang.RuntimeException: There is no started application
The error message tells you that you need to start the application. Note that you may need to close and re-open the Scala console before continuing.
Starting the Play application in the console
To use table definitions from your application, you need a running Play application for play-slick to be able to access the application’s configuration. Start an application in the console like this:
import play.api.{ DefaultApplication, Mode, Play }
val applicationPath = new java.io.File(".")
val classLoader = this.getClass.getClassLoader
val sources = None
val applicationMode = Mode.Dev
Play.start(new DefaultApplication(applicationPath, classLoader, sources, applicationMode))
This starts an application in development mode with the current directory’s configuration, using the current class loader and with no sources.
Now that an application is running, play-slick can import
play.api.Play.current
to access its configuration to look-up the
configured database driver. Now you are finally ready to inspect SQL in
the most convenient way.
Inspecting generated SQL for application models
After you have opened a Play application’s Scala console, and started the application, inspect SQL for the imported table definitions.
scala> import models.database.Cocktails
import models.database.Cocktails
scala> (new Cocktails).ddl.createStatements.mkString
res1: String = create table "COCKTAIL" ("ID" BIGINT NOT NULL,"NAME" VARCHAR NOT NULL)
This time, the output shows H2 SQL syntax, as specified in the application configuration (see getting started for the configuration), with double-quotes instead of MySQL’s back ticks.
Here’s the SQL for a trivial query:
scala> Query(new Cocktails).selectStatement
res2: String = select x2."ID", x2."NAME" from "COCKTAIL" x2
Note: table names are quoted, which makes the names case-sensitive in
Slick’s generated queries. This matters if you create tables yourself
using a different case. For example, if your table definition specifies
lower-case names, Slick queries will use lower-case names. However, if
you create tables manually without quoting their names, e.g. with
create table cocktail (…)
, you may discover that your database assumes
that you want an upper-case name: this is what Oracle and H2 (mimicking
Oracle) do.
Next steps
Now that you can generate SQL for a table definition and a query, you can: