Learning new things​

One of my favorite things about software engineering, is that it's a career of continuous learning. Every new project or challenge presents us with an opportunity to increase our knowledge and improve our skills. Different people learn in different ways, but for me, one of the most effective ways to learn something new has always been by doing. Sure, reading books, watching videos and immersing myself in technical documentation is foundational, but I only feel like I really understand something after I've applied it in some practical way.

This is why I'm excited to announce that we've partnered with Wilco to create a new kind of learning experience for Atlas users. Wilco develops a platform, built to emulate the conditions at a tech startup, sends users on "quests" that cover everyday engineering tasks - from deploying an app to finding the root cause of a production issue - utilizing real-life tech stacks.

When I first heard about Wilco, I was immediately intrigued by the idea of using a simulation game to teach people about database migrations. Particularly interesting, is the possibility to present people with opportunities to solve all kinds of issues that many engineers only hit during a serious outage when there's very little room for error.

Introducing Wilco​

When you use Wilco, you're put in the shoes of a software engineer at a fictional startup. You're given access to a made up chat system humoristically called "Snack" where you meet different characters that you can interact with. You are also given access to a real GitHub repo and the means to run a real development environment on your local machine.

The new Atlas quest on Wilco​

In the first quest we're releasing today on Wilco, you will be tasked with planning a seemingly simple database migration to support the development of a new feature in your imaginary company's app. As your team starts to roll out the feature, you will quickly realize that there are some serious issues that must be resolved, and fast!

And now, without further ado, you can head over to start your quest!

What's next?​

Have questions? Feedback? Feel free to reach out on our Discord server.

Atlas is most commonly used for managing and applying schema changes to databases, but it can also be used for something else: exploring and understanding database schemas.

With inspection, Atlas connects to your database, analyzes its structure from the metadata tables, and creates a graph data structure that maps all the entities and relations within the database. Atlas can then take this graph and represent it in various formats for users to consume. In this post, I will present two such forms of representation: Entity Relationship Diagrams (ERDs) and JSON documents.

Schemas as ERDs​

One of the most useful ways to represent a database schema is using an Entity Relationship Diagram (ERD). This allows developers to see the schema in a visual and intuitive way, making it easy to understand the relationships between different elements of the database. When using ERDs, because the data is presented in a graph format, you can easily navigate through the schema and see how different entities are connected. This can be especially useful when working with complex or large databases, as it allows you to quickly identify patterns and connections that might not be immediately obvious when looking at the raw data.

To automatically generate an ERD from your database, you can use the Explore feature of Atlas Cloud. To visualize a schema using the Explore feature, you need to provide your database schema in one of two ways:

1. Provide a connection string to your database. This will allow Atlas Cloud to connect to your database and automatically generate a schema from the metadata tables. Note: this method only works for databases that are publicly accessible via the internet.

2. Provide the schema as an Atlas HCL file. If you have an existing Atlas project, you can use the atlas schema inspect command to generate the HCL file from your database.

   After installing Atlas, you can run the following command to generate the HCL representation of your database schema:

   # MySQL
   atlas schema inspect -u mysql://root:pass@localhost:3306/db_name
   
   # PostgreSQL
   atlas schema inspect postgres://postgres:pass@localhost:5432/db_name?sslmode=disable
   

Schemas as JSON documents​

In addition to producing ERDs, Atlas can also produce a JSON document that represents the database schema. One of the key benefits of representing the database schema as a JSON document is that it allows you to use standard tools like jq to analyze the schema programmatically. jq is a popular command-line tool for working with JSON data, and it can be especially useful for exploring and manipulating the schema data generated by Atlas.

With jq, you can easily extract specific information from the schema, such as the names of all the tables in the database or the foreign key relationships between different entities. This makes it easy to write scripts or programs that can automatically analyze the schema and identify potential issues or opportunities for optimization.

To get the JSON representation of your database schema, you can use the atlas schema inspect command with a custom logging format:

atlas schema inspect -u '<url>' --format '{{ json . }}'

This will output the schema as a JSON document:

{
  "schemas": [
    {
      "name": "test",
      "tables": [
        {
          "name": "blog_posts",
          "columns": [
            {
              "name": "id",
              "type": "int"
            },
            {
              "name": "title",
              "type": "varchar(100)",
              "null": true
            },
            // .. Truncated for brevity ..
        ]
    }
  ]
}

Once your schema is represented as a JSON document, you can use jq to analyze it. For example, to get a list of all the tables that contain a foreign key, run:

atlas schema inspect -u '<url>' --format '{{ json . }}' | jq '.schemas[].tables[] | select(.foreign_keys | length > 0) | .name'

This will output:

"blog_posts"

Wrapping up​

In this blog post, we demonstrated how Atlas can be used as a schema inspection and visualization tool, in addition to its more commonly known use as a schema migration tool. We showed how to use the Explore feature to create an ERD from your database schema, and how to use the atlas schema inspect command to generate a JSON document that can be analyzed using jq and other tools.

Have questions? Feedback? Feel free to reach out on our Discord server.

Most software projects are backed by a database, that's widely accepted. The schema for this database almost always evolves over time: requirements change, features are added, and so the application's model of the world must evolve. When this model evolves, the database's schema must change as well. No one wants to (or should) connect to their production database and apply changes manually, which is why we need tools to manage schema changes. Most ORMs have basic support, but eventually projects tend to outgrow them. This is when projects reach to choose a schema migration tool.

Many such tools exist, and it's hard to know which to choose. My goal in this article is to present 3 popular choices for migration tools for Go projects to help you make this decision.

By way of introduction (and full disclosure): my name is Pedro Henrique, I'm a software engineer from Brazil, and I've been a contributing member of the Ent/Atlas community for quite a while. I really love open-source and think there's room for a diverse range of tools in our ecosystem, so I will do my best to provide you with an accurate, respectful, and fair comparison of the tools.

golang-migrate - Created: 2014 GitHub Stars: 10.3k
Golang migrate is one of the most famous tools for handling database migrations. Golang migrate has support for many database drivers and migration sources, it takes a simple and direct approach for handling database migrations.

Goose - Created: 2012 GitHub Stars: 3.2k
Goose is a solid option when choosing a migration tool. Goose has support for the main database drivers and one of its main features is support for migrations written in Go and more control of the migrations application process.

Atlas - Created: 2021 GitHub Stars: 2.1k
Atlas is an open-source schema migration tool that supports a declarative workflow to schema migrations, making it a kind of "Terraform for databases". With Atlas, users can declare their desired schema and let Atlas automatically plan the migrations for them. In addition, Atlas supports classic versioned migration workflows, migration linting, and has a GitHub Actions integration.

Golang migrate​

Golang migrate was initially created by Matt Kadenbach. In 2018 the project was handed over to Dale Hui, and today the project resides on the golang-migrate organization and is actively maintained, having 202 contributors.

One of Golang migrate's main strengths is the support for various database drivers. If your project uses a database driver that is not very popular, chances are that Golang migrate has a driver for it. For cases where your database is not supported, Golang migrate has a simple API for defining new database drivers. Databases supported by Golang migrate include: PostgreSQL, Redshift, Ql, Cassandra, SQLite, MySQL/MariaDB, Neo4j, MongoDB, Google Cloud Spanner, and more.

Another feature of Golang migrate is the support for different migrations sources, for cases where your migration scripts resides on custom locations or even remote servers.

Goose​

Goose has a similar approach to Golang migrate. The project was initially created by Liam Staskawicz in 2012, and in 2016 Pressly created a fork improving the usage by adding support for migrations in Go, handling cases of migrations out of order and custom schemas for migration versioning. Today Goose has 80 contributors.

Goose only provides support for 7 database drivers, so if your project uses one of the main databases in the market, Goose should be a good fit. For migration sources, Goose allows only the filesystem, it's worth pointing out that with Go embed it is possible to embed the migration files on a custom binary. Goose's main difference from Golang migrate is the support for migrations written in Go, for cases where it is necessary to query the database during the migration. Goose allows for different types of migration versioning schemas, improving one key issue with Golang migrate.

Atlas​

Atlas takes a completely different approach to Golang migrate or Goose. While both tools only focus on proving means of running and maintaining the migration directory, Atlas takes one step further and actually constructs a graph representing the different database entities from the migration directory contents, allowing for more complex scenarios and providing safety for migration operations.

Migrations in Atlas can be defined in two ways:

• Versioned migrations are the classical style, where the migration contents are written by the developer using the database language.
• Declarative migrations are more similar to Infrastructure-as-Code, where the schema is defined in a Terraform-like language and the migrations commands are calculated based on the current and desired state of the database. It's possible to use Atlas in a hybrid way as well, combining both styles, called Versioned Migration Authoring where the schema is defined in the Atlas language, but the Atlas engine is used to generate versioned migrations.

On top of Atlas's ability to load the migration directory as a graph of database entities, an entire infrastructure of static code analysis was built to provide warnings about dangerous or inefficient operations. This technique is called migration linting and can be integrated with the Atlas GitHub Action during CI.

In addition, if you would like to run your migrations using Terraform, Atlas has a Terraform provider as well.

Another key point that Atlas solves is handling migration integrity, which becomes a huge problem when working with multiple branches that all make schema changes. Atlas solves this problem by using an Integrity file. While we are on the topic of integrity, one key feature of Atlas is the support for running the migrations inside a transaction, unlike Goose during the process of migration. Atlas acquires a lock ensuring that only one migration happens at a time and the migration order/integrity is respected. For cases where problems are found, Atlas makes the troubleshooting process easier, allowing schema inspections, dry runs and providing helpful links to the common problems and solutions.

Feature comparison​

• 1: Atlas provides a few packages related to database operations, but the use is limited to complex cases and there is no package that provides migration usage out of the box.

Wrapping up​

In this post we saw different strengths of each migration tool. We saw how Golang migrate has a great variety of database drivers and database sources, how Goose allows use to written migration in Go for the complexes migration scenarios and how Atlas makes the migration a complete different business, improving the safety of the migration operations and bringing concepts from others fields.

Introduction​

Today we are very excited to announce the release of Atlas Terraform Provider v0.4.0. This release brings some exciting new features and improvements to the provider which we will describe in this post.

In addition, this release is the first to be published under our new partnership with HashiCorp as a Technology Partner. Atlas is sometimes described as a "Terraform for Databases", so we have high hopes that this partnership will help us to bring many opportunities to create better ways for integrating database schema management into IaC workflows.

What's new​

When people first hear about integrating schema management into declarative workflows, many raise the concern that because making changes to the database is a high-risk operation, they would not trust a tool to do it automatically.

This is a valid concern, and this release contains three new features that we believe will help to address it:

• SQL plan printing
• Versioned migrations support
• Migration safety verification

SQL Plan Printing​

In previous versions of the provider, we displayed the plan as a textual diff showing which resources are added, removed or modified. With this version, the provider will also print the SQL statements that will be executed as part of the plan.

For example, suppose we have the following schema:

schema "market" {
 charset = "utf8mb4"
 collate = "utf8mb4_0900_ai_ci"
 comment = "A schema comment"
}

table "users" {
 schema = schema.market
 column "id" {
   type = int
 }
 column "name" {
   type = varchar(255)
 }
 primary_key {
   columns = [
     column.id
   ]
 }
}

And our Terraform module looks like this:

terraform {
 required_providers {
   atlas = {
     source  = "ariga/atlas"
     version = "0.4.0"
   }
 }
}

provider "atlas" {}

data "atlas_schema" "market" {
 src        = file("${path.module}/schema.hcl")
 dev_db_url = "mysql://root:pass@localhost:3307"
}

resource "atlas_schema" "market" {
 hcl        = data.atlas_schema.market.hcl
 url        = "mysql://root:pass@localhost:3306"
 dev_db_url = "mysql://root:pass@localhost:3307"
}

When we run terraform plan we will see the following output:

Plan: 1 to add, 0 to change, 0 to destroy.
╷
│ Warning: Atlas Plan
│
│   with atlas_schema.market,
│   on main.tf line 17, in resource "atlas_schema" "market":
│   17: resource "atlas_schema" "market" {
│
│ The following SQL statements will be executed:
│
│
│ -- add new schema named "market"
│ CREATE DATABASE `market`
│ -- create "users" table
│ CREATE TABLE `market`.`users` (`id` int NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`)) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci
│

Versioned migrations​

Atlas supports two types of workflows: Declarative and Versioned. With declarative workflows, the plan to migrate the database is generated automatically at runtime. Versioned migrations provide teams with a more controlled workflow where changes are planned, checked-in to source control and reviewed ahead of time. Until today, the Terraform provider only supported the declarative workflow. This release adds support for versioned migrations as well.

Suppose we have the following migration directory of two files:

CREATE TABLE `users` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `age` bigint(20) NOT NULL,
 `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `age` (`age`)
);

h1:OlaV3+7xXEWc1uG/Ed2zICttHaS6ydHZmzI7Hpf2Fss=
20221101163823_create_users.sql h1:mZirkpXBoLLm+M73EbHo07muxclifb70fhWQFfqxjD4=

We can use the Terraform Atlas provider to apply this migration directory to a database:

terraform {
 required_providers {
   atlas = {
     source  = "ariga/atlas"
     version = "0.4.0"
   }
 }
}

provider "atlas" {}

// The `atlas_migration` data source loads the current state of the given database
// with regard to the migration directory.
data "atlas_migration" "hello" {
 dir = "migrations?format=atlas"
 url = "mysql://root:pass@localhost:3306/hello"
}

// The `atlas_migration` resource applies the migration directory to the database.
resource "atlas_migration" "hello" {
 dir     = "migrations?format=atlas"
 version = data.atlas_migration.hello.latest # Use latest to run all migrations
 url     = data.atlas_migration.hello.url
 dev_url = "mysql://root:pass@localhost:3307/test"
}

Running terraform plan will show the following output:

data.atlas_migration.hello: Reading...
data.atlas_migration.hello: Read complete after 0s [id=migrations?format=atlas]

Terraform used the selected providers to generate the following execution plan.
Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # atlas_migration.hello will be created
  + resource "atlas_migration" "hello" {
      + dev_url = (sensitive value)
      + dir     = "migrations?format=atlas"
      + id      = (known after apply)
      + status  = (known after apply)
      + url     = (sensitive value)
      + version = "20221101163823"
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Linting​

Atlas provides extensive support for linting database schemas. This release adds support for linting schemas as part of the Terraform plan. This means that you can now run terraform plan and see if there are any linting errors in your schema. This is especially useful when you are using the versioned migrations workflow, as you can now run terraform plan to see if there are any linting errors in your schema before you apply the changes.

Suppose we add the following migration:

ALTER TABLE users
 DROP KEY age,
 ADD CONSTRAINT NAME UNIQUE (`name`);

If we run terraform plan on the above schema, Terraform prints the following warning:

╷
│ Warning: data dependent changes detected
│
│   with atlas_migration.hello,
│   on main.tf line 20, in resource "atlas_migration" "hello":
│   20: resource "atlas_migration" "hello" {
│
│ File: 20221101165036_change_unique.sql
│
│ - MF101: Adding a unique index "NAME" on table "users" might fail in case column
│ "name" contains duplicate entries
╵

Atlas detected that the migration may fail in case the column name contains duplicate entries! This is a very useful warning that can help you avoid unpredicted failed deployments. Atlas supports many more safety checks, which you can read about here.

Wrapping up​

In this blogpost we have discussed three new features that were added to the Terraform Atlas provider that are designed to make it safer and more predictable to manage your database schemas with Terraform. We hope you will enjoy this release!

Have questions? Feedback? Feel free to reach out on our Discord server.

Wikipedia defines Multi-tenancy as:

a software architecture in which a single instance of software runs on a server and serves multiple tenants.

In recent years, multitenancy has become a common topic in our industry as many organizations provide service to multiple customers using the same infrastructure. Multitenancy usually becomes an issue in software architecture because tenants often expect a decent level of isolation from one another.

In this post, I will go over different known approaches for achieving multi-tenancy and discuss the approach we took to build Ariga's cloud platform. In addition, I will demonstrate how we added built-in support for multi-tenant environments in Atlas to overcome some of the challenges we faced.

Introduction​

Throughout the last few years, I have had the opportunity to implement multi-tenancy in various ways. Some of them might be familiar to you:

1. A separate environment (deployment) per tenant, where isolation is achieved at both compute and data layers.
2. A schema (named database) per tenant, where there is one environment for compute (e.g., a K8S cluster), but tenants are stored in different databases or schemas. Isolation is achieved at the data layer while compute resources are shared.
3. One environment for all tenants, including the data layer. Typically, in this case, each table holds a tenant_id column that is used to filter statements by the tenant. Both data and compute layers are shared, with isolation achieved at the logical, database query level.

Each approach has pros and cons, but I want to briefly list the main reasons we chose to build our cloud platform based on the second option: schema per tenant.

1. Management: Easily delete, backup tenants, and allow them to export their data without affecting others.
2. Isolation: Limit credentials, connection pooling, and quotas per tenant. This way, one tenant cannot cause the database to choke and interrupt other tenants in case they share the same physical database.
3. Security and data privacy: In case it is required, some tenants can be physically separated from others. For example, data can be stored in the tenant's AWS account, and the application can connect to it using a secure connection, like VPC peering in AWS.
4. Code-maintenance: Most of the application code is written in a way that it is unaware of the multi-tenancy. In our case, there is one layer "at the top" that attaches the tenant connection to the context, and the API layer (e.g., GraphQL resolver) extracts the connection from the context to read/write data. As a result, we are not concerned that API changes will cross tenant boundaries.
5. Migration: Schema changes can be executed first on "test tenants" and fail-fast in case of error.

The primary con to this approach was that there was no elegant way to execute migrations on multiple databases (N times) in Atlas. In the rest of the post, I'll cover how we solved this problem in Ariga and added built-in support for multi-tenancy in Atlas.

Atlas config file​

Atlas provides a convenient way to describe and interact with multiple environments using project files. A project file is a file named atlas.hcl and contains one or more env blocks. For example:

env "local" {
  url = "mysql://root:pass@:3306/"
  migrations {
    dir = "file://migrations"
  }
}

env "prod" {
  // ... a different env
}

Once defined, a project's environment can be worked against using the --env flag. For example:

atlas migrate apply --env local

The command above runs the schema apply against the database that is defined in the local environment.

Multi-Tenant environments​

The Atlas configuration language provides a few capabilities adopted from Terraform to facilitate the definition of multi-tenant environments. The first is the for_each meta-argument that allows defining a single env block that is expanded to N instances, one for each tenant. For example:

variable "url" {
  type    = string
  default = "mysql://root:pass@:3306/"
}

variable "tenants" {
  type = list(string)
}

env "local" {
  for_each = toset(var.tenants)
  url      = urlsetpath(var.url, each.value)
  migration {
    dir = "file://migrations"
  }
}

The above configuration expects a list of tenants to be provided as a variable. This can be useful when the list of tenants is dynamic and can be injected into the Atlas command. The urlsetpath function is a helper function that sets the path of the database URL to the tenant name. For example, if url is set to mysql://root:pass@:3306/?param=value and the tenant name is tenant1, the resulting URL will be mysql://root:pass@:3306/tenant1?param=value.

The second capability is Data Sources. This option enables users to retrieve information stored in an external service or database. For the sake of this example, let's extend the configuration above to use the SQL data source to retrieve the list of tenants from the INFORMATION_SCHEMA in MySQL:

// The URL of the database we operate on.
variable "url" {
  type    = string
  default = "mysql://root:pass@:3306/"
}

// Schemas that match this pattern will be considered tenants.
variable "pattern" {
  type    = string
  default = "tenant_%"
}

data "sql" "tenants" {
  url   = var.url
  query = <<EOS
SELECT `schema_name`
  FROM `information_schema`.`schemata`
  WHERE `schema_name` LIKE ?
EOS
  args = [var.pattern]
}

env "local" {
  for_each = toset(data.sql.tenants.values)
  url      = urlsetpath(var.url, each.value)
}

Example​

Let's demonstrate how managing migrations in a multi-tenant architecture is made simple with Atlas.

1. Install Atlas

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

brew install ariga/tap/atlas

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

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

2. Create a migration directory with the following example content:

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

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

h1:w2ODzVxhTKdBVBdzqntHw7rHV8lKQF98TmNevOEZfIo=
20220811074144_create_users_table.sql h1:KnMSZM/E4TBGidYCZ+UHxkHEWaRWeyuPIUjSHRybQqA=
20220811074314_add_users_name.sql h1:jUpaANgD0SjI5DjaHuJxtHZ6Wq98act0MmE5oZ+NRU0=

3. Create two example tenants on a local database:

create database tenant_a8m;
create database tenant_rotemtam;

4. Run Atlas to execute the migration scripts on the tenants' databases:

atlas migrate apply --env local

Migrating to version 20220811074314 (2 migrations in total):

  -- migrating version 20220811074144
    -> CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
  -- ok (36.803179ms)

  -- migrating version 20220811074314
    -> ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL;
  -- ok (26.184177ms)

  -------------------------
  -- 72.899146ms
  -- 2 migrations
  -- 2 sql statements

Migrating to version 20220811074314 (2 migrations in total):

  -- migrating version 20220811074144
    -> CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
  -- ok (61.987153ms)

  -- migrating version 20220811074314
    -> ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL;
  -- ok (24.656515ms)

  -------------------------
  -- 95.233384ms
  -- 2 migrations
  -- 2 sql statements

Running the command again will not execute any migrations:

No migration files to execute
No migration files to execute

Migration logging​

At Ariga, our services print structured logs (JSON) to feed our observability tools. That is why we felt obligated to add support for custom log formatting in Atlas. To continue the example from above, we present how we configure Atlas to emit JSON lines with the tenant name attached to them.

1. Add the log configuration to the local environment block:

env "local" {
  for_each = toset(data.sql.tenants.values)
  url      = urlsetpath(var.url, each.value)
  // Emit JSON logs to stdout and add the
  // tenant name to each log line.
  format {
    migrate {
      apply = format(
        "{{ json . | json_merge %q }}",
        jsonencode({
          Tenant : each.value
        })
      )
    }
  }
}

2. Create a new script file in the migration directory:

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

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

-- create "pets" table
CREATE TABLE `pets` (`id` bigint, PRIMARY KEY (`id`));

h1:coAZ5kxfT6EpZLjsMRWZv+lYAuChLTnhOnRjAVbZOao=
20220811074144_create_users.sql h1:INH5s7+VEs2pO4+YqL1XNvJUPnF5abhReSEz75L6fz0=
20220811074314_add_users_name.sql h1:EHo+0gM1Cpfl1pe0Cid35qr9mWUhQ9FSXSr+pHUwQwk=
20221027125605_create_pets.sql h1:glWEZaQjERCv1N5evUDz154XvJ84ivlfORrQp/pL7yY=

3. Run migrate apply in our "local" environment:

atlas migrate apply --env local

{"Applied":[{"Applied":["CREATE TABLE `pets` (`id` bigint, PRIMARY KEY (`id`));"],"Description":"create_pets","End":"2022-10-27T16:03:03.685899+03:00","Name":"20221027125605_create_pets.sql","Start":"2022-10-27T16:03:03.655879+03:00","Version":"20221027125605"}],"Current":"20220811074314","Dir":"migrations","Driver":"mysql","End":"2022-10-27T16:03:03.685899+03:00","Pending":[{"Description":"create_pets","Name":"20221027125605_create_pets.sql","Version":"20221027125605"}],"Start":"2022-10-27T16:03:03.647091+03:00","Target":"20221027125605","Tenant":"tenant_a8m","URL":{"ForceQuery":false,"Fragment":"","Host":":3308","OmitHost":false,"Opaque":"","Path":"/tenant_a8m","RawFragment":"","RawPath":"","RawQuery":"parseTime=true","Schema":"tenant_a8m","Scheme":"mysql","User":{}}}
{"Applied":[{"Applied":["CREATE TABLE `pets` (`id` bigint, PRIMARY KEY (`id`));"],"Description":"create_pets","End":"2022-10-27T16:03:03.787476+03:00","Name":"20221027125605_create_pets.sql","Start":"2022-10-27T16:03:03.757463+03:00","Version":"20221027125605"}],"Current":"20220811074314","Dir":"migrations","Driver":"mysql","End":"2022-10-27T16:03:03.787476+03:00","Pending":[{"Description":"create_pets","Name":"20221027125605_create_pets.sql","Version":"20221027125605"}],"Start":"2022-10-27T16:03:03.748399+03:00","Target":"20221027125605","Tenant":"tenant_rotemtam","URL":{"ForceQuery":false,"Fragment":"","Host":":3308","OmitHost":false,"Opaque":"","Path":"/tenant_rotemtam","RawFragment":"","RawPath":"","RawQuery":"parseTime=true","Schema":"tenant_rotemtam","Scheme":"mysql","User":{}}}

Next steps​

Currently, Atlas uses a fail-fast policy, which means the process exits on the first tenant that returns an error. We built it this way because we find it helpful to execute migrations first on "test tenants" and stop in case the operation fails on any of them. However, this means the execution is serial and may be slow in cases where there is a large amount of tenants. Therefore, we aim to add more advanced approaches that will allow executing the first M tenants serially and the rest of the N-M tenants in parallel.

Have questions? Feedback? Feel free to reach out on our Discord server.

With the release of v0.6.0, we introduced a workflow for managing changes to database schemas that we have called: Versioned Migration Authoring.

Today, we released the first version of the Atlas migration execution engine, that can apply migration files on your database. In this post, we will give a brief overview of the features and what to expect in the future.

Migration File Format​

The Atlas migration filename format follows a very simple structure: version_[name].sql, with the name being optional. version can be an arbitrary string. Migration files are lexicographically sorted by filename.

↪ tree .
.
├── 1_initial.sql
├── 2_second.sql
├── 3_third.sql
└── atlas.sum

0 directories, 4 files

-- add new schema named "my_schema"
CREATE DATABASE `my_schema`;
-- create "tbl" table
CREATE TABLE `my_schema`.`tbl` (`col` int NOT NULL);

ALTER TABLE `my_schema`.`tbl` ADD `col_2` TEXT;

CREATE TABLE `tbl_2` (`col` int NOT NULL);

h1:cD9kOv5VDRLrKVZ0pM4CxAlhH6mgE8PQLpUeuIMKDcs=
1_initial.sql h1:SrFyOe0eg5WnE96GH3TtAt6046sfrOK4YKZYBlYr1SA=
2_second.sql h1:FPzwV+MzwyCss7SASZtyafXiYc9Un5bzlcc3u7MxLJU=
3_third.sql h1:uD+xDcA3Q+gHqwca2ZBDAHWYvC2eiUcwr1IgMsN0Q6c=

If you want to follow along, you can simply copy and paste the above files in a folder on your system. Make sure you have a database ready to work on. You can start an ephemeral docker container with the following command:

# Run a local mysql container listening on port 3306.
docker run --rm --name atlas-apply --detach --env MYSQL_ROOT_PASSWORD=pass -p 3306:3306 mysql:8

Apply Migrations​

In order to apply migrations you need to have the Atlas CLI in version v0.7.0 or above. Follow the installation instructions if you don't have Atlas installed yet.

Now, to apply the first migration of our migration directory, we call atlas migrate apply and pass in some configuration parameters.

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

Migrating to version 1 (1 migrations in total):

  -- migrating version 1
    -> CREATE DATABASE `my_schema`;
    -> CREATE TABLE `my_schema`.`tbl` (`col` int NOT NULL);
  -- ok (17.247319ms)

  -------------------------
  -- 18.784204ms
  -- 1 migrations
  -- 2 sql statements

Migration Status​

Atlas saves information about the database schema revisions (applied migration versions) in a special table called atlas_schema_revisions. In the example above we connected to the database without specifying which schema to operate against. For this reason, Atlas created the revision table in a new schema called atlas_schema_revisions. For a schema-bound connection Atlas will put the table into the connected schema. We will see that in a bit.

Go ahead and call atlas migrate status to gather information about the database migration state:

atlas migrate status \
  --dir "file://migrations" \
  --url "mysql://root:pass@localhost:3306/"

Migration Status: PENDING
  -- Current Version:  1
  -- Next Version:     2
  -- Executed Files:   1
  -- Pending Files:    2

This output tells us that the last applied version is 1, the next one is called 2 and that we still have two migrations pending. Let's apply the pending migrations:

Note, that we do not pass an argument to the apply, in which case Atlas will attempt to apply all pending migrations.

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

Migrating to version 3 from 1 (2 migrations in total):

  -- migrating version 2
    -> ALTER TABLE `my_schema`.`tbl` ADD `col_2` TEXT;
  -- ok (13.98847ms)

  -- migrating version 3
    -> CREATE TABLE `tbl_2` (`col` int NOT NULL);
     Error 1046: No database selected

  -------------------------
  -- 15.604338ms
  -- 1 migrations ok (1 with errors)
  -- 1 sql statements ok (1 with errors)

Error: Execution had errors: Error 1046: No database selected

Error: sql/migrate: executing statement "CREATE TABLE `tbl_2` (`col` int NOT NULL);" from version "3": Error 1046: No database selected

What happened here? After further investigation, you will find that our connection URL is bound to the entire database, not to a schema. The third migration file however does not contain a schema qualifier for the CREATE TABLE statement.

By default, Atlas wraps the execution of each migration file into one transaction. This transaction gets rolled back if any error occurs withing execution. Be aware though, that some databases, such as MySQL and MariaDB, don't support transactional DDL. If you want to learn how to configure the way Atlas uses transactions, have a look at the docs.

Migration Retry​

To resolve this edit the migration file and add a qualifier to the statement:

CREATE TABLE `my_schema`.`tbl_2` (`col` int NOT NULL);

Since you changed the contents of a migration file, we have to re-calculate the directory integrity hash-sum by calling:

atlas migrate hash --force \
  --dir "file://migrations"

Then we can proceed and simply attempt to execute the migration file again.

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

Migrating to version 3 from 2 (1 migrations in total):

  -- migrating version 3
    -> CREATE TABLE `my_schema`.`tbl_2` (`col` int NOT NULL);
  -- ok (15.168892ms)

  -------------------------
  -- 16.741173ms
  -- 1 migrations
  -- 1 sql statements

Attempting to migrate again or calling atlas migrate status will tell us that all migrations have been applied onto the database and there is nothing to do at the moment.

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

No migration files to execute

atlas migrate status \
  --dir "file://migrations" \
  --url "mysql://root:pass@localhost:3306/"

Migration Status: OK
  -- Current Version:  3
  -- Next Version:     Already at latest version
  -- Executed Files:   3
  -- Pending Files:    0

Moving an existing project to Atlas with Baseline Migrations​

Another common scenario is when you need to move an existing project to Atlas. To do so, create an initial migration file reflecting the current state of a database schema by using atlas migrate diff. A very simple way to do so would be by heading over to the database from before, deleting the atlas_schema_revisions schema, emptying your migration directory and running the atlas migrate diff command.

rm -rf migrations
docker exec atlas-apply mysql -ppass -e "CREATE SCHEMA `my_schema_dev`;" # create a dev-db
docker exec atlas-apply mysql -ppass -e "DROP SCHEMA `atlas_schema_revisions`;"
atlas migrate diff \
  --dir "file://migrations" \
  --to "mysql://root:pass@localhost:3306/my_schema" \
  --dev-url "mysql://root:pass@localhost:3306/my_schema_dev"

To demonstrate that Atlas can also work on a schema level instead of a realm connection, we are running on a connection bound to the my_schema schema this time.

You should end up with the following migration directory:

-- create "tbl" table
CREATE TABLE `tbl` (`col` int NOT NULL, `col_2` text NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- create "tbl_2" table
CREATE TABLE `tbl_2` (`col` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

h1:5zAQ8J0qziTKWg09fRNdbUf3rnLdvA1KHayh8l1SgM0=
20220908105652.sql h1:4WEB39tqALVYNQJTfULKizxEuUC37sgFs0LN5dKJpOw=

Now, let's create a new migration file to create a table tbl_3 and update the directory integrity file.

atlas migrate new add_table --dir "file://migrations"
echo "CREATE TABLE `tbl_3` (`col` text NULL);" >> migrations/$(ls -t migrations | head -n1)
atlas migrate hash --force --dir "file://migrations"

Since we now have both a migration file representing our current database state and the new migration file to apply, we can make use of the --baseline flag:

atlas migrate apply \
  --dir "file://migrations" \
  --url "mysql://root:pass@localhost:3306/my_schema" \
  --baseline "20220908110527" # replace the version with the one generated by you

Migrating to version 20220908110847 from 20220908110527 (1 migrations in total):

  -- migrating version 20220908110847
    -> CREATE TABLE `tbl_3` (`col` text NULL);
  -- ok (14.325493ms)

  -------------------------
  -- 15.786455ms
  -- 1 migrations
  -- 1 sql statements

Outlook​

The Atlas migration engine is powering Ent and the execution engine is already being used within Ariga for several months. We will continue working on improving it, releasing cool features, such as assisted troubleshooting for failed migrations, a more intelligent, dialect-aware execution planning for things like MySQLs implicits commits and more.

Wrapping up​

In this post we learned about the new migration execution engine of Atlas and some information about its internals.

Further reading​

To learn more about Versioned Migration Authoring:

• Read the docs
• CLI Command Reference

Have questions? Feedback? Find our team on our Discord server.

Losing data is painful for almost all organizations. This is one of the reasons teams are very cautious when it comes to making changes to their databases. In fact, many teams set explicit policies on what kinds of changes to the database are allowed, often completely prohibiting any change that is destructive.

Destructive changes are changes to a database schema that result in loss of data. For instance, consider a statement such as:

ALTER TABLE `users` DROP COLUMN `email_address`;

This statement is considered destructive because whatever data is stored in the email_address column will be deleted from disk, with no way to recover it.

Suppose you were in charge of a team that decided to prohibit destructive changes, how would you go about enforcing such a policy? From our experience, most teams enforce policies relating to schema migrations in code-review: a human engineer, preferably with some expertise in operating databases, manually reviews any proposed database migration scripts and rejects them if they contain destructive changes.

Relying on a human reviewer to enforce such a policy is both expensive (it takes time and mental energy) and error-prone. Just like manual QA is slowly being replaced with automated testing, and manual code style reviews are being replaced with linters, isn't it time that we automate the process of ensuring that changes to database schemas are safe?

Announcing the Atlas GitHub Action​

Today, we're happy to announce the release of the official Atlas GitHub Action which can be used to apply migration directory linting for a bunch of popular database migration tools. golang-migrate, goose, dbmate and Atlas itself are already supported, and Flyway and Liquibase are coming soon.

If you're using GitHub to manage your source code, you're in luck. By adding a short configuration file to your repository, you can start linting your schema migration scripts today! Let's see a short example.

Setting up​

Suppose we are running a website for an e-commerce business. To store the data for our website we use a MySQL database. Because the data in this database is everything to us, we use a careful versioned migrations approach where each change to the database schema is described in an SQL script and stored in our Git repository. To execute these scripts we use a popular tool called golang-migrate.

The source code for this example can be found in rotemtam/atlas-action-demo.

Initially, our schema contains two tables: users and orders, documented in the first few migration files:

Create the users table:

-- create "users" table
CREATE TABLE `users` (
    `id` int NOT NULL,
    `name` varchar(100) NULL,
    PRIMARY KEY (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Add a unique email column:

ALTER TABLE `users` ADD COLUMN `email` varchar(255) NOT NULL, ADD UNIQUE INDEX `email_unique` (`email`);

Create the orders table, with a foreign-key referencing the users table:

-- create "orders" table
CREATE TABLE `orders` (
    `id` int NOT NULL,
    `user_id` int NOT NULL,
    `total` decimal(10) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `user_orders` (`user_id`),
    CONSTRAINT `user_orders` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Installing the Atlas Action​

To make sure we never accidentally delete data during schema changes, we enact a policy that prohibits destructive changes to the database. To enforce this policy, we invoke the atlas-action GitHub Action from within our continuous integration flow by adding a workflow file name .github/workflows/atlas-ci.yaml:

name: Atlas CI
on:
  # Run whenever code is changed in the master branch,
  # change this to your root branch.
  push:
    branches:
      - master
  # Run on PRs where something changed under the `path/to/migration/dir/` directory.
  pull_request:
    paths:
      - 'migrations/*'
jobs:
  lint:
    services:
      # Spin up a mysql:8.0.29 container to be used as the dev-database for analysis.
      mysql:
        image: mysql:8.0.29
        env:
          MYSQL_ROOT_PASSWORD: pass
          MYSQL_DATABASE: test
        ports:
          - "3306:3306"
        options: >-
          --health-cmd "mysqladmin ping -ppass"
          --health-interval 10s
          --health-start-period 10s
          --health-timeout 5s
          --health-retries 10
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3.0.1
        with:
          fetch-depth: 0 # Mandatory unless "latest" is set below.
      - uses: ariga/atlas-action@v0
        with:
          dir: migrations/
          dir-format: golang-migrate # Or: atlas, goose, dbmate
          dev-url: mysql://root:pass@localhost:3306/test

Detecting a destructive change​

Next, let's see what happens when a developer accidentally proposes a destructive change, to drop a column in the orders table:

-- modify "orders" table
ALTER TABLE `orders` DROP COLUMN `total`;

This change is proposed in PR #1 in our example repo. Because we have previously set up the Atlas GitHub Action to lint our migration directory, whenever a file changes under the migrations/ directory, a workflow is triggered.

After letting our workflow complete, observe that GitHub informs us that the Atlas CI / lint check has failed:

Clicking on the "details" link we find a detailed explanation on the causes for the failure:

Examining the Action run summary we find the following annotation:

As you can see, Atlas has detected the destructive change we proposed to apply to our database and failed our build!

Wrapping up​

In this post we discussed why many teams set policies to prevent destructive changes to database schemas. We further showed how such policies can be enforced in an automated way using the official Atlas GitHub Action.

Further reading​

To learn more about CI for database schema changes:

• Read the docs for atlas migrate lint
• Learn about migration analyzers
• CLI Command Reference

Have questions? Feedback? Find our team on our Discord server.

With the release of v0.6.0, we are happy to announce official support for a style of workflow for managing changes to database schemas that we have been experimenting with in the past months: Versioned Migration Authoring.

TL;DR​

• Atlas supports a declarative workflow (similar to Terraform) where users provide the desired database schema in a simple data definition language and Atlas calculates a plan to get a target database to that state. This workflow is supported by the schema apply command.
• Many teams prefer a more imperative approach where each change to the database schema is checked-in to source control and reviewed during code-review. This type of workflow is commonly called versioned migrations (or change based migrations) and is supported by many established tools such as Flyway and Liquibase.
• The downside of the versioned migration approach is, of course, that it puts the burden of planning the migration on developers. As part of the Atlas project we advocate for a third combined approach that we call "Versioned Migration Authoring".
• Versioned Migration Authoring is an attempt to combine the simplicity and expressiveness of the declarative approach with the control and explicitness of versioned migrations.
• To use Versioned Migration Authoring today, use the atlas migrate diff command. See the Getting Started section below for instructions.

Declarative Migrations​

The declarative approach has become increasingly popular with engineers nowadays because it embodies a convenient separation of concerns between application and infrastructure engineers. Application engineers describe what (the desired state) they need to happen, and infrastructure engineers build tools that plan and execute ways to get to that state (how). This division of labor allows for great efficiencies as it abstracts away the complicated inner workings of infrastructure behind a simple, easy to understand API for the application developers and allows for specialization and development of expertise to pay off for the infra people.

With declarative migrations, the desired state of the database schema is given as input to the migration engine, which plans and executes a set of actions to change the database to its desired state.

For example, suppose your application uses a small SQLite database to store its data. In this database, you have a users table with this structure:

schema "main" {}

table "users" {
  schema = schema.main
  column "id" {
    type = int
  }
  column "greeting" {
    type = text
  }
}

Now, suppose that you want to add a default value of "shalom" to the greeting column. Many developers are not aware that it isn't possible to modify a column's default value in an existing table in SQLite. Instead, the common practice is to create a new table, copy the existing rows into the new table and drop the old one after. Using the declarative approach, developers can change the default value for the greeting column:

schema "main" {}

table "users" {
  schema = schema.main
  column "id" {
    type = int
  }
  column "greeting" {
    type = text
    default = "shalom"
  }
}

And have Atlas's engine devise a plan similar to this:

-- Planned Changes:
-- Create "new_users" table
CREATE TABLE `new_users` (`id` int NOT NULL, `greeting` text NOT NULL DEFAULT 'shalom')
-- Copy rows from old table "users" to new temporary table "new_users"
INSERT INTO `new_users` (`id`, `greeting`) SELECT `id`, IFNULL(`greeting`, 'shalom') AS `greeting` FROM `users`
-- Drop "users" table after copying rows
DROP TABLE `users`
-- Rename temporary table "new_users" to "users"
ALTER TABLE `new_users` RENAME TO `users`

Versioned Migrations​

As the database is one of the most critical components in any system, applying changes to its schema is rightfully considered a dangerous operation. For this reason, many teams prefer a more imperative approach where each change to the database schema is checked-in to source control and reviewed during code-review. Each such change is called a "migration", as it migrates the database schema from the previous version to the next. To support this kind of requirement, many popular database schema management tools such as Flyway, Liquibase or golang-migrate support a workflow that is commonly called "versioned migrations".

In addition to the higher level of control which is provided by versioned migrations, applications are often deployed to multiple remote environments at once. These environments are not controlled (or even accessible) by the development team. In such cases, declarative migrations, which rely on a network connection to the target database and on human approval of migrations plans in real-time, are not a feasible strategy.

With versioned migrations (sometimes called "change-based migrations"), instead of describing the desired state ("what the database should look like"), developers describe the changes themselves ("how to reach the state"). Most of the time, this is done by creating a set of SQL files containing the statements needed. Each of the files is assigned a unique version and a description of the changes. Tools like the ones mentioned earlier are then able to interpret the migration files and to apply (some of) them in the correct order to transition to the desired database structure.

The benefit of the versioned migrations approach is that it is explicit: engineers know exactly what queries are going to be run against the database when the time comes to execute them. Because changes are planned ahead of time, migration authors can control precisely how to reach the desired schema. If we consider a migration as a plan to get from state A to state B, oftentimes multiple paths exist, each with a very different impact on the database. To demonstrate, consider an initial state which contains a table with two columns:

CREATE TABLE users (
    id int,
    name varchar(255)
);

Suppose our desired state is:

CREATE TABLE users (
    id int,
    user_name varchar(255)
);

There are at least two ways get from the initial to the desired state:

• Drop the name column and create a new user_name column.
• Alter the name of the name column to user_name.

Depending on the context, either may be the desired outcome for the developer planning the change. With versioned migrations, engineers have the ultimate confidence of what change is going to happen, which may not be known ahead of time in a declarative approach.

Migration Authoring​

The downside of the versioned migration approach is, of course, that it puts the burden of planning the migration on developers. This requires a certain level of expertise that is not always available to every engineer, as we demonstrated in our example of setting a default value in a SQLite database above.

As part of the Atlas project we advocate for a third combined approach that we call "Versioned Migration Authoring". Versioned Migration Authoring is an attempt to combine the simplicity and expressiveness of the declarative approach with the control and explicitness of versioned migrations.

With versioned migration authoring, users still declare their desired state and use the Atlas engine to plan a safe migration from the existing to the new state. However, instead of coupling planning and execution, plans are instead written into normal migration files which can be checked-in to source control, fine-tuned manually and reviewed in regular code review processes.

Getting started​

Start by downloading the Atlas CLI:

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

brew install ariga/tap/atlas

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

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

Next, define a simple Atlas schema with one table and an empty migration directory:

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"

Observe that two files were created in the migrations directory:

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

h1:t1fEP1rSsGf1gYrYCjsGyEyuM0cnhATlq93B7h8uXxY=
20220811074144_create_users.sql h1:liZcCBbAn/HyBTqBAEVar9fJNKPTb2Eq+rEKZeCFC9M=

Further reading​

To learn more about Versioned Migration Authoring:

• Read the docs
• CLI Command Reference

Have questions? Feedback? Find our team on our Discord server.

With the release of v0.5.0, we are happy to announce a very significant milestone for the project. While this version includes some cool features (such as multi-file schemas) and a swath of incremental improvements and bugfixes, there is one feature that we're particularly excited about and want to share with you in this post.

As most outages happen directly as a result of a change to a system, Atlas provides users with the means to verify the safety of planned changes before they happen. The sqlcheck package provides interfaces for analyzing the contents of SQL files to generate insights on the safety of many kinds of changes to database schemas. With this package, developers may define an Analyzer that can be used to diagnose the impact of SQL statements on the target database.

This functionality is exposed to CLI users via the migrate lint subcommand. By utilizing the sqlcheck package, Atlas can now check your migration directory for common problems and issues.

atlas migrate lint in action​

Recall that Atlas uses a dev database to plan and simulate schema changes. Let's start by spinning up a container that will serve as our dev database:

docker run --name atlas-db-dev -d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=pass  mysql

Next let's create schema.hcl, the HCL file which will contain the desired state of our database:

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

To simplify the commands we need to type in this demo, let's create an Atlas project file to define a local environment.

env "local" {
  src = "./schema.hcl"
  url = "mysql://root:pass@localhost:3306"
  dev = "mysql://root:pass@localhost:3307"
}

Next, let's plan the initial migration that creates the users table:

atlas migrate diff --env local

Observe that the migrations/ directory was created with an .sql file and a file named atlas.sum:

├── atlas.hcl
├── migrations
│   ├── 20220714090139.sql
│   └── atlas.sum
└── schema.hcl

This is the contents of our new migration script:

-- add new schema named "example"
CREATE DATABASE `example`;
-- create "users" table
CREATE TABLE `example`.`users` (`id` int NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`)) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Next, let's make a destructive change to the schema. Destructive changes are changes to a database schema that result in loss of data, such as dropping a column or table. Let's remove the name name column from our desired schema:

schema "example" {
}
table "users" {
  schema = schema.example
  column "id" {
    type = int
  }
  // Notice the "name" column is missing.
  primary_key {
    columns = [
      column.id
    ]
  }
}

Now, let's plan a migration to this new schema:

atlas migrate diff --env local

Observe the new migration which Atlas planned for us:

-- modify "users" table
ALTER TABLE `example`.`users` DROP COLUMN `name`;

Finally, let's use atlas migrate lint to analyze this change and verify it's safety:

atlas migrate lint --env local --latest 1

Destructive changes detected in file 20220714090811.sql:

	L2: Dropping non-virtual column "name"

When we run the lint command, we need to instruct Atlas on how to decide what set of migration files to analyze. Currently, two modes are supported.

• --git-base <branchName>: which selects the diff between the provided branch and the current one as the changeset.
• --latest <n> which selects the latest n migration files as the changeset.

As expected, Atlas analyzed this change and detected a destructive change to our database schema. In addition, Atlas users can analyze the migration directory to automatically detect:

• Data-dependent changes
• Migration Directory integrity
• Backward-incompatible changes (coming soon)
• Drift between the desired and the migration directory (coming soon)
• .. and more

Wrapping up​

We started Atlas more than a year ago because we felt that the industry deserves a better way to manage databases. A huge amount of progress has been made as part of the DevOps movement on the fronts of managing compute, networking and configuration. So much, in fact, that it always baffled us to see that the database, the single most critical component of any software system, did not receive this level of treatment.

Until today, the task of verifying the safety of migration scripts was reserved to humans (preferably SQL savvy, and highly experienced). We believe that with this milestone we are beginning to pave a road to a reality where teams can move as quickly and safely with their databases as they can with their code.

Have questions? Feedback? Find our team on our Discord server.

Today, I'm happy to announce the release of v0.4.2 of the Atlas CLI. This version includes many improvements and fixes, but I wanted to share with you exciting news about something I personally worked on. As of v0.4.2, Atlas includes preview support for CockroachDB 🎉

Atlas is an open-source project that helps developers to better manage their database schemas. It has a CLI tool and a Terraform integration. By using Atlas's Data Definition Language (with a syntax similar to Terraform), users can plan, verify and apply changes to their databases in a simple, declarative workflow. Earlier this year, Atlas became the migration engine for Ent, a widely popular, Linux Foundation backed entity framework for Go.

CockroachDB is an open-source NewSQL database. From their README:

CockroachDB is a distributed SQL database built on a transactional and strongly-consistent key-value store. It scales horizontally; survives disk, machine, rack, and even datacenter failures with minimal latency disruption and no manual intervention; supports strongly-consistent ACID transactions; and provides a familiar SQL API for structuring, manipulating, and querying data.

CockroachDB has been gaining popularity and many of you have been asking for Atlas to support it.

While CockroachDB aims to be PostgreSQL compatible, it still has some incompatibilities (e.g. 1, 2,3) which prevented Atlas users using the existing Postgres dialect from working with it.

With the latest release of Atlas, the Postgres driver automatically detects if it is connected to a CockroachDB database and uses a custom driver which provides compatability with CockroachDB.

Getting started with Atlas and CockroachDB​

Let's see how we can use Atlas CLI to manage the schema of a CockroachDB database. Start by downloading the latest version of Atlas, on macOS:

brew install ariga/tap/atlas

For installation instructions on other platforms, see the docs.

For the purpose of this example, let's spin up a local, single-node CockroachDB cluster in a container by running:

docker run --rm -d -p 26257:26257 --name crdb cockroachdb/cockroach start-single-node --insecure

Next, let's seed the database with a simple table:

docker exec crdb cockroach sql --insecure -e 'CREATE TABLE users (id int primary key);'

After creating the users table, use Atlas's schema inspect command to read the schema of our local database and save the result to a file:

atlas schema inspect -u 'postgres://root:pass@localhost:26257/?sslmode=disable' --schema public > schema.hcl

Observe the current HCL representation of the public schema, which contains our newly created table, users:

table "users" {
  schema = schema.public
  column "id" {
    null = false
    type = bigint
  }
  primary_key {
    columns = [column.id]
  }
}
schema "public" {
}

Next, edit schema.hcl to add a column to the users table:

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

Now apply the schema using the schema apply command:

atlas schema apply -u 'postgres://root:pass@localhost:26257/?sslmode=disable' --schema public -f schema.hcl

Atlas prints out the planned changes and asks for your confirmation:

-- Planned Changes:
-- Create "test" table
ALTER TABLE "public"."users" ADD COLUMN "name" character varying(100) NOT NULL
? Are you sure?:
  ▸ Apply
    Abort

After hitting "Apply", Atlas applies the desired schema to the database:

✔ Apply

We have successfully applied our schema to our database.

To stop the container running CockroachDB run:

docker stop crdb

Learn more about Atlas​

In this short example, we demonstrated two of Atlas's basic features: database inspection and declarative schema migration (applying a desired schema on a database). Here are some topics you may want to explore when getting started with Atlas:

• Learn the DDL - learn how to define any SQL resource in Atlas's data definition language.
• Try the Terraform Provider - see how you can use the Atlas Terraform Provider to integrate schema management in your general Infrastructure-as-Code workflows.
• Use the migrate command to author migrations - In addition to the Terraform-like declarative workflow, Atlas can manage a migration script directory for you based on your desired schema.

Preview support​

The integration of Atlas with CockroachDB is well tested with version v21.2.11 (at the time of writing, latest) and will be extended in the future. If you're using other versions of CockroachDB or looking for help, don't hesitate to file an issue or join our Discord channel.

Have questions? Feedback? Find our team on our Discord server.