Database Transactions in Node.js

A transaction groups several database statements into a single all-or-nothing unit of work. Either every statement succeeds and the changes are made permanent, or any failure undoes the whole group as if it never ran. This matters whenever one logical operation touches multiple rows or tables — transferring money, placing an order with line items, or updating a balance and writing an audit log — because a partial write leaves your data in an inconsistent state. In Node.js the key discipline is simple: a transaction must always end in exactly one COMMIT or one ROLLBACK, even when an error is thrown midway.

ACID properties

Relational databases guarantee transactions satisfy four properties, abbreviated ACID:

Property	Guarantee
Atomicity	All statements commit together or none do — no partial application.
Consistency	The transaction moves the database from one valid state to another, respecting constraints.
Isolation	Concurrent transactions do not see each other’s uncommitted changes.
Durability	Once committed, changes survive crashes and power loss.

Atomicity and isolation are the two you actively control from application code; consistency and durability are largely enforced by the engine and its constraints.

BEGIN, COMMIT, and ROLLBACK

Every transaction follows the same skeleton. You open it with BEGIN (some drivers call it START TRANSACTION), run your statements, and close it with COMMIT to persist or ROLLBACK to discard. The single most important rule is that all statements in one transaction must run on the same physical connection — you cannot start a transaction on one pooled connection and commit on another.

Transactions with pg (PostgreSQL)

With node-postgres you check out a dedicated client from the pool so every statement shares one connection. Wrap the work in try/catch/finally so a failure rolls back and the client is always released.

import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

async function transfer(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    const debit = await client.query(
      "UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1 RETURNING balance",
      [amount, fromId],
    );
    if (debit.rowCount === 0) {
      throw new Error("Insufficient funds");
    }

    await client.query(
      "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
      [amount, toId],
    );

    await client.query("COMMIT");
    return debit.rows[0].balance;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

console.log("New balance:", await transfer(1, 2, 50));

Output:

New balance: 150

If the second UPDATE throws, the catch block issues ROLLBACK and the debit is undone — the money never disappears.

Warning: Never run transaction statements directly on the pool object (pool.query). Each pool.query call may grab a different connection, so your BEGIN and COMMIT could land on separate sessions, silently breaking atomicity.

Transactions with mysql2

The mysql2/promise API exposes helper methods so you do not have to write the SQL keywords by hand. Grab a connection, call beginTransaction(), then commit() or rollback().

import mysql from "mysql2/promise";

const pool = mysql.createPool({ uri: process.env.MYSQL_URL });

async function placeOrder(userId, items) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    const [result] = await conn.execute(
      "INSERT INTO orders (user_id, status) VALUES (?, 'pending')",
      [userId],
    );
    const orderId = result.insertId;

    for (const item of items) {
      await conn.execute(
        "INSERT INTO order_items (order_id, sku, qty) VALUES (?, ?, ?)",
        [orderId, item.sku, item.qty],
      );
    }

    await conn.commit();
    return orderId;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

Tip: In CommonJS the only change is the import — const mysql = require("mysql2/promise"). The transaction logic is identical.

Isolation levels

Isolation controls what concurrent transactions can observe. Weaker levels allow more concurrency but expose read anomalies; stronger levels prevent them at the cost of throughput and possible serialization errors you must retry.

Level	Dirty read	Non-repeatable read	Phantom read
Read uncommitted	Possible	Possible	Possible
Read committed	No	Possible	Possible
Repeatable read	No	No	Possible*
Serializable	No	No	No

PostgreSQL defaults to Read committed; MySQL InnoDB defaults to Repeatable read (and prevents phantoms with next-key locks, marked *). Set a level per transaction right after opening it:

await client.query("BEGIN");
await client.query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");

Use SERIALIZABLE when correctness under concurrency is critical, and wrap such transactions in a retry loop because the engine may abort one with a serialization failure.

Transactions with an ORM

ORMs wrap the same mechanics in a callback. With Prisma, $transaction commits if the callback resolves and rolls back automatically if it throws — no manual release() needed.

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

await prisma.$transaction(async (tx) => {
  await tx.account.update({
    where: { id: 1 },
    data: { balance: { decrement: 50 } },
  });
  await tx.account.update({
    where: { id: 2 },
    data: { balance: { increment: 50 } },
  });
});

Sequelize offers the same pattern with sequelize.transaction(async (t) => { ... }), passing t to each query. The managed callback form is preferred because it cannot leak a connection.

Best practices

Always run a transaction’s statements on one checked-out connection, never on the pool directly.
Wrap the work in try/catch/finally: COMMIT on success, ROLLBACK on error, release the connection in finally.
Keep transactions short — hold locks for as little time as possible and never await network calls or user input mid-transaction.
Choose the lowest isolation level that is still correct for your workload, and add a retry loop when using SERIALIZABLE.
Let constraints (CHECK, foreign keys, unique indexes) enforce consistency instead of relying solely on application checks.
Prefer the managed callback form in ORMs (Prisma $transaction, Sequelize managed transactions) so connections cannot leak.