Managing database changes with Liquibase

Ever had the problem of keeping track of database changes during your software development project? There is a solution in the form of Liquibase.
Many teams will work on the same database. Perhaps even on the same database tables and overall structure. How can you keep track of whom did which changes (and why perhaps) and make sure that no change is ever forgotten or overwritten?
How can you make sure that databases will remain structurally equal over different environments…

Liquibase can provide this functionality. It provides database refactoring automation and keeps track of the changes. Used in combination with a version control system like SVN or Mercury you get a powerful tool to keep track of these changes cross-teams and over time.
Liquibase is database vendor independent but also supports various platforms to allow more vendor-tailored changes to be performed on a database.

Liquibase uses two database tables and a changelog XML file to maintain database state. The database tables contains checksums and changesetids which have been executed by liquibase on that particular database.
The changeLog XML file is used to hold the changeset definitions which is maintained by developers.

The changelog contains the changeSets. These changeSets basically hold the sql-statements which are needed for a particular change. A changeset can contains changes for multiple schemas and multiple tables.

An example of such a changeset:
<changeSet author="johnny.devmaster" id="123456789">
<createTable schemaName="MYDBSCHEMA" tableName="SOUNDS">
<column name="ID" type="BIGINT">
<constraints nullable="false" primaryKey="true" />
</column>
<column name="SAMPLE_RATE" type="BIGINT" />
<column name="MIME_TYPE" type="VARCHAR(255)" />
<column name="ARTIST" type="VARCHAR(1024)" />
<column name="LANGUAGE" type="VARCHAR(31)" />
</createTable>
</changeSet>
As can be seen from the example above a changeset has an id and author attribute. The author attribute is not mandatory but allows for easy tracking of whom did the changes.
A changeset should contains a ‘one-transaction’ set of changes. This allows for better rollback when executing a changeset throws errors.

Whenever a teammember needs to make a structural change to the database he (or she) can test this on the development environment and when the actual statements have been defined they need to be inserted into the changelog as a changeset.
At the end of a development cycle the software is release together with the Liquibase-changelog.
Liquibase provides a command-line interface (CLI) to execute this changelog against a database.

As part of the deployment-activities the Liquibase CLI should be executed to get the database for the environment up to par with the release software.

When using Liquibase you should refrain from altering the database manually.
If absolutely necessary to make changes outside of Liquibase make sure that liquibase knows about it to prevent future changelog-execution problem.

More information about this great tool can be found at liquibase.org