Testing

Bun SQLite Testing: In-Memory Fixtures, Migrations, and Query Assertions

HelpMeTest

HelpMeTest

— 6 min read
Bun SQLite Testing: In-Memory Fixtures, Migrations, and Query Assertions

Bun ships a native SQLite driver (bun:sqlite) that creates in-memory databases in microseconds. This makes it practical to spin up a fully migrated, seeded database in beforeEach, run tests against real SQL, and discard the database after — no mocking, no Docker, no external process.

Key Takeaways

In-memory databases are disposable test fixtures. new Database(":memory:") creates an isolated SQLite database that disappears when the reference is garbage-collected — ideal for beforeEach reset.

Run your real migration files against the in-memory DB. db.run(migrationSql) applies the same schema your production database uses, so tests catch schema bugs, not just query bugs.

Test transactions with rollback assertions. SQLite's synchronous transaction API lets you assert that rollback behavior works correctly inside a single test.

Foreign key enforcement must be explicitly enabled. SQLite disables foreign key checks by default — always PRAGMA foreign_keys = ON in test setup or you will miss constraint violations.

db.query().all() returns typed rows. Use TypeScript generics on db.query<RowType>() to get typed results without manual casting, making assertion code readable.

Why Test with Real SQLite

The common pattern for database tests is to mock the database layer entirely. That approach tests that your code calls the right functions — but it does not test that your SQL is correct, that your schema constraints fire, or that your migrations are idempotent. When you use bun:sqlite, you can test all three at near-zero cost because in-memory databases start in under a millisecond.

The result is tests that resemble integration tests in confidence but unit tests in speed.

Setting Up the Test Database

Install nothing. bun:sqlite is built into Bun.

import { Database } from "bun:sqlite";
import { describe, test, expect, beforeEach, afterEach } from "bun:test";
import { readFileSync } from "fs";

let db: Database;

function createTestDb(): Database {
  const db = new Database(":memory:");
  // Always enable foreign keys — SQLite disables them by default
  db.run("PRAGMA foreign_keys = ON");
  return db;
}

beforeEach(() => {
  db = createTestDb();
});

afterEach(() => {
  db.close();
});

Running Migration Files Against the Test DB

The key to testing your real schema is applying the same migration files used in production.

// src/db/migrate.ts
import { Database } from "bun:sqlite";
import { readdirSync, readFileSync } from "fs";
import { join } from "path";

export function runMigrations(db: Database, migrationsDir: string): void {
  db.run(`
    CREATE TABLE IF NOT EXISTS _migrations (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      filename TEXT NOT NULL UNIQUE,
      applied_at TEXT NOT NULL DEFAULT (datetime('now'))
    )
  `);

  const applied = new Set(
    db.query<{ filename: string }>("SELECT filename FROM _migrations")
       .all()
       .map((r) => r.filename)
  );

  const files = readdirSync(migrationsDir)
    .filter((f) => f.endsWith(".sql"))
    .sort();

  for (const file of files) {
    if (applied.has(file)) continue;
    const sql = readFileSync(join(migrationsDir, file), "utf8");
    db.transaction(() => {
      db.run(sql);
      db.run("INSERT INTO _migrations (filename) VALUES (?)", [file]);
    })();
  }
}

Test the migration runner itself:

import { Database } from "bun:sqlite";
import { test, expect } from "bun:test";
import { runMigrations } from "./migrate";
import { join } from "path";

const MIGRATIONS_DIR = join(import.meta.dir, "../migrations");

test("migrations create expected tables", () => {
  const db = new Database(":memory:");
  db.run("PRAGMA foreign_keys = ON");

  runMigrations(db, MIGRATIONS_DIR);

  const tables = db
    .query<{ name: string }>(
      "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
    )
    .all()
    .map((r) => r.name);

  expect(tables).toContain("users");
  expect(tables).toContain("posts");
  expect(tables).toContain("_migrations");

  db.close();
});

test("migrations are idempotent", () => {
  const db = new Database(":memory:");
  db.run("PRAGMA foreign_keys = ON");

  // running twice should not throw
  runMigrations(db, MIGRATIONS_DIR);
  expect(() => runMigrations(db, MIGRATIONS_DIR)).not.toThrow();

  db.close();
});

A Repository Under Test

Here is a realistic repository that the rest of the examples test against:

// src/db/userRepository.ts
import { Database } from "bun:sqlite";

export interface User {
  id: number;
  name: string;
  email: string;
  created_at: string;
}

export interface CreateUserInput {
  name: string;
  email: string;
}

export class UserRepository {
  constructor(private db: Database) {}

  create(input: CreateUserInput): User {
    const stmt = this.db.prepare(
      "INSERT INTO users (name, email) VALUES (?, ?) RETURNING *"
    );
    return stmt.get(input.name, input.email) as User;
  }

  findById(id: number): User | null {
    return this.db
      .query<User>("SELECT * FROM users WHERE id = ?")
      .get(id);
  }

  findByEmail(email: string): User | null {
    return this.db
      .query<User>("SELECT * FROM users WHERE email = ?")
      .get(email);
  }

  list(): User[] {
    return this.db.query<User>("SELECT * FROM users ORDER BY id").all();
  }

  delete(id: number): boolean {
    const result = this.db.run("DELETE FROM users WHERE id = ?", [id]);
    return result.changes > 0;
  }
}

Testing CRUD Operations

import { Database } from "bun:sqlite";
import { describe, test, expect, beforeEach, afterEach } from "bun:test";
import { runMigrations } from "./migrate";
import { UserRepository } from "./userRepository";
import { join } from "path";

const MIGRATIONS_DIR = join(import.meta.dir, "../migrations");

describe("UserRepository", () => {
  let db: Database;
  let repo: UserRepository;

  beforeEach(() => {
    db = new Database(":memory:");
    db.run("PRAGMA foreign_keys = ON");
    runMigrations(db, MIGRATIONS_DIR);
    repo = new UserRepository(db);
  });

  afterEach(() => db.close());

  test("create returns the inserted user with an id", () => {
    const user = repo.create({ name: "Alice", email: "alice@example.com" });

    expect(user.id).toBeGreaterThan(0);
    expect(user.name).toBe("Alice");
    expect(user.email).toBe("alice@example.com");
    expect(user.created_at).toBeDefined();
  });

  test("findById returns the correct user", () => {
    const created = repo.create({ name: "Bob", email: "bob@example.com" });
    const found = repo.findById(created.id);

    expect(found).toEqual(created);
  });

  test("findById returns null for missing id", () => {
    expect(repo.findById(9999)).toBeNull();
  });

  test("list returns all users in insertion order", () => {
    repo.create({ name: "Alice", email: "alice@example.com" });
    repo.create({ name: "Bob", email: "bob@example.com" });
    repo.create({ name: "Carol", email: "carol@example.com" });

    const users = repo.list();
    expect(users).toHaveLength(3);
    expect(users.map((u) => u.name)).toEqual(["Alice", "Bob", "Carol"]);
  });

  test("delete removes the user and returns true", () => {
    const user = repo.create({ name: "Alice", email: "alice@example.com" });
    expect(repo.delete(user.id)).toBe(true);
    expect(repo.findById(user.id)).toBeNull();
  });

  test("delete returns false for non-existent id", () => {
    expect(repo.delete(9999)).toBe(false);
  });
});

Testing Unique Constraints

test("create throws on duplicate email", () => {
  repo.create({ name: "Alice", email: "alice@example.com" });

  expect(() =>
    repo.create({ name: "Alice 2", email: "alice@example.com" })
  ).toThrow();
});

Testing Foreign Key Constraints

SQLite's foreign key enforcement requires the PRAGMA foreign_keys = ON to be run on every connection. This is a common source of test failures in CI environments where foreign key checks are accidentally skipped.

describe("foreign key constraints", () => {
  test("deleting a user with posts raises an error", () => {
    // Given
    const user = repo.create({ name: "Alice", email: "alice@example.com" });
    db.run("INSERT INTO posts (user_id, title) VALUES (?, ?)", [
      user.id,
      "My Post",
    ]);

    // When / Then
    expect(() => repo.delete(user.id)).toThrow();

    // The user should still exist
    expect(repo.findById(user.id)).not.toBeNull();
  });
});

Testing Transactions

import { Database } from "bun:sqlite";
import { test, expect, beforeEach, afterEach } from "bun:test";

describe("transactions", () => {
  let db: Database;

  beforeEach(() => {
    db = new Database(":memory:");
    db.run("PRAGMA foreign_keys = ON");
    db.run("CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance REAL NOT NULL)");
    db.run("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 500)");
  });

  afterEach(() => db.close());

  function transfer(from: number, to: number, amount: number): void {
    const txn = db.transaction(() => {
      const fromBalance = (db.query<{ balance: number }>(
        "SELECT balance FROM accounts WHERE id = ?"
      ).get(from))?.balance ?? 0;

      if (fromBalance < amount) throw new Error("Insufficient funds");

      db.run("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, from]);
      db.run("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, to]);
    });

    txn();
  }

  test("transfer moves balance between accounts", () => {
    transfer(1, 2, 200);

    const account1 = db.query<{ balance: number }>(
      "SELECT balance FROM accounts WHERE id = 1"
    ).get(1);
    const account2 = db.query<{ balance: number }>(
      "SELECT balance FROM accounts WHERE id = 2"
    ).get(2);

    expect(account1?.balance).toBe(800);
    expect(account2?.balance).toBe(700);
  });

  test("transfer rolls back on insufficient funds", () => {
    expect(() => transfer(2, 1, 1000)).toThrow("Insufficient funds");

    // Both balances must be unchanged after rollback
    const account2 = db.query<{ balance: number }>(
      "SELECT balance FROM accounts WHERE id = 2"
    ).get(2);
    expect(account2?.balance).toBe(500);
  });
});

Seeding Test Data

For complex test scenarios, a seed helper keeps test code readable:

// test/seed.ts
import { Database } from "bun:sqlite";
import type { User } from "../src/db/userRepository";

export function seedUsers(db: Database, count = 5): User[] {
  const users: User[] = [];
  for (let i = 1; i <= count; i++) {
    const user = db
      .query<User>("INSERT INTO users (name, email) VALUES (?, ?) RETURNING *")
      .get(`User ${i}`, `user${i}@example.com`);
    if (user) users.push(user);
  }
  return users;
}
test("pagination returns correct page", () => {
  const allUsers = seedUsers(db, 20);

  const page2 = db
    .query<User>("SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10")
    .all();

  expect(page2).toHaveLength(10);
  expect(page2[0].id).toBe(allUsers[10].id);
});

What to Test vs. What to Skip

Test:

  • Migration files — verify that each migration creates the expected tables, columns, and indexes
  • Constraint enforcement — unique, not null, foreign key, and check constraints all deserve explicit tests
  • Query correctness — filters, ordering, pagination, and joins that are easy to get wrong
  • Transaction rollback — verify that partial writes do not survive a thrown error inside a transaction
  • The migration runner itself — idempotency, ordering, and the migration tracking table

Skip:

  • SQLite internals — don't test that UNIQUE constraint works; test that your schema enforces it
  • ORM internals — if you use Drizzle or Prisma on top of bun:sqlite, don't retest the ORM; test your repository methods
  • Very large datasets — performance testing belongs in a separate benchmark, not in beforeEach fixtures
  • Disk-based databases in unit tests — always use :memory: in tests; the file-based driver adds I/O latency and state leakage between test runs

Read more