Database Testing and Local Development Tools

Databases 2026-02-09 · 6 min read database testing testcontainers docker migrations seed-data

Database Testing and Local Development Tools

Database testing is the most neglected part of most test suites. Teams write hundreds of unit tests with mocked data but never test their actual queries against a real database. When migrations fail in production, seed data is inconsistent, or a query that worked in SQLite breaks in Postgres, it's because the database was never part of the test pipeline. This guide covers the tools that fix this.

Testcontainers: Real Databases in Tests

Testcontainers spins up real databases in Docker containers for your tests. No mocks, no in-memory substitutes, no "it works on my machine." Your tests run against the same database engine you use in production.

Node.js / TypeScript

npm install -D @testcontainers/postgresql

import { PostgreSqlContainer } from "@testcontainers/postgresql";
import { Client } from "pg";
import { describe, it, expect, beforeAll, afterAll } from "bun:test";

describe("User Repository", () => {
  let container;
  let client: Client;

  beforeAll(async () => {
    container = await new PostgreSqlContainer("postgres:16")
      .withDatabase("testdb")
      .start();

    client = new Client({
      connectionString: container.getConnectionUri(),
    });
    await client.connect();

    // Run migrations
    await client.query(`
      CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at TIMESTAMPTZ DEFAULT NOW()
      )
    `);
  });

  afterAll(async () => {
    await client.end();
    await container.stop();
  });

  it("should insert and retrieve a user", async () => {
    await client.query(
      "INSERT INTO users (name, email) VALUES ($1, $2)",
      ["Alice", "[email protected]"]
    );

    const result = await client.query(
      "SELECT * FROM users WHERE email = $1",
      ["[email protected]"]
    );

    expect(result.rows[0].name).toBe("Alice");
  });

  it("should enforce unique email constraint", async () => {
    await client.query(
      "INSERT INTO users (name, email) VALUES ($1, $2)",
      ["Bob", "[email protected]"]
    );

    expect(
      client.query(
        "INSERT INTO users (name, email) VALUES ($1, $2)",
        ["Charlie", "[email protected]"]
      )
    ).rejects.toThrow();
  });
});

Supported Databases

Testcontainers has dedicated modules for most databases:

npm install -D @testcontainers/postgresql   # PostgreSQL
npm install -D @testcontainers/mysql         # MySQL
npm install -D @testcontainers/mongodb       # MongoDB
npm install -D @testcontainers/redis         # Redis
npm install -D @testcontainers/elasticsearch # Elasticsearch

For databases without a dedicated module, use the generic container:

import { GenericContainer } from "testcontainers";

const container = await new GenericContainer("cockroachdb/cockroach:latest")
  .withExposedPorts(26257)
  .withCommand(["start-single-node", "--insecure"])
  .start();

Performance Tips

Container startup takes 2-5 seconds depending on the image. For large test suites, this adds up. Solutions:

Reuse containers across test files:

// test-setup.ts
import { PostgreSqlContainer, StartedPostgreSqlContainer } from "@testcontainers/postgresql";

let container: StartedPostgreSqlContainer;

export async function getTestDatabase() {
  if (!container) {
    container = await new PostgreSqlContainer("postgres:16").start();
  }
  return container.getConnectionUri();
}

Use Testcontainers' reuse feature:

const container = await new PostgreSqlContainer("postgres:16")
  .withReuse()
  .start();

This keeps the container running between test runs. The first run starts it; subsequent runs reuse the existing container.

Clean data between tests instead of recreating containers:

afterEach(async () => {
  await client.query("TRUNCATE TABLE users, orders, products CASCADE");
});

Docker Compose for Local Development

For development environments with multiple services, Docker Compose is the standard:

# docker-compose.dev.yml
services:
  postgres:
    image: postgres:16
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: myapp_dev
      POSTGRES_USER: dev
      POSTGRES_PASSWORD: devpass
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./db/init.sql:/docker-entrypoint-initdb.d/init.sql

  redis:
    image: redis:7-alpine
    ports:
      - "6379:6379"

  mailhog:
    image: mailhog/mailhog
    ports:
      - "1025:1025"   # SMTP
      - "8025:8025"   # Web UI

volumes:
  pgdata:

docker compose -f docker-compose.dev.yml up -d

Database Init Scripts

The init.sql file runs when the container is first created:

-- db/init.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Create schemas
CREATE SCHEMA IF NOT EXISTS app;

-- Grant permissions
GRANT ALL ON SCHEMA app TO dev;

Migration Testing

Testing Migrations in CI

Migrations that work locally can fail in production because of data, permissions, or version differences. Test them properly:

# .github/workflows/ci.yml
migration-test:
  runs-on: ubuntu-latest
  services:
    postgres:
      image: postgres:16
      env:
        POSTGRES_DB: test
        POSTGRES_PASSWORD: test
      ports:
        - 5432:5432
      options: >-
        --health-cmd pg_isready
        --health-interval 10s
        --health-timeout 5s
        --health-retries 5
  steps:
    - uses: actions/checkout@v4
    - uses: oven-sh/setup-bun@v2
    - run: bun install
    - run: bun run db:migrate
      env:
        DATABASE_URL: postgres://postgres:test@localhost:5432/test
    - run: bun run db:seed
      env:
        DATABASE_URL: postgres://postgres:test@localhost:5432/test

Testing Up and Down Migrations

Always test that migrations can be rolled back:

# Apply all migrations
bun run db:migrate

# Roll back the last migration
bun run db:migrate:rollback

# Re-apply it
bun run db:migrate

# If this cycle works, your migration is reversible

Automate this in CI:

- name: Test migration rollback
  run: |
    bun run db:migrate
    bun run db:migrate:rollback
    bun run db:migrate

Schema Drift Detection

Check that your migration files produce the expected schema:

# With Drizzle
bunx drizzle-kit check

# With Prisma
bunx prisma migrate diff \
  --from-migrations ./prisma/migrations \
  --to-schema-datamodel ./prisma/schema.prisma \
  --exit-code

If the schema produced by your migrations doesn't match your schema definition, something drifted. Catch this in CI, not in production.

Seed Data Management

Structured Seed Scripts

Don't dump random data into seed scripts. Create structured, deterministic seed data:

// db/seed.ts
import { db } from "./client";
import { users, products, orders } from "./schema";

async function seed() {
  // Clear existing data (order matters for foreign keys)
  await db.delete(orders);
  await db.delete(products);
  await db.delete(users);

  // Seed users
  const [alice, bob] = await db
    .insert(users)
    .values([
      { name: "Alice Admin", email: "[email protected]", role: "admin" },
      { name: "Bob User", email: "[email protected]", role: "user" },
    ])
    .returning();

  // Seed products
  const [widget, gadget] = await db
    .insert(products)
    .values([
      { name: "Widget", price: 999, stock: 100 },
      { name: "Gadget", price: 1999, stock: 50 },
    ])
    .returning();

  // Seed orders
  await db.insert(orders).values([
    { userId: alice.id, productId: widget.id, quantity: 2 },
    { userId: bob.id, productId: gadget.id, quantity: 1 },
  ]);

  console.log("Seed data inserted successfully");
}

seed().catch(console.error);

Factory Functions for Tests

Instead of copying seed data everywhere, create factory functions:

// test/factories.ts
import { db } from "../db/client";
import { users, products } from "../db/schema";

let counter = 0;

export async function createUser(overrides = {}) {
  counter++;
  const [user] = await db
    .insert(users)
    .values({
      name: `Test User ${counter}`,
      email: `test${counter}@example.com`,
      role: "user",
      ...overrides,
    })
    .returning();
  return user;
}

export async function createProduct(overrides = {}) {
  counter++;
  const [product] = await db
    .insert(products)
    .values({
      name: `Product ${counter}`,
      price: 999,
      stock: 100,
      ...overrides,
    })
    .returning();
  return product;
}

// In tests
it("should not allow orders with zero quantity", async () => {
  const user = await createUser();
  const product = await createProduct({ stock: 10 });

  expect(
    createOrder({ userId: user.id, productId: product.id, quantity: 0 })
  ).rejects.toThrow("Quantity must be positive");
});

Local Database Tools

pgAdmin and Database GUIs

For visual database management during development:

pgAdmin: Web-based PostgreSQL admin. Run it in Docker alongside your dev database.
DBeaver: Universal database GUI. Supports every database you'll encounter. Free community edition.
TablePlus: Clean, fast native app for macOS, Windows, and Linux. Paid but worth it.

psql Tips for Development

# Connect to local dev database
psql postgres://dev:devpass@localhost:5432/myapp_dev

# Useful psql commands
\dt                    # list tables
\d users               # describe table
\di                    # list indexes
\x                     # toggle expanded display
\timing                # toggle query timing
\watch 2               # re-run last query every 2 seconds

Database Snapshots

For rapid iteration, snapshot and restore your dev database:

# Snapshot
pg_dump -Fc myapp_dev > snapshot.dump

# Restore
pg_restore -d myapp_dev --clean snapshot.dump

Create snapshots at known-good states. When a migration goes wrong during development, restore in seconds instead of re-seeding from scratch.

Comparison

Tool	Purpose	Setup Complexity	CI Support
Testcontainers	Real databases in tests	Low	Excellent
Docker Compose	Local dev environments	Low	Good
GitHub Services	CI database instances	Minimal	Native
Factory functions	Test data generation	Custom code	N/A
pg_dump/restore	Dev database snapshots	Minimal	N/A

Recommendations

Use Testcontainers for integration tests. Test against the same database engine you run in production. Mocking your database means your tests lie to you.
Use Docker Compose for local development. One command to spin up Postgres, Redis, and any other services you need. Commit the compose file so every developer has the same setup.
Test migrations in CI. Run migrations against a fresh database on every PR. Test rollback. Detect schema drift.
Create factory functions for test data. Don't copy-paste seed data. Factories are more readable, more maintainable, and make tests self-documenting.
Use database snapshots during development. They save minutes of re-seeding time when you need to reset your dev database.
General principle: Your database is part of your application. Test it like code. If your tests don't touch a real database, you're not testing your application -- you're testing your mocks.