Documentation

You are viewing the documentation for the 3.0.0 release. The latest stable release series is 3.0.x.

§Managing database evolutions

When you use a relational database, you need a way to track and organize your database schema evolutions. Typically there are several situations where you need a more sophisticated way to track your database schema changes:

§Enable evolutions

Add evolutions and jdbc into your dependencies list. For example, in build.sbt:

libraryDependencies ++= Seq(evolutions, jdbc)

§Running evolutions using compile-time DI

If you are using compile-time dependency injection, you will need to mix in the EvolutionsComponents trait to your cake to get access to the ApplicationEvolutions, which will run the evolutions when instantiated. EvolutionsComponents requires dbApi to be defined, which you can get by mixing in DBComponents and HikariCPComponents. Since applicationEvolutions is a lazy val supplied by EvolutionsComponents, you need to access that val to make sure the evolutions run. For example you could explicitly access it in your ApplicationLoader, or have an explicit dependency from another component.

Your models will need an instance of Database to make connections to your database, which can be obtained from dbApi.database.

import play.api.db.evolutions.EvolutionsComponents
import play.api.db.DBComponents
import play.api.db.Database
import play.api.db.HikariCPComponents
import play.api.routing.Router
import play.api.ApplicationLoader.Context
import play.api.BuiltInComponentsFromContext
import play.filters.HttpFiltersComponents

class AppComponents(cntx: Context)
    extends BuiltInComponentsFromContext(cntx)
    with DBComponents
    with EvolutionsComponents
    with HikariCPComponents
    with HttpFiltersComponents {
  // this will actually run the database migrations on startup
  applicationEvolutions

}

§Evolutions scripts

Play tracks your database evolutions using several evolutions script. These scripts are written in plain old SQL and, by default, should be located in the conf/evolutions/{database name} directory of your application. If the evolutions apply to your default database, this path is conf/evolutions/default.

The first script is named 1.sql, the second script 2.sql, and so on…

Each script contains two parts:

For example, take a look at this first evolution script that bootstraps a basic application:

-- Users schema

-- !Ups

CREATE TABLE User (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    fullname varchar(255) NOT NULL,
    isAdmin boolean NOT NULL,
    PRIMARY KEY (id)
);

-- !Downs

DROP TABLE User;

The Ups and Downs parts are delimited by using a standard, single-line SQL comment in your script containing either !Ups or !Downs, respectively. Both SQL92 (--) and MySQL (#) comment styles are supported, but we recommend using SQL92 syntax because it is supported by more databases.

Play splits your .sql files into a series of semicolon-delimited statements before executing them one-by-one against the database. So if you need to use a semicolon within a statement, escape it by entering ;; instead of ;. For example, INSERT INTO punctuation(name, character) VALUES ('semicolon', ';;');.

Evolutions are automatically activated if a database is configured in application.conf and evolution scripts are present. You can disable them by setting play.evolutions.enabled=false. For example when tests set up their own database you can disable evolutions for the test environment.

When evolutions are activated, Play will check your database schema state before each request in DEV mode, or before starting the application in PROD mode. In DEV mode, if your database schema is not up to date, an error page will suggest that you synchronize your database schema by running the appropriate SQL script.

If you agree with the SQL script, you can apply it directly by clicking on the ‘Apply evolutions’ button.

§Evolutions configuration

Evolutions can be configured both globally and per datasource. For global configuration, keys should be prefixed with play.evolutions. For per datasource configuration, keys should be prefixed with play.evolutions.db.<datasourcename>, for example play.evolutions.db.default. The following configuration options are supported:

For example, to enable autoApply for all evolutions, you might set play.evolutions.autoApply=true in application.conf or in a system property. To disable autocommit for a datasource named default, you set play.evolutions.db.default.autocommit=false.

§Location of the evolution scripts

As already mentioned, evolution scripts by default are located in the conf/evolutions/{database name} directory of your application. You can however change the name of the folder: For example, if you wish to store the scripts of the default database in conf/db_migration/default you can do so by setting the path config:

# For the default database
play.evolutions.db.default.path = "db_migration"

# Or, if you want to set the location for all databases
play.evolutions.db.path = "db_migration"

Play can always load these scripts, because the content of the conf folder is on the classpath in development mode as well as in production.

You can also store the evolution scripts outside your project folder and reference them with an absolute or a relative path:

# Absolute path:
play.evolutions.db.default.path = "/opt/db_migration"
# Relative path (as seen from your project's root folder)
play.evolutions.db.default.path = "../db_migration"

However, please be aware that when using such configurations, the evolution scripts will not be included in a production package if you decide to bundle one, so it’s up to you to manage the evolution scripts in production.

Lastly, you can store evolution scripts within your project, but outside the conf folder:

play.evolutions.db.default.path = "./db_migration"

In that case, the scripts will not be on the classpath. During development that does not matter, because before looking for evolution scripts on the classpath, Play looks them up on the filesystem (that’s why above absolute and relative path approaches work).
However, not placing the evolution folder in the conf directory and therefore also not on the classpath means it will not be packaged for production. To make sure the folder gets packaged and is available in production you therefore have to set that up in your build.sbt:

Universal / mappings ++= (baseDirectory.value / "db_migration" ** "*").get.map {
  (f: File) => f -> f.relativeTo(baseDirectory.value).get.toString
}

§Variable substitution

You can define placeholders in your evolutions scripts which will be replaced with their substitutions, defined in application.conf:

play.evolutions.db.default.substitutions.mappings = {
  table = "users"
  name = "John"
}

An evolution script like

INSERT INTO $evolutions{{{table}}}(username) VALUES ('$evolutions{{{name}}}');

will now become

INSERT INTO users(username) VALUES ('John');

at the moment when evolutions get applied.

The evolutions meta table will contain the raw sql script, without placeholders replaced.

Variable substitution is case insensitive, therefore $evolutions{{{NAME}}} is the same as $evolutions{{{name}}}.

You can also change the prefix and suffix of the placeholder syntax:

# Change syntax to @{...}
play.evolutions.db.default.substitutions.prefix = "@{"
play.evolutions.db.default.substitutions.suffix = "}"

The evolution module also comes with support for escaping, for cases where variables should not be substituted. This escaping mechanism is enabled by default. To disable it you need to set:

play.evolutions.db.default.substitutions.escapeEnabled = false

If enabled, the syntax !$evolutions{{{...}}} can be used to escape variable substitution. For example:

INSERT INTO notes(comment) VALUES ('!$evolutions{{{comment}}}');

will not be replaced with its substitution, but instead will become

INSERT INTO notes(comment) VALUES ('$evolutions{{{comment}}}');

in the final sql.

This escape mechanism will be applied to all !$evolutions{{{...}}} placeholders, no matter if a mapping for a variable is defined in the substitutions.mappings config or not.

§Synchronizing concurrent changes

Now let’s imagine that we have two developers working on this project. Jamie will work on a feature that requires a new database table. So Jamie will create the following 2.sql evolution script:

-- Add Post

-- !Ups
CREATE TABLE Post (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    content text NOT NULL,
    postedAt date NOT NULL,
    author_id bigint(20) NOT NULL,
    FOREIGN KEY (author_id) REFERENCES User(id),
    PRIMARY KEY (id)
);

-- !Downs
DROP TABLE Post;

Play will apply this evolution script to Jamie’s database.

On the other hand, Robin will work on a feature that requires altering the User table. So Robin will also create the following 2.sql evolution script:

-- Update User

-- !Ups
ALTER TABLE User ADD age INT;

-- !Downs
ALTER TABLE User DROP age;

Robin finishes the feature and commits (let’s say by using Git). Now Jamie has to merge Robin’s work before continuing, so Jamie runs git pull, and the merge has a conflict, like:

Auto-merging db/evolutions/2.sql
CONFLICT (add/add): Merge conflict in db/evolutions/2.sql
Automatic merge failed; fix conflicts and then commit the result.

Each developer has created a 2.sql evolution script. So Jamie needs to merge the contents of this file:

<<<<<<< HEAD
-- Add Post

-- !Ups
CREATE TABLE Post (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    content text NOT NULL,
    postedAt date NOT NULL,
    author_id bigint(20) NOT NULL,
    FOREIGN KEY (author_id) REFERENCES User(id),
    PRIMARY KEY (id)
);

-- !Downs
DROP TABLE Post;
=======
-- Update User

-- !Ups
ALTER TABLE User ADD age INT;

-- !Downs
ALTER TABLE User DROP age;
>>>>>>> devB

The merge is really easy to do:

-- Add Post and update User

-- !Ups
ALTER TABLE User ADD age INT;

CREATE TABLE Post (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    content text NOT NULL,
    postedAt date NOT NULL,
    author_id bigint(20) NOT NULL,
    FOREIGN KEY (author_id) REFERENCES User(id),
    PRIMARY KEY (id)
);

-- !Downs
ALTER TABLE User DROP age;

DROP TABLE Post;

This evolution script represents the new revision 2 of the database, that is different of the previous revision 2 that Jamie has already applied.

So Play will detect it and ask Jamie to synchronize the database by first reverting the old revision 2 already applied, and by applying the new revision 2 script:

§Inconsistent states

Sometimes you will make a mistake in your evolution scripts, and they will fail. In this case, Play will mark your database schema as being in an inconsistent state and will ask you to manually resolve the problem before continuing.

For example, the Ups script of this evolution has an error:

-- Add another column to User

-- !Ups
ALTER TABLE Userxxx ADD company varchar(255);

-- !Downs
ALTER TABLE User DROP company;

So trying to apply this evolution will fail, and Play will mark your database schema as inconsistent:

Now before continuing you have to fix this inconsistency. So you run the fixed SQL command:

ALTER TABLE User ADD company varchar(255);

… and then mark this problem as manually resolved by clicking on the button.

But because your evolution script has errors, you probably want to fix it. So you modify the 3.sql script:

-- Add another column to User

-- !Ups
ALTER TABLE User ADD company varchar(255);

-- !Downs
ALTER TABLE User DROP company;

Play detects this new evolution that replaces the previous 3 one, and will run the appropriate script. Now everything is fixed, and you can continue to work.

In development mode however it is often simpler to simply trash your development database and reapply all evolutions from the beginning.

§Transactional DDL

By default, each statement of each evolution script will be executed immediately. If your database supports Transactional DDL you can set evolutions.autocommit=false in application.conf to change this behaviour, causing all statements to be executed in one transaction only. Now, when an evolution script fails to apply with autocommit disabled, the whole transaction gets rolled back and no changes will be applied at all. So your database stays “clean” and will not become inconsistent. This allows you to easily fix any DDL issues in the evolution scripts without having to modify the database by hand like described above.

§Evolution storage and limitations

Evolutions are stored in your database in a table called play_evolutions. A Text column stores the actual evolution script. Your database probably has a 64kb size limit on a text column. To work around the 64kb limitation you could: manually alter the play_evolutions table structure changing the column type or (preferred) create multiple evolutions scripts less than 64kb in size.

Next: Server Backends