Skip to main content

Automatic Schema Migration Planning

The atlas migrate diff command streamlines the process of writing schema migrations by automatically generating the SQL scripts needed to migrate a database schema from its current state to the new desired state. How does it work?

Developers define the desired state and Atlas maintains the migrations directory, which contains the explicit SQL migration scripts to move from one version to the next. The desired state can be defined using an HCL or SQL schema definition, a database URL, or an external schemas like ORM.

Summary​

To get started with versioned migrations, run atlas migrate diff. This command generates a new migration file that aligns the migration directory with the state defined by the desired schema. Below are a few examples of how to use this command to generate schema migrations from various sources:

MySQL/MariaDB:

atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://mysql/8/dev" # Or: "docker://mariadb/latest/dev"

PostgreSQL:

atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/15/dev?search_path=public"
Ensure linear migration history

Teams that have connected their project to Atlas Cloud (see setup) will get a prompt in the CLI if their migration directory is out of sync with the latest version in Atlas Cloud. This ensures that new migration files are added in a sequential order, preventing unexpected behavior. For example:

atlas migrate diff --env dev

? Your directory is outdated (2 migrations behind). Continue or Abort:
▸ Continue (Rebase later)
  Abort (Pull changes and re-run the command)

Additionally, the atlas migrate lint command helps enforce this requirement during the CI stage. Learn more on how to integrate Atlas into your GitHub Actions or GitLab CI Components.

Generate migrations from HCL schemas​

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

schema.hcl
schema "public" {}

table "users" {
  schema = schema.public
  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 of the migration directory. Defaults to file://migrations, which is automatically created if it does not exist.
  • --to - the URL(s) of the desired state. Can be defined using an HCL or SQL schema, a database URL, or external ORM.
  • --dev-url a URL to a Dev Database that will be used to compute the diff.
  • --format (optional) - Go template to use to format the output.
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://postgres/15/test?search_path=public"
info

If you are working with multiple schemas and want qualified identifiers to appear in the migration files, omit the schema parameter from the --dev-url flag. In PostgreSQL, this corresponds to the search_path; in MySQL/MariaDB, it is the database in the URL path.

Run ls migrations, and you will notice that Atlas created two files. For example, for the MySQL flavor, the following files will be created:

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 "public" {}

table "users" {
  schema = schema.public
  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 "docker://postgres/15/dev?search_path=public"
info

If you are working with multiple schemas and want qualified identifiers to appear in the migration files, omit the schema parameter from the --dev-url flag. In PostgreSQL, this corresponds to the search_path; in MySQL/MariaDB, it is the database in the URL path.

You will notice Atlas added a new file to the migration directory. For example, for the MySQL flavor, the following file will be created:

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

The following diagram explains how Atlas automates database schema changes. 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 SQL schemas​

Atlas allows you to define your desired state using SQL schemas. An SQL schema can be a single file containing CREATE and ALTER statements, or a directory with multiple SQL files. As an example, suppose we have an SQL schema with one table and an empty migration directory:

schema.sql
CREATE TABLE "users" (
  "id" int NOT NULL
)

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 or SQL schema definition, or a database URL.
  • --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.sql" \
  --dev-url "docker://postgres/15/dev?search_path=public"
info

If you are working with multiple schemas and want qualified identifiers to appear in the migration files, omit the schema parameter from the --dev-url flag. In PostgreSQL, this corresponds to the search_path; in MySQL/MariaDB, it is the database in the URL path.

Run ls migrations, and you will notice that Atlas created two files. For example, for the MySQL flavor, the following files will be created:

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 SQL schema file and running Atlas migration authoring again. We add a new column name to our SQL schema:

schema.sql
CREATE TABLE "users" (
  "id" int NOT NULL,
  "name" varchar(255) NOT NULL
)

Then, run atlas migrate diff:

atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/15/dev?search_path=public"
info

If you are working with multiple schemas and want qualified identifiers to appear in the migration files, omit the schema parameter from the --dev-url flag. In PostgreSQL, this corresponds to the search_path; in MySQL/MariaDB, it is the database in the URL path.

You will notice that Atlas added a new file to the migration directory. For example, for the MySQL flavor, the following file will be created:

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

To summarize, the example above explains how Atlas loads the current state by replaying the migration directory onto the specified dev-database, compares it to the desired state defined in the SQL schema file, and writes a new migration script for moving from the current to the desired state.

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 "docker://mysql/8/dev"

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 "docker://mysql/8/dev" \
  --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 "docker://mysql/8/dev"

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 "docker://postgres/15"

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

-- Add new schema named "auth"
CREATE SCHEMA "auth";
-- Add new schema named "market"
CREATE SCHEMA "market";
-- create "tokens" table
CREATE TABLE "auth"."tokens" ("value" integer NOT NULL);
-- create "users" table
CREATE TABLE "market"."users" ("name" integer NOT NULL);

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

Diff Policy​

Atlas allows configuring the schema diffing policy in project configuration to fine-tune or modify suggested changes before they are written to the migration directory:

To instruct Atlas to create and drop indexes concurrently, set the concurrent_index option in the diff block of the environment configuration. Note that such migrations are tagged with atlas:txmode none to ensure they do not run within a transaction.

atlas.hcl
env "local" {
  diff {
    // By default, indexes are not created or dropped concurrently.
    concurrent_index {
      add  = true
      drop = true
    }
  }
}

Indented SQL​

Starting in v0.35, the migrate diff command uses two-space indentation for generated SQL by default. You can change or remove the indentation using the --format flag. For example:

# Indent SQL statements with tab characters (\t).
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://mysql/8/dev" \
  --format '{{ sql . "  " }}'

# Disable indentation.
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://mysql/8/dev" \
  --format '{{ sql . "" }}'

Exclude Objects​

note

This option is not recommended for general use. It is intended for use when you encounter limitations in the Atlas schema definition or when your schema references objects that are not part of the desired state (schema) and you want Atlas to ignore their existence.

Atlas allows setting exclusion patterns for the migrations directory. Objects that match the exclusion patterns are not considered during the migrate diff stage.

Using this option, users can (manually) define objects such as tables, functions, and foreign keys in the migration directory that are not part of the desired state (schema), and instruct Atlas not to drop them during the migrate diff stage - essentially, to ignore their existence.

atlas.hcl
env "dev" {
  migration {
    exclude = ["*.constraint_name"]
  }
}

For more examples, see the exclude documentation.

Video Tutorial​

Reference​

CLI Command Reference