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
- macOS + Linux
- Homebrew
- Docker
- Windows
- CI
- Manual Installation
To download and install the latest release of the Atlas CLI, simply run the following in your terminal:
curl -sSf https://atlasgo.sh | sh
Get the latest release with Homebrew:
brew install ariga/tap/atlas
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"
Download the latest release and move the atlas binary to a file location on your system PATH.
GitHub Actions
Use the setup-atlas action to install Atlas in your GitHub Actions workflow:
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}
Other CI Platforms
For other CI/CD platforms, use the installation script. See the CI/CD integrations for more details.
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.
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:
- Pushes the migration directory to the Schema Registry.
- Checks the database for drift every hour.
- 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):
- PostgreSQL
- MySQL
- MariaDB
- SQLite
- SQL Server
- ClickHouse
- YugabyteDB
- Azure HorizonDB
$ atlas migrate push app \
--dev-url "docker://postgres/15/dev?search_path=public"
$ atlas migrate push app \
--dev-url "docker://mysql/8/dev"
$ atlas migrate push app \
--dev-url "docker://mariadb/latest/dev"
$ atlas migrate push app \
--dev-url "sqlite://dev?mode=memory"
$ atlas migrate push app \
--dev-url "docker://sqlserver/2022-latest"
$ atlas migrate push app \
--dev-url "docker://clickhouse/23.11"
$ atlas migrate push app \
--dev-url "docker://ysql/latest/dev?search_path=public"
$ atlas migrate push app \
--dev-url "docker://postgres/17/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:
- Atlas Registry
- Local directory
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.
image: ubuntu:latest
stages:
- drift
include:
- component: $CI_SERVER_FQDN/arigaio/atlas/migrate-drift@~latest
inputs:
stage: drift
url: $DB_URL
dir: file://migrations
dev-url: "postgres://user:pass@postgres/dev?sslmode=disable"
atlas-cloud-token: $ATLAS_CLOUD_TOKEN
atlas-migrate-drift:
rules:
- if: $CI_PIPELINE_SOURCE == "schedule"
services:
- postgres:17
variables:
POSTGRES_DB: dev
POSTGRES_USER: user
POSTGRES_PASSWORD: pass
Atlas replays the migration files on the dev database to build the expected schema. The postgres
service starts an empty database for the job, and its service name is the host in dev-url.
dev-url must have the same scope as url. This DB_URL has no
search_path, so it covers the whole database, and so does the dev URL. If DB_URL sets
search_path=public, use:
postgres://user:pass@postgres/dev?search_path=public&sslmode=disable
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.
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.
-
In Build -> Pipeline schedules, click Run on the schedule. The job passes and logs:
atlas-migrate-driftpostgres://app:xxxxx@db:5432/app?sslmode=disable matches the expected state at version 20261102000000
`atlas migrate drift` completed successfully, no drift detected -
Add an index directly on the database:
CREATE INDEX orders_created_at_idx ON orders (created_at); -
Run the schedule again. The job fails and logs the drift:
atlas-migrate-driftpostgres://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 -
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:
| Result | Job log | Tests 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:
| Kind | Meaning |
|---|---|
extra | The object exists in the database but not in the expected schema. |
missing | The object is in the expected schema but not in the database. |
modified | The 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:
- It reads the last applied version from the revisions table.
- It builds the expected schema at that version.
- 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:
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:
| Variable | Value |
|---|---|
ATLAS_OUTPUT_MIGRATE_DRIFT_DRIFTED | true if any checked database drifted, otherwise false. |
ATLAS_OUTPUT_MIGRATE_DRIFT_REPORT | A 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:
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:
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:
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:
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:
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:
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:
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 withatlas migrate setinstead of running the change again.migrate setmarks 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:
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
Drift Detection for Versioned Migrations
Learn about the pre-apply check, atlas migrate drift, drift reports, and exclude patterns.
GitLab CI Components Reference
See the inputs and outputs of migrate-drift and the other Atlas GitLab CI components.
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 GitLab
Lint, push, and deploy versioned migrations with GitLab CI.