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!

Database Aliases – Multiple Databases with the Same Schema

Sometimes in development or maintenance of a web application, it is useful to create multiple copies of the same database; for example, an active development copy, a staging copy and a production copy. Often these are hosted on separate machines, but in certain cases (like smaller projects) they are not. Because the migration module uses the database name as the sub-folder name containing its patch files, it is a little difficult to simply change the name of the database the module creates and migrates.

To overcome this issue, this version of the module (1.3 and higher) supports “aliases”, which allow for the easy modification of the database name parameter, as well as simpler synchronized migration of multiple databases on the same machine. To introduce this feature, let’s start with our example database configuration:

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

Now, suppose that we wish to split db1 into three synchronized databases: db1_dev, db1_staging and db1_prod. In older migrate module versions, this would require us to maintain three copies of the same patch scripts--definitely not desirable. With database aliases, we can now avoid unnecessary redundancy:

# The comma delimited list of databases that migrate should update.
migrate.module.dbs=db1_dev[db1],db1_staging[db1],db1_prod[db1],db2

As you well may have guessed, each database is now an alias for the set of patch files located in ‘db1’. Thus, in this example ‘db1’ is more of a name we assign to the set of patch files, rather than the name of a database. Note that if the alias syntax is NOT used, then the migrate module works just as it did before this new feature.

WARNING!! With this format, it is extremely important that your patch files DO NOT contain any references to database / schema names. If there are any such references, only the database explicitly referred to will be modified.

Generic Creation Script Interpolation

If you’ve used the migration module before with multiple databases, you may have observed something annoying about the ‘create.sql’ script: duplicated code. For each database, you essentially run the same commands: create the database by name, and then add the patchlevel table to it.

To avoid this unnecessary duplication version 1.3 and higher of the migrate module supports a generic creation script, which is run and interpolated for each database defined in the module’s ‘migrate.module.dbs’ configuration.

If the ‘generic_create.sql’ file is present in the db/migrate folder, it’s contents will be executed for each database registered with the module. Within this file the string ‘${db}’ is replaced with the name of the database being operated upon.

If the ‘generic_create.sql’ file is NOT present, the normal ‘create.sql’ file will be run when a ‘play migrate:create’ is run and this feature is not activated.

Running the patch files

To run your database creation script, use the play migrate:create command. This just runs the create.sql or interpolated _generic_create.sql_ script (depending on configuration).

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.

You can run any of the commands on just one database by adding ‘--db=[database name]’ as an additional argument to the command.