Community contributed extensions

Migrate module

The migrate module helps you manage versions of your database.

If you’re familiar with Ruby On Rails data migration, this module is similar to that. Essentially, the purpose of this module is to provide a useful way to track and manage the version of one or more databases during the development process.

Enable the migrate module for the application

In the /conf/application.conf file, you need a line to add the module to your project:

# The migrate module
module.migrate=${play.path}/modules/migrate

Migrate vs. jpa.ddl=update

In addition to enabling the migrate module, you might want to consider check your jpa.ddl configuration setting and make sure it is set to either jpa.ddl=validate or jpa.ddl=none; otherwise, JPA will modify your database schema based on your models.

Generally, if you’re just at the start of a project, it’s probably best to just use the jpa.ddl=update setting and not bother with the migrate module. However, migrate really offers a lot of advantages over **jpa.ddl* once your project matures a little (e.g., populating your database with metadata, transforming existing operational data into new structures, etc.)

Configuration

Configure database credentials

You need to specify connection and credential parameters for the migrate module.

Make sure that the database user you specify (username and password) has the rights needed to create a new database as well as modify tables.

The connection parameters (port and host) are those used when connecting to your database.

# The parameters for running migration steps.
migrate.module.username=root
migrate.module.password=root_password
migrate.module.port=3306
migrate.module.host=localhost

List your databases

The module can migrate multiple databases, which are accessible through the same connection and credentials. List your databases by name, separated by commas.

# The comma delimited list of databases that migrate should update.
migrate.module.dbs=db1,db2

Choose or write your command-line format string

In order to run database modifications, the module uses command line tools to execute migration files on its databases.

The command line is different for each particular database, so the module uses a format string as a template for the command. The example configuration lines show the template for MySQL and PostgreSQL databases.

# db.module.file.format is used to run commands directly from a file via the command-line 
# on a database.  This format is populated with the appropriate fields in order to perform
# creation and migration scripts on the database.
#
# The MySQL version: 
# db.module.file.format=mysql -u%(username)s --password=%(password)s -h %(host)s -P%(port)s --skip-column-names %(dbname)s < %(filename)s
# The PostgreSQL version:
# db.module.file.format=psql -U %(username)s -h %(host)s -P%(port)s -t -d %(dbname)s -f %(filename)s

Make sure that the command (i.e., ‘mysql’ or ‘psql’) is available on your system’s path or the module will encounter errors.

Writing patch files

Migrate processes two types of files: a single create script and multiple patch scripts. The create script is named create.sql and is only run to generate your database(s) initially. The patch scripts are named according to this pattern: [version number].[‘up’ or ‘down’].[description].sql

These scripts are stored in your play application’s folder like this:

playapp/ 
 |- + db/
    |- + migrate/
	   |-   create.sql
	   |- + db1/
	      |- 1.up.create_user.sql  
	      |- 1.down.remove_user.sql  
	      |- 2.up.create_address.sql  
	      |- 3.up.add_password_to_user.sql  
	   |- + db2/
	      |- 1.up.create_foobar.sql

Run play migrate:init from the command line to build a skeletal directory structure and some example scripts.

The create.sql file contains the instructions to create all of your databases, as well as a single table called patchlevel, which the migrate module uses to keep track of each databases current version. This special table needs two fields called version and status, which hold the database version number and it’s last reported migration status, respectively. See the create.sql file generated by the migrate:init command for example code to create this table.

The patch scripts identify a version number for their contents as well as the direction of the patch. The up patches are used to bring the database version from its previous version up to the version of the patch (e.g., if the database’s current version is 12, patch 13.up.xxx.sql will bring the database to version 13). The down patches remove the changes of the corresponding up patch, bringing the database to the previous version.

NOTE: code to run ‘down’ patches is not implemented in this version of the migrate module!

h2. Running the patch files

To run your database creation script, use the play migrate:create command. This just runs the create.sql script.

To bring your database(s) to their most recent patch version(s), use the play migrate:up command.

Starting fresh

Sometimes during early development, it’s useful to start with a clean database (this is also often nice during tests). You can drop all of your databases, create new ones and migrate to the most recent database version in one command: play migrate:drop-rebuild command.

Other commands

You can see your database(s) current version number(s) and status(es) with the play migrate:version command.

You can create a skeleton directory structure (with example files) with the play migrate:init command.