Liquibase

Liquibase - ChangeLogs - XML vs Sql vs ..

Liquibase supports changeset logfiles in several formats: SQL, XML, YAML, JSON

A changeset is script containing a series of commands to update a database.

Generally you have a changeset per changetype.

A changetype is a type of change you want to make to the database. common changetypes include

  • Table - used to create a table
  • Column - used to add columns to a table
  • Index - used to add indexes
  • View - used to add a database view
  • Procedure - used to add procedures

Actually there are 10 types of change types documented here: https://docs.liquibase.com/change-types/home.html This page gives you a link to the commands

Liquibase recomments you to use XML files. However it will use the other types of files as well (SQL, XML, YAML, JSON)

SQL Sample

--liquibase formatted sql

--changeset liquibase-docs:addColumn-example
ALTER TABLE cat.person ADD address VARCHAR(255) NULL,
ADD name VARCHAR(50) NOT NULL;

XML Sample

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    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-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet author="liquibase-docs" id="addColumn-example">
        <addColumn catalogName="cat"
            schemaName= "public"
            tableName="person">
            <column name="address"
                type="varchar(255)"/>
            <column name="name"
                type="varchar(50)">
                <constraints nullable="false" />
            </column>
        </addColumn>
    </changeSet>

</databaseChangeLog>

SQL Pro’s

Most people understand SQL scripts

SQL Con’s

The SQL Script has to be coded to the specific type of database it is intended for. For example if you create a MSSQL Server script, then it will only work on the SQL Server.

Youi need to add your own rollback scripts

XML Pro’s

  • The XML structure is a database independant format. Consequently (in theory) you can post the same script to both a SQL Server and an Informix database.

  • The XML structure can be rolled back without adding additional logic.

  • SQL Server scripts requires Code:ID to be used as a signature, XML does not need the signature.

  • Substitution variables can be added to the XML file itself. Click here for more information: https://docs.liquibase.com/concepts/changelogs/property-substitution.html