BT

Database Migration and Refactoring with LiquiBase

by Geoffrey Wiseman on May 16, 2007 |

Although the process and tools used to develop software are interrelated, the world of database development has not always been influenced as heavily by modern IDEs, agile processes and build tools as, say, Java, .NET and Ruby. At times, this has led to a mismatch between the processes and tools used by two team members in a single software project, or even a single team member filling two roles.

In recent years, there has been a fair amount of discussion about how this mismatch can be reduced. In particular, Scott Ambler and Pramod Sadalage's Refactoring Databases have popularized the idea of developing databases through a series of fine-grained, trackable changes with automatic migration, sometimes called 'database refactoring'.

If you're not using ActiveRecord in Ruby on Rails, and particularly if you're using Java, LiquiBase (once known as the Sundog Database Refactoring Tool) is an interesting candidate: an open-source tool for database refactoring and migration.

LiquiBase can perform database migrations as part of a build with Ant support and a nascent Maven plugin, or as part of your deployment process, using a Servlet listener (http://www.liquibase.org/manual/latest/servletlistenermigrator.html). Alternately, it can be invoked directly from the command-line. LiquiBase has good documentation and a quick-start guide to help you get up and running quickly. It should be compatible with any JDBC database, although testing has focused on Oracle, Microsoft SQL Server, MySQL and PostgreSQL.

LiquiBase has a recent release and future plans that include a Database Refactoring IDE plugin, support for stored procedures and views, test data generation and more.

Hello stranger!

You need to Register an InfoQ account or to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Interesting by Sebastien Auvray

This soft looks great!
Anyone already using that one ?
By the way it doesn't support Sybase (Oracle, MySQL, PostgreSQL, and MS-SQL)

Re: Interesting by Geoffrey Wiseman

Isn't tested on Sybase, but may support it; they're planing on focusing some of the pre-release roadmap on improved database support, so perhaps that'll come soon?

Re: Interesting by Nathan Voxland

Hi, I'm one of the main developers of LiquiBase. I think that if you tried it with Sybase, it would fail out pretty early saying it's an unsupported database. Adding support for additional databases is not difficult, it is mainly a matter of testing.

We are planning on adding support for many more databases soon after the 1.0 release, if you have experience working with a particular database and would like to help with the coding efforts, be sure to let us know.

DB Diff is what is really needed by Jonathan O'Connor

In my projects I just write a SQL update scripts to move from one version to the next. However, during development of a new version, using JPA, its easy to forget what changes were made to the DB. Can LiquiBase compare two DBs and generate an update script? That is the feature that is needed.

Re: DB Diff is what is really needed by Alex Popescu

In my projects I just write a SQL update scripts to move from one version to the next.However, during development of a new version, using JPA, its easy to forget what changes were made to the DB. Can LiquiBase compare two DBs and generate an update script? That is the feature that is needed.


I was using a similar process (with specific naming conventions that were allowing upgrading/downgrading). This worked well for a while, till new team members were added and they started to directly modify the schema. Indeed, having a diff kind of tool would be an interesting feature. I am wondering if such a tool would not be even better fitting the DB itself, so that it creates a journal of the schema changes.

./alex
--
.w( the_mindstorm )p.
________________________
Alexandru Popescu
Senior Software Eng.
InfoQ TechLead&CoFounder

Re: DB Diff is what is really needed by Nathan Voxland

We have thought about "diff"ing adding it as a later feature.

The trouble I've always had with diff tools is that you don't know HOW it got from point A to point B. Just because the old database has just a table called "Employee" and the new database has a table called "Person" and no "Employee" doesn't tell you if you should drop "Person" and create "Employee" or if you should rename "Person" to "Employee". If you pick wrong, you could either loose valuable production data or end up with wrong data. The same thing can happen with renamed columns, joined columns, moved columns, etc.

Plus, where to you stop with diff tools? Changes to data is often as important as schema changes, but you can't compare every row in the database, especially if your development database doesn't have the exact same data due to differences in time it was extracted, or differences in test data.

I see having a diff tool as a good backup/sanity check to make sure no changes were made outside of your change management tool. It would see differences and let you decide if (and what) changes need to be made to your update scripts. While it would be nice to diff, we felt we needed to first get the base change tracking system going as a foundation.

Why reinvent the wheel? by Nikolay Kolev

Have you bothered looking into Rails Migrations or Django Schema Evolution?

Adding, dropping, and altering tables, fields and so on are the least of my concerns - there are many tools that autogenerate such SQL scripts. Migrating existing data (for example, splitting a FullName column into two separate FirstName and LastName columns) is the real issue and the only option we're given by using LiquiBase is to maintain different "changelogs" (BTW, you should pick a different term) for each DB vendor supported.

I haven't personally used it, but I've seen people highly recommending Scriptella for DB migrations in the Java World.

nikolay

Re: Why reinvent the wheel? by Fyodor Kupolov


I haven't personally used it, but I've seen people highly recommending Scriptella for DB migrations in the Java World.

A new version of Scriptella ETL 0.9 was released yesterday. While its primary goal are migrations between different databases (and other ETL operations) Scriptella provides several solutions for Rails-like migrations in Java.

In contrast to Rails Migrations we believe in plain old SQL-based approach which supports existing scripts and do not tie to the specific framework like Migrations or Scriptella. So it's OK to continue using your favorite update_{VERSION}.sql files. Additionally Scriptella ETL support many useful features like importing CSV, XML data and even producing sample data with any scripting language like JEXL, JavaScript or using Java code. Scriptella can be called from Ant (etl task), Java SE/EE (EtlExecutor class) and Spring (EtlExecutorBean) environment using only 1-2 lines of code (see tutorial).

Make ETL Easier - Use Scriptella ETL!

Re: Why reinvent the wheel? by Nathan Voxland

I have looked at other options that are available, but I've found that none of them fit the needs I had.

I have looked into Rails Migrations, but always run up against the problem that, while they're working on it, it doesn't support multiple branches and multiple developers well.

Other solutions weren't cross-database, or didn't allow us to modularize our changes well.

I don't think LiquiBase is re-inventing the wheel, it is just adding an additional choice that may work best for some people.

I agree that more complex refactorings is what is needed and we have begun adding ones such as Merge Columns. We plan on adding more as time goes on, we are still a new project.

You also don't have to create multiple changelogs per vendor. Depending on your changes, the same changelog can be applied to any database. If there is database-vendor specific changes, you can use contexts to specify which changes belong to which vendor. I'll work on the documentation to make that more clear.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

9 Discuss

Educational Content

General Feedback
Bugs
Advertising
Editorial
InfoQ.com and all content copyright © 2006-2013 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT