Skip to main content

Detecting Schema Drift Using GitHub Actions

GitHub Actions is a CI/CD platform integrated with GitHub repositories. It runs workflows on events such as pushes and pull requests, or on a schedule.

This guide uses GitHub Actions and the ariga/atlas-action/migrate/drift action to check databases managed with the versioned migrations workflow for schema drift.

Atlas Pro Feature

The migrate/drift action requires Atlas Pro. To create an account or start a trial, run atlas login.

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​

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 the URL documentation.

Create another secret named DB_URL and populate it with the URL of your database.

The database user in this URL must be able to read the revisions table and inspect all objects managed by the migration directory. If the user cannot see an object, Atlas may report it as missing.

Network access​

The GitHub Actions runner must be able to reach the database. GitHub-hosted runners connect over the public internet. For databases in a private network, use a self-hosted runner inside that network.

Offline Access​

ariga/setup-atlas caches the Atlas grant (~/.atlas) between runs and restores it automatically. This ensures Atlas Cloud is not a single point of failure in your GitHub workflow, keeping your pipelines operational even without connectivity to Atlas Cloud.

No extra cache configuration is required beyond using ariga/setup-atlas with cloud-token.

Drift Detection Workflow​

atlas migrate apply uses the revisions table to know which migrations have already run. It applies the remaining migration files in order.

The revisions table cannot tell if someone changed the database outside Atlas. For example, someone may manually add an index during an incident. The revisions table still shows the same migration version, but the real database schema is now different from the expected schema.

The next migration may then fail because it was written for a different schema. Even worse, it may succeed and leave the database in a state that was never reviewed.

The pre-apply check protects deployments from this problem, but it only runs when you deploy. The migrate/drift action runs the same type of check between deployments.

In this guide, the pipeline will:

  1. Push the migration directory to the Schema Registry.
  2. Check the database for drift every hour.
  3. Track each drift in a GitHub issue and notify Slack.
  4. Extend the check to multiple environments and tenants, and run it before deployments.

Common sources of drift​

  • Manual fixes. Someone adds an index or changes a column during an incident, but no migration is added afterward.
  • Incomplete changes. A migration is marked as applied, but an external schema-change tool fails before the real database change finishes.
  • Changes to only part of a fleet. One tenant or shard is changed manually and no longer matches the others.
  • Other tools changing the database. An ORM, BI tool, or another migration system creates or changes database objects.

Pushing a migration directory to Atlas Cloud​

The drift check builds the expected schema from the migration directory. 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"

To keep the Registry up to date, push the directory on every merge, for example with the migrate/push action as shown in the versioned workflow guide.

You can also check against a local migration directory with a dev database, as shown in the next section.

Setting up GitHub Actions​

Create a .github/workflows/drift.yml file with the following content. Remember to replace "app" with the real name of your repository.

.github/workflows/drift.yml
name: Schema Drift
on:
  schedule:
    - cron: '17 * * * *'
  workflow_dispatch:
jobs:
  drift:
    runs-on: ubuntu-latest
    steps:
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
      - uses: ariga/atlas-action/migrate/drift@v1
        with:
          url: ${{ secrets.DB_URL }}
          dir: atlas://app

Here, dir points to the migration directory in the Atlas Registry. Atlas uses the Registry to find the expected schema for the database's applied version.

This file does three things:

  1. The schedule trigger runs the check every hour. GitHub can delay scheduled workflows when its runners are busy. The start of each hour is especially busy, so this example runs at minute 17.
  2. The workflow_dispatch trigger adds a Run workflow button, so you can run the check manually after a restore, failover, incident fix, or other database change.
  3. The migrate/drift step compares the database with the expected schema at its last applied migration. If they are different, the step fails.

Choose the schedule based on how quickly you want to know about drift. Hourly checks work well for production because a manual incident fix can be found while the incident is still fresh. Daily checks may be enough for staging.

How the check works​

The action runs atlas migrate drift for each database. It does three things:

  1. Reads the last applied migration version from the revisions table.
  2. Builds the expected schema for that version.
  3. Inspects the real database and compares it with the expected schema.

Only the schema at the applied version is compared. Newer migration files are considered pending, not drift. This also means a database that is several releases behind can still be checked correctly.

The action only reads the database. It does not change the schema.

It also takes the same database lock as atlas migrate apply, so it never compares a database while a migration is running. If a migration holds the lock, the check waits up to the lock timeout, 10 seconds by default, and then fails with Drift Check Failed.

The action has no input for the lock timeout. To wait longer, set lock_timeout in the migration block of an env in atlas.hcl:

atlas.hcl
env "prod" {
  migration {
    lock_timeout = "5m"
  }
}

Then check out the repository so Atlas can read atlas.hcl, and pass env: prod to the action:

      - uses: actions/checkout@v7
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
      - uses: ariga/atlas-action/migrate/drift@v1
        with:
          url: ${{ secrets.DB_URL }}
          dir: atlas://app
          env: prod

Job summary​

The job summary shows one section for each database, headed with its status:

  • No Drift Detected
  • Drift Detected
  • Drift Check Failed

When drift is found, Atlas lists the changed objects:

ChangeMeaning
extraThe object exists in the database but should not.
missingThe object is expected but does not exist in the database.
modifiedThe object exists in both places, but its definition differs, for example a table with an added index.

The summary also shows SQL that reproduces the drift. Each drifted database gets a warning annotation in the workflow run.

Outputs​

The step fails if drift is found or if a database cannot be checked.

Before failing, it sets these outputs so later steps can use them. If Atlas exits before checking any database, for example because the login failed, the outputs are empty.

OutputValue
drifted"true" if any checked database has drift.
reportA JSON array with one entry per checked database: its redacted URL, applied Version, Drifted, the drifted Changes with their SQL, and an Error if the check failed.

To tell a new drift from one you already reported, use a key computed from the drifted Changes, as the issue workflow does.

Testing the workflow​

Run the workflow, introduce drift, and check that the action reports it.

  1. Commit and push .github/workflows/drift.yml to the main branch.
  2. In GitHub, open the Actions tab, select Schema Drift, and click Run workflow. The run should pass, and the job summary should show No Drift Detected.
  3. Make a change directly on the database, outside Atlas. For example, add an index to one of the tables managed by the migration directory.
  4. Run the workflow again. The run fails, and the job summary shows Drift Detected with the table listed as modified.
  5. Drop the index and run the workflow again to confirm the database is clean.

Tracking Drift in GitHub Issues​

A failed workflow run is useful for detecting drift, but not for tracking it.

If the check runs every hour, it fails every hour until the drift is fixed. Instead, you can create one GitHub issue when drift first appears, keep it open while the drift exists, and close it when the database is clean again.

A key computed from the drifted objects makes this possible. The workflow hashes the kind, object, and SQL of each change, sorted by kind and object. The key stays the same while the drift does, even after a deployment changes the version and the rest of the schema.

Create a schema-drift label in the repository before running this workflow:

.github/workflows/drift.yml
name: Schema Drift
on:
  schedule:
    - cron: '17 * * * *'
  workflow_dispatch:
concurrency:
  group: schema-drift
  cancel-in-progress: false
permissions:
  contents: read
  issues: write
jobs:
  drift:
    runs-on: ubuntu-latest
    env:
      GH_TOKEN: ${{ github.token }}
      GH_REPO: ${{ github.repository }}
      RUN_URL: ${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}
    steps:
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
      - id: drift
        uses: ariga/atlas-action/migrate/drift@v1
        with:
          url: ${{ secrets.DB_URL }}
          dir: atlas://app
      - name: Open an issue for new drift
        id: issue
        if: failure() && steps.drift.outputs.drifted == 'true'
        env:
          REPORT: ${{ steps.drift.outputs.report }}
        run: |
          key=$(jq -c '.[0].Changes | map({Kind, Object, Cmds}) | sort_by(.Kind, .Object)' <<< "$REPORT" | sha256sum | cut -c1-12)
          if gh issue list --label schema-drift --json title --jq '.[].title' | grep -qF "$key"; then
            exit 0
          fi
          {
            echo "Production drifted from version $(jq -r '.[0].Version' <<< "$REPORT"):"
            echo
            jq -r '.[0].Changes[] | "- \(.Kind) \(.Object)"' <<< "$REPORT"
            echo
            echo "Report: $RUN_URL"
          } > body.md
          url=$(gh issue create --label schema-drift --title "Schema drift in production ($key)" --body-file body.md)
          echo "url=$url" >> "$GITHUB_OUTPUT"
      - name: Close resolved drift issues
        if: success()
        run: |
          issues=$(gh issue list --label schema-drift --json number --jq '.[].number')
          for n in $issues; do
            gh issue close "$n" --comment "No drift detected: $RUN_URL"
          done

REPORT is passed through an environment variable instead of being inserted directly into the shell script because object names come from the database.

Setting permissions turns off every permission not listed. contents: read keeps actions/checkout working if you add it, for example to read atlas.hcl.

The concurrency group runs one check at a time. Listing issues and creating one are separate steps, so two overlapping runs, such as a scheduled run and a manual one, could otherwise both miss the key and open duplicate issues.

The workflow handles each result like this:

ResultAction
New driftOpen an issue with the changed objects and a link to the workflow run.
Same driftKeep the existing issue, including after a deployment.
Different driftOpen a new issue because the key changed. The earlier issue stays open.
No driftClose all open drift issues.
Check failedLeave existing issues open and fail the job.

A failed check never closes an issue. This prevents a check error from being mistaken for a clean database.

The workflow continues to fail while drift exists, so the run history also shows when the drift started and when it was fixed.

Notifying Slack​

Create a Slack incoming webhook and store its URL in a repository secret named SLACK_WEBHOOK_URL.

Add this step after Open an issue for new drift:

      - name: Notify Slack
        if: failure() && steps.issue.outputs.url != ''
        uses: slackapi/slack-github-action@v4.0.0
        with:
          webhook: ${{ secrets.SLACK_WEBHOOK_URL }}
          webhook-type: incoming-webhook
          payload: |
            text: "Schema drift detected in production: ${{ steps.issue.outputs.url }}"

Because the drift step failed earlier, the Slack step needs failure() in its condition.

Audit trail​

Closed drift issues give you a history of changes made outside your migration workflow. They show when drift was found and when it was resolved.

This can also provide evidence for change-management controls. SOC 2 and ISO/IEC 27002 audits commonly require organizations to show that unexpected changes are detected and investigated.

See Detecting and Preventing Database Schema Drift for more about compliance requirements.

Checking Multiple Environments​

Staging and production often run different migration versions.

A simple comparison with the latest migration files would report staging or production as drifted whenever it is behind. The drift action avoids this by checking each database against its own applied version.

That means one workflow can safely check both environments:

.github/workflows/drift.yml
name: Schema Drift
on:
  schedule:
    - cron: '17 * * * *'
  workflow_dispatch:
jobs:
  drift:
    strategy:
      fail-fast: false
      matrix:
        environment: [staging, production]
    runs-on: ubuntu-latest
    environment:
      name: ${{ matrix.environment }}
      deployment: false
    steps:
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
      - uses: ariga/atlas-action/migrate/drift@v1
        with:
          url: ${{ secrets.DB_URL }}
          dir: atlas://app

Each GitHub environment stores its own DB_URL secret.

deployment: false lets the job use the environment's secrets without creating a deployment record for every drift check.

fail-fast: false makes sure drift in staging does not cancel the production check.

Required reviewers and wait timers still apply when deployment: false is used, and an environment with a custom deployment protection rule fails the job. If production deployments require approval, consider keeping the drift check's database secret in a separate environment.

If you track drift with GitHub issues, use a separate label for each environment, for example schema-drift-${{ matrix.environment }}, and replace "production" in the issue title and body with ${{ matrix.environment }}. Then a clean staging check closes only staging issues.

Checking Multiple Tenants​

In a database-per-tenant or schema-per-tenant setup, one tenant can drift while all others stay correct.

For example, someone may manually add an index to a large tenant to fix a performance problem. That tenant now has a different schema from the rest of the fleet.

An Atlas env using for_each can check all tenants in one action.

Each tenant is checked against its own applied migration version, so tenants can also be on different versions during a staged rollout.

This example finds all tenant schemas on a MySQL server:

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

data "sql" "tenants" {
  url   = var.url
  query = <<EOS
SELECT `schema_name`
  FROM `information_schema`.`schemata`
  WHERE `schema_name` LIKE 'tenant_%'
EOS
}

env "tenants" {
  for_each = toset(data.sql.tenants.values)
  url      = urlsetpath(var.url, each.value)
  migration {
    dir = "atlas://app"
  }
}

DB_URL points to the MySQL server without selecting a database. This lets the query list all tenant schemas.

urlsetpath then creates one database URL for each tenant schema.

The workflow checks out the repository so Atlas can read atlas.hcl, then passes the env name to the action:

      - uses: actions/checkout@v7
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
      - uses: ariga/atlas-action/migrate/drift@v1
        with:
          env: tenants
        env:
          DB_URL: ${{ secrets.DB_URL }}

Finding drift in one tenant does not stop the remaining checks.

Each tenant gets its own section in the job summary and its own entry in report. For example, with the report output in REPORT, as in the issue workflow above, this command lists the drifted tenant URLs:

jq -r '.[] | select(.Drifted) | .URL' <<< "$REPORT"

If a tenant cannot be checked, for example because its database is unreachable, its section shows Drift Check Failed with the error, and its report entry has an Error. The remaining tenants are still checked, and the step fails.

Gating Deployments​

You can also run a drift check before deployment.

If drift exists, the drift job fails and the deploy job never starts:

.github/workflows/deploy.yml
name: Deploy
on:
  push:
    branches: [master]
jobs:
  drift:
    runs-on: ubuntu-latest
    steps:
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
      - uses: ariga/atlas-action/migrate/drift@v1
        with:
          url: ${{ secrets.DB_URL }}
          dir: atlas://app
  deploy:
    needs: drift
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v7
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
      - uses: ariga/atlas-action/migrate/push@v1
        with:
          dir: file://migrations
          dir-name: app
          dev-url: docker://postgres/17/dev
      - uses: ariga/atlas-action/migrate/apply@v1
        with:
          url: ${{ secrets.DB_URL }}
          dir: atlas://app
          env: prod

The deploy job pushes the migration directory before applying it, so atlas://app includes the migrations from this commit. As in the local directory example, the dev-url must use the same scope as DB_URL.

Pending migration files are not considered drift, so migrations that are about to be deployed do not cause the drift job to fail.

Enable the pre-apply check on the prod env as well. The check is off until the env has a check "migrate_apply" block:

atlas.hcl
env "prod" {
  check "migrate_apply" {
    drift {
      on_error = FAIL
    }
  }
}

The first drift job gives you a clear drift report before deployment. The pre-apply check runs again inside migrate apply and protects against changes made between the drift check and the migration.

Responding to Drift​

When Atlas finds drift, you normally have three choices:

  • Keep the change. Add the change to your migration directory, either by writing the migration or generating one with atlas migrate diff. Deploy it to the other environments normally. On the database where the change already exists, use atlas migrate set to mark the new version as applied instead of running the same change again. migrate set marks every version up to the selected version as applied, so use it when the new migration is the only pending one.
  • Revert the change. Undo the manual database change. The SQL in the drift report shows what changed and can help you understand what needs to be reversed.
  • Exclude the object. Some objects are intentionally managed outside your migration directory, such as tables from another service, manually installed extensions, or temporary tables from an online schema-change tool. Add these objects to exclude.

For example, to exclude audit_log and the temporary tables gh-ost creates:

      - uses: ariga/atlas-action/migrate/drift@v1
        with:
          url: ${{ secrets.DB_URL }}
          dir: atlas://app
          exclude: |
            audit_log
            _*_gho
            _*_ghc
            _*_del

These patterns match object names when the database URL is scoped to one schema.

If the URL covers the whole database, include the schema name as described in Excluding Objects.

The action's exclude input applies only to the drift check. If you also gate deployments with the pre-apply check, set the patterns once with exclude on the env. Both checks read it:

atlas.hcl
env "prod" {
  exclude = [
    "audit_log",
    "_*_gho",
    "_*_ghc",
    "_*_del",
  ]
  check "migrate_apply" {
    drift {
      on_error = FAIL
    }
  }
}

Then pass env: prod to the drift action instead of exclude, and check out the repository so Atlas can read atlas.hcl:

      - uses: actions/checkout@v7
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
      - uses: ariga/atlas-action/migrate/drift@v1
        with:
          url: ${{ secrets.DB_URL }}
          dir: atlas://app
          env: prod

Do not automatically revert drift.

A manual index added during an incident, for example, may be the change keeping an important query fast. A person should review the drift and decide whether to keep, revert, or exclude it.

Wrapping up​

This guide used GitHub Actions with Atlas to check versioned databases for schema drift between deployments. The pipeline:

  • Checks production for drift every hour and on demand.
  • Tracks each drift in one GitHub issue and notifies Slack when a new drift appears.
  • Checks multiple environments and tenants, each at its own applied version.
  • Stops a deployment when the target database has drifted.

For more information on drift detection, see the Drift Detection documentation.

Next Steps​

Drift Detection for Versioned Migrations

Learn about the pre-apply check, atlas migrate drift, drift reports, and exclude patterns.

GitHub Actions Reference

See the inputs and outputs of migrate/drift and the other Atlas GitHub Actions.

Schema Monitoring

Monitor databases that do not use a migration directory.

Drift Detection and Compliance

Use drift detection as a change-management control for SOC 2, ISO/IEC 27002, and PCI DSS.

Deploying to Multiple Tenants

Deploy and check migrations across tenant databases with for_each.

CI/CD on GitHub Actions

Lint, push, and deploy versioned migrations with GitHub Actions.