Add database integration tests

The handler tests from the previous lesson use an in-memory repository. They prove the routes work and nothing about the SQL. A typo in a column name, a CHECK constraint stricter than the Zod schema, a migration that fails on an empty database: none of that shows up until the real thing runs.

A mock can only tell you that your code called a method with some arguments. It can’t tell you the database agreed. So for the data layer I don’t mock. I run the real schema and queries against a real SQLite file, a temporary one that each test creates and throws away.

One database per test

SQLite makes this cheap. A database is a file, so a fresh database is a fresh file in the OS temp directory:

import test from 'node:test'
import assert from 'node:assert/strict'
import { mkdtempSync, rmSync } from 'node:fs'
import { join } from 'node:path'
import { tmpdir } from 'node:os'
import { DatabaseSync } from 'node:sqlite'
import { migrate } from './migrate.js'
import { createApp } from './app.js'

test('creates and reads a book through SQLite', async () => {
  const dir = mkdtempSync(join(tmpdir(), 'books-'))
  const db = new DatabaseSync(join(dir, 'books.db'))
  migrate(db)
  const app = createApp(db)

  const created = await app.request('/books', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ title: 'Dune', author: 'Frank Herbert' })
  })
  assert.equal(created.status, 201)

  const row = db.prepare('SELECT title FROM books').get()
  assert.equal(row.title, 'Dune')

  db.close()
  rmSync(dir, { recursive: true })
})

Three things to notice. The test calls migrate(db), the same function production uses, not a hand-written CREATE TABLE for tests. It asserts on the row in the table, not only on the 201, because a 201 with nothing persisted is exactly the bug we’re hunting. And it closes the connection before deleting the directory, or the cleanup fails on some platforms with a busy file.

Use production code paths

The temptation is to write a smaller schema for tests, because the real migrations feel heavy. Resist it. If tests run a different schema, a broken migration passes every test and fails on deploy.

Never point tests at a development database either. Let each test create its own file. A test suite that wipes your local data once teaches this lesson better than I can.

What to cover

The happy path is the least interesting test. Add these:

• a constraint violation, like an empty title inserted through the repository, and the 422 it becomes
• a migration run from an empty database, so a fresh checkout works
• a multi-statement write that fails halfway, and a check that the transaction rolled back and nothing partial remains
• two writes in a row against the same book, if your API has any operation where order matters

Each one asserts persisted state after the response, not just the response.

Make failures readable

When one of these fails, you want to know which layer broke: the HTTP mapping, the SQL, the schema, a locked file, or the cleanup. Print the problem body on an unexpected status, and let SQLite errors surface with their message in the test output.

Run create, read, update and delete through the app against a temporary database now. Then rename a column in the migration on purpose, and watch this test catch it while the handler tests stay green.