Database migrations are often one of the most dreaded parts of working with SQL databases. In many cases, teams end up keeping a bunch of timestamped .sql files and automatically running them in order. While this work, it doesn’t scale well: the more developers you have working on your schema, the more you increase the risk of conflicts. Atlas solves this problem by offering you to define your SQL schemas in a declarative manner, without requiring you to use a specific ORM.

Imperative vs Declarative

The traditional way of managing database schemas is through imperative changes. It consists of describing the steps required to achieve the state you want. This is consistent with the tools given to us by SQL: once you have created a table with CREATE TABLE, you’re not supposed to re-use CREATE TABLE but to append changes with ALTER TABLE.

Declarative programming works by describing the state you expect and letting your tools figure out how to get there. This is similar to what can be found in languages like Prolog or LISP, or tools like terraform. Declarative solutions are a great match for infrastructure problems because they allow you to keep track of the expected state of your systems, and let you use git for versioning and merging changes. This removes toil from the ops team while increasing visibility on the system’s state.

Getting started with Atlas

To create a table with Atlas, you just have to write a CREATE TABLE SQL statement. Like the following, which I will write in a schema.sql:

CREATE TABLE `users` (
    `id` int NOT NULL,
    `name` varchar(100) NULL,
    `password` char(88) NOT NULL,
    PRIMARY KEY (`id`)
);

To run this in my database, you can use atlas schema apply, which takes the URL of your database using -u, the file path using --to, and the address of a test database (for linting) using --dev-url (you can use docker:// to have Atlas launch an ephemeral docker container).

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

Now if you want to modify the table you created, you can modify the CREATE TABLE statement in schema.sql. For example, I turn the name columns into NOT NULL:

CREATE TABLE `users` (
    `id` int NOT NULL,
    `name` varchar(100) NOT NULL,
    `password` char(88) NOT NULL,
    PRIMARY KEY (`id`)
);

Rerunning the same atlas schema apply command will modify the database appropriately. In the output, we can see that Atlas generated the following SQL statement to achieve that:

ALTER TABLE `users` MODIFY COLUMN `name` varchar(100) NOT NULL;

Using HCL

Atlas also allows you to use HCL to define schemas. You might already be familiar with HCL if you have been using Terraform, but if not, the main benefit is that it is easy to read and modify for both humans and computers. This is interesting if you ever need your CI to interact with your database schemas.

The users table from the previous example would look like this in HCL:

table "users" {
  schema = schema.db
  column "id" {
    null = false
    type = int
  }
  column "name" {
    null = false
    type = varchar(100)
  }
  column "password" {
    null = false
    type = char(88)
  }
  primary_key {
    columns = [column.id]
  }
}

As a bonus, HCL doesn’t require a database for linting, so if you choose to use this, you can skip the --dev-url parameter when applying.

Migrating an existing system

With an already running database, you can use atlas schema inspect to generate the schema files. It also requires a -u parameter for the database URL and --format for the output format (SQL/HCL/JSON).

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

If you are already using an imperative migration system, and do not want to throw it away right now, you can also use Atlas to generate migration files. This is done using the atlas migrate diff [migration_name] command, which takes a --dir parameter for the migration folder, a --to parameter for the schema file, and a --dev-url parameter like the apply command.

atlas migrate diff my_migration \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://mysql/8/test"

Use atlas migrate apply to run the migrations:

atlas migrate apply \
  -u "mysql://root:pass@localhost:3306/db" \
  --dir "file://migrations"

Integrating Atlas to your Go codebase

After using Atlas for a bit, you might need to write a wrapper for it or want to use it as part of your custom infrastructure tooling. This is made simple with their Go SDK. The packages ariga.io/atlas-go-sdk/atlasexec will expose all the high-level functions that you can use for that.

The example below behaves like atlas schema apply:

func main() {
    client, err := atlasexec.NewClient(".", "atlas")
    if err != nil {
        log.Fatalf("failed to initialize client: %v", err)
    }

    params := atlasexec.SchemaApplyParams{
        URL: "mysql://root:pass@localhost:3306/db",
        To: "file://schema.hcl",
    }
    res, err := client.SchemaApply(context.Background(), &params)
    if err != nil {
        log.Fatalf("Failed to apply schema: %v", err)
    }

    log.Printf("Applied %d changes (%d pending)\n", len(res.Changes.Applied), len(res.Changes.Pending))
}