Atomic Batch Inserts with Knex.js Transactions: A Practical Guide

The Problem: Partial Writes Break Data Integrity

When inserting multiple related records—order lines, audit events, or user permissions—a failure halfway through leaves the database in an inconsistent state. Knex.js solves this with its transaction method, which wraps a series of queries in a single database transaction so they either all commit or all roll back.

Prerequisites

• Node.js 18+ with a Knex 3.x project already configured
• A supported database (PostgreSQL, MySQL, MariaDB, SQLite3, or Oracle) and a working connection pool
• Basic familiarity with async/await and promise error handling

Core Pattern: knex.transaction with Async/Await

The transaction callback receives a transaction-bound Knex instance (conventionally named trx). Every query chained off trx participates in the same transaction.

async function insertOrderWithLines(knex, order, lines) {
  return await knex.transaction(async (trx) => {
    const [orderId] = await trx('orders').insert({
      customer_id: order.customerId,
      total: order.total,
      status: 'pending'
    }).returning('id');

    const lineRows = lines.map(line => ({
      order_id: orderId,
      product_id: line.productId,
      quantity: line.qty,
      unit_price: line.price
    }));
    await trx('order_lines').insert(lineRows);

    return orderId;
  });
}

If any query throws, Knex automatically rolls back the transaction and releases the connection back to the pool. No explicit commit or rollback call is needed when using async/await.

Validating Rollback Behavior

Create a test script to confirm atomicity. This example uses PostgreSQL but works identically on other dialects.

// test-atomic.js
const knex = require('knex')({
  client: 'pg',
  connection: process.env.DATABASE_URL,
  pool: { min: 2, max: 10 }
});

async function runTest() {
  // Ensure clean table
  await knex.schema.dropTableIfExists('test_atomic');
  await knex.schema.createTable('test_atomic', t => {
    t.increments('id').primary();
    t.string('val').notNullable();
  });

  try {
    await knex.transaction(async (trx) => {
      await trx('test_atomic').insert({ val: 'first' });
      // Simulate a failure after the first insert
      throw new Error('Intentional rollback');
    });
  } catch (e) {
    // Expected
  }

  const rows = await knex('test_atomic').select('*');
  console.log('Rows after rollback:', rows.length); // Should be 0

  // Verify connection returned to pool
  const pool = knex.client.pool;
  console.log('Available connections:', pool.availableResources.length);
  console.log('Total pool size:', pool.size);

  await knex.destroy();
}

runTest().catch(console.error);

Run with node test-atomic.js. Expected output: Rows after rollback: 0 and the available connection count should match the pool's min setting (2 in this example).

Connection Pool Safety

Knex acquires a connection from the pool when the transaction starts and returns it on either commit or rollback. The critical rule: always return the promise from the transaction callback or throw an error. Forgetting to return a promise (or swallowing the error) leaves the transaction unresolved and the connection checked out, eventually exhausting the pool.

// ❌ Dangerous: missing return
knex.transaction(async (trx) => {
  await trx('table').insert({ a: 1 });
  // No return, no throw → connection leaked
});

// ✅ Correct
await knex.transaction(async (trx) => {
  await trx('table').insert({ a: 1 });
  // Implicit return of undefined resolves the promise
});

Nested Transactions and Savepoints

Knex supports nested transaction calls. On PostgreSQL and SQLite, the inner transaction becomes a savepoint, allowing partial rollback without aborting the outer transaction. On MySQL (InnoDB), savepoints are supported but Knex's implementation falls back to a no-op inner transaction—meaning an error in the inner block still rolls back the entire outer transaction.

await knex.transaction(async (outer) => {
  await outer('accounts').insert({ id: 1, balance: 100 });

  try {
    await outer.transaction(async (inner) => {
      await inner('accounts').where({ id: 1 }).decrement('balance', 50);
      throw new Error('Insufficient funds'); // Rolls back to savepoint
    });
  } catch (e) {
    // Outer transaction still alive; balance remains 100
  }

  await outer('accounts').where({ id: 1 }).increment('balance', 10);
  // Final balance: 110
});

If you target MySQL, treat nested transactions as documentation only—plan for full rollback on any inner error.

Error Handling and Recovery Options

Retryable Errors (Deadlocks, Serialization Failures)

Wrap the transaction call in a retry loop with exponential backoff for transient errors (PostgreSQL error codes 40001, 40P01).

async function withRetry(fn, maxAttempts = 3) {
  for (let attempt = 1; ; attempt++) {
    try {
      return await fn();
    } catch (err) {
      const isRetryable = err.code === '40001' || err.code === '40P01';
      if (!isRetryable || attempt >= maxAttempts) throw err;
      await new Promise(r => setTimeout(r, 100 * Math.pow(2, attempt)));
    }
  }
}

const orderId = await withRetry(() => insertOrderWithLines(knex, order, lines));

Non-Retryable Errors (Constraints, Foreign Keys)

Let these bubble up. The transaction is already rolled back; your application layer decides whether to show a user-friendly message or log for manual intervention.

Limitations and Gotchas

• No automatic retry—you must implement it yourself.
• Long-running transactions hold a pool connection; keep callbacks fast. Avoid external API calls inside a transaction.
• DDL statements (CREATE TABLE, ALTER) cause implicit commit in MySQL and Oracle, breaking atomicity. Restrict transactions to DML (INSERT/UPDATE/DELETE).
• Streaming/Chunked inserts using trx.batchInsert or trx.insert(chunk).returning('*') work inside transactions, but each chunk is a separate round-trip.

Verification Checklist

1. Run the test script above; confirm zero rows remain after intentional error.
2. Check pool metrics before/after: knex.client.pool.availableResources.length should return to baseline.
3. Add a deliberate constraint violation (duplicate unique key) and verify the catch block receives the error and no partial rows persist.
4. Load-test with clinic.js or autocannon to ensure no connection leaks under concurrency.

Quick Reference

Scenario	Pattern
Simple batch insert	await knex.transaction(trx => trx('t').insert(rows))
Need generated IDs	const [id] = await trx('t').insert(row).returning('id')
Conditional rollback	if (!ok) throw new Error('rollback')
Retry on deadlock	Wrap call in withRetry helper
Nested savepoint (PG/SQLite)	await trx.transaction(inner => …)