Liquibase Tutorial

Introduction:

Sometimes we need to create a database and put fake data inside it, when running the application, we can do it manually using SQL command, but this way is very traditional, so we will use Liquibase Technology to do it.

What is Liquibase in?general?

Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes.

Liquibase enables you to go faster and automate your processes by tracking, versioning and deploying database changes.

How does it?work?

After running your application Liquibase will create two tables inside your Database, the first one called DataBaseChangeLog & DataBaseChangeLogLock each of these tables has its own work, and Liquibase uses it to handle its work.

• DataBaseChangeLog?: This table will be tracking ChangeSet and whos author of this file and what is the Id of this file, and where is the location of the file.

The following image represents this table with information about ChangeSet.

• DataBaseChangeLogLock?: Which will ensure that only one instance of Liquibase is running at a time so that no conflict will happen during the migration.

The following image represents this table with information about the table.

How to set it up?

• we need to add the following dependency inside the POM.xml file

I use version 4.16.1 with Liquibase which is the latest version.

<!-- https://mvnrepository.com/artifact/org.liquibase/liquibase-core -->
<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>{{liquibase.verion}}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.liquibase/liquibase-maven-plugin -->
<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <version>{{liquibase.verion}}</version>
</dependency>        

• we need to add the following plugin inside the POM.xml file

<plugin>
   <groupId>org.liquibase</groupId>
   <artifactId>liquibase-maven-plugin</artifactId>   
   <version>${liquibase.version}</version>
   <configuration>    
      <propertyFile>${liquibase.propertyFile}</propertyFile>
      <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>    
   </configuration>
</plugin>        

${liquibase.propertyFile}: We will replace it with liquibase properties file path like the following image.

• We need to fill data inside liquibase.properties the common path to define this folder inside the resource file in your project.

In this file, we will define information about the database.

databaseUrl, username, password, and changeLog file location, and now let's fill the data

url=${your urlPath}
username=$${your username}
password=${your password}
driver=${your data base driver} eg: org.postgresql.Driver
changeLogFiles= ${your change log file path} cahngelog_master path        

ChangeLog Vs ChangeSet In liquibase

To create a changeLog and changeSet we need to know what is the meaning of these files.

• changeLog

The change log is a YAML file, we write inside it, how we want to run the liquibase environment.and how we want to order our SQL files at run time

cahnageLog.yaml structures

databaseChangeLog:
  - include:
      file: src/main/resources/db/changelog/0001/changelog.yaml        

• ?changeSet

?The chnageLog files will run cahngeSet file, the changeSet can be a SQL XML JSON, and run the query using changeLog file.

cahngeSet structures:

--liquibase formatted sql
--changeset author:id

CREATE TABLE ...

INSERT QUERY ... 

etc ....

-- rollback DROP TABLE ...;        

Like the above structures

• In the first line, we need to define the syntax for the changeset file.
• In the second line, we need to define who is the author.
• After that, we will write syntax like the above example.
• Finally, we will write a rollback query.

How we can use the changelog and changeSet files?together?

In the first step and to organize our application I will create a folder in the resource folder called DB.

Now we need to create a file called a changelog-master.yaml

• changelog-master.yaml?: It is a master cahngeLog file. the content in it, all of the logs operators sequentially running. using it we can run changeLog or changeset file.

for example changelog-master.yaml:

databaseChangeLog:
  - include:
      file: src/main/resources/db/changelog/0001/changelog.yaml        

And now we will create another package called 0001, and we will create files inside it.

• the first file called cahngelog.yaml:

It is a cahgneLog file for the 0001 package and it is a master for the 0001 package.

It will call SQL files using this in sequentially.

databaseChangeLog:
  - include:
      file: src/main/resources/db/changelog/0001/createUserTable.sql
  - include:
      file: src/main/resources/db/changelog/0001/insertUsers.sql        

• The second file called createUserTable.Sql?

--liquibase formatted sql
--changeset abed:0001-01
CREATE TABLE IF NOT EXISTS user_tb (
id integer PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(255),
last_name VARCHAR(255),
mobile_number VARCHAR(255)
);
-- rollback DROP TABLE user_tb;        

• The third file called insertUsers.sql

--liquibase formatted sql
--changeset abed:0001-02

INSERT INTO user_tb
(
 first_name,
 last_name,
 mobile_number
)
VALUES
    ('USER_FIRST_A','USER_LAST_A','99625541'),
    ('USER_FIRST_B','USER_LAST_B','99625511'),
    ('USER_FIRST_C','USER_LAST_C','99625555');

-- rollback DELETE FROM product WHERE first_name IN ('USER_FIRST_A', 'USER_FIRST_B', 'USER_FIRST_C');        

And we need to update application.yml and add the following command to allow liquibase when running your application.

spring:
  liquibase:
    enabled: true
    drop-first: true
    change-log: classpath:db/changelog/changelog-master.yml
    default-schema: public        

You can find the code inside this repository and if running this application, you can go to the H2 console using the following link https://localhost:8085/h2-console the username & password is admin, and check the table called USER_TB using the following query

SELECT * FROM USER_TB        

and the result must be similar like the following image.

Organizing changesets by?feature

The way I usually organize the changesets is by creating a folder for each new feature, with the folder being identified by a feature id, that way each feature will have its own changelog file.

conclusion:

Sometimes we need to initial test data or some column inside the database, for saving your time you can use liquibase to do it, after preparing your changeLog and changeSet.

references?:

• https://docs.liquibase.com/concepts/introduction-to-liquibase.html
• https://eliedhr.medium.com/evolving-your-database-in-a-spring-boot-application-with-liquibase-709aad8336c8
• https://en.wikipedia.org/wiki/Liquibase