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.
 

Related Whitepaper:

Functional Programming in Java: Harnessing the Power of Java 8 Lambda Expressions

Get ready to program in a whole new way!

Functional Programming in Java will help you quickly get on top of the new, essential Java 8 language features and the functional style that will change and improve your code. This short, targeted book will help you make the paradigm shift from the old imperative way to a less error-prone, more elegant, and concise coding style that’s also a breeze to parallelize. You’ll explore the syntax and semantics of lambda expressions, method and constructor references, and functional interfaces. You’ll design and write applications better using the new standards in Java 8 and the JDK.

Get it Now!  

Leave a Reply


− 5 = zero



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
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.

Sign up for our Newsletter

20,709 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books