Prove multi-step writes are atomic: inject failures between steps, assert nothing partial persists, and test isolation across concurrent connections.

Testing Database Transactions and Rollbacks

A transaction is a promise that several writes happen together or not at all. Transferring stock between warehouses, creating an order with its lines, charging a balance and recording the ledger entry — each is several statements, and a failure half-way must leave nothing behind. That property is invisible when everything succeeds, which is why most suites never test it: every test takes the happy path, every write lands, and the day a constraint fails on the fourth statement, the first three are discovered committed in production. This guide covers testing atomicity by injecting failures between steps, testing isolation with genuinely concurrent connections, and avoiding the common trap where the test harness’s own rollback hides the application’s missing one. It sits under database and ORM mocking.

Root Cause Analysis

Atomicity bugs come from code that looks transactional and is not. A function opens a transaction but one repository call inside it uses the global connection instead of the transaction handle, so that write commits independently. A developer adds an await to an external call inside the transaction, the call fails, and the error is caught and logged rather than rethrown, so the transaction commits a half-finished state. Each looks correct in review and passes every happy-path test.

Isolation bugs are the second class. Two requests read the same balance, both decide there is enough, and both write — the classic lost update. Whether this can happen depends on the isolation level and on whether the code locks the rows it reads, and it can only be observed with two connections acting at once. A single-connection test suite is structurally unable to find it.

The third problem is self-inflicted. Many test setups wrap each test in a transaction and roll it back for isolation, which is excellent practice — and it means the application’s own transaction becomes a nested one, a savepoint. Behaviour that depends on a real commit, such as a deferred constraint or an after-commit hook, then never happens in tests.

How a write escapes a transaction Inside a transaction, two writes use the transaction handle but one uses the global connection; when a later step fails and the transaction rolls back, the global-connection write has already committed and remains. transaction insert order via tx reserve stock via global db insert lines — fails via tx rollback undoes the order — not the stock reservation stock stays reserved every happy-path test passes; only a failure between steps reveals the leak
The leak is invisible until a later step fails, which is exactly the case happy-path tests never reach.

Reproducible Setup

A service function that performs several writes in one transaction, with its repositories accepting the transaction handle explicitly.

// src/orders/place-order.ts
import type { Db } from '../db/client';

type Repos = (tx: Db) => { orders: OrderRepo; lines: LineRepo; stock: StockRepo };

export async function placeOrder(db: Db, repos: Repos, input: PlaceOrderInput) {
  return db.transaction(async (tx) => {
    const r = repos(tx);
    const order = await r.orders.insert({ id: input.orderId, customerId: input.customerId });
    for (const line of input.lines) {
      await r.stock.reserve(line.sku, line.quantity);          // throws if insufficient
      await r.lines.insert({ orderId: order.id, ...line });
    }
    return order;
  });
}
// test/db.ts — each test runs against a fresh worker schema (see the Drizzle guide)
export { db, prepareWorkerSchema, truncateAll } from './db-setup';

Note that this suite truncates between tests rather than wrapping each in a rollback, precisely so the application’s transaction is a real top-level one.

Implementation

Step 1 — Inject a failure between steps and assert nothing persisted. Wrap one repository so it fails at a chosen point, run the operation, and check every table the operation touches.

// src/orders/place-order.test.ts
import { test, expect, beforeEach } from 'vitest';
import { db, truncateAll } from '../../test/db';
import { realRepos } from '../db/repos';
import { placeOrder } from './place-order';

beforeEach(truncateAll);

test('a failure on the second line leaves no order, no lines and no reservations', async () => {
  await seedStock({ A: 5, B: 5 });
  const failingRepos = (tx: typeof db) => {
    const r = realRepos(tx);
    let calls = 0;
    return { ...r, lines: { insert: async (l: any) => { if (++calls === 2) throw new Error('injected'); return r.lines.insert(l); } } };
  };

  await expect(placeOrder(db, failingRepos, {
    orderId: 'o1', customerId: 'c1', lines: [{ sku: 'A', quantity: 1 }, { sku: 'B', quantity: 1 }],
  })).rejects.toThrow('injected');

  expect(await count('orders')).toBe(0);
  expect(await count('order_lines')).toBe(0);
  expect(await stockLevel('A')).toBe(5);                 // the reservation was rolled back too
});

Step 2 — Test with a real constraint failure, not only injected ones. Injected failures prove the transaction boundary; a real constraint proves the database actually rejects what you expect.

test('insufficient stock aborts the whole order', async () => {
  await seedStock({ A: 5, B: 0 });
  await expect(placeOrder(db, realRepos, {
    orderId: 'o2', customerId: 'c1', lines: [{ sku: 'A', quantity: 2 }, { sku: 'B', quantity: 1 }],
  })).rejects.toThrow(/insufficient/);
  expect(await stockLevel('A')).toBe(5);
});

Step 3 — Test concurrency with two real connections. Start two operations that compete for the same row and assert the invariant holds afterwards.

test('two concurrent orders cannot oversell the last unit', async () => {
  await seedStock({ A: 1 });
  const a = placeOrder(db, realRepos, { orderId: 'o3', customerId: 'c1', lines: [{ sku: 'A', quantity: 1 }] });
  const b = placeOrder(db, realRepos, { orderId: 'o4', customerId: 'c2', lines: [{ sku: 'A', quantity: 1 }] });

  const results = await Promise.allSettled([a, b]);
  expect(results.filter((r) => r.status === 'fulfilled')).toHaveLength(1);
  expect(await stockLevel('A')).toBe(0);                 // never -1
});

This test only means something if the pool has at least two connections and the reservation uses a locking read or a conditional update, such as UPDATE stock SET qty = qty - $1 WHERE sku = $2 AND qty >= $1.

Step 4 — Test after-commit behaviour against a real commit. Hooks that run only once the transaction commits — sending an event, enqueuing a job — must not fire when it rolls back.

test('the order-placed event is published only after commit', async () => {
  const bus = memoryBus();
  await expect(placeOrderWithEvents(db, bus, badInput())).rejects.toThrow();
  expect(bus.published).toEqual([]);

  await placeOrderWithEvents(db, bus, goodInput());
  expect(bus.published).toHaveLength(1);
});
A lost update between two connections Two transactions both read a stock level of one, both decide there is enough, and both decrement, leaving minus one; a conditional update or a locking read makes the second transaction fail instead. connection A connection B read qty = 1 write qty = 0 read qty = 1 write qty = 0 → -1 fix: UPDATE … WHERE qty >= $1, or SELECT … FOR UPDATE only a two-connection test can prove it
No single-connection test can reproduce this interleaving; two real connections can.

Step 5 — Keep test-harness rollbacks for tests that do not care about commits. Most repository tests are fine inside a rolled-back wrapper; the tests in this guide are the exception and belong in a file configured to truncate instead.

Step 6 — Add a structural guard against the global-handle leak. A lint rule or a type-level restriction that repositories must be constructed from a handle, never import the global one, prevents the leak in Step 1 from being reintroduced.

Verification

Confirm the atomicity test detects the leak by reintroducing it: change the stock repository to use the global connection and run the failure-injection test. It must fail on the stock assertion — which proves the test is guarding exactly this mistake.

npx vitest run src/orders/place-order.test.ts
# with stock.reserve using the global db:
# FAIL a failure on the second line leaves no order, no lines and no reservations
#   expected 4 to be 5

Then confirm the concurrency test is sensitive. Replace the conditional update with an unconditional one; the oversell test should fail on the final stock level. Run it several times, since races are probabilistic — a test that fails on some runs is still catching the bug.

When the test harness's rollback hides the application's behaviour Wrapping a test in a rolled-back transaction turns the application's transaction into a savepoint, so after-commit hooks and deferred constraints never run; tests about commit behaviour must truncate instead. rollback wrapper app transaction = savepoint no real commit ever happens fine for most repository tests truncate between tests app transaction is top-level commits and hooks are real required for this guide's tests
Choose the isolation strategy per file, according to whether the test needs a real commit.

Troubleshooting

Symptom: the rollback test passes even though writes leak in production. Diagnosis: the test is itself inside a rollback wrapper, so the leaked write is rolled back by the harness. Fix: run transaction tests with truncation-based isolation, as this guide does.

Symptom: the concurrency test never fails, even with the bug. Diagnosis: the pool has one connection, so the operations serialise. Fix: configure at least two connections for the test pool, and consider adding a small delay between read and write in a test-only build to widen the race window.

Symptom: concurrency tests deadlock. Diagnosis: two transactions lock rows in opposite orders. Fix: this is a real bug the test has found — lock rows in a consistent order, for example sorted by key, and add the test permanently.

Symptom: truncation between tests is slow. Diagnosis: truncating many tables with foreign keys one at a time. Fix: truncate all tables in a single statement with CASCADE, or restrict truncation to the tables the file actually touches.

FAQ

Can transactions be tested with a mocked database?

Not meaningfully. A mock can record that transaction was called, but atomicity and isolation are properties of the engine. This is one of the clearest cases where only a real database, as in testing Drizzle ORM queries against a real database, can answer the question.

Should every service function get a failure-injection test?

Every function that performs more than one write and relies on them being atomic. A single-statement write is atomic by definition. In practice this is a modest number of functions — the ones that move money, stock or ownership — and they are exactly the ones where a partial write is expensive.

What isolation level should tests use?

The one production uses. Testing under a stronger level than production hides anomalies; testing under a weaker one reports anomalies that cannot occur. Set it explicitly in the test connection configuration rather than relying on the default.

How do I test a transaction that spans a message publish?

You cannot make a database write and a message publish atomic directly. The standard answer is an outbox table written in the same transaction, with a separate process publishing from it — and the test becomes the after-commit test in Step 4, applied to the outbox rows.