Knex.js transactions for multi-table updates: scope, pool limits, and rollback checks

The failure this design prevents

Two writes that must agree — debit one row, credit another, append a ledger entry — can partially succeed if each runs as its own statement. Knex.js wraps them in a transaction so the database commits all of them or none. The transaction call itself is one line; the engineering decisions are about where the transaction object is allowed to travel, how the connection pool is sized, and what happens when the callback throws.

Requirements that decide the shape

• All statements in the unit of work must hit the same database and the same connection.
• The unit of work is short — milliseconds, not seconds — because it holds a pooled connection and row locks.
• Only the data layer receives the transaction object; callers pass plain values.
• Errors must propagate to the caller so the caller can decide retry versus surface.

Smallest suitable design

One transaction wrapper in the service, one repository function that accepts trx. Nothing else changes.

// repository.js — the only place trx is used
async function moveStock(trx, { fromId, toId, qty }) {
  await trx('inventory').where({ id: fromId }).decrement('on_hand', qty);
  await trx('inventory').where({ id: toId }).increment('on_hand', qty);
  await trx('stock_moves').insert({ from_id: fromId, to_id: toId, qty });
}

// service.js
async function moveStockService(knex, input) {
  return knex.transaction(async (trx) => {
    await moveStock(trx, input);
  }, { isolationLevel: 'read committed' });
}

If a statement must be raw SQL, use trx.raw(sql, bindings) so it runs on the transaction connection. For bulk inserts, trx.batchInsert(table, rows, chunkSize) is the intended API, but confirm on your dialect that it participates in the outer transaction rather than opening its own; if that is unclear, plain trx(table).insert(rows) is easier to reason about.

Trust and data boundaries

The trx object is a connection handle, not a value. Treat it as a capability: whoever holds it can run statements inside your transaction.

• Do not pass trx into HTTP handlers, queue consumers, or event emitters — those can outlive the transaction and will fail with a closed connection.
• Do not store trx on a request object or a module-level variable.
• Do not start a second transaction inside the callback; a nested knex.transaction call acquires a second connection and can deadlock the pool. Knex exposes savepoints through trx.transaction in some versions, but verify before relying on it.
• Validate inputs before the transaction opens; a validation failure should not consume a connection.

Operational checks

Isolation level: Knex passes isolationLevel to the driver when you supply it. The default comes from the database, not from Knex, and supported values differ by dialect. Verify what your database actually applies — in PostgreSQL, run SHOW transaction_isolation inside the transaction; in MySQL, run SELECT @@transaction_isolation.

Pool sizing: transactions hold a connection for their whole duration. A pool that is too small queues requests behind long transactions; a pool that is too large can exceed the database's own connection limit.

const knex = require('knex')({
  client: 'pg',
  connection: process.env.DATABASE_URL,
  pool: {
    min: 2,
    max: 10,
    idleTimeoutMillis: 30000,
    acquireTimeoutMillis: 10000  // tarn option; verify for your Knex version
  }
});

Set acquireTimeoutMillis so a saturated pool fails fast instead of hanging. Log a distinct message when acquisition times out; that is a capacity signal, not a query bug.

Failure modes

Verification you can run

Run against a disposable database, not production. This checks that a thrown error rolls back both inserts.

// verify-rollback.js — run with: node verify-rollback.js
const knex = require('knex')({
  client: 'pg',
  connection: process.env.TEST_DATABASE_URL
});

async function main() {
  try {
    await knex.transaction(async (trx) => {
      await trx('accounts').insert({ id: 9001, balance: 10 });
      await trx('ledger').insert({ account_id: 9001, amount: 10 });
      throw new Error('forced rollback');
    });
  } catch (err) {
    console.log('caught:', err.message);
  }
  const accounts = await knex('accounts').where({ id: 9001 });
  const ledger = await knex('ledger').where({ account_id: 9001 });
  console.log({ accounts: accounts.length, ledger: ledger.length });
  await knex.destroy();
}

main();

Expected result: both counts are 0. If either is 1, the statements did not share the transaction — check that every call used trx, not the root knex instance.

To confirm pool behavior, set pool: { min: 1, max: 1 } and start two concurrent transactions that each pause briefly. The second should wait for the first to finish. That is expected, not a bug; it shows why long transactions reduce throughput.

Conditions that would change the design

• Writes span two services or two databases: use an outbox table plus a relay, or a saga with compensating actions. A single Knex transaction cannot span them.
• Bulk loads of hundreds of thousands of rows: chunk outside one transaction or use dialect-specific bulk loaders; a single transaction holds locks and a connection too long.
• Strict serializable isolation is required: verify driver support and expect retries on serialization failures.
• Read-only reporting: skip the transaction; a consistent snapshot may be cheaper via a replica or a single query.
• Retries become routine: make each transaction idempotent with a natural key or unique constraint, and add a retry budget.

One caveat worth flagging for review: exact isolation defaults, batchInsert behavior inside a transaction, and tarn pool option names vary by Knex version and dialect. Confirm them against the documentation for the version you deploy before treating them as fixed.