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 migrationsatlas 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 isfile://migrations
, e.g a directory namedmigrations
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 inmy_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.
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
- 2_add_pets_table.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`)
)
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"
.
└── 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:
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"