Test Database Isolation

The pollution problem

Test A creates a book. Test B expects an empty database. If B runs after A, it finds a book it did not create. B fails — not because of a bug, but because of leftover data from A. This is test pollution.

Strategy 1: DELETE before each test

The simplest approach — clear all tables before each test:

beforeEach(() => {
  // Order matters: delete children before parents (foreign keys)
  testDb.exec("DELETE FROM reviews");
  testDb.exec("DELETE FROM books");
  testDb.exec("DELETE FROM authors");
});

This is what the Factories lesson used. It works well for small schemas. For large schemas with many tables, the DELETE list grows long.

Strategy 2: Drop and recreate

For larger schemas, drop all tables and recreate:

function resetDatabase() {
  testDb.exec("DROP TABLE IF EXISTS reviews");
  testDb.exec("DROP TABLE IF EXISTS books");
  testDb.exec("DROP TABLE IF EXISTS authors");

  testDb.exec(`
    CREATE TABLE authors (...);
    CREATE TABLE books (...);
    CREATE TABLE reviews (...);
  `);
}

beforeEach(() => {
  resetDatabase();
});

Heavier but guarantees a completely fresh state — no stale indexes, no orphaned data.

Strategy 3: Transaction rollback

The most elegant approach: wrap each test in a transaction and roll it back:

import { beforeEach, afterEach } from "vitest";

beforeEach(() => {
  testDb.exec("BEGIN");
});

afterEach(() => {
  testDb.exec("ROLLBACK");
});

Every INSERT, UPDATE, DELETE within the test happens inside a transaction. After the test, ROLLBACK undoes everything — the database returns to its pre-test state. No DELETE statements needed. Very fast.

[!NOTE] The Database Design course’s Transactions lesson covered BEGIN, COMMIT, and ROLLBACK. Here, ROLLBACK is used as a cleanup mechanism — every change the test makes is undone automatically.

The limitation: if your application code uses transactions internally (e.g., db.transaction(() => { ... })), nesting transactions in SQLite requires savepoints. For most test cases, the DELETE approach is simpler.

Parallel test isolation

Vitest runs test files in parallel by default. Each file gets its own worker. If all files share the same in-memory database, they can interfere with each other.

Two solutions:

One database per file: Create the in-memory database in each test file, not in a shared setup:

// At the top of each test file
const db = new Database(":memory:");
// ... create schema, use this db

Sequential execution: Run tests sequentially with vitest --no-threads:

{
  "test": "vitest run --no-threads"
}

For API tests with a shared database, sequential execution is simpler. The tests are fast enough (in-memory SQLite) that parallelism is not necessary.

Exercises

Exercise 1: Implement DELETE-based cleanup. Run two tests that create data. Verify each starts clean.

Exercise 2: Implement transaction-based cleanup. Compare the speed to DELETE-based.

Exercise 3: Run tests in parallel. Observe test pollution. Fix it with sequential execution or per-file databases.