Using Play framework database evolutions with Slick 1.0.1

This tutorial for using [Slick](http://slick.typesafe.com) with a \ [Play Framework](http://www.playframework.com) application shows you how to create your database and apply data model.

A Play framework web application that uses a relational database will generally assume that the database already exists at runtime. The database, with its tables and other objects, is an application dependency that we expect to be set-up as part of application installation. You automate this by providing SQL scripts that Play Evolutions uses to create the database.

Using the Scala console with Play and Slick shows you how to use Slick to generate the SQL DDL statements that create tables in your database, which you can use to write your SQL database creation scripts. Most of the rest is more about using Play evolutions than about Slick, but using Slick in the right way reduces the amount of work you have to do yourself.

Summary

You’ll need to know how to complete the following tasks.

Creating an initial Evolutions script
Adding test data
Using different databases for development and production
Applying data model changes
Automatically generating create/drop scripts

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

Creating an initial Evolutions script

In Getting started with Play and Slick we started with a simple database table mapping:

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
}

To use this at runtime, you need a COCKTAIL table with ID and NAME columns. Normally, when using evolutions, you write an SQL data definition language (DDL) script yourself, to create the initial database contents.

With default settings, the first evolutions script is the file conf/evolutions/default/1.sql. When evolutions are enabled, Play will run this script when the application starts, if it hasn’t run it before. Instead of writing it by hand, use play-slick to generate it:

follow the getting started instructions to set-up your application
add the table mapping in app/models/database/Cocktails.scala
tell Slick where the table mappings are with the following addition to the Play configuration in conf/application.conf:

	slick.default="models.database.*"

Now run the application (in DEV mode) - Slick creates the initial script in conf/evolutions/default/1.sql:

# --- Created by Slick DDL
# To stop Slick DDL generation, remove this comment and start using Evolutions

# --- !Ups

create table "COCKTAIL" ("ID" BIGINT NOT NULL,"NAME" VARCHAR NOT NULL);

# --- !Downs

drop table "COCKTAIL";

After Slick creates this Script, Play will see that there is a new Evolutions script and offer to run it:

Play offers to run the evolutions script

Now you have a database for your application to use at runtime.

Adding test data

The initial SQL script that Slick generates will create a database with empty tables. In practice, your application will be more interesting if it has some data, but the first release of an application probably doesn’t have functionality to edit data in place yet. Especially if you deploy to the test server after every feature so that your customer can see continuous delivery. It can be useful to add test data at this stage, to avoid blank pages.

To add initial data to your application, add it to your evolutions script. Start with three classic cocktails:

insert into COCKTAIL values (1, 'Margarita');
insert into COCKTAIL values (2, 'Caipirinha');
insert into COCKTAIL values (3, 'Piña colada');

In DEV mode, Play will detect the change on the next request and offer to run the new script.

Updated evolutions script

As you can see, Play will first run a Downs section that drops the table. This is the Downs section from the previous version of the script, which Play stored in the database. This safely reverts the database to its state before Play ran the first version of 1.sql, before applying the Ups from the second version of the same script. As you work on adding test data to 1.sql you will repeat this process for each change: modify the script, reload the page in the browser, and apply evolutions to reapply the script.

This is is what you want during development, but once you have deployed a production release, it is important not to change 1.sql again. If you do, Play will have to run the Downs from all of the scripts applied so far, which is generally destructive, before applying all of the Ups in the correct order. Instead, only make changes in evolution scripts that have not been run on production yet. You can check this by querying the play_evolutions table.

SQL insert statements are fine for small amounts of tests data. If you’re going to import a truly huge amount of data, then you might want something else that will be less verbose and will perform better. For example, PostgreSQL’s COPY command lets you insert tab-separated data that you provide in-line or in a separate file.

Using different databases for development and production

You may want to use different databases for development and test/production, so you can use an in-memory H2 database for development and MySQL production, for example.

This works, because play-slick uses the Slick driver for the database specified by the current Play configuration. This means that you can use a different Play configuration for the production database and queries will continue to work at runtime, because Slick generates SQL in the correct database-specific dialect.

However, you still have hand-written SQL in your evolutions SQL scripts, which will then have to work on both development and production databases. This is possible, if you stick to standard SQL that both databases support, but this is likely to be too limiting for create table DDL statements, where you generally use database-specific column types.

If you do try writing cross-database SQL scripts, you probably won’t be able to quote table names, because the quoting characters vary:

create table "COCKTAILS" -- H2 uses double quotes
create table `COCKTAILS` -- MySQL uses backticks

In SQL, quoted table names are case-sensitive while unquoted names are not, which is reasonable. What you might not expect is that if you’re using H2 create table cocktails creates a table called COCKTAILS, not cocktails, which makes your queries fail at runtime if you use lower-case names in your Slick table definition:

[JdbcSQLException: Table "cocktail" not found; SQL statement:
select x2."id", x2."name" from "cocktail" x2 [42102-168]]

An alternative might be to have separate evolutions SQL scripts, but there isn’t an obvious way to do this and it’s duplication that you probably don’t want anyway. Ideally, in the case of H2, you would just use it’s MySQL compatibility mode in development, and use MySQL dialect in your SQL scripts. However, this won’t work because Slick will still generate H2 dialect SQL for queries at runtime.

For now, our conclusion is that you have to use the same database in production and development. Fortunately - if you’re using MySQL on OSX, say - this isn’t much harder than brew install mysql.

Applying data model changes

The first production release is straightforward: you have a single evolutions file, generated by Slick, possibly with additional test data. What’s harder is when your next production release includes data model changes. You need to know how to write a second evolutions SQL script that modifies the existing database to change it to the new data model, without destroying existing data.

The good news is that most database platforms make it easy to modify existing tables using SQL DDL, so you can refactor your data model by changing table definitions. If you have a DBA who tells you that the data model is written in stone and must not be changed, then you’re more likely to need a new DBA than a new database platform.

Changing the table definitions is actually the easy part: preserving the data is the tricky part. Some tricks are standard: when you add a NOT NULL column, you either specify a default value or you add the column without the constraint, set the value for all existing rows and then add the constraint. Some issues are less obvious.

Suppose you have a database table with non-empty date columns for recording when the record was created and updated. A good default value for both fields is the current date and time. However, MySQL currently only allows one date column to have a default value. This means that you have to add an additional update statement to fix the other column’s value.

alter table COCKTAILS add column CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
alter table COCKTAILS add column UPDATED TIMESTAMP;
update COCKTAILS set UPDATED = CREATED where UPDATED = '0000-00-00 00:00:00';

In general, you need to do something similar for every data model change: whenever you add or change a column, think about what the new values for that column should be. Note that this still applies if your data model change is to remove a column, because the Downs section in the script has to revert the change by adding the column again and somehow repopulating its contents.

Automatically generating create/drop scripts

To write the SQL scripts for applying data model changes, you need to know the correct syntax for things like column types and indexes. It’s convenient to use Slick to generate a complete `create database' script for the current database, so that you can use its differences to the previous version to identify which changes are needed, and what the new syntax is.

It is also convenient to have `create database' and `drop database' scripts if you want to create a complete database without using Play evolutions. Running both scripts is a quick way to reset the whole database to empty tables.

As you saw earlier, Slick only generates the initial SQL: script if you haven’t already already created one. Instead, you can write your own code to generate create-database.sql and drop-database.sql scripts when the application starts in development mode. The following class is a Play GlobalSettings that does just that, based on the code from the play-slick’s play.api.db.slick.SlickDDLPlugin Play plug-in.

import java.io.File
import play.api.db.slick.plugin.TableScanner
import play.api.libs.Files
import play.api.{Mode, Application, GlobalSettings}

object Global extends GlobalSettings {

  private val configKey = "slick"
  private val ScriptDirectory = "conf/evolutions/"
  private val CreateScript = "create-database.sql"
  private val DropScript = "drop-database.sql"
  private val ScriptHeader = "-- SQL DDL script\n-- Generated file - do not edit\n\n"

/**
 * Creates SQL DDL scripts on application start-up.
 */
  override def onStart(application: Application) {

	if (application.mode != Mode.Prod) {
	  application.configuration.getConfig(configKey).foreach { configuration =>
		configuration.keys.foreach { database =>
		  val databaseConfiguration = configuration.getString(database).getOrElse{
			throw configuration.reportError(database, "No config: key " + database, None)
		  }
		  val packageNames = databaseConfiguration.split(",").toSet
		  val classloader = application.classloader
		  val ddls = TableScanner.reflectAllDDLMethods(packageNames, classloader)

		  val scriptDirectory = application.getFile(ScriptDirectory + database)
		  Files.createDirectory(scriptDirectory)

		  writeScript(ddls.map(_.createStatements), scriptDirectory, CreateScript)
		  writeScript(ddls.map(_.dropStatements), scriptDirectory, DropScript)
		}
	  }
	}
  }

  /**
   * Writes the given DDL statements to a file.
   */
  private def writeScript(ddlStatements: Seq[Iterator[String]], directory: File,
	fileName: String): Unit = {

	val createScript = new File(directory, fileName)
	val createSql = ddlStatements.flatten.mkString("\n\n")
	Files.writeFileIfChanged(createScript, ScriptHeader + createSql)
  }
}

This will create or update both scripts in the conf/evolutions/default directory (if changed). Add these files to version control, even though they are generated, so that you get SQL diffs when you make changes.

Next steps

Now you have created an initial database and populated it with test data, you can:

execute database queries
insert, update and delete data.