• Monstar Lab PH

Make Your Persistence-Layer Production-Ready with Liquibase

Updated: Feb 13, 2019


Software developers working on different systems or applications usually start small or from scratch. During the development they focus on the requirements and making things work (functional), but once the project reaches maturity, that is when things tend get challenging.


There are so many production related problems that are not visible during the development but we are here to focus on "Persistence" related problems.



Issue#1: Multiple Environments


Assuming you have 1 project, and for this example, we can call your project: Project Cement.

Project Cement during production phase if the team will be following the "SDLC V-Model - Where each phase corresponds to a testing phase" the project will be needing the following environments

Local environment

  • Team environment

  • User-Acceptance Test (UAT) environment

  • Production environment

Now we have 4 environments, and this is just the usual setup, there's a possibility to require more depending on the situation.


But wait there's more, our problem doesn't end there. For each environment, there's a possibility of having multiple databases and multiple schemas.


maintenance = no. of environments * no of databases * no. of schemas


which simply means, it is hard to maintain.


Observance:

Developer 1 was assigned to create a feature that requires a schema change, he/she finished the task, tested his changes on his local environment and everything was working fine. He applied the new feature to the team environment where the team QA will perform extensive testing.


Developer 1 being agile and eager, wants to utilize his working hours and started on a new task.

After a while, the team QA announced that the feature is now ready for UAT environment.


Developer 1 working on a new task, was too busy, and applied/pushed the code changes to UAT environment, but forgot the schema changes, therefore the feature he created failed miserably on UAT environment, now imagine that happening on production environment where there's higher risk.


Moral of the story: Multiple changes are hard to maintain and prone to error.


Solution: Package the schema change with the project codes with Liquibase because it is a tool that can manage database schema by having the versioning feature which makes schema, easier to refactor/change, rollback in case of a problem and deploys on multiple environments.



Issue #2: Migration to another RDBMS (Relational Database Management System)


Observance:

Project Cement was already in production, and the client has decided that instead of using open-source solution, they want to use a proprietary (paid) product this means that Project Cement's persistence layer will be migrated from a free one MySQL Database to Oracle Database.


With the given deadline, Developer 1 escalated that it will not be feasible, and will require a lot more work to migrate. The client was persistent, and still pushed the migration, Developer 1 was left with a tremendous workload to migrate everything while asking himself "Why??".


Solution: Developer 1's misery should have been avoided, if the team used Liquibase, because it is a tool with the ability to adjust to multiple types of RDBMS, where it has the attribute of liquid (which comes from its name) that it can adjust/take shape of whatever RDBMS the project requires.



Issue #3: Creating a fresh database


Observance:

Having an existing system, with multiple environments, developer 1 was asked to create a new environment, the problem is that all the existing environment has existing data so developer 1 only have 2 options.

  • Option 1: Create a SQL dump that contains only the schema

  • Option 2. Create a SQL dump that contains everything and delete the unnecessary data.

Both options will consume more time than expected. Option 1, will require the initial data for system initialisation such as Admin account and application config/whitelist/secrets, while Option 2, contains everything but deleting the unnecessary data is prone to error and re-work.


Solution: Use Liquibase, because it's a tool with the ability to do, versioning, for both schema and data change, simply run it and everything you need will be there.



How to integrate Liquibase with your project (This will be the standalone Liquibase flavor, as it can easily be added to your CI/CD automation pipeline, for alternative you can also check the maven implementation)


Step 1: Create the following folders inside your repository that contains the XML Liquibase script.


Step 2: Inside the config folder

You can have multiple environment config and credentials.


Set accordingly the database credentials <username> , <password> , <url/schema>



Step 3: Under drivers folder Add drivers of the databases of your choice. (Note this will be used on config under driver and classpath properties.)


Step 4: Under db-updates folder, create the following files. changeset-master.xml contains the execution included script files to be executed by liquibase <?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"

xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"

xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd

http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

<include file="../db-updates/changeset-update-01-create-tables.xml"/>

<include file="../db-updates/changeset-update-02-default-data.xml"/>

</databaseChangeLog>

changeset-update-01-create-tables.xml contains the scripts for creating tables.

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

xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"

xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd

http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

<changeSet id="1" author="morjo">

<createTable tableName="service">

<column name="id" type="bigint" autoIncrement="true">

<constraints primaryKey="true" nullable="false"/>

</column>

<column name="code" type="varchar(45)" >

<constraints nullable="false" unique="true"

uniqueConstraintName="service_code_uk" />

</column>

<column name="name" type="varchar(45)" >

<constraints nullable="false" unique="true"

uniqueConstraintName="service_name_uk" />

</column>

<column name="description" type="varchar(191)" />

</createTable>

<rollback>

<dropTable tableName="service"/>

</rollback>

</changeSet>

</databaseChangeLog>

Changeset-update-02-default-data.xml contains the script for creating default data such as admin account / initial configuration

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

xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"

xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd

http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

<changeSet id="2" author="morjo">

<preConditions onFail="MARK_RAN">

<sqlCheck expectedResult="0">SELECT COUNT(*) FROM service WHERE code = 'default';</sqlCheck>

</preConditions>

<insert tableName="service">

<column name="code" value="default" />

<column name="name" value="Default Service" />

<column name="description" value="This is a default service to demo liquibase" />

</insert>

<rollback>

<delete

tableName="service">

<where>code = 'default'</where>

</delete>

</rollback>

</changeSet>

</databaseChangeLog>


Step 5: Under executable folder extract liquibase-<version>-bin.zip that can be downloaded on their website (https://download.liquibase.org/download/?frm=n)


Step 6: Tag (snapshot current version) the database before running Liquibase so that you can perform a rollback. By executing while on Liquibase/executable folder java -jar liquibase.jar --defaultsFile=../config/localhost.properties tag freshdb Step 7: Execute update that will create tables and default data

java -jar liquibase.jar --defaultsFile=../config/localhost.properties update Step 8: (Optional) When there’s a problem, perform a rollback. java -jar liquibase.jar --defaultsFile=../config/uat.properties rollback freshdb Notes: - You can create multiple Liquibase scripts just add it to the changeset-master.xml

- Take note of the “id” attribute because Liquibase will run it in sequence

- when you mess up, it’s easier to re-create schema and run Liquibase.



Contributor:

John Morales

Sr. Java Developer


Here at Monstar Lab Philippines, we encourage our engineers, developers, and practically all team members to share technicals skills, development hacks, and industry knowledge to the entire family.


We conduct regular Tech Talk Tuesdays (or Thursdays) more popularly known as T3 Sessions.

This helps us keep our minds sharp and always up to date with the latest in software development, mobile development, and emerging technology.


You can count on the expertise of our team to design and deliver top-notch enterprise software solutions. Feel free to send us a message anytime and we’ll help you out.