Liquibase Integration in Mulesoft Application

In this article, we will learn how we can integrate liquibase in MuleSoft application

Liquibase Introduction

Liquibase is a database schema change management solution that enables you to revise and release database changes faster and safer from development to production.

To keep it simple, you can write migrations in SQL — a database-specific syntax supported for PL/SQL. To take advantage of abstraction abilities, use a database-agnostic way by specifying your changes in XML, JSON, or YAML.

Liquibase uses SQL, XML, JSON, and YAML changelog files to list database changes in sequential order. Database changes have the format of changesets. Changesets contain Change Type, which is a type of operations to apply to the database, such as adding a column or primary key. Context, label, and precondition changelog tags help precisely control when a database change is made and to which database environment it is deployed.

To set the connection between Liquibase with your database, you need the database connection information and parameters. Liquibase comes with a configuration file called liquibase.properties to store your database connection information and parameters that do not frequently change. Setting the parameters as environment variables to handle sensitive database information or running them at the command prompt is an alternative option.

Liquibase runs 6 basic types of commands: update, rollback, snapshot, diff, status, and utility commands. When you use the update command to deploy your first changes, Liquibase checks the database connection information, including credentials, database URL, and JDBC driver.

When you deploy your changes, Liquibase creates two tables in your database: DATABASECHANGELOG and DATABASECHANGELOGLOCK.

The DATABASECHANGELOG table tracks deployed changes so that you have a record. Liquibase compares the changesets in the changelog file with the DATABASECHANGELOG tracking table and deploys only new changesets.

DATABASECHANGELOGLOCK prevents multiple instances of Liquibase from updating the database at the same time. The table manages access to the DATABASECHANGELOG table during deployment and ensures only one instance of Liquibase is updating the database.

Setting up a Mule Project with liquibase with PostgreSQL database

1. Create a Mule Project with name mulesoft-liquibase-integration
2. Create a new folder liquibase under src/main/resources. Create a subfolder changelog under liquibase folder. Create a new subfolder script under the changelog folder.
3. Create a new plain-text file named dbchangelog.xml in the changelog directory. This file will be your changelog, a file that will keep track of all the changes you make to your database structure.
4. Open the dbchangelog.xml file and update it with the following text. This is an empty changelog file.

<?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">
</databaseChangeLog>

5. Create another plain text file in the liquibase directory, named liquibase.properties

6. Edit the liquibase.properties file to add the following properties:

changeLogFile: src/main/resources/liquibase/changelog/dbchangelog.xml
url: jdbc:postgresql://localhost:5432/postgres
username: username
password: password
contexts: dev

7. Add a changeset Script to the changelog. This changeset will have one change in it, to create a table named “accounts”. Create a SQL file under liquibase/changelog/scripts and rename it as 01_create_accounts_table.sql

--liquibase formatted sql
--changeset shyam:1
CREATE TABLE accounts (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
password VARCHAR ( 50 ) NOT NULL,
email VARCHAR ( 255 ) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);

8. Add a changeset script to the changelog. In the dbchangelog.xml file, add a new changeset.

<?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="scripts/01_create_accounts_table.sql" relativeToChangelogFile="true"/>
</databaseChangeLog>

9. Edit the pom.xml file and update it to have the following contents in plugins:

<plugin>
   <groupId>org.liquibase</groupId>
   <artifactId>liquibase-maven-plugin</artifactId>
   <version>4.5.0</version>
<configuration>
<propertyFile>src/main/resources/liquibase/liquibase.properties</propertyFile>
</configuration>
<dependencies>
  <dependency>
    <groupId>org.liquibase.ext</groupId>
    <artifactId>liquibase-postgresql</artifactId>
    <version>4.5.0</version>
  </dependency>
</dependencies>
</plugin>

10. Open the command prompt and navigate to the mulesof-liquibase-integration directory.

11. Run the following command: mvn liquibase:update

12. From a database UI Tool, for example: “pgAdmin” check your database changes under “postgres”. You should see a new “accounts” table added to the database.

13. Also, you should see two more tables:

• DATABASECHANGELOG — This table keeps a record of all the changesets that have been deployed. The next time you run the update command, the changesets in the changelog will be compared with the DATABASECHANGELOG tracking table, and only the new changesets not found in the DATABASECHANGELOG will be deployed. You will notice that a new row was created in that table with the changeset information you have just deployed.
• DATABASECHANGELOGLOCK — This table is used internally by Liquibase to manage access to the changelog table during deployment.

14. Let’s create a new http endpoint to get the accounts details and it should return empty records.

15. Let’s create another database change log for adding a record in the accounts table with liquibase script. Create a new file 02_insert_records_accounts.sql in the script folder and add the below script.

INSERT INTO public.accounts
(username, "password", email,created_on, last_login)
VALUES('mule', 'max', 'mule@liquibase.com', now(), now());

16. Let’s add the above script in dbchangelog.xml file also.

17. Now run the maven liquibase script again and validate these two entries in databasechangelog table.

mvn liquibase:update

We will see below screen after successful execution.

18. Run below script to validate the execution of script files in databasechangelog.

19. Run the MuleSoft application and hit the get account endpoints to verify the inserted recorded by liquibase.

20. We can integrate this mvn liquibase update as part of our CI/CD to run the database script before our build and deployment of mule application.

In the next article, I will cover how we can integrate liquibase as part of mulesoft application startup. Please refer:

https://shyamrajprasad.medium.com/liquibase-integration-in-mulesoft-application-with-spring-dependency-f82739a4a99d

GitHub Repo:

https://github.com/shyamrajprasad/mulesoft-liquibase-integration/

References:

Setting up a Liquibase Project with Maven and PostgreSQL | Liquibase Docs

Happy Learning!