Hello Liquibase

You will never check-in your code without any version control tool. Be it GIT or SVN or anything. Then why would you make changes in your database without and versioning strategy. Would you?

Liquibase is one of the database changes version control tool. It is an open source library for tracking, managing and applying database changes that can be used for any database with a JDBC driver. It is build on simple rule: all database related changes should be available in human readable form and available in code repository readily accessible to all the developers.
Or in other words, This is a record of the schema changes over time. It allows the database designer to specify changes in schema & enables programmatic upgrade or downgrade of the schema on demand.

At its simplest form, Liquibase is a tool that reads a list of database changes from a changelog file available in our code. At the beginning of our application development, the file starts out empty but as you make changes to your application, that require corresponding changes in the underlying database, you keep adding a description of the database related changes to the changelog file. The action or description of the changes can be standard SQL commands such as “add column”, “delete column” or more complex descriptions such as “Stored Procedures”. The changelog file is often XML-based, but does not have to be. It could come in other flavors as well like YAML, JSON and SQL Queries. Once a new change is added to the changelog file, Liquibase will read the file, find the new change and apply it to the database. When you commit your code changes to your version control system, you commit the changelog file containing the database “version” alongside it.

This brings discipline among the developers when it comes to changing or updating schema. You just can’t go and overwrite other developer’s change and execute. Instead you create your own changeset, add it to the end of sequence of changes to be executed. This also brings in clarity on what changes came, when and who brought it into the code.

Compared to other database change tracking tools, Liquibase has three defining differences:

1. Understand how the database changed, not just what changed: 

Suppose you have to rename your table’s one of the column name from person.fname to person.first_name. A database comparison would see that there is no longer a person.fname column and there is now a person.first_name column. The SQL it would generate would be:

alter table person drop column fname;
alter table person add column first_name varchar(255);

Since the above schema is correct in form but when we apply this to our production database, you will find that all your records have null in person.first_names column and there is no person.fname column anymore. Because the tool was not smart enough to understand how the schema changed, you have lost data.

There are many similar examples of the importance of knowing how the schema changed. In order to protect the database, Liquibase does not use automatic comparison but instead relies on a changelog being built up manually one changeset set at a time.

2. Handle Multiple Developers and Multiple Branches/Merges

In particular, database versioning tools are built around a concept of a linear database “version” which starts at version 1. After a change is added, the version is incremented to 2, then 3, etc. When an existing database is set to be updated, the current version is determined and all the changesets after that version are applied.

This works well when there is only developer in the team but this may fail when we have team of developers and everyone is working on thier own changeset. This can be managed with good team communication, but falls apart completely when versions 4 to 10 were added in a feature branch that needs to be integrated back into the master branch that already has versions 4 to 20 added.

Liquibase does away with this issue by using a unique identification scheme for each changeset that is designed to guarantee uniqueness across developers and branches while still being easy to manually manage. As you will see in the below examples, each Liquibase changeset contains two attributes: an “id” and an “author”. These two attributes along with the name and path of the file make up the changeset identifier Liquibase uses to determine if it has been ran against a given database. At update time, each changeset is compared against the list of applied changesets and it is executed if and only if it has not been run before. Since the comparison is done for each changeset instead of being based on a single “version”, any new changesets brought into the changelog file – whether from a different developer or from a different branch – will be correctly executed.

3. Higher Level Refactorings

Finally, Liquibase supports not just standard create/alter/update SQL statements, but higher level database “refactorings” such as “split column” and “introduce lookup table” which allow complex database changes to be described and managed easily. This not only makes the initial changelog creation easier, but improves readability and traceability. Furthermore, you gain the ability to support updating and managing the same schema across multiple database vendors using the same changelog file.

Liquibase is built on the Java platform, and therefore requires the Java runtime environment (1.5+) to be installed in your system.

Liquibase Best Practices

Managing Stored Procedures

Try to maintain separate changelog for Stored Procedures and use runOnChange=”true”. This flag forces LiquiBase to check if the changeset was modified. If so, liquibase executes the change again.

One Change per ChangeSet

As far as possible, Avoid multiple changes per changeset to avoid failed autocommit statements that can leave the database in an unexpected state.

ChangeSet Ids

Choose what works for you. Some use a sequence number starting from 1 and unique within the changelog, some choose a descriptive name (e.g. ‘new-address-table’).

Document ChangeSets

Use <comments> in the change sets. They say “A stitch in time saves nine!”

Always think about rollback

Try to write changesets in a way that they can be rolled back. e.g. use relevant change clause instead of using custom <sql> tag. Include a <rollback> clause whenever a change doesn’t support out of box rollback. (e.g. <sql><insert>, etc)

Reference Data Management

Leverage Liquibase to manage your Reference Data. Environment separation (DEV, QA, PROD) can be achieved using “context”.

Procedure for the developer

  • Using your favorite IDE or editor, create a new local changeSet containing the change;
  • Run Liquibase to execute the new changeSet (this tests the SQL code);
  • Perform the corresponding changes in the application code (e.g., Java code);
  • Test the new application code together with the database change;
  • Commit both the changeSet and the application code.

Hope this had cleared your some of doubt about Liquibase. I’ll post, how to integrate Liquibase with your application via different means i.e. CLI or Maven etc.