Sunday, February 25, 2018

Liquibase Tutorial

Photo credit: Pixabay.com


Please follow Rest API Server to see the big picture and GitHub repo details.

Liquibase helps in database schema management. The schema can be defined in XML, YAML, JSON and SQL format. It supports multiple databases. Also, the evolution of the database can be managed through liquibase using the concept of changesets.

In build.gradle add

  compile('org.liquibase:liquibase-core')

In application.properties add

spring.liquibase.change-log=classpath:db/liquibase-changelog.xml

In the directory as mentioned in property (spring.liquibase.change-log) add the following lines

<?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"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
         
<include file="changeLog/0000_initial.xml" relativeToChangelogFile="true" />


</databaseChangeLog>


Now create a file changeLog/0000_initial.xml relative to liquibase-changelog.xml

<?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"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

<changeSet id="0000" author="lalit.bhatt">

<createSequence sequenceName="users_seq" cycle="true"
incrementBy="1" minValue="1" schemaName="public" startValue="1" />

<createTable tableName="users" remarks="User table">
<column name="id" type="bigint" defaultValueComputed="nextval('users_seq')">
<constraints nullable="false" unique="true" primaryKey="true" />
</column>
<column name="first_name" type="varchar(50)">
<constraints nullable="false" />
</column>
<column name="last_name" type="varchar(50)">
<constraints nullable="false" />
</column>
<column name="email" type="varchar(100)">
<constraints nullable="false" unique="true" />
</column>
<column name="password" type="varchar(100)">
<constraints nullable="false" />
</column>
<column name="role" type="varchar(10)">
<constraints nullable="false" />
</column>
</createTable>

<createSequence sequenceName="groups_seq" cycle="true"
incrementBy="1" minValue="1" schemaName="public" startValue="1" />

<createTable tableName="groups" remarks="Group table">
<column name="id" type="bigint" defaultValueComputed="nextval('groups_seq')">
<constraints nullable="false" unique="true" primaryKey="true" />
</column>
<column name="name" type="varchar(100)">
<constraints nullable="false" unique="true" />
</column>
</createTable>

<createSequence sequenceName="group_user_seq" cycle="true"
                incrementBy="1" minValue="1" schemaName="public" startValue="1" />

<createTable tableName="group_user" remarks="Group User relationship table">
<column name="id" type="bigint" 
                                         defaultValueComputed="nextval('group_user_seq')">
<constraints nullable="false" unique="true" primaryKey="true" />
</column>
<column name="group_id" type="bigint">
           <constraints nullable="false" foreignKeyName="fk_group_user__groups" 
                                         references="groups(id)"/>
</column>
<column name="user_id" type="bigint">
           <constraints nullable="false" foreignKeyName="fk_group_user__users" 
                                          references="users(id)"/>
</column>
</createTable>

</changeSet>

<changeSet id="0001" author="lalit.bhatt">

<insert tableName="users ">
<column name="first_name" value="Lalit" />
<column name="last_name" value="Bhatt" />
<column name="email" value="admin@admin.admin.khalibali" />
<column name="password" 
                  value="$2a$10$878AEpyzAIo4VNmMvAywjeY2JbkNyT.m4XE9WDVSnNX9FVRpFlKwq" />
<column name="role" value="ADMIN" />
</insert>

</changeSet>

</databaseChangeLog>

2 comments:

  1. Hello Lalit,

    Is there a way to create a db log file which similar to what a spool functionality does in sql?

    For Eg: If my changeset has 2 tables being created and 10 records being inserted in each table and a trigger being comiled.

    Is it possible to get a log which clearly mentions.

    Table A created.
    Table B created.
    10 rows inserted.
    Trigger 1 compiled.

    Thank you!
    Mrinal.

    ReplyDelete
    Replies
    1. Mrinal,

      Check the description field of databasechangelog table in the db. See if that helps.

      Delete