Pre-planning Schema Migrations
The atlas schema plan
command allows users to pre-plan declarative schema migrations before applying them to the
database. Once a migration is planned, reviewed, and approved, it can be applied using the atlas schema apply
command to update the database to the desired state.
Note: If you are not familiar with the atlas schema apply
command, please refer to the Applying Changes
guide first.
The atlas schema plan
command is available exclusively to Pro users. To use this feature, run:
atlas login
Overview
The atlas schema apply
command updates the database to the desired state defined by the user. These auto-planned schema
changes can be approved in one of the following ways:
- Reviewed and approved interactively by a human.
- Auto-approved using the
--auto-approve
flag, though this may be risky in a production database. - Auto-reviewed based on the lint-review policy, which requires human review only if the linter detects issues. or errors.
These options depend on the database state and cannot predict whether the migration will succeed, fail, or abort.
This is where atlas schema plan
becomes useful.
The atlas schema plan
command allows users to pre-plan, review, and approve migrations before executing atlas schema apply
on the database. This enables users to preview and modify SQL changes, involve team members in the review process, and ensure
that no human intervention is required during the atlas schema apply
phase.
How does it work? In short (more details below), atlas schema plan
generates a migration plan for the specified
Schema Transition (State1 -> State2) and stores it in the Atlas Registry. During
atlas schema apply
, Atlas checks if there is an approved migration plan for the specific schema transition and applies
it without recalculating SQL changes at runtime or requiring user-approval.
If users wish to modify the auto-generated migration plan, they can edit it locally and then push it to the Atlas Registry.
Local Example
Let's consider a simple example. We have a table users
with two columns id
and name
, and we want to add a new column
email
to the table.
Example Setup
Before running atlas schema plan
, let's ensure that a schema repository named app
exists in Atlas Registry and there
is a database containing the previous schema state (before our changes):
- Schema Definition
- Config File
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
env "local" {
# URL to the target database.
url = "sqlite://main.db"
# URL to the dev-database.
dev = "sqlite://dev?mode=memory"
schema {
# Desired schema state.
src = "file://schema.sql"
# Atlas Registry config.
repo {
name = "app"
}
}
}
We run atlas schema push
to create the schema in Atlas Registry:
atlas schema push --env local
Schema: app
-- Atlas URL: atlas://app
-- Cloud URL: https://a8m.atlasgo.cloud/schemas/141733920781
Then, we run atlas schema apply
to align the database with the schema state:
atlas schema apply --env local --auto-approve
Changing the Schema
At this stage, we want to add a non-nullable email
column to the users
table. Let's update the schema.sql
file and then run
atlas schema plan
to generate a migration plan.
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
email TEXT NOT NULL
);
We run atlas schema plan
to generate a migration plan for adding the email
column to the users
table:
atlas schema plan --env local
The output looks like this:
Planning migration from local database to file://schema.sql (1 statement in total):
-- add column "email" to table: "users":
-> ALTER TABLE `users` ADD COLUMN `email` text NOT NULL;
-------------------------------------------
Analyzing planned statements (1 in total):
-- data dependent changes detected:
-- L2: Adding a non-nullable "text" column "email" will fail in case table "users"
is not empty https://atlasgo.io/lint/analyzers#MF103
-- ok (346.192µs)
-------------------------
-- 5.038728ms
-- 1 schema change
-- 1 diagnostic
? Approve or abort the plan:
▸ Approve and push
Abort
Data-Dependent Changes
Atlas detects data-dependent changes in the migration plan and provides a diagnostic message. In this case, it warns
that adding the non-nullable email
column, will fail if the users
table is not empty. The recommended solution is to
provide a default value for the new column. Let's fix this by adding a default value to the email
column and re-run the
atlas schema plan
command.
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
email TEXT NOT NULL DEFAULT 'unknown'
);
Then, we run atlas schema plan
again to generate a new migration plan, but this time, we approve it:
atlas schema plan --env local
Planning migration from local database to file://schema.sql (1 statement in total):
-- add column "email" to table: "users":
-> ALTER TABLE `users` ADD COLUMN `email` text NOT NULL DEFAULT 'unknown';
-------------------------------------------
Analyzing planned statements (1 in total):
-- no diagnostics found
-------------------------
-- 6.393773ms
-- 1 schema change
? Approve or abort the plan:
▸ Approve and push
Abort
Once approved, the migration plan will be pushed to the Atlas Registry, and can be applied using atlas schema apply
.
Plan Status: APPROVED
-- Atlas URL: atlas://app/plans/20240923085308
-- Cloud URL: https://a8m.atlasgo.cloud/schemas/141733920769/plans/210453397504
At this stage, we can run atlas schema apply
to apply the changes to the database, on any environment, without
re-calculating the SQL changes at runtime or requiring human intervention.
Applying approved migration using pre-planned file 20240923085308 (1 statement in total):
-- add column "email" to table: "users"
-> ALTER TABLE `users` ADD COLUMN `email` text NOT NULL DEFAULT 'unknown';
-- ok (749.815µs)
-------------------------
-- 802.902µs
-- 1 migration
-- 1 sql statement
Atlas Registry
Atlas Registry allows you to store, version, and maintain a single source of truth for your database schemas and its migration plans. It is similar to Docker Hub, but for your schemas and migrations. In addition to functioning as storage, it is schema-aware and provides extra capabilities such as ER diagrams, SQL diffing, schema docs, and more.
Schema pushed with atlas schema push
Edit a Plan
One of the first questions that come to mind when comparing the declarative approach to the versioned approach is: How can I edit a migration plan? There are three ways to edit a migration plan:
Edit in-place:
- Ensure the
EDITOR
environment variable is set (e.g.,export EDITOR=vim
). - Run
atlas schema plan --edit
to open the plan in the default editor. Upon closing, the plan will be pushed after approval.
- Ensure the
Save, edit, and push:
- Run
atlas schema plan --save
to save the plan to a file and edit it manually. - Run
atlas schema plan push --file file://<path>
to push the edited plan to the Atlas Registry.
- Run
Pull, edit, and push:
- Pull a remote plan by running
atlas schema plan pull --url atlas://<schema>/plans/<plan> > name.plan.hcl
. - Open
name.plan.hcl
in the editor, and edit themigration
attribute. - Push the edited plan to the Atlas Registry by running
atlas schema plan push --file file://<path>
.
- Pull a remote plan by running
To complete the example, let's edit the migration plan from the example above by changing all email
columns with 'unknown'
value to a computed email value:
We pull the plan first into a file named
20240923085308.plan.hcl
:atlas schema plan pull --url atlas://app/plans/20240923085308 > 20240923085308.plan.hcl
20240923085308.plan.hclplan "20240923085308" {
from = "vJYpErjN4kWJpw4nRaJcEX3xx/jExj4a05Ll3Y7gXr4="
to = "B5OVckDEeHcaSdYCUMEfYe8CZN85ahLkef44hfwCe2g="
migration = <<-SQL
-- Add column "email" to table: "users"
ALTER TABLE `users` ADD COLUMN `email` text NOT NULL DEFAULT 'unknown';
SQL
}Note that the
from
andto
are fingerprints of the schema states. They are used to identify the states in the schema transition. We will ignore them for now (without changing them, of course) and focus on the migration attribute.We edit the
migration
attribute to change all rows with'unknown'
emails to a computed email value:20240923085308.plan.hclplan "20240916133205" {
from = "vJYpErjN4kWJpw4nRaJcEX3xx/jExj4a05Ll3Y7gXr4="
to = "B5OVckDEeHcaSdYCUMEfYe8CZN85ahLkef44hfwCe2g="
migration = <<-SQL
-- Add column "email" to table: "users"
ALTER TABLE `users` ADD COLUMN `email` text NOT NULL DEFAULT 'unknown';
-- Change all unknown "email" columns with a new computed email
UPDATE `users` SET `email` = PRINTF('%s+a8m@atlasgo.cloud', `name`) WHERE `email` = 'unknown';
SQL
}Then, we push the edited plan to the Atlas Registry:
atlas schema plan push --file file://20240923085308.plan.hcl
Planning migration statements (2 in total):
-- add column "email" to table: "users":
-> ALTER TABLE `users` ADD COLUMN `email` text NOT NULL DEFAULT 'unknown';
-- change all unknown "email" columns with a new computed email
-> UPDATE `users` SET `email` = PRINTF('%s+a8m@atlasgo.cloud', `name`) WHERE `email` = 'unknown';
-------------------------------------------
Analyzing planned statements (2 in total):
-- no diagnostics found
-------------------------
-- 43.566575ms
-- 2 schema changes
? Approve or abort the plan:
▸ Approve and push
AbortOnce approved, the migration plan will be pushed to the Atlas Registry.
Schema DriftNote that if your manual changes are not in sync with the desired state (i.e., do not bring the database to the desired state), Atlas will detect the schema drift and reject this migration plan.
Then, running
atlas schema apply
will apply the changes to the database, including the newUPDATE
statement.Applying approved migration using pre-planned file 20240923085308 (2 statements in total):
-- add column "email" to table: "users"
-> ALTER TABLE `users` ADD COLUMN `email` text NOT NULL DEFAULT 'unknown';
-- ok (826.977µs)
-- change all unknown "email" columns with a new computed email
-> UPDATE `users` SET `email` = PRINTF('%s+a8m@atlasgo.cloud', `name`) WHERE `email` = 'unknown';
-- ok (447.152µs)
-------------------------
-- 1.353026ms
-- 1 migration
-- 2 sql statements
Push a Plan
By default, atlas schema plan
proposes pushing the plan file to the Atlas registry. However, you can use the --save
flag
to dump the plan to a file, edit it, and then push it manually to the Atlas Registry using the atlas schema plan push
command:
atlas schema plan push \
--file file://<path-plan-file> \
--env <config-env>
Approve a Plan
By default, atlas schema plan
pushes plans in an APPROVED
state to the registry. However, in some cases, we may prefer to create
the plan in pending state, and later approve it manually or automatically after it passes the team's review.
There are two ways to create a plan in a pending state and approve it after review.
- Manual workflow:
- Run the
atlas schema plan
command with the--pending
flag. This creates the plan in pending state. - Then, in order to approve the plan, either go to the Atlas Registry Web UI and approve the plan, or run the
atlas schema plan approve
command.
- Run the
- Automated (CI) workflow:
- When setting the
schema/plan
GitHub Action for your repository, Atlas automatically creates a plan in a pending state. - Then, after the PR is merged, Atlas auto-approves the created plan in the registry.
- When setting the
Users can protect their registry schemas by limiting who can push changes, push approved plans, or approve existing plans.
To enable this for your schema, go to the schema repository settings in the registry and enable the Protected Flows
option.
Pull a Plan
To pull a plan from the Atlas Registry, use the atlas schema plan pull
command:
atlas schema plan pull \
--url atlas://app/plans/add_email > add_email.plan.hcl
List Plans
To list all plans in the Atlas Registry for the given schema transition, use the atlas schema plan list
command:
atlas schema plan list \
--env local
Plan Status: APPROVED
-- Atlas URL: atlas://app/plans/add_email
-- Cloud URL: https://<tenant>.atlasgo.cloud/schemas/<schema-id>/plans/<plan-id>
Lint a Plan
To lint a plan (remote or local) before pushing it to the Atlas Registry, use the atlas schema plan lint
command:
atlas schema plan lint \
--file file://add_email.plan.hcl \
--env local
Apply a Plan
Running atlas schema apply
searches for a migration plan in the Atlas Registry and applies it to the database, if exists.
However, in unusual cases, you might have multiple (approved) migration plans for the same schema transition store in the
registry (e.g., one per environment). In that case, running atlas schema apply
will abort with the following error:
Error: multiple pre-planned migrations were found in the registry for this schema transition.
Current hash: vJYpErjN4kWJpw4nRaJcEX3xx/jExj4a05Ll3Y7gXr4=
Desired hash: hna312Vk535aibL1hTRcBlxeyUvIwV6Mov7kfaZ2+3s=
Plans found:
atlas://app/plans/<plan-one>
atlas://app/plans/<plan-two>
To resolve the issue, either delete the conflicting plans or provide the plan URL explicitly using the --plan flag.
In this case, we either delete the conflicting plans from the Atlas Registry or provide the plan URL explicitly using
the --plan
flag:
Applying approved migration using pre-planned file 20240923085308 (2 statements in total):
-- add column "email" to table: "users"
-> ALTER TABLE `users` ADD COLUMN `email` text NOT NULL DEFAULT 'unknown';
-- ok (789.621µs)
-- change all unknown "email" columns with a new computed email
-> UPDATE `users` SET `email` = PRINTF('%s+a8m@atlasgo.cloud', `name`) WHERE `email` = 'unknown';
-- ok (883.177µs)
-------------------------
-- 1.77283ms
-- 1 migration
-- 2 sql statements
GitHub Actions
Atlas provides an official GitHub Actions integration to automatically plan, review, and approve declarative schema migrations during PR workflows. The example below demonstrates how to set up this workflow for your repository.
Plan Generated by atlas schema plan
Create a Schema Repository in Atlas Registry
For the purpose of the example, let's create a schema repository named demo
in Atlas Registry with the following
SQL schema:
- Schema Definition
- Config File
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY
);
env "dev" {
# The URL to the dev-database.
dev = "docker://postgres/15/dev?search_path=public"
schema {
# Desired schema state.
src = "file://schema.sql"
# Atlas Registry config.
repo {
name = "demo"
}
}
}
To create the schema repository in the Atlas Registry, run the following command:
atlas schema push --env dev
https://<your-tenant>.atlasgo.cloud/schemas/141733920769
Set Up the schema/push
GitHub Action
In order to keep our schema repository up-to-date with the latest changes, we can set up the schema/push
GitHub Action. This
action automatically pushes the schema to the Atlas Registry whenever changes are made to the SQL schema file:
name: Push Declarative Schemas
on:
push:
branches:
- master
paths:
- .github/workflows/atlas-push.yaml
- 'schema.sql' # Can be HCL, ORM, other instead.
permissions:
contents: read
jobs:
push:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
with:
fetch-depth: 0
- uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_TOKEN }}
- uses: ariga/atlas-action/schema/push@master
with:
env: dev # Use the "dev" environment from the atlas.hcl file.
To push the schema to Atlas Registry from your GitHub Action, set up a GitHub secret named ATLAS_TOKEN
using your Atlas
Cloud token. To create a token, follow these instructions.
Set Up the schema/plan
GitHub Action
The last step is to set up the schema/plan
and schema/plan/approve
Actions:
schema/plan
- This action automatically plans the schema migration whenever changes are made to the SQL schema file. After a plan is created, it is pushed to the Atlas Registry inPENDING
state, and waiting to be approved.schema/plan/approve
- This action automatically approves the pending plan in the Atlas Registry after the PR is merged to the master branch.
name: Plan Declarative Migrations
on:
workflow_dispatch:
push:
branches:
- master
paths:
- .github/workflows/atlas-plan.yaml
- 'schema.sql'
pull_request:
branches:
- master
paths:
- .github/workflows/atlas-plan.yaml
- 'schema.sql'
permissions:
contents: read
pull-requests: write
jobs:
plan:
name: plan
if: ${{ github.event_name == 'pull_request' }}
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- name: Setup Atlas
uses: ariga/setup-atlas@master
with:
cloud-token: ${{ secrets.ATLAS_TOKEN }}
- name: Run schema plan
uses: ariga/atlas-action/schema/plan@master
env:
GITHUB_TOKEN: ${{ github.token }}
with:
env: dev # Use the "dev" environment from the atlas.hcl file.
approve:
name: approve
if: ${{ github.event_name == 'push' && github.ref == 'refs/heads/master' }}
runs-on: ubuntu-latest
env:
GITHUB_TOKEN: ${{ github.token }}
steps:
- uses: actions/checkout@v4
- name: Setup Atlas
uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_TOKEN }}
- name: Approve the plan
id: plan-approve
uses: ariga/atlas-action/schema/plan/approve@master
with:
env: dev # Use the "dev" environment from the atlas.hcl file.
from
- Defines the current state of the schema to calculate the migration from. If not provided, Atlas will use theurl
in theatlas.hcl
file (same asatlas schema apply
). If theurl
attribute is not set, Atlas will use the last known state from the Atlas Registry.to
- Defines the desired state of the schema to calculate the migration to. If not provided, Atlas will use theschema.src
attribute in theatlas.hcl
file.
To avoid a race condition between the push
and plan
workflows, we can merge them into a single workflow.
name: Plan Declarative Migrations
on:
workflow_dispatch:
push:
branches:
- master
paths:
- .github/workflows/atlas-schema.yaml
- 'schema.sql'
pull_request:
branches:
- master
paths:
- .github/workflows/atlas-schema.yaml
- 'schema.sql'
permissions:
contents: read
pull-requests: write
jobs:
plan:
name: plan
if: ${{ github.event_name == 'pull_request' }}
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- name: Setup Atlas
uses: ariga/setup-atlas@master
with:
cloud-token: ${{ secrets.ATLAS_TOKEN }}
- name: Run schema plan
uses: ariga/atlas-action/schema/plan@master
env:
GITHUB_TOKEN: ${{ github.token }}
with:
env: dev
approve-push:
name: approve-push
if: ${{ github.event_name == 'push' && github.ref == 'refs/heads/master' }}
runs-on: ubuntu-latest
env:
GITHUB_TOKEN: ${{ github.token }}
steps:
- uses: actions/checkout@v4
- name: Setup Atlas
uses: ariga/setup-atlas@v0
with:
cloud-token: ${{ secrets.ATLAS_TOKEN }}
# Plan against the latest schema state (one before the PR).
- name: Approve the plan
id: plan-approve
uses: ariga/atlas-action/schema/plan/approve@master
with:
env: dev
# Push the schema after the plan is approved.
- name: Push the schema
id: schema-push
uses: ariga/atlas-action/schema/push@master
with:
env: dev