Migration Applying

Atlas has its own migration execution engine, that works with the Atlas migration file format, e.g. migration files generated by atlas migrate diff.

Arguments

atlas migrate apply accepts one positional integer argument to specify how many pending migration files to run.

• atlas migrate apply run all pending migrations
• atlas migrate apply 2 run at most 2 pending migrations

Flags

When using migrate apply to apply migrations, users must supply multiple parameters:

• --url the URL to the database to apply migrations on.
• --dir the URL of the migration directory, by default it is file://migrations, e.g a directory named migrations in the current working directory.

Schema Revision Information

Atlas saves information about the applied migrations on a table called atlas_schema_revisions in the connected database schema (e.g. mysql://user@host/my_schema or postgres://user@host/db?search_path=my_schema). If the database connection is not bound to a specific schema (e.g. mysql://user@host/ or postgres://user@host/db), the table is stored in its own schema called atlas_schema_revisions. This behavior can be changed by setting the schema manually:

• --revisions-schema my_schema to store the data in my_schema.atlas_schema_revisions.

Transaction Configuration

By default, Atlas creates one transaction per migration file and will roll back that transaction if a statement in the wrapped migration fails to execute. Atlas supports three different transaction modes:

• --tx-mode file (default) will wrap each pending migration into its own transaction.
• --tx-mode all will wrap all pending migration files into one transaction.
• --tx-mode none will not create any transaction. If a statement fails, the execution will stop. However, Atlas is smart enough to detect which statement fails and on another migration attempt will continue with the failed statement. This means altering the migration file from the failed statements onwards is safe and recommended.

caution

Please be aware, that non DDL transactional databases like MySQL (due to implicit commits) can not be safely rolled back completely, and you might end up with a mismatched schema and revision table state. Atlas will handle those cases in future releases. A good source of information can be found in the PostgreSQL wiki.

Existing Databases

If you have an existing database project and want to switch over to Atlas Versioned Migrations, you need to provide Atlas with a starting point. The first step is to create a migration file reflecting the current schema state. This can be easily done:

atlas migrate diff my_baseline \
  --dir "file://migrations" \
  --to "mysql://root:pass@remote:3306/my_schema"

1_baseline.sql

CREATE TABLE `users` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `age` bigint(20) NOT NULL,
    `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `age` (`age`)
)

2_add_pets_table.sql

CREATE TABLE `pets` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
    PRIMARY KEY (`id`)
)

On your first migration execution attempt, you can then specify a baseline version. Atlas will mark this version as already applied and proceed with the next version:

• --baseline 1 will skip all files including version 1 (1_baseline.sql) and proceed with the next version (2_add_pets_table).

If your database does contain resources but no revision information yet, Atlas will refuse to execute migration files. One way to override that behavior is by using the --baseline flag above. However, e.g. in cases where the existing tables are not managed by Atlas at all and should not be part of a baseline file, you can run the initial migration attempt by providing the following flag:

• --allow-dirty to allow starting migration execution on a non-clean database.

--allow-dirty and --baseline are mutually exclusive.

Dry Run

If you want to check what exactly Atlas would do when attempting a migration execution, you can provide the --dry-run flag:

• --dry-run to not execute any SQL but print it on the screen.

Down migrations

Migrations that "roll back" or reverse changes made to the database schema are called "down migrations". These are often used during local development to undo the changes made by corresponding "up migrations". Atlas follows a linear migration history model, in which all migration files are "roll-forward". However, it is still possible to clean or revert schema changes made by specific migration files using the schema clean or schema apply commands. For example:

Reverting deleted migration files

1. To undo the changes made by migration files that have already been deleted, you can use the schema apply command to roll back the database state to the state defined by the migration directory:

atlas schema apply \
  --url "mysql://root:pass@localhost:3306/example" \
  --to "file://migrations" \
  --dev-url "docker://mysql/8/example" \
  --exclude "atlas_schema_revisions"

2. Remove the files/versions that were rolled back from the revision table:

atlas migrate set \
  --url "mysql://root:pass@localhost:3306/example" \
  --dir "file://migrations"

Example output

Current version is 20221207170547 (2 removed):

  - 20221225113035 (add_users)
  - 20221225113406 (add_blog_posts)

3. Verify the migration status by using the atlas migrate status command:

atlas migrate status \
  --url "mysql://root:pass@localhost:3306/example" \
  --dir "file://migrations"

Example output

Migration Status: OK
  -- Current Version: 20221207170547
  -- Next Version:    Already at latest version
  -- Executed Files:  25
  -- Pending Files:   0

Reverting applied migration files

1. To undo the changes made by migration files that are still present in the migration directory, you can use the version parameter to specify that the database should be rolled back to the state defined in a specific version of the migration directory. For example:

atlas schema apply \
  --url "mysql://root:pass@localhost:3306/example" \
  --to "file://migrations?version=20220925094437" \
  --dev-url "docker://mysql/8/example" \
  --exclude "atlas_schema_revisions"

tree .

.
└── migrations
    ├── 20220925092817_initial.sql
    ├── 20220925094021_second.sql
    ├── 20220925094437_third.sql
    ├── 20220925094438_fourth.sql # changes made by this file are reverted.
    └── atlas.sum

2. To remove the 20220925094438 version from the revision table, set the current version to the preceding one 20220925094437:

atlas migrate set 20220925094437 \
  --url "mysql://root:pass@localhost:3306/example" \
  --dir "file://migrations"

Example output

Current version is 20220925094437 (1 removed):

  - 20220925094438 (fourth)

3. Verify the migration status by using the atlas migrate status command:

atlas migrate status \
  --url "mysql://root:pass@localhost:3306/example" \
  --dir "file://migrations"

Example output

Migration Status: PENDING
  -- Current Version: 20220925094437
  -- Next Version:    20220925094438
  -- Executed Files:  3
  -- Pending Files:   1

Reverting all schema changes

3. In order to clean the entire schema, use the schema clean command, and then use migrate apply to replay the migration files. For example:

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

Migration status

In addition to the --dry-run flag Atlas also provides the atlas migrate status command, that provides in-depth information about the migration status of the connected database.

Multi-Tenant environments

The Atlas configuration language provides built-in support for executing versioned workflows in multi-tenant environments. Using the for_each meta-argument, users can define a single env block that is expanded to N instances, one for each tenant:

atlas.hcl

env "prod" {
  for_each = toset(var.tenants)
  url      = urlsetpath(var.url, each.value)
  migration {
    dir = "file://migrations"
  }
  log {
    migrate {
      apply = format(
        "{{ json . | json_merge %q }}",
        jsonencode({
          Tenant : each.value
        })
      )
    }
  }
}

Read more about how to define versioned workflows using project files in multi-tenant environments.

Examples

First time apply with baseline on production environment:

atlas migrate apply \
  --env "production" \
  --baseline "20220811074144"

Execute 1 pending migration file, but don't run, but print SQL statements on screen:

atlas migrate apply 1 \
  --env "production" \
  --baseline "20220811074144" \
  --dry-run

Specify revision table schema and custom migration directory path:

atlas migrate apply \
  --url "mysql://root:pass@remote:3306/my_database" \
  --revisions-schema "atlas_migration_history" \
  --dir "file://custom/path/to/dir"

Ignore unclean database and run the first 3 migrations:

atlas migrate apply 3 \
  --url "mysql://root:pass@remote:3306/my_database" \
  --dir "file://custom/path/to/dir"

Run all pending migrations, but do not use a transaction:

atlas migrate apply \
  --url "mysql://root:pass@remote:3306/my_database" \
  --tx-mode "none"

Show information about the migration status of a deployment:

atlas migrate status \
  --url "mysql://root:pass@remote:3306/my_database" \
  --dir "file://custom/path/to/dir" \
  --revisions-schema "atlas_migration_history"

Reference

CLI Command Reference