Keep multi-table writes consistent with Knex.js transactions

The problem: an orphan customer after a failed order

Imagine an order flow that inserts a customer row, then inserts an order row that references that customer. If you run them as two separate knex('table') calls and the order insert fails, the customer row stays behind. Your database now has a customer with no order, and any code that assumes every customer has a recent order breaks. The practical fix is to make those writes one atomic unit.

Use one transaction handle for every statement

Knex exposes knex.transaction(async (trx) => { ... }). Inside the callback, call trx('table') for each statement, or attach the transaction with .transacting(trx). Every statement then runs on the same database connection and is committed or rolled back together. Do not mix knex('table') and trx('table') in the same unit of work; statements on the base knex instance run outside the transaction and will not roll back.

Worked example: create a customer and an order

async function placeOrder(knex, { email, total }) {
  return knex.transaction(async (trx) => {
    const [customerId] = await trx('customers')
      .insert({ email })
      .returning('id');

    await trx('orders')
      .insert({ customer_id: customerId, total, status: 'pending' });

    return { customerId };
  });
}

If the order insert throws, the transaction handler rejects and Knex rolls back both writes. If it resolves, Knex commits before the outer promise settles. Always await every query inside the handler; a floating promise or swallowed error can let the transaction commit partial work.

Trade-offs and limits

• Connection pool pressure. A transaction holds one pooled connection until commit or rollback. Long transactions reduce throughput and raise deadlock risk. Keep the callback short and move external API calls, email sending, or user interaction outside it.
• Dialect differences. returning, nested transactions, savepoints, and isolation levels vary across PostgreSQL, MySQL/MariaDB, SQLite, MSSQL, and Oracle. Treat nested transactions as something to verify per dialect, not as a portable correctness mechanism.
• Transactions are not a schema substitute. Keep foreign keys and unique indexes so invalid rows cannot persist even if application code is wrong.

Check that it worked

Run an integration test against your real dialect: insert a parent row, force the child insert to fail, and assert the parent row is absent after rollback. You can also check your installed Knex version with:

# Run in your project root. No special permissions required.
npm ls knex

Expected check: the command prints the installed Knex version. Compare it against the transaction documentation for that version before relying on options beyond the basic handler form. Risk: assuming a newer option exists in an older version can cause runtime errors.

Where to draw the boundary

Put the transaction boundary at the request or use-case edge, pass trx into repository functions, and keep slow work outside the callback. Add a failure-injection test that asserts no rows persist when the second write fails. If you run serverless or multi-process deployments, plan connection limits separately; one Knex instance with pooling in a long-running Node process is the baseline assumption.

Quick checklist

• Wrap related inserts and updates in knex.transaction.
• Pass trx to every statement in the unit of work.
• Await every query; do not let promises float.
• Keep the callback short; no network calls or user interaction inside.
• Verify dialect-specific features before using them.