How to automate database deployment with Liquibase?
The translation of the material was prepared as part of the course Migration Management Express Course (DBVC).
Liquibase Is a database change management tool. With it, you can track changes in the database made with SQL (or XML) scripts. These scripts can be added to version control systems like git.
In this article, we will look at how to automate database deployments using Liquibase. Automation can be done with
1. Jenkins pipeline
2. Shell scripts
I will not talk about either of these two methods, since this is not the topic of this article – just to keep everything simple and clear.
There are two ways to write changesets to Liquibase, i.e. XML and SQL. I chose SQL to avoid writing the appropriate XML changes for every SQL update we make. So the same SQL file we write for development is used to deploy Liquibase.
Note that I have only tested these steps for the Postgre database, however the same steps can be repeated for any database. Also make sure on your computer configured by liquibase …
Follow the steps below:
Create changelog file
Create XML file named liquibase-changelog.xml (name can be anything!) with the following content:
<?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="<path to changeset SQL file>/<changeset file name>.sql>" relativeToChangelogFile="true"/> ...more <include> tags goes here... </databaseChangeLog>
Pay attention to the tag
include in the above XML. Every SQL changeset file that is to be tracked by Liquibase must be logged to this changelog file.
Creating SQL changesets
Add the SQL changeset files to the location of your choice. Syntax SQLwhich works with Liquibase is the following:
--liquibase formatted sql --changeset <author name>:<a unique identifier for the SQL changeset> <SQL statements go here> <SQL statements go here> --rollback <rollback SQL statements> --rollback <rollback SQL statements>
Let’s consider an example:
--liquibase formatted sql --changeset xameeramir:create-test-table CREATE TABLE IF NOT EXISTS testTable( columnName1 VARCHAR (355) ); --rollback DROP TABLE --rollback testTable
Note that the SQL changeset file is different from the XML changelog file.
Logging SQL changeset in changelog XML
Include the SQL changeset file in the changelog file we created earlier with the following XML tags:
<include file=”<path to SQL changeset file>/<changeset file name>.sql” relativeToChangelogFile="true" />
Add as many SQL changesets and log them in the changelog file as you need.
Trigger in Liquibase to update the database
Just run the below command:
liquibase --changeLogFile=<path to changelog file>/<liquibase changelog file name>.xml --username=<database username> --password=<database password> --classpath=<path to the liquibase installation>/postgresql-42.2.5.jar --url=jdbc:postgresql://<database url>/<database name> update
Classpath (classpath) is the JDBC driver we configured in previous publication… Postgresql-42.2.5.jar is a JDBC driver designed for Postgresand it can be replaced with the database of your choice without any special transformations in these steps.
The above command can be used in shell scripts or in the CI / CD pipeline to trigger database updates.
CI / CD Automation
After the above configuration is installed, the automation can be performed either on the client using shell scripts, or on the server using shell scripts or CI / CD implementation.
Suppose a CI / CD implementation that triggers a Liquibase deployment is means executing the Liquibase trigger command above, on every git push to the DEVELOP branch (or whatever).
The first prerequisite is the liquibase-changelog.xml file. Let’s say we store it at the ~ / level with include statements pointing to the folder where the SQL changeset is located. The following workflow will automate database deployment using the CI / CD pipeline:
Place the SQL changeset file in the function repository.
Submit a patch request for the DEVELOP branch
After validation and approval, merge the feature branch into the DEVELOP branch.
The CI / CD implementation configured on the DEVELOP server will launch Liquibase to update the database.
Liquibase will automatically only execute new files (any already executed files will not be rerun).
Automation with shell scripts
In shell scripts, the same Liquibase trigger command will be written. As soon as the shell scripts are executed, the liquibase containing their changesets will be executed automatically.
You may be wondering how shell scripts know when to execute a command? The answer is simple:
Shell scripts can be executed on cron triggers.
Shell scripts can be executed on some system events.
The choice is yours!
Learn more about Migration Management Express Course (DBVC)