Learn NestJS - Transaction & Advanced Persistence

#Introduction

Database operations involving multiple steps must be consistent — if one step fails, all of them must be undone. That's where transactions come in. Episode 10 covers transactions in TypeORM, unit of work, query optimization, and connection pooling.

These concepts matter for applications that process money, stock, or data requiring strict consistency.

#Transaction Management

#Why Transactions Are Needed

Imagine a money transfer: the sender's balance is decreased and the receiver's balance is increased. If the second step fails, the sender's balance drops without anyone receiving it. Transactions ensure both operations either succeed together or fail together.

#Transactions with DataSource

TypeORM provides a transaction API via DataSource:

Transaksi memakai DataSource

import { Injectable } from "@nestjs/common";
import { InjectDataSource } from "@nestjs/typeorm";
import { DataSource } from "typeorm";
 
@Injectable()
export class TransferService {
  constructor(
    @InjectDataSource()
    private readonly dataSource: DataSource,
  ) {}
 
  async transfer(fromId: number, toId: number, amount: number): Promise<void> {
    await this.dataSource.transaction(async (manager) => {
      const from = await manager.findOneBy(User, { id: fromId });
      const to = await manager.findOneBy(User, { id: toId });
 
      from.balance -= amount;
      to.balance += amount;
 
      await manager.save([from, to]);
    });
  }
}

The callback receiving manager runs all operations in a single transaction. If any exception occurs, the transaction is rolled back automatically.

#Unit of Work and Repository Orchestration

#The Unit of Work Concept

A Unit of Work groups several operations into one logical unit. TypeORM's EntityManager is an implementation of unit of work — all changes within a transaction are managed by a single manager.

Orchestrasi beberapa repository

async createOrder(orderData: OrderDto): Promise<void> {
  await this.dataSource.transaction(async (manager) => {
    const order = manager.create(Order, orderData);
    await manager.save(order);
 
    for (const item of orderData.items) {
      await manager.decrement(Product, { id: item.productId }, "stock", item.qty);
    }
  });
}

Within one transaction, the service coordinates creating the order while simultaneously reducing stock — an example of orchestration across repositories.

#Query Optimization

#Eager Loading

Relations can be loaded together with the main entity using relations:

Eager loading relasi

async findOrdersWithUser(): Promise<Order[]> {
  return this.ordersRepository.find({
    relations: {
      user: true,
      items: true,
    },
  });
}

Loading user and items at once avoids the N+1 problem — many small queries that slow the application down.

#Lazy Loading

Alternatively, lazy relations are loaded only when accessed:

Relasi lazy

@Entity()
export class Order {
  @ManyToOne(() => User)
  user: Promise<User>;
}

With Promise<User>, the relation is loaded when awaited. Be careful with lazy loading because it can trigger unexpected queries inside loops.

#Indexes and Efficient Queries

Always add indexes to columns that are frequently filtered:

Entity dengan index

@Entity()
export class User {
  @Column()
  name: string;
 
  @Index()
  @Column({ unique: true })
  email: string;
}

An index significantly speeds up searches based on email, especially on large tables.

#Connection Pooling and Performance Tuning

#Database Connection Pools

A connection pool keeps a set of reusable database connections, reducing the overhead of creating a new connection for every request:

Konfigurasi pool di TypeORM

TypeOrmModule.forRoot({
  type: "postgres",
  url: process.env.DATABASE_URL,
  autoLoadEntities: true,
  synchronize: false,
  poolSize: 10,
})

poolSize: 10 limits the number of concurrent connections. Adjust it to your load and database capacity.

#Other Performance Tuning

A few tuning practices: turn off synchronize in production, limit results with take and skip, avoid unnecessary select *, and use the query builder for complex queries. This combination keeps the application responsive as data grows.

#Conclusion

Episode 10 strengthens your persistence skills: transactions for consistency, unit of work for orchestration, query optimization for speed, and connection pooling for scalability.

Key takeaways:

• Transactions ensure multi-step operations are consistent or fail together.
• dataSource.transaction accepts a callback with manager.
• Unit of work coordinates multiple repositories in one transaction.
• Eager loading avoids the N+1 problem.
• Indexes speed up queries on frequently filtered columns.
• Connection pools reuse connections and reduce overhead.

In the next episode 11 we'll discuss background jobs and scheduling — task scheduling with @nestjs/schedule, queue processing with Bull or BullMQ, retry policies and delayed jobs, plus monitoring jobs and failure handling.