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:

$ 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.

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.

Reference​

CLI Command Reference