Database schema migrations have long been one of the most anxiety-inducing parts of application development. Many teams have experienced that moment of dread when applying schema changes to production, wondering whether they'll bring down the entire system or corrupt critical data. For teams managing databases of any size, executing complex migrations is a high-stakes task that demands precision and confidence.

The Problem

A typical scenario unfolds when a backend team needs to implement a new feature requiring database changes, like adding user authentication to a growing application. Suppose that we are running MySQL on Amazon RDS. The initial approach might start with a straightforward SQL query:

CREATE TABLE users (
  id char(36) PRIMARY KEY,
  name char(32) NOT NULL,
  is_admin BOOLEAN NOT NULL DEFAULT (FALSE),
);

Here we create a table to store usernames. The table is accessed via REST API and specific handler functions for CRUD operations; everything runs just fine.

Soon, the need arises to keep track of when a user was created and when it last logged in. Since we want to be able to reproduce all stages of our migration, we need to add (and orchestrate) three more SQL queries:

ALTER TABLE users ADD COLUMN created_at TIMESTAMP NOT NULL
);
ALTER TABLE users ADD COLUMN last_login TIMESTAMP NOT NULL
);

This adds the required columns to the table and needs to be easily rolled back. We need some more queries:

ALTER TABLE users
DROP COLUMN last_login;

ALTER TABLE users
DROP COLUMN created_at;

Finally, to be able to return to a clean slate:

DROP TABLE users;

These are very straightforward changes, but our whole pipeline requires careful handling, at any scale. Rollback queries must be executed in reverse order, with proper sequencing at the orchestration level; especially when automated by scripts or migration tools.

...but what if database schema evolution could be as mundane as merging a pull request? What if your team could treat database schemas as code, with the same rigorous review processes and version control you apply to your application logic? With the challenges of manual schema migrations in mind, Terrateam and Atlas offer a more efficient, automated solution.

What is Atlas?

Atlas is an open-source tool that takes a different approach to database schema migrations. Instead of the traditional migration scripts that specify how to change a database schema, Atlas lets users define what the schema should look like (declarative approach). You can use HCL, SQL or any ORM or language. Besides its own CLI tool (which, by the way, supports automatic schema inspection!), Atlas features a Terraform provider, which makes it ideal to integrate into a Terrateam workflow.

How to Use Terrateam and Atlas for Schema Migrations

Step 1: Install Terrateam

Sign up for Terrateam at terrateam.io.
Install the Terrateam GitHub App for your organization, selecting the repository with your Terraform code.
Commit .github/workflows/terrateam.yml to your default branch to enable Terraform jobs via GitHub Actions.
Grant Terrateam permission to access your cloud provider for secure state storage (configured later). See Cloud Provider Setup for details.
Create a .terrateam/config.yml file to define PR handling.

undefined

when_modified:
 autoapply: true

cost_estimation:
 enabled: true

hooks:
 all:
   pre:
	 - type: oidc
	   provider: aws
	   role_arn: "arn:aws:iam::YOUR_AWS_ACCOUNT_ID:role/terrateam"

terraform:
 backend:
   s3:
	 bucket: "terrateam-atlas"
	 key: "terraform.tfstate"
	 region: "eu-west-1"
	 encrypt: true

workflows:
 - tag_query: ""
   plan:
	 - type: init
	 - type: plan
   apply:
	 - type: init
	 - type: apply

This configuration uses OIDC for secure AWS access and your account should be already configured accordingly. It enables automatic terraform apply on merge as well as cost estimation, since RDS is a paid service. Nevertheless, if you are eligible for an AWS Free Tier account, it will suffice for our scope with its included compute hours.

Step 2: Create a PostgreSQL database with Amazon RDS and the initial table

First of all, we need to define two new repository secrets TF_VAR_DB_USERNAME and TF_VAR_DB_PASSWORD. From your repo, click on Settings -> Secrets and Variables -> Actions -> New Repository Secret.

undefined

We also need to create a new feature/db and start defining our manifest.

As we already have introduced, our "legacy" table creation query would look like this

CREATE TABLE users (
  id char(36) PRIMARY KEY,
  name char(32) NOT NULL,
  is_admin BOOLEAN NOT NULL DEFAULT (FALSE),
);

and would require an additional step in your pipeline for its orchestration.

With Atlas, we have a whole array of supported data types, compatible with and specific for most DBMS; we define a schema in a declarative way by creating a schema.hcl:

schema "terrateam" {}

table "users" {
    schema = schema.terrateam
    column "id" {
        type = UUID
        null = false
    }
    column "name" {
        type = text
        null = false
    }
    column "isadmin" {
        type = boolean
        null = false
        default = false
    }
    primary_key {
        columns = [column.id]
    }
}

The schema file is, in turn, going to be sourced from main.tf:

/* Infrastructure provisioning has been largely redacted for practical purposes. Check
out the above linked, complete main.tf file for a comprehensive reference. */

# Atlas provider configuration
provider "atlas" {
}

# Load schema from HCL file
data "atlas_schema" "terrateam" {
  dev_url = "mysql://${var.db_username}:${var.db_password}@${aws_db_instance.terrateam.address}:3306/"
  src = file("${path.module}/schema.hcl")
  depends_on = [aws_db_instance.terrateam]
}

# Sync target state with HCL file
resource "atlas_schema" "terrateam" {
  hcl = data.atlas_schema.terrateam.hcl
  url = "mysql://${var.db_username}:${var.db_password}@${aws_db_instance.terrateam.address}:3306/"
  depends_on = [aws_db_instance.terrateam]
}

While provisioning the database, Terraform will automatically inherit our GitHub secrets as var.db_username and var.db_password, then provision them as administrative credentials. To simplify our test case, it will also publish the database to the internet, which makes this manifest unsuitable for production!

Save your files, commit, push and open a new pull request to GitHub, then observe terrateam automatically run terraform plan:

undefined

In absence of errors and after validating the plan against your desired state, you can merge the branch into main, thereby triggering terraform apply:

undefined

Let's manually check our database! From the AWS RDS Console, click on the terrateam database and copy the endpoint URL; then from any database editor, such as pgAdmin, DBeaver or from the command line, verify that your table has been created:

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| terrateam          |
+--------------------+
5 rows in set (0,498 sec)

MySQL [(none)]> use terrateam;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [terrateam]> show tables;
+---------------------+
| Tables_in_terrateam |
+---------------------+
| users               |
+---------------------+
1 row in set (0,087 sec)

MySQL [terrateam]> show columns from users;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | char(36)    | NO   | PRI | NULL    |       |
| name     | varchar(32) | NO   |     | NULL    |       |
| is_admin | tinyint(1)  | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0,083 sec)

MySQL [terrateam]>

Step 3: Add additional columns to the schema

At this point, our initial configuration is complete and, unless some AWS service is required in the future, we are allowed to forget about it.

As soon as our team is ready for our next migration step:

ALTER TABLE users ADD COLUMN created_at TIMESTAMP NOT NULL
);
ALTER TABLE users ADD COLUMN last_login TIMESTAMP NOT NULL
);

we simply open a new pull request with the updated schema.hcl, where we just add the two columns:

schema "terrateam" {}

table "users" {
    schema = schema.terrateam
    column "id" {
        type = UUID
        null = false
    }
    column "name" {
        type = text
        null = false
    }
    column "created_at" {
        type = timestamp
        null = false
    }
    column "last_login" {
        type = timestamp
        null = false
    }
    column "isadmin" {
        type = boolean
        null = false
        default = false
    }
    primary_key {
        columns = [column.id]
    }
}

This makes our configuration efforts a very good investment, especially as soon we need to rollback our schema to some previous state - as easy as modifying schema.hcl and opening a new PR.

After our last update, our database shows the new columns in the users table:

MySQL [terrateam]> show columns from users;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | char(36)    | NO   | PRI | NULL    |       |
| name       | varchar(32) | NO   |     | NULL    |       |
| is_admin   | tinyint(1)  | NO   |     | NULL    |       |
| created_at | timestamp   | NO   |     | NULL    |       |
| last_login | timestamp   | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

Managing database schema migrations doesn’t have to be risky, manual or divorced from the rest of your infrastructure lifecycle. With GitOps, Atlas and Terrateam you gain a declarative and version-controlled pipeline that fits into your workflow and drastically reduces negative stress.

Features

Learn More

Learn

Connect