Introduction to Liquibase

What is Liquibase?

Liquibase is an open-source library that use to track and manage the database schema level changes of a project. Basically, Liquibase is a database change management tool. Rather than writing and running DDL SQL directly against the database to create, update or drop database objects, developers can define their desired database changes in separate files and able to execute these changes against database

Why Liquibase?

There are some common problems developers face when they are developing a project.

1. Developers have to maintain manually what are the database changes they have made.
2. When moving project to a new environment, sometimes they forget to run some SQL scripts.
3. No versioning in SQL scripts.
4. Not supporting code branching.
5. When multiple developers working on same codebase, there is no monitoring process for database level changes.
6. We have to create database changes documentation manually among versions.

We have GIT as a code level changes management. But for database schema level changes?? We have Liquibase with whole bunch of useful features.

Liquibase act as a version controller for database schema level changes. We don’t have to maintain database change records manually. When moving to a new environment we don’t need to run the SQL scripts manually against the database, Liquibase manage that. It supports multiple developers to work on same codebase without any database conflicts. Using Liquibase, developers can create database change documents easily. Liquibase supports multiples database such as oracle, derby, postgres, DB2, sql server and many more. Moreover we can use Liquibase with build tool such as maven, any and gradle. We do not need a live database to check the SQL scripts. We can generate SQL using Liquibase without execute against database. Can we use Liquibase for existing schemas?? BOOM!! Yes, you can. Liquibase support existing schemas. That’s how awesome Liquibase is.

Features of Liquibase

1. Define database changes in code level
2. Update and manage the same schema across multiple types of database
3. Easily add new changes and reorder them
4. Track, version, and deploy database changes
5. Rollbacks included
6. Visualize changes
7. Selectively deploy changes to different environments
8. Package code and automate
9. Forecast changes and eliminate failed deployments
10. Enforce rules
11. Work with stored logic

How Liquibase works

We don’t write SQL scripts directly when using Liquibase. We write them in “DatabaseChangeLog” files. These file format can be YAML, JSON or XML. Liquibase read these “DatabaseChangeLog” files and convert them to SQL format and run against the database. Advantage of this is, Imagine we are moving our database from oracle to mysql, We have to modify the database schema to support mysql. But when we are using Liquibase we only have to specify the database type. Liquibase automatically convert the schema to relevant database type.

When executing Liquibase, it will automatically create 2 tables DATABASECHANGELOG and DATABASECHANGELOGLOCK in our schema to manage the access and to log which scripts already run against the database. They manage the version control of the schema.

DATABASECHANGELOG table keeps records of which change sets run against the database with change set ID, Author, file name, data executed, description and MD5SUM value. This MD5SUM value is a unique combination of author, changeset ID and file path. This helps each changeset entry in the database changelog table to detect differences between what is currently in the changelog and what was actually ran against the database.

DATABASECHANGELOGLOCK lock the database for a particular developer when he is doing some schema level changes. Only one developer can modify the DB. This will help multiple developers to work on same codebase without any conflicts.

Liquibase Tags

In these “DatabaseChangeLog” files inside the preconditions tag we can add pre conditions for validation purposes. We can set the database type, username, custom error messages, custom failure messages. Basically, in precondition tag we add Preconditions check before executing change set.

<preConditions onError="HALT" onErrorMessage="Error Message" onFail="HALT" onFailMessage="Fail Message">
    <dbms type="derby"/>
    <runningAs username="liquibase"/>
</preConditions>

We can have properties in “DatabaseChangeLog” file. We can define these properties inside the property tag, we can access them using the ID we provide to the property tag across the “DatabaseChangeLog” file.

<property  name="table.name"  value="tableOne"/>
<changeSet id="firsttable" author="Vinod">
    <createTable tableName="${table.name}">
        <column name="EMP_ID" type="varchar(20)"></column>
        <column name="NAME" type="varchar(20)"></column>
    </createTable>
</changeSet>

Change set is tag where we write our actual schema level changes inside our “Database Changelog” file. Changeset is a logic group in which you can put any real operation. A change set can have operations to create a table, rename a column, add foreign key or any other database operations. Change set must have a ID and a author. When it runs for the first time Liquibase create a record on DATABASECHANGELOG. When in the next run Liquibase check with DATABASECHANGELOG and run only the new changesets. Inside a change set also we can have preconditions for validations.

Imagine you need to add a column to an existing table. Then if you try edit an existing changeset for a particular development, it will give an error because it will validate with the DATABASECHANGELOG. So, the best solution is to create a new “DatabaseChangeLog” file and do the changes. But you can try deleting the existing record in the DATABASECHANGELOG and do the update. But it is not recommended. The best practice is one changeset per modification to make roll back easily. Changes to database can be tagged.

changeSet id="firsttable" author="Vinod">
    <createTable tableName="${table.name}">
        <column name="EMP_ID" type="varchar(20)"></column>
        <column name="NAME" type="varchar(20)"></column>
    </createTable>
</changeSet>

We can have multiple “DatabaseChangeLog” files in a code base with multiple change sets. So it will be hard to manage so much files in a same code base. So, the best practice is to create one master “DatabaseChangeLog” file and manage other “DatabaseChangeLog” files using that. We can achieve this by <include> tag in Liquibase.

<include file="file_path" relativeToChangelogFile="true"/>

Sample code:

I’m using Derby as my database, maven as the build tool and java as the language. First I create a maven project and add these dependencies and plugin to my POM.xml file.

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derbyclient</artifactId>
    <version>10.14.2.0</version>
</dependency>

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>4.3.1</version>
</dependency>
<build>
    <plugins>
        <plugin>
          <groupId>org.liquibase</groupId>
            <artifactId>liquibase-maven-plugin</artifactId>
            <version>4.3.1</version>
            <configuration>           <propertyFile>src/main/resources/liquibase.properties</propertyFile>
            </configuration>
        </plugin>
    </plugins>
</build>

And add a new file liquibase.properties under src/main/resources folder. Add properties of the database to liquibase.properties.

url=jdbc:derby://localhost:1527/sample;create=true
username=liquibase
password=liquibase
driver=org.apache.derby.jdbc.ClientDriver
changeLogFile=src/main/resources/changeLog.xml

Now we going to add our changelog.xml file under src/main/resources folder.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
    <preConditions onError="HALT" onErrorMessage="Error Message" onFail="HALT" onFailMessage="Fail Message">
        <dbms type="derby"/>
        <runningAs username="liquibase"/>
    </preConditions>
    <changeSet id="firsttable" author="Vinod">
        <createTable tableName="Emp">
            <column name="EMP_ID" type="varchar(20)"></column>
            <column name="NAME" type="varchar(20)"></column>
        </createTable>
    </changeSet>
    <include file="path" relativeToChangelogFile="true"/>
</databaseChangeLog>

Now when you run mvn liquibase:update in terminal and it will automatically create native sql queries and execute it on the database. When you query the database you can see EMP table is now create with DATABASECHANGELOG and DATABASECHANGELOGLOCK tables.

Some Important command in liquibase

· mvn liquibase:update — update the schema

· mvn liquibase:dropAll — drop the existing schema

· mvn liquibase:updateSQL — to view the generated SQL queries. You can see them under target > liquibase> migrate.sql

I think you will understand some basics of Liquibase with this article. Thank you. Happy Reading!!!