Skip to main content

Database Schema Drift Detection for Versioned Migrations

Database schema drift happens when the target database diverges from the source of truth: the version-controlled schema and migration files your code was written against. For versioned migrations, Atlas detects drift at two points in time:

  • Before a deployment, with a pre-apply check that runs at the start of atlas migrate apply and blocks the migration when the database has drifted.
  • Between deployments, with atlas migrate drift, a standalone command you run on a schedule from a cron job or a CI pipeline, or by hand when you suspect a change.

Both compare the database against the state its migration directory defines at the last applied version. For continuous, agent-based detection that also covers databases without a migration directory, see Schema Monitoring.

Atlas Pro Feature

Drift detection is available to Atlas Pro users. You can create a trial account using the atlas login command.

Overview​

The declarative and versioned workflows handle planning very differently, which is why drift is particularly dangerous in versioned deployments. Declarative apply re-inspects the target database on every run and either uses a pre-planned migration for the requested transition or computes one on the fly, so drift is absorbed at plan time.

Versioned migrations move planning to dev and CI (atlas migrate diff, atlas migrate lint). At apply time, atlas migrate apply executes the next pending migration files in order, trusting the revisions table and assuming the target database has not been changed outside of Atlas since the last applied migration. When that assumption holds, deployments are fast, predictable, and deterministic. When it breaks, a migration written against an expected state can fail mid-flight, partially apply, or silently produce a different result.

The pre-apply check verifies that assumption right before a migration runs. The migrate drift command verifies it in between, so drift is found when it happens rather than at the next deploy.

Pre-Apply Check​

The pre-apply check runs at the start of atlas migrate apply and guarantees deterministic behavior at deploy time. It prevents the apply from failing mid-flight on a drifted schema.

Prerequisites​

  • Ensure your migration directory is pushed to the Atlas Registry during continuous delivery. The registry provides the expected state for each version that the drift check compares against.
  • At least one revision must already be applied. On a fresh database with no revisions, the check is skipped.

Configuration​

The check is configured inside a check "migrate_apply" block in atlas.hcl, alongside any other pre-execution checks:

atlas.hcl
env "prod" {
  url = env("DATABASE_URL")
  migration {
    dir = "atlas://my-app"
  }
  check "migrate_apply" {
    drift {
      on_error = FAIL
    }
  }
}

With this configuration, every atlas migrate apply --env prod first runs the drift check. If the target database has drifted from the expected state at the latest applied revision, the apply is aborted before any migration file runs.

How it works​

  1. Atlas reads the latest applied revision from the database's revisions table.
  2. It fetches the expected state for this revision from the Atlas Registry.
  3. It inspects the target database.
  4. It diffs the expected state against the actual state. If they differ, the migration is blocked (or a warning is emitted when on_error = CONTINUE).

Enabling on existing environments​

When enabling drift detection on an existing environment, start with on_error = CONTINUE rather than FAIL. This surfaces any pre-existing drift in the apply transcript without blocking the deployment, so the first run does not fail unexpectedly on objects that may have intentional deviations from the migration directory:

atlas.hcl
check "migrate_apply" {
  drift {
    on_error = CONTINUE
  }
}

Review the diff Atlas reports and triage:

  • Unintentional drift (someone ran an out-of-band ALTER, an old hotfix was never folded back) should be remediated by adding a migration that brings the schema back in line.
  • Intentional drift (extensions installed manually, audit or sidecar tables managed by another tool, columns added by a separate service) should be added to exclude so the drift check ignores them on every subsequent run. See Excluding Objects for patterns and the full list of use cases.

Once the diff is clean, switch on_error to FAIL to make drift a hard stop on production deployments.

Examples​

When the target database differs from the expected state at the latest revision, the apply is aborted before any migration file runs:

Output
Executing pre-execution check (1 check in total):

  -- check at atlas.hcl:10 (drift):
    -> check drift against version 20260423120000

        --- expected state
        +++ actual state
        @@ -0,0 +1,3 @@
        +CREATE TABLE "audit_log" (
        +  "id" integer NULL
        +);

    Error: database state does not match expected state at version "20260423120000"

-------------------------------------------

database state does not match expected state at version "20260423120000"

The diff is rendered in the apply transcript so the operator can see exactly which objects drifted. The migration files themselves are never executed.

Continuing on drift​

For staging or canary environments where drift may exist by design and you want visibility without a hard stop, set on_error = CONTINUE. Atlas prints the diff on every apply so the deployment log captures the divergence, but the migration proceeds.

If you share a single check "migrate_apply" block across environments, switch on atlas.env so production still aborts on drift while non-production environments only warn:

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

Background Check​

atlas migrate drift runs the same comparison as the pre-apply check, outside of a deployment. It reads the revisions table on the connected database, resolves the state the migration directory defines at the last applied version, and diffs the two. Migration files after that version are pending, not drift, and the report counts them as ignored. The command never changes the database. It takes the same advisory lock as atlas migrate apply while it reads the revisions table and inspects the schema, so an in-flight deployment is not reported as drift.

The command exits with status 0 when the database matches and status 1 when drift is found, so a cron job or a CI step fails on drift without parsing the output.

Running the command​

The expected state comes from one of two places, and the report names which one it used:

When the migration directory is an atlas:// URL, or migration.repo.name is set, the state of the applied version is fetched from the Atlas Registry by version and hash. This is the registry mode:

atlas.hcl
env "prod" {
  url = env("DATABASE_URL")
  migration {
    dir = "atlas://my-app"
  }
}
atlas migrate drift --env prod
Output
Drift Status: OK
  -- Current Version: 20260423120000
  -- Expected State:  atlas://my-app (registry)
  -- Pending Files:   0

The report​

When the database has drifted, the report lists a short diff of the two states, then every drifted object with the DDL that reproduces the change. Changes are classified from the database's point of view: extra objects exist only in the database, missing objects exist only in the expected state, and modified objects exist in both but differ. Column, index, and constraint changes are grouped under their table:

Output
Drift Status: DRIFTED
  -- Current Version: 20260423120000
  -- Expected State:  atlas://my-app (registry)
  -- Pending Files:   0
  -- Changes:         3 (1 extra, 1 missing, 1 modified)
  -- Objects:         3 tables
  -- Fingerprint:     28274a91e01d

  --- expected state (version 20260423120000)
  +++ actual state (postgres://localhost:5432/app)
  @@ -1,7 +1,8 @@
   CREATE TABLE "authors" (
     "id" integer NOT NULL,
     "name" text NOT NULL,
     "email" text NULL,
  +  "nickname" text NULL,
     PRIMARY KEY ("id")
   );
  +CREATE INDEX "authors_name" ON "authors" ("name");

The database diverged from the expected state as if the following were executed:

  -- modified table "authors":
    -> ALTER TABLE "authors" ADD COLUMN "nickname" text NULL;
    -> CREATE INDEX "authors_name" ON "authors" ("name");
  -- missing table "logs":
    -> DROP TABLE "logs";
  -- extra table "audit":
    -> CREATE TABLE "audit" ("id" integer NOT NULL, PRIMARY KEY ("id"));
Error: database state does not match expected state at version 20260423120000

The text report details up to ten changes and counts the rest. --format accepts a Go template over the report, and {{ json . }} emits the full report with every change:

atlas migrate drift --env prod --format '{{ json . }}'

The JSON carries URL, Dir, Mode, Version, Pending, Drifted, Fingerprint, a Summary with the change counts by kind and by object type, the Changes with their Cmds, and Cached. The fingerprint is a hash of the actual database state that ignores object order. It stays the same while the drift is unchanged and changes when another object drifts, which makes it a key for deduplicating alerts. The same template can be set in the env with format { migrate { drift = "..." } }.

Expected-state cache​

Both modes cache the expected state, so scheduled runs neither compute it again nor call the registry every time. A cache hit is marked on the report as (local, cached) or (registry, cached), and the JSON report carries "Cached": true. Pass --no-cache to bypass it.

The cache defaults to file://~/.atlas/cache. The cache block inside the atlas block sets its location, and any blob URL works, so one bucket can be shared between machines and CI runners:

atlas.hcl
atlas {
  cache {
    dir = "s3://my-bucket/atlas-cache?region=us-east-1"
  }
}

Flags​

FlagEnv attributeNotes
--urlurlRequired. The database to check.
--dev-urldevDev database, required for a local directory.
--dirmigration.dirDefaults to file://migrations.
--dir-formatAs in atlas migrate apply.
--revisions-schemaAs in atlas migrate apply.
--excludemigration.exclude, else excludeSee Excluding Objects.
--formatformat.migrate.driftGo template for the report.
--lock-timeoutmigration.lock_timeoutDefaults to 10s.
--lock-namemigration.lock_nameAdvisory lock name shared with migrate apply.
--skip-lockmigration.skip_lockSkip the advisory lock.
--no-cacheBypass the expected-state cache.

Excluding Objects​

exclude lets you ignore database objects that intentionally live outside the migration scope. Common examples:

  • Extensions installed manually (PostGIS, pgcrypto, vector indexes for an external service).
  • Audit, log, or sidecar tables maintained by a separate service.
  • Schemas owned by another team or another tool.
  • Temporary or test objects created by jobs that run between migrations.

Without exclude, every one of these would be reported as drift on every run. The pre-apply check reads the patterns from drift.exclude. The migrate drift command reads them from --exclude, falling back to migration.exclude and then to env.exclude. The revisions table and its containing schema are excluded automatically in both.

The pattern format depends on the URL scope of env.url:

When the URL points to a single schema (e.g., in PostgreSQL with search_path set), each pattern matches an object name within that schema:

atlas.hcl
check "migrate_apply" {
  drift {
    exclude = [
      "audit_log",                  // a single table
      "monitoring_*",               // tables matching a prefix
      "t*[type=table]",             // all tables matching a prefix
      "*[type=policy|function]",    // all policies and functions
    ]
  }
}

For the full glob syntax and [type=...] selectors, see the --exclude flag reference.

When drift.exclude is set, it replaces env.exclude for the pre-apply check rather than extending it. When unset, env.exclude is used as the fallback.

See Also​