Skip to main content

The Atlas Migration Execution Engine

· 8 min read
Jannik Clausen

With the release of v0.6.0, we introduced a workflow for managing changes to database schemas that we have called: Versioned Migration Authoring.

Today, we released the first version of the Atlas migration execution engine, that can apply migration files on your database. In this post, we will give a brief overview of the features and what to expect in the future.

Migration File Format​

The Atlas migration filename format follows a very simple structure: version_[name].sql, with the name being optional. version can be an arbitrary string. Migration files are lexicographically sorted by filename.

↪ tree .
.
├── 1_initial.sql
├── 2_second.sql
├── 3_third.sql
└── atlas.sum

0 directories, 4 files

If you want to follow along, you can simply copy and paste the above files in a folder on your system. Make sure you have a database ready to work on. You can start an ephemeral docker container with the following command:

# Run a local mysql container listening on port 3306.
docker run --rm --name atlas-apply --detach --env MYSQL_ROOT_PASSWORD=pass -p 3306:3306 mysql:8

Apply Migrations​

In order to apply migrations you need to have the Atlas CLI in version v0.7.0 or above. Follow the installation instructions if you don't have Atlas installed yet.

Now, to apply the first migration of our migration directory, we call atlas migrate apply and pass in some configuration parameters.

atlas migrate apply 1 \
  --dir "file://migrations" \
  --url "mysql://root:pass@localhost:3306/"
Migrating to version 1 (1 migrations in total):

  -- migrating version 1
    -> CREATE DATABASE `my_schema`;
    -> CREATE TABLE `my_schema`.`tbl` (`col` int NOT NULL);
  -- ok (17.247319ms)

  -------------------------
  -- 18.784204ms
  -- 1 migrations
  -- 2 sql statements

Migration Status​

Atlas saves information about the database schema revisions (applied migration versions) in a special table called atlas_schema_revisions. In the example above we connected to the database without specifying which schema to operate against. For this reason, Atlas created the revision table in a new schema called atlas_schema_revisions. For a schema-bound connection Atlas will put the table into the connected schema. We will see that in a bit.

Go ahead and call atlas migrate status to gather information about the database migration state:

atlas migrate status \
  --dir "file://migrations" \
  --url "mysql://root:pass@localhost:3306/"
Migration Status: PENDING
  -- Current Version:  1
  -- Next Version:     2
  -- Executed Files:   1
  -- Pending Files:    2

This output tells us that the last applied version is 1, the next one is called 2 and that we still have two migrations pending. Let's apply the pending migrations:

Note, that we do not pass an argument to the apply, in which case Atlas will attempt to apply all pending migrations.

atlas migrate apply \
  --dir "file://migrations" \
  --url "mysql://root:pass@localhost:3306/"
Migrating to version 3 from 1 (2 migrations in total):

  -- migrating version 2
    -> ALTER TABLE `my_schema`.`tbl` ADD `col_2` TEXT;
  -- ok (13.98847ms)

  -- migrating version 3
    -> CREATE TABLE `tbl_2` (`col` int NOT NULL);
     Error 1046: No database selected

  -------------------------
  -- 15.604338ms
  -- 1 migrations ok (1 with errors)
  -- 1 sql statements ok (1 with errors)

Error: Execution had errors: Error 1046: No database selected

Error: sql/migrate: executing statement "CREATE TABLE `tbl_2` (`col` int NOT NULL);" from version "3": Error 1046: No database selected

What happened here? After further investigation, you will find that our connection URL is bound to the entire database, not to a schema. The third migration file however does not contain a schema qualifier for the CREATE TABLE statement.

By default, Atlas wraps the execution of each migration file into one transaction. This transaction gets rolled back if any error occurs withing execution. Be aware though, that some databases, such as MySQL and MariaDB, don't support transactional DDL. If you want to learn how to configure the way Atlas uses transactions, have a look at the docs.

Migration Retry​

To resolve this edit the migration file and add a qualifier to the statement:

CREATE TABLE `my_schema`.`tbl_2` (`col` int NOT NULL);

Since you changed the contents of a migration file, we have to re-calculate the directory integrity hash-sum by calling:

atlas migrate hash --force \
  --dir "file://migrations"

Then we can proceed and simply attempt to execute the migration file again.

atlas migrate apply \
  --dir "file://migrations" \
  --url "mysql://root:pass@localhost:3306/"
Migrating to version 3 from 2 (1 migrations in total):

  -- migrating version 3
    -> CREATE TABLE `my_schema`.`tbl_2` (`col` int NOT NULL);
  -- ok (15.168892ms)

  -------------------------
  -- 16.741173ms
  -- 1 migrations
  -- 1 sql statements

Attempting to migrate again or calling atlas migrate status will tell us that all migrations have been applied onto the database and there is nothing to do at the moment.

atlas migrate apply \
  --dir "file://migrations" \
  --url "mysql://root:pass@localhost:3306/"
No migration files to execute

Moving an existing project to Atlas with Baseline Migrations​

Another common scenario is when you need to move an existing project to Atlas. To do so, create an initial migration file reflecting the current state of a database schema by using atlas migrate diff. A very simple way to do so would be by heading over to the database from before, deleting the atlas_schema_revisions schema, emptying your migration directory and running the atlas migrate diff command.

rm -rf migrations
docker exec atlas-apply mysql -ppass -e "CREATE SCHEMA `my_schema_dev`;" # create a dev-db
docker exec atlas-apply mysql -ppass -e "DROP SCHEMA `atlas_schema_revisions`;"
atlas migrate diff \
  --dir "file://migrations" \
  --to "mysql://root:pass@localhost:3306/my_schema" \
  --dev-url "mysql://root:pass@localhost:3306/my_schema_dev"

To demonstrate that Atlas can also work on a schema level instead of a realm connection, we are running on a connection bound to the my_schema schema this time.

You should end up with the following migration directory:

-- create "tbl" table
CREATE TABLE `tbl` (`col` int NOT NULL, `col_2` text NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- create "tbl_2" table
CREATE TABLE `tbl_2` (`col` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Now, let's create a new migration file to create a table tbl_3 and update the directory integrity file.

atlas migrate new add_table --dir "file://migrations"
echo "CREATE TABLE `tbl_3` (`col` text NULL);" >> migrations/$(ls -t migrations | head -n1)
atlas migrate hash --force --dir "file://migrations"

Since we now have both a migration file representing our current database state and the new migration file to apply, we can make use of the --baseline flag:

atlas migrate apply \
  --dir "file://migrations" \
  --url "mysql://root:pass@localhost:3306/my_schema" \
  --baseline "20220908110527" # replace the version with the one generated by you
Migrating to version 20220908110847 from 20220908110527 (1 migrations in total):

  -- migrating version 20220908110847
    -> CREATE TABLE `tbl_3` (`col` text NULL);
  -- ok (14.325493ms)

  -------------------------
  -- 15.786455ms
  -- 1 migrations
  -- 1 sql statements

Outlook​

The Atlas migration engine is powering Ent and the execution engine is already being used within Ariga for several months. We will continue working on improving it, releasing cool features, such as assisted troubleshooting for failed migrations, a more intelligent, dialect-aware execution planning for things like MySQLs implicits commits and more.

Wrapping up​

In this post we learned about the new migration execution engine of Atlas and some information about its internals.

Further reading​

To learn more about Versioned Migration Authoring:

Have questions? Feedback? Find our team on our Discord server.