LiquiBase

If you didn’t see, version 1.1.1 of LiquiBase was released the day after 1.1 because of problems reported by users with the new diff tool.

Now, we could simply say “it’s a new feature, you should expect bugs”, but they were pretty obvious ones that should have been caught by simply executing the diff tool against all our supported databases. A simple unit test or two that actually ran the diff tool exposed the bugs and they were easy to catch.

Why weren’t those tests wrote before the 1.1 release? It was because I was testing with a mock database connection which didn’t capture all the idiosyncrasies of different databases. The tests were there, and the coverage was there, but there were still bugs because of limitations in the (mock) abstraction layer.

The lesson to be learned is that when writing database access level code, never use a mock database connection.

LiquiBase 1.1.1 has been released to address issues found with the diff tool in the 1.1 version.

LiquiBase 1.1 has been released. Major new features include:

• Support for additional databases: DB2, Derby, Sybase (not fully tested), and HSQL
• Support for “unsupported” databases
• Database Diff Tool
• Database Creation Script Generator
• new DBMS attribute on change set tag that allows you to specify what databases to run the change set against
• “verify” command that checks for change log problems without attempting to execute any
• “status” command that shows information on unrun change sets
• Handle date/time and numeric values better on inserts and setting default values
• Bug Fixes

Upgrading is simply a matter of replacing the liquibase.jar file. To take advantage of newer change log features, change your XSD declaration to:

<databaseChangeLog    xmlns=”http://www.liquibase.org/xml/ns/dbchangelog/1.1″    xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”    xsi:schemaLocation=”http://www.liquibase.org/xml/ns/dbchangelog/1.1    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.1.xsd”>

Download LiquiBase 1.1

1.0.1 is a minor bug fix release that addresses an issue with null values in the defaultValue attribute being quoted.

Reg Developer in the UK recently posted an interview with Scott Ambler about database refactoring at http://www.regdeveloper.co.uk/2007/07/04/evolutionary_database_design/.

In the interview, he discusses questions developers and DBAs often have when first approaching evolutionary database design (EDD). The two main points he covers are:

1. Having both developers and DBAs involved in the database design process is an improvement over “traditional” waterfall-style databases designed only by the DBA.
2. If you make your schema changes with triggers and views correctly, you can have a “transition window” that will allow you to make your changes without breaking existing systems that use the database.

While I agree with both these points, I worry about his focus on “transition window” database refactoring methods. There is certainly value in being able to provide these windows for databases that have many independent systems depending on them, but I think that for the vast majority of projects it makes database refactoring seem too difficult to even attempt.

I would propose that more focus needs to be placed on the simpler aspect of EDD: databases that are specific to a single system or set of related systems that are all managed and updated in a frequent, agile manner. These are the teams most likely to adopt an EDD process and there is still a lot of work to get them the tools they need to work efficiently.

In the interview, Scott says that “right now we’re at the beginning of the adoption curve” in regards to EDD and I agree with that. So–for now–let’s focus on the needs of the majority of those early adopters.

I see the tools and practices required to do EDD efficiently as a spectrum that range from a single-project database with no DBA, through a set of related projects with a part time DBA involved, to a database that supports a large set of independent systems that is under the constant care of a DBA. “Transition Window” methods of database refactoring work great for the high end of the spectrum and I don’t think it should be ignored because it is important to know that a process can scale.

For now, however, we need to start a the simple end and build a strong foundation of tools and techniques before working our way up the stack to the top. There is the start of the required tools in the form of DBUnit for unit testing and LiquiBase for managing refactorings, but there is still large holes including IDE support for refactorings, best practices and pattern catalogs for database testing, and more.

Managing, tracking, and applying database changes is difficult, especially in an agile database environment where there are many changes made throughout a project’s life cycle. Even with a tool like LiquiBase, It takes a lot of discipline to apply your database changes in a consistent and traceable manner.

For projects that need to deal with SOX-compliant releases the process is even more difficult because your release documentation needs to include not only how to update your database, but also how to roll back in case of a problem with the release.

It was to address this problem that we added automatic rollback support to LiquiBase. For each changeSet in your change log file, LiquiBase can (usually) generate rollback SQL. For changes that cannot be automatically undone (drop table, insert data, etc.), or if you want to override the default rollback method, you can specify a tag containing the correct SQL. This method of generating rollback commands works well because for most cases you don’t have to do anything, and when you do have to specify rollback SQL, it is stored along side the original change.

To control the generation of generating SQL for updating a database and for rolling it back, see the command line migrator documentation.