Simple Database Migration Scripts On Your CI/CD step

I've seen a lot of outstanding, high-level work in several of the submissions to the DevOps Writing Contest. However, few individuals consider how to keep the database up to date on common standbys (dev, stage, prod, etc.) while working every day. This article is intended for novice developers. I used Spring as an example to demonstrate a simple technique for introducing a database migration system into the CI/CD process.

We will require Liquibase and GitLab CI/CD tools for this deployment. Assume for the moment that a database is up and running. I will be using PostgreSQL.

GitLab CI/CD

If you already have GitLab deployed, you can skip this step entirely.

To deploy Gitlab, we will use the Docker method. It is important to note that we need to create the $GITLAB_HOME variable.

sudo docker run --detach \
  --hostname gitlab.devopscontest.com \
  --publish 443:443 --publish 80:80 --publish 22:22 \
  --name gitlab \
  --restart always \
  --volume $GITLAB_HOME/config:/etc/gitlab \
  --volume $GITLAB_HOME/logs:/var/log/gitlab \
  --volume $GITLAB_HOME/data:/var/opt/gitlab \
  --shm-size 256m \
  gitlab/gitlab-ee:16.4.1-ee.0

The next step is to install and configure runners.

If you already have a long-running project and everything has been set up for a long time, you can skip creating a new runner.

I'll be showing the creation of a runner using version 16.4.1 as an example. Anything after 17.0 goes to registration using RUNNER_AUTHENTICATION_TOKEN, but the approach is close to the current one.

First, you need to install the runner source. In order to install it via bash on your deployed GitLab, this example helps how you can install runner for Apple Silicon-based systems (other OS you can find here):

# Download the binary for your system
sudo curl -L --output /usr/local/bin/gitlab-runner https://gitlab-runner-downloads.s3.amazonaws.com/latest/binaries/gitlab-runner-linux-amd64

# Give it permission to execute
sudo chmod +x /usr/local/bin/gitlab-runner

# Create a GitLab Runner user
sudo useradd --comment 'GitLab Runner' --create-home gitlab-runner --shell /bin/bash

# Install and run as a service
sudo gitlab-runner install --user=gitlab-runner --working-directory=/home/gitlab-runner
sudo gitlab-runner start

Now we can create the runner itself through the UI by clicking the New project runner button.

Each runner can have a platform, tags that appear in blue, and other runtime configurations. When we need to set the possibility of running commands from the program, tags come in handy. For the time being, we are uninterested in everything else.

After clicking Create runner, the only thing left to do is to register your runner.

# Command to register runner
gitlab-runner register --url https://gitlab.devopscontest.com/ --registration-token glrt-bjyfCR5pM5wypQDdfSqU

Liquibase

Liquibase is a free and open-source platform for managing database migrations. In summary, Liquibase allows you to describe rollup and rollback procedures using changset files. The scripts themselves can be conventional SQL commands as well as database-independent descriptions of modifications that will be turned into a script tailored to your database. The list of supported databases is available here.

I will show you a way to perform migrations in a Spring application. This is necessary so that you can control the execution of all change sets as a separate step.

Project

To quickly develop a Spring application, go here.

Let's now quickly create application.yml. Particular attention should be paid to the enabled and change-log options. The first option enables and disables Liquibase at the application level, while the second sets the path to the changelog.

server:
  port: 80

spring:
  datasource:
    username: ${DB_USERNAME}
    password: ${DB_PASSWORD}
    url: ${DB_HOST}
  liquibase:
    enabled: true
    change-log: classpath:db/changelog/db.changelog-master.xml

What to do with the variables?

We use Kubernetes in our project, and these parameters must be set by the teams themselves when constructing the application using value files that are preconfigured for various schemes

env:
  - name: SPRING_PROFILES_ACTIVE
    value: "dev"
  - name: DB_HOST
    value: "jdbc:postgresql://127.0.0.1:5436/your_db"
  - name: DB_USERNAME
    value: "user"
  - name: DB_PASSWORD
    value: "pass"

Let's go back to customization. Creating completely self-sufficient apps that are prepared for deployment right away is, in my opinion, a smart practice. In order to further build a distinct stage in CI/CD, let's construct a migrate file. We can use different tools for different teams, and we are not restricted to the migration system with this file.

#!/bin/sh

cd /opt/app

/opt/app/liquibase \
    --changeLogFile=./db/changelog/changelog-master.xml \
    --url="$DB_HOST" \
    --username="$DB_USERNAME" \
    --password="$DB_PASSWORD" \
    --logLevel=info \
    --headless=true \
    update

All possible commands can be found in the Liquibase documentation. Now we only need update, which allows us to update to the current version from the changelog-master.xml file.

The next step is to create a.gitlab-ci.yml file at the project root that contains instructions on how to proceed with the CI/CD process.

Firstly, I would want to draw attention to the built-in linker for the gitlab-ci.yml file, which can be found in the project at the relative path ci/lint. Before fluffing the configuration, I suggest using it. It's also assumed that you know how to work with YAML files.

Stages and jobs

stages:
  - publish
  - dev

.migrate:
  stage: publish
  allow_failure: true
  script:
    - ./migrate.sh

Migrate:
  tags:
    - migration
  extends: .migrate
  environment:
    name: dev

The migration tag instructs our previously generated runner to react to this task and run scripts in the script section. The stage string identifies the stage to which this job belongs. If necessary, the same runner can work on several projects at once. The environment block refers to the environment. This is a type of dashboard where you can view the status of your stands (dev, pre-prod, prod, etc.) and take actions like manual deploys. Additionally, environment-bound variables can be configured; however, I haven't had a chance to test this premium feature.

Rejection of measures with errors

You can stop the corresponding merging in case something goes wrong with the deployment procedure. In particular, this is helpful if tests are integrated into CI/CD as an independent phase. Although Liquibase does not have tests in our example, it can malfunction if incorrect file paths are entered, for instance.

Final touches

Let's create a migration file and enable it. To do this we need to create the changelog-master.xml file as an input point.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.1.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
    <include file="db/changelog/changeset-202310151300-create-table.sql"/>
</databaseChangeLog>

--liquibase formatted sql

--changeset a.tyryshkin:create-table
create table if not exists table
(
    id     bigserial primary key,
    name   varchar(255)
);

create sequence if not exists table_id_seq;

You can use different XML, YAML, or JSON formats to describe migrations, but SQL is the easiest to read for my taste.

We try to execute and everything is successful. Check our database and see the entry in databasechangelog.

Conclusion

In this article, we discussed how to deploy GitLab yourself, download and configure runners, and, most significantly, integrate a database migration system into your CI/CD process. I hope this guide will assist you in putting this guide into action.