Knex.js Nested Transactions: Savepoints for Partial Rollbacks

The batch that should not die on one bad row

You need an outer transaction that groups several writes, but inside it you have sub-operations that can fail independently. Rolling back the whole outer transaction for a single bad line item is wasteful. The useful takeaway is that Knex.js maps nested transaction callbacks to SQL savepoints on databases that support them, letting you roll back a sub-operation without aborting the parent.

A savepoint is a named marker inside an open transaction. Rolling back to a savepoint undoes work after that marker while keeping the outer transaction alive. Knex creates and releases those savepoints automatically when you call trx.transaction inside an existing transaction.

How nesting becomes savepoints

Knex does not create a real nested transaction in the database. It starts a transaction with BEGIN, then for each nested callback it issues SAVEPOINT knex_1, SAVEPOINT knex_2, and so on. A successful inner callback issues RELEASE SAVEPOINT. An error in the inner callback triggers ROLLBACK TO SAVEPOINT and the error propagates to the caller.

This works on dialects that implement savepoints, such as PostgreSQL, MySQL 5.7+, and SQLite 3.6.8+. On dialects without savepoint support Knex falls back to a flat transaction. The fallback is silent, which means nesting appears to work but provides no isolation.

A concrete pattern for isolated sub-operations

Run the following in a Node.js process with a Knex instance configured for a savepoint-capable database. The database user needs INSERT and UPDATE privileges on the target tables; savepoint creation is standard for transactional users.

const knex = require('knex')({
  client: 'pg',
  connection: process.env.DATABASE_URL,
  // debug: true helps verification, see below
});

async function processOrder(orderId, items) {
  await knex.transaction(async trx => {
    await trx('orders').insert({ id: orderId, status: 'processing' });

    for (const item of items) {
      try {
        await trx.transaction(async subTrx => {
          // sub-operation that may fail for one item
          await subTrx('inventory')
            .where({ sku: item.sku })
            .decrement('qty', item.qty);
          if (!item.valid) {
            throw new Error('invalid item');
          }
          await subTrx('order_items')
            .insert({ order_id: orderId, sku: item.sku, qty: item.qty });
        });
      } catch (err) {
        // inner rollback to savepoint happens automatically;
        // outer transaction continues for remaining items
        await trx('order_items_failed')
          .insert({ order_id: orderId, sku: item.sku, reason: err.message });
      }
    }

    await trx('orders').where({ id: orderId }).update({ status: 'completed' });
  });
}

Risks to note. If the error inside subTrx is not caught, the outer transaction is marked for rollback and the whole batch aborts. Keep nesting shallow; deep recursive callbacks increase JavaScript call stack usage and create many savepoints.

Trade-offs and silent failure modes

Savepoint creation adds round trips. Excessive nesting creates many savepoints and can degrade performance on high-throughput paths. Most databases impose a practical depth limit, and very deep nesting can cause stack issues in the Node callback chain.

Dialect support is the main limitation. Attempting nested transactions on a dialect without savepoints does not raise an error; Knex simply executes statements in the same transaction. That can lead to unexpected commits when you assumed isolation.

Verify savepoints are actually used

Enable Knex query logging with debug: true in the client config and inspect the SQL stream for SAVEPOINT, RELEASE SAVEPOINT, and ROLLBACK TO SAVEPOINT statements. Their presence confirms the driver is using savepoints for the nested callbacks.

As a second check, run a small script against a test database. Start an outer transaction, perform a nested transaction that intentionally errors, and then commit the outer transaction. Query the tables afterwards to confirm that writes from the successful outer scope persisted while writes from the failed inner scope did not.

Use nested transactions only when you need partial rollback inside an atomic batch and you are on a savepoint-capable dialect. Otherwise prefer explicit error handling with conditional writes in a single flat transaction.