Skip to main content

CI/CD for Databases on GitLab (Versioned Migrations)

GitLab is a popular, open-source alternative to GitHub. In addition to a self-hosted version, GitLab also offers a hosted version at gitlab.com. Similar to GitHub, GitLab offers users storage for Git repositories, issue tracking, and CI/CD pipelines.

In this guide we will demonstrate how to use GitLab CI and Atlas to setup CI pipelines for your database schema changes using the versioned migrations workflow.

Prerequisites​

Installing Atlas​

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

curl -sSf https://atlasgo.sh | sh

Installation instructions can be found here.

After installing Atlas locally, you will need to log in to your organization. You can do this by running the following command:

atlas login

Creating a bot token​

In order to report the results of your CI runs to Atlas Cloud, you will need to create a bot token for Atlas Cloud to use.

Follow these instructions to create a token and copy it.

Next, in your Gitlab project go to Settings -> CI/CD -> Variables and create a new variable called ATLAS_CLOUD_TOKEN. Paste your token in the value field.

info

Make sure the variables are exported (the "Protect variable" checkbox is unchecked), so that they are available to all branches.

Creating a variable for your database URL​

To avoid having plain-text database URLs which may contain sensitive information in your configuration files, create another variable named DB_URL and populate it with the URL (connection string) of your database.

To learn more about formatting URLs for different databases, see the URL documentation.

Creating a Gitlab access token (optional)​

Atlas will need permissions to comment lint reports on merge requests. To enable it, in your Gitlab project go to Settings -> Access Tokens. Create a new token. The role field should be set to "Reporter" or higher, and the "API" checkbox should be checked.

Copy the token, and then go to Settings -> CI/CD -> Variables and create a new variable called GITLAB_TOKEN. Paste the token in the value field.

Versioned Migrations Workflow​

In the versioned workflow, changes to the schema are represented by a migration directory in your codebase. Each file in this directory represents a transition to a new version of the schema.

Based on our blueprint for Modern CI/CD for Databases, our pipeline will:

  1. Lint new migration files whenever a merge request (MR) is opened.
  2. Push the migration directory to the Schema Registry when changes are merged to the mainline branch.
  3. Apply new migrations to our database.

Creating a simple migration directory​

If you don't have a migration directory yet, create one by running the following commands:

atlas migrate new --edit

and paste the following in the editor:

-- create table "users"
CREATE TABLE users(
	id int NOT NULL,
	name varchar(100) NULL,
	PRIMARY KEY(id)
);

-- create table "blog_posts"
CREATE TABLE blog_posts(
	id int NOT NULL,
	title varchar(100) NULL,
	body text NULL,
	author_id int NULL,
	PRIMARY KEY(id),
	CONSTRAINT author_fk FOREIGN KEY(author_id) REFERENCES users(id)
);

Pushing a migration directory to Atlas Cloud​

Run the following command from the parent directory of your migration directory to create a "migration directory" repo in your Atlas Cloud organization (replace "app" with the name you want to give to your new repository):

$ atlas migrate push app \
  --dev-url "docker://postgres/15/dev?search_path=public"
info

If the migration directory contains multiple schemas, adjust the dev-url accordingly.

Atlas will print a URL leading to your migrations on Atlas Cloud. You can visit this URL to view your migrations.

Setting up GitLab CI​

Create a .gitlab-ci.yml file with the following pipelines, based on the type of your database. Remember to replace "app" with the real name of your repository.

.gitlab.yml
image: ubuntu:latest

services:
  - postgres:latest

variables:
  POSTGRES_DB: dev
  POSTGRES_USER: user
  POSTGRES_PASSWORD: pass

stages:
  - lint
  - push
  - apply

include:
  - component: $CI_SERVER_FQDN/arigaio/atlas/migrate-lint@~latest
    inputs:
      stage: lint
      dir: "file://migrations"
      atlas-cloud-token: $ATLAS_CLOUD_TOKEN
      dir-name: "app"
      dev-url: "postgres://user:pass@postgres/dev?sslmode=disable"

  - component: $CI_SERVER_FQDN/arigaio/atlas/migrate-push@~latest
    inputs:
      stage: push
      dir: "file://migrations"
      dir-name: "app"
      dev-url: "postgres://user:pass@postgres/dev?sslmode=disable"
      atlas-cloud-token: $ATLAS_CLOUD_TOKEN
      
  - component: $CI_SERVER_FQDN/arigaio/atlas/migrate-apply@~latest
    inputs:
      stage: apply
      dir: "file://migrations"
      url: $DB_URL
      revisions-schema: public
      atlas-cloud-token: $ATLAS_CLOUD_TOKEN

Let's break down what this file is doing:

  1. The migrate-lint component will run on every new merge request. If new migrations are detected, Atlas will lint them and post the report as a merge request comment like this:
Screenshot of a GitLab merge request comment from Atlas, showing a successful linting report for a new database migration.
  1. After the merge request is merged into to main branch, the migrate-push component will push the new state of the schema to the Schema Registry on Atlas Cloud.
  2. Then, the migrate-apply component will deploy the new migrations to your database.

Testing our pipeline​

Let's take our pipeline for a spin:

  1. Locally, create a new branch and add a new migration with atlas migrate new --edit. Paste the following in th editor:
schema.sql
CREATE TABLE `test` (`c1` INT)
  1. Commit and push the changes.
  2. In Gitlab, open a merge request.
  3. View the lint report generated by Atlas. Follow the links to see the changes visually on Atlas Cloud.
  4. Merge the MR.
  5. When the pipeline is finished running, check your database to see if the changes were applied.

Wrapping up​

In this guide, we demonstrated how to use GitLab CI/CD with Atlas to set up a modern CI/CD pipeline for versioned database migrations. Here's what we accomplished:

  • Automated migration linting on every pull request to catch issues early
  • Centralized migration management by pushing to Atlas Cloud's Schema Registry
  • Automated deployments to your target database when changes are merged

For more information on the versioned workflow, see the Versioned Migrations documentation.

Next steps​