Skip to main content

CI/CD for Databases on GitHub Actions (Declarative)

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 declarative 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.

Declarative Migrations Workflow​

In the declarative workflow, developers provide the desired state of the database as code. Atlas can read database schemas from various formats such as plain SQL, Atlas HCL, ORM models, and even another live database. Atlas then connects to the target database and calculates the diff between the current state and the desired state. It then generates a migration plan to bring the database to the desired state.

In this guide, we will use the SQL schema format.

Our goal​

When a pull request contains changes to the schema, we want Atlas to:

  • Compare the current state (your database) with the new desired state
  • Create a migration plan to show the user for approval
  • Mark the plan as "approved" when the pull request is approved and merged
  • Use the approved plan to apply the changes to the database during deployment

Creating a simple SQL schema​

Create a file named schema.sql and paste the following:

-- 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)
);

Then, create a configuration file for Atlas named atlas.hcl as follows:

atlas.hcl
env "github" {
    url = getenv("DB_URL")
    schema {
        src = "file://schema.sql"
        repo {
            name = "app"
        }
    }
}

Pushing the schema to Atlas Cloud​

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

$ atlas schema push app \
  --dev-url "docker://postgres/15/dev?search_path=public" \
  --env github

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.

.github/workflows/ci-atlas.yaml
name: Atlas
on:
  push:
    branches: [ master ]
  pull_request:
    branches: [ master ]
  workflow_dispatch:
# Permissions to write comments on the pull request.
permissions:
  contents: read
  pull-requests: write
jobs:
  plan:
    if: ${{ github.event_name == 'pull_request' }}
    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
    env:
      GITHUB_TOKEN: ${{ github.token }}
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
      - uses: ariga/atlas-action/schema/plan@v1
        with:
          env: github
          dev-url: 'postgres://postgres:pass@localhost:5432/dev?search_path=public&sslmode=disable'
  push:
    if: ${{ github.event_name == 'push' && github.ref == 'refs/heads/master' }}
    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
    env:
      GITHUB_TOKEN: ${{ github.token }}
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
      - uses: ariga/atlas-action/schema/plan/approve@v1
        id: plan-approve
        with:
          env: github
          dev-url: 'postgres://postgres:pass@localhost:5432/dev?search_path=public&sslmode=disable'
      - uses: ariga/atlas-action/schema/push@v1
        with:
          env: github
          dev-url: 'postgres://postgres:pass@localhost:5432/dev?search_path=public&sslmode=disable'
      - uses: ariga/atlas-action/schema/apply@v1
        with:
          env: github
          dev-url: 'postgres://postgres:pass@localhost:5432/dev?search_path=public&sslmode=disable'
info

Make sure the DB_URL environment variable is set to the URL of your database in your GitHub Actions environment.

Let's break down what this file is doing:

  1. When a new pull request is opened, the plan job will check if the desired state of the schema was changed. If it was, Atlas will generate a migration plan, lint it and post the report as a pull request comment.

  2. When the pull request is merged, two things happen: First, the updated schema is pushed to the schema registry by the schema/push step. Second, the plan created in the pull request will be approved.

  3. The schema-apply step will then use the approved plan to apply the new schema state to the database.

Testing our workflow​

Let's see our CI/CD workflow in action.

Add an "address" column to the users table:

schema.sql
	name varchar(100) NULL,
+	address varchar(100) NULL,
	PRIMARY KEY(id)

Commit the change to a new branch, push it to GitHub, and open a pull request. The plan job will use Atlas to create a migration plan from the current state of the database to the new desired state:

GitHub Actions workflow showing Atlas schema plan and lint results in pull request comment

There are two things to note: The comment also includes instructions for editing the plan. This is useful when the plan has lint issues (for example, dropping a column will raise a "destructive changes" error).

  • The plan is created in a "pending" state, which means Atlas can't use it yet against the real database.

After merging the changes to the main branch, the workflow will run the push job:

  1. The schema-plan-approve step will approve the plan that was generated earlier.

  2. The schema-push step will update the schema registry with the new desired state.

  3. The schema-apply step will deploy the changes to our database.

GitHub Actions workflow console output showing successful Atlas schema apply deployment to database

The last thing to do is to inspect our database to make sure the changes were applied correctly:

$ atlas schema diff \
  --from $DB_URL \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/15/dev?search_path=public"
Schemas are synced, no changes to be made.

Wrapping up​

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

  • Automated schema planning on every pull request to visualize changes
  • Centralized schema management by pushing to Atlas Cloud's Schema Registry
  • Approval workflow ensuring only reviewed changes are applied
  • Automated deployments using approved plans

For more information on the declarative workflow, see the Declarative Migrations documentation.

Next steps​