Deploying Schema Migrations to Database-per-Tenant Architecture

In the previous section, we learned how to define target groups in Atlas to manage migrations for a database-per-tenant architecture. In this section, we will see how to deploy migrations to the target groups.

Setting up

For the purpose of this guide, we will use a simple example to demonstrate how to deploy migrations to target groups. To simplify things, we will be using SQLite files as our target databases and statically defining the target groups in the atlas.hcl file.

Our config file

In our project directory, let's create a file named atlas.hcl with the following content:

locals {
  tenant = ["tenant_1", "tenant_2"]
}

env "prod" {
  for_each = toset(local.tenant)
  url = "sqlite://${each.value}.db"
  migration {
    dir = "file://migrations"
  }
}

An initial migration

Let's create an initial migration file to bootstrap our project:

atlas migrate new --edit init

Once the local editor opens, add the following SQL statements:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

Save the file and exit the editor. Observe that two new files were created in the migrations/ directory:

├── 20240721101205_init.sql
└── atlas.sum

1 directory, 2 files

Deploying the migrations

We can deploy the migrations directly to the target group using the migrate apply command with the --env flag:

atlas migrate apply --env prod

This command will apply the migrations to both tenant_1 and tenant_2 databases. Atlas will output:

Migrating to version 20240721101205 (1 migrations in total):

  -- migrating version 20240721101205
    -> CREATE TABLE users (
         id INTEGER PRIMARY KEY,
         name TEXT NOT NULL
       );
  -- ok (345.458µs)

  -------------------------
  -- 3.400333ms
  -- 1 migration
  -- 1 sql statement
Migrating to version 20240721101205 (1 migrations in total):

  -- migrating version 20240721101205
    -> CREATE TABLE users (
         id INTEGER PRIMARY KEY,
         name TEXT NOT NULL
       );
  -- ok (266.375µs)

  -------------------------
  -- 905.875µs
  -- 1 migration
  -- 1 sql statement

As you can see from the output, the migration was applied to both databases. Observe that two new files were created in our project directory: tenant_1.db and tenant_2.db.

Verifying our migrations were applied

We can check the current schema of our local SQLite databases using the migrate status command. Run:

atlas migrate status --url sqlite://tenant_1.db

Atlas prints:

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

As expected, the tenant_1 database is up-to-date with the latest migration.

Checking for Drift

Additionally, we may want to verify that the schema of the tenant database is in sync with the latest migration. We can utilize the schema diff command to compare the current schema with the latest migration:

atlas schema diff \
  --dev-url 'sqlite://?mode=memory' \
  --from file://migrations \
  --to sqlite://tenant_1.db \
  --exclude 'atlas_schema_revisions'

Atlas will output:

Schemas are synced, no changes to be made.

Next steps

As you can see, deploying migrations to target groups is straightforward using the Atlas CLI, but getting visibility into the status of each tenant, is done individually. To bridge this gap, we will show how to use the Atlas Cloud control plane to gain visibility into the status of our system in the next section.