Isolate database tests

Database tests go flaky for three reasons. They share rows, they depend on a test that ran before them, or they leave a transaction open. All three break the moment you run tests in a different order or in parallel.

Isolation fixes this. Each test can run first, last, alone, or next to any other test and get the same result.

One database per test

Our Books API uses SQLite in development, so the simplest isolation is a fresh database file for each test. Create a temporary path, open it, apply the real migrations, and register cleanup right away:

test('stores and reads back a book', async t => {
  const dir = await mkdtemp(join(tmpdir(), 'books-'))
  const db = openDatabase(join(dir, 'books.db'))
  t.after(async () => {
    db.close()
    await rm(dir, { recursive: true })
  })

  await migrate(db)
  const books = new SqliteBooks(db)

  await books.insert({ title: 'Dune', author: 'Frank Herbert' })
  assert.equal((await books.findByTitle('Dune')).author, 'Frank Herbert')
})

Notice the t.after() call comes before anything that can fail. If the assertion throws, cleanup still runs.

Notice also that migrate() runs the same migration files production uses. Creating tables with test-only SQL would hide a broken production migration behind a green test.

Transactions versus fresh databases

Another common approach wraps each test in a transaction and rolls it back at the end. It’s fast. But it has a boundary.

If the code under test opens a second connection, commits on its own, or does work after the request returns, the outer rollback won’t contain those writes. A fresh database is slower and catches all of that. I pick based on what the test needs to prove.

Seed only what the test needs

Insert the rows the scenario names, and nothing else. A big shared fixture makes uniqueness and ordering bugs hard to read: which of the fifty books collided?

Avoid fixed IDs across parallel tests unless every test has its own database. And close statements and connections before deleting the file, or the delete fails on some platforms.

Read it back for real

A CRUD test only proves persistence if it reads persisted state. Checking the object insert() returned can pass without a single write hitting the disk. Create through one call, then read through a separate query, or a separate HTTP request, that goes to the database.

Try this: run the full Books API CRUD sequence against a fresh temporary database. Then run it twice in parallel with different titles, and prove neither test can see the other one’s rows.