Seed Ephemeral Databases with Testcontainers

TL;DR

Start PostgreSQL with Testcontainers, run versioned SQL migrations, insert deterministic fixtures, and point your application at the container connection URI. Keep schema lifecycle at suite scope, reset rows between tests, and let Testcontainers remove the database when the suite ends.

Key Takeaways

• Start a real PostgreSQL container for each test suite instead of mocking database behavior.
• Apply schema migrations before fixtures so seed data always matches the current database contract.
• Use explicit fixture identifiers and reset sequences to make assertions deterministic.
• Wait for container readiness and verify the database with an actual query.
• Reset data between tests while keeping schema setup at suite scope for practical speed.
• Capture container logs and connection details without exposing credentials when failures occur.

A reliable seed ephemeral databases Testcontainers tutorial must do more than start PostgreSQL. It must wait for readiness, apply the real schema, load deterministic fixtures, prove isolation, and clean up without leaving state on a developer laptop or CI runner.

This tutorial builds that workflow in TypeScript with Node.js, Vitest, PostgreSQL, and Testcontainers. If you need the architecture behind short-lived databases, services, and full stacks, read the ephemeral test environments complete guide before or after this hands-on implementation.

You will use a real database engine, not an in-memory substitute. The result behaves like production PostgreSQL while remaining disposable, repeatable, and safe for parallel automation.

What You Will Build

You will create a small repository test harness that:

• Starts PostgreSQL 17 in a Docker container.
• Waits until PostgreSQL accepts connections.
• Applies ordered SQL migration files.
• Seeds stable users and orders inside a transaction.
• Tests repository behavior against the live container.
• Resets mutable tables between tests.
• Stops and removes the database after the suite.

The completed flow is start -> migrate -> seed -> test -> reset -> stop. It is suitable for a service repository, a contract-test project, or a CI job that needs realistic database behavior.

Prerequisites

Use Node.js 22 LTS or newer, npm 10 or newer, and a Docker-compatible container runtime. Docker Engine and Docker Desktop are common choices. The Testcontainers Node library talks to the runtime API, so confirm the daemon is running before installing anything.

Check your tools:

node --version
npm --version
docker version

Create a clean project and install the current package releases resolved by npm:

mkdir testcontainers-postgres-seeding
cd testcontainers-postgres-seeding
npm init -y
npm install pg
npm install --save-dev typescript vitest testcontainers @testcontainers/postgresql @types/node @types/pg

This tutorial uses ESM and the official PostgreSQL module from the Testcontainers for Node.js project. You need permission to pull postgres:17-alpine the first time the test runs. CI also needs access to a supported container runtime.

Verification: docker version prints both Client and Server sections, and npm ls @testcontainers/postgresql pg vitest shows installed packages without dependency errors.

Step 1: Start the Seed Ephemeral Databases Testcontainers Tutorial Project

Configure TypeScript, Vitest, and npm scripts first. This gives every later file the same ESM rules and makes the test command reproducible locally and in CI.

Replace the relevant fields in package.json with these values while keeping the dependencies created by npm:

{
  "name": "testcontainers-postgres-seeding",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "vitest run",
    "test:watch": "vitest"
  }
}

Create tsconfig.json:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "NodeNext",
    "moduleResolution": "NodeNext",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "types": ["node", "vitest/globals"]
  },
  "include": ["src", "test"]
}

Create vitest.config.ts:

import { defineConfig } from "vitest/config";

export default defineConfig({
  test: {
    environment: "node",
    fileParallelism: false,
    hookTimeout: 120_000,
    testTimeout: 30_000,
  },
});

Serial file execution keeps this first implementation easy to understand. Later, you can give each worker its own container and enable file parallelism. The longer hook timeout covers an initial image pull without weakening individual assertions.

Verification: Run npx tsc --noEmit. It should exit successfully. Vitest may report that no test files exist if you run it now, which is expected.

Step 2: Define the PostgreSQL Schema Migration

Create test/migrations/001_initial.sql. Treat this file like a production migration: declare keys, foreign keys, uniqueness, defaults, and checks in the database rather than relying only on application validation.

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX orders_user_id_idx ON orders(user_id);

Keeping migration SQL outside test code makes schema drift visible. Your production migration tool can replace this simple runner later, but the container must receive the same logical schema deployed elsewhere. Do not use synchronize: true or a test-only schema shortcut. Those conveniences can hide migration ordering and constraint failures.

Use UTC-aware TIMESTAMPTZ, integer minor currency units, and explicit UUID fixtures. These choices remove locale, floating-point, and generated-identifier surprises from assertions. The fixture values in the next step will remain readable across logs and failed snapshots.

Verification: Confirm the file is named 001_initial.sql and run npx tsc --noEmit again. SQL is executed in Step 4, where the test will query PostgreSQL metadata and fail if any statement is invalid.

Step 3: Start PostgreSQL with Testcontainers

Create test/support/database.ts. This module owns the container and the pg connection pool, so tests do not need to know mapped ports or credentials.

import { PostgreSqlContainer, type StartedPostgreSqlContainer } from "@testcontainers/postgresql";
import { Pool } from "pg";

export type TestDatabase = {
  container: StartedPostgreSqlContainer;
  pool: Pool;
};

export async function startTestDatabase(): Promise<TestDatabase> {
  const container = await new PostgreSqlContainer("postgres:17-alpine")
    .withDatabase("app_test")
    .withUsername("test_user")
    .withPassword("test_password")
    .start();

  const pool = new Pool({
    connectionString: container.getConnectionUri(),
    max: 5,
  });

  await pool.query("SELECT 1 AS ready");
  return { container, pool };
}

export async function stopTestDatabase(db: TestDatabase): Promise<void> {
  await db.pool.end();
  await db.container.stop();
}

Testcontainers selects an available host port and builds the correct connection URI. Never hardcode port 5432 on the host. Concurrent jobs can each expose container port 5432 through different mapped ports.

The PostgreSQL module includes a readiness strategy appropriate for the image. The explicit SELECT 1 adds an application-level proof that pg can authenticate and execute a query. Close the pool before stopping the container so no client holds a stale socket.

Verification: Create test/support/database.smoke.test.ts temporarily if you want an early check:

import { expect, test } from "vitest";
import { startTestDatabase, stopTestDatabase } from "./database.js";

test("starts PostgreSQL", async () => {
  const db = await startTestDatabase();
  const result = await db.pool.query<{ ready: number }>("SELECT 1 AS ready");
  expect(result.rows[0].ready).toBe(1);
  await stopTestDatabase(db);
});

Run npm test. The test passes, and Docker shows a short-lived PostgreSQL container that disappears afterward. Remove the smoke file before continuing to avoid starting a second container.

Step 4: Run Migrations Before Seeding

Add a small ordered migration runner to test/support/database.ts. Add these imports at the top:

import { readdir, readFile } from "node:fs/promises";
import { join } from "node:path";

Then add this exported function:

export async function migrateDatabase(
  pool: Pool,
  directory = join(process.cwd(), "test/migrations"),
): Promise<void> {
  const files = (await readdir(directory))
    .filter((file) => file.endsWith(".sql"))
    .sort();

  if (files.length === 0) {
    throw new Error(`No SQL migrations found in ${directory}`);
  }

  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    for (const file of files) {
      const sql = await readFile(join(directory, file), "utf8");
      await client.query(sql);
    }
    await client.query("COMMIT");
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

Lexically sortable numeric prefixes make ordering explicit. A transaction prevents a partially applied schema when a statement fails. This minimal runner is ideal for the tutorial. In an application, call your actual migration CLI or library so checksum tables, nontransactional migrations, and upgrade behavior receive coverage too.

Do not seed before migration. Fixtures should fail loudly when the schema changes instead of silently targeting an obsolete test-only shape.

Verification: After calling migrateDatabase(db.pool), run this query:

const tables = await db.pool.query<{ table_name: string }>(
  `SELECT table_name FROM information_schema.tables
   WHERE table_schema = 'public' ORDER BY table_name`,
);
expect(tables.rows.map((row) => row.table_name)).toEqual(["orders", "users"]);

The assertion proves that PostgreSQL executed the migration, not merely that the SQL file exists.

Step 5: Seed Deterministic PostgreSQL Fixtures

Create test/support/seed.ts. Insert the complete fixture graph in one transaction, with parent rows before child rows.

import type { Pool } from "pg";

export const fixtureIds = {
  userAda: "00000000-0000-4000-8000-000000000001",
  userGrace: "00000000-0000-4000-8000-000000000002",
  orderPaid: "10000000-0000-4000-8000-000000000001",
} as const;

export async function seedDatabase(pool: Pool): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query(
      `INSERT INTO users (id, email, display_name, created_at) VALUES
       ($1, $2, $3, $4), ($5, $6, $7, $8)`,
      [
        fixtureIds.userAda, "ada@example.test", "Ada Tester", "2026-01-01T00:00:00Z",
        fixtureIds.userGrace, "grace@example.test", "Grace Hopper", "2026-01-02T00:00:00Z",
      ],
    );
    await client.query(
      `INSERT INTO orders (id, user_id, status, total_cents, created_at)
       VALUES ($1, $2, $3, $4, $5)`,
      [
        fixtureIds.orderPaid, fixtureIds.userAda, "paid", 1299,
        "2026-01-03T00:00:00Z",
      ],
    );
    await client.query("COMMIT");
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

Parameterized queries prevent escaping mistakes and model safe application behavior. Reserved .test email domains cannot accidentally target a real mailbox. Fixed timestamps and UUIDs make expected results stable. Seed only the smallest graph required for the behavior under test. A copied production dump is slower, harder to understand, and may expose sensitive data.

If production-like data is necessary for broader validation, follow a documented production data masking workflow for preview environments. Masking is a separate control, not a substitute for focused fixtures.

Verification: After migration and seeding, query the counts:

const users = await db.pool.query<{ count: string }>("SELECT COUNT(*) FROM users");
const orders = await db.pool.query<{ count: string }>("SELECT COUNT(*) FROM orders");
expect(Number(users.rows[0].count)).toBe(2);
expect(Number(orders.rows[0].count)).toBe(1);

The conversion is intentional because PostgreSQL COUNT values arrive from pg as strings by default.

Step 6: Apply the Seed Ephemeral Databases Testcontainers Tutorial

Create src/orderRepository.ts. Keep the repository dependent on Pool, which lets production and test code supply different connection settings without branching behavior.

import type { Pool } from "pg";

export type OrderSummary = {
  id: string;
  email: string;
  status: string;
  totalCents: number;
};

export async function findOrdersByEmail(
  pool: Pool,
  email: string,
): Promise<OrderSummary[]> {
  const result = await pool.query<{
    id: string; email: string; status: string; total_cents: number;
  }>(
    `SELECT o.id, u.email, o.status, o.total_cents
     FROM orders o
     JOIN users u ON u.id = o.user_id
     WHERE u.email = $1
     ORDER BY o.created_at, o.id`,
    [email],
  );

  return result.rows.map((row) => ({
    id: row.id,
    email: row.email,
    status: row.status,
    totalCents: row.total_cents,
  }));
}

Create test/orderRepository.test.ts and connect the full lifecycle:

import { afterAll, beforeAll, describe, expect, test } from "vitest";
import { findOrdersByEmail } from "../src/orderRepository.js";
import {
  migrateDatabase, startTestDatabase, stopTestDatabase, type TestDatabase,
} from "./support/database.js";
import { fixtureIds, seedDatabase } from "./support/seed.js";

describe("order repository with ephemeral PostgreSQL", () => {
  let db: TestDatabase;

  beforeAll(async () => {
    db = await startTestDatabase();
    await migrateDatabase(db.pool);
    await seedDatabase(db.pool);
  });

  afterAll(async () => {
    await stopTestDatabase(db);
  });

  test("returns the seeded paid order", async () => {
    await expect(findOrdersByEmail(db.pool, "ada@example.test")).resolves.toEqual([
      {
        id: fixtureIds.orderPaid,
        email: "ada@example.test",
        status: "paid",
        totalCents: 1299,
      },
    ]);
  });

  test("returns an empty list for an unknown user", async () => {
    await expect(findOrdersByEmail(db.pool, "missing@example.test")).resolves.toEqual([]);
  });
});

This is the core of the seed ephemeral databases Testcontainers tutorial: production SQL runs against a real engine whose initial state is known. The join, parameter binding, constraints, mapping, and ordering all receive coverage.

Verification: Run npm test. Vitest should report one passing file and two passing tests. Run it twice. The second run must also pass because it receives a new container rather than state from the first run.

Step 7: Reset Data Between Mutating Tests

Suite-level containers avoid repeated startup cost, but a mutating test can contaminate the next test. Add this function to test/support/seed.ts:

export async function resetDatabase(pool: Pool): Promise<void> {
  await pool.query("TRUNCATE TABLE orders, users CASCADE");
  await seedDatabase(pool);
}

Then update the Vitest imports and add a hook inside the describe block:

import { afterAll, beforeAll, beforeEach, describe, expect, test } from "vitest";
import { fixtureIds, resetDatabase, seedDatabase } from "./support/seed.js";

beforeEach(async () => {
  await resetDatabase(db.pool);
});

TRUNCATE is fast and explicit for a small fixture schema. List all mutable tables and use CASCADE carefully. A missing table can leave hidden state, so update the reset function whenever a migration adds persistent data. PostgreSQL sequences can be reset with RESTART IDENTITY if your schema uses generated integer IDs. This example uses fixed UUIDs, so sequence state does not exist.

An alternative is one container per test. That provides the strongest boundary but increases startup work. Another option is wrapping each test in a transaction and rolling it back, but it fails when application code opens separate connections or commits independently. Choose the boundary that matches what the test must prove.

Verification: Add a test that deletes every user and confirms the table is empty. Add another test that expects two users. Both should pass in either order because beforeEach restores fixtures. You can temporarily call test.shuffle only if your chosen Vitest version supports the intended API; otherwise reverse the tests manually. Avoid depending on execution order.

Step 8: Make the Workflow CI-Ready

Run the same npm test command in CI. On GitHub Actions, an Ubuntu runner provides Docker, so no PostgreSQL service block is necessary. Create .github/workflows/integration.yml:

name: integration

on:
  pull_request:
  push:
    branches: [main]

jobs:
  test:
    runs-on: ubuntu-latest
    timeout-minutes: 15
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 22
          cache: npm
      - run: npm ci
      - run: npm test

Do not add fixed host ports or a long-lived database secret. Testcontainers supplies credentials for the disposable database at runtime. Pin the PostgreSQL image tag in code so a registry update does not silently change the database major version. Let your dependency update process review image and npm package upgrades deliberately.

For multiple Vitest workers, create a database container per worker or test file. Never share one mutable schema across concurrent workers unless each gets a unique database or schema namespace. For full application previews, use a preview environment for every pull request, then enforce automatic cleanup for stale preview environments. A test container handles process cleanup, while preview infrastructure also needs quotas, labels, and scheduled reconciliation.

Verification: Push a branch and inspect the job log. It should pull PostgreSQL, run both tests, stop the container, and finish without a service-container declaration. Re-run the job to confirm it does not depend on artifacts from the previous runner.

Troubleshooting

Cannot connect to the Docker daemon -> Start Docker and verify docker version shows a Server section. On remote or rootless runtimes, configure the supported socket environment for your platform instead of hardcoding a local path in test code.

Container startup times out on the first run -> Pull postgres:17-alpine once with docker pull postgres:17-alpine, check registry access, and retain the longer Vitest hook timeout. Do not add arbitrary sleeps because readiness time varies.

Authentication succeeds but relations do not exist -> Call migrateDatabase after the container starts and before seeding. Log the sorted migration filenames and query information_schema.tables to distinguish path errors from SQL errors.

Duplicate key errors appear between tests -> Reset tables before every mutating test, or change suite scope to test scope. Do not make inserts silently ignore conflicts, because that hides leaked state.

Vitest hangs after assertions pass -> Close the pg pool with pool.end() before stopping the container. Also check for application clients, timers, or consumers that remain open.

Tests fail only under parallel execution -> Give each worker an independent container, database, or schema. A shared database plus destructive TRUNCATE creates races even when every individual test is correct.

Best Practices

• Pin the database major version and test upgrades intentionally.
• Execute real migrations instead of maintaining test-only DDL.
• Keep fixtures small, named, deterministic, and free of personal data.
• Seed inside a transaction so failures leave no partial graph.
• Assert business outcomes, not container implementation details.
• Use mapped connection details returned by Testcontainers.
• Close clients before container cleanup.
• Preserve useful failure logs, but redact credentials and customer data.
• Separate unit tests from container-backed integration tests in reports.
• Prove repeatability by running the suite multiple times and in CI.

Avoid a global shared database on a developer machine. It creates invisible prerequisites and makes failures depend on whatever ran previously. Avoid enormous fixture factories too. A test should reveal the few records that establish its scenario. For broader test-layer decisions, review the integration testing guide. To strengthen database assertions and query review, use these SQL interview questions for testers as a practical checklist.

Interview Questions and Answers

Q: Why use Testcontainers instead of mocking PostgreSQL?

A mock can verify application decisions, but it does not execute PostgreSQL SQL, constraints, indexes, transactions, or type behavior. Testcontainers gives the test a real engine with an isolated lifecycle. Use unit mocks for narrow logic and container-backed tests for persistence boundaries.

Q: Should you start one database per test or per suite?

Start per suite when schema setup is expensive and reset data before each test. Start per test when maximum isolation matters or reset logic is risky. Measure the tradeoff and keep parallel workers independent.

Q: Why run migrations before seed scripts?

Fixtures are consumers of the schema. Running migrations first ensures fixture failures expose incompatible schema changes and validates the deployment path. A separate test schema can drift and create false confidence.

Q: How do you make database fixtures deterministic?

Use fixed valid identifiers, explicit timestamps, stable ordering, and the smallest complete relationship graph. Avoid current time, random values, and production dumps unless the test explicitly controls them. Reset state before mutating tests.

Q: How should secrets be managed for an ephemeral database?

Generate or define test-only credentials in the container configuration and consume the returned connection URI. Never reuse production credentials. Redact the URI if logs could leave the CI boundary.

Q: What causes flaky container database tests?

Common causes include fixed sleeps, shared mutable state, hardcoded host ports, unordered queries, unclosed clients, and image drift. Replace sleeps with readiness checks, isolate workers, order results explicitly, and pin image versions.

Where To Go Next

You now have a realistic database integration-test boundary. Use the complete guide to ephemeral test environments to decide where containers, namespaces, and preview stacks fit across your delivery pipeline.

Next, expand from a database test to a per-pull-request preview environment. If the preview needs representative records, apply safe production data masking. Finally, add automatic stale environment destruction so failed jobs cannot accumulate infrastructure.

Conclusion

The dependable pattern is simple: start a pinned PostgreSQL container, verify readiness, migrate, seed deterministic fixtures, run production repository code, reset mutations, and stop every resource. This gives you database fidelity without the state leakage of a shared server.

Adopt the harness with one repository test first. Run it repeatedly and in CI, then expand fixtures only when a new behavior requires them. Small, explicit scenarios remain the easiest ephemeral tests to trust and debug.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Accessibility testing with Cypress (2026)
• Accessibility testing with Playwright (2026)
• Agentic testing with tool calling (2026)
• AI powered visual testing with vision models (2026)
• AI test data generation with Faker and LLMs (2026)
• API contract testing with Pact: A Practical Guide (2026)