Database Management With Liquibase

Table of contents

Reading Time: 3 minutes

Introduction

Liquibase helps in managing the database objects without writing and without depending on SQL queries. This brings the concept of one query over multiple tables, a query written using database-neutral languages like XML, JSON, and YAML. Liquibase is a database change management tool. Suppose, in an application, you modify any tables for any requirements and if you want to revert to the old versions, liquibase can be used safely.

How does it work

For every change that is specified in platform-agnostic language and translated to whatever database platform you use. Basically, you keep a running index of changes, and Liquibase uses those changes for you via its execution engine. Since we are using Java, so we need the correct JDBC driver to perform the database update. You’ll need the latest JRE installed. As you know that Liquibase runs from a shell or command line. Now you can run the code manually or from whatever deployment pipeline you use. It traces all the changes by utilizing its own tables in your schema in order to assure consistency and bypass breakdown due to incorrectly altered changelogs. It records a hash of each changeset. In the process of running updates, it puts a “lock” on your database and you can’t accidentally run two changelogs concurrently.

Dependency

We have to add the following Maven dependency for Liquibase.

<dependency>

    <groupId>org.liquibase</groupId>

     <artifactId>liquibase-core</artifactId>

      <version>3.4.1</version>

</dependency>

Changelogs

The source of all Liquibase changes is the changelog file. Liquibase utilizes a changelog to sequentially list all changes made to your database. Think of it as a ledger. Basically, it is a file that has a record of all your database changes (changesets). Liquibase utilizes this changelog record to audit your database and implement any changes that are not yet applied to your database.

<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.5.xsd">

</databaseChangeLog>

Manipulating database schema and tables

Create schema

There is no “Create Schema” tag in Liquibase because it is designed to manage objects within the application’s schema. But, we can also implement a custom SQL statement within a ‘SQL’ tag in order to make a creation of a schema a part of our migration.

<changeSet author="Krishna" id="1234">

    <sql dbms="h2" endDelimiter=";">

        CREATE SCHEMA schema

    </sql>

</changeSet>

Create table

In order to create a table, we have to add the following code inside the database changelog tag:

<changeSet author="Krishna" id="1234">

    <createTable tableName="newTable">
 
        <column name="id" type="int">

        <constraints primaryKey="true"/>

        </column>

        <column name="dept" type="varchar(${dep.size})">

        <constraints nullable="false"/>

        </column>

        <column name="emp_id" type="int">

        <constraints nullable="false"/>

    </createTable>

</changeSet>

Drop table

In order to delete a table in the database, we have to specify the name of the table and the schema. We have to set cascade constraints to true, and also drop all the constraints referring to primary and unique keys in the dropped table. Hence it means that related records in child table/tables will be deleted. If there is a referential integrity constraint but we don’t set the cascade constraints to true, the database will return an error and won’t drop the table.

<changeSet author="Krishna" id="1234">

    <dropTable tableName="newTable" schemaName="public" cascadeConstraints="true"/>

</changeSet>

Manipulating the data in the database

Update

In order to update the values in the table we have to specify the name of the column, and the new value and provide the ‘where’ condition to find the location of the value we like to change.

<update tableName="newTable" schemaName="public">

    <column name="newColumn" value="1337"/>

    <where>newColumn='11'</where>

</update>

Insert

When we like to insert new values in the table, we have to set the name of the column and the value.

<insert schemaName=”public" tableName="newTable">

    <column name="newColumn" value="newValue"/>
 
</insert>

Delete

To delete the data from the table we have to operate a ‘where’ tag to determine which data we’d like to delete. In the below code, we have deleted all the rows that have the value ‘deleteMe’ in the column ‘columnName’.

<changeSet author="Krishna" id="1234">

    <delete tableName="newTable" schemaName="public">

        <where>columnName='deleteMe'</where>

    </delete>

</changeSet>