Books/articles/recommendations on how to write a tool to migrate database data from one version of a Hibernate enabled Java application to the next

Question

We develop and support and application that uses Hibernate as an Object-Relational Mapping tool to persist our Java objects into the database. Unfortunately, there have been some changes in object model, and thus the database schema, between OurApp 1.0 and OurApp 2.0. So, we would like to write an automated tool to migrate the data from databases of customers using OurApp 1.0 to a new database with a schema matching OurApp 2.0.

We were thinking of creating a tool that has two versions of each Java class. For example we might have a Java class MyObject_1_0 that matches the MyObject class as it appears in OurApp 1.0 and a MyObject_2_0 that matches the MyObject class as it appears in OurApp 2.0. We would then write code that knows how to convert a MyObject_1_0 to a MyObject_2_0.

However, this isn't something I've done before and I'd like to know if this is the best approach. It would be nice if I could review some literature and research on best practices and common pitfalls. Can anyone recommend books or articles that I might find useful as we research the most effective ways to do this?

Thanks!

Answer 1

I would first recommend to look at dbMigrate and liquiBase and maybe also dbMaintain.

These libraries, tools are purpose build to do schema migrations. They use different approaches.

They may be part of your solution, or even be sufficient in themselves.

Answer 2

We were thinking of creating a tool that has two versions of each Java class. For example we might have a Java class MyObject_1_0 that matches the MyObject class as it appears in OurApp 1.0 and a MyObject_2_0 that matches the MyObject class as it appears in OurApp 2.0. We would then write code that knows how to convert a MyObject_1_0 to a MyObject_2_0.

As suggested by Peter, I would indeed consider using a database migrations tool instead, this should be much more efficient and also less work at the end as we'll see. And since the target database isn't under your control, I would look for a something allowing to describe changes in a database agnostic way, like liquibase, which uses XML.

Another argument in favor of liquibase is that it provides Hibernate integration which is a killer feature for your scenario: it can generate a migration script by comparing the new mappings of your entities (XML or annotations) against an old database. Even if the result needs some tweaking, this should highly simplify the work. From the documentation:

Hibernate Integration (Since 1.6)

The LiquiBase-Hibernate is a replacement to Hibernate's hbm2ddl functionality.

Advantages of LiquiBase over hbm2ddl

While hbm2ddl works in general, it is basically a database diff tool and therefore has all the problems associated with database diff tools.

The LiquiBase-Hibernate integration records the database changes required by your current Hibernate mapping to a change log file which you can then inspect and modify as needed before executing. Development Process

Using Hibernate with LiquiBase consists of the following steps:

1. Make needed changes to your Hibernate-mapped objects
2. Run diffChangeLog between your Hibernate config file and your development database (see examples below)
3. Inspect and modify new change sets (if needed)
4. Update your database with the new changes

And you could either provide SQL script for all supported database or simply wrap the tool in some Ant build.

This is also an opportunity to adopt such a tool in your development process.