Deploying Migrations to a 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
migrate apply trusts the revisions table and assumes each tenant database was not changed outside
of Atlas since its last applied migration. atlas migrate drift verifies that assumption between
deployments. It reads the revisions table on each target, resolves the state the migration directory
defines at the last applied version, and diffs the two. Migration files after that version are
pending, not drift, and the report counts them as ignored. The command never changes the database.
Drift detection is available to Atlas Pro users. You can create a trial account
using the atlas login command.
The command takes the same --env flag as migrate apply and checks every tenant in the target
group. Our migration directory is still a local directory, so the env needs a
dev database for Atlas to compute the expected state from the directory up
to the applied version. Add dev to the env:
locals {
tenant = ["tenant_1", "tenant_2"]
}
env "prod" {
for_each = toset(local.tenant)
url = "sqlite://${each.value}.db"
dev = "sqlite://?mode=memory"
migration {
dir = "file://migrations"
}
}
Then run:
atlas migrate drift --env prod
Atlas prints one report per tenant:
Drift Status: OK
-- Current Version: 20240721101205
-- Expected State: file://migrations (local)
-- Pending Files: 0
Drift Status: OK
-- Current Version: 20240721101205
-- Expected State: file://migrations (local)
-- Pending Files: 0
The revisions table and its containing schema are excluded automatically.
Objects that intentionally live outside the migration scope, such as tables another service maintains
inside a tenant database, are excluded with --exclude or the exclude attribute of the env.
When a tenant drifts
To see how Atlas reports drift, create a table directly on tenant_1, outside the migration directory:
CREATE TABLE audit (id INTEGER);
Running atlas migrate drift --env prod again reports the drift on tenant_1 alone. tenant_2 is
still checked and comes back clean, and the command exits with status 1:
Drift Status: DRIFTED
-- Current Version: 20240721101205
-- Expected State: file://migrations (local)
-- Pending Files: 0
-- Changes: 1 (1 extra)
-- Objects: 1 table
-- Fingerprint: 9c41f0be7d15
--- expected state (version 20240721101205)
+++ actual state (sqlite://tenant_1.db)
@@ -3,3 +3,6 @@
`name` text NOT NULL,
PRIMARY KEY (`id`)
);
+CREATE TABLE `audit` (
+ `id` integer NULL
+);
The database diverged from the expected state as if the following were executed:
-- extra table "audit":
-> CREATE TABLE `audit` (`id` integer NULL);
Drift Status: OK
-- Current Version: 20240721101205
-- Expected State: file://migrations (local)
-- Pending Files: 0
A drifted report shows a diff of the expected and actual state, then the DDL that reproduces each
change. Changes are named from the database's side: extra objects exist only in the tenant
database, missing objects exist only in the expected state, and modified objects exist in both
but differ. The text report lists up to ten changes per tenant and counts the rest. The
drift detection docs show a report with all three.
Each tenant is compared with the expected state at its own applied version, and the run exits 0 only
when every tenant matches. A cron job or CI step can fail on drift without parsing the output.
Tenants on different versions can all pass; the Current Version line shows where each one stands.
A tenant that can't be checked, for example because its database is unreachable, stops the run.
The Fingerprint of a drifted tenant is a hash of its actual state, independent of object order. It
changes only when the drift changes, so it can deduplicate alerts: the same fingerprint as last
night is the same open issue.
To collect the reports as JSON, set a format for the command in the env. {{ json . }} prints each
tenant's full report, and json_merge adds the tenant's name to it:
env "prod" {
for_each = toset(local.tenant)
url = "sqlite://${each.value}.db"
dev = "sqlite://?mode=memory"
migration {
dir = "file://migrations"
}
format {
migrate {
drift = format(
"{{ json . | json_merge %q }}",
jsonencode({
Tenant = each.value
})
)
}
}
}
atlas migrate drift --env prod now prints one JSON object per tenant, with Tenant, Mode,
Version, Pending, Drifted, Fingerprint, a Summary of change counts by kind and object type,
and the Changes with their Cmds. A tenant whose check fails gets an Error field instead.
After you push the migration directory to the Atlas Registry in the next
section, set dir = "atlas://db-per-tenant" in the env. Atlas then fetches the expected state from the
registry by version and hash, the dev attribute is no longer needed, and reports show (registry)
instead of (local).
For continuous, agent-based detection with alerts, see
Schema Monitoring: Drift Detection. The same check also runs at deploy
time as a pre-apply check, which stops migrate apply
on a drifted target before any migration runs.
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.