Skip to main content

Database Migrations for On-Premise and Self-Hosted Software

An on-premise or self-hosted product upgrades its database from its installer. With the versioned workflow, the installer runs the same migration files your team reviewed and merged. Each release takes four steps:

  1. Plan: you or a coding agent edit the desired schema, and Atlas plans the migration: atlas migrate diff writes the next file.
  2. Check: on every pull request, Atlas lints and tests the new file in CI, with atlas migrate lint and atlas migrate test.
  3. Package: the release build ships the migration directory, atlas.sum included.
  4. Upgrade: on the customer machine, the installer runs atlas migrate apply, or the migration tool it already uses.
note

Customer machines often can't reach Atlas Cloud. On the Enterprise tier, Atlas can issue an offline license that gives the installer the Atlas Pro features it uses, in air-gapped environments too. See offline operation.

To work with a coding agent, install the Atlas agent skills. See Get Started with a Coding Agent.

Step 1: Plan the Change​

Keep the desired schema as code. You or a coding agent edit it, and Atlas plans each migration from it:

atlas.hcl
env "dev" {
  dev = "docker://postgres/17/dev?search_path=public"
  schema {
    src = "file://schema.sql"
  }
  migration {
    dir = "file://migrations"
  }
}
atlas migrate diff add_orders --env dev

atlas migrate diff compares the migration directory with schema.sql on the dev database, and writes the statements that bring the directory to the desired schema into a new file in migrations/.

The dev database URL names the public schema, so the files hold unqualified statements. They run in the schema that the customer's database URL names with search_path. See URLs.

Match the tool your installer runs​

If the installer already runs Flyway, Liquibase, golang-migrate, goose, or dbmate, set format, and Atlas writes the files in that tool's layout:

atlas.hcl
env "dev" {
  dev = "docker://postgres/17/dev?search_path=public"
  schema {
    src = "file://schema.sql"
  }
  migration {
    dir    = "file://migrations"
    format = flyway
  }
}

The installer keeps running the tool it has, and Atlas lints and tests the files in that format. See custom formats for the file names of each format.

Step 2: Check the Change​

Lint every pull request​

atlas migrate lint replays each new migration file on the dev database and analyzes every statement. The installer runs each file on databases you can't inspect, so fail the lint on every change that can break an upgrade:

atlas.hcl
lint {
  # Dropping a column or a table loses the customer's data. Fails the lint by default.
  destructive {
    error = true
  }
  # A unique index on a column with duplicates, or a NOT NULL column without a default,
  # fails the upgrade on some customers' data.
  data_depend {
    error = true
  }
  # A change that rewrites a table, such as a column type change, locks it while the upgrade runs.
  blocking {
    error = true
  }
  # A hotfix file versioned before files that already shipped fails on databases that have them.
  non_linear {
    error = true
  }
}

Each block configures an analyzer: destructive, data_depend, blocking, and non_linear. Without error = true, all but destructive only report what they find. If the previous release keeps serving traffic during the upgrade, add incompatible too: it reports renamed and dropped columns that the running release still reads.

The same lint block holds your own release rules. A statement rule matches statements by pattern, for example to allow a DELETE only with a WHERE clause. Without error = true, a denied statement only reports a warning:

atlas.hcl
lint {
  statement {
    error = true
    allow "delete-with-where" {
      match = "(?i)DELETE .+ WHERE"
    }
    deny "delete-truncate" {
      match   = "(?i)DELETE"
      message = "DELETE statements must have a WHERE clause"
    }
  }
}

For rules about the schema itself, such as requiring a default on every new column, write a custom schema policy; the analyzers page lists every built-in check and its options. A data-dependent change can still ship if the file checks the customer's data first: a pre-migration check runs on the target database before the file, and the installer stops before any statement runs when the check fails. Run lint on every pull request, as in Setup CI/CD.

Test upgrades from earlier releases​

Customers skip releases, so the installer can start from any earlier version. A migration test starts from the last version of a release, inserts rows the way that release writes them, upgrades, and checks the rows:

migrate.test.hcl
test "migrate" "upgrade_from_2_4" {
  # The last version of release 2.4.
  migrate {
    to = "20240613061046"
  }
  # Rows as release 2.4 writes them.
  exec {
    sql = "INSERT INTO users (name) VALUES ('Ada Lovelace')"
  }
  # The version of the current release.
  migrate {
    to = "20240613061102"
  }
  # The upgrade split the name into two columns.
  exec {
    sql    = "SELECT first_name, last_name FROM users"
    output = "Ada, Lovelace"
  }
}

Add the test file to the env, and run the tests in CI next to lint:

atlas.hcl
env "dev" {
  dev = "docker://postgres/17/dev?search_path=public"
  schema {
    src = "file://schema.sql"
  }
  migration {
    dir = "file://migrations"
  }
  test {
    migrate {
      src = ["migrate.test.hcl"]
    }
  }
}
atlas migrate test --env dev

Add a test for each release that customers still run.

Step 3: Package the Release​

A release ships the migration directory, atlas.sum included, with the Atlas CLI, or with the migration tool the installer already runs:

installer/
├── atlas
├── migrations/
│   ├── 20240613061046_create_users.sql
│   ├── 20240613061102_split_name.sql
│   └── atlas.sum
└── install.sh

Before you build the release, run atlas migrate validate --env dev. It checks every file against atlas.sum and replays the directory on the dev database, so a file edited by hand, or a statement the database rejects, fails the build.

On the customer machine, atlas migrate apply checks the files against atlas.sum before it runs anything. A file changed after the release stops the upgrade before any statement runs:

Output
You have a checksum error in your migration directory.

	L3: 20240613061102_split_name.sql was edited

Please check your migration files and run 'atlas migrate hash' to re-hash the contents

Error: checksum mismatch

See migration directory integrity.

Step 4: Upgrade Customer Databases​

The installer applies the pending files to the customer database:

atlas migrate apply \
  --dir "file://migrations" \
  --url "$DATABASE_URL"
  • Atlas records each applied version in the atlas_schema_revisions table, and runs only the pending files, in order. A customer three releases behind gets every file it missed. See schema revision information.
  • If a file fails, the command exits with an error and the files after it don't run, so the installer can stop the upgrade and report the error.
  • Atlas takes an advisory lock while it applies. A second installer that starts on the same database waits for the lock, and fails after --lock-timeout (10s by default). See advisory locks.
  • --dry-run prints the SQL without running it, for customers whose DBA reviews changes before an upgrade.
  • atlas migrate status --dir "file://migrations" --url "$DATABASE_URL" prints the version a database is on and the files still pending, so support can tell which release a customer runs. See migration status.

If you set format, the installer runs the files with the migration tool it already uses instead, and that tool records the applied versions.

Databases installed before Atlas​

A database installed before you adopted Atlas has tables but no revision history, and atlas migrate apply refuses to run on it. Create a baseline file that matches the schema of your last release before Atlas. --baseline marks that version as applied without running the file, so first upgrade a database on an older release to that release with your previous installer. Then pass the baseline version on its first upgrade with Atlas:

atlas migrate apply \
  --dir "file://migrations" \
  --url "$DATABASE_URL" \
  --baseline "20220811074144"

Atlas then applies the files after the baseline version. See existing databases to create the baseline file.

Get Started with a Coding Agent​

With a coding agent, install the Atlas agent skills. One command installs them:

Install the skills from a terminal. It installs the Atlas plugin, with all its skills, for every project on this machine.

claude plugin marketplace add ariga/atlas
claude plugin install atlas@ariga

Then start the onboarding skill in Claude Code. See Claude Code with Atlas for the other ways to use the skills.

/atlas:atlas-onboard

The atlas skill has the agent lint and test each migration it writes, with atlas migrate lint and atlas migrate test. To set up Atlas in a new project, start the atlas-onboard skill.

Next Steps​

Versioned Migrations

How Atlas writes, checks, and applies versioned migration files

Migration Linting

Analyzers, lint policies, and how lint picks the files to analyze

Testing Migrations

Write tests that seed data and check each migration

Applying Migrations

Flags, transactions, locks, and existing databases for migrate apply

Setup CI/CD

Lint and test every migration in your pipeline before release