CI/CD pipeline to automate database deployments using Liquibase and GitLab
Wondering how to automate database deployments? In this article, I will walk you through an example of building a continuous delivery pipeline that helps you to deploy, track and roll back the database code changes using Liquibase and GitLab.
You will need the following prerequisites before you get started:
- AWS account
- GitLab account
- Amazon EC2 Instance configured as GitLab Runner
- Amazon Aurora RDS database (PostgreSQL)
- A database user that can connect to the Amazon Aurora RDS database and create/drop a table
- A local machine where the Git client is installed
- Docker
- AWS CLI
Here is a high-level Architecture of the implementation:
Commit SQL changeset file to the GitLab repository. The GitLab Runner picks up the job, pulls the image from ECR, fetches the DB credentials from AWS Secrets Manager, and executes the liquibase update and liquibase rollbackCount commands.
Step 1: Use AWS Secrets Manager to store the database credentials.
Refer to the below link to create a secret in Secrets Manager:
Step 2: Create and Publish the Docker Image to AWS ECR
To create a Docker image:
Create a file called Dockerfile.
touch Dockerfile
Edit the Dockerfile you just created and add the following content.
This Dockerfile uses the Docker 19.03 image. The RUN instructions update the package caches, install some software packages for liquibase, downloads the required driver. The CMD instruction starts Docker.
Before you can push your Docker images to Amazon ECR, you must create a repository to store them in.
Refer to the below link to create Amazon ECR repositories with the AWS Management Console.
https://docs.aws.amazon.com/AmazonECR/latest/userguide/repository-create.html
Retrieve an authentication token and authenticate your Docker client to your registry.
Use the AWS CLI:
aws ecr get-login-password — region <REGION_NAME> | docker login — username AWS — password-stdin <ACCOUNTID>.dkr.ecr.<REGION_NAME>.amazonaws.com
Build your Docker image using the following command.
docker build -t liquibase .
After the build completes, tag your image so you can push the image to this repository:
docker tag liquibase:latest <ACCOUNTID>.dkr.ecr.<REGION_NAME>.amazonaws.com/liquibase:latest
Run the following command to push this image to your newly created AWS repository:
docker push <ACCOUNTID>.dkr.ecr.<REGION_NAME>.amazonaws.com/liquibase:latest
Note the Image URI that has to be replaced in the .gitlab-ci.yml.
Step 3: Create a GitLab project
Create a project in Gitlab
https://docs.gitlab.com/ee/user/project/working_with_projects.html#create-a-project
Clone the repository that you just created
https://docs.gitlab.com/ee//////gitlab-basics/start-using-git.html#clone-with-https
To use GitLab CI/CD:
1. Ensure you have runners available to run your jobs. If you don’t have a runner, install GitLab Runner and register a runner for your instance, project, or group.
2. Create a .gitlab-ci.yml file at the root of your repository. This file is where you define your CI/CD jobs.
When you commit the file to your repository, the runner runs your jobs. The job results are displayed in a pipeline.
Create a .gitlab-ci.yml file
The pipeline file consists of two stages: deploy and rollback.
Deploy stage executes liquibase validate and liquibase update command.
liquibase — changeLogFile=<PATH_TO_changelog.sql_FILE>/changelog.sql — url=jdbc:postgresql://$hostname:$portnumber/${DB_NAME} — username=$lquser — password=$lqpassword validate
The validate command checks and identifies any possible errors in a changelog that can cause the update command to fail.
liquibase — changeLogFile=<PATH_TO_changelog.sql_FILE>/changelog.sql — url=jdbc:postgresql://$hostname:$portnumber/${DB_NAME} — username=$lquser — password=$lqpassword — driver=org.postgresql.Driver — classpath=/liquibase/lib/postgresql-42.2.8.jar update
The classpath is the JDBC driver. The postgresql-42.2.5.jar is the JDBC driver specific for Postgres and should be replaceable with the database of your choice without any special changes in these steps.
The update command deploys any changes that are in the changelog file and that have not been deployed to your database yet.
The rollback stage is a manual stage that executes the liquibase rollbackCount command when manually triggered.
The rollbackCount <value> command reverts a specified number of changesets, where <value> is the number of changesets you want to revert sequentially on your database.
liquibase — changeLogFile=<PATH_TO_changelog.sql_FILE>/changelog.sql — url=jdbc:postgresql://$hostname:$portnumber/${DB_NAME} — username=$lquser — password=$lqpassword — driver=org.postgresql.Driver — classpath=/liquibase/lib/postgresql-42.2.8.jar rollbackCount $COUNT
We can configure COUNT as a CI/CD variable to the project.
Refer to the below link to add variables to the project.
https://docs.gitlab.com/ee/ci/variables/#add-a-cicd-variable-to-a-project
Step 4: Create SQL changesets
The SQL syntax which works with Liquibase is:
Create a SQL changelog file as shown below.
When you commit these two files - .gitlab-ci.yml and changelog.sql, a pipeline would be started.
To view your pipeline:
Go to CI/CD > Pipelines.
To view details of a job, click the job name, for example, deploy.
Connect to the database and verify if the changeset has been executed.
To roll back the changeset, go back to Gitlab pipelines and just click the play button to execute that particular job.
You can change the value of COUNT depending on the number of changesets you wish to roll back.
Verify if the table has been dropped.
With that, we have now successfully implemented a CI/CD pipeline that helps us to deploy and roll back database code changes.
