AWS Partner Network (APN) Blog

How to Integrate Amazon RDS Schema Changes into CI/CD Pipelines with GitLab and Liquibase

By Jeffrey Pell, VP Data Services at Blue Sentry

Blue-Sentry-AWS-Partners

As software release cycles get progressively faster, coordinating database schema changes with application releases becomes more difficult.

Previously, database changes would be executed as a separate step to application releases within the same release cycle. These changes would often be executed manually by a database administrator (DBA) so that post-change validation could occur.

Now, as CI/CD pipelines become the standard for releasing application changes, retaining the often-manual process of performing database changes becomes an obstacle that slows down deployment and exposes the process to the possibility of human error.

Integrating database changes into the CI/CD pipeline is critical to increasing the speed of database schema changes. Cleanly performing this automation requires use of source code repositories, automatic application of changes, the automatic rollback of changes when problems occur, and notification of final status.

Additionally, a method of communicating the current database state must be communicated to the application release process to ensure all necessary changes are present before a release is attempted.

In this post, I will outline the steps required to implement this process, including the goal of each step and code samples to implement them.

Blue Sentry is an AWS Premier Consulting Partner with AWS Competencies in DevOps, Migration, Data & Analytics, and Microsoft Workloads that has assisted many customers in implementing this type of solution. Blue Sentry is also a member of the AWS Managed Service Provider (MSP) Partner Program.

Toolset

Utilizing industry standard tools is important to simplify ease of access for users. The tools named below are leaders in their respective segment.

The tools utilized are:

  • GitLab is a web-based DevOps lifecycle tool that provides a Git-repository manager.
  • Liquibase is an open source, database-independent library for tracking, managing, and applying database schema changes. For this setup, Liquibase is installed on the development workstation.
  • GitLab runners is an application that works with GitLab CI/CD to execute jobs in a pipeline. For this setup, the runners are hosted on the development workstation.
  • Docker is a set of platform-as-a-service (PaaS) products that use operating system (OS)-level virtualization to deliver software in packages called containers. For this setup, Docker is used to host the “tear down” database to check the complete rebuild step.
  • Amazon Aurora MySQL is a MySQL-compatible relations database built for the cloud. For this setup, Aurora is utilized for the integrated development database.

Stairway to Automation

The following process covers the four basic configuration steps. The actual code referenced can be viewed in our GitLab repo.

Step 0: Developer Sandbox

Developers should perform all of their work in a sandbox database. Ideally, this environment is regularly refreshed from a higher environment to provide a representative volume and diversity of data.

The refresh process should be a separate automated process that creates the environment from the desired snapshot and performs any necessary data cleansing.

This provides developers with a representative set of isolated data that improves both functional and performance testing while not impacting other ongoing development processes.

Step 1: Automate Complete Rebuild on Commit

To ensure the database can be rebuilt from scratch using only the code stored in the GitLab repository, the process will apply ALL code in the repository, including the developer’s changes, utilizing Liquibase.

Once the developer has completed their work, including development and initial testing, they commit that work to a new branch in the GitLab repository.

The commit action triggers the GitLab CI/CD runners to attempt the following parts (a complete copy of .gitlab-ci.yml included in the GitLab repo).

  • First, start a Docker container for MySQL. This is a “clean” database with no existing objects. It checks for existing Docker containers of the same name and starts a Docker container when not already running.

docker inspect $DB_HOST_BRANCH >& /dev/null || docker run -p 3306:3306 --name $DB_HOST_BRANCH -e MYSQL_ROOT_PASSWORD="$MYSQL_PASSWORD" -d mysql:8 mysqld --default-authentication-plugin=mysql_native_password

  • Next, create the necessary schema in the database.

echo "CREATE SCHEMA if not exists $DB_SCHEMA;" | mysql --user=$DB_USER_BRANCH --host=$DB_HOST_BRANCH --protocol=tcp

  • Execute the Liquibase command to apply ALL code, including the changes made by the developer. If Liquibase encounters any problems, the pipeline stage will fail and leave the Docker container running for further analysis. This container will require manual stopping once analysis is complete.

liquibase --defaultsFile liquibase.properties.mysql --url jdbc:mysql://$DB_HOST:3306/$DB_SCHEMA?useSSL=FALSE --username=$DB_USER_BRANCH --password=$DB_PASSWORD_BRANCH update

  • Stop Docker container.

docker stop $PID
docker container rm $PID

Step 2: Automate Changes to Integrated Development Database on Merge

Now that the code changes are proven to not negatively affect a complete rebuild of the database, we need to test the changes against the integrated development database. This ensures the changes can apply cleanly to the database as it exists today.

Once the developer’s modifications have been approved, they must be merged into the main branch of the repository.

Merging the developer’s branch into the master branch will trigger this step and attempt the following parts (complete copy of .gitlab-ci.yml included in the GitLab repo).

  • Ensure the correct schema exists in the database. If it does not exist, as would happen in a new development environment, create it.

echo "CREATE SCHEMA if not exists $DB_SCHEMA;" | mysql --user=$DB_USER_BRANCH --host=$DB_HOST_BRANCH --protocol=tcp

  • Execute the necessary Liquibase command to apply all NEW code, including the changes made by the developer. For this step, the ability to automatically rollback changes when Liquibase encounters any error will be implemented, as well by defining a “tag” that Liquibase uses to remove any changes that may have applied during this execution.

liquibase --defaultsFile liquibase.properties.mysql --url jdbc:mysql://$DB_HOST_MASTER:3306/$DB_SCHEMA?useSSL=FALSE --username=$DB_USER_MASTER --password=$DB_PASSWORD_MASTER tag ${mytag}
liquibase --defaultsFile liquibase.properties.mysql --url jdbc:mysql://$DB_HOST_MASTER:3306/$DB_SCHEMA?useSSL=FALSE --username=$DB_USER_MASTER --password=$DB_PASSWORD_MASTER update ||
liquibase --defaultsFile liquibase.properties.mysql --url jdbc:mysql://$DB_HOST_MASTER:3306/$DB_SCHEMA?useSSL=FALSE --username=$DB_USER_MASTER --password=$DB_PASSWORD_MASTER rollback ${mytag}

Step 3: Validate Database Version Before Application Changes

In some environments, coordinating the database and application changes for a new version requires reliance on communication between team members to perform a release.

Often, the database changes wait until the application changes are ready for release, and then a person with authority to apply those changes to the database executes all the necessary changes. Once complete, it notifies the application team that things are ready. This required interaction can lead to delays.

By implementing the following changes to your GitLab CI/CD process on the application repository, the release process can check that database contains the appropriate version and stop or continue the release process based on that determination (complete copy of .gitlab-ci.yml included in the GitLab repo).

  • Check for the existence of a Git tag. If one is not present only the first stage of the pipeline will not run. This is enforced by GitLab CI/CD rules.

 rules:
    - if: '$CI_COMMIT_TAG != null'

  • Check the desired release exists in the version tracking table in the database.

REC_COUNT=`echo "select count(1) from lbtest.version_tracker where version = '$CI_COMMIT_TAG';"| mysql --user="$DB_USER"  --host="$DB_HOST"  --protocol=tcp -sN`
if [[ $REC_COUNT == "1" ]]; then echo "Version match.  Build will continue"; echo "Yes" > build/versionmatch.txt; else echo "Version not in database.  Build will halt"; echo "No" > build/versionmatch.txt; fi

  • If the desired release does NOT exist stop the pipeline execution.

if [[ $VERSION_MATCH == "No" ]]; then echo "Version not in DB"; exit 1; fi

Next Steps

Now that these steps are automated, Steps 2 and 3 above can be applied to progressively higher environments, including production.

While the triggering event may be different for each environment, that can be handled by modifying the GitLab CI/CD processes to handle each appropriately.

Conclusion

Tools such as GitLab and Liquibase can be used to automate the deployment of database schema changes. For some teams, it may require a complete overhaul of processes and perhaps even a change in mindset but it is possible.

Even teams that recognize the need to release changes in this manner may require retraining and redevelopment of processes, but that should not hamper implementation of the process.

With faster and faster release cycles and an ever-increasing number of databases involved (both types and instances), automating database releases is critical to minimize blocking by database schema changes.

Using tools such as GitLab and Liquibase will help streamline this process. While implementing these processes in a new environment takes time and effort, the long-term payback more than outweighs the effort.

Blue Sentry has extensive experience using infrastructure as code (IaC) to improve CI/CD processes and to support better outcomes for customers. To find out how we do it, visit the Blue Sentry website to learn more.

The content and opinions in this blog are those of the third-party author and AWS is not responsible for the content or accuracy of this post.

.
Blue-Sentry-APN-Blog-CTA-1
.

Blue Sentry – AWS Partner Spotlight

Blue Sentry is an AWS Premier Consulting Partner with deep expertise in Kubernetes that focuses on cloud-native application deployment and operations.

Contact Blue Sentry | Partner Overview

*Already worked with Blue Sentry? Rate the Partner

*To review an AWS Partner, you must be a customer that has worked with them directly on a project.