Skip to main content

Detecting Schema Drift Using GitLab CI

The migrate-drift component checks whether a database still matches its migration history. This guide runs it on a pipeline schedule, reads its results, and extends the check to more environments, tenants, and deployments.

The migrate-drift component 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:

atlas login

Creating a bot token​

Create an Atlas Cloud bot token by following these instructions. Then, in your GitLab project, go to Settings -> CI/CD -> Variables and add it as a variable named ATLAS_CLOUD_TOKEN, with its visibility set to Masked.

Storing the database URL​

Create a URL for the database to check, and add it as a variable named DB_URL:

  • Set its visibility to Masked.
  • Select Protect variable.
  • Clear Expand variable reference. While expansion is on, GitLab rejects a masked value that contains ?, and most database URLs do.

A protected variable reaches only pipelines on protected branches and tags, such as the scheduled pipelines on the default branch. Merge request pipelines from unprotected branches never receive it. Merge request pipelines between two protected branches do receive it while Allow merge request pipelines to access protected variables and runners is selected in the same Variables settings. Clear it so no merge request pipeline can read the URL.

The check only reads the database, so give it a read-only user. The user must be able to read the revisions table and inspect every object the migration directory manages. Atlas may report an object the user cannot see as missing.

Network access​

The GitLab runner must be able to reach the database. Hosted runners on GitLab.com connect over the public internet. For a database in a private network, register a self-managed runner inside that network.

info

To use the components on a self-managed GitLab instance, mirror the component project first.

Offline Access​

Atlas GitLab CI components cache the Atlas grant (.atlas) between jobs and pipelines and restore it automatically for offline continuity. No extra cache setup is needed.

Drift Detection Workflow​

atlas migrate apply reads the revisions table to know which migration files already ran. The table does not see changes made outside Atlas. An index added by hand during an incident leaves the version unchanged and the schema different. The next migration then runs against a schema it was not written for: it fails, or it succeeds and leaves a state nobody reviewed.

The pre-apply check catches this when you deploy. The migrate-drift component catches it between deployments. In this guide, the pipeline:

  1. Pushes the migration directory to the Schema Registry.
  2. Checks the database for drift every hour.
  3. Checks more environments and tenants, and runs before deployments.

Common sources of drift​

  • Manual fixes: an index or column changed during an incident, with no migration added afterward.
  • Incomplete changes: a migration marked as applied while an external schema-change tool failed before finishing the change.
  • Part of a fleet: one tenant or shard changed by hand.
  • Other tools: an ORM, a BI tool, or another migration system that creates or alters objects.

Pushing the migration directory​

The check builds the expected schema from the migration directory. From the parent directory of your migration directory, run the following command to create a repository in your Atlas Cloud organization (replace "app" with the name you want):

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

To keep the Registry current, push the directory on every merge, for example with the migrate-push component as shown in the versioned workflow guide. To check against the directory in your repository instead, use a dev database, as the next section shows.

Scheduling the check​

Create a .gitlab-ci.yml file with the following content, replacing "app" with the name of your repository:

.gitlab-ci.yml
image: ubuntu:latest

stages:
- drift

include:
- component: $CI_SERVER_FQDN/arigaio/atlas/migrate-drift@~latest
inputs:
stage: drift
url: $DB_URL
dir: atlas://app
atlas-cloud-token: $ATLAS_CLOUD_TOKEN

atlas-migrate-drift:
rules:
- if: $CI_PIPELINE_SOURCE == "schedule"

dir points to the migration directory in the Atlas Registry, which holds the expected schema for every version.

atlas-migrate-drift is the job the component adds, and GitLab merges the keys you set under that name into it. The component sets no rules of its own. Without the rule above, the job runs on every push, including pushes to branches that cannot read DB_URL.

Commit the file to the default branch. Then, in Build -> Pipeline schedules, click New schedule and set:

  • Interval Pattern: a custom pattern of 17 * * * *, to run at minute 17 of every hour.
  • Select target branch or tag: the default branch.

Hourly checks suit production: a manual fix is reported while the incident is still fresh. Daily checks are usually enough for staging.

note

A scheduled pipeline runs every job in .gitlab-ci.yml whose rules match. If the file also deploys migrations, start the rules of those jobs with if: $CI_PIPELINE_SOURCE == "schedule" and when: never, so the hourly check does not deploy.

Running the check​

Run the check on a clean database, change the database outside Atlas, and run it again.

  1. In Build -> Pipeline schedules, click Run on the schedule. The job passes and logs:

    atlas-migrate-drift
    postgres://app:xxxxx@db:5432/app?sslmode=disable matches the expected state at version 20261102000000
    `atlas migrate drift` completed successfully, no drift detected
  2. Add an index directly on the database:

    CREATE INDEX orders_created_at_idx ON orders (created_at);
  3. Run the schedule again. The job fails and logs the drift:

    atlas-migrate-drift
    postgres://app:xxxxx@db:5432/app?sslmode=disable drifted from the expected state at version 20261102000000 with 1 change(s): 1 modified
    -- modified table "public"."orders":
    -> CREATE INDEX "orders_created_at_idx" ON "public"."orders" ("created_at");
    `atlas migrate drift` detected drift in 1 database(s): postgres://app:xxxxx@db:5432/app?sslmode=disable
  4. Drop the index and run the schedule once more to confirm the database is clean.

The SQL describes the change made outside Atlas, not its fix. The password in the URL is redacted.

Results​

The pipeline's Tests tab lists each checked database as a test case named after its URL. Each database ends with one of three results:

ResultJob logTests tab
No drift<url> matches the expected state at version <version>Passed
Drift<url> drifted from the expected state at version <version> with ...Failed, with the drifted objects and the SQL
Check failed<url> could not be checked: <error>Error

The job fails on drift and on a failed check. A database that could not be checked, for example with no migration history found on the connected database, has an unknown state. The job fails on every run while the drift exists, so the schedule's pipeline history shows when the drift appeared and when it was fixed.

Each drifted object has one of three kinds:

KindMeaning
extraThe object exists in the database but not in the expected schema.
missingThe object is in the expected schema but not in the database.
modifiedThe object exists in both but differs, for example a table with an added index.

How the check works​

For each database, the component runs atlas migrate drift:

  1. It reads the last applied version from the revisions table.
  2. It builds the expected schema at that version.
  3. It inspects the database and compares the two.

Files newer than the applied version are pending, not drift, so a database several releases behind is still checked correctly. The check never changes the database.

The check takes the same lock as atlas migrate apply, so it never compares a database while a migration is running. It waits up to the lock timeout, 10 seconds by default, and then fails. The component has no input for the timeout. To wait longer, set lock_timeout in an env in atlas.hcl:

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

Then pass env: prod to the component. GitLab checks out the repository for every job, so Atlas finds atlas.hcl:

include:
- component: $CI_SERVER_FQDN/arigaio/atlas/migrate-drift@~latest
inputs:
stage: drift
url: $DB_URL
dir: atlas://app
env: prod
atlas-cloud-token: $ATLAS_CLOUD_TOKEN

Reading the Report in Later Jobs​

The component passes two outputs to later jobs:

VariableValue
ATLAS_OUTPUT_MIGRATE_DRIFT_DRIFTEDtrue if any checked database drifted, otherwise false.
ATLAS_OUTPUT_MIGRATE_DRIFT_REPORTA JSON array with one entry per checked database.

A job reads them when it lists atlas-migrate-drift in needs. That job fails on drift, so the reading job also needs when: always in its rules. GitLab still passes the outputs of a failed job to the jobs that need it.

This job prints each drifted database and its changes:

.gitlab-ci.yml
stages:
- drift
- report

drift-report:
stage: report
needs: [atlas-migrate-drift]
rules:
- if: $CI_PIPELINE_SOURCE == "schedule"
when: always
before_script:
- apt-get update -q && apt-get install -y -q jq
script:
- |
if [ "$ATLAS_OUTPUT_MIGRATE_DRIFT_DRIFTED" = true ]; then
printf '%s' "$ATLAS_OUTPUT_MIGRATE_DRIFT_REPORT" |
jq -r '.[] | select(.Drifted) | .URL, (.Changes[] | " \(.Kind) \(.Object)")'
fi

With the index from Running the check, it logs:

drift-report
postgres://app:xxxxx@db:5432/app?sslmode=disable
modified table "public"."orders"

Each report entry has the redacted URL, the applied Version, and Drifted. Each item in Changes has a Kind, an Object, and the SQL of the change in Cmds. An entry has an Error when its database could not be checked. A job like this one can use these fields to notify a chat channel or open an issue.

Checking Multiple Environments​

Staging and production often run different migration versions. Each database is checked at its own applied version, so one pipeline can check both without reporting the one behind as drifted:

.gitlab-ci.yml
image: ubuntu:latest

stages:
- drift

include:
- component: $CI_SERVER_FQDN/arigaio/atlas/migrate-drift@~latest
inputs:
stage: drift
url: $DB_URL
dir: atlas://app
atlas-cloud-token: $ATLAS_CLOUD_TOKEN

atlas-migrate-drift:
rules:
- if: $CI_PIPELINE_SOURCE == "schedule"
parallel:
matrix:
- ENVIRONMENT: [staging, production]
environment:
name: $ENVIRONMENT
action: verify

Add DB_URL once per environment, with Environment scope set to staging and to production. Each job of the matrix reads the URL of its own environment. action: verify gives the job the environment's variables without recording a deployment.

The matrix runs atlas-migrate-drift: [staging] and atlas-migrate-drift: [production] as separate jobs, so drift in staging does not stop the production check.

To read the report of each environment in a later job, run that job on the same matrix. Add these keys to drift-report:

drift-report:
parallel:
matrix:
- ENVIRONMENT: [staging, production]
needs:
- job: atlas-migrate-drift
parallel:
matrix:
- ENVIRONMENT: ['$[[ matrix.ENVIRONMENT ]]']

$[[ matrix.ENVIRONMENT ]] is a GitLab matrix expression, available in GitLab 18.6 and later. It gives each drift-report job the outputs of its own environment.

Checking Multiple Tenants​

In a database-per-tenant or schema-per-tenant setup, one tenant can drift while the rest stay correct. An Atlas env with for_each checks every tenant in one job, each at its own applied version, so tenants can be on different versions during a staged rollout.

This env finds every tenant schema 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 a database, so the query can list every tenant schema. urlsetpath then builds one URL per tenant.

Pass the env to the component instead of url and dir. Atlas reads DB_URL from the CI/CD variable of the same name:

include:
- component: $CI_SERVER_FQDN/arigaio/atlas/migrate-drift@~latest
inputs:
stage: drift
env: tenants
atlas-cloud-token: $ATLAS_CLOUD_TOKEN

Each tenant gets its own line in the job log, its own test case in the Tests tab, and its own entry in the report. A drifted tenant does not stop the others. A tenant that cannot be checked, for example because its database is unreachable, logs the error, and the remaining tenants are still checked.

To list the drifted tenants in a later job, read the report:

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

Gating Deployments​

Run the drift check in the first stage of a deployment pipeline. GitLab starts a stage only when every job in the earlier stages succeeds, so a drifted database fails the gate and the deploy jobs are skipped:

.gitlab-ci.yml
image: ubuntu:latest

stages:
- drift
- push
- apply

include:
- component: $CI_SERVER_FQDN/arigaio/atlas/migrate-drift@~latest
inputs:
job_name: atlas-drift-gate
stage: drift
url: $DB_URL
dir: atlas://app
atlas-cloud-token: $ATLAS_CLOUD_TOKEN
- component: $CI_SERVER_FQDN/arigaio/atlas/migrate-push@~latest
inputs:
stage: push
dir: file://migrations
dir-name: app
dev-url: "postgres://user:pass@postgres/dev?sslmode=disable"
atlas-cloud-token: $ATLAS_CLOUD_TOKEN
- component: $CI_SERVER_FQDN/arigaio/atlas/migrate-apply@~latest
inputs:
stage: apply
url: $DB_URL
dir: atlas://app
env: prod
atlas-cloud-token: $ATLAS_CLOUD_TOKEN

atlas-drift-gate:
rules:
- if: $CI_PIPELINE_SOURCE == "push" && $CI_COMMIT_BRANCH == $CI_DEFAULT_BRANCH

atlas-migrate-push:
rules:
- if: $CI_PIPELINE_SOURCE == "push" && $CI_COMMIT_BRANCH == $CI_DEFAULT_BRANCH
services:
- postgres:17
variables:
POSTGRES_DB: dev
POSTGRES_USER: user
POSTGRES_PASSWORD: pass

atlas-migrate-apply:
rules:
- if: $CI_PIPELINE_SOURCE == "push" && $CI_COMMIT_BRANCH == $CI_DEFAULT_BRANCH

job_name gives the gate its own job, so the same file can also run the scheduled check in atlas-migrate-drift. The push stage runs before the apply stage, so atlas://app includes the migrations from this commit. As in the local directory example, dev-url must have the same scope as DB_URL.

The new migration files are pending, not drift, so they do not fail the gate.

Enable the pre-apply check on the prod env too. It is off until the env has a check "migrate_apply" block:

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

The gate reports the drift in detail before the deploy starts. The pre-apply check runs again inside migrate apply and catches a change made after the gate. In that case atlas-migrate-apply stops before running any file and logs:

atlas-migrate-apply
database state does not match expected state at version 20261102000000

Responding to Drift​

A drift report leaves three choices:

  • Keep the change. Add it to the migration directory, by writing the migration or generating it with atlas migrate diff, and deploy it to the other environments as usual. On the database that already has the change, mark the new version as applied with atlas migrate set instead of running the change again. migrate set marks every version up to the one you select, so use it when the new migration is the only pending one.
  • Revert the change. Undo it on the database by hand. The SQL in the report shows what changed.
  • Exclude the object. Some objects are managed outside the migration directory on purpose, such as tables owned by another service, extensions installed by hand, or temporary tables of an online schema-change tool. List them in exclude, one pattern per line:
include:
- component: $CI_SERVER_FQDN/arigaio/atlas/migrate-drift@~latest
inputs:
stage: drift
url: $DB_URL
dir: atlas://app
atlas-cloud-token: $ATLAS_CLOUD_TOKEN
exclude: |
public.audit_log
public.tmp_*

DB_URL in this guide covers the whole database, so each pattern names its schema. With a URL scoped to one schema, such as one with search_path=public, drop the schema: audit_log. See Excluding Objects for the full syntax.

The exclude input applies only to this check. If you also gate deployments with the pre-apply check, set the patterns once on the env, where every check that uses it reads them:

atlas.hcl
env "prod" {
exclude = [
"public.audit_log",
"public.tmp_*",
]
check "migrate_apply" {
drift {
on_error = FAIL
}
}
}

Then pass env: prod to the component instead of exclude.

Never revert drift automatically. An index added during an incident may be what keeps an important query fast. A person should decide whether to keep, revert, or exclude each change.

Wrapping Up​

This guide used the migrate-drift component to check versioned databases for drift between deployments. The pipeline:

  • Checks production every hour and on demand, with a test case per database in the Tests tab.
  • Fails the scheduled pipeline on drift and passes a JSON report to later jobs.
  • Checks multiple environments and tenants, each at its own applied version.
  • Stops a deployment when the target database has drifted.

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

Next Steps​