Testing Business Logic

Testing functions that use the database

Pure functions and schemas have no dependencies. But database functions — getAllBooks, createBook, getBookById — need a database. These are still unit tests (testing one function) but they require setup.

The test database

The Project Setup lesson created an in-memory test database. Now we use it:

// tests/setup.ts
import Database from "better-sqlite3";
import { beforeEach } from "vitest";

export const testDb = new Database(":memory:");
testDb.pragma("foreign_keys = ON");

testDb.exec(`
  CREATE TABLE authors (
    id TEXT PRIMARY KEY, name TEXT NOT NULL, bio TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  );
  CREATE TABLE books (
    id TEXT PRIMARY KEY, title TEXT NOT NULL, author_id TEXT NOT NULL,
    genre TEXT NOT NULL, description TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (author_id) REFERENCES authors(id)
  );
  CREATE TABLE reviews (
    id TEXT PRIMARY KEY, book_id TEXT NOT NULL, user_id TEXT NOT NULL,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5), body TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (book_id) REFERENCES books(id)
  );
`);

Injecting the test database

Your production code imports db from src/db.ts. Tests need to replace it with the test database. The simplest approach: make query functions accept a database parameter.

// src/shared/queries.ts
import type Database from "better-sqlite3";
import prodDb from "./db.js";

export function getAllBooks(db: Database.Database = prodDb): BookRow[] {
  return db.prepare("SELECT ...").all() as BookRow[];
}

export function getBookById(id: string, db: Database.Database = prodDb): BookRow | null {
  return (db.prepare("SELECT ... WHERE books.id = ?").get(id) as BookRow) ?? null;
}

export function createBook(input: CreateBookInput, db: Database.Database = prodDb): BookRow {
  const id = crypto.randomUUID();
  db.prepare("INSERT INTO books ...").run(
    id,
    input.title,
    input.authorId,
    input.genre,
    input.description ?? null,
  );
  return getBookById(id, db)!;
}

Production code calls getAllBooks() (uses default prodDb). Tests call getAllBooks(testDb) (uses the test database).

Setup and teardown

Each test should start with a known state. Use beforeEach to reset the database:

// tests/unit/queries.test.ts
import { describe, test, expect, beforeEach } from "vitest";
import { testDb } from "../setup.js";
import { getAllBooks, getBookById, createBook } from "../../src/shared/queries.js";

beforeEach(() => {
  // Clear all data
  testDb.exec("DELETE FROM reviews");
  testDb.exec("DELETE FROM books");
  testDb.exec("DELETE FROM authors");

  // Seed with known data
  testDb.prepare("INSERT INTO authors (id, name) VALUES (?, ?)").run("author-1", "Octavia Butler");
  testDb
    .prepare("INSERT INTO books (id, title, author_id, genre) VALUES (?, ?, ?, ?)")
    .run("book-1", "Kindred", "author-1", "science-fiction");
});

beforeEach runs before every test in this file. Each test starts with one author and one book — a known, predictable state.

Testing queries

describe("getAllBooks", () => {
  test("returns all books with author names", () => {
    const books = getAllBooks(testDb);
    expect(books).toHaveLength(1);
    expect(books[0].title).toBe("Kindred");
    expect(books[0].author_name).toBe("Octavia Butler");
  });

  test("returns empty array when no books exist", () => {
    testDb.exec("DELETE FROM books");
    const books = getAllBooks(testDb);
    expect(books).toHaveLength(0);
  });
});

describe("getBookById", () => {
  test("returns book when found", () => {
    const book = getBookById("book-1", testDb);
    expect(book).not.toBeNull();
    expect(book!.title).toBe("Kindred");
  });

  test("returns null when not found", () => {
    const book = getBookById("nonexistent", testDb);
    expect(book).toBeNull();
  });
});

describe("createBook", () => {
  test("creates a book and returns it", () => {
    const book = createBook(
      {
        title: "Parable of the Sower",
        authorId: "author-1",
        genre: "science-fiction",
      },
      testDb,
    );

    expect(book.title).toBe("Parable of the Sower");
    expect(book.author_name).toBe("Octavia Butler");
    expect(book.id).toBeDefined();
  });

  test("book appears in getAllBooks after creation", () => {
    createBook({ title: "New Book", authorId: "author-1", genre: "fiction" }, testDb);
    const books = getAllBooks(testDb);
    expect(books).toHaveLength(2);
  });
});

Exercises

Exercise 1: Write tests for getAllBooks with the test database. Test with 0, 1, and 3 books.

Exercise 2: Write tests for createBook. Verify the returned book has all expected fields.

Exercise 3: Add a beforeEach that resets and seeds the database. Verify each test starts with the same state.

Why should each test start with a clean, known database state?