Liquibase Series: About Liquibase

What is Liquibase?

Liquibase is an open-source database migration tool that provides organizations with an effortless way to track, version, and deploy database schema changes.

Liquibase uses commands and Change Types (changes to database schemas) that allow you to specify what you need Liquibase to do and how you want it to be done.

Liquibase uses changelogs and tracking tables to determine what is deployed to the database after the commands have been run.

Features of Liquibase

• Liquibase is compatible with a broad range of databases. For a complete list of supported databases, see Liquibase Getting Started.
• A flexible extension framework that allows for seamless additions of newly released platforms and integrations.
• Full control of when, where, and how database changes are deployed.

Liquibase Supports

• Ability to merge changes from multiple developers.
• Code branches.
• Multiple databases.
• Cluster-safe database upgrades.
• Generation of starting changelogs from existing databases.
• Generation of database change documentation.
• Automated updates or generation of SQL.

Database Schema Changes Supported Files

• SQL
• XML
• YAML
• JSON

Changes are specified as changelogs, changesets, and Change Types.

How Liquibase Works

There are two main mechanisms, or concepts, that are the foundation of Liquibase. These two concepts are:

1. Schema Changes which consist of changelogs, changesets, and Change Types.
2. Tracking tables that record database changes.

Schema Changes

In SQL, schema changes are written using SQL statements that

• create,
• modify, or
• drop database objects.

Examples of typical schema changes are

• create table,
• add index, and
• drop column.

In XML, YAML, or JSON, the same schema changes are modeled as Liquibase Change Types.

• One or more schema changes (Change Types) are grouped into a changeset. The best practice is to limit each changeset to only one Change Type.
• One or more changesets are contained in a changelog.
• Changelogs are text files containing schema changes. They should be stored and versioned in your preferred source control tool.

In addition to containing schema changes, a changelog can include other changelogs. This allows multiple teams to each work on their own changelogs so they can work independently.

Tracking Tables

Tracking tables are used to track, version, and deploy database schema changes. If your database does not contain a tracking table, Liquibase will create one for you.

Liquibase uses two types of tables to track successful schema change deployments:

1. DATABASECHANGELOG
2. DATABASECHANGELOGLOCK

DATABASECHANGELOG tracks each changeset in the changelog by id, author, and the file where the changeset resides. The composite of “id”, “author”, and “filename” is unique across all rows of the table.

DATABASECHANGELOGLOCK ensures only one instance of Liquibase is running at one time. It locks others out to prevent multiple Liquibase commands from being executed at the same time which could cause database conflicts. This assists in keeping other Devs, DBA’s, or teams from overwriting your changes accidentally.

Liquibase Commands

Below are common commands used when working with Liquibase. They include:

• update — applies to all unrun changes.
• rollback — reverts (rolls back) changes you have made to your database.
• snapshot — used when you want to quickly compare changes in your database or keep a record of your current database state.
• diff — allows you to compare two databases of the same type or different types to one another.
• diff-changelog — used when you want to create a deployable changelog to synchronize multiple databases.
• history — a helper command that lists out all your deploymentIds and all changesets associated with each deploymentId.

Installation

Navigate to this page to download liquibase installer or compressed file to unzip locally.

Liquibase will not run without Java. Make sure Java is correctly installed on your system by checking the version of Java installed on your system.

After the installation , Open your Terminal or Linux Command Line and type

liquibase --help

Liquibase Configuration

liquibase.properties

This file stores properties that are frequently used and do not change often. The most useful properties are the database connection information.

If you set the values in the liquibase.properties file, then the values do not need to be specified on the command line each time you run a command.

Values specified on the command-line will always override the values in the Liquibase properties file.

Liquibase Changelogs

Liquibase uses a changelog to list all changes, in order, made to your database. Think of it as a ledger. It is a file that contains a record of all your database changes (changesets). Liquibase uses this changelog record to audit your database and execute any changes not yet applied to your database.

Formatted SQL files use comments to provide Liquibase with metadata. Each SQL changelog file must begin with the following comment:

-- liquibase formatted sql

A SQL changelog may look like this:

-- liquibase formatted sql

-- changeset manish:1
create table person (
    id int primary key,
    name varchar(255)
);

-- rollback drop table person;
-- changeset manish:2
insert into manish (id, name) values (1, ‘manish 1′);
insert into manish (id, name) values (2, ‘manish 2′);

-- changeset manish:3 dbms:oracle
create sequence seq_test;

Deploying a changelog will run your changesets. To perform this, you must first run the update command.

• The database you use will have DATABASECHANGELOG and DATABASECHANGELOGLOCK tables created.
• Liquibase reads your list of changesets in order and checks the DATABASECHANGELOG table for anything that was previously run to prevent duplicate runs.

Fig: How Tracking Table data will look like

• Any changeset that has not been applied to the database will be applied during the run. Liquibase will track all of this information.

A changeset is a unit of change that Liquibase can execute on a database. A list of changes created by changesets is tracked in a changelog.

A changeset is defined by both an author and an id attribute, as well as the changelog file path. Both author and id must be included to execute the changeset.

If Liquibase determines a changeset has already run, it will skip it unless directed to execute with a “runAlways” tag set to “true” in the changeset. The id tag does not affect the order of changeset execution.

The DATABASECHANGELOGLOCK Table

To prevent multiple instances of Liquibase running at one time, Liquibase creates and uses a DATABASECHANGELOGLOCK table behind the scenes.

Below is a sample DATABASECHANGELOGLOCK table.

Note: If Liquibase does not exit cleanly, the lock row can get stuck in a locked state that prevents others from making their own database changes even when you are done.

If this happens, you can clear out the current lock by running:

liquibase releaseLocks, which runs
UPDATE DATABASECHANGELOGLOCK SET LOCKED=0

Liquibase Developer Workflow

1. The Liquibase Developer Workflow
2. The Liquibase Developer Workflow is the day to day process for using Liquibase in both pre-production and production environments.

It consists of five steps that migrate your database successfully.

Step 1: Add your changesets to a changelog.

Step 2: Verify the SQL that you will execute.

Step 3: Save your changelog to your source control.

Step 4: Run the database update command.

Step 5: Verify that the changeset or changesets were executed.

Step 1: Add your changesets to a changelog

-- liquibase formatted sql

-- changeset manish:1
create table person (
    id int primary key,
    name varchar(255)
);

-- rollback drop table person;
-- changeset manish:2
insert into manish (id, name) values (1, ‘manish 1′);
insert into manish (id, name) values (2, ‘manish 2′);

-- changeset manish:3 dbms:oracle
create sequence seq_test;

Step 2: Verify the SQL that you will execute.

• take time to review that the changes you have identified are the ones you want to be made to your database.
• Liquibase provides commands called “helper” commands that help identify proposed database changes.
• The updateSQL command is a helper command that allows you to inspect the SQL Liquibase will run while using the update command.

liquibase --changeLogFile=changelog.h2.sql updateSQL

liquibase --changeLogFile=mychangelog_mysql.xml updateSQL

Step 3: Save your changelog to your source control

• Once the required changes have been verified, it is important to save your changelog to a source control system of your choice like GitLab etc.

Step 4: Run the database update command

• Liquibase applies your planned database changes that were added to the changelog file with the update command.

Step 5: Verify that the changeset or changesets were executed

• Run the history command to see a list of deployed changesets.
• Run the status command to see a list of undeployed changesets.
• Open your database IDE and directly inspect the database.

Generate ChangeLog

The generateChangeLog command creates a changelog file that has a sequence of changesets which describes to Liquibase how to re-create the current state of the source database. It is typically used when there is an existing database but Liquibase has not been used before. It assists in getting both databases in sync and on the same schema.

The generateChangeLog command does not create a new database or schema, it just makes a copy of your existing database.

liquibase --changeLogFile=changelog.sql generateChangeLog

Liquibase will create the changelog based on the extension that was specified.

Liquibase Commands

• update command

liquibase -changeLogFile=dbchangelog.sql update
liquibase -changeLogFile=dbchangelog.sql updateCount 2
liquibase hisory

• history command
• snapshot command

The snapshot command is typically used when you want to quickly compare changes in your database or keep a record of your current database state. A snapshot is similar to a photograph of your database that can be used in the diff command or diffChangeLog command.

Snapshots can also be used to compare:

• A previous database state to an online database.
• A previous database state to another snapshot.

Running a diff command using at least one snapshot.yaml file is faster than using a diff command with two online databases.

liquibase --outputFile=myschemaSnapshot.yaml snapshot --snapshotFormat=yaml

• diff command

The diff command allows you to compare two database schemas of the same type, or different types, to one another. It is typically used at the completion of a project to verify that the changes were applied to the database or to detect drift between a model schema and a database’s actual schema.

There are three main reasons to run this command:

• To find missing objects between one database and another.
• To see that a specific change was made to the database.
• To find unexpected items in the database.

Verify or configure your liquibase.properties file to include the referenceURL and its credentials, required attributes of driver classpath, and the database URL.

liquibase diff --outputFile=diff_output.json 

Descriptions: The diff command will produce a list of categories and descriptions

Missing — there are objects on the source database (referenceUrl) that are not on the target database (url).

Unexpected — there are objects on the target database (url) that are not on the source database (referenceUrl).

Changed — the object as it exists on the target database (url) is different than as it exists on the source database (referenceUrl).

• diffChangeLog Command

The diffChangeLog command creates a changelog file containing deployable changesets, but Liquibase determines what these changesets should be based on a comparison of two specified databases.

This command is useful when synchronizing multiple databases. It will provide more information in three main areas:

• To find missing objects in the database.
• To see changes made to the database.
• To find unexpected items in the database.

Verify that the referenceUrl is set to the source database and the database URL is set to the database to be compared.

liquibase diffChangeLog --changeLogFile=file_name.h2.sql

• Rollback Commands

Liquibase allows for reverting changes to the database through rollback commands. Rollback commands will sequentially undo changes that have been deployed to a specified point such as a date or number of changes.

• rollbackToDate <date> — reverts your database to the state it was in at the date and time you specify.
• rollbackCount <value> — reverts a specified number of changesets, where <value> is the number of changesets you want to revert sequentially on your database. There is no number limitation.
• rollback <tag name> — revert all changes made to the database based on the specified tag.

liquibase --changeLogFile=changelog_h2.sql rollbackToDate <YYYY-MM-DD>
liquibase --changeLogFile=changelog_h2.sql rollbackCount <COUNT>
liquibase --changeLogFile=changelog_h2.sql rollback <TAG>

Best Practices

• Liquibase tracks a changeset in the tracking table. It is best practice to execute one change per changeset using the writer’s name as the author. Using unknown as author will work but makes it difficult to know who made the change.
• Organized your changelogs.

All these changelogs can be called from a “root” changelog, which itself is always an .xml formatted file.

• Add comments to changeset
• Add <include> tag

It Allows you to reference multiple changelogs from a root changelog. Also allows Liquibase to uniquely identify each changeset with the id, author, and file name

Included changelogs are run in the order they are found. Make sure that the included changelogs are completely independent or that any required changelogs are run first.

• add <includeAll> tag

Allows you to break up your changelogs into more manageable pieces. It is similar to the <include> tag, but instead of passing a particular changelog file to include, you specify a directory and it will include all *.xml or *.sql files as individual changes.

All files that are found within the directory will be run in alphabetical order.

• Trim your changes

The standard process for using Liquibase is to append individual changesets to your changelog file for each database change you need to make. Over time those changes can build-up to thousands of entries, many of which are now redundant (create a table and later drop it) or inefficient (create a table, then add columns individually vs. just creating the table with all the columns).