Liquibase - Introduction

What is Liquibase

<<TODO>>

Why Liquibase

<<TODO>>

Liquibase Setup

1] To experiment with liquibase, you need a db instance running. Configure it using AWS - (click here for reference link)

2] Download Liquibase and add to PATH 

export PATH=$PATH:/path/to/extracted/liquibase/dir

3] Create a directory to contain liquibase scripts.

Add liquibase.properties file.

driver=org.postgresql.Driver
classpath=/home/sibtain/work/liquibase/postgresql-9.4.1212.jre6.jar 
url=jdbc:postgresql://lqbase.cfm21jixd6ei.us-east-1.rds.amazonaws.com:5432/my_db
username=postgres_user
password=postgres_pswd
# default changelog to use, relative to classpath
changeLogFile=release_1.changelog.xml

Add release_1.changelog.xml

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

 <changeset author="lqbase_sibtain" id="1">
        <createtable tablename="subject">
            <column name="id" type="int">
                <constraints nullable="false" primarykey="true">
            </constraints></column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false">
            </constraints></column>
            <column defaultvalueboolean="true" name="active" type="boolean">
        </column></createtable>
    </changeset>

</databasechangelog>

In case you have not created aws db instance, then you may need to download postgresql driver from here.

4] Execute Command

$ liquibase migrate

5] Check your database to find subject table created.

                List of relations
 Schema |         Name          | Type  |  Owner 
--------+-----------------------+-------+---------
 public | databasechangelog     | table | sibtain
 public | databasechangeloglock | table | sibtain
 public | subject               | table | sibtain

Examples

Create Employee table and add few records to it.

main.changelog.xml

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

    <include file="create_table_employee.changelog.xml" 
        relativeToChangelogFile="true"/>
    <include file="insert_data_employee.changelog.xml" 
        relativeToChangelogFile="true"/>
</databaseChangeLog>

create_table_employee.changelog.xml

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

    <changeSet id="create_employee_table" author="sibtain">
        <createTable tableName="Employee">
            <column name="EmployeeId" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="FirstName" type="VARCHAR(50)">
                <constraints nullable="true"/>
            </column>
            <column name="LastName" type="varchar(50)">
                <constraints nullable="true"/>
            </column>
            <column name="Phone" type="varchar(50)">
                <constraints nullable="true"/>
            </column>
            <column name="JoiningDate" type="Date">
                <constraints nullable="true"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet id="add_pk_for_employee" author="sibtain">
    <addPrimaryKey columnNames="EmployeeId"
                  constraintName="pk_employee"
                  tableName="Employee" />
    </changeSet>
</databaseChangeLog>

insert_data_employee.changelog.xml

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


<changeSet id="add_employee_data" author="sibtain">
    <insert tableName="Employee">
        <column name="EmployeeId" type="BIGINT" valueNumeric="101"/>
        <column name="FirstName">John</column>
        <column name="LastName">Legault</column>
        <column name="Phone">+91 12398283</column>
        <column name="JoiningDate" valueDate="2014-03-10"/>
    </insert>
    <insert tableName="Employee">
        <column name="EmployeeId" type="BIGINT" valueNumeric="102"/>
        <column name="FirstName">Pete</column>
        <column name="LastName">Coyle</column>
        <column name="Phone">+44 12398283</column>
        <column name="JoiningDate" valueDate="2015-12-21"/>
    </insert>
    <insert tableName="Employee">
        <column name="EmployeeId" type="BIGINT" valueNumeric="103"/>
        <column name="FirstName">Brian</column>
        <column name="LastName">Apostle</column>
        <column name="Phone">+20 12398283</column>
        <column name="JoiningDate" valueDate="2016-09-20"/>
    </insert>
    <rollback>
        <sql>
            delete from Employee
            where EmployeeId in (101, 102, 103)
        </sql>
    </rollback>
</changeSet>
</databaseChangeLog>

After doing liquibase migrate, perform select to view results in database.

my_db=> SELECT * FROM "Employee";
 EmployeeId | FirstName | LastName |    Phone     | JoiningDate
------------+-----------+----------+--------------+-------------
        101 | John      | Legault  | +91 12398283 | 2014-03-10
        102 | Pete      | Coyle    | +44 12398283 | 2015-12-21
        103 | Brian     | Apostle  | +20 12398283 | 2016-09-20
(3 rows)