Knex.js Savepoints: Partial Rollbacks Without Nested Transaction Confusion

The Problem: All-or-Nothing Transactions Are Too Blunt

You're processing a batch of orders. One order fails a foreign-key check because the customer was deleted minutes ago. The other 99 orders are perfectly valid. With a plain Knex transaction, the entire batch rolls back — you lose the 99 good orders and must retry them. That's wasteful and, under load, a recipe for connection-pool exhaustion.

Knex's savepoint API lets you mark a point inside a transaction, roll back to that point, and continue. It's not a nested transaction (Knex emulates those with savepoints anyway), but a named marker you can release or roll back independently. The catch: savepoint names must be unique, MySQL and PostgreSQL handle release differently, and forgetting to pass the transaction object (trx) to every query builder is the most common silent bug.

How Savepoints Fit Into a Knex Transaction

A Knex transaction callback receives a trx object. Inside that callback you can call:

• trx.savepoint(name) — creates a savepoint.
• trx.rollback(name) — rolls back to that savepoint, discarding work done after it.
• trx.release(name) — releases the savepoint (PostgreSQL requires this; MySQL releases implicitly on commit/rollback).

The outer transaction still controls the final commit or rollback. If the callback promise resolves, Knex commits; if it rejects, Knex rolls back the whole thing — including any released savepoints.

Worked Example: Batch Insert With Per-Row Validation

Assume a PostgreSQL 15+ schema:

CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT REFERENCES customers(id),
  amount NUMERIC(10,2) NOT NULL CHECK (amount > 0)
);

We want to insert a batch of orders. If an order references a missing customer, skip that order but keep the others.

async function insertOrdersBatch(knex, orders) {
  return knex.transaction(async (trx) => {
    const results = [];
    for (const [idx, order] of orders.entries()) {
      const spName = `sp_order_${idx}`;
      await trx.savepoint(spName);
      try {
        const [id] = await trx('orders')
          .transacting(trx)               // critical: every query uses trx
          .insert({ customer_id: order.customerId, amount: order.amount })
          .returning('id');
        await trx.release(spName);        // success — release savepoint
        results.push({ status: 'inserted', id: id.id });
      } catch (err) {
        if (err.code === '23503') {       // FK violation (PostgreSQL)
          await trx.rollback(spName);     // undo only this order
          results.push({ status: 'skipped', reason: 'missing_customer' });
        } else {
          throw err;                      // other errors abort whole transaction
        }
      }
    }
    return results;                       // resolves → outer commit
  });
}

Key points:

• Savepoint names include the loop index to guarantee uniqueness.
• Every query builder call chains .transacting(trx). Omitting it runs the query on a separate connection outside the transaction.
• We catch only the foreign-key violation (PostgreSQL error code 23503). Other errors bubble up and abort the entire transaction.
• On success we release the savepoint; on FK error we rollback to it.

MySQL vs PostgreSQL: Release Behavior Differs

MySQL (InnoDB) releases all savepoints automatically when the transaction commits or rolls back. PostgreSQL keeps them until you call RELEASE SAVEPOINT (via trx.release()). If you forget to release in PostgreSQL, the savepoint remains until the outer transaction ends — harmless but unnecessary. The Knex API is the same, but the underlying SQL differs:

Action	PostgreSQL	MySQL (InnoDB)
trx.savepoint('sp1')	SAVEPOINT sp1	SAVEPOINT sp1
trx.rollback('sp1')	ROLLBACK TO SAVEPOINT sp1	ROLLBACK TO SAVEPOINT sp1
trx.release('sp1')	RELEASE SAVEPOINT sp1	RELEASE SAVEPOINT sp1 (no-op on commit)

If you write dialect-agnostic code, always call release after a successful operation; it's a no-op on MySQL but required on PostgreSQL.

Common Pitfalls and How to Verify

1. Forgetting .transacting(trx)

Queries without .transacting(trx) run on a different connection and are not part of the transaction. They commit immediately and won't roll back. Lint rule: every query builder inside a transaction callback must chain .transacting(trx).

2. Connection Pool Exhaustion

A transaction holds a pool connection for the entire callback duration. Avoid await fetch(), file I/O, or long sleeps inside the callback. If you need external calls, do them before entering the transaction or after committing.

3. DDL Inside Transactions (MySQL)

MySQL performs an implicit commit before any DDL (CREATE TABLE, ALTER TABLE). That breaks atomicity — prior inserts in the same transaction become permanent. Run migrations separately; never mix DML and DDL in a Knex transaction on MySQL.

4. Unhandled Promise Rejections

Automatic rollback only triggers on a rejected promise returned from the callback. If you spawn an async function without await and it rejects, the outer promise may resolve and commit while the inner work fails silently. Always await every async operation inside the callback.

Quick Verification Checklist

1. Run the batch-insert script against PostgreSQL and MySQL; confirm only valid orders persist.
2. Monitor knex.client.pool.numUsed() during a load test (50 concurrent transactions, 100 ms sleep each) — connections should be held only for the callback duration.
3. In TypeScript, hover the callback parameter; it should resolve to Knex.Transaction and show .transacting() on query builders.
4. Test nested emulation: outer transaction inserts row A, inner (savepoint) inserts row B, rollback inner, commit outer — row A exists, row B does not.

Closing Takeaway

Savepoints give you surgical rollback inside a single database transaction. They're cheaper than retrying whole batches and avoid the connection overhead of multiple transactions. The discipline is simple: generate unique names, always pass trx to every query, release on success, rollback on expected failures, and let unexpected errors abort the lot. Your batch jobs will thank you.