Getting started with SQL declarative migrations for a MySQL schema

Introduction

Atlas makes it easy to manage your database using the declarative migrations workflow. Declarative migrations focus on describing the desired state of the database and allow the migration engine to plan and execute the necessary actions to reach that state.

In this quickstart guide, we will go through the three essential steps for using Atlas to manage your database schema: inspecting your database, authoring the desired changes, and applying those changes.

Installing Atlas

macOS + Linux

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

curl -sSf https://atlasgo.sh | sh

Homebrew

Get the latest release with Homebrew:

brew install ariga/tap/atlas

Docker

To pull the Atlas image and run it as a Docker container:

docker pull arigaio/atlas
docker run --rm arigaio/atlas --help

If the container needs access to the host network or a local directory, use the --net=host flag and mount the desired directory:

docker run --rm --net=host \
  -v $(pwd)/migrations:/migrations \
  arigaio/atlas migrate apply
  --url "mysql://root:pass@:3306/test"

Windows

Download the latest release and move the atlas binary to a file location on your system PATH.

Manual Installation

If you want to manually install the Atlas CLI, pick one of the below builds suitable for your system.

• MacOS
  • amd64 (md5 / sha256)
  • arm64 (md5 / sha256)
• Windows
  • amd64 (md5 / sha256)
• Linux
  • amd64 (md5 / sha256)
  • arm64 (md5 / sha256)

Warming up

To demonstrate the examples in the article, let's start a docker container with an example database and create a table in it.

Use the following command to run a MySQL Docker container:

docker run --name atlas-demo -e MYSQL_ROOT_PASSWORD=pass -e MYSQL_DATABASE=example -p 3306:3306 -d mysql:latest

Once the container is running, you can connect to the MySQL server using the command below:

docker exec -it atlas-demo mysql -ppass --database=example

Once you're connected, you can create a table in it by running the following command:

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL
);

Step 1: Inspecting the database

Before planning any changes to your database, it can be useful to know what your database schema looks like. To do this, you can use the atlas schema inspect command to inspect your database. This command generates a file that represents your database schema, which can be used as a starting point for making changes.

Usage

atlas schema inspect [flags]

Example

The following command will inspect the current state of the schema example and store it in a file named schema.sql

atlas schema inspect \
  --url "mysql://root:pass@localhost:3306/example" \
  --format "{{ sql . }}" > schema.sql

schema.sql

-- create "customers" table
CREATE TABLE `customers` (
    `id` int NOT NULL AUTO_INCREMENT, 
    `first_name` varchar(255) NOT NULL, 
    `last_name` varchar(255) NOT NULL, 
    PRIMARY KEY (`id`)
);

info

To learn more about inspecting schema with Atlas in variety of use cases, visit the documentation here

Step 2: Planning a change

Once you have inspected your database, the next step is to plan a change. This is done by modifying the SQL file in a way that it defines the desired state of your schema. The file should include the changes you want to make to your database, such as creating tables, columns, or indexes.

info

In this article, we will be focusing on SQL-based schema inspection with Atlas. For those opting to use HCL schema, please refer to the documentation here.

Example

Let’s add the following table definition to the SQL schema file that we previously created with the atlas schema inspect command:

schema.sql

-- create "customers" table
CREATE TABLE `customers` (
    `id` int NOT NULL AUTO_INCREMENT, 
    `first_name` varchar(255) NOT NULL, 
    `last_name` varchar(255) NOT NULL, 
    PRIMARY KEY (`id`)
);

CREATE TABLE orders (
    id int NOT NULL AUTO_INCREMENT, 
    customer_id int NOT NULL, 
    order_date date NOT NULL, 
    PRIMARY KEY (id), 
    CONSTRAINT customer_id_fk FOREIGN KEY (customer_id) REFERENCES example.customers (id)
);

Step 3: Applying the change

The final step is to apply the change to your database. To do this, simply run the atlas schema apply command. This command will compare the current state of your database to the desired state defined in your SQL file and make the necessary changes.

Usage

atlas schema apply [flags]

Example

Let’s apply the changes we created in Step 2 using the following command:

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

The role of the Dev Database

Atlas loads the desired state by executing the SQL files onto the provided dev database, compares it against the database current state by inspecting its schema and writes a migration plan for moving from the current state to the desired state.

-- Planned Changes:
-- Create "orders" table
CREATE TABLE `orders` (`id` int NOT NULL AUTO_INCREMENT, `customer_id` int NOT NULL, `order_date` date NOT NULL, PRIMARY KEY (`id`), INDEX `customer_id_fk` (`customer_id`), CONSTRAINT `customer_id_fk` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Use the arrow keys to navigate: ↓ ↑ → ← 
? Are you sure?: 
  ▸ Apply
    Abort

Selecting Apply will apply the changes to the database.

You can inspect the latest state of the database again and verify that the changes have been applied by re-running the atlas schema inspect command:

atlas schema inspect -u "mysql://root:pass@localhost:3306/example" --format "{{ sql . }}"

-- create "customers" table
CREATE TABLE `customers` (
    `id` int NOT NULL AUTO_INCREMENT, 
    `first_name` varchar(255) NOT NULL, 
    `last_name` varchar(255) NOT NULL, 
    PRIMARY KEY (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- create "orders" table
CREATE TABLE `orders` (
    `id` int NOT NULL AUTO_INCREMENT, 
    `customer_id` int NOT NULL, 
    `order_date` date NOT NULL, PRIMARY KEY (`id`), 
    INDEX `customer_id_fk` (`customer_id`), 
    CONSTRAINT `customer_id_fk` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Great! Our changes have been successfully applied to the example schema.

Conclusion

By following the steps above, you can inspect the current state of the database, make changes to the schema definition file and apply those changes to the database with confidence.

The declarative migration is a flexible way to manage your database schema. It allows you to define your desired state, and then automatically apply those changes to your database. This can save you time and effort, and also reduce the risk of errors or inconsistencies in your database schema.

info

Atlas supports both Declarative and Versioned workflows for managing database schemas. The choice between the two depends on your requirements. To understand the difference and choose the best option for you, visit the documentation on Declarative vs Versioned workflow here.

Need More Help?

Join the Ariga Discord Server for early access to features and the ability to provide exclusive feedback that improves your Database Management Tooling.

Sign up to our newsletter to stay up to date about Atlas, and the cloud platform Atlas Cloud.