Testing a booking engine with pglite and 1600+ tests

A booking engine is mostly a database problem wearing a calendar costume. Two clients must never hold the same slot with the same stylist. A standing weekly series must land on the right instants without re-booking the anchor. One salon must never see another salon's customers. Almost every one of those rules is enforced by Postgres itself: a partial unique index, an org-scoped WHERE clause, a constraint. So the question for our test suite was simple. How do you test code whose correctness lives in the database, without mocking the database away and testing nothing?

Our answer is to run a real Postgres for every test. Not a shared dev database, not a Docker container the CI runner waits on, and not a hand-rolled fake. We run pglite, which is Postgres compiled to WebAssembly, in-memory, in the same Node process as the test. We point Drizzle at it, replay our actual migration SQL, and let the same query code that runs in production run against it. There are now more than 1600 tests built this way, and about 80 integration files spin up their own database.

Why a real Postgres instead of mocks

The interesting bugs in a booking system are not in the TypeScript. They are at the boundary. Does the partial unique index actually reject the second booking when the status is 'confirmed'? Does an org-scoped query genuinely return nothing for the wrong tenant, or does it quietly leak? You cannot answer either of those by mocking the database client, because the behaviour you care about is the database's behaviour. A mock just returns whatever you told it to, so it tests your assumptions rather than the system.

pglite gets us the real thing cheaply. It is the Postgres query planner and executor, partial indexes and all, running in WASM with no network and no daemon. A fresh database costs almost nothing, so we can afford one per file, and a fresh org per test where a suite wants full isolation. The trade-off is honest. pglite is single-connection and not byte-for-byte identical to a managed Postgres for every extension. But for schema, constraints, indexes, and standard SQL, which is where our correctness lives, it is the same engine.

Replaying the real migrations

The setup is deliberately dumb, and that is the point. We do not maintain a separate test schema. Every suite reads the same numbered migration files we apply in production, in order, and executes them against a fresh pglite instance. If a migration is wrong, the tests run against the wrong schema and fail, which is exactly what we want.

beforeAll(async () => {
  const client = new PGlite();
  const dir = path.resolve(process.cwd(), MIGRATIONS_DIR);
  const files = fs.readdirSync(dir).filter((f) => f.endsWith(".sql")).sort();
  for (const f of files) {
    await client.exec(fs.readFileSync(path.join(dir, f), "utf8"));
  }
  tdb = drizzle(client, { schema });
});

That sorted readdir is load-bearing. Migrations are numbered (0000, 0001, and so on), so a lexical sort replays them in the order they were authored. The result is a database whose schema is, by construction, the schema we ship. When we added galleries or per-service video meetings, the new migration just appeared in the folder and every suite picked it up for free.

Pointing the app's db at the test database

Our application code imports a shared db instance from one module. We do not want the tests to reach the database through a different code path, because then we would be testing a different program. So instead of refactoring every function to accept an injected client, we redirect that shared module at the in-memory instance. Vitest needs the mock hoisted above the imports, so we use a tiny holder.

const h = vi.hoisted(() => {
  let d: unknown = null;
  return { get: () => d, set: (x) => { d = x; } };
});
vi.mock("@/db", () => ({ get db() { return h.get(); } })); // the shared db module

// later, in beforeAll:
h.set(tdb);

The getter matters. The mock exposes db as a property that reads from the holder at access time, so the module can be imported before the database exists and still resolve to the live instance once beforeAll has run. From there, createBooking, getAvailability and the rest run unmodified. The only thing that changed is which Postgres they talk to.

Testing the rules that live in SQL

The double-booking guard is a good example of why this approach earns its keep. There is no application-level lock and no read-then-write check that hopes to win a race. The rule is a partial unique index:

-- conceptual: names simplified for illustration
CREATE UNIQUE INDEX "one_booking_per_staff_slot"
  ON "appointments" ("staff_id", "start_at")
  WHERE status IN ('pending','confirmed') AND staff_id IS NOT NULL;

Because the WHERE clause only covers live bookings, a cancelled appointment frees the slot automatically, and two clients can never both hold the same (staff, instant). The tests do not assert that we 'called the check'. They book a slot, try to book it again, and assert the second is rejected, with the real index doing the rejecting. A sibling index does the same for a shared room or resource. If someone weakened either WHERE clause in a migration, the conflict tests would go red immediately.

Multi-tenant isolation gets the same treatment. We seed two orgs, then prove that loading a customer by her real id but scoped to the other org returns nothing. It is a three-line test, but it is a real query against a real database, which is the only version of that test worth having.

const leaked = await db
  .select()
  .from(schema.customers)
  .where(and(eq(schema.customers.id, alice.id),
             eq(schema.customers.orgId, orgB.id)));
expect(leaked).toHaveLength(0);

Bugs come back as a test, with the story attached

Most of these tests started life as a bug report. We write the failing case first, fix it, and leave the real-world story in a comment so the next person knows why the test exists. The reschedule suite is a clear one. An admin dragging a booking onto another stylist's lane got 'Time no longer available'. The cause was that the admin action called the reschedule function without a source, so it took the strict customer-facing path and rejected any slot the engine deemed busy or out of hours. A deliberate admin move should force-book. Only a genuine same-(staff, instant) double-book may block it.

The bug: dragging a booking to another staff member's lane came back 'Time no longer available'. The fix: pass 'admin' so the move force-books like the calendar drag, blocking only a genuine same-(staff, instant) double-book.

The test then pins both halves at once. The admin action force-moves a booking to 06:00, before the 9am default open, and succeeds, while the same move on the default 'online' source still fails. That second assertion is the important one. It is easy to fix a bug by loosening a rule everywhere. Pinning the strict path in the same test stops the fix from quietly handing the admin bypass to the public.

The recurring-series tests follow the same shape. A standing weekly series must force-book each occurrence like its anchor, even out of hours on a not-yet-configured org, yet still skip an occurrence whose exact slot is already taken and report it in skippedCount. Both behaviours sit in one file, each with the scenario that motivated it written above the assertions.

Keeping behaviour stable while refactoring

Not every test asserts a single rule. The availability engine is dense (multi-staff, org-wide blocks, back-to-back bookings, cross-midnight windows, processing-time gaps, resource pools, non-UTC timezones), and we wanted to refactor it without changing what it returns. So there is a characterization test. It runs getAvailability across a representative sweep of those scenarios and snapshots the output, with the clock and the date window pinned so the result is deterministic. The snapshot is the behaviour contract. The refactor has to keep it byte-identical or explain why it changed.

This is a different use of the same setup. The unit-rule tests say 'this specific thing must be true'. The characterization test says 'whatever this currently does, keep doing it until you decide otherwise'. Both run against the real schema, so both stay honest as the database evolves.

What it costs, and what is next

The honest downsides. pglite is single-connection, so it cannot reproduce a true OS-level parallel write race. We do fire two bookings at the same slot with Promise.all and assert exactly one wins, but pglite serialises those calls, so what the test really proves is that the unique index rejects the duplicate, not that two parallel transactions race cleanly. For our shape that is the right thing to lean on: make the race structurally impossible at the index rather than hammer for it. It is still a real limitation worth naming. There is also a small per-file cost to standing up a database and replaying every migration, which grows as the migration list grows. So far it sits comfortably inside our 30-second per-test timeout, and the payoff is worth it: no Docker, no shared state, and it runs identically on a laptop and in CI.

The next step is squeezing the setup cost. Replaying all migrations per file is simple and correct, but as the schema grows we will likely cache a migrated database snapshot and clone it per suite. The principle will not change. Tests run against the same Postgres and the same migrations we ship, because the bugs that matter in a booking engine are the ones only the database can catch.