english/.opencode/skills/web-testing/references/database-testing.md

Database Testing

Testcontainers (Real Database Instances)

Setup

npm install -D @testcontainers/postgresql
# or
npm install -D @testcontainers/mongodb

PostgreSQL Example

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

describe('User Repository', () => {
  let container: PostgreSqlContainer;
  let pool: Pool;

  beforeAll(async () => {
    container = await new PostgreSqlContainer()
      .withDatabase('testdb')
      .start();

    pool = new Pool({ connectionString: container.getConnectionUri() });
    await runMigrations(pool);
  }, 60000); // 60s timeout for container start

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

  afterEach(async () => {
    await pool.query('TRUNCATE users RESTART IDENTITY CASCADE');
  });

  it('creates user', async () => {
    const repo = new UserRepository(pool);
    const user = await repo.create({ email: 'test@example.com' });
    expect(user.id).toBeDefined();
  });
});

MongoDB Example

import { MongoDBContainer } from '@testcontainers/mongodb';
import mongoose from 'mongoose';

describe('User Repository', () => {
  let container: MongoDBContainer;

  beforeAll(async () => {
    container = await new MongoDBContainer().start();
    await mongoose.connect(container.getConnectionString(), {
      directConnection: true,
    });
  }, 60000);

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

  afterEach(async () => {
    await mongoose.connection.dropDatabase();
  });
});

Transaction Rollback Pattern

describe('User Service', () => {
  let transaction: Transaction;

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

  afterEach(async () => {
    await transaction.rollback(); // Always rollback
  });

  it('creates user within transaction', async () => {
    const service = new UserService(transaction);
    await service.create({ email: 'test@example.com' });
    // Transaction rolls back - no cleanup needed
  });
});

Playwright Database Fixture

// fixtures/db.ts
import { test as base } from '@playwright/test';
import { PostgreSqlContainer } from '@testcontainers/postgresql';

export const test = base.extend<{ db: Pool }>({
  db: [async ({}, use, testInfo) => {
    const container = await new PostgreSqlContainer().start();
    const pool = new Pool({ connectionString: container.getConnectionUri() });

    // Seed per-worker data
    await seedData(pool, testInfo.workerIndex);

    await use(pool);

    await pool.end();
    await container.stop();
  }, { scope: 'worker' }]
});

In-Memory Alternatives

// SQLite in-memory (faster, less realistic)
const db = new Database(':memory:');

// PGlite (Postgres in browser/Node)
import { PGlite } from '@electric-sql/pglite';
const db = new PGlite();

Best Practices

• Real DB in CI: Use Testcontainers for high fidelity
• In-memory locally: Faster iteration during development
• Isolation: Worker-scoped containers for parallel tests
• Migrations: Always run migrations before tests
• Cleanup: Truncate after each test, stop containers after all
• Timeouts: Increase timeout for container startup (60s)