Skip to main content

Faster Schema Tests with PostgreSQL Template Databases

Atlas schema tests run each test group against a fresh copy of your schema on the dev database: Atlas builds the desired schema, runs the group, and tears it down again so nothing leaks between cases. On large schemas, rebuilding the schema for every group dominates the run time. On PostgreSQL, Atlas can instead use a template database, making per-cycle setup near-constant no matter how large the schema is: typically 10x-20x faster end to end, and up to dozens of times faster for larger schemas.

Atlas Pro Feature

Schema testing and template databases are available only to Atlas Pro users. To use this feature, run:

atlas login

How It Works​

With template = true set on the dev block:

  • Every test group runs on a fresh copy of the desired schema (plus the optional baseline). Setup time per group is near-constant, no matter how large the schema is.
  • All DDL and DML performed by a test is isolated to its own copy and discarded when the group ends.
  • Tests marked parallel = true share a single fresh copy, and every non-parallel test gets its own copy, so sequences, inserts, and schema changes never bleed between cases.
  • Cluster-level role changes are isolated too: roles a test creates are removed, and baseline roles a test alters or drops are restored.

Docker-Managed Dev Database​

The simplest way to opt in is to add template = true to a docker "postgres" block and point your environment's dev at it:

atlas.hcl
docker "postgres" "dev" {
  image    = "postgres:18"
  template = true
}

env "test" {
  src = "file://schema.sql"
  dev = docker.postgres.dev.url
}

Given a schema:

schema.sql
CREATE TABLE users (
  id   BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

And a few tests that show the per-group isolation (users_table_exists runs in the shared parallel copy, while insert_is_isolated and fresh_copy each get their own):

schema.test.hcl
test "schema" "users_table_exists" {
  parallel = true
  exec {
    sql    = "SELECT count(*) FROM information_schema.tables WHERE table_name = 'users'"
    output = "1"
  }
}

test "schema" "insert_is_isolated" {
  exec {
    sql = "INSERT INTO users (name) VALUES ('alice')"
  }
  exec {
    sql    = "SELECT count(*) FROM users"
    output = "1"
  }
}

test "schema" "fresh_copy" {
  exec {
    sql    = "SELECT count(*) FROM users"
    output = "0"
  }
}

Run the tests:

atlas schema test --env test
-- PASS: users_table_exists (0.01s)
-- PASS: insert_is_isolated (0.01s)
-- PASS: fresh_copy (0.01s)
PASS

fresh_copy sees zero rows even though insert_is_isolated inserted one because each group ran on its own fresh copy of the schema.

User-Managed Dev Database​

To run against a database you manage yourself (e.g., a shared, pre-warmed PostgreSQL kept close to your tests) declare a dev "postgres" block with template = true:

atlas.hcl
dev "postgres" "main" {
  url      = "postgres://user:pass@dev-server:5432/dev"
  template = true
}

env "test" {
  src = "file://schema.sql"
  dev = dev.postgres.main.url
}

Atlas runs every test group on a fresh copy of the schema and leaves the database as it found it when the run finishes, so the same database can be reused across runs. The database must be dedicated to Atlas and grant Atlas enough privileges to create and drop databases and roles.

Baseline Objects​

Some objects must exist before your schema loads and while your tests run, yet are not part of the schema you are testing (i.e., cluster roles, extensions, or cloud-specific shim functions). Declare them with baseline, and every test group's copy includes them:

atlas.hcl
docker "postgres" "dev" {
  image    = "postgres:18"
  template = true
  baseline = <<-SQL
    CREATE ROLE app_owner;
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
  SQL
}

Since role changes are isolated per group, a baseline role that a test alters or drops is restored, and a role a test creates is removed. Baseline objects behave as a stable, isolated starting point for every case.

Limitations
  • Template databases are PostgreSQL only; CockroachDB, YugabyteDB, and Aurora DSQL are not supported.
  • The dev connection must target a database rather than a single schema (a search_path-scoped URL is not supported).
  • The speedup applies across several test groups; a run with a single group sees no benefit.
  • A user-managed dev database must be dedicated to Atlas and grant it the privileges needed to create and drop databases and roles.

Next Steps​