Backend Database Testing: Fixtures, Isolation, and Real Queries

Backend database testing starts with a small dataset whose expected behavior you can explain.

Suppose a repository returns paid orders for one tenant and source. A useful test database should contain matching orders, orders from another tenant, records from another source, and orders exactly on the reporting boundaries.

Each record has a purpose. Together, they let the test distinguish a correct query from one that returns plausible results while silently dropping a filter.

The same approach applies to writes: define the state before the operation, perform the change, and inspect what was actually persisted. A successful function call is one piece of evidence. The resulting database state is another.

I find database tests most useful when the expected answer is easy to describe before the implementation runs. That makes a failure easier to investigate: a particular boundary, persistence rule, or transaction guarantee changed.

A test should use a real database when the behavior depends on the database’s execution rules. A mocked repository cannot establish whether the deployed schema rejects a duplicate business key, whether a query excludes another tenant, or whether a failed transaction leaves a partial write behind.

Other rules can be checked without starting PostgreSQL. Date-range validation, input normalization, and calculations over already-loaded records usually have a smaller test boundary.

The distinction matters when reading the results. A repository test can prove that a particular query returns the expected rows from its fixture. It does not establish that an HTTP handler derives the tenant from authentication or checks source permissions correctly. Those decisions need tests at the application boundary too.

For this article, the query receives one already-authorized tenant and source. The reporting period uses payment time, with an inclusive start and exclusive end. The sum includes paid orders in TRY minor units. Database Scope Is a Product Contract explains how to establish that scope before querying.

This is a deliberately small contract. Keeping it explicit lets the database tests challenge one operation without turning every test into an entire user journey.

Testcontainers can start PostgreSQL for a test and provide its connection details. It needs a supported container runtime in the local or CI environment; having the Node package installed alone is insufficient. The official PostgreSQL module and container runtime documentation describe that setup.

In the backend project that owns the tests, the relevant packages are:

npm install pg
npm install --save-dev @testcontainers/postgresql @types/pg

Start the database, run the application’s real migration path, execute the test, and close the resources. The following helper uses a fresh container for each invocation. runMigrations is an application-owned helper that applies the same migrations the service uses, through the supplied connection.

import { PostgreSqlContainer } from "@testcontainers/postgresql"
import { Client } from "pg"
import { runMigrations } from "../src/db/migrations.js"

async function withTestDatabase(
  check: (db: Client, connectionString: string) => Promise<void>,
) {
  const container = await new PostgreSqlContainer("postgres:18")
    .withDatabase("orders_test")
    .start()

  const connectionString = container.getConnectionUri()
  const db = new Client({ connectionString })

  try {
    await db.connect()
    await runMigrations(db)
    await check(db, connectionString)
  } finally {
    try {
      await db.end()
    } finally {
      await container.stop()
    }
  }
}

The major-version tag keeps the example readable. In a repeatable test configuration, choose an approved exact image tag or digest and match the database version and extensions your application relies on. A test environment should also use an explicit timezone configuration where application behavior depends on it.

Applying real migrations is essential. A hand-maintained test schema can continue accepting writes after the application schema adds a constraint or changes a column. That gives the tests a different contract from the system they are meant to check.

A fresh container per case provides a straightforward starting point, with a startup cost. Later, a suite can reuse a container while allocating separate databases to workers. Change the resource lifecycle only after deciding how each test will receive clean state.

The snippets below describe a small example application. Its migration creates an orders table with text identifiers, tenant_id, source_id, external_order_key, status, paid_at, currency, and amount_minor. It includes a named nonnegative-amount constraint and a unique business key across tenant, source, and external order reference. Apply the same test ideas to your application’s actual types and relationships.

Large seed datasets make tests harder to explain. A few carefully chosen records can expose the important failures much more directly.

Use Tenant A, Store 1, and the UTC day October 7, 2026. The example fixture is:

Each excluded record satisfies the other reporting conditions. The cancelled order deliberately retains a historical payment timestamp, which this example’s lifecycle permits. If the status predicate disappears, that row can now enter the result. A cancelled record with a null payment timestamp would still be excluded by the time predicate and would not expose that mistake.

This reduced schema uses tenant-local source keys: both tenants can have a source called store-1. That makes the tenant predicate independently testable. If your real source IDs are globally unique UUIDs, preserve that model in the fixture. Do not create an impossible cross-tenant relationship just to make a test fail; verify tenant and source authorization through valid records and application requests.

The fixture helper can make every input visible:

const fixtureRows = [
  ["order-a1", "tenant-a", "store-1", "key-a1", "paid", "2026-10-07T00:00:00Z", 10000],
  ["order-a2", "tenant-a", "store-1", "key-a2", "paid", "2026-10-07T12:00:00Z", 20000],
  ["order-a3", "tenant-a", "store-1", "key-a3", "paid", "2026-10-08T00:00:00Z", 30000],
  ["order-a4", "tenant-a", "store-1", "key-a4", "cancelled", "2026-10-07T13:00:00Z", 40000],
  ["order-b1", "tenant-b", "store-1", "key-a1", "paid", "2026-10-07T14:00:00Z", 50000],
  ["order-a5", "tenant-a", "store-2", "key-a5", "paid", "2026-10-07T15:00:00Z", 60000],
]

async function seedPaidOrderFixture(db: Client) {
  for (const row of fixtureRows) {
    await db.query(
      `INSERT INTO orders (
        id, tenant_id, source_id, external_order_key,
        status, paid_at, amount_minor, currency
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, 'TRY')`,
      row,
    )
  }
}

The external reference key-a1 appears in both tenants. The scoped unique constraint permits this while preventing a duplicate within the same tenant and source.

Keep dates, IDs, and amounts deterministic. A fixture based on the current clock can change meaning when a test runs near midnight. Fixed inputs let someone calculate the expected result by reading the table.

Extend the fixture only when a new behavior needs it. A same-timestamp pair can challenge ordering. A second currency can challenge the currency filter. A record just before the start can challenge the lower time boundary. Each addition should have a reason the test name can express.

For the selected scope, the expected IDs are order-a1 and order-a2. The matching count is 2, and the sum is 30000 TRY minor units. These are expectations for the example data, rather than measured outcomes from a production system.

A repository implementation should apply the same population predicate to its list and summary. Here is a compact example with no pagination, so the list includes every matching order:

type PaidOrderScope = {
  tenantId: string
  sourceId: string
  period: { start: Date; endExclusive: Date }
}

function safeInteger(value: string): number {
  const parsed = Number(value)
  if (!Number.isSafeInteger(parsed)) {
    throw new RangeError("Database total exceeds the numeric contract")
  }
  return parsed
}

async function listPaidOrders(db: Client, scope: PaidOrderScope) {
  const predicate = `tenant_id = $1 AND source_id = $2
    AND paid_at >= $3 AND paid_at < $4
    AND status = 'paid' AND currency = 'TRY'`
  const values = [
    scope.tenantId,
    scope.sourceId,
    scope.period.start,
    scope.period.endExclusive,
  ]

  const items = await db.query(
    `SELECT id FROM orders WHERE ${predicate} ORDER BY paid_at, id`,
    values,
  )
  const summary = await db.query(
    `SELECT count(*) AS total,
      coalesce(sum(amount_minor), 0) AS revenue_minor
      FROM orders WHERE ${predicate}`,
    values,
  )

  return {
    items: items.rows,
    total: safeInteger(summary.rows[0].total),
    revenueMinor: safeInteger(summary.rows[0].revenue_minor),
  }
}

This mapping assumes the default node-postgres parsers for these aggregate types. PostgreSQL count and a sum over integer amounts can produce values beyond JavaScript’s safe integer range. The example rejects those values; an application that supports larger totals needs an explicit string or big-integer representation. See node-postgres data types for its parsing behavior.

The shared predicate is application-owned SQL. Input values remain parameters. The list uses id as a tie-breaker, making the expected ordering deterministic when payment times match.

Now verify the whole answer using Node’s test runner:

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

test("returns exactly the paid orders in the selected scope", async () => {
  await withTestDatabase(async (db) => {
    await seedPaidOrderFixture(db)

    const result = await listPaidOrders(db, {
      tenantId: "tenant-a",
      sourceId: "store-1",
      period: {
        start: new Date("2026-10-07T00:00:00Z"),
        endExclusive: new Date("2026-10-08T00:00:00Z"),
      },
    })

    assert.deepEqual(result.items.map((order) => order.id), [
      "order-a1",
      "order-a2",
    ])
    assert.equal(result.total, 2)
    assert.equal(result.revenueMinor, 30000)
  })
})

An assertion such as items.every(order => order.tenantId === tenantId) also passes when items is empty. Exact identifiers establish that the operation returned the required records and excluded the others. A count by itself is insufficient too: the wrong pair of records could still produce 2.

The fixture remains unchanged between the list and summary reads here. Shared filters alone do not establish snapshot consistency under concurrent writes. If the API promises one coherent snapshot, test its transaction and isolation behavior separately. The database scope article covers that distinction.

For a paginated repository, make the page size smaller than the matching population. Check that the list contains only the page while the count and revenue still describe all matching orders. Do not accidentally assert that a full-population summary equals the sum of one page.

Isolation determines whether a test owns its state. Cleanup that works for a serial repository test may fail when the application opens a connection pool or a second worker runs at the same time.

A rollback pattern can be concise:

await db.query("BEGIN")
try {
  await seedPaidOrderFixture(db)
  await checkRepositoryUsingThisConnection(db)
} finally {
  await db.query("ROLLBACK")
}

Here, checkRepositoryUsingThisConnection is a test callback that passes the same client into the repository. A transaction belongs to that connection. If the application opens a different connection and commits an order there, rolling back db will leave that order in the database. The node-postgres transaction guide explicitly requires using one client for the statements in a transaction.

The reverse problem matters too: another connection cannot normally read the fixture’s uncommitted rows. Wrapping fixture setup in a transaction on one client and sending an HTTP request through the application’s pool may give the request an empty database.

For tests that cross connection boundaries, a separate database per worker is often easier to explain. Apply migrations once for that worker, load committed fixtures for each case, and reset state between cases. Tests within one worker still need serialization or a further isolation mechanism; sharing a worker database does not make concurrent cases independent.

Cleanup needs to account for foreign keys, sequences, and background work. A worker that continues writing after a reset can contaminate the next case. Wait for or stop that work before clearing state. A schema strategy must also prevent pooled connections from silently falling back to a shared public schema.

Rollback is useful for fast repository checks, but a persistence test should observe the application’s own commit behavior. Start with the promise the case needs to verify, then select the smallest isolation boundary that preserves it.

Read tests establish the population a query returns. Write tests establish which states the system can create and what survives a failure.

Begin with a successful application write. After the operation completes, read the stored record and check its tenant, source, external reference, status, and amount. If the contract includes committed persistence, perform that read through a separate connection. Reading inside the same transaction can observe a write that nobody else can see yet.

Then test the constraints that make invalid states impossible. Our example migration gives the business key a specific name:

CONSTRAINT orders_business_key_unique
  UNIQUE (tenant_id, source_id, external_order_key)

The amount column is non-null and has a named check:

amount_minor bigint NOT NULL
  CONSTRAINT orders_amount_nonnegative CHECK (amount_minor >= 0)

Both constraints need tests against the migrated database. PostgreSQL’s constraint documentation explains how these rules apply to stored data.

For the duplicate-key case, use a new row ID while copying an existing business key. That ensures the test challenges the business-key constraint instead of accidentally hitting the primary key:

await assert.rejects(
  db.query(`INSERT INTO orders (
    id, tenant_id, source_id, external_order_key,
    status, paid_at, amount_minor, currency
  ) SELECT
    'order-a1-copy', tenant_id, source_id, external_order_key,
    status, paid_at, amount_minor, currency
  FROM orders WHERE id = 'order-a1'`),
  {
    code: "23505",
    constraint: "orders_business_key_unique",
  },
)

const persisted = await db.query(`SELECT id FROM orders
  WHERE tenant_id = 'tenant-a' AND source_id = 'store-1'
    AND external_order_key = 'key-a1'
  ORDER BY id`)
assert.deepEqual(persisted.rows.map((row) => row.id), ["order-a1"])

This snippet assumes the fixture is committed and the client has no surrounding test transaction. If the failing statement runs inside an explicit transaction, roll it back or recover through an appropriate savepoint before issuing the follow-up query.

Check the specific database failure. 23505 denotes a unique violation, while 23514 denotes a check violation. Asserting any rejected promise could also accept a connection failure or malformed SQL. PostgreSQL publishes these identifiers in its error-code reference.

For a multi-step write, trigger failure after an earlier mutation. Suppose the operation reserves inventory and then inserts the order. Arrange the fixture with five available units, reserve one, and make the later order insert violate the amount constraint. Drive the actual application function that owns the transaction.

After it returns the expected failure, a separate connection should observe five available units and no new order. If the inventory remains at four, the operation left a partial write. A failure that happens during input validation, before any database mutation, cannot establish this rollback guarantee.

The test should distinguish these outcomes:

PostgreSQL transactions provides the atomicity mechanism. The application still has to put the related statements inside the same transaction. Sending them independently does not create that guarantee.

A unique constraint also does not define the complete API retry contract. The service may translate a duplicate into a conflict or return a previous result under an idempotency policy. Test that behavior through the application path, including any business side effects. Backend Test Cases: A Practical API Testing Matrix connects those cases to HTTP responses and uncertain write outcomes. API design that survives change discusses the promises consumers need at that boundary.

Keep the cases focused enough that failures remain interpretable. After a regression, add the fixture record or failure transition that was missing and name the behavior it protects. Testing is a boundary design problem connects these database cases to the wider backend testing strategy.

A useful database test makes the expected result visible before the query runs. When the query changes, the test should tell you which behavior changed and which boundary was crossed.