Database Version Control With Liquibase

Database Version Control With Liquibase

Introduction to managing DB shcema changes with Liquibase

2020-09-06

Motivation

Let me give you a scenario,
You have a project with multiple database instances in different environments (Dev, QA, Production) and you need to manage the database schema changes that are done against those environments. Let’s assume that you are managing those changes by creating a git project or a shared file on a drive and adding a new SQL file for each database changes you are doing. To implement your changes in that database, for each SQL file, you need to run the changes in each environment manually and add a flag or note to indicate which change is run on which environment.
Would it be able to complete your task? Yes, Yet, is it a decent method to manage schema changes?
Of Course Not.

Here are some of the many problems you might face while doing so

  1. Hard to synchronize database and application code changes in different environments
  2. The tedious process to run each change manually in different environments
  3. Collaboration across the development team on what change is deployed and what is not
  4. Hard to roll-back to the previous version of the database
  5. Possibility of data loss

Here comes Liquibase

Liquibase is an open-source library for tracking and managing database schema changes that can be used for any database with a JDBC driver.
It is a platform-independent database migration tool that allows the database changes referred to as ‘changesets’ to be written in various formats including XML, JSON, YAML, and SQL.

Features

  1. Supports almost all databases that have a JDBC driver.
  2. Changesets can be written in different formats like XML, JSON, YAML, and SQL.
  3. Can be used to automatically generate changesets for an existing database
  4. Easy to integrate with build tools like Jenkins, Maven etc
  5. Supports database rollbacks
  6. Supports context-dependent logic allowing us to use global context and preconditions
  7. Can be executed via command line, Apache Maven, Apache Ant, Spring Framework
  8. Has feature to generate changeset from an existing database and can also generate schema difference as changesets

Different ways to run liquibase

  1. Embed liquibase with your app: Embedding liquibase with your application code will automatically deploy liquibase on the app startup.
  2. Run liquibase using build tools: Integrate liquibase into your build process (with build tools like Jenkins, Ant, Maven, and Gradle) and update them without being tied up with the application.
  3. Generate the SQL and run it manually: Using update SQL, Liquibase provides the SQL generated from the changeset along with the database changes required to keep the tracking tables up to date. DBA will then inspect the SQL and run them against the database.

Installation Process

Prerequisites: Liquibase requires Java 8+

There are two ways to install Liquibase, Manual installation and using liquibase installer.

If you set up liquibase using the liquibase installer, dependencies, directories, config and properties files will all be in place already. It also provides some examples which will provide you with the core concepts required to understand the changesets.

In the case of manual installation, you need to download the compressed liquibase file and extract it in your workspace.
For windows users, you need to add a new PATH variable in the Environment Variables.
For macOS users, the path should be added to the bash.profile file.

For detailed instruction on Installation please follow this document .

Core Concepts

  1. liquibase.properties: The file liquibase.properties is a text-based file that stores common properties like database connection parameters, driver details, classpath parameters, global changelog parameters etc. If you install liquibase using liquibase installer, it will provide pre-written liquibase.properties file while in case of manual installation, you need to create liquibase.properties file using a sample file provided.

Sample liquibase.properties file for Oracle

  1. DatabaseChangeLog: Databasechangelog is a file where all changesets go. Each database changelog can include one or more changesets.

Example of an empty DatabaseChangeLog file

  1. Changeset: In liquibase, a changeset is represented as an atomic change to the database. Each changeset should be uniquely identified using author and id fields. The database handles each changeset as a single transaction. Changesets can be written in JSON, XML, SQL and YAML formats.

Example of a changeset in XML format

  1. DATABASECHANGELOG & DATABASECHANGELOGLOCK: These two tables are created by liquibase to track the changes that are run against the database and to make sure that no other migrations age going on.

DATABASECHANGELOG table structure

DATABASECHANGELOGLOCK table structure

What if I don’t like Liquibase?

If you told me that you don’t like liquibase and are looking for alternatives, I would ask why not Liquibase first.
Liquibase is a sophisticated tool for database migration that has all features that you need for professional database refactoring and versioning.

But still, if you don’t want to use liquibase, here are some alternatives.

  1. Flyway: Flyway is an open-source Apache licenced tool for database migration where you can write migrations in database-specific SQL or using Java code. For more details on Flyway, you can refer to this website. https://flywaydb.org/

  2. YUNIQL: YUNIQL is also an open-source schema versioning and database migration engine that uses plain SQL scripts which can be integrated with CI/CD pipelines. If you want to check out YUNIQL, you can refer to this website. https://yuniql.io/