Test real queries in isolation

A mocked database client can tell you that insert was called with certain arguments. It cannot tell you that the migration created the column, that the foreign key fires, or that your where clause matches the rows you think it matches. For database code, I test against a real database. With SQLite that’s cheap: a fresh file per test, created in milliseconds.

A fresh database per test

The trick is a helper that creates a temporary file, runs the real migrations on it, and returns a client:

import fs from 'node:fs'
import os from 'node:os'
import path from 'node:path'
import { drizzle } from 'drizzle-orm/node-sqlite'
import { migrate } from 'drizzle-orm/node-sqlite/migrator'

function freshDb() {
  const file = path.join(os.tmpdir(), `notes-${process.pid}-${Date.now()}.sqlite`)
  const db = drizzle(file)
  migrate(db, { migrationsFolder: './drizzle' })
  return { db, file }
}

migrate() from the driver’s migrator module applies the same SQL files drizzle-kit migrate does. So the test runs against exactly the schema production will have, including the constraints and the index. The process ID and timestamp in the filename keep two test runs from colliding.

The test

Now a test with Node’s built-in runner, in test/notes.test.ts:

import assert from 'node:assert/strict'
import test from 'node:test'
import { notes, users } from '../src/db/schema'

test('a note needs an existing author', async () => {
  const { db, file } = freshDb()

  const [user] = await db
    .insert(users)
    .values({ email: '[email protected]', name: 'Flavio' })
    .returning()

  const [note] = await db
    .insert(notes)
    .values({ authorId: user.id, title: 'First note' })
    .returning()
  assert.equal(note.authorId, user.id)

  await assert.rejects(
    db.insert(notes).values({ authorId: 999, title: 'Orphan' }),
    (error: { cause?: Error }) =>
      /FOREIGN KEY constraint failed/.test(error.cause?.message ?? ''),
  )

  db.$client.close()
  fs.rmSync(file)
})

Run it with npx tsx --test test/notes.test.ts:

✔ a note needs an existing author (12.08ms)
ℹ tests 1
ℹ pass 1
ℹ fail 0

Notice the rejection check looks at error.cause. Drizzle wraps driver errors in a DrizzleQueryError whose message shows the failed SQL. SQLite’s own message, the one we care about, is on the cause.

At the end we close the connection and delete the file. Run two copies of the test at the same time: they pass, and no file is left in the temp folder. Independent tests mean order never changes the result.

Test the promises the schema makes

A green “insert works” test proves little. The valuable tests are the failures the schema is supposed to produce:

• a duplicate email is rejected
• a note with an unknown author is rejected
• an update by the wrong user returns undefined and changes nothing
• a failed transaction leaves no partial rows

Each of these is a rule you decided on in an earlier lesson. Each one takes ten lines to lock in.

File or memory

drizzle(':memory:') gives you an in-memory database, faster still. I use a file anyway. Some settings, like journal mode, behave differently in memory, and I want the test database to look like the real one. If you choose memory for speed, write down why, so nobody later wonders about the difference.