Database Migration with Liquibase and DbUnit

Posted by Brian Porter on December 07, 2010

Today I needed to migrate a MySQL database to Oracle – with a repeatable process, as the project is currently in a development phase.

I already knew that I could use DbUnit to migrate the data, once the database schemas were migrated, so I was pleased to discover that I could use Liquibase – a project I had never heard of – to solve that problem.

I setup a ant based script to execute the tasks.

Getting the Libraries

The first thing I did was to download the libraries I needed, and store then in the /lib directory of my migration project:

  • Liquibase
  • DbUnit
  • MySQL JDBC Drivers
  • Oracle JDBC Drivers
  • SL4J (needed by Liquibase)
  • Logback (needed by Liquibase)

Setup Database Connections

Before you begin, create a database property file (name it build.properties) for the databases you want to migrate from/to. db1 is the one you are migrating from, and db2, the one you want to migrate to.

db1.driver=com.mysql.jdbc.Driver
db1.url=jdbc:mysql://localhost/sorucedb
db1.user=user
db1.password=password

db2.driver=oracle.jdbc.driver.OracleDriver
db2.url=jdbc:oracle:thin:@localhost:1521:targetdbsid
db2.user=user
db2.password=password

Create the Ant Script

This ant script loads the build.properties file, and sets up the DbUnit and Liquibase tasks. We are assuming that the necessary jar files where downloaded and placed in the lib subdirectory.

<?xml version="1.0" encoding="UTF-8"?>
<project name="changeme" default="all" basedir=".">
  <property file="${basedir}/build.properties" />
  <property name="lib.dir" value="${basedir}/lib" />

  <!-- Ant classpath -->
  <path id="tool.classpath">
    <fileset dir="${lib.dir}">
      <include name="*.jar"/>
      <include name="*.zip"/>
      </fileset>
    <pathelement location="${basedir}"/>
    </path>

  <taskdef resource="liquibasetasks.properties" classpathref="tool.classpath"/>
  <taskdef name="dbunit" classname="org.dbunit.ant.DbUnitTask" classpathref="tool.classpath"/>
<project>

Export the Database Schema

The next step is to export a Liquibase changelog for the database schema you want to migrate:

<target name="export.changelog">
    <generateChangeLog
            outputFile="${basedir}/ddl.xml"
            driver="${db1.driver}"
            url="${db1.url}"
            username="${db1.user}"
            password="${db1.password}"
            classpathref="tool.classpath" />
    <replace file="${basedir}/ddl.xml" token="int identity" value="int"/>
    <replace file="${basedir}/ddl.xml" token="baseTableSchemaName='dbo' " value=""/>
    <replace file="${basedir}/ddl.xml" token="schemaName='dbo' " value=""/>
    <replace file="${basedir}/ddl.xml" token="image" value="longtext"/>
  </target>

When executed, it creates a file “ddl.xml”

Generating the DDL file for the new database

Next I load the changelog, and generat the DDL script for the Oracle Database. The reason for doing it this way, is that I needed to update the Oracle Schema name manually in the script, as it was not the same in the source/target.

<target name="generate.targetddl">
    <updateDatabase
            changeLogFile="${basedir}/ddl.xml"
            driver="${db2.driver}"
            url="${db2.url}"
            username="${db2.user}"
            password="${db2.password}"
            promptOnNonLocalDatabase="false"
            dropFirst="false"
            outputFile="${basedir}/oracle_ddl.sql"
            classpathref="tool.classpath"/>
  </target>

Note: I also removed “ON UPDATE NO ACTION;” from the end of each statement as it caused problems.

Export the Data

Next I used DbUnit to export the data. You will have to list all tables you need (unless you want all tables in your database), as well as take into account if you have tables without a primary key.

<target name="export.db1">
    <dbunit driver="${db1.driver}"
        url="${db1.url}"
        userid="${db1.user}"
        password="${db1.password}">

      <dbconfig>
        <property name="datatypeFactory" value="org.dbunit.ext.mysql.MySqlDataTypeFactory" />
      </dbconfig>

      <export dest="${basedir}/dataexport.xml" >
          <table name="A_TABLENAME"/>
      </export>
    </dbunit>
  </target>

Note: in order to avoid foreign key problems, I exported the tables in the correct order. It might also be possible to import them with foreign key checks turned off if your database or driver supports it. For example “sessionVariables=FOREIGN_KEY_CHECKS=0” for MySQL

Import the Data

Finally import the data into your new database. Depending on the DbUnit Operation Type you use (or can use), you can make this a repeatable process.

<target name="import.data">
    <dbunit driver="${db2.driver}"
        url="${db2.url}"
        userid="${db2.user}"
        password="${db2.password}"
        schema="${db2.user}"
        >
      <dbconfig>
        <property name="datatypeFactory" value="org.dbunit.ext.oracle.OracleDataTypeFactory" />
        <property name="batchedStatements" value="true" />
        <property name="batchSize" value="500" />
      </dbconfig>
      <operation type="REFRESH" transaction="false" src="${basedir}/dataexport.xml"/>
    </dbunit>
  </target>

Note: This took forever for my large database at first until I realized I just needed to set a batch size so that it was not doing a commit after each insert/update

If you made it this far, you may as well follow me on LinkedIn: Follow Brian Porter