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>

References:

No comments:

Post a Comment