Sustainable peace with database changes into a Java environment

Sergio MolinaJanuary 28th, 2014Last Updated: January 27th, 2014

0 23 2 minutes read

Sustainable peace for us is remove uncertainty. In this case over database changes the idea Active Record Migrations of Ruby was welcomed.

And what does migration means for us?. Well, it is a convenient way to alter our database schema overtime in a consistent and easy way that removes a lot of uncertainty about database changes in our software development process.

Goal

Our goal will be maintaining the lifecycle of the database according to the development and evolution of the project with an absolute control over the changes.

For this we have to look for a simple tool with a basic group of characteristics as the following ones:

Works with any database although now our database is MySQL.
Enable concurrent developers to work independently.
Enable different development environments.
Able to integrate with any version control system.
Able to integrate easily migration tasks into Apache Ant.
Allow forward and backward migrations and conflicts easily manageable.

We select MyBatis Migrations tool as the best solution for us and a GitHub repository Ant Script to run MyBatis Migrations’ commands as a start line.

Let’s go to the point: How we work with migrations

With these tools we think that a lifecycle of migration may be like this one

The first time

Create a migrations directory into our project directory.
Download MyBatis Schema migrations file mybatis-migrations-3.1.1-bundle.zip.
Create a lib directory and copy mybatis-3.2.3.jar and mybatis-migrations-3.1.1.jar files.
Download Ant tasks build.properties and build.xml files from mybatis-migrations-anttasks-master.zip and rename it as migrations.properties/xml for clearer goals.

Obviously, this files define ant tasks and basic properties for migrations tool while migrations.properties (comments are included for clearly) defines

# Default environment
mybatis.default.environment=development

mybatis.dir=migrations
mybatis.lib.dir=${mybatis.dir}/lib

mybatis.repository.dir=${mybatis.dir}/db

# This directory contains your migration SQL files. These are the files 
# that contain your DDL to both upgrade and downgrade your database 
# structure. By default, the directory will contain the script to 
# create the changelog table, plus one empty example migration script. 
mybatis.scripts.dir=${mybatis.repository.dir}/scripts

# Place your JDBC driver .jar or .zip files in this directory.
# Upon running a migration, the drivers will be dynamically loaded.
mybatis.drivers.dir=${mybatis.repository.dir}/drivers

# In the environments folder you will find .properties files that 
# represent your database instances. By default a development.properties 
# file is created for you to configure your development time database 
# properties.
# You can also create test.properties and production.properties 
# files. The properties file is self documented.
mybatis.env.dir=${mybatis.repository.dir}/environments

and migrations.xml defines ant tasks as you can see in the original documentation. Of course, you must rename it as xml file descriptor property to load it

<?xml version="1.0" encoding="UTF-8"?>
<project name="MyBatis Migrations" basedir="." 
         default="db:migrate:status">

	<property file="migrations/migrations.properties" />

.....
</project>

But, how to install it … It’s easy, basically we have to execute:

$ ant -f migrations.xml db:migrate:init

It creates directories and the initial files as they were defined in migrations.properties as you can see in this output log

Buildfile: /wpr/myproject/migrations/migrations.xml
 
db:migrate:init:
     [echo] ** Executing "migrate init" on "development" environment **
      ------------------------------------------------------------
      -- MyBatis Migrations - init
      ------------------------------------------------------------
      Initializing: db
      Creating: environments
      Creating: scripts
      Creating: drivers
      Creating: README
      Creating: development.properties
      Creating: bootstrap.sql
      Creating: 20131123174059_create_changelog.sql
      Creating: 20131123174100_first_migration.sql
      Done!
     
      ------------------------------------------------------------
      -- MyBatis Migrations SUCCESS
      -- Total time: 2s
      -- Finished at: Sat Nov 23 18:41:00 CET 2013
      -- Final Memory: 1M/117M
      ------------------------------------------------------------.
 
BUILD SUCCESSFUL
Total time: 3 seconds

while

environments, scripts and drivers are directories (as seen before).
README, that explains directories contents as the name suggests.
bootstral.sql, in which you have to include the database actual schema. You need to start from a known state.
20131123174059_create_changelog.sql contains a default control table for migration tool. It’s a price that you have to pay.
20131123174100_first_migration.sql will be your first SQL migration file. You can delete it or rename it for clearly although you must keep the format as yyyymmddHHMMss_.

Keep migrations/db/environment/development.properties database properties for development environment

## JDBC connection properties. 
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/<databaseName>
username=root
password=root

Add others environment properties files to each migrations/db/environment/<environment>.properties if you need.
As last step, put your actual database schema into bootstrap.sql file.

Day by day

Among all migrate commands we normally use

Create one or many migrations doing db:migrate:new.
Apply migrations to database doing db:migrate:up.

Optional steps included:

Revert migrations if necessary to solve conflicts. Any mistake has an easy solution with db:migrate:down .. but remember that it is by single steps.

Apply pending migrations out of order if it’s safe to do so with db:migrate:pending or db:migrate:version. Actually, if you want to execute those tasks you will have to add the code belong into migrations.xml

<?xml version="1.0" encoding="UTF-8"?>
<project name="MyBatis Migrations" basedir="." default="db:migrate:status">
....

	<!-- $ migrate pending -->
	<target name="db:migrate:pending" description="Runs all pending migrations regardless of their order or position in the status log">
		<migrate command="pending" environment="${environment}" />
	</target>

	<!-- $ migrate version -->
	<target name="db:migrate:version" description="Migrate the schema to any specific version">
		<input addproperty="specific.version" message="Specific version to migrate:" />
		<migrate command="version" environment="${environment}">
			<extraarguments>
				<arg value="${specific.version}" />
			</extraarguments>
		</migrate>
	</target>

</project>

Generate migration scripts to be run “offline” in environments that are beyond your control.
Get the status of the system at any time doing db:migrate:status.

We hope you find useful our solution, all comments are welcomed and apologies for my english.

Reference: Sustainable peace with database changes into a Java environment from our JCG partner Sergio Molina at the TODOdev blog.