Liquibase

This document will serve as an introductory article to Liquibase as well as set the guidelines on how we use it in GOBii Postgresql Database.

Overview

Liquibase is an open source database-independent library for tracking, managing and applying database schema changes. It was started in 2006 to allow easier tracking of database changes, especially in an agile software development environment. It is a framework written in Java used to manage and apply your change files (i.e. sql, xml, json, or yaml). Their website sports the tagline “source control for your database”, which might be a bit misleading. Liquibase is not a version control system like Git or Subversion. In fact, it is meant to be used in tandem with a version control system. When you use Liquibase you will have a project, just like any old Java project, that contains your sql files. When you run this project Liquibase will install your changes to the database. You can also embed Liquibase (and your sql files) into an existing project, allowing your application to manage its own database. Liquibase is meant to bring the management and deployment of your sql files into the familiar developer realms of IDE’s, version control, and continuous integration.

In order for Liquibase to work its magic, you have to give it some hints. These hints come from markups to your SQL files, or by writing your SQL changes in XML, YAML, or JSON. This is the biggest pain point I have found for developers. Even if you stick with normal .sql files, you still have to add flags (in the form of comments) telling Liquibase how to work with the file. It is worth it. Once you get in the habit, it’s no harder than writing normal sql.

Major Functionalities

• Over 30 built-in database refactorings

• Extensibility to create custom changes

• Update database to current version

• Rollback last X changes to database

• Rollback database changes to particular date/time

• Rollback database to "tag"

• SQL for Database Updates and Rollbacks can be saved for manual review

• Stand-alone IDE and Eclipse plug-in

• "Contexts" for including/excluding change sets to execute

• Database diff report

• Database diff changelog generation

• Ability to create changelog to generate an existing database

• Database change documentation generation

• DBMS Check, user check, and SQL check preconditions

• Ability to split change log into multiple files for easier management

• Executable via command line, Apache Ant, Apache Maven, servlet container, or Spring Framework.

• Support for 10 database systems

Concepts

Changesets and Changelogs

Changesets are units of work for Liquibase to apply. It is basically the sql you want applied to the database. Each changeset should be a single, independent unit of work. You should never have one changeset applying multiple changes unless it is absolutely necessary. Here is why:

Liquibase attempts to execute each changeSet in a transaction that is committed at the end, or rolled back if there is an error. Some databases will auto-commit statements which interferes with this transaction setup and could lead to an unexpected database state. Therefore, it is usually best to have just one change per changeSet unless there is a group of non-auto-committing changes that you want applied as a transaction such as inserting data.

Changelogs are how you tell Liquibase what changesets to apply and in what order. The order in which the changesets appear in the changelog is the order in which they will be executed. Think of them as your release documentation. They are composite-able, so you can include changelogs inside of changelogs. This is advisable so you don't end up with one massive master changelog. A pattern I like to follow is to group like changesets, for instances all changes for a given user story, into a single changelog file. This changelog is then included in the master changelog file for a release. This gives you the benefit of independently applicable changesets as well as better developer accessibility from grouping changes together in a single file.

Liquibase in GOBii

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
	<include file="changelogs/db.changelog-1.0.xml"/> 
</databaseChangeLog>

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
	<include file="changelogs/changesets/test_changeset1.sql"/>
</databaseChangeLog>

--liquibase formatted sql
--changeset kpalis:test_1 context:dummy
alter table variant rename column code to variant_code;
--rollback alter table variant rename

driver: org.postgresql.Driver
classpath: drivers/postgresql-9.4.1209.jar
url: jdbc:postgresql://localhost:5432/gobii_liquibase_test2
username: appuser
password: appuser
changeLogFile: changelogs/db.changelog-master.xml
contexts: dev

Sources:

• http://mmstratton.com/wp/liquibase-introduction/
• https://en.wikipedia.org/wiki/Liquibase

Supplementary Readings

• http://mmstratton.com/wp/managing-enterprise-database-changes/
• http://henryranch.net/tutorials/a-simple-introduction-to-database-change-management-with-sqlite-and-liquibase/