Database

Atlas Schema Testing: Automated Schema Change Validation

HelpMeTest

HelpMeTest

— 10 min read
Atlas Schema Testing: Automated Schema Change Validation

Atlas brings declarative schema management to database migrations, letting you define what your schema should look like rather than how to get there. This post covers automated schema validation with atlas schema diff, lint-driven migration safety checks, CI/CD pipeline integration, and drift detection to keep your database and codebase in sync.

Key Takeaways

Declare desired state, let Atlas compute the diff. Unlike script-based tools, Atlas compares your HCL schema definition to the current database state and generates the migration SQL automatically — eliminating a class of human error.

atlas migrate lint catches breaking changes before merge. The linter flags destructive operations (DROP COLUMN, DROP TABLE), missing rollback support, and constraint violations before any SQL touches a database.

Schema drift is a silent killer. Ad-hoc changes applied directly to production without going through your migration pipeline will diverge from your HCL definitions. Atlas drift detection finds these mismatches automatically.

Why Atlas Takes a Different Approach to Schema Management

Most migration tools — Flyway, Liquibase, Goose, Alembic — are imperative. You write the SQL or DSL commands that transform the database from state A to state B. You're responsible for making those commands correct, reversible, and safe.

Atlas inverts this. You declare what your schema should look like using HCL (HashiCorp Configuration Language) or SQL schema definitions, and Atlas computes the migration SQL by diffing the desired state against the actual database state. This is the "infrastructure as code" model applied to databases.

The implications for testing are significant. Instead of testing that your migration SQL is syntactically correct and logically sound, you test that your schema definition accurately describes your intended schema — and you let Atlas prove it by computing and validating the diff.

Atlas Schema Definitions

Atlas schemas are written in HCL files:

# schema.hcl
schema "public" {}

table "users" {
  schema = schema.public

  column "id" {
    type = bigint
    null = false
  }

  column "email" {
    type    = varchar(255)
    null    = false
  }

  column "subscription_tier" {
    type    = varchar(20)
    null    = false
    default = "free"
  }

  column "created_at" {
    type    = timestamptz
    null    = false
    default = sql("CURRENT_TIMESTAMP")
  }

  primary_key {
    columns = [column.id]
  }

  index "idx_users_email" {
    columns = [column.email]
    unique  = true
  }
}

table "orders" {
  schema = schema.public

  column "id" {
    type = bigint
    null = false
  }

  column "user_id" {
    type = bigint
    null = false
  }

  column "amount" {
    type      = numeric(10, 2)
    null      = false
  }

  column "status" {
    type    = varchar(20)
    null    = false
    default = "pending"
  }

  primary_key {
    columns = [column.id]
  }

  foreign_key "fk_orders_users" {
    columns     = [column.user_id]
    ref_columns = [table.users.column.id]
    on_delete   = RESTRICT
    on_update   = CASCADE
  }
}

Atlas also supports SQL schema definitions if you prefer:

-- schema.sql
CREATE TABLE users (
    id              BIGINT          NOT NULL,
    email           VARCHAR(255)    NOT NULL,
    subscription_tier VARCHAR(20)   NOT NULL DEFAULT 'free',
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX idx_users_email ON users (email);

CREATE TABLE orders (
    id      BIGINT          NOT NULL,
    user_id BIGINT          NOT NULL,
    amount  NUMERIC(10, 2)  NOT NULL,
    status  VARCHAR(20)     NOT NULL DEFAULT 'pending',
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

atlas schema diff: Validating Schema Changes

atlas schema diff compares two schemas and outputs the migration SQL needed to go from one to the other. The two schemas can be a file and a live database, two files, or two database connections.

Diff Against a Live Database

# Compare your HCL definition to the current database state
atlas schema diff \
  --from <span class="hljs-string">"postgres://testuser:testpass@localhost:5432/testdb" \
  --to <span class="hljs-string">"file://schema.hcl" \
  --dev-url <span class="hljs-string">"docker://postgres/16/dev"

Output example:

-- Modify "users" table
ALTER TABLE "public"."users"
  ADD COLUMN "phone_number" character varying(20) NULL;

-- Create index "idx_users_phone" to table "users"
CREATE INDEX "idx_users_phone" ON "public"."users" ("phone_number");

If there is no diff, the command exits 0 with no output — a clean signal for CI.

Diff Between Two Schema Definitions

You can diff between a baseline schema and your updated schema to see what a proposed change does:

atlas schema diff \
  --from "file://schema-v1.hcl" \
  --to <span class="hljs-string">"file://schema-v2.hcl" \
  --dev-url <span class="hljs-string">"docker://postgres/16/dev"

This is the core of schema change review in pull requests. Instead of reading migration SQL and reasoning about what it does, reviewers see a precise diff of schema definitions.

atlas migrate lint: Catching Breaking Changes

atlas migrate lint analyzes a set of migration files and flags operations that are risky or destructive. This runs before any migration reaches a database.

First, generate migration files from your schema diff:

# Initialize the migrations directory
atlas migrate diff \
  --to <span class="hljs-string">"file://schema.hcl" \
  --dev-url <span class="hljs-string">"docker://postgres/16/dev" \
  --<span class="hljs-built_in">dir <span class="hljs-string">"file://migrations"

This creates:

migrations/
  20240515120000_create_users.sql
  20240515120001_create_orders.sql
  atlas.sum             # integrity hash file

Now lint the migrations directory:

atlas migrate lint \
  --dir <span class="hljs-string">"file://migrations" \
  --dev-url <span class="hljs-string">"docker://postgres/16/dev" \
  --latest 1             <span class="hljs-comment"># lint only the most recent migration

Atlas lint detects:

Destructive changes — dropping tables or columns:

Error: data dependent changes detected:
  L5: Dropping non-virtual column "email" from table "users" is not backward compatible

Non-concurrent index creation — which locks tables in PostgreSQL:

Warning: Creating index "idx_users_email" non-concurrently locks the table for writes.
  Consider using CONCURRENTLY: CREATE INDEX CONCURRENTLY

Constraint additions on populated tables:

Error: Adding a NOT NULL constraint to existing column "phone_number" in "users"
  may fail if there are existing NULL values

Configure lint behavior in atlas.hcl:

# atlas.hcl
lint {
  destructive {
    error = true    # fail on DROP TABLE, DROP COLUMN
  }
  incompatible {
    error = true    # fail on backward-incompatible changes
  }
  data_depend {
    error = true    # fail on changes that may fail due to existing data
  }
}

Testing Schema Definitions Against a Real Database

For integration testing, Atlas provides a Go SDK that can be used directly in test code. But for most teams, the CLI-based approach with Docker is more practical.

Here's a bash-based test harness that you can run in CI:

#!/bin/bash
<span class="hljs-comment"># test-schema.sh

<span class="hljs-built_in">set -euo pipefail

SCHEMA_FILE=<span class="hljs-string">"schema.hcl"
DEV_URL=<span class="hljs-string">"docker://postgres/16/dev"
TARGET_URL=<span class="hljs-string">"postgres://testuser:testpass@localhost:5432/testdb"

<span class="hljs-built_in">echo <span class="hljs-string">"=== Step 1: Validate schema HCL syntax ==="
atlas schema inspect \
  --url <span class="hljs-string">"$DEV_URL" \
  --format <span class="hljs-string">'{{ . }}' > /dev/null
<span class="hljs-built_in">echo <span class="hljs-string">"HCL syntax valid."

<span class="hljs-built_in">echo <span class="hljs-string">"=== Step 2: Apply schema to test database ==="
atlas schema apply \
  --url <span class="hljs-string">"$TARGET_URL" \
  --to <span class="hljs-string">"file://$SCHEMA_FILE" \
  --dev-url <span class="hljs-string">"$DEV_URL" \
  --auto-approve

<span class="hljs-built_in">echo <span class="hljs-string">"=== Step 3: Verify no drift after apply ==="
DIFF=$(atlas schema diff \
  --from <span class="hljs-string">"$TARGET_URL" \
  --to <span class="hljs-string">"file://$SCHEMA_FILE" \
  --dev-url <span class="hljs-string">"$DEV_URL")

<span class="hljs-keyword">if [ -n <span class="hljs-string">"$DIFF" ]; <span class="hljs-keyword">then
  <span class="hljs-built_in">echo <span class="hljs-string">"ERROR: Schema drift detected after apply:"
  <span class="hljs-built_in">echo <span class="hljs-string">"$DIFF"
  <span class="hljs-built_in">exit 1
<span class="hljs-keyword">fi

<span class="hljs-built_in">echo <span class="hljs-string">"=== Step 4: Lint migration files ==="
atlas migrate lint \
  --<span class="hljs-built_in">dir <span class="hljs-string">"file://migrations" \
  --dev-url <span class="hljs-string">"$DEV_URL" \
  --latest 5

<span class="hljs-built_in">echo <span class="hljs-string">"All schema tests passed."

For a more structured test setup using Docker Compose:

# docker-compose.test.yml
version: "3.9"
services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: testuser
      POSTGRES_PASSWORD: testpass
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "testuser", "-d", "testdb"]
      interval: 5s
      timeout: 3s
      retries: 10

  schema-test:
    image: arigaio/atlas:latest
    depends_on:
      db:
        condition: service_healthy
    volumes:
      - ./:/workspace
    working_dir: /workspace
    command: >
      schema apply
        --url "postgres://testuser:testpass@db:5432/testdb"
        --to "file://schema.hcl"
        --dev-url "docker://postgres/16/dev"
        --auto-approve

Run with:

docker compose -f docker-compose.test.yml up --exit-code-from schema-test

Drift Detection

Schema drift happens when someone applies a change directly to the database — via psql, a database GUI, or an emergency hotfix — without going through the migration pipeline. The schema in production diverges from your HCL definition, and the next migration may conflict with the undocumented change.

Atlas detects drift by comparing the live database to your schema definition:

#!/bin/bash
<span class="hljs-comment"># check-drift.sh

SCHEMA_FILE=<span class="hljs-string">"schema.hcl"
PROD_URL=<span class="hljs-string">"postgres://produser:prodpass@prod-db:5432/proddb"
DEV_URL=<span class="hljs-string">"docker://postgres/16/dev"

DRIFT=$(atlas schema diff \
  --from <span class="hljs-string">"$PROD_URL" \
  --to <span class="hljs-string">"file://$SCHEMA_FILE" \
  --dev-url <span class="hljs-string">"$DEV_URL" 2>&1)

<span class="hljs-keyword">if [ -n <span class="hljs-string">"$DRIFT" ]; <span class="hljs-keyword">then
  <span class="hljs-built_in">echo <span class="hljs-string">"SCHEMA DRIFT DETECTED in production:"
  <span class="hljs-built_in">echo <span class="hljs-string">"$DRIFT"

  <span class="hljs-comment"># Send to Slack, PagerDuty, or your alerting system
  curl -s -X POST <span class="hljs-string">"$SLACK_WEBHOOK_URL" \
    -H <span class="hljs-string">"Content-Type: application/json" \
    -d <span class="hljs-string">"{\"text\": \"Schema drift detected:\\n\`\`\`${DRIFT}\`\`\`\"}"

  <span class="hljs-built_in">exit 1
<span class="hljs-keyword">fi

<span class="hljs-built_in">echo <span class="hljs-string">"No schema drift detected."

Run this as a daily cron job in CI:

# .github/workflows/schema-drift-check.yml
name: Schema Drift Detection

on:
  schedule:
    - cron: '0 8 * * *'  # Every day at 8am UTC
  workflow_dispatch:

jobs:
  drift-check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Install Atlas
        run: |
          curl -sSf https://atlasgo.sh | sh

      - name: Check for schema drift
        run: bash scripts/check-drift.sh
        env:
          PROD_DB_URL: ${{ secrets.PROD_DB_URL }}
          SLACK_WEBHOOK_URL: ${{ secrets.SLACK_WEBHOOK_URL }}

Migration File Integrity

Atlas maintains a atlas.sum file alongside your migrations directory. This is a hash of all migration files, ensuring that migration files cannot be edited after they've been committed — similar to Flyway's checksum mechanism, but cryptographically stronger.

# Verify migration integrity
atlas migrate validate \
  --<span class="hljs-built_in">dir <span class="hljs-string">"file://migrations" \
  --dev-url <span class="hljs-string">"docker://postgres/16/dev"

If any migration file has been edited:

Error: checksum mismatch for migration file "20240515120000_create_users.sql"
  expected: sha256:abc123...
  got:      sha256:def456...
  Run "atlas migrate hash" to recalculate the checksum file.

The integrity check should run in CI before any migration is applied. If the checksum fails, the pipeline fails.

# In CI, fail if integrity check fails
atlas migrate validate --<span class="hljs-built_in">dir <span class="hljs-string">"file://migrations" --dev-url <span class="hljs-string">"docker://postgres/16/dev" <span class="hljs-pipe">|| {
  <span class="hljs-built_in">echo <span class="hljs-string">"Migration integrity check failed — migration files may have been edited"
  <span class="hljs-built_in">exit 1
}

Full CI/CD Integration

Here is a complete GitHub Actions workflow for Atlas schema testing:

name: Atlas Schema Tests

on:
  push:
    paths:
      - 'schema.hcl'
      - 'migrations/**'
  pull_request:
    paths:
      - 'schema.hcl'
      - 'migrations/**'

jobs:
  schema-tests:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:16-alpine
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: testuser
          POSTGRES_PASSWORD: testpass
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4

      - name: Install Atlas
        uses: ariga/setup-atlas@v0
        with:
          cloud-token: ${{ secrets.ATLAS_CLOUD_TOKEN }}  # optional, for Atlas Cloud

      - name: Validate migration integrity
        run: |
          atlas migrate validate \
            --dir "file://migrations" \
            --dev-url "docker://postgres/16/dev"

      - name: Lint migrations for breaking changes
        run: |
          atlas migrate lint \
            --dir "file://migrations" \
            --dev-url "docker://postgres/16/dev" \
            --latest 1 \
            --format "{{ range .Files }}{{ range .Reports }}{{ .Text }}\n{{ end }}{{ end }}"

      - name: Apply migrations to test database
        run: |
          atlas migrate apply \
            --url "postgres://testuser:testpass@localhost:5432/testdb" \
            --dir "file://migrations"

      - name: Verify no drift after migration
        run: |
          DIFF=$(atlas schema diff \
            --from "postgres://testuser:testpass@localhost:5432/testdb" \
            --to "file://schema.hcl" \
            --dev-url "docker://postgres/16/dev")

          if [ -n "$DIFF" ]; then
            echo "FAIL: Schema drift detected after migration apply:"
            echo "$DIFF"
            exit 1
          fi

          echo "PASS: Schema matches definition after migration"

      - name: Comment PR with schema diff
        if: github.event_name == 'pull_request'
        uses: actions/github-script@v7
        with:
          script: |
            const { execSync } = require('child_process');
            const diff = execSync(`
              atlas schema diff \
                --from "postgres://testuser:testpass@localhost:5432/testdb" \
                --to "file://schema.hcl" \
                --dev-url "docker://postgres/16/dev" \
                --format "{{ sql . }}" || echo "No changes"
            `).toString();

            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: `## Schema Diff\n\`\`\`sql\n${diff}\n\`\`\``
            });

Atlas Cloud: Centralized Schema Registry

Atlas Cloud (cloud.ariga.io) provides a centralized schema registry, migration history, and team collaboration features. Once connected, you can push schema state from CI:

# Push current migration state to Atlas Cloud
atlas migrate push myapp \
  --<span class="hljs-built_in">dir <span class="hljs-string">"file://migrations" \
  --dev-url <span class="hljs-string">"docker://postgres/16/dev"

Atlas Cloud stores the full migration history, shows diffs between versions, and can gate deployments based on lint results. For teams managing multiple database environments (dev, staging, prod), the centralized registry eliminates the "which migrations ran where" question.

Testing Multi-Schema Setups

If your application uses multiple PostgreSQL schemas (for multi-tenancy or service separation), Atlas handles this natively:

# multi-schema.hcl
schema "app" {}
schema "reporting" {}

table "users" {
  schema = schema.app
  # ... columns
}

table "user_metrics" {
  schema = schema.reporting
  # ... columns
}

Test each schema independently or together:

# Apply only the app schema
atlas schema apply \
  --url <span class="hljs-string">"postgres://testuser:testpass@localhost:5432/testdb" \
  --to <span class="hljs-string">"file://multi-schema.hcl" \
  --dev-url <span class="hljs-string">"docker://postgres/16/dev" \
  --schema app

<span class="hljs-comment"># Apply all schemas
atlas schema apply \
  --url <span class="hljs-string">"postgres://testuser:testpass@localhost:5432/testdb" \
  --to <span class="hljs-string">"file://multi-schema.hcl" \
  --dev-url <span class="hljs-string">"docker://postgres/16/dev"

Comparing Atlas to Script-Based Tools

Capability Atlas Flyway / Liquibase
Schema definition Declarative HCL/SQL Imperative migration scripts
Migration generation Auto-computed from diff Hand-written
Lint / safety checks Built-in Requires third-party or manual review
Rollback Auto-computed Requires explicit rollback scripts
Drift detection Built-in schema diff Not built-in
Multi-database support PostgreSQL, MySQL, SQLite, MariaDB, MS SQL PostgreSQL, MySQL, Oracle, DB2, and more

Atlas trades the flexibility of hand-written migrations for the safety of computed migrations. For teams that have experienced production incidents caused by incorrect or incomplete migration SQL, that trade-off is usually worth it.

HelpMeTest can run your Atlas schema tests automatically on every pull request, alerting you to breaking changes and drift before they reach production — sign up free

Read more