Database migrations made easy with Atlas

Database migrations is one of the thorniest subjects there is. In short, they happen when a database schema must change to accommodate new or updated data. One simple example of a database migration is adding or removing columns from a table.

There are many tools available to help the process of database migration, following a variety of approaches. In this article we will look at Atlas, a declarative tool for managing database schemas, that draws inspiration from infrastructure-as-code tools to manage migrations in a novel way.

#The state of the art in declarative management

Hashicorp's Terraform is a tool that devops engineers use to manage software and hardware resources used to deploy applications. It works in a declarative way, by having the engineer specify the desired state of the system. Terraform is then able to inspect the current state of the system, derive the delta between the current state and the desired state, and then work to bring the system to the desired state. The main advantage is that the engineer doesn't have to be concerned with the how (imperative)to bring the system to the desired state, but merely declaring (declarative) what the desired state is.

Let's look at a simplified version of the getting started example on Hashicorp's website:

provider "aws" {
  region  = "us-west-2"
}

resource "aws_instance" "app_server" {
  ami           = "ami-830c94e3"
  instance_type = "t2.micro"

  tags = {
    Name = "ExampleAppServerInstance"
  }
}

After executing terraform apply, we will have a VM on AWS us-west-2. It will be of the t2.micro type, using the AMI ami-830c94e3. The VM will have the tag ExampleAppServerInstance.

If the VM doesn't exist, it will be created. If it already exists, but the properties don't match what we want (for example, it has a different tag), it will be fixed. We only need to specify our desired state, and Terraform will take us there.

#Terraform meets databases

What is, fundamentally, the difference between a database schema and cloud infrastructure? We have some tables, with some properties and relationships between them. We would like to just specify what we want the new schema to be, and let tooling take us there.

Atlas is a tool that does just that, and brings the Terraform experience to database management.

As an example, the following SQLite table:

CREATE TABLE users(
    id integer primary key,
    email text not null,
    full_name text not null,
    username text not null
);

Can be expressed using Hashicorp's Configuration Language (HCL):

table "users" {
  schema = schema.main
  column "id" {
    null = true
    type = integer
  }
  column "email" {
    null = false
    type = text
  }
  column "full_name" {
    null = false
    type = text
  }
  column "username" {
    null = false
    type = text
  }
  primary_key {
    columns = [column.id]
  }
}
schema "main" {
}

We can then apply this to an existing database, and let Atlas take us to the desired state.

#Putting this all together

We will now look into how we can use Atlas to declaratively manage and evolve the schema of SQLite databases.

The first step is to install Atlas. See the instructions on how to install Atlas, for more details, but most people should be able to just:

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

As a simple example, let's save the contents above into a file called atlas.hcl, and apply this to an empty SQLite database file:

$ atlas schema apply — to file://atlas.hcl -u sqlite://example.db
-- Planned Changes:
-- Create "users" table
CREATE TABLE `users` (`id` integer NULL, `email` text NOT NULL, `full_name` text NOT NULL, `username` text NOT NULL, PRIMARY KEY (`id`));
Use the arrow keys to navigate: ↓ ↑ → ←
? Are you sure?:
  ▸ Apply
    Abort

Because the database was initially empty, the table was simply created. Note that much like terraform plan, Atlas will prompt you before applying the changes.

If we run the same command again, we see that the database already has the desired state:

$ atlas schema apply --to file://atlas.hcl -u sqlite://example.db
Schema is synced, no changes to be made

As a next step, say we want to make the example a bit more complex. We will create some indexes on the users table. We add the following lines to the atlas.hcl file, inside the table scope:

  index "idx_users_email" {
    unique  = true
    columns = [column.email]
  }
  index "idx_users_username" {
    unique  = true
    columns = [column.username]
  }

And apply the schema changes:

$ atlas schema apply --to file://atlas.hcl -u sqlite://example.db
-- Planned Changes:
-- Create index "idx_users_email" to table: "users"
CREATE UNIQUE INDEX `idx_users_email` ON `users` (`email`);
-- Create index "idx_users_username" to table: "users"
CREATE UNIQUE INDEX `idx_users_username` ON `users` (`username`);
Use the arrow keys to navigate: ↓ ↑ → ←
? Are you sure?:
  ▸ Apply
    Abort

Note the tool will find the minimum amount of changes needed to bring the database in sync with the hcl file. In this case, all it needs to do is create the indexes.

#Migrations on the Edge

Especially for those who are familiar with Terraform, Atlas is the recommended way to do migrations with Turso, the edge database based on SQLite. Let's look at an example:

First create the database with the Turso CLI:

$ turso db create atlas
Created database atlas in Seattle, Washington (US) (sea) in 8 seconds.

We can use the turso db show command to get the url of the database. We will then have to change it to conform to Atlas' URL schemes, and save it to an environment variable. For example:

$ turso db show atlas --url
libsql://atlas-glommer.turso.io
$ TURSO_DB_URL=$(libsql+wss://atlas-glommer.turso.io)

The next step is to create an access token:

$ TURSO_DB_TOKEN=$(turso db tokens create atlas)

We can now use atlas to bring this empty database to the same state as our SQLite file:

$ atlas schema apply -u "${TURSO_DB_URL}?authToken=${TURSO_DB_TOKEN}" \
 --to sqlite://example.db --exclude '_litestream_seq,_litestream_lock'
-- Planned Changes:
-- Create "users" table
CREATE TABLE `users` (`id` integer NULL, `email` text NOT NULL, `full_name` text NOT NULL, `username` text NOT NULL, PRIMARY KEY (`id`));
-- Create index "idx_users_email" to table: "users"
CREATE UNIQUE INDEX `idx_users_email` ON `users` (`email`);
-- Create index "idx_users_username" to table: "users"
CREATE UNIQUE INDEX `idx_users_username` ON `users` (`username`);
Use the arrow keys to navigate: ↓ ↑ → ←
? Are you sure?:
  ▸ Apply
    Abort

Note that the SQLite database file from the prior section was used directly here as the — to param. We could have used the atlas.hcl file, but it is great that Atlas also supports database endpoints, which it will use to determine the desired final state of the migration. We can use this to sync the schemas of a SQLite database with Turso (or vice versa), two different Turso databases, etc.

Because the Turso database is empty at this point, this command will create the table and its indexes, as shown in the command output.

The second thing to notice is that Turso has some internal tables that are created with every database, and we want to mark them as excluded from the migration. We are working to hide those tables so that this step is not needed in the future.

#Summary

Atlas is a tool to manage database migrations declaratively, which brings the ease of use of Terraform to the database migration world. It works with most popular databases, in particular SQLite and Turso.

Get started with Atlas today, and see for yourself what the tool can do for you.