How to set up Drizzle for testing? (vitest)

[guettli]

I would like to use drizzle with PG. I want to run tests with vitest.

What is the recommended setup?

I have a local PG. I have concerns that tests can't be run in parallel when connecting to one DB.

[KeepALifeUS]

Drizzle + Vitest Testing Setup

Here are the recommended approaches for parallel test isolation:

Option 1: Schema-per-test (Recommended for PG)

// test/setup.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { Pool } from "pg";
import { randomUUID } from "crypto";

export async function createTestDb() {
  const schemaName = `test_${randomUUID().slice(0, 8)}`;
  
  const pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });
  await pool.query(`CREATE SCHEMA IF NOT EXISTS "${schemaName}"`);
  await pool.query(`SET search_path TO "${schemaName}"`);
  
  const db = drizzle(pool);
  await migrate(db, { migrationsFolder: "./drizzle" });
  
  return {
    db,
    cleanup: async () => {
      await pool.query(`DROP SCHEMA "${schemaName}" CASCADE`);
      await pool.end();
    }
  };
}

Option 2: Use Testcontainers

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

let container: StartedPostgreSqlContainer;

beforeAll(async () => {
  container = await new PostgreSqlContainer().start();
  process.env.DATABASE_URL = container.getConnectionUri();
});

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

Option 3: Transaction rollback per test

import { beforeEach, afterEach } from "vitest";

let tx: Transaction;

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

afterEach(async () => {
  await tx.rollback();
});

Vitest Config

// vitest.config.ts
export default defineConfig({
  test: {
    pool: "forks",  // Use forks for true isolation
    poolOptions: {
      forks: { singleFork: true }  // Or parallel with schema isolation
    }
  }
});

Schema-per-test is the most reliable for parallel execution.