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
First of all, we are following Liquibase's best practices.
Directory Structure
We organize our changelogs by major releases then include the major release changelogs to one master changelog file. The master changelog is the one that is ran to get the latest database changes. The initial structure is shown below:
Master Changelog
<?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>
Major Release Changelog
<?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>
Sample Changeset
--liquibase formatted sql --changeset kpalis:test_1 context:dummy alter table variant rename column code to variant_code; --rollback alter table variant rename
SQL Formatted Changesets
Liquibase offers the ability to write changelogs on XML, SQL, YAML, JSON, etc. But we will only use SQL changelogs/changesets for manageability and consistency. Read more about the syntax here: http://www.liquibase.org/documentation/sql_format.html
Contexts
“Contexts” in Liquibase are tags you can add to changeSets to control which will be executed in any particular migration run. Any string can be used for the context name and they are checked case-insensitively.
When you run the migrator though any of the available methods, you can pass in a set of contexts to run. Only changeSets marked with the passed contexts will be run.
If you don’t assign a context to a changeSet, it will run all the time, regardless of what contexts you pass in to the migrator.
If you do not specify a context when you run the migrator, ALL contexts will be run.
Read more about contexts here: http://www.liquibase.org/documentation/contexts.html
For GOBII, we will be using the following contexts:
Context Name | Description |
---|---|
Dummy | As the name implies, these are dummy changesets. They should never be ran on a non-disposable GOBII schema instance |
General | These are changesets that apply on all GOBII instances. This means that they are not crop-specific. Majority of the changes will fall into this context. |
<crop_name> | These are changesets that are only specific to certain crops. This will include seed data (ex. IRRI curators only need to be in the Contacts table of the IRRI GOBII instance). Context names for crop-specific instances will be the crop name, ie. rice, maize, wheat, chickpea, or sorghum. |
The contexts listed here are not final. We may find instances when we need to create more contexts, but for GOBII v1.0, these will very likely stay the same.
Property File
We will use a property file to set the liquibase target and credentials for each run. You will find this file in the $GOBII_postgres_root/builder/liquibase folder having the name liquibase.properties.
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:
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/