Group Related Writes with Knex.js Transactions

The problem: two writes that must succeed together

Imagine an order placement endpoint. It inserts a row into orders and decrements stock in inventory. If the first insert succeeds but the stock update fails, you have sold something you do not have. If the stock update succeeds but the order insert fails, inventory disappears with no order. You need both writes to commit or neither.

Use Knex transactions for related writes

Knex exposes knex.transaction, which accepts a callback. Knex begins the transaction, runs your callback, commits automatically if the callback resolves, and rolls back automatically if it throws or rejects. You do not call BEGIN, COMMIT, or ROLLBACK yourself.

Inside the callback, every query must receive the transaction object, usually named trx, through .transacting(trx). Knex borrows one connection from the pool for the whole transaction. Queries that omit .transacting(trx) use a different connection and run outside the transaction, so they will not be rolled back if something later fails.

Worked example: order and inventory

Run this in a local Node.js script against a test database. The Knex instance is assumed to be configured for PostgreSQL; syntax is similar for other dialects, but DDL behavior differs.

const knex = require('knex')({
  client: 'pg',
  connection: process.env.DATABASE_URL,
  pool: { min: 0, max: 10 }
});

async function placeOrder(orderId, productId, quantity) {
  return knex.transaction(async (trx) => {
    await knex('orders')
      .insert({ id: orderId, product_id: productId, quantity, status: 'placed' })
      .transacting(trx);

    await knex('inventory')
      .where('product_id', productId)
      .decrement('available', quantity)
      .transacting(trx);

    if (quantity > 100) {
      throw new Error('Quantity exceeds limit');
    }

    return { orderId, productId, quantity };
  });
}

// To verify rollback behavior intentionally:
// placeOrder('order-1', 'product-1', 150).catch(() => {
//   // Query orders and inventory directly. Neither should reflect the call.
// });

Required permissions: the database user needs INSERT on orders and UPDATE on inventory. Placeholders: replace process.env.DATABASE_URL, table names, and column names with your schema. Expected check: after an intentional throw, query both tables directly and confirm neither table contains the new order or the decremented stock. Risk: if you forget .transacting(trx) on either query, that query commits independently even when the transaction rolls back.

Connection pool implications

Knex holds one pool connection for the entire transaction. A short transaction is usually fine, but a long-running transaction—waiting on an external API, a large file upload, or user think-time—keeps that connection occupied. Under load, the pool can exhaust and new requests time out waiting for a connection.

Keep transactions short. Do not open a transaction before an outbound HTTP call and wait for the response inside it. Fetch or compute what you need first, then open the transaction, write, and return.

When not to use a transaction

• Read-only reporting queries do not need a transaction and only consume a pool connection unnecessarily.
• Schema changes in migrations are not portable inside transactions. PostgreSQL supports transactional DDL, but MySQL does not, so wrapping CREATE TABLE or ALTER TABLE is not safe across dialects.
• Nested transactions in Knex use savepoints, not true nested transactions. Behavior varies by driver and database, so avoid them unless you have tested the specific combination.

Check that it worked

Run the example script with a quantity that throws. Then query orders and inventory directly in your database client. If neither table changed, the rollback worked. To observe pool contention, open a transaction that sleeps for several seconds and check your database's connection count or Knex pool metrics while it is open.

Closing

Use Knex transactions when two or more writes must succeed or fail together. Always pass trx to every query inside the callback, keep the transaction short, and avoid them for reads or schema changes unless your dialect supports it. A quick rollback test in a staging database is the best way to confirm the behavior before you deploy.