Menu

Official website

Using the Scala console with Play and Slick 1.0.1

author image Peter Hilton

13 Aug 2013

min read

slick playframework en

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.

  1. Importing a database driver

  2. Pasting a table definition into the console

  3. Using table definitions defined in the application model

  4. Starting the Play application in the console

  5. 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:

expand_less