Sustainable peace with database changes into a Java environment

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

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.
 

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

Leave a Reply


+ nine = 13



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close