In the Accelerate State of DevOps 2021 Report, the DevOps Research and Assessment (DORA) team reveals “elite DevOps performers are 3.4 times more likely to exercise database change management compared to their low-performing counterparts.” Tracking changes with version control is not just for application code, though. It’s crucial for managing changes for one of your most important assets: your database.
The GitLab DevOps platform enables database management teams to leverage CI/CD to track, manage, and deploy database changes, along with application development and automation and infrastructure as code. Database change management tools have become more advanced in recent years, supporting easier collaboration and communication, which are the keys to successful DevOps. In this blog post, I’ll take you through a tutorial using Liquibase, a tool that integrates seamlessly into the GitLab DevOps platform so your teams can deliver database code changes as fast as application code changes (without compromising on quality and security).
What is Liquibase?
Liquibase was founded as an open source project over 15 years ago to address getting database changes into version control. With more than 75 million downloads, the company behind Liquibase expanded to paid editions and support to help teams release software faster and safer by bringing the database change process into their existing CI/CD automation.
Integrating Liquibase with GitLab CI/CD enables database teams to leverage DevOps automation and best practices for database management. Liquibase helps teams build automated database scripts and gain insights into when, where, and how database changes are deployed. In this tutorial, we’ll demonstrate how to check database scripts for security and compliance issues, speed up database code reviews, perform easy rollbacks, and provide database snapshots to check for malware.
Adding Liquibase to GitLab’s DevOps Platform
Teams can add Liquibase to GitLab to enable true CI/CD for the database. It’s easy to integrate Liquibase into your GitLab CI/CD pipeline. Before jumping into the tutorial, let’s take a look at the example Liquibase GitLab project repository you’ll be using.
Understanding the example Liquibase GitLab project repository
For this example, the GitLab CI/CD pipeline environments include DEV, QA, and PROD. This pipeline goes through several stages: build, test, deploy, and compare. A post stage comes into play later to capture a snapshot of your database in Production.
Stages:
- build
- test
- deploy
- compare
Liquibase commands in the pipeline
For each of the predefined jobs in the GitLab repository, you’ll be using several Liquibase commands to help manage database changes quickly and safely:
-
liquibase_job:
before_script:
- functions
- isUpToDate
- liquibase checks run
- liquibase updateSQL
- liquibase update
- liquibase rollbackOneUpdate --force
- liquibase tag $CI_PIPELINE_ID
- liquibase --logFile=${CI_JOB_NAME}_${CI_PIPELINE_ID}.log --logLevel=info update
- liquibase history
script:
- echo "Comparing databases DEV --> QA"
- liquibase diff
- liquibase --outputFile=diff_between_DEV_QA.json diff --format=json
script:
- echo "Snapshotting database PROD"
- liquibase --outputFile=snapshot_PROD.json snapshot --snapshotFormat=json
Learn more about each of these commands in the README file in the GitLab repository.
Tutorial
The following tutorial demonstrates how to run Liquibase in a GitLab CI/CD pipeline. Follow along by watching this companion video:
Prerequisites
To start, I’m using a Linux machine with the following:
- A GitLab account
- Self-managed Runner on a Linux machine
- Git
- Java 11
- Access to a SQL Server database with multiple environments
Download, install, and configure Liquibase
Get a free Liquibase Pro license key. No credit card is required, so you can play with all the advanced features and get support for 30 days. You’ll use this key later when you configure environment variables within GitLab.
Ensure Liquibase is installed properly by running the liquibase --version command. If everything is good you’ll see the following:
Starting Liquibase at 18:10:06 (version 4.6.1 #98 built at 2021-11-04 20:16+0000)
Running Java under /usr/lib/jvm/java-11-openjdk-11.0.13.0.8-1.el7_9.x86_64 (Version 11.0.13)
Liquibase Version: 4.6.1
Liquibase Community 4.6.1 by Liquibase
Prepare your GitLab project
Fork this example GitLab project repository. (See more information about forking a repository.)
Create a self-managed GitLab Runner on your Linux instance with your newly forked GitLab project.
Clone your newly forked project repository:
git clone https://gitlab.com/
Go to the “sql_server” project folder.
cd sql_server
Run the following command to change your git branch to staging:
git checkout staging
Configure the GitLab CI/CD pipeline environment variables.
Your configuration will include CI/CD variables, Liquibase properties, database credentials, and the Liquibase Pro trial license key so you can use all the advanced Liquibase commands.
From the main sql_server project, go to Settings → CI/CD
Under Variables, click Expand and add the following variables:
Configure the self-managed GitLab runner
From the main sql_server project, go to Settings → CI/CD
Expand the runners section, click the pencil edit icon, and add the following runner tags (comma separated):
dev_db,prod_db,test_db
Note: Tags are created to help choose which runner will do the job. In this example, we are associating all tags to one runner. Learn more about configuring runners.
Make changes to the database
Edit the changelog.sql file and add the following changeset after
liquibase formatted sql:
-- changeset SteveZ:createTable_salesTableZ
CREATE TABLE salesTableZ (
ID int NOT NULL,
NAME varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
REGION varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
MARKET varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
--rollback DROP TABLE salesTableZ
Add, commit, and push all new database changes.
git add changelog.sql
git commit -m “added changelog id and a create table salesTableZ changeset”
git push -u origin staging
Merge the changes and run the pipeline
Let’s merge the changes from branch staging → main to trigger the pipeline to run all jobs.
Click Merge requests → New merge request
Select staging as Source branch and main as Target branch
Click Compare branches and continue
On the next screen, click Create merge request
Click Merge to finish merging the changes
Once these steps are completed, the code is merged into main and the pipeline is triggered to run.
To see the pipeline running, click Pipelines.
To view the pipeline progress, click the pipeline ID link. You can view each job’s log output by clicking on each job name.
Clicking into the build-job example:
The liquibase checks run command validates the SQL for any violations.
57Starting Liquibase at 22:19:14 (version 4.6.1 #98 built at 2021-11-04 20:16+0000)
58Liquibase Version: 4.6.1
59Liquibase Pro 4.6.1 by Liquibase licensed to customersuccess until Mon Jun 27 04:59:59 UTC 2022
60Executing Quality Checks against changelog.sql
61Executing all checks because a valid Liquibase Pro license was found!
62Changesets Validated:
63 ID: createTable_salesTableZ; Author: SteveZ; File path: changelog.sql
64Checks run against each changeset:
65 Warn on Detection of 'GRANT' Statements
66 Warn on Detection of 'REVOKE' Statements
67 Warn when 'DROP TABLE' detected
68 Warn when 'DROP COLUMN' detected
69 Check for specific patterns in sql (Short Name: SqlCreateRoleCheck)
70 Warn when 'TRUNCATE TABLE' detected
71 Warn on Detection of grant that contains 'WITH ADMIN OPTION'
72Liquibase command 'checks run' was executed successfully.
The liquibase update command deploys the changes. If you choose, you can view a full report of your changes in Liquibase Hub. The update command also saves the deployment log output file as an artifact.
227Starting Liquibase at 22:19:34 (version 4.6.1 #98 built at 2021-11-04 20:16+0000)
228Liquibase Version: 4.6.1
229Liquibase Pro 4.6.1 by Liquibase licensed to customersuccess until Mon Jun 27 04:59:59 UTC 2022
230
