MyBatis Database Migration Tool

Evolving databases has been one of the major challenges for software development. Often regardless of our software development methodology, the database follows a different change management process. The tools of the past have been GUI centric, proprietary for a particular database and/or carried a steep license cost. Yet, at the end of the day they suffered from the same challenges.

The MyBatis Migration Maven plugin is a simple command line tool that helps us to manage the database schema changes in a more systematic way where manual work can be avoided. It can be used for any relational database systems like postgres, mysql, oracle etc.

For migration, you create the sql script files as usual which will contain one more sql statements and save the file with a specific naming convention provided by the MyBatis Miration. File naming convention YYYYMMDDHHMMSS_file_name.sql. The timestamp provided in the filename should be unique otherwise the migration will abort with an error.

• Eg:- 20170506163015_create_member_details.sql

One of the good thing about MyBatis Migration is that the script file will contain sql statements to make the changes as well as to rever those changes. Hence the script file has been divided into two sections like below

• --// First migration. -- Migration SQL that makes the change goes here. 
• --//@UNDO -- SQL to undo the change goes here.

Here are the steps to integrate your new or an existing project with MyBatis Migration.

Add Maven Plugin and Dependency

<plugin>
         <groupId>org.mybatis.maven</groupId>
         <artifactId>migrations-maven-plugin</artifactId>
         <version>1.1.2</version>
         <configuration>
             <repository>src/main/resources/db/</repository>
             <output>dist/migration-name.sql</output>
         </configuration>
             <dependencies>
                 <dependency>
                     <groupId>org.postgresql</groupId>
                     <artifactId>postgresql</artifactId>
                     <version>9.4-1205-jdbc4</version>

                 </dependency>
             </dependencies>
     </plugin>

Add a property file

Under your src/main/resources add a sub-directory named environments. And create a property file and name it say development.properties. You can also create test.properties and production.properties files. The environment can be specified when running a migration by using the –env=<environment> option (without the path or “.properties” part). Below is the template which should be used for the property file.

## Base time zone to ensure times are consistent across machines
time_zone=GMT+0:00

## The character set that scripts are encoded with
# script_char_set=UTF-8

## JDBC connection properties.
driver=
url=
username=
password=

# Name of the table that tracks changes to the database
changelog=db_changelog

# If set to true, each statement is isolated in its own transaction.
# Otherwise the entire script is executed in one transaction.
auto_commit=false

# This controls how statements are delimited. 
# By default statements are delimited by an end of line semicolon. 
# Some databases may (e.g. MS SQL Server) may require a full line delimiter such as GO.
delimiter=;
full_line_delimiter=false

# This ignores the line delimiters and simply sends the entire script at once.
# Use with JDBC drivers that can accept large blocks of delimited text at once.
send_full_script=false

# Custom driver path to avoid copying your drivers
# driver_path=

Maven Goals

mvn migration:status

As the goal is straight forward, it provides the status of the migration whether the script has been executed or not. It provides a simple tabular information that contains id (timestamp provided in the filename), description (filename after the timestamp) and applied_at specifies when the migration has run for each file on the database.

mvn migration:up

A goal which will execute all pending migation scripts one by one based on the timestamp provided in the order.

mvn migration:down

A goal which will execute the undo section of the last migrated script.

mvn migration:pending

A goal which will execute all pending migation scripts one by one based on the timestamp provided in the order.

An alternate for MyBatis Migration is FlyWay and Liquidbase. You can download the fully functional working copy of the project from GitHub.

In case if you are wondering if we have any similar tool for MongoDB, then you can explore MongoBee which works similarly but only thing it lacks is reverting the changes as it’s not supported currently. To know more about it, please visit MongoBee.