Mark Joseph Remedio

Software Engineer

With over 6 years of experience in Software Engineering who likes to take part in interesting projects.

Posts

Liquibase - Open Source Database Version Control

02 November 2021

Liquibase - Open Source Database Version Control

Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes. Developers can easily deploy and roll back changes for specific versions without needing to know what has already been deployed. You don’t have to know a specific programming language to be able to use liquibase since it supports XML,SQL,YAML and JSON format. The only prerequisite would be a running java on the machine.

2. Getting Started

2.1. Installation

  1. Install java on your local or where you will be running the liquibase
  2. Download the installer base on your os
  3. Install the liquibase

2.2. Create the project

Create project folder where the liquibase migration files and liquibase config will reside

A good practice is to put all you migration files in a subfolder and just include the migrations in the changeLogFile. Since the liquibase.properties only accept a single file as a changeLogFile.

Sample of the project structure

project_folder
    - migration
    - parameters
    mysql-connector.jar
    liquibase.properties
    changelog-master.yaml
  1. create the changelog-master.yaml
    databaseChangeLog:
      - includeAll:
       path: migration/
    
  2. Download the database connector in maven repository. In this example since we are using mysql as the database search for mysql-connector and choose the version and download the jar

  3. Move the downloaded jar to your project directory and update the liquibase.properties classpath accordingly.

  4. Configure liquibase. Create liquibase.properties text file

         driver: com.mysql.cj.jdbc.Driver
         url: <db url connection>
         username: <user>
         password: <password>
         changeLogFile: changelog-master.yaml
         classpath: mysql-connector-java-8.0.21/mysql-connector-java-8.0.21.jar
    

2.3 Take a snapshot of your existing database

Execute : liquibase --changeLogFile=mydatabase_changelog.xml generateChangeLog

This may take a while depending on the existing data.

Sample generated file

<?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"  
  xmlns:pro="http://www.liquibase.org/xml/ns/pro"  
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd
      http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.5.xsd">  
    <changeSet  author="lb-generated"  id="1185214997195-1">  
        <createTable  name="BONUS">  
            <column  name="NAME"  type="VARCHAR2(15)"/>  
            <column  name="JOB"  type="VARCHAR2(255)"/>  
            <column  name="SAL"  type="NUMBER(255)"/>
        </createTable>  
    </changeSet>
    <changeSet  author="lb-generated"  id="1185214997195-2">  
        <createTable  name="DEPT">  
            <column  name="DEPTNO"  type="INTEGER"/>  
            <column  name="DNAME"  type="VARCHAR2(15)"/>  
            <column  name="LOC"  type="VARCHAR2(255)"/>  
        </createTable>  
    </changeSet>
    <changeSet  author="lb-generated"  id="1185214997195-3">
         <createView fullDefinition="false" viewName="myView2">SELECT "DEPT".DEPTNO,
    "DEPT".DNAME
   FROM "DEPT";</createView>
    </changeSet>
    <changeSet  author="lb-generated"  id="1185214997195-4">  
        <pro:createFunction functionName="myFunction" path="objects/function/myFunction.sql" relativeToChangelogFile="true"/>  
    </changeSet>
</databaseChangeLog>

2.4 Create the migration file

Choose from JSON,YAML,XML and SQL format and create the file in the migration folder

Create the changelog file under the migration folder. You can read more about the format here

Sample in yaml:

databaseChangeLog:
  - changeSet:
      id: modify_table_datatype
      author: mark.remedio@fullspeedtechnologies.com
      validCheckSum: ANY
      comment: Modify datatype of the tabe field
      changes:
        - tagDatabase:
            tag: modify-column-datatype-in-table
        - modifyDataType:
            columnName: column_name
            newDataType: varchar(255)
            schemaName: db_name
            tableName: table_name
      rollback:
        - modifyDataType:
            columnName: column_name
            newDataType: varchar(50)
            schemaName: db_name
            tableName: table_name

2.4 Executing the Update

liquibase update

2.5 Executing the rollback

liquibase rollbackCount 1

3. Summary

Liquibase is an independent database version control where knowledge in a programming language is unnecessary. You can also create migration from your existing database automatically. More info, syntax and features of liquibase can be found here

Read More