Database Testing: LocalDB vs. Docker and Testcontainers

For .NET engineers who know: SQL Server LocalDB, EF Core’s in-memory provider, xUnit, and integration testing with WebApplicationFactory You’ll learn: How the TypeScript ecosystem replaces LocalDB with real PostgreSQL in Docker, runs integration tests against isolated containers, and seeds test data with factory libraries Time: 15-20 min

The .NET Way (What You Already Know)

.NET gives you two main paths for database testing without touching production:

SQL Server LocalDB — a minimal SQL Server instance that runs as a user process. Integration tests connect to it with a LocalDB-specific connection string, run migrations, and test against real SQL Server behavior.

// appsettings.Testing.json
{
  "ConnectionStrings": {
    "Default": "Server=(localdb)\\mssqllocaldb;Database=MyApp_Test;Trusted_Connection=True;"
  }
}

// Integration test with WebApplicationFactory
public class UserApiTests : IAsyncLifetime
{
    private readonly WebApplicationFactory<Program> _factory;
    private AppDbContext _db = null!;

    public UserApiTests()
    {
        _factory = new WebApplicationFactory<Program>()
            .WithWebHostBuilder(builder => {
                builder.UseEnvironment("Testing");
            });
    }

    public async Task InitializeAsync()
    {
        _db = _factory.Services.GetRequiredService<AppDbContext>();
        await _db.Database.MigrateAsync(); // apply all migrations
    }

    public async Task DisposeAsync()
    {
        await _db.Database.EnsureDeletedAsync(); // clean up after suite
    }

    [Fact]
    public async Task CreateUser_Returns201_WithValidData()
    {
        var client = _factory.CreateClient();
        var response = await client.PostAsJsonAsync("/users", new { Name = "Alice", Email = "alice@example.com" });
        response.EnsureSuccessStatusCode();
        Assert.Equal(HttpStatusCode.Created, response.StatusCode);
    }
}

EF Core In-Memory Provider — a fake database that lives entirely in process memory. No SQL is generated, no PostgreSQL-specific behavior is exercised.

// Fast unit tests, but dangerous — doesn't test real SQL
services.AddDbContext<AppDbContext>(options =>
    options.UseInMemoryDatabase("TestDb"));

The in-memory provider is a trap. It accepts queries EF Core would reject against a real database. It does not enforce constraints, foreign keys, or uniqueness. It does not test your migrations. For anything beyond the simplest unit tests, LocalDB or a real database is the right choice.

The TypeScript Way

The Problem with In-Memory Fakes

TypeScript has no built-in equivalent to EF Core’s in-memory provider. Mock libraries like jest-mock-extended can mock the Prisma Client, but they have the same fundamental problem as the in-memory provider: they do not test your actual queries, your schema, or your database-specific behavior. Use them for pure unit tests of business logic, not for tests that touch data access.

For integration tests, the TypeScript ecosystem uses real PostgreSQL running in Docker.

Docker Compose for Local Development

The standard setup: a docker-compose.yml in your project root that defines a PostgreSQL container for local development and testing.

# docker-compose.yml
version: '3.9'

services:
  postgres:
    image: postgres:16-alpine
    restart: unless-stopped
    environment:
      POSTGRES_USER: myapp
      POSTGRES_PASSWORD: myapp
      POSTGRES_DB: myapp_dev
    ports:
      - '5432:5432'
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./scripts/init.sql:/docker-entrypoint-initdb.d/init.sql  # optional seed
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U myapp']
      interval: 5s
      timeout: 5s
      retries: 5

  postgres_test:
    image: postgres:16-alpine
    restart: unless-stopped
    environment:
      POSTGRES_USER: myapp
      POSTGRES_PASSWORD: myapp
      POSTGRES_DB: myapp_test
    ports:
      - '5433:5432'  # different host port so both can run simultaneously
    tmpfs:
      - /var/lib/postgresql/data  # in-memory storage — fast, no persistence

volumes:
  postgres_data:

The test database uses tmpfs — storage backed by RAM rather than disk. Writes do not survive container restart, which is fine for tests. It is significantly faster than disk-backed storage for write-heavy test workloads.

Start everything:

# Start both services
docker compose up -d

# Wait for postgres to be ready (if your test runner doesn't handle this)
docker compose exec postgres pg_isready -U myapp

Prisma Testing Patterns

Environment configuration for tests:

# .env.test
DATABASE_URL="postgresql://myapp:myapp@localhost:5433/myapp_test"

// package.json — pass test env when running tests
{
  "scripts": {
    "test": "dotenv -e .env.test -- vitest run",
    "test:watch": "dotenv -e .env.test -- vitest",
    "test:integration": "dotenv -e .env.test -- vitest run --reporter=verbose",
    "db:test:migrate": "dotenv -e .env.test -- prisma migrate deploy",
    "db:test:reset": "dotenv -e .env.test -- prisma migrate reset --force"
  }
}

Global test setup — apply migrations once, reset data between tests:

// vitest.config.ts
import { defineConfig } from 'vitest/config';

export default defineConfig({
  test: {
    globalSetup: './tests/setup/global-setup.ts',
    setupFiles: ['./tests/setup/test-setup.ts'],
    environment: 'node',
    testTimeout: 30_000, // integration tests can be slow
  },
});

// tests/setup/global-setup.ts
// Runs once before all test suites
import { execSync } from 'child_process';

export async function setup() {
  // Apply all pending migrations to the test database
  execSync('prisma migrate deploy', {
    env: { ...process.env, DATABASE_URL: process.env.DATABASE_URL },
    stdio: 'inherit',
  });
}

export async function teardown() {
  // Optional: drop test database after all suites complete
}

// tests/setup/test-setup.ts
// Runs before each test file
import { prisma } from '../lib/prisma';

beforeEach(async () => {
  // Reset all data between tests — order matters for foreign keys
  await prisma.$transaction([
    prisma.orderItem.deleteMany(),
    prisma.order.deleteMany(),
    prisma.user.deleteMany(),
  ]);
});

afterAll(async () => {
  await prisma.$disconnect();
});

Truncation vs. deletion:

For large test databases, TRUNCATE with CASCADE and RESTART IDENTITY is faster than deleteMany():

// Faster reset for large datasets
async function resetDatabase(prisma: PrismaClient) {
  const tableNames = await prisma.$queryRaw<{ tablename: string }[]>`
    SELECT tablename FROM pg_tables
    WHERE schemaname = 'public'
    AND tablename NOT LIKE '_prisma_%'
  `;

  const tables = tableNames.map(({ tablename }) => `"${tablename}"`).join(', ');

  await prisma.$executeRawUnsafe(
    `TRUNCATE TABLE ${tables} RESTART IDENTITY CASCADE;`
  );
}

Testcontainers for Isolated Integration Tests

Testcontainers starts a real Docker container programmatically inside your test suite. Each test suite gets its own isolated PostgreSQL instance — no shared state, no port conflicts, no manual Docker management required.

npm install --save-dev @testcontainers/postgresql

// tests/integration/user.test.ts
import { PostgreSqlContainer, StartedPostgreSqlContainer } from '@testcontainers/postgresql';
import { PrismaClient } from '@prisma/client';
import { execSync } from 'child_process';

let container: StartedPostgreSqlContainer;
let prisma: PrismaClient;

beforeAll(async () => {
  // Start a real PostgreSQL container — takes 5-15 seconds on first run
  container = await new PostgreSqlContainer('postgres:16-alpine')
    .withDatabase('testdb')
    .withUsername('test')
    .withPassword('test')
    .start();

  const connectionUrl = container.getConnectionUri();

  // Apply migrations to the fresh database
  execSync('prisma migrate deploy', {
    env: { ...process.env, DATABASE_URL: connectionUrl },
    stdio: 'pipe',
  });

  prisma = new PrismaClient({
    datasources: { db: { url: connectionUrl } },
  });
}, 60_000); // generous timeout for container startup

afterAll(async () => {
  await prisma.$disconnect();
  await container.stop();
});

beforeEach(async () => {
  // Reset between tests within this suite
  await prisma.$transaction([
    prisma.order.deleteMany(),
    prisma.user.deleteMany(),
  ]);
});

describe('User repository', () => {
  test('creates a user and retrieves it by email', async () => {
    await prisma.user.create({
      data: { name: 'Alice', email: 'alice@example.com' },
    });

    const found = await prisma.user.findUnique({
      where: { email: 'alice@example.com' },
    });

    expect(found).not.toBeNull();
    expect(found!.name).toBe('Alice');
  });

  test('enforces unique email constraint', async () => {
    await prisma.user.create({ data: { name: 'Alice', email: 'dupe@example.com' } });

    await expect(
      prisma.user.create({ data: { name: 'Bob', email: 'dupe@example.com' } })
    ).rejects.toThrow(); // Prisma throws PrismaClientKnownRequestError P2002
  });
});

Testcontainers pulls the image from Docker Hub on first use and caches it. Subsequent runs use the cached image and start in 2-5 seconds.

Seeding Test Data with Factories

fishery is the TS equivalent of factory_bot (Ruby) or AutoFixture (C#). It generates objects that match your types, with sensible defaults you can override per test.

npm install --save-dev fishery @faker-js/faker

// tests/factories/user.factory.ts
import { Factory } from 'fishery';
import { faker } from '@faker-js/faker';
import { Prisma } from '@prisma/client';

// Factory for the Prisma create input shape
export const userFactory = Factory.define<Prisma.UserCreateInput>(() => ({
  name: faker.person.fullName(),
  email: faker.internet.email(),
  isActive: true,
  createdAt: faker.date.recent(),
}));

// tests/factories/order.factory.ts
export const orderFactory = Factory.define<Prisma.OrderCreateInput>(({ associations }) => ({
  status: 'PENDING',
  total: parseFloat(faker.commerce.price({ min: 10, max: 500 })),
  customer: associations.customer ?? {
    create: userFactory.build(),
  },
  lineItems: {
    create: [
      {
        productName: faker.commerce.productName(),
        quantity: faker.number.int({ min: 1, max: 5 }),
        unitPrice: parseFloat(faker.commerce.price()),
      },
    ],
  },
}));

// Using factories in tests
test('returns only pending orders for a customer', async () => {
  const customer = await prisma.user.create({
    data: userFactory.build(),
  });

  // Create 3 orders with different statuses
  await prisma.order.createMany({
    data: [
      orderFactory.build({ status: 'PENDING', customer: { connect: { id: customer.id } } }),
      orderFactory.build({ status: 'COMPLETED', customer: { connect: { id: customer.id } } }),
      orderFactory.build({ status: 'PENDING', customer: { connect: { id: customer.id } } }),
    ],
  });

  const pendingOrders = await prisma.order.findMany({
    where: { customerId: customer.id, status: 'PENDING' },
  });

  expect(pendingOrders).toHaveLength(2);
});

Building without persisting — useful for unit tests:

// Build the object without hitting the database
const userData = userFactory.build({ name: 'Specific Name' });
// userData is a plain object matching Prisma.UserCreateInput
// Use it to test pure functions that operate on user-shaped data

Mocking the DB Layer for Unit Tests

For pure unit tests of service logic, mock the Prisma Client rather than running a real database. jest-mock-extended (or vitest-mock-extended) generates type-safe mocks for any TypeScript type.

npm install --save-dev vitest-mock-extended

// tests/unit/user.service.test.ts
import { describe, it, expect, vi } from 'vitest';
import { mockDeep, mockReset } from 'vitest-mock-extended';
import { PrismaClient } from '@prisma/client';
import { UserService } from '../../src/user.service';

const prismaMock = mockDeep<PrismaClient>();

beforeEach(() => {
  mockReset(prismaMock);
});

describe('UserService', () => {
  it('returns null when user does not exist', async () => {
    prismaMock.user.findUnique.mockResolvedValue(null);

    const service = new UserService(prismaMock);
    const result = await service.findById('nonexistent-id');

    expect(result).toBeNull();
    expect(prismaMock.user.findUnique).toHaveBeenCalledWith({
      where: { id: 'nonexistent-id' },
    });
  });

  it('throws when creating a user with duplicate email', async () => {
    prismaMock.user.create.mockRejectedValue(
      new Error('Unique constraint failed on the fields: (`email`)')
    );

    const service = new UserService(prismaMock);
    await expect(service.createUser({ name: 'Alice', email: 'exists@example.com' }))
      .rejects.toThrow();
  });
});

The mock is fully typed — prismaMock.user.findUnique has the same signature as the real Prisma method. TypeScript catches calls with wrong arguments.

Key Differences

Gotchas for .NET Engineers

1. Prisma does not apply migrations automatically in tests

EF Core’s Database.MigrateAsync() is one method call that applies all pending migrations. Prisma has no equivalent inside the PrismaClient API. You must call prisma migrate deploy via a shell command before tests run. Forgetting this means your tests run against a stale or empty schema and produce confusing errors.

// global-setup.ts — do not skip this
import { execSync } from 'child_process';

export async function setup() {
  execSync('prisma migrate deploy', {
    env: { ...process.env, DATABASE_URL: process.env.DATABASE_URL! },
    stdio: 'inherit',
  });
  console.log('Migrations applied to test database');
}

If you use Testcontainers, you must run migrations against the container’s connection URL, not the one in your environment:

execSync('prisma migrate deploy', {
  env: { ...process.env, DATABASE_URL: container.getConnectionUri() },
  stdio: 'pipe',
});

2. deleteMany ordering matters — foreign key violations will stop your reset

EF Core’s EnsureDeletedAsync() drops the entire database, bypassing all constraint checks. Prisma’s deleteMany() deletes rows from a live table. If you delete a parent table before its child tables, PostgreSQL will throw a foreign key violation.

// WRONG — deletes users before orders that reference them
await prisma.$transaction([
  prisma.user.deleteMany(),   // FK violation: orders still reference users
  prisma.order.deleteMany(),
]);

// CORRECT — delete children first, then parents
await prisma.$transaction([
  prisma.orderItem.deleteMany(),
  prisma.order.deleteMany(),
  prisma.user.deleteMany(),
]);

// Or use raw TRUNCATE with CASCADE (simpler for complex schemas)
await prisma.$executeRaw`TRUNCATE TABLE "OrderItem", "Order", "User" CASCADE`;

3. Testcontainer startup time requires a long timeout

xUnit’s test collection setup is synchronous; a 5-10 second container startup is unremarkable. Vitest’s beforeAll is async, but the default test timeout is short (5 seconds in some configurations). Container startup easily exceeds this.

Always set an explicit timeout on the beforeAll that starts your container:

beforeAll(async () => {
  container = await new PostgreSqlContainer().start();
  // ... migrate, connect ...
}, 60_000); // 60 seconds is safe; first pull takes longer

Also set testTimeout in vitest.config.ts to a value that accommodates your test durations.

4. The EF Core in-memory provider has no PostgreSQL equivalent — do not reach for a mock when you should use Docker

A common mistake is to use mockDeep<PrismaClient>() for every test, avoiding Docker entirely. This mirrors the mistake of using EF Core’s InMemoryProvider for integration tests. Mock-based tests do not verify that your Prisma queries produce correct SQL. They do not catch N+1 bugs, missing indexes, constraint violations, or schema drift. Use mocks only for unit tests of business logic that does not touch the database.

A reasonable split:

• Unit tests (fast, no Docker): mock PrismaClient, test service and domain logic in isolation.
• Integration tests (slower, require Docker): use @testcontainers/postgresql or the shared Docker Compose test database, test real queries end to end.

5. faker data can violate unique constraints unless you scope it

faker.internet.email() generates realistic-looking emails, but with a finite seed they repeat. In a test suite with many test cases, you will eventually collide.

// Fragile — same email could appear twice across test runs
const email = faker.internet.email();

// Robust — prefix with a unique identifier
const email = `test-${crypto.randomUUID()}@example.com`;

// Or use fishery's sequence counter
export const userFactory = Factory.define<Prisma.UserCreateInput>(({ sequence }) => ({
  email: `user-${sequence}@example.com`, // guaranteed unique within this factory
  name: faker.person.fullName(),
}));

Hands-On Exercise

Set up a complete integration testing environment for a simple blog API with Post and Comment entities.

Schema to implement:

model User {
  id       String    @id @default(cuid())
  email    String    @unique
  name     String
  posts    Post[]
}

model Post {
  id        String    @id @default(cuid())
  title     String
  content   String
  published Boolean   @default(false)
  authorId  String
  author    User      @relation(fields: [authorId], references: [id])
  comments  Comment[]
  createdAt DateTime  @default(now())
}

model Comment {
  id        String   @id @default(cuid())
  content   String
  postId    String
  post      Post     @relation(fields: [postId], references: [id])
  createdAt DateTime @default(now())
}

Tasks:

1. Write a docker-compose.yml with a postgres_test service using tmpfs storage on port 5433.
2. Write vitest.config.ts with globalSetup that applies migrations before all tests.
3. Write beforeEach cleanup that resets data in the correct order.
4. Write factories for User, Post, and Comment using fishery and faker.
5. Write an integration test that verifies: fetching a published post includes its comments and author, and fetching an unpublished post returns null for a non-author.
6. Write a unit test using mockDeep<PrismaClient>() that tests a PostService.publish() method throws when the post does not exist.

Quick Reference

Testcontainers minimal setup:

import { PostgreSqlContainer } from '@testcontainers/postgresql';

const container = await new PostgreSqlContainer('postgres:16-alpine')
  .withDatabase('testdb')
  .withUsername('test')
  .withPassword('test')
  .start();

const url = container.getConnectionUri();
// Use url for DATABASE_URL in prisma migrate deploy and PrismaClient
await container.stop(); // in afterAll

fishery factory pattern:

import { Factory } from 'fishery';
import { faker } from '@faker-js/faker';
import { Prisma } from '@prisma/client';

export const entityFactory = Factory.define<Prisma.EntityCreateInput>(({ sequence }) => ({
  field: faker.word.noun(),
  uniqueField: `value-${sequence}`,
}));

// Build without saving
const obj = entityFactory.build({ field: 'override' });

// Create in database
const created = await entityFactory.create({ field: 'override' }, { transient: { prisma } });

Vitest config for integration tests:

// vitest.config.ts
import { defineConfig } from 'vitest/config';

export default defineConfig({
  test: {
    globalSetup: './tests/setup/global-setup.ts',
    setupFiles: ['./tests/setup/test-setup.ts'],
    testTimeout: 30_000,
    hookTimeout: 60_000, // generous for container startup
    poolOptions: {
      threads: {
        singleThread: true, // avoid parallel DB access conflicts in integration tests
      },
    },
  },
});

Further Reading

• Testcontainers for Node.js — PostgreSQL module — installation, configuration, and usage patterns including reuse across test suites
• Prisma — Testing with Prisma — the official guide covering unit tests with mocks, integration tests with a test database, and e2e testing
• fishery documentation — factory library for TypeScript with associations, sequences, and transient parameters
• @faker-js/faker documentation — the community-maintained Faker port for TypeScript with locale support and all the standard fake data generators
• Vitest — globalSetup — how to run code once before all test suites, and the difference between globalSetup and setupFiles