Mastering Atomic Operations with Knex.js Transactions

Creating a user and their default settings takes two inserts. If the second one fails—a constraint violation, a timeout, a bug—you are left with a user row that has no settings and a service that assumes they exist. That partial-write problem is exactly what transactions solve, and Knex.js makes the fix a one-line wrapper, provided you follow one rule that trips up almost everyone at first: pass the transaction handle to every query.

The callback pattern and the trx rule

Knex's recommended transaction API is a callback. You call knex.transaction() with an async function that receives a transaction builder, usually named trx. If your function resolves, Knex issues COMMIT. If it throws or rejects, Knex issues ROLLBACK and re-raises your error. You never handle commit and rollback bookkeeping yourself.

Here is the rule: every query inside the block must be built from trx, not the global knex instance. Each Knex instance acquires its own connection from the pool. A query built from knex while you are inside a transaction runs on a different connection, outside the transaction, and commits independently. The result is precisely the partial state you were trying to avoid—and nothing in the code warns you when you do it.

Knex also supports manual control via trx.commit() and trx.rollback(), but the callback form is harder to get wrong: you cannot forget to commit on a success path or leak a connection on an error path, because Knex owns both.

Worked example: user plus default settings

The following assumes Knex 2.x or 3.x on a maintained Node.js release, with a PostgreSQL database. returning() is used to get the new id back; it works on PostgreSQL and SQLite, while on MySQL you would read the insert id from the result instead.

const knex = require('./db-connection');

async function registerUser(userData) {
  return knex.transaction(async (trx) => {
    // Step 1: create the user
    const [user] = await trx('users')
      .insert({ name: userData.name, email: userData.email })
      .returning(['id']);

    // Step 2: create default settings — note trx, not knex
    await trx('settings').insert({
      user_id: user.id,
      theme: 'dark',
      notifications: true,
    });

    return user;
  });
}

If the settings insert violates a foreign key or any other error occurs, the whole callback rejects, Knex rolls back the user insert, and registerUser throws. The caller sees one failure, and the database shows no trace of the attempt.

Verifying the rollback actually happens

Do not take this on faith—test it once per codebase:

1. Temporarily add throw new Error('simulated failure'); between the two inserts.
2. Run the flow, then check the database: SELECT * FROM users WHERE email = '...'; should return no rows.
3. Remove the throw and confirm both rows appear on a successful run.

To confirm the queries share one connection, PostgreSQL's pg_stat_activity view shows the backend handling the transaction; both statements should hit the same pid while the callback runs. Equivalent tooling exists for other engines, but the specifics are dialect-dependent.

The costs you accept

A transaction is not free, and the bill shows up in three places:

• Pooled connections. The callback holds one connection from start to finish. An external API call or slow computation inside the block pins that connection and, under load, can exhaust the pool so unrelated requests stall. Do validation and network calls before opening the transaction; keep the block to database work only.
• Locking. Writes inside the transaction take locks that other transactions must wait on. Many concurrent transactions touching the same rows invite deadlocks and latency spikes.
• Dialect differences. Default isolation levels and lock behavior vary: PostgreSQL defaults to READ COMMITTED, MySQL's InnoDB to REPEATABLE READ, and SQLite serializes writes at the database level. Code that is deadlock-free on one engine can behave differently on another.

None of these are reasons to skip transactions for multi-step writes; they are reasons to keep them short and to test under realistic concurrency.

One audit worth doing today

Search your codebase for knex.transaction (or your wrapper around it) and check every query builder inside each callback. If any of them starts from the global knex instead of the trx parameter, that statement is silently running outside the transaction. Fixing those call sites is usually a one-word change, and the throw-in-the-middle test above will confirm the difference.