Fast database tests: migrate a template once, copy it per test

by Chanan Braunstein, Owner

Every database test in TripEagle gets a database of its own. Nothing is shared, nothing is mocked, and each test starts from exactly what the migrations make. That's the kind of test I trust. It was also slow: the web suite took 2,076 seconds of test time on CI, spread over three shards, and nearly all of it was database tests.

Most of that wasn't the tests. It was the setup: running every migration, 93 of them at the time, into a fresh schema before each test. The fix was to run the migrations once per run, into a template, and give each test a copy. The suite went to 962 seconds, 54% less, and only one existing test had to change.

This post is about that change: what per-test migration costs, how CREATE DATABASE … TEMPLATE works and the gotchas it comes with, and how the same question, "a schema or a database?", played out for pull request previews. It's the fourth post in Techniques from TripEagle, after one import that shipped my database driver to the browser. The code is simplified from the TripEagle monorepo so it stands on its own.

What per-test migration costs

TripEagle's tests reach the database through one helper. A test calls it, gets back connection URLs, and the helper drops everything when the test finishes:

test('a member sees only their own trip', async () => {
  const db = await scratchDatabase()
  // …seed rows through db.ownerUrl, query through db.appUrl
})

Behind that call, each test got:

  • Two new roles. An owner, which owns the schema and runs the migrations, and an app role, which owns nothing and is granted the tables. That's how production is set up, and neither is a superuser. The tables force row-level security, so its policies apply to both, the owner included.
  • A new schema, owned by the owner.
  • Every migration, run into that schema as the owner.

The migrations are the expensive part, and they grow with the app. I measured the helper on a clean local Postgres 17 (Docker on a Mac), ten calls each:

scratchDatabase()Setup, medianSetup and teardown, median
Migrating a schema847 ms1,039 ms
Copying a migrated template180 ms324 ms

A database test's own work is usually a few hundred milliseconds. So with per-test migration, a test spent more time getting a database than using one. Two test files with 50 tests between them took 18 seconds.

The obvious way out is to share: one schema per file, and truncate the tables or roll back a transaction between tests. It's cheaper still, but I didn't want it. A test would no longer start from what the migrations make, and the concurrency tests commit real transactions from several connections, which a rollback can't undo. I wanted to keep the database per test and make it cheaper to get.

CREATE DATABASE … TEMPLATE

Postgres has had the answer built in for a long time. Every CREATE DATABASE copies another database: by default template1, which is why a new database comes with whatever you installed there. You can name any database instead:

create database testrun_123_copy template testrun_123_template;

The copy has the template's schemas, tables, functions, policies and rows. It doesn't replay any SQL. Since Postgres 15 the default strategy, wal_log, copies the template block by block through the write-ahead log, which for TripEagle's schema takes about 100 ms. The older strategy = file_copy copies the files and forces a checkpoint each time. I tried it, and it was slower, at about 380 ms.

So the plan is:

  1. Before any test runs, migrate one template database.
  2. For each test, copy it.
  3. When the run ends, drop the template and anything a test left behind.

Migrating the template once

Vitest's globalSetup runs once per run, before any test file, which is exactly where the template belongs. It builds the template the same way a test's schema used to be built, as a non-superuser owner, from the migration files on disk, so a migration added on the branch is in it. In watch mode, project.onTestsRerun migrates it again before each rerun, so a migration written while Vitest watches is in the next run's copies:

// database-tests.setup.ts — vitest globalSetup
import { randomUUID } from 'node:crypto'
import { migrate } from './migrate'
import { withClient, urlFor } from './pg-helpers' // a client per call; a URL with a database, user and search_path
import { dropRun } from './leftovers' // below: drops everything named with the prefix

export default async function setup(project) {
  const url = process.env.MIGRATE_DATABASE_URL // a superuser with CREATEDB
  const prefix = `testrun_${process.pid}_`
  const template = { prefix, database: `${prefix}template`, owner: `${prefix}template_owner` }

  const password = randomUUID()
  await withClient(url, async (client) => {
    await client.query(`create role ${template.owner} login password '${password}'`)
    await client.query(`create database ${template.database}`)
  })
  await withClient(urlFor(url, { database: template.database }), (client) =>
    client.query(`create schema ${template.database} authorization ${template.owner}`),
  )
  await migrate({
    url: urlFor(url, {
      database: template.database,
      user: template.owner,
      password,
      searchPath: template.database,
    }),
  })

  // Postgres copies no database that anyone is connected to.
  await withClient(url, (client) =>
    client.query(`alter database ${template.database} with allow_connections false`),
  )

  project.provide('databaseTemplate', template)
  return () => dropRun(url, prefix)
}

project.provide hands the template's names to the test workers, which read them back with inject('databaseTemplate').

If the template fails to migrate, the real setup catches the error and provides that instead, so every database test fails with the migration's own message, while the tests that never ask for a database still run. A broken migration then shows up as one clear error repeated, not as a setup crash with no tests run.

The gotcha: no open connections

Postgres refuses to copy a database while anyone else is connected to it:

ERROR:  source database "testrun_123_template" is being accessed by other users

That includes your own idle pool connection, the migrate step's connection if it's still open, and a psql someone left connected. Copying itself is fine: the session running CREATE DATABASE is connected to another database, and Postgres lets any number of copies of one template run at once. It's any connection to the template that breaks every copy until it goes away.

Two things avoid it. First, everything that touches the template closes its connection: the setup uses a client per step, not a pool. Second, once it's migrated, the setup marks it allow_connections false. After that nobody can connect, not even by mistake, so a stray session can't make every copy in the run fail.

Making the copy the test's own

A raw copy isn't quite what the migrations would have made for the test. Its schema still has the template's name, its objects still belong to the template's owner, and a few things inside it point back at the template by name. So the helper fixes those up:

// test-database.ts
import { randomUUID } from 'node:crypto'
import { inject, onTestFinished } from 'vitest'
import { withClient, urlFor } from './pg-helpers'
// repointSearchPaths, grantTables and dropCopy are below

const superuserUrl = process.env.MIGRATE_DATABASE_URL

export async function scratchDatabase() {
  const template = inject('databaseTemplate')
  const name = `${template.prefix}${randomUUID().replaceAll('-', '')}`
  const owner = `${name}_owner`
  const app = `${name}_app`
  const password = randomUUID()

  await withClient(superuserUrl, async (client) => {
    await client.query(`create role ${owner} login password '${password}'`)
    await client.query(`create role ${app} login password '${password}'`)
    await client.query(`create database ${name} template ${template.database}`)
  })
  onTestFinished(() => dropCopy(name, owner, app))

  await withClient(urlFor(superuserUrl, { database: name }), async (client) => {
    await client.query(`alter schema ${template.database} rename to ${name}`)
    await client.query(`reassign owned by ${template.owner} to ${owner}`)
    await repointSearchPaths(client, template.database, name)
    await client.query(`set role ${owner}`)
    await grantTables(client, name, app) // what the migrate command grants
  })

  return {
    schema: name,
    ownerUrl: urlFor(superuserUrl, { database: name, user: owner, password, searchPath: name }),
    appUrl: urlFor(superuserUrl, { database: name, user: app, password, searchPath: name }),
  }
}

The schema and the database share one name, and the roles are named after it, which keeps everything after this simple.

  • Rename the schema. Any code that reads a test's schema by name keeps working.
  • Reassign ownership. reassign owned by moves everything the template's owner owned in this database to the test's owner, in one statement.
  • Fix search_path functions. This one surprised me. A function declared with set search_path from current stores the schema's name at the moment it was created. In the copy, that name is the template's, which this database no longer has. Renaming the schema doesn't update it, so each such function has to be pointed at the new name:
do $$
declare fn record;
begin
  for fn in
    select p.oid::regprocedure as signature, substr(setting, 13) as path
      from pg_proc p, unnest(p.proconfig) setting
     where p.pronamespace = 'testrun_123_copy'::regnamespace
       and setting like 'search_path=%'
  loop
    execute format('alter routine %s set search_path = %s',
      fn.signature, replace(fn.path, 'testrun_123_template', 'testrun_123_copy'));
  end loop;
end $$;
  • Grant the tables, as the owner, to the test's app role, the same way the migrate command does in production.

How do you know the copy really matches a migrated schema? A test holds one to the other. It makes a copy and a freshly migrated schema side by side, reads both from the system catalog, and compares owners, grants, columns, constraints, indexes, row-level security policies, functions with their search_path, triggers, views and the list of migrations recorded. If a future migration adds something the copy gets wrong, that test fails, not a confusing one somewhere else.

Why the roles are still per test

Separate databases already keep the tests' rows apart, so why not make one owner and one app role for the whole run? It would save about 50 ms a test: the two roles, and reassigning ownership to them. But roles in Postgres belong to the cluster, not to a database. One test helper waits until a given role has some number of sessions blocked on a lock, by counting that role's rows in pg_stat_activity across the whole cluster. Another test grants a role membership in another. With shared roles, one test would count another's sessions, and the failure would be rare and baffling. The roles alone cost about 20 ms. I kept them.

Dropping a copy without force

The first time this ran on CI, every test passed and the run still failed, with 38 unhandled errors. The teardown dropped each copy with drop database … with (force), which terminates any session still connected. Some app tests leave a pool with an idle connection open, and when Postgres kills a connection under a pool, the pool emits an error nobody is listening for. Vitest counts that as a failure.

So a test drops its copy without force. If something is still connected, Postgres refuses (55006), and the copy is left for the run's teardown, which runs after the test processes have exited and can use force safely:

async function dropCopy(name, owner, app) {
  await withClient(superuserUrl, async (client) => {
    try {
      await client.query(`drop database if exists ${name}`)
    } catch (error) {
      if (error.code === '55006') return // still in use: the run's teardown drops it
      throw error
    }
    await client.query(`drop role if exists ${app}`)
    await client.query(`drop role if exists ${owner}`)
  })
}

Leftovers, found by name

Runs crash, and people press Ctrl-C. Everything a run makes starts with testrun_<pid>_: the template, every copy, every role. So a run's setup can clean up after the runs before it. It lists the schemas, then the databases, then the roles whose names match, and drops those whose process is gone:

function isRunning(pid) {
  try {
    process.kill(pid, 0) // signal 0: checks the process exists, sends nothing
    return true
  } catch (error) {
    return error.code === 'EPERM' // it exists, and belongs to someone else
  }
}

A live run's leftovers are never touched, which matters because two worktrees on one laptop can test against the same local Postgres at the same time. The pid check only works if every run against a database is on one machine, which is true for a laptop and for a CI job's own Postgres service.

The slow path stays

A few tests still migrate a schema of their own: the ones that test the migrations themselves, which need to stop at an earlier migration and check what the next one does, and the catalog test above, which needs a fresh migrate to compare against. They pass an option, and the helper takes the old path. Every other test copies.

The numbers

The helper's setup went from 847 ms to 180 ms, about a fifth. On CI (two-vCPU runners, the whole web suite in three shards):

ShardBeforeAfterChange
1/3626 s340 s−46%
2/3610 s338 s−45%
3/3840 s284 s−66%
Total2,076 s962 s−54%

Each job's wall-clock time went from 11 to 15 minutes to 6 or 7. The only call site that changed was one test that reached a test's schema through the main database's URL, which no longer holds it. It now asks the helper for the copy's URL.

Scratch schemas for runs

Not everything that needs a database is a unit test. TripEagle's end-to-end suites, its demo recorder and its red-team script each need a migrated, seeded database for one run, and must never touch the dev database's data or its AI spend ledger. They get a scratch schema: a schema with its own two roles in the local Postgres, migrated and seeded once, and dropped when the run ends, whether it finished, threw or was stopped with Ctrl-C.

It's the same idea at a different scale. A unit test pays for a database per test, so that has to be cheap. A suite pays for one per run, so a plain migrate is fine, and a schema is enough isolation because one run is one process.

Previews: schemas, then a Postgres each

The same question came up for pull request previews, and there the answer went the other way.

Each pull request that touches the web app gets a preview app, and a branch's schema must never touch another's, or production's. The first design was a database per pull request on the production cluster. But Fly Managed Postgres, which TripEagle runs on, can create a database from its CLI and offers no way to drop one, so closed pull requests would leave databases behind until someone deleted them by hand.

So the next design made each preview a schema: one previews database, a schema pr_<number> with its own roles, migrated by the preview's release command, and dropped with DROP SCHEMA … CASCADE when the pull request closed. It was cheap, and it leaned on a rule the migrations already followed: a migration never names a schema. It runs in whatever schema the search_path gives it. That rule is what lets the tests, the scratch schemas and the template all run the same migrations.

It didn't survive the day. When I checked it on the real cluster:

  • No role on the cluster could create a role in SQL. Roles with passwords could only be made through the provider's CLI or dashboard.
  • The users that CLI makes all share a few group roles. Every one of them may connect to every database, and two of them can read and write all of its data.

So any credential a preview held could have read and written production's rows. A schema can isolate tables, but not if the roles themselves can reach everything.

Now each preview runs its own Postgres: a small postgres:17 machine with a 1 GB volume, reached over the private network. The preview job creates the owner and app roles there, the release command migrates as the owner, and closing the pull request destroys the machine and its volume. CI's preview jobs hold no credential for the production cluster at all. The cost is a small always-on machine for each open pull request, and in exchange the previews run the same plain Postgres 17 image that CI and local development use.

The lesson I took from that: a schema is enough isolation when you control the roles. In tests and scratch runs, I create every role and grant every table, so a schema or a copied database is all I need. On a managed cluster whose roles I don't control, it isn't.

What to take from this

  • Measure your test setup on its own. If getting a database takes longer than using it, that's where the time goes.
  • Migrate once, copy per test. CREATE DATABASE … TEMPLATE copies a migrated database in about 100 ms and keeps each test's database its own.
  • Close the template. Postgres won't copy a database with a connection open, so set allow_connections false once it's migrated.
  • Make the copy match a migrate: rename the schema, reassign ownership, fix set search_path from current functions, grant the tables. Then test that it matches, against the catalog.
  • Keep the roles per test if anything reads role activity across the cluster.
  • Don't drop with force under a live pool. Let the run's teardown do it.
  • Name everything a run makes after the run, so the next one can find and drop what a crash left.
  • Never name a schema in a migration. Then the same migrations run anywhere.

More articles

Red-teaming your own AI features, with numbers

How TripEagle measures whether its real models obey prompt injection: one corpus of injections, each feature's real prompt, three verdicts kept apart, a dollar cap, and records committed so runs can be compared.

Read more: Red-teaming your own AI features, with numbers

Tell us about your project

Ready to start building? Let's talk about how we can help bring your idea to life.