Skip to main content

CI/CD for Databases on CircleCI - Versioned Workflow

CircleCI is a popular CI/CD platform that allows you to automatically build, test, and deploy your code. Combined with Atlas, you can manage database schema changes with confidence.

In this guide, we will demonstrate how to use CircleCI and Atlas to set up CI/CD 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 and CircleCI context​

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

Next, we'll create a CircleCI context to securely store environment variables that will be shared across jobs:

  1. In CircleCI, go to Organization Settings -> Contexts
  2. Click Create Context and name it dev (this matches the context used in our example configuration)
  3. Click on the newly created dev context
  4. Add the following environment variables:
    • ATLAS_TOKEN: Your Atlas Cloud bot token (how to create) - required
    • DATABASE_URL: The URL (connection string) of your target database (URL format guide) - required
    • GITHUB_TOKEN: GitHub personal access token with repo scope (how to create) - optional, needed for PR comments
    • GITHUB_REPOSITORY: Your GitHub repository in the format owner/repo (e.g., ariga/atlas) - required for PR comments

Using a context allows you to manage these sensitive variables in one place and reuse them across multiple projects and workflows.

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.

In this guide, we will walk through each of these steps and set up a CircleCI configuration to automate them.

The full source code for this example can be found in the atlas-examples/versioned repository.

Defining the desired schema​

First, define your desired database schema. Create a file named schema.sql with the following content:

schema.sql
CREATE TABLE "users" (
  "id" bigserial PRIMARY KEY,
  "name" text NOT NULL,
  "active" boolean NOT NULL,
  "address" text NOT NULL,
  "nickname" text NOT NULL,
  "nickname2" text NOT NULL,
  "nickname3" text NOT NULL
);

CREATE INDEX "users_active" ON "users" ("active");

Creating the Atlas configuration file​

Create a configuration file for Atlas named atlas.hcl with the following content:

atlas.hcl
variable "database_url" {
  type        = string
  default     = getenv("DATABASE_URL")
  description = "URL to the target database to apply changes"
}

env "dev" {
  src = "file://schema.sql"
  url = var.database_url
  dev = "docker://postgres/15/dev?search_path=public"
  migration {
    dir = "file://migrations"
  }
  diff {
    concurrent_index {
      add  = true
      drop = true
    }
  }
}

Generating your first migration​

Now, generate your first migration by comparing your desired schema with the current (empty) migration directory:

atlas migrate diff initial --env dev

This command will automatically create a migrations directory with a migration file containing the SQL statements needed to create the users table and index, as defined in our file linked at src in the dev environment.

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:

atlas migrate push circleci-atlas-action-versioned-demo --env dev

This command pushes the migrations directory linked in the migration dir field in the dev environment defined in our atlas.hcl to a project in the Schema Registry called circleci-atlas-action-versioned-demo.

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

Setting up CircleCI​

Create a .circleci/config.yml file in the root of your repository with the following content:

.circleci/config.yml
version: 2.1

orbs:
  atlas-orb: ariga/atlas-orb@0.3.1

jobs:
  lint-migrations:
    docker:
      - image: cimg/base:current
      - image: cimg/postgres:15.0
        environment:
          POSTGRES_DB: postgres
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
    steps:
      - checkout
      - run:
          name: Wait for Postgres
          command: dockerize -wait tcp://127.0.0.1:5432 -timeout 60s
      - atlas-orb/setup:
          version: "latest"
      - atlas-orb/migrate_lint:
          env: dev
          dir_name: "circleci-atlas-action-versioned-demo"
          dev_url: "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable&search_path=public"

  push-and-apply-migrations:
    docker:
      - image: cimg/base:current
      - image: cimg/postgres:15.0
        environment:
          POSTGRES_DB: postgres
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
    steps:
      - checkout
      - run:
          name: Wait for Postgres
          command: dockerize -wait tcp://127.0.0.1:5432 -timeout 60s
      - atlas-orb/setup:
          version: "latest"
      - atlas-orb/migrate_push:
          env: dev
          dir_name: "circleci-atlas-action-versioned-demo"
          dev_url: "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable&search_path=public"
      - atlas-orb/migrate_apply:
          env: dev
          dir: "atlas://circleci-atlas-action-versioned-demo"
workflows:
  version: 2
  atlas-workflow:
    jobs:
      - lint-migrations:
          context: dev
          filters:
            branches:
              ignore: main
      - push-and-apply-migrations:
          context: dev
          filters:
            branches:
              only: main
note

This configuration uses main as the default branch name. If your GitHub repository uses a different default branch (such as master), update the workflow filters accordingly:

filters:
  branches:
    only: master  # Change to match your default branch

Let's break down what this pipeline configuration does:

  1. The lint-migrations job runs on every pull request (branches that are not main). When new migrations are detected, Atlas will lint them and post a report as a comment on the pull request. This helps you catch issues early in the development process.
CircleCI pull request comment showing Atlas migration lint results

  1. After the pull request is merged into the main branch, the push-and-apply-migrations job will push the new state of the migration directory to the Schema Registry on Atlas Cloud.

  2. The migrate_apply step will then 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 the editor:
    schema.sql
    DROP INDEX "users_active";
  2. Commit and push the changes.
  3. In GitHub, create a pull request for the branch you just pushed.
  4. View the lint report generated by Atlas. Follow the links to see the changes visually on Atlas Cloud.
  5. Merge the pull request.
  6. When the pipeline has finished running, check your database to verify that the changes were applied.

Wrapping up​

In this guide, we demonstrated how to use CircleCI 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​