Using an In-Memory SQLite for Repository Tests

When the thing under test is the query itself — a filter, a join, a unique constraint — a mock proves nothing, because it returns only what you told it to. This guide is for engineers on Vitest 1.x who want their repository layer executed against a real SQL engine that is fast enough to run on every commit: an in-memory SQLite database via better-sqlite3. It sits in the database and ORM mocking topic area and deliberately takes the low seam, the complement to mocking the Prisma client in Vitest. We cover schema setup, transaction rollback per test for hermetic isolation, and per-worker connections so a fully parallel pipeline never crosses streams.

Root Cause Analysis

Repository tests exist in an awkward middle. Mock the client and you never execute the SQL, so a broken WHERE clause or a missing index sails through green. Point them at a shared Postgres and they become slow, order-dependent, and flaky under parallelism because tests see each other’s committed rows. The root cause of both failures is the same: the test’s isolation boundary is in the wrong place. You want real query execution but a private, disposable world for each test.

In-memory SQLite resolves this because the entire database lives in the process’s heap and is created and destroyed with the connection. There is no port, no disk, no shared server — opening :memory: gives you a brand-new engine that real SQL runs against at in-process speed. The remaining risk is dialect divergence: SQLite is more permissive than Postgres about types and, unless told otherwise, about foreign keys. That means a constraint you believe you are testing may not be enforced. The mitigation is to enable the strict pragmas up front and to keep genuinely dialect-specific queries at a throwaway-Postgres tier, a boundary the topic area draws deliberately. Within schema-portable territory, SQLite gives you real execution at mock-like cost.

The reason this tier is worth building at all, rather than mocking the repository, is that the failures it catches are invisible to a mock. A mock returns whatever you told it, so a WHERE clause that filters on the wrong column, an ORDER BY that sorts the wrong direction, a join that drops rows, or a unique index that was never created will all pass a mocked test and fail in production. Running the actual SQL against a real engine turns each of those into a test failure at the exact line that wrote the query. The cost of that fidelity is small because the engine is in-process, which is what makes SQLite the sweet spot for the repository layer: enough realism to catch query bugs, cheap enough to run on every commit.

Reproducible Setup

Install Vitest and the synchronous SQLite driver, which is the fastest option for tests because it avoids async overhead. better-sqlite3 runs queries synchronously in the same thread, which is ideal for tests: there is no event-loop scheduling to reason about, statements execute in order, and assertions read back immediately without awaiting, so the tests read almost like plain data structure manipulation while still exercising a real SQL engine.

npm install -D vitest better-sqlite3 @types/better-sqlite3

Create a factory that returns a fresh, fully-migrated in-memory database, with the strict pragmas enabled so constraints behave like a real engine.

// src/db/test-db.ts
import Database from 'better-sqlite3';

export function makeTestDb() {
  const db = new Database(':memory:');
  db.pragma('foreign_keys = ON'); // SQLite enforces FKs only when asked
  db.exec(`
    CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
    CREATE TABLE book (
      id INTEGER PRIMARY KEY,
      author_id INTEGER NOT NULL REFERENCES author(id),
      title TEXT NOT NULL
    );
  `);
  return db;
}

// src/repos/book-repo.ts — the system under test
import type BetterSqlite3 from 'better-sqlite3';

export function booksByAuthor(db: BetterSqlite3.Database, authorId: number) {
  return db
    .prepare('SELECT title FROM book WHERE author_id = ? ORDER BY title')
    .all(authorId);
}

Implementation

Step 1 — Give each test file a fresh database in beforeEach. Because an in-memory database is cheap to build, the simplest hermetic strategy is to recreate it per test. State cannot leak because the old engine is discarded.

// src/repos/book-repo.test.ts
import { beforeEach, afterEach, describe, it, expect } from 'vitest';
import Database from 'better-sqlite3';
import { makeTestDb } from '../db/test-db';
import { booksByAuthor } from './book-repo';

let db: Database.Database;

beforeEach(() => { db = makeTestDb(); });
afterEach(() => { db.close(); }); // always close, or handles accumulate across the run

Step 2 — Seed inside the test, and query through the code path under test. Insert the minimal rows the case needs, then call the repository function so the real SQL runs, and assert on what it returns.

it('returns an author\'s books in title order', () => {
  db.prepare('INSERT INTO author (id, name) VALUES (1, ?)').run('Ada');
  db.prepare('INSERT INTO book (author_id, title) VALUES (1, ?), (1, ?)').run('Zeta', 'Alpha');

  const titles = booksByAuthor(db, 1).map((r: any) => r.title);

  expect(titles).toEqual(['Alpha', 'Zeta']); // proves the ORDER BY actually runs
});

Step 3 — Prefer transaction rollback when setup is expensive. If your schema and seed data are large, rebuilding per test is wasteful. Build once per file, then wrap each test in a savepoint and roll it back, so the seed persists but every test’s writes vanish.

import { beforeAll, beforeEach, afterEach, afterAll } from 'vitest';

let db: Database.Database;
beforeAll(() => { db = makeTestDb(); /* seed shared fixtures here */ });
beforeEach(() => { db.exec('SAVEPOINT t'); });
afterEach(() => { db.exec('ROLLBACK TO t'); });
afterAll(() => { db.close(); });

Step 4 — Assert the constraints, not just the happy path. The reason to run real SQL is to catch what a mock cannot. Prove the unique index and the foreign key actually reject bad writes.

it('rejects a duplicate author name', () => {
  db.prepare('INSERT INTO author (id, name) VALUES (1, ?)').run('Ada');
  expect(() =>
    db.prepare('INSERT INTO author (id, name) VALUES (2, ?)').run('Ada'),
  ).toThrow(/UNIQUE/);
});

it('rejects a book with a missing author', () => {
  expect(() =>
    db.prepare('INSERT INTO book (author_id, title) VALUES (99, ?)').run('Orphan'),
  ).toThrow(/FOREIGN KEY/);
});

Step 5 — Isolate connections per worker for parallel CI. Each :memory: database is already private to its connection, so distinct workers are naturally isolated. The pitfall is a shared-cache URL that lets workers collide; keep the default private :memory: or key the name to the worker id.

const workerId = process.env.VITEST_WORKER_ID ?? '0';
const db = new Database(`file:memdb-${workerId}?mode=memory`); // one heap per worker

Verification

Confirm the tests execute real SQL by breaking the query on purpose — change the ORDER BY to DESC in the repository and watch the ordering assertion fail. A mock would not have noticed. Then run the file in isolation and inside the full suite, and confirm identical results, which proves the rollback or fresh-database strategy is truly hermetic and order-independent. Finally check that no handles leak: an in-memory suite should leave zero open databases after the run.

npx vitest run src/repos/book-repo.test.ts
npx vitest run --pool=threads   # confirm the same results under parallel workers

Identical pass counts single-threaded and parallel, plus a failing assertion when you sabotage the query, together prove the seam is genuinely testing SQL and staying isolated.

The decision of when to trust SQLite and when to escalate to a real Postgres is worth making explicit rather than case by case. The tree below encodes it: if the query uses only portable SQL and portable constraints, SQLite is faithful and fast; the moment it leans on a Postgres-only type, function, or locking behavior, the test moves to a disposable real instance.

Troubleshooting

Symptom: foreign key violations are never thrown. Diagnosis: SQLite enforces foreign keys only when PRAGMA foreign_keys = ON is set on the connection, and the pragma resets per connection. Fix: enable it in the factory immediately after opening the database, before any statement runs.

Symptom: tests pass alone but fail in the suite. Diagnosis: state is leaking because a shared database is not being reset — either the savepoint is not rolled back or a fresh database is not created per test. Fix: put the rollback in afterEach unconditionally, or recreate the database in beforeEach, and never share a mutable connection across files.

Symptom: a query passes on SQLite but fails on production Postgres. Diagnosis: dialect divergence — SQLite accepted a type coercion or default that Postgres rejects. Fix: move that specific query to a throwaway real-Postgres tier; keep SQLite for schema-portable queries where the behavior is identical across engines.

FAQ

Related

• Back to Database & ORM Mocking
• Mocking Prisma client in Vitest — the high seam for when the query is not the subject.
• Isolating MongoDB with mongodb-memory-server — the same real-engine approach for document stores.
• When to skip integration tests in favor of unit tests — decide when a real-engine test is worth its cost.