Testing

Run each test in a test transaction, which always rolls back, so that tests leave the data in the database unchanged. Test transactions need a database created with test: true:

export const database = createDatabase({
  driver: postgresJsDriver(postgres(process.env.DATABASE_URL!)),
  test: process.env.NODE_ENV === "test",
})

Create the test helpers of the database once, in a module of your tests, with the hooks of your test runner:

// test/helpers.ts
import { after, before } from "node:test"

import { createTestHelpers } from "kern-orm/testing"

export const { testTransaction, useTestFixture } = createTestHelpers(database, {
  before,
  after,
})

With bun:test and Vitest, pass { before: beforeAll, after: afterAll }.

Wrap the body of every test in testTransaction, the same way with node:test, bun:test, and Vitest:

import assert from "node:assert/strict"
import { test } from "node:test"

test("publishes a form", () =>
  testTransaction(async () => {
    await publishForm(formId)
    const form = await db.form.find(formId).select("published")
    assert.deepEqual(form, { published: true })
  }))

With Vitest, aroundEach in a setup file runs every test in a test transaction together with its hooks, so tests need no wrapper and beforeEach can create data for each test:

// vitest.setup.ts, listed in setupFiles of the Vitest config
import { aroundEach } from "vitest"

aroundEach(runTest => testTransaction(runTest))

Transactions of the tested code run as nested transactions of the test transaction. When the tested code starts a transaction at an isolation level, pass the same isolation to testTransaction, or to useTestFixture when the test runs in a fixture, since a nested transaction runs at the level of its transaction:

test("assigns the next position", () =>
  testTransaction(
    async () => {
      await addField(formId, "Name")
      // ...
    },
    { isolation: "serializable" },
  ))

A test transaction does not count as a transaction for $isInTransaction() and $ensureTransaction, so that the tested code behaves as in production. To test how the code behaves inside a transaction, wrap the call in $transaction in the test.

Such a database rejects queries and transactions outside test transactions and the seeds of fixtures. Queries of a test that keep running after it times out never run in the transaction of another test.

Rolling back does not reset PostgreSQL sequences, so the IDs a test generates may differ between runs: compare them with the records the test creates, not with literal values.

Fixtures

To prepare the database once for several tests, such as with migrations or shared data, run the preparation in a test fixture. The fixture keeps its changes for the tests that run while it is open, returns the result of its callback as its data, and rolls back its changes after the tests.

A fixture that opens while another one is open nests in it, and tests run in the innermost one.

Open a fixture for the tests of a test file or of a describe block with useTestFixture:

import { describe, test } from "node:test"

describe("forms of an author", () => {
  const fixture = useTestFixture(async () => ({ author: await createAuthor() }))

  test("lists the forms of an author", () =>
    testTransaction(async () => {
      await createForm({ authorId: fixture.data.author.id })
      // ...
    }))
})

Put a fixture for all tests, such as one that applies migrations, into a setup module:

// test/setup.ts
import { migrate } from "kern-migrations"

useTestFixture(() => migrate(db, migrations))

On a database whose transactions default to repeatable read or serializable, pass { isolation: "read committed" } to such a fixture: migrate rejects in a transaction at a stricter level when it has migrations to apply.

Run the statements of the preparation through the database, such as the SQL of migrations with db.$script, so that the fixture rolls them back: a tool that connects to PostgreSQL on its own commits its changes.

Each test runner loads the setup module in its own way:

• node:test loads it with NODE_ENV=test node --import ./test/setup.ts --test "test/**/*.test.ts"; unlike Bun and Vitest, Node.js does not set NODE_ENV, and the pattern keeps the setup module from running as a test file. It runs each test file in its own process by default, so the setup module runs once for each test file. With --test-isolation=none, all test files load before their tests run, so call useTestFixture inside a describe that holds the tests of a file.
• bun:test loads it as a preload, such as preload = ["./test/setup.ts"] in the [test] section of bunfig.toml. It runs all test files in one process, one after another, so the setup module runs once for the whole run.
• Vitest loads it as a setup file in setupFiles. It runs the setup files once for each test file.

Test files that run in parallel processes, as node:test and Vitest run them by default, each have their own fixtures, and fixtures that create or alter the same tables, such as migrations, make them run one after another.

While a fixture is open, tests run one at a time: a test transaction that starts while another one still runs, such as one that timed out, rolls the other one back.

Tests that time out

To roll back the transaction of a test as soon as the test runner gives up on the test, such as when it times out, pass the abort signal of the test to testTransaction:

test("publishes a form", ({ signal }) =>
  testTransaction(
    async () => {
      await publishForm(formId)
      // ...
    },
    { signal },
  ))

With Vitest, pass the signal of the test context in aroundEach:

aroundEach((runTest, { signal }) => testTransaction(runTest, { signal }))

When the signal aborts, the later queries of the test fail, and its transaction rolls back without waiting for the test to finish. Without a signal, as with bun:test, which gives none for a test, the transaction of a test that times out stays open until the code of the test finishes or, while a fixture is open, until the next test starts.

A query that the test has already sent runs to its end before the rollback. To limit how long such a query delays the next test, set statement_timeout for the connections of the test database.

Queries that wait for a lock

To check that a query waits for a lock that another transaction holds, such as of a row that it updates, read the backend PID of its transaction with getBackendPid. Then pass the PID to waitForLock, which resolves once the backend waits for a lock:

import { getBackendPid, waitForLock } from "kern-orm/testing"

// Simplified example: a failure before the backend PID is read leaves the lock held.
// Another transaction locks the form until the test unlocks it.
const locked = Promise.withResolvers<void>()
const unlock = Promise.withResolvers<void>()
const locking = db.$transaction(async () => {
  await db.form.find(formId).forUpdate()
  locked.resolve()
  await unlock.promise
})
await locked.promise

const backend = Promise.withResolvers<number>()
const publishing = db.$transaction(async () => {
  backend.resolve(await getBackendPid(db.$database))
  await db.form.find(formId).update({ published: true })
})
try {
  await waitForLock(db.$database, await backend.promise)
} finally {
  unlock.resolve()
  await Promise.all([locking, publishing])
}

Run such tests on a database created without test: true, since their transactions run beside each other.

When the code under test runs the query on a connection that the test cannot reach, wait for it by a part of its SQL with waitForLockedStatement, which resolves to its backend PID. Both reject when nothing waits within 2 seconds; pass timeout to wait longer or shorter.

Test migrations

To check that every migration applies and rolls back, and how migrations change existing rows, see Test migrations.