Transactions and Data Integrity

The Repository Pattern and Services covered how to keep query logic out of your services and behind a clean interface. That separation makes the next problem obvious: once a single business operation touches more than one table, or more than one repository, something has to guarantee that all of those writes succeed together or not at all. That guarantee is a transaction, and getting it wrong is one of the quieter ways a SaaS product ends up with corrupted data that nobody notices until a customer complains.

This is article three in the NestJS Production Guide, and it's the one where the repository pattern stops being an abstraction exercise and starts paying rent. A repository that can't participate in a transaction cleanly will eventually cause a partial write in production. Most of what follows uses PostgreSQL and TypeORM, since that's the pairing the rest of this series uses, but the underlying reasoning applies to Prisma or raw pg just as much; the API differs, the failure modes don't.

Why a single failed write can corrupt more than one row

Picture an order-placement flow: create an order row, decrement inventory for each line item, and record a payment intent. Each of those is a separate write, often against different repositories. If the order insert succeeds and the inventory decrement then throws because a database constraint failed, you're left with an order that exists but has no stock reserved against it. Nobody planned for that order to exist in that state. It exists anyway, because nothing rolled it back.

A transaction is the database's mechanism for saying: run this whole sequence of statements as one atomic unit. Either every statement commits, or the database rolls all of them back and the data looks exactly as it did before the transaction started. This is the "A" and "C" in ACID, and it's not optional for any write path that touches more than one table with a dependency between them.

This gets missed in practice because most CRUD endpoints are single-table writes, and those don't need an explicit transaction; a single INSERT or UPDATE is already atomic on its own. The problem shows up when a service method calls two or more repository methods in sequence and treats the whole thing as one logical operation. That's exactly the shape the repository pattern from the previous article produces, so this is the natural next problem to solve.

Running transactions in NestJS with TypeORM

TypeORM gives you a few ways to run a transaction, and the level you choose should match how much control you need over the connection.

The simplest option is DataSource.transaction(), which hands you an EntityManager scoped to a single database connection and rolls back automatically if the callback throws:

// src/modules/orders/orders.service.ts
import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';
import { Order } from './entities/order.entity';
import { InventoryItem } from '../inventory/entities/inventory-item.entity';

@Injectable()
export class OrdersService {
  constructor(private readonly dataSource: DataSource) {}

  async placeOrder(userId: string, items: { sku: string; quantity: number }[]) {
    return this.dataSource.transaction(async (manager) => {
      const order = manager.create(Order, { userId, status: 'pending' });
      await manager.save(order);

      for (const item of items) {
        const result = await manager.decrement(
          InventoryItem,
          { sku: item.sku, quantity: manager.query('quantity') as unknown as number },
          'quantity',
          item.quantity,
        );

        if (result.affected === 0) {
          throw new Error(`Insufficient stock for SKU ${item.sku}`);
        }
      }

      return order;
    });
  }
}

Everything inside that callback runs against the same connection and the same transaction. If any statement throws, TypeORM issues a ROLLBACK and the thrown error propagates out normally, so your existing exception filters still catch it. This is the right default for most business operations. It's explicit and readable, and it doesn't leak connection management into the rest of the service.

The lower-level option is a QueryRunner, which you manage manually:

// src/modules/orders/orders.service.ts
async placeOrderManual(userId: string, items: { sku: string; quantity: number }[]) {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction();

  try {
    const order = queryRunner.manager.create(Order, { userId, status: 'pending' });
    await queryRunner.manager.save(order);

    // ... additional writes using queryRunner.manager

    await queryRunner.commitTransaction();
    return order;
  } catch (error) {
    await queryRunner.rollbackTransaction();
    throw error;
  } finally {
    await queryRunner.release();
  }
}

Reach for QueryRunner when you need something transaction() can't express cleanly: conditional commits based on logic outside the callback, or a transaction spanning multiple methods on a request-scoped service. For everything else, it's more code for the same guarantee, and the manual try/catch/finally is boilerplate that gets copy-pasted incorrectly the third time someone needs it. Default to transaction() and only drop down when you have a concrete reason.

Keeping transactions out of your business logic

The awkward part of the example above is that OrdersService now knows about manager.decrement and InventoryItem directly, which undoes some of the separation the repository pattern was supposed to give you. The fix is to let repository methods accept an optional EntityManager (or QueryRunner) and fall back to the injected default when none is passed:

// src/modules/inventory/inventory.repository.ts
import { Injectable } from '@nestjs/common';
import { DataSource, EntityManager } from 'typeorm';
import { InventoryItem } from './entities/inventory-item.entity';

@Injectable()
export class InventoryRepository {
  constructor(private readonly dataSource: DataSource) {}

  async reserveStock(sku: string, quantity: number, manager?: EntityManager) {
    const runner = manager ?? this.dataSource.manager;
    const result = await runner
      .createQueryBuilder()
      .update(InventoryItem)
      .set({ quantity: () => 'quantity - :quantity' })
      .where('sku = :sku AND quantity >= :quantity', { sku, quantity })
      .setParameters({ sku, quantity })
      .execute();

    return result.affected === 1;
  }
}

The service then owns the transaction boundary and passes its manager down to each repository call:

// src/modules/orders/orders.service.ts
async placeOrder(userId: string, items: { sku: string; quantity: number }[]) {
  return this.dataSource.transaction(async (manager) => {
    const order = await this.ordersRepository.create({ userId }, manager);

    for (const item of items) {
      const reserved = await this.inventoryRepository.reserveStock(
        item.sku,
        item.quantity,
        manager,
      );
      if (!reserved) {
        throw new Error(`Insufficient stock for SKU ${item.sku}`);
      }
    }

    return order;
  });
}

This keeps the transaction boundary at the service layer, which is where it belongs: the service knows what "placing an order" means as a business operation, and repositories stay focused on their own tables. The rule worth enforcing across a codebase: any repository method with side effects should accept an optional manager parameter from day one, even before a caller needs it. Retrofitting that signature across a dozen call sites later is a bigger diff than adding one optional parameter up front.

Isolation levels and where the defaults quietly hurt you

Postgres defaults every transaction to READ COMMITTED, which means a query inside your transaction can see rows committed by other transactions between two statements in the same transaction. For most CRUD operations that's fine and it's also the fastest option, since it takes the fewest locks and never aborts a transaction just because another one ran concurrently.

It stops being fine once your logic depends on a value staying stable across multiple statements, the classic case being "read the current balance, then write a new balance based on it." Under READ COMMITTED, two concurrent transactions can both read the same balance, both compute their own update, and the second write silently overwrites the first, with money effectively disappearing. Two ways to fix this, and they trade off differently:

Row-level locking with SELECT ... FOR UPDATE is the narrower fix. It locks only the specific rows you read, so other transactions touching unrelated rows are unaffected, and it fails fast: a concurrent transaction trying to lock the same row blocks until yours commits or rolls back, rather than silently proceeding.

async transferFunds(fromId: string, toId: string, amount: number) {
  return this.dataSource.transaction(async (manager) => {
    const from = await manager
      .createQueryBuilder(Wallet, 'wallet')
      .setLock('pessimistic_write')
      .where('wallet.id = :id', { id: fromId })
      .getOne();

    if (!from || from.balance < amount) {
      throw new Error('Insufficient funds');
    }

    await manager.decrement(Wallet, { id: fromId }, 'balance', amount);
    await manager.increment(Wallet, { id: toId }, 'balance', amount);
  });
}

SERIALIZABLE isolation is the broader fix: it makes Postgres behave as if every transaction ran one at a time, and it detects the whole class of anomalies row locking might miss, at the cost of aborting transactions with a serialization failure whenever it detects a conflict, which means your application code has to be ready to retry. SERIALIZABLE is worth reaching for when the invariant you're protecting is genuinely complex (multiple tables, multiple conditions) and worth the retry logic. For a single balance check like the example above, row locking is simpler and cheaper, and it gets the job done.

Production pitfalls that show up under real load

In practice, the most common transaction bug is a transaction held open too long. Every open transaction holds a connection from your pool and, depending on the isolation level and the rows touched, holds locks that block other transactions. Making an HTTP call to a payment provider from inside a transaction means that connection sits idle, waiting on a network round trip that has nothing to do with your database, while every other request competing for that pool connection waits behind it. Do the external call before you open the transaction, or after it commits, and only wrap the database writes themselves.

The second pitfall is deadlocks from inconsistent lock ordering. If one transaction locks wallet A then wallet B, and a concurrent transaction locks B then A, Postgres detects the cycle and aborts one of them. The fix is boring and effective: always acquire locks in a consistent order, typically by sorting on primary key before locking, so two transactions touching the same two rows always approach them the same way.

The third is treating a caught transaction error as the end of the story. A serialization failure or a deadlock abort is often transient and safe to retry; a unique constraint violation from real duplicate data is not. Distinguish between the two before deciding whether to retry, log, or surface an error to the caller, and cap retries with backoff so a genuinely conflicting workload doesn't spin forever.

Finally, a database transaction only protects your database. If "placing an order" also means calling a payment API or publishing an event, a local transaction can't roll those back if your database commit later fails. That's a distributed consistency problem, not a transaction problem, and the outbox pattern article later in this series covers keeping a database write and an external side effect consistent without a distributed transaction.