Skip to main content

Pre-approved Declarative Schema Migrations Using GitLab CI

Introduction​

One of the main benefits of moving schema changes into a declarative workflow is the ability to offload the responsibility of planning schema changes from developers to a specialized tool. However, since the database is such a critical part of applications, many organizations still want to have a human in the loop to approve schema changes before they are applied.

With pre-approvals, schema changes are planned during the development or CI process and stored in the schema registry.

How Pre-approvals work​

Pre-approvals work by adding two automated steps to the schema change process:

  1. Plan Generation - When a schema change is detected, the CI pipeline runs the atlas schema plan command to create a migration plan by diffing the target database against the desired schema. The CI runner must have Atlas installed and access to the database for this step.

    This is typically accomplished using the schema-plan GitLab CI component but can be done using any method that can run the atlas schema plan command.

  2. Plan Approval - The MR containing the schema changes is reviewed by a human. The migration plan is included in the MR description or as a comment. Once the plan is reviewed and approved, the MR is merged.

    This step is manual and requires a human to review the changes and the migration plan.

    Once the MR is merged, a second automated step stores the approved plan in the schema registry. This is typically done using the schema-plan-approve GitLab CI component.

Once the plan is stored in the registry, schema-apply behaves differently. Every time the component is executed, it checks whether the corresponding migration plan has been approved:

  • If the plan is approved, it will be used for the schema-apply component without recalculating the migration.
  • If the plan is not approved, the command fails and requires human intervention.

Guide: Pre-approval flows​

In this guide, we demonstrate how to set up pre-approval flows for declarative migrations using GitLab CI.

Step 1: Set up the Atlas Project​

Let's start by setting up an Atlas project. In this guide we will be managing a simple Neon Postgres database and defining our desired schema using plain SQL files.

First, create a new repository on GitLab and clone it to your local machine.

In this directory, create a new file named schema.sql with the following content:

schema.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Next, let's create the project configuration file atlas.hcl:

atlas.hcl
variable "db_url" {
  type = string
  default = getenv("DB_URL")
}

env {
  name = atlas.env
  url = var.db_url
  schema {
    src = "file://schema.sql"
    repo {
      name = "preapproval-gitlab"
    }
  }
  dev = "docker://postgres/16/dev"
}

Verify the project is set up correctly by running the following commands:

  1. Start a local database for development via Docker:
docker run --name postgres -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 postgres
  1. Export the database URL to your environment:
export DB_URL="postgres://postgres:postgres@localhost:5432/postgres?search_path=public&sslmode=disable"
  1. Diff the current schema with the desired schema:
atlas schema diff --env local --from env://url --to env://schema.src --format "{{ sql . }}"

If everything is set up correctly, you should see the diff between the current schema (presumably empty) and the desired schema:

-- Create "users" table
CREATE TABLE "users" ("id" serial NOT NULL, "name" character varying(100) NOT NULL, PRIMARY KEY ("id"));

Finally, commit these changes to the mainline branch of your repository.

Step 2: Push to Schema Registry​

Before proceeding, make sure you have an Atlas Cloud account and are logged in:

atlas login

If you don't have an account or need to log in, follow the instructions on the screen.

Now, push the schema to the registry:

atlas schema push --env local

If the push is successful, your output should resemble the following:

Schema: preapproval-gitlab
  -- Atlas URL: atlas://preapproval-gitlab
  -- Cloud URL: https://your-org.atlasgo.cloud/schemas/141733920820

You can visit the Cloud URL to see the schema in the registry.

Step 3: Set up CI & CD​

Now that we have our schema in the registry, let's set up a GitLab CI pipeline to manage the pre-approval process.

As a prerequisite, please make sure you:

  1. Have a Bot Token with access to the registry repo we just pushed to. To learn how to create a Bot Token, please refer to the Atlas docs.

  2. Add the Bot Token to your GitLab repository CI/CD variables as ATLAS_CLOUD_TOKEN. To learn how, refer to the GitLab docs.

  3. Have a Neon database set up. To learn how to set up a Neon database, please refer to the Neon docs. You can also use any other database supported by Atlas.

  4. Store the database URL in a GitLab CI/CD variable named NEON_DB_URL.

Next, create a new file in your repository named .gitlab-ci.yml with the following content:

.gitlab-ci.yml
services:
  - postgres:latest

variables:
  POSTGRES_DB: dev
  POSTGRES_USER: postgres
  POSTGRES_PASSWORD: pass

stages:
  - plan
  - deploy

include:
  - component: $CI_SERVER_FQDN/arigaio/atlas/schema-plan@~latest
    inputs:
      stage: plan
      env: ci
      dev-url: 'postgres://postgres:pass@postgres/dev?sslmode=disable'
      atlas-cloud-token: $ATLAS_CLOUD_TOKEN
      gitlab-token: $GITLAB_TOKEN
    rules:
      - if: $CI_PIPELINE_SOURCE == "merge_request_event"

  - component: $CI_SERVER_FQDN/arigaio/atlas/schema-plan-approve@~latest
    inputs:
      stage: deploy
      branches:
        - main
      env: cd
      from: $NEON_DB_URL
      atlas-cloud-token: $ATLAS_CLOUD_TOKEN
    rules:
      - if: $CI_COMMIT_BRANCH == "main"

  - component: $CI_SERVER_FQDN/arigaio/atlas/schema-push@~latest
    inputs:
      stage: deploy
      branches:
        - main
      env: cd
      atlas-cloud-token: $ATLAS_CLOUD_TOKEN
    rules:
      - if: $CI_COMMIT_BRANCH == "main"

  - component: $CI_SERVER_FQDN/arigaio/atlas/schema-apply@~latest
    inputs:
      stage: deploy
      branches:
        - main
      url: $NEON_DB_URL
      env: cd
      atlas-cloud-token: $ATLAS_CLOUD_TOKEN
    rules:
      - if: $CI_COMMIT_BRANCH == "main"

Then push the pipeline configuration to mainline branch:

git checkout main
git add .
git commit -m "Setup pre-approval flow"
git push origin main

Let's break down the pipeline's main components:

On Merge Request​

  1. schema-plan - This component runs the atlas schema plan command to generate a migration plan. The plan is pushed and stored on the schema registry. The component will add a comment with the plan for reviewing, if the GITLAB_TOKEN is set. These are the provided inputs:
    • env - The environment to use for the plan. We use ci to mark this as a CI environment.
    • dev-url - The database URL to use for development/analysis.
    • atlas-cloud-token - The token to authenticate with Atlas Cloud.
    • gitlab-token - The token to post comments on the merge request.

On Push to Main​

  1. schema-plan-approve - This component approves the plan generated in the previous step. These are the provided inputs:
    • env - The environment to use for the plan. We use cd to mark this as a CD environment.
    • from - The database URL to compare against.
    • atlas-cloud-token - The token to authenticate with Atlas Cloud.
  2. schema-push - This component pushes the schema to the schema registry using the commit as tag, also updating the latest tag. These are the provided inputs:
    • env - The environment to use. Again, cd denotes the CD environment.
    • atlas-cloud-token - The token to authenticate with Atlas Cloud.
  3. schema-apply - This component applies the schema to the database. These are the provided inputs:
    • url - The database URL to apply the schema to.
    • env - The environment to use. We use cd to mark this as a CD environment.
    • atlas-cloud-token - The token to authenticate with Atlas Cloud.

Step 4: Verify the workflow​

Now that we have our CI and CD pipelines set up, let's verify that everything is working correctly.

  1. Create a new branch in your repository and make a change to the schema.sql file. For example, add a new column to the users table:
schema.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);
  1. Commit the changes and push them to your repository:
git checkout -b add-email-column
git add schema.sql
git commit -m "Add email column to users table"
git push origin add-email-column
  1. Create a merge request from the add-email-column branch to the main branch.
  2. The CI pipeline should automatically run and generate a migration plan. You should see the migration plan in the MR description or as a comment.
  3. Review the MR and the migration plan. Once you are satisfied with the changes, merge the MR.
  4. The CD pipeline should automatically approve and apply the migration plan. You should see the report in the GitLab CI/CD pipeline log.

Conclusion​

In this guide, we demonstrated how to set up pre-approval flows for declarative migrations using GitLab CI.