Skip to main content

CI/CD for Databases on GitHub Actions (Versioned Migrations)

GitHub Actions is a popular CI/CD platform integrated with GitHub repositories. It allows users to automate workflows for building, testing, and deploying applications.

In this guide, we will demonstrate how to use GitHub Actions and Atlas to set up 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

After installing Atlas locally, log in to your organization by running the following command:

atlas login

Creating a bot token​

To report CI run results to Atlas Cloud, create an Atlas Cloud bot token by following these instructions and copy it.

Next, in your GitHub repository, go to Settings -> Secrets and variables -> Actions and create a new secret named ATLAS_CLOUD_TOKEN. Paste your token in the value field.

Creating a secret for your database URL​

To connect Atlas to your target database, create a URL (connection string) by following our URL documentation.

Create another secret named DB_URL and populate it with the URL of your database to avoid having sensitive information in your configuration files.

Creating a GitHub personal access token (optional)​

Atlas will need permissions to comment lint reports on pull requests. To enable this, create a personal access token with the repo scope. Then, add it as a secret named GITHUB_TOKEN.

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 pull request is opened.
  2. Push the migration directory to the Schema Registry when changes are merged to the main branch.
  3. Apply new migrations to our database.
Auto-generating migrations

Instead of generating migrations locally, you can use the migrate/diff action as part of your CI pipeline.

Creating a simple migration directory​

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

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" repository 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 GitHub Actions​

Create a .github/workflows/ci-atlas.yaml file with the following content, based on the type of your database. Remember to replace "app" with the real name of your repository.

.github/workflows/ci-atlas.yaml
name: Atlas
on:
  push:
    branches:
      - master
    paths:
      - .github/workflows/ci-atlas.yaml
      - 'migrations/*'
  pull_request:
    paths:
      - 'migrations/*'
# Permissions to write comments on the pull request.
permissions:
  contents: read
  pull-requests: write
jobs:
  atlas:
    services:
      # Spin up a postgres:15 container to be used as the dev-database for analysis.
      postgres:
        image: postgres:15
        env:
          POSTGRES_DB: dev
          POSTGRES_PASSWORD: pass
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-start-period 10s
          --health-timeout 5s
          --health-retries 5
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: '${{ secrets.ATLAS_CLOUD_TOKEN }}'
      - uses: ariga/atlas-action/migrate/lint@v1
        with:
          dir: 'file://migrations'
          dev-url: 'postgres://postgres:pass@localhost:5432/dev?search_path=public&sslmode=disable'
          dir-name: 'app'
        env:
          GITHUB_TOKEN: '${{ github.token }}'
      - uses: ariga/atlas-action/migrate/push@v1
        if: github.ref == 'refs/heads/master'
        with:
          dir: 'file://migrations'
          dev-url: 'postgres://postgres:pass@localhost:5432/dev?search_path=public&sslmode=disable'
          dir-name: 'app'
      - uses: ariga/atlas-action/migrate/apply@v1
        if: github.ref == 'refs/heads/master'
        with:
          dir: 'file://migrations'
          url: '${{ secrets.DB_URL }}'

Let's break down what this file is doing:

  1. The migrate-lint step will run on every pull request. If new migrations are detected, Atlas will lint them and post the report as a comment on the pull request. This helps you catch issues early in the development process.
GitHub Actions pull request comment showing Atlas migration lint results with error details

  1. The migrate-push step will run only when the pull request is merged into the main branch. It will push the new migration directory to the Schema Registry on Atlas Cloud, allowing you to manage your migrations in a centralized location.

  2. The migrate-apply step will then deploy the new migrations to your database.

Testing our action​

Let's take our new workflow for a spin. We will create a new migration, push it to the repository, and see how the GitHub Action runs.

  1. Locally, create a new branch and add a new migration with atlas migrate new --edit. Paste the following in the editor:
schema.sql
CREATE TABLE `test` (`c1` INT)
  1. Commit and push the changes.
  2. In GitHub, open a new pull request for the branch you just pushed. This will trigger the migrate-lint step in the workflow.
  3. View the lint report generated by Atlas. Follow the links to see the changes visually on Atlas Cloud.
  4. Merge the pull request into the main branch. This will trigger the migrate-push and migrate-apply steps in the workflow.
  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 GitHub Actions 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​