Is Liquibase recommended for migrating production data from an old database schema to a new one?

Question

I'm new to the world of data migration and looking into ways to migrate the data of customers using OurApp 1.0 to a new database with a schema compatible with OurApp 2.0. I've seen a more than a few people recommending Liquibase for database change management and database refactoring tasks, which to my newbie ears sounds like it may be close to what we need.

However, after reading through the material on www.liquibase.org, I get the feeling that Liquibase is more about keeping the schema up to date than it is about converting lots of already existing data so that it can be persisted in the new schema.

Say I wanted to split a column in my Employee table called name into a firstname and lastname column. Liquibase would be able to alter the table by dropping the name column and adding a firstname and lastname column. However, I get the feeling Liquibase isn't really built for me to plug in conversion code that would parse the name field of existing records in the database into a firstname and lastname and store those in their respective columns.

For example, say my table looked like this

id | name             | position
*********************************
12   Horace Slughorn    Professor
13   Albus Dumbledore   Headmaster

After I ran Liquibase the name column would be replaced by a firstname and lastname column so my database schema would be correct. But I'm guessing Liquibase is NOT a framework that lets me plug in some code that parses "Horace Slughorn" into "Horace" and "Slughorn" and stores these values in the firstname and lastname columns for that record.

id | firstname   |  lastname  | position
*****************************************
12   Horace         Slughorn         Professor
13   Albus          Dumbledore       Headmaster

So Liquibase keeps your schema up to date, but isn't designed to help you convert your existing data so that it it matches the new schema. Is that right?

Answer 1

The goal of Liquibase is to allow you to move your database from one version to the new version. It has many built in database "refactorings", but it does not have one to split your data as you are describing because how you break up existing strings is so dependent on your implementation.

Liquibase does allow you to create custom change classes (http://www.liquibase.org/manual/custom_refactoring_class) in 1.9 as well as a much more powerful custom change support in 2.0 (http://liquibase.org/extensions). It is java based, so your script would need to be written in java, but you can do any data reading and manipulation you need.

Note: if you have a lot of data, reading the records into java, then writing them back out will be slow. In that case, you can use liquibase's tag and create database-based sql statement(s) and/or stored procedure(s) that will do the splitting in-database. Liquibase will still be very helpful to track that the splitting has been done and should not be done again.

Answer 2

You can also use dbdeploy if you want to write custom SQL. Liquibase supports writing custom SQL too, there is DBMaintain or iBatis Migrator.