Test databases, fixtures and isolation

Picture a suite where every test hits the same Postgres database. Test A creates a user with the email alex@example.com. Test B, written six months later by someone who never saw test A, tries to register the same email and gets a 409 it wasn't expecting. Nothing in test B's code is wrong. It just ran after test A left something behind. This is the single most common source of flaky test suites, and it has nothing to do with async code or bad assertions. It's shared state.

The fix: a database that starts empty every time

Linkstash's db.ts opens with one default that quietly solves the whole problem:

/** Opens a database and ensures the schema exists. Defaults to in-memory, which
 *  is what the tests use: every test gets a pristine database with no cleanup. */
export function openDb(file = ":memory:") {
  const db = new Database(file);
  db.pragma("journal_mode = WAL");
  db.pragma("foreign_keys = ON");
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id           INTEGER PRIMARY KEY AUTOINCREMENT,
      email        TEXT    NOT NULL UNIQUE,
      passwordHash TEXT    NOT NULL,
      createdAt    TEXT    NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS links (
      id        INTEGER PRIMARY KEY AUTOINCREMENT,
      userId    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      url       TEXT    NOT NULL,
      title     TEXT    NOT NULL,
      createdAt TEXT    NOT NULL
    );
    CREATE INDEX IF NOT EXISTS links_user_created ON links (userId, id DESC);
  `);
  return db;
}

Call openDb() with no argument and you get a fresh SQLite database that lives entirely in memory, schema and all, gone the moment the process exits. No file on disk, no leftover rows, nothing to clean up between test runs because there's nothing to clean. Combine that with the app factory from the last lesson:

describe("Linkstash API", () => {
  let db: Database;
  let app: Express;
  beforeEach(() => { db = openDb(); app = createApp(db); });
  // ...
});

Every single test gets its own database and its own app, built fresh in beforeEach. Two tests could run in parallel, in any order, a thousand times, and neither would ever see the other's data. That's the entire trick behind fixing the flaky-suite problem from the intro: don't clean up shared state carefully, just never share it.

Fixtures: reusable setup, not copy-pasted setup

A fixture is just a helper that builds the data a test needs, so you're not repeating the same five lines at the top of every it(). app.test.ts defines one at the top of the file:

async function registerAndLogin(app: Express, email: string) {
  await request(app).post("/auth/register").send({ email, password: "correct horse battery" });
  const res = await request(app).post("/auth/login").send({ email, password: "correct horse battery" });
  return res.body.token as string;
}

Every test that needs an authenticated user calls registerAndLogin(app, "alex@example.com") and gets back a real token, issued through the real registration and login routes, not a token constructed by hand to fake the shape of a real one. That matters: if the login route ever changes what a token looks like, every test using this fixture picks up the change automatically, instead of forty tests failing because they were all forging tokens the old way.

Fixtures look different at different layers

Compare that to links.test.ts, which tests the links module directly instead of going through HTTP:

beforeEach(() => {
  db = openDb();
  db.prepare("INSERT INTO users (id, email, passwordHash) VALUES (1, 'alex@example.com', 'x')").run();
  db.prepare("INSERT INTO users (id, email, passwordHash) VALUES (2, 'maya@example.com', 'x')").run();
});

No registration flow, no password hashing, no HTTP at all. Just two rows inserted directly, because at this layer the test only cares that createLink, listLinks, and deleteLink behave correctly given some userId, not how a user came to exist. Using registerAndLogin here would be slower for no benefit and would couple a database-layer test to the auth system for no reason. Pick the fixture that matches what the test is actually about: a full HTTP fixture for tests that exercise the whole stack, a direct insert for tests that don't need to.

Previous: integration-testing Linkstash. Next: code coverage and the lies it tells, where a fully green, fully isolated suite still ships a real bug.