CI/CD for Databases with Azure DevOps Repos

Azure DevOps provides a complete DevOps solution with integrated source control (Azure Repos) and CI/CD pipelines (Azure Pipelines). When your code is hosted in Azure Repos, you can configure pipelines to automatically trigger on pull requests and branch changes, creating a seamless development workflow.

In this guide, we will demonstrate how to set up Atlas database CI/CD workflows using Azure Repos with Azure DevOps Pipelines, including the necessary branch policies and PR triggers.

Prerequisites

Installing Atlas

macOS + Linux

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

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

Homebrew

Get the latest release with Homebrew:

brew install ariga/tap/atlas

Docker

To pull the Atlas image and run it as a Docker container:

docker pull arigaio/atlas
docker run --rm arigaio/atlas --help

If the container needs access to the host network or a local directory, use the --net=host flag and mount the desired directory:

docker run --rm --net=host \
  -v $(pwd)/migrations:/migrations \
  arigaio/atlas migrate apply
  --url "mysql://root:pass@:3306/test"

Windows

Download the latest release and move the atlas binary to a file location on your system PATH.

Manual Installation

If you want to manually install the Atlas CLI, pick one of the below builds suitable for your system.

• MacOS
  • amd64 (md5 / sha256)
  • arm64 (md5 / sha256)
• Windows
  • amd64 (md5 / sha256)
• Linux
  • amd64 (md5 / sha256)
  • arm64 (md5 / sha256)

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

atlas login

Setting up Azure DevOps

1. Create an Azure DevOps organization if you don't have one already.
2. Create a new project in your Azure DevOps organization.
3. Create or import a repository in Azure Repos for your project.
4. Add the Atlas extension to your organization from the Azure DevOps Marketplace.

Creating an Atlas Cloud bot token

To report CI run results to Atlas Cloud, create an Atlas Cloud bot token by following these instructions. Copy the token and store it as a secret using the following steps.

Creating secrets in Azure DevOps

In your Azure DevOps project, go to Pipelines → Library and create a variable group:

1. Create a variable group named "atlas-vars".
2. Add the following variables:
   • ATLAS_TOKEN - Your Atlas Cloud bot token (mark as secret)
   • DB_URL - Your database connection string (mark as secret)

Configuring branch policies

To ensure your pipeline runs on pull requests, you need to configure branch policies:

1. In your Azure DevOps project, go to Repos → Branches.
2. Find your main branch (usually main or master) and click the three dots (...).
3. Select Branch policies.
4. Under Build validation, click Add build policy.
5. Configure the build policy:
   • Build pipeline: Select the pipeline you'll create (you can come back to this step after creating the pipeline)
   • Trigger: Set to Automatic
   • Policy requirement: Set to Required to prevent merging without a successful build
   • Build expiration: Set to Immediately or as per your requirements
6. Click Save.

Granting pipeline permissions

Azure DevOps requires explicit permissions for pipelines to access repositories and perform certain operations. You need to grant the following permissions:

Repository permissions:

• In your Azure DevOps project, go to Project Settings → Repositories.
• Select your repository and go to the Security tab.
• Find the build service account: [Project Name] Build Service ([Organization Name]).
• Grant the following permissions:
  • Contribute: Allow
  • Contribute to pull requests: Allow
  • Create branch: Allow

info

If you encounter permission errors during pipeline execution, check that the build service account has the necessary permissions. Common issues include insufficient repository access or missing project-level permissions.

Choose a workflow

Atlas supports two types of schema management workflows:

• Versioned Migrations - Changes to the schema are defined as migrations (SQL scripts) and applied in sequence to reach the desired state.
• Declarative Migrations - The desired state of the database is defined as code, and Atlas calculates the migration plan to apply it.

This guide focuses on the Versioned Migrations workflow. To learn more about the differences and tradeoffs between these approaches, see the Declarative vs Versioned article.

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.

Pushing a migration directory to Atlas Cloud

Running the following command from the parent directory of your migration directory creates a "migration directory" repo in your Atlas Cloud organization (substitute "app" with the name you want to give the new Atlas repository before running):

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

Dev Database

Replace docker://postgres/16/dev with the appropriate dev database URL for your database. For more information on the dev database, see the dev database article.

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

Setting up Azure DevOps Pipeline

Create an azure-pipelines.yml file in the root of your Azure Repos repository with the following content. Remember to replace "app" with the real name of your repository.

azure-pipelines.yml

trigger:
  branches:
    include:
      - main
  paths:
    include:
      - 'migrations/*'
      - 'azure-pipelines.yml'

pr:
  branches:
    include:
      - main
  paths:
    include:
      - 'migrations/*'

pool:
  vmImage: ubuntu-latest

variables:
  - group: atlas-vars

steps:
- checkout: self
  persistCredentials: true
  fetchDepth: 0
  fetchTags: true

- script: |
    echo "Configuring git user for commits...."
    git config user.email "azure-pipelines[bot]@users.noreply.github.com"
    git config user.name "azure-pipelines[bot]"
  displayName: 'Configure Git User for Commits'

- script: curl -sSf https://atlasgo.sh | sh
  displayName: Install Atlas

- script: atlas version
  displayName: Atlas Version

- script: atlas login --token $(ATLAS_TOKEN)
  displayName: Atlas Login

  # Lint migrations on pull requests
- task: AtlasAction@1
  condition: eq(variables['Build.Reason'], 'PullRequest')
  env:
    SYSTEM_ACCESSTOKEN: $(System.AccessToken)
  inputs:
    action: 'migrate lint'
    dir: 'file://migrations'
    dir_name: 'app-schema-2'
    config: 'file://atlas.hcl'
    env: 'ci'
  displayName: Lint Migrations

  # Push migrations to Atlas Cloud on main branch
- task: AtlasAction@1
  condition: and(succeeded(), eq(variables['Build.SourceBranch'], 'refs/heads/main'))
  inputs:
    action: 'migrate push'
    dir: 'file://migrations'
    dir_name: 'app-schema-2'
    latest: true
    env: 'ci'
  displayName: Push Migrations

# Apply migrations to database on main branch
- task: AtlasAction@1
  condition: and(succeeded(), eq(variables['Build.SourceBranch'], 'refs/heads/main'))
  inputs:
    action: 'migrate apply'
    dir: 'file://migrations'
    url: $(DB_URL)
  displayName: Apply Migrations

Also, create an atlas.hcl file in the root of your Azure Repos repository with the following content:

atlas.hcl

env {
    name = atlas.env
    dev = "docker://postgres/16/dev?search_path=public" # Replace if necessary (see "Dev Database" above)
    migration {
      repo {
        name = "app" # Replace with the name of your repository in previous step
      }
    }
}

Let's break down what this pipeline does:

1. Lint on Pull Requests: The migrate lint step runs automatically whenever a pull request is opened that modifies the migrations/ directory. Atlas analyzes the new migrations for potential issues like destructive changes, backward incompatibility, or syntax errors. Because we configured the githubConnection parameter, lint results appear as a comment directly on the GitHub pull request.

2. Push to Registry: When changes are merged into the main branch, the migrate push step pushes the migration directory to Atlas Cloud's Schema Registry. This creates a versioned snapshot of your migrations that can be referenced and deployed across environments.

3. Apply to Database: The migrate apply step deploys pending migrations to your database using the connection string stored in the DB_URL secret.

Testing the workflow

Let's take our new pipeline for a spin. Assume we have an existing migration file in our repository:

migrations/20251019111_create_t1_table.sql

CREATE TABLE t1
(
  c1 serial NOT NULL,
  c2 integer NOT NULL,
  c3 integer NOT NULL,

  CONSTRAINT pk PRIMARY KEY (c1)
);

Now let's add a new migration:

1. Create a new branch in your Azure Repos repository and add a new migration locally with atlas migrate new drop_c3 --edit. Paste the following in the editor:

migrations/20251019222_drop_c3.sql

ALTER TABLE "t1" DROP COLUMN "c3";

2. Commit and push the changes to Azure Repos.

3. Create a pull request in Azure DevOps. This will automatically trigger the pipeline due to the branch policies you configured.

4. Check the lint report. Follow any instructions to fix the issues.

5. Complete the pull request to merge into the main branch. This will trigger the migrate push and migrate apply steps.

6. When the pipeline finishes running, check your database to see if the changes were applied.