1. Introduction to Atlas

atlas

Atlas is a language-independent tool designed specifically for managing and migrating database schemas using modern DevOps principles. It provides two workflow options:

  • Declarative: Similar to Terraform, Atlas compares the current state of the database with the desired state defined using HCL, SQL, or ORM schema. Based on this comparison, it generates and executes a migration plan to transition the database to its desired state.
  • Versioned: Unlike other tools, Atlas automatically plans schema migrations for you. Users can describe their desired database schema using HCL, SQL, or their chosen ORM, and with Atlas, plan, review, and apply the necessary migrations to the database.

A key advantage of Atlas is its simplification of the complexity of database management, making it easy for version control, collaboration, and implementation.

2. Installing Atlas

Before using Atlas to manage database versions, we need to install it. Below are the installation steps for different platforms.

2.1 macOS + Linux Installation

On macOS or Linux systems, you can download and install the latest version of Atlas using the following command line:

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

This command will automatically detect the operating system version, download the suitable build version, and place the Atlas binary file in the executable path.

2.2 Installing via Homebrew

If you are using macOS and have already installed the Homebrew package manager, installing Atlas is as simple as running the command:

brew install ariga/tap/atlas

This will fetch the latest version of Atlas from Homebrew's repository and install it.

2.3 Docker Installation

Installing and running Atlas through Docker is a fast and convenient method, especially for temporary testing or for users who prefer not to install additional software on their host system.

First, pull the Atlas Docker image:

docker pull arigaio/atlas

Then, you can run a help command to confirm a successful installation:

docker run --rm arigaio/atlas --help

If the container needs to access the host network or local directories, use the --net=host flag and mount the required directories:

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

2.4 Windows Installation

Download the binary file, https://release.ariga.io/atlas/atlas-windows-amd64-latest.exe, and add the installation path to the system's PATH environment variable.

3. Exporting Existing Database Table Structure Using Atlas

Atlas provides the atlas schema inspect command, which can be used to export the structure of an existing database. This command supports reading database descriptions from the database URL and outputs them in three different formats: the default Atlas HCL format, SQL format, and JSON format. In this guide, we will show how to use Atlas HCL and SQL formats, as JSON format is typically used for processing output with jq.

To inspect a locally running MySQL instance and write the output to a file named schema.hcl, use the following command:

atlas schema inspect -u "mysql://root:pass@localhost:3306/example" > schema.hcl

Open the schema.hcl file to view the Atlas table structure definition describing the database (HCL is a database-independent table structure configuration format defined by Atlas). For example, this file will contain the following content:

table "users" {
  schema = schema.example
  column "id" {
    null = false
    type = int
  }
  column "name" {
    null = true
    type = varchar(100)
  }
  primary_key {
    columns = [column.id]
  }
}

This code block represents a table structure with id and name columns. The schema field references the example model definition defined elsewhere in this document. In addition, the primary_key sub-block specifies the id column as the primary key for the table. Atlas strives to mimic the syntax of the database being operated on. In this example, the id column has a type of int, while the name column has a type of varchar(100).

Similarly, to export a SQL model definition of the database, you can use the following command:

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

Open the schema.sql file to view the SQL description of the database, typically containing some CREATE TABLE statements.

This approach makes it convenient to understand the details of the existing database table structure and provides an accurate reference for subsequent versioned migrations.

4.1 Declarative Workflow

Atlas' Declarative Workflow allows users to declaratively define the desired final state of the database. Users only need to describe what they want the database schema's final state to be, and the Atlas tool will automatically generate and execute migration plans to safely transition the database schema from the current state to this expected state.

Below is how to use the Declarative Workflow to define and achieve the desired database state:

4.1.1 Defining the Target Table Structure

First, you need to create a file defining the expected database structure, which can be in HCL, SQL, or ORM (Object-Relational Mapping) format.

Taking HCL format as an example, define a new blog_posts table:

table "blog_posts" {
  schema = schema.example
  column "id" {
    null = false
    type = int
  }
  column "title" {
    null = true
    type = varchar(100)
  }
  column "body" {
    null = true
    type = text
  }
  column "author_id" {
    null = true
    type = int
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "author_fk" {
    columns     = [column.author_id]
    ref_columns = [table.users.column.id]
  }
}

Tip: The syntax format of HCL will be covered in a later section.

4.1.2 Perform Migration Using Atlas Tool

Once the database table structure definition is completed, you can use the schema apply command of Atlas to perform the migration.

atlas schema apply \
  -u "mysql://root:pass@localhost:3306/example" \
  --to file://schema.hcl

After executing the above command, Atlas will compare the existing database schema with the schema defined in the file, generate a migration plan, and ask the user for confirmation to execute. Once the user confirms the execution plan, Atlas will perform the migration on the database and update it to the desired state.

4.2 Versioned Workflow

The versioned workflow is another usage mode supported by Atlas, sometimes referred to as "change-based migration". It is suitable for scenarios where database schema changes need to be version-controlled and reviewed in the code review process.

The steps of the versioned workflow include:

4.2.1 Calculate Differences

Before starting the migration, it is necessary to compare the current database structure with the desired state and determine the differences between the two. This can be accomplished by running the atlas migrate diff command.

atlas migrate diff create_blog_posts \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://mysql/8/example"

The --dir specifies the URL of the migration folder, defaulting to file://migrations. The --to specifies the URL of the desired state (e.g., your development environment database), and --dev-url provides a URL for a development database used to calculate the differences (note that this --dev-url needs to specify an empty database that Atlas uses to calculate differences).

Tip: If you want to generate SQL files, refer to the previous section for setting the format using the --format parameter.

4.2.2 Apply Migration Changes

After the difference calculation is completed, Atlas will generate two migration files saved in the migrations folder. For example, if the selected format is SQL, the files generated by the diff command, such as the following SQL file, contain migration commands to create a new table:

-- create "blog_posts" table
CREATE TABLE `blog_posts` (
  `id` int NOT NULL,
  `title` varchar(100) DEFAULT NULL,
  `body` text DEFAULT NULL,
  `author_id` int NULL REFERENCES `users`(id),
  PRIMARY KEY (`id`)
);

Once the migration files are generated, you can use version control tools (e.g., Git) to manage these changes. This approach allows for making numerous database table structure modifications in the development environment and, when it's time for release, comparing the development environment and UAT environment using Atlas commands to generate database table structure migration files. These migration files can then be applied to the UAT and production environments to upgrade the database.

These two workflows, Declarative and Versioned, provide flexibility for different development and deployment modes, allowing teams to choose the method that best suits their project's needs for managing database schema changes.

5. HCL Format Description

5.1 Introduction

HCL is a declarative language used to describe database table structure definitions. Atlas uses the HCL format to write database schemas, providing a rich structure to describe different aspects of the database. The advantage of HCL lies in its readability, ease of maintenance, and support for features such as variable injection and additional annotations.

Tip: If your project needs to adapt to multiple databases, describing table structures in a database-agnostic way using HCL can be very convenient.

5.2 schema object

The schema object is used to describe a database schema. In MySQL and SQLite, it represents the DATABASE, while in PostgreSQL, it represents the SCHEMA. A HCL file can contain one or more schema objects.

schema "public" {
  comment = "A schema comment"
}

schema "private" {}

5.3 table object

The table object is used to describe a table in a SQL database, including columns, indexes, constraints, and various additional properties supported by different database drivers.

table "users" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "name" {
    type = varchar(255)
  }
  column "manager_id" {
    type = int
  }
  primary_key {
    columns = [
      column.id
    ]
  }
  index "idx_name" {
    columns = [
      column.name
    ]
    unique = true
  }
  foreign_key "manager_fk" {
    columns     = [column.manager_id]
    ref_columns = [table.users.column.id]
    on_delete   = CASCADE
    on_update   = NO_ACTION
  }
}

5.4 column object

The column is a sub-resource of table used to define the columns in the table.

column "name" {
  type = text
  null = false
}

column "age" {
  type = integer
  default = 42
}

column "active" {
  type = tinyint(1)
  default = true
}

5.5 primary_key object

The primary_key is a sub-resource of table that defines the primary key of the table.

primary_key {
  columns = [column.id]
}

5.6 foreign_key object

The foreign_key is a sub-resource of table that defines columns referencing columns in other tables.

table "orders" {
  schema = schema.market
  // ...
  column "owner_id" {
    type = integer
  }
  foreign_key "owner_id" {
    columns     = [column.owner_id]
    ref_columns = [table.users.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
}

5.7 index object

The index object represents an index on the table.

index "idx_name" {
  columns = [
    column.name
  ]
  unique = true
}