As applications evolve, database schema changes become inevitable. However, some types of changes can break the contract between the backend server and the database, leading to errors during deployment and application downtime. Ensuring that these breaking changes are detected and prevented is crucial for maintaining system stability and delivering a seamless user experience. In this post, we will explore how Atlas can help you prevent breaking schema changes from impacting your application.

What are breaking changes?

Backward-incompatible changes, also known as breaking changes, are schema changes that have the potential to break the contract with applications that rely on the old schema. Applications today are expected to be highly-available, which means that we cannot shut them down for maintenance, and must continuously serve traffic, 24/7.

The common strategy for dealing with this requirement is called a "rolling deployment" where we deploy new versions of the application in stages, gradually increasing the percentage of traffic served by the new version. This means that during the deployment phase of a new version we will always have old and new code running side by side.

This means, for example, that renaming a column from email_address to email will cause older versions of the application to fail when they try to access the column using the old name. This is a breaking change, and it can cause downtime if not detected and prevented.

Manually enforcing backward-compatability

Preventing breaking changes in production is crucial to maintaining a highly-available system. To enforce backward-compatibility most teams rely on two things:

1. GitOps: Making sure every change to the database is checked-in to source control system, and using automated schema migration tools to apply the change.
2. Manual code-review: Carefully reviewing every schema change to make sure it will not break the contract between the backend and the database.

Checking-in database artifacts into source control is a well established technique - in fact it's one of the first principles mentioned in the seminal Evolutionary Database Design (Fowler and Sadalage, 2016).

However, let's consider the impact of relying on human review for changes using the widely accepted DORA Metrics for assessing DevOps performance:

1. Lead time increases - Lead time measures the time it takes to get a commit to production. If a change needs the careful attention and approval of an expert reviewer that reviewer becomes a bottleneck.

2. Change failure rate increases - measures the percentage of deployments causing a failure in production. Relying on humans to routinely evaluate and verify every change against dozens of rules and policies is naturally error-prone, especially if they are busy and under constant pressure to deliver (which expert reviewers almost always are).

3. Deployment frequency decreases - How often an organization successfully releases to production. When engineers learn that a certain type of change gets queued up in slow review cycles there is a tendency to batch changes together, decreasing the frequency of deployments and increasing their riskiness.

This pattern is not unique to database changes, in fact it looks exactly the same for any high risk change. Since the emergence of the DevOps movement manual verification of changes is gradually being replaced by automation in many fields, can the same be done for database changes?

Automated detection of schema changes with Atlas

Atlas provides support for a process called migration linting, which many teams use to automatically verify the safety of schema changes against a predefined set of policies. Unlike most available SQL linters, Atlas's linter is focused on the semantic level, rather than the syntactic level, which involves formatting and coding style standards. The linter analyzes the meaning of the changes and their potential impact, rather than how the changes are written.

Atlas's migrate lint command exposes this mechanism, making the automatic detection of breaking schema changes simple and straightforward.

Suppose we wanted to run the following migration:

ALTER TABLE `users` RENAME TO `Users`;

Renaming a table is a backward-incompatible change that can cause errors during deployment (migration) if applications running the previous version of the schema refer to the old name in their statements. To check if our latest migration file contains any dangerous changes we run:

atlas migrate lint --dev-url docker://mysql/8/dev --latest 1

Atlas prints:

20230330203525.sql: backward incompatible changes detected:

    L1: Renaming table "users" to "Users"

Amazing! Atlas detected our breaking change automatically. In addition to breaking changes Atlas supports many other safety checks that you can apply to your schema changes.

Linting can be used during development from the developer's workstation to detect issues locally, but it really shines when you connect it to your project's continuous integration pipeline, allowing you to prevent such changes from ever reaching production. Setting up CI for schema changes takes less than a minute using Atlas Cloud or a little longer using our GitHub Action.

Wrapping up

Detecting and preventing breaking changes in your database schema is essential for maintaining a high-quality user experience and ensuring system stability. Atlas provides you with a practical and efficient solution to analyze and detect potential breaking changes before they impact your application.

What's next?​

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

It's been two months since the release of v0.9.0, so we figured it's about time to release a new version and share with you what we've accomplished so far, as well as what's to come in the upcoming weeks. Besides the many improvements and bug fixes in v0.10.0, we added two major features to Atlas that I want to share with you: schema loaders and the Community Preview of Atlas Cloud.

Schema Loaders​

In our previous post, we discussed our motivation for developing an infrastructure to load desired states from external sources (not just SQL and HCL), and we highlighted the importance of schema loaders. Today, I'm happy to share that we've made significant progress on this front. We started by creating a schema loader for the Ent framework, and with the release of v0.10.0, Ent users can now use their ent/schema package as the desired state in all the different Atlas commands.

Using the new integration, users can compare an ent/schema package with any other state, apply it onto a database, generate migrations from it, and much more. Here are two examples:

atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "ent://path/to/schema" \
  --dev-url "sqlite://dev?mode=memory&_fk=1"

atlas schema diff \
  --from "file://migrations" \
  --to "ent://path/to/schema" \
  --dev-url "sqlite://dev?mode=memory&_fk=1"

I'm really eager to see this initiative come to fruition because it has proven to work well for the Ent community. We are now ready to expand support for additional frameworks and languages. In the upcoming weeks, you can expect to see additional integrations, such as GORM, Sequelize, and more. With these new superpowers, users will be able to manage all of their database schemas using a single tool - Atlas!

Atlas Cloud Community Preview​

We are also super thrilled to announce the Community Preview of Atlas Cloud! Atlas Cloud is a cloud-based service that provides teams with an end-to-end solution for managing database schema changes. As part of the Community Preview, we are offering a free "Community" plan for all users which you can use to manage up to 5 migration directories for your team or personal projects.

One important feature that was recently added to Atlas is the ability to connect remote migration directories stored in GitHub to Atlas Cloud. This new functionality empowers users to easily audit and view their migration history and get migration linting checks on their PRs, such as destructive or backwards incompatible changes detection.

Let's walk through a simple guide on how to set it up to a project with just a few clicks:

1. Login to atlasgo.cloud and create a new workspace (organization) for your projects:

2. Once created, go to /dirs/configure and connect your migration directory stored in GitHub to Atlas Cloud:

3. After connecting your directory, you'll see an extensive overview of your migration history and the schema it presents:

4. From this point on, every change made to the migration directory will be reflected in Atlas Cloud. But what about the changes themselves? Here's where the magic happens. Once a directory is connected, any pull request that modifies it will be automatically checked and reviewed by Atlas!

Let's create a sample migration change, open a pull request, and see it in action:

Wonderful! However, that's not all. There is another detailed and visualized report available in Atlas Cloud that has been specifically created for this CI run. Go to the migration directory page, click on the CI Runs button to check it out.

A big thanks to @giautm, @masseelch and @yonidavidson for building this feature for Atlas!

What next?​

Well, this is just the beginning of Atlas Cloud! In the upcoming weeks, we will be rolling out several new major features that we have been working on lately, including schema drift detection, managed migration deployments, and much more. If any of these features sound interesting to you, please do not hesitate to contact us.

We would love to hear from you on our Discord server ❤️.

For a long time, one of the most common feature requests we've been getting from our users is the ability to manage their desired "schema state" using SQL. This is understandable, using Atlas DDL (HCL) can feel unfamiliar to some users, especially those who have never worked with Terraform before. For this reason, we're excited to announce the release of Atlas v0.9.0, which now fully supports SQL.

Schema as Code (SaC)​

Atlas applies the common IaC concept of declarative resource management to database schemas. With Atlas, users do not need to plan schema changes themselves. Instead of figuring out the correct SQL statements to update their database schemas, users provide to Atlas the schema definition that describe their desired state and Atlas generates a migration plan to move from the current state to the desired state defined by the schema.

Starting from v0.9.0, users can use SQL schema files (or a directory) containing CREATE and ALTER statements to describe their desired state. To demonstrate this, let's use this schema example with a single users table:

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

Given this schema file, Atlas offers two workflows to update databases:

• Declarative: Similar to Terraform, Atlas compares the current state of the database schema with the desired state defined by the SQL schema, and generates a migration plan to reach that state.
• Versioned: Atlas compares the current state defined by the migrations directory to the desired state defined by the SQL schema, and writes a new migration script to the directory to update the database schema to the desired state.

In this blog post, we'll focus on explaining how SQL schemas can be used with the declarative workflow. For the sake of simplicity, let's assume we have an empty database that we want to apply the schema above to:

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

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

atlas schema apply \
  --url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/15"

atlas schema apply \
  --url "sqlite://file.db" \
  --to "file://schema.sql" \
  --dev-url "sqlite://file?mode=memory"

Running the command above with the --auto-approve flag will apply the following changes:

-- Planned Changes:
-- Create "users" table
CREATE TABLE `users` (`id` int NOT NULL, `name` varchar NULL, PRIMARY KEY (`id`));

Hooray! We have successfully created the users table defined in our schema file. Let's inspect our database and ensure its schema was actually updated by the command above:

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

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

atlas schema inspect \
  --url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
  --format "{{ sql . }}"

atlas schema inspect \
  --url "sqlite://file.db" \
  --format "{{ sql . }}"

Excellent! As you can see, our database schema has been updated:

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

Now let's make our schema more interesting by adding a column to the users table and creating a blog_posts table with a foreign key that references users:

-- create table "users
CREATE TABLE users(
    id int NOT NULL,
    name varchar(100) NULL,
    email varchar(50) NULL,
    PRIMARY KEY(id)
);

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

Next, executing atlas schema apply again will update the database schema with the following changes:

-- Planned Changes:
-- Add column "email" to table: "users"
ALTER TABLE `users` ADD COLUMN `email` varchar NULL;
-- Create "blog_posts" table
CREATE TABLE `blog_posts` (`title` varchar NULL, `body` text NULL, `author_id` int NULL, `id` int NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `author_fk` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION);

Boom! Atlas automatically calculates the difference between the current state of our database and the desired state defined by our schema file, and generates the necessary changes to migrate the database to the new state. We don't need to specify each individual migration – we simply tell Atlas what state we want the database to be in, and it handles the rest.

To see a full description of this generated migration plan, check out this diagram example in Atlas public playground:

Diff ERD​

Diff SQL​

Atlas Playground​

As part of this version, we have released the Atlas playground where users can visualize their database schemas in an interactive way. Simply provide an SQL or HCL schema, or import one from an existing database, and in return get an ERD visualizing their entire data model.

Users can also compare between two schemas with the Schema Diff button, and get the SQL statements necessary to migrate from one schema to the other - give it try!

A big thanks to @solomonme, @ronenlu and @masseelch for contributing this feature to Atlas!

Schema Loaders​

What's next? In the near future, we plan to add an infrastructure for loading schemas from external sources. This will enable ORM maintainers to integrate with Atlas and provide their schema definitions as Atlas schemas. As as result, they can utilize the Atlas engine to diff schemas, plan and lint migrations, execute them on the databases, and more.

The first ORM to integrate with Atlas will be Ent. Using this integration, Ent users will be able to generate Atlas schemas or migrations for their Ent projects with a single command:

atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "ent://path/to/schema" \
  --dev-url "docker://<driver-name>"

Would you like to see other ORMs integrated with Atlas? Please, join our Discord server and let me know.

What next?​

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

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 schema 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.