Skip to main content

Down Migrations

The atlas migrate down command allows reverting applied migrations. Unlike the traditional approach, where down files are "pre-planned," Atlas computes a migration plan based on the current state of the database. Atlas reverts previously applied migrations and executes them until the desired version is reached, regardless of the state of the latest applied migration — whether it succeeded, failed, or was partially applied and left the database in an unknown version.

By default, Atlas generates and executes a set of pre-migration checks to ensure the computed plan does not introduce destructive changes or deletes data. Users can review the plan and run the checks before the plan is applied by using the --dry-run flag or Atlas Cloud as described below.

Migration Approval​

Users under the Business or the Enterprise plan can protect this flow by configuring Atlas to require approval from one or more reviewers before applying it to the database. Here's how you can do it:

Screenshot example
Migration Approval Policy

Migration approval policy enabled for the atlas migrate down command

Once set, running atlas migrate down will create a plan that requires approvals before it is applied:

Defaults for new Projects​

You can enable protected flows by default for newly created projects in the general settings. When a new project is created, protected flows are then enabled with the configured users and their roles.

Screenshot example
Default Migration Approval Policy

Migration approval policy enabled by default for newly created projects

Once set, running atlas migrate down will create a plan that requires approvals before it is applied:

$ atlas migrate down --env prod
To approve the plan visit: https://a8m.atlasgo.cloud/deployments/51539607645

Review Required

Dry Run​

The dry-run option allows viewing the SQL statements planned to downgrade the database to its desired version, without executing them. If the down migration plan contains pre-migration checks, Atlas executes the checks on the database and reports the results.

atlas migrate down \
  --url "mysql://root:pass@localhost:3306/example" \
  --dir "file://migrations" \
  --dry-run
Dry Run Cloud Reports

Users who have connected or pushed their migration directory to the Atlas Schema Registry can review the dry-run reports and the pre-migration checks results on their Atlas dashboard.

Screenshot example
Migrate Down Dry Run

Dry-run down migration in Atlas Cloud

Revert local databases​

When experimenting with generating and applying migrations locally, sometimes there is a need to revert the last applied migration or migrations. The following steps demonstrate how to revert the last applied migration, but you can specify the number of migrations to revert as an argument.

1. Assuming a migration file named 20240305171146.sql was last applied to the database and needs to be reverted.

2. Before deleting 20240305171146.sql, run the following command to revert the last applied migration:

atlas migrate down \
  --dir "file://migrations" \
  --url "mysql://root:pass@localhost:3306/example" \
  --dev-url "docker://mysql/8/dev"

3. After downgrading your database to the desired version, you can safely delete the migration file 20240305171146.sql from the migration directory by running atlas migrate rm 20240305171146.

4. After the file was deleted and the database downgraded, you can generate a new migration using the atlas migrate diff command with the optional --edit flag to open the generated file in your default editor.

Cleaning the database

atlas migrate down only reverts changes applied by the migration directory, and does not touch resources that exist in the schema, but are not managed by the migration directory. If you need to clean the entire database or a schema to its initial state, including the atlas_schema_revisions table, consider using the atlas schema clean command:

atlas schema clean -u "mysql://root:pass@:3306/example"

Revert real environments​

Atlas follows a linear migration history model, in which schema changes are "roll-forward" by default rather than rolled back. In practice, that means reverting a schema change, such as table or column addition, requires generating a new migration version to undo the changes. This is due to the fact that the "up" and "down" might not occur consecutively as the schema evolves over time.

However, there are cases that reverting the last applied migration(s) is necessary, such when experimenting on a staging environment or when the entire deployment is rolled back and the schema changes introduced in the new version are not backward compatible for old versions of the application and therefore need to be reverted. To undo applied migration(s) on real environments, there are two approaches:

Using the Schema Registry​

If the project is connected to the Atlas Schema Registry, you can simply revert to a specific version or a tag (e.g., GitHub commit) using the Atlas GitHub Action or with the following commands:

atlas.hcl
env {
  name = atlas.env # dynamically set
  migration {
    dir = "atlas://myapp"
  }
}
atlas migrate down \
  --to-tag "$COMMIT_SHA" \
  --url "$DATABASE_URL" \
  --dev-url "docker://mysql/8/dev" \
  --env "staging"

If this command requires a review, a new plan will be created and Atlas will wait for approval before executing it.

Down Manually​

If the project is not connected to the Schema Registry, the steps are identical to the local workflow mentioned above.

Rollback vs. Down​

Although sometimes people refer to down migrations as rollbacks, Atlas uses these terms differently:

  • Rollback - A migration that was wrapped in a transaction and all statements that were executed in the transaction context will be rolled back in case of a failure and won't have any effect on the database. By default, Atlas executes each migration file in a transaction; however, users can configure Atlas to execute all pending files in a single transaction using the tx-mode=all flag.

  • Down - A down migration is when changes were applied to the database and need to be reverted. Atlas computes the necessary changes to downgrade the database to the desired version and executes them.

    Transactional DDLs

    Note, some databases like MySQL do not support transactional DDLs, causing the database to stay in unclear state in case the migration failed in the middle of the execution. In such cases, "rolling back" the partially applied migrations requires reverting the applied statements, which is what atlas migrate down does.

    In addition, since non-transactional DDLs can cause the database to stay in a stale state in case they fail in the middle of the execution, when Atlas generates a down migration plan, it takes the database (+ its version) and the necessary changes into account. If a transactional plan, executable as a single unit, can be created, Atlas will opt for this approach. If not, Atlas reverts each applied statement one at a time, ensuring the database is not left in an unknown state in case a failure occurs midway, for any reason.

Pre-planned Down Migrations​

By default, Atlas computes the down migration plan dynamically, based on the current state of the database and the target version specified by the user. In some cases, however, teams may want to explicitly define how a migration should be reverted, especially when dealing with non-schema logic such as data migrations or other business-specific operations that Atlas cannot infer automatically (for example, calling a stored procedure or reverting a configuration flag).

In these cases, users can define manual down migrations alongside their regular migration files. These files use the txtar format described below. The following example demonstrates a migration that includes both an "up" and a manual "down" plan:

migrations/20240305171146.sql
-- atlas:txtar

-- migration.sql --
-- The statement below applies the "up" migration.
INSERT INTO users (id, name) VALUES (1, 'Alice');

-- down.sql --
-- The statement below defines the "down" migration.
DELETE FROM users WHERE id = 1;

Atlas txtar directive​

Archive Format

Atlas supports a text-based archive format for describing complete migration plans. Unlike standard migration files that typically contain only DDL statements (and optional directives), txtar files can include multiple file entries such as:

  • checks.sql - pre-execution assertions that must pass before applying the migration
  • migration.sql - SQL statements applied during the migration
  • down.sql - SQL statements used to revert the migration

Each section in the archive begins with a marker line formatted as -- FILENAME -- and continues until the next marker or the end of the file. For example:

migrations/20240305171146.sql
-- atlas:txtar

-- checks.sql --
-- The assertion below must evaluate to true, ensuring the "users" table is empty.
SELECT NOT EXISTS(SELECT * FROM users WHERE id = 1);

-- migration.sql --
-- Executed only if the assertion above succeeds.
INSERT INTO users (id, name) VALUES (1, 'Alice');

-- down.sql --
-- Used to revert the migration.
DELETE FROM users WHERE id = 1;

Execution Flow for Manual Down Migrations​

Manual down migration files override automatically generated plans. When a migration includes a down.sql section in the txtar format, Atlas uses it to revert the migration instead of computing a plan dynamically. However, a few important details apply:

  • Atlas performs drift detection before executing the plan, comparing the current database state with the expected state defined by the target version. This validation also applies when using manual down files, meaning Atlas verifies that the manually defined statements align with the expected schema transitions. If a mismatch is detected, execution stops and the drift is reported.
  • Atlas still generates and runs all defined pre-migration checks before applying a down migration, whether it's manual or automatic. To skip these checks, use the --skip-checks flag.
  • When reverting multiple migration versions:
    • If all down statements can be executed safely in a single transaction, Atlas wraps them together and runs them atomically.
    • If not, Atlas executes each down migration sequentially to ensure the database remains consistent.
  • When multiple versions are reverted and one or more lack a defined down file, Atlas will prompt you to split the operation. For example, first reverting the versions with manual down definitions, then the remaining ones using automatically generated plans.
  • To force Atlas to compute all down migrations dynamically and ignore manual definitions, use the --plan flag.

Flags and Arguments​

The default behavior of atlas migrate down is to revert the last applied file. However, you can pass the amount of migrations to revert as an argument, or a target version or a tag as a flag. For instance, atlas migrate down 2 will revert up to 2 pending migrations while atlas migrate down --to-version 1234 will revert all applied migrations after version "1234".

  • --url the URL to the database to revert the migrations.
  • --dir the URL to the migration directory. It defaults to file://migrations.
  • --dev-url a URL to a Dev Database that will be used to plan and analyze the migration.
  • --to-version (optional) the version to revert to.
  • --to-tag (optional) the directory tag to revert to.
  • --dry-run (optional) if set, the down migration plan is printed without executing it.
  • --skip-checks (optional) if set, skips executing pre-migration checks before applying the down migration plan.
  • --plan (optional) - if set, forces Atlas to compute the down migration plan dynamically, ignoring any manual down definitions.

Reference​

CLI Command Reference