BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ
News Articles Presentations Podcasts Guides

Topics

Choose your language

QCon New York

Find real-world practical inspiration from the world’s most innovative software leaders. Attend in-person or online.
QCon San Francisco

Learn what's next in software from world-class leaders pushing the boundaries. Attend in-person or online.
The Software Architects' Newsletter

Your monthly guide to all the topics, technologies and techniques that every professional needs to know about. Subscribe for free.

InfoQ Homepage News Database Migration and Refactoring with LiquiBase

QCon New York (June 13-15): Learn best practices from senior developers at early adaptor companies.

Database Migration and Refactoring with LiquiBase

This item in japanese

Bookmarks

May 16, 2007 1 min read

Write for InfoQ

Join a community of experts. Increase your visibility.
Grow your career.Learn more

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.

Rate this Article

Adoption
Style

This content is in the Architecture topic

Related Topics:

Hello stranger!

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

Get the most out of the InfoQ experience.

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

Community comments

  • Interesting

    by Sebastien Auvray,

    Your message is awaiting moderation. Thank you for participating in the discussion.

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

    Back to top
  • Re: Interesting

    by Geoffrey Wiseman,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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?

    Back to top
  • Re: Interesting

    by Nathan Voxland,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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.

    Back to top
  • DB Diff is what is really needed

    by Jonathan O'Connor,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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.

    Back to top
  • Re: DB Diff is what is really needed

    by Alex Popescu,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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

    Back to top
  • Re: DB Diff is what is really needed

    by Nathan Voxland,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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.

    Back to top
  • Why reinvent the wheel?

    by Nikolay Kolev,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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

    Back to top
  • Re: Why reinvent the wheel?

    by Fyodor Kupolov,

    Your message is awaiting moderation. Thank you for participating in the discussion.


    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!

    Back to top
  • Re: Why reinvent the wheel?

    by Nathan Voxland,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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.

    Back to top

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

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

BT