Testing tenant isolation without a database server

In a multi-tenant app, the dangerous mistake is a query that forgets which company it's for. With one company in the development database, that query returns exactly the right rows. Nothing looks wrong until a second company signs up and starts seeing the first one's customers.

So a test for it needs at least two companies, and it has to run the query that's actually shipped.

The usual unit-test move is to replace the database with a mock that returns prepared rows. Here that tests nothing useful. The bug lives in the SQL, in a missing organization_id = ?, and a mock never runs the SQL. It returns whatever the test told it to, filter or no filter.

The other extreme is a real database server in every test run. It works, but it's slow to start and one more thing to install. Cloudflare D1 is SQLite underneath, though, and Node has shipped its own SQLite as node:sqlite since version 22.

The app's data code only uses a few D1 methods: prepare, bind, first, all, run and batch. So the stand-in implements just those on top of an in-memory DatabaseSync. Shortened:

export function createD1Adapter(database: DatabaseSync): D1Database {
  const statement = (sql: string, values: SQLInputValue[] = []) => ({
    bind: (...next: unknown[]) => statement(sql, next.map(toSqlite)),
    first: async () => database.prepare(sql).get(...values) ?? null,
    all: async () => ({ results: database.prepare(sql).all(...values) }),
    run: async () => database.prepare(sql).run(...values),
  });
  return {
    prepare: (sql: string) => statement(sql),
    batch: async (statements) => {
      database.exec("BEGIN");
      try {
        const results = [];
        for (const s of statements) results.push(await s.run());
        database.exec("COMMIT");
        return results;
      } catch (error) {
        database.exec("ROLLBACK");
        throw error;
      }
    },
  };
}

batch matters most. D1 runs a batch as one transaction, and the app relies on that when it saves a workspace. If one statement fails, none of them should stick. Wrapping the loop in BEGIN and ROLLBACK gives the tests the same guarantee.

One line is easy to forget: PRAGMA foreign_keys = ON. SQLite doesn't enforce foreign keys unless you ask, and the composite keys that tie every record to its company are half of what's being tested.

Each test builds the schema from the same code the app uses, creates two companies with one owner each, and saves through the real repository functions. The sharpest case gives both companies a customer with the same ID:

await saveWorkspace(db, "org-a", workspaceFor("Installer A", "shared-customer-id", "Customer A"), now);
await saveWorkspace(db, "org-b", workspaceFor("Installer B", "shared-customer-id", "Customer B"), now);

const stateA = await loadWorkspace(db, "org-a");
const stateB = await loadWorkspace(db, "org-b");

expect(stateA?.customers.map((c) => c.name)).toEqual(["Customer A"]);
expect(stateB?.customers.map((c) => c.name)).toEqual(["Customer B"]);

If any query looked a customer up by ID alone, one of those lists would hold the wrong name, or the second save would overwrite the first. The same pattern covers production logs, PDF templates, email history and shared quotes.

Once the real SQL runs in a test, other bugs that only show up in the database become testable too. Every save carries the revision it started from. To check that, a test copies one workspace into two “tabs”, saves tab A, then saves the now-stale tab B:

await expect(
  saveWorkspace(db, "org-a", tabB, now, { expectedRevision: first.revision, … }),
).rejects.toBeInstanceOf(WorkspaceConflictError);

expect((await loadWorkspace(db, "org-a"))?.customers[0].name).toBe("Saved from tab A");

The second assertion is the important one. Rejecting the stale save isn't enough. Tab A's edit has to still be there afterwards.

The stand-in is not D1. It runs on a different SQLite build, in Node rather than in a Worker, and it has none of D1's limits. It proves the queries are right, not that the deployed database behaves the same in every detail. A Playwright test against the running app covers that end, and the two kinds of test check different things.

What it buys is speed. The whole suite, all 171 tests, runs in about three seconds with nothing to install beyond the project's own dependencies. A test that runs on every change gets run, so it actually catches the bug.