Knex.js Transactions: Preventing Partial Writes Across Multiple Tables

When two database writes depend on each other, running them as separate queries invites inconsistency. If the first UPDATE succeeds and the second fails, you are left with a partial state that is hard to unwind. Knex.js transaction callbacks solve this by binding related queries to a single connection and tying commit or rollback to the promise you return.

Wrapping related statements in knex.transaction(async trx => { ... }) gives you atomicity without manual connection management.

The partial-write problem

Consider an e-commerce checkout that inserts an order row and then decrements inventory. If the inventory statement fails—because a constraint rejects the update or the row was locked—the order remains in the table with no matching stock deduction. Cleaning up orphaned rows after the fact is messy and error-prone. The same hazard appears whenever you touch two tables that must agree: ledger entries, user profiles and audit logs, or account balances.

How Knex transaction callbacks work

Knex exposes a transaction method that accepts a callback. Inside that callback you receive a transaction object, conventionally named trx. Every query you build with trx instead of the root knex instance runs on the same dedicated database connection. When the callback returns a promise, Knex watches it:

• If the promise resolves, Knex commits the transaction.
• If the promise rejects—because you threw an error or a query failed—Knex rolls back automatically.

This removes the boilerplate of manually opening a connection, issuing BEGIN, and deciding when to COMMIT or ROLLBACK. In recent Knex versions (3.x line), the callback style is the recommended default.

Worked example: transferring funds between accounts

The classic case is moving money between two rows in an accounts table. Both the debit and the credit must happen, or neither should. The following function wraps the pair in a transaction and throws when the source balance is too low.

async function transferFunds(knex, fromId, toId, amount) {
  return knex.transaction(async (trx) => {
    const source = await trx('accounts').where('id', fromId).first();

    if (!source || source.balance < amount) {
      throw new Error('Insufficient funds');
    }

    await trx('accounts').where('id', fromId).decrement('balance', amount);
    await trx('accounts').where('id', toId).increment('balance', amount);
  });
}

If the balance check fails, the throw rejects the promise returned by the callback. Knex then rolls back both statements. If both updates succeed, the promise resolves and Knex commits. The caller does not need to manage the transaction lifecycle.

Nested transactions and savepoints

Knex also supports nesting. Calling trx.transaction(...) inside an outer transaction creates a savepoint. If the inner block fails, only the inner work is rolled back; the outer transaction can continue and commit its own changes. This is useful when a sub-operation is optional but you still want atomicity for its own writes.

Trade-offs: connection pools and manual finalization

Transactions are not free. The most common pitfall is holding a connection longer than necessary. While the callback is running, that connection is checked out from the pool. If you await an external HTTP request or a slow file operation inside the transaction, you starve other queries. Keep the scope tight: fetch any external data before you open the transaction, and run only the dependent database statements inside it.

A second sharp edge is manual finalization. If you call trx.commit() or trx.rollback() yourself, you must still return a resolved promise from the callback. Otherwise Knex will attempt its own commit or rollback afterward, which can produce confusing driver errors or leave the connection in an unexpected state.

Verifying the rollback path

You can confirm rollback with a short script. Run it from your project root with Node.js. It requires a database user that has SELECT and UPDATE permissions on the accounts table. Replace the connection placeholders with your own credentials.

// verify-transfer.js
const knex = require('knex')({
  client: 'pg',
  connection: 'postgres://USER:PASSWORD@localhost:5432/DB_NAME'
});

async function verify() {
  await knex('accounts').del();
  await knex('accounts').insert([
    { id: 1, balance: 100 },
    { id: 2, balance: 50 }
  ]);

  try {
    await knex.transaction(async (trx) => {
      await trx('accounts').where('id', 1).decrement('balance', 200);
      await trx('accounts').where('id', 2).increment('balance', 200);
    });
  } catch (err) {
    console.log('Rejected as expected:', err.message);
  }

  const rows = await knex('accounts').select('*').orderBy('id');
  console.log('Balances after failed transfer:', rows);
  // Expected: id 1 remains 100, id 2 remains 50
  await knex.destroy();
}

verify();

Risk: this script deletes existing rows in accounts. Do not point it at a production database.

If the balances are unchanged after the rejected transfer, the rollback path is working. If they changed, the transaction was not applied correctly—check that you are using the trx object for every statement and that your database user is not running in autocommit mode at the session level.

Actionable takeaways

• Wrap related writes in knex.transaction(async trx => { ... }) and let the returned promise drive commit or rollback.
• Use the trx object for every query inside the block; mixing knex and trx breaks atomicity.
• Keep transactions short and free of external awaits to protect your connection pool.
• Test the failure path explicitly; a rollback that never runs in tests is a production incident waiting to happen.