Skip to main content

Automatic Migration Authoring

With the atlas migrate diff command users can implement a kind of workflow that we call versioned migration authoring. This workflow is a synthesis between declarative workflows, where developers specify the desired state of their database, and versioned migrations where each change is explicitly defined as a migration script with a specific version.

Practically speaking, this means developers define the desired state with either an HCL schema definition or a database connection, and Atlas maintains the migrations directory, which contains the explicit SQL scripts to move from one version to the next.

Generate migrations from Atlas schemas​

Suppose we have an Atlas schema with one table and an empty migration directory:

schema.hcl
schema "test" {}

table "users" {
  schema = schema.test
  column "id" {
    type = int
  }
}

Let's run atlas migrate diff with the necessary parameters to generate a migration script for creating our users table:

  • --dir the URL to the migration directory, by default it is file://migrations.
  • --to the URL of the desired state, an HCL file or a database connection.
  • --dev-url a URL to a Dev Database that will be used to compute the diff.
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "mysql://root:pass@:3306/test"

Run ls migrations, and you will notice Atlas created 2 files:

By default, migration files are named with the following format {{ now }}_{{ name }}.sql. If you wish to use a different file format, use the format query parameter in the directory URL.

-- create "users" table
CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Let's repeat the process above by changing our HCL schema file and running Atlas migration authoring again. We add a new column name to our HCL schema:

schema.hcl
schema "test" {}

table "users" {
  schema = schema.test
  column "id" {
    type = int
  }
  column "name" {
    type = varchar(255)
  }
}

Then, run atlas migrate diff:

atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "mysql://root:pass@:3306/test"

You will notice Atlas added a new file to the migration directory:

-- modify "users" table
ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL;

The following diagram explains how it works. Atlas loads the current state by replaying the migration directory onto the provided dev database, compares it against the desired state and writes a new migration script for moving from the current to the desired state.

Diff From Schema

Generate migrations from database schemas​

Suppose we have a database with a users table that was created manually or by an ORM like Ent, we can tell Atlas that this is our desired state, and we want to generate a migration script to create this table.

mysql> describe users;

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

Let's run atlas migrate diff with the necessary parameters to generate a migration script for creating our users table:

atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "mysql://root:pass@:3306/public" \
  --dev-url "mysql://root:pass@:3306/test"

Run ls migrations, and you will notice Atlas created 2 files:

By default, migration files are named with the following format {{ now }}_{{ name }}.sql. If you wish to use a different file format, use the format query parameter in the directory URL.

-- create "users" table
CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Generate migrations with custom qualifiers​

When working on a specific database schema, Atlas generates migration scripts without schema qualifiers to allow executing them multiple times on different schemas. However, in some cases, it is necessary to have those qualifiers. To address this, Atlas allows passing another flag to migrate diff named --qualifier.

Let's run the example above, with the --qualifier flag and compare the output:

atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "mysql://root:pass@:3306/test" \
  --qualifier "market"

Running cat migrations/*.sql will print the same migration script but the users table will be qualified with the market schema:

-- create "users" table
CREATE TABLE `market`.`users` (`id` int NOT NULL, `name` varchar(255) NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Generate migrations with custom formats​

Some migration tools use a different file format than the one used by Atlas. You can control the format of the migration directory by passing in the format query parameter to the migration directory URL.

atlas migrate diff create_users \
  --dir "file://migrations?format=golang-migrate" \
  --to "file://schema.hcl" \
  --dev-url "mysql://root:pass@:3306/test"

Run ls migrations, and you will notice Atlas created 3 files:

-- create "users" table
CREATE TABLE `users` (`name` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Generate migrations for the entire database​

Atlas supports generating migrations for databases or multiple schemas. In PostgreSQL, a database can be created with the CREATE DATABASE command and can hold multiple schemas. In MySQL however, a database is an instance with one or more schemas.

Suppose we have an Atlas schema that defines two database schemas where each one contains a single table.

schema.hcl
schema "auth" {}
schema "market" {}

table "users" {
  schema = schema.market
  column "name" {
    type = int
  }
}

table "tokens" {
  schema = schema.auth
  column "value" {
    type = int
  }
}

Let's run atlas migrate diff to generate migration scripts for creating the entire schema. However, unlike the previous examples where the --dev-url flag was set to a URL of a specific schema, in this case we omit the schema name from the connection string.

atlas migrate diff create_all \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "mysql://root:pass@:3306/"

Running cat migrations/*.sql will print the followings:

-- add new schema named "auth"
CREATE DATABASE `auth`;
-- add new schema named "market"
CREATE DATABASE `market`;
-- create "tokens" table
CREATE TABLE `auth`.`tokens` (`value` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- create "users" table
CREATE TABLE `market`.`users` (`name` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

As you can see, Atlas generates statements for creating the auth and market schemas, and added them as qualifiers in the created tables.

Reference​

CLI Command Reference