Connecting PostgreSQL with TypeORM

Last time in the NestJS Production Guide, I covered Role and Permission Authorization, which assumed a user record existed somewhere to check roles against. This article is where that assumption becomes real: wiring a NestJS application to PostgreSQL through TypeORM, so entities, repositories, and connection pooling stop being an abstraction and start being code you have to run in production.

TypeORM is one of two realistic choices for a NestJS project going to PostgreSQL, the other being Prisma, and I've covered that decision separately. This article assumes you've already picked TypeORM, either because the project needs the Active Record or Data Mapper flexibility, an existing team is already fluent in it, or the codebase predates Prisma's rise in the NestJS ecosystem. The goal here is getting the connection, entity design, and repository layer right the first time. Retrofitting connection pooling or fixing an N+1 query pattern after the schema has grown costs a lot more than doing it correctly at the start.

Setting up the connection

NestJS's @nestjs/typeorm package wraps TypeORM's DataSource in a module that plugs into Nest's dependency injection container, so repositories become injectable providers instead of something you import and instantiate manually. The setup starts in AppModule, but the connection details should never be hardcoded there.

// src/app.module.ts
import { Module } from '@nestjs/common';
import { ConfigModule, ConfigService } from '@nestjs/config';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    ConfigModule.forRoot({ isGlobal: true }),
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (config: ConfigService) => ({
        type: 'postgres',
        host: config.get<string>('DB_HOST'),
        port: config.get<number>('DB_PORT'),
        username: config.get<string>('DB_USERNAME'),
        password: config.get<string>('DB_PASSWORD'),
        database: config.get<string>('DB_NAME'),
        entities: [__dirname + '/**/*.entity{.ts,.js}'],
        synchronize: false,
        ssl: config.get<string>('DB_SSL') === 'true' ? { rejectUnauthorized: false } : false,
        poolSize: config.get<number>('DB_POOL_SIZE', 10),
      }),
    }),
  ],
})
export class AppModule {}

forRootAsync with ConfigService injected is the version worth defaulting to, because it means the connection reads from environment variables through the same configuration pipeline as the rest of the app rather than a separate hardcoded config file. synchronize: false is not optional in anything that runs in production. It tells TypeORM to stop auto-generating schema changes from your entity definitions, which is convenient in a local sandbox and genuinely dangerous once real data exists, since a dropped column in an entity can silently drop a column in the live table. Migrations, covered in the next article in this series, are the correct replacement.

poolSize deserves a specific mention because it's easy to leave at the driver's default and forget about. Each Nest instance holds its own pool of Postgres connections, and Postgres itself has a hard cap on total connections, usually somewhere around 100 by default depending on your instance size. If you're running several replicas of the same service, each with a pool of 10, you've already committed to dozens of connections before any other service touches the database. That adds up fast. Size the pool based on how many app instances will run concurrently, not based on a default that assumes a single instance.

Defining entities

An entity is a class decorated to describe a table, and TypeORM uses that metadata to generate queries and, when synchronize is on, schema itself. The decorators carry real intent: they're where column types, nullability, and relationships get declared once instead of scattered across raw SQL in every query.

// src/modules/users/entities/user.entity.ts
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  Index,
  OneToMany,
} from 'typeorm';
import { Order } from '../../orders/entities/order.entity';

@Entity('users')
@Index(['email'], { unique: true })
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ type: 'varchar', length: 255 })
  email: string;

  @Column({ type: 'varchar', length: 255, select: false })
  passwordHash: string;

  @Column({ type: 'varchar', length: 50, default: 'member' })
  role: string;

  @OneToMany(() => Order, (order) => order.user)
  orders: Order[];

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}

A few of these decisions are worth calling out because they're easy to skip and expensive to fix later. select: false on passwordHash means a plain find or findOne never returns that column unless a query explicitly asks for it with addSelect, which closes off an entire class of accidental leaks where a hash ends up serialized into an API response because someone returned the whole entity. The unique index on email is a database constraint, not an application-level check, and it holds even if two requests race past a service-layer uniqueness check at the same time. Application code should still validate uniqueness for a clean error message. But the database index is what actually prevents the duplicate row.

PrimaryGeneratedColumn('uuid') over an auto-incrementing integer is worth defaulting to for anything user-facing or multi-tenant. Sequential integer IDs leak information (how many users exist, roughly when a row was created relative to another) and make IDs guessable in a way UUIDs don't. The tradeoff is index size and insert locality; UUIDs are larger and, unless you're using a sequential variant like UUIDv7, don't cluster well on insert. That's a real cost. For most SaaS-scale tables, though, it's not the bottleneck worth optimizing around first.

Repositories and the query layer

@nestjs/typeorm lets you inject a repository for any entity directly into a service, which is where the actual query logic should live, not in the controller.

// src/modules/users/users.module.ts
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from './entities/user.entity';
import { UsersService } from './users.service';

@Module({
  imports: [TypeOrmModule.forFeature([User])],
  providers: [UsersService],
  exports: [UsersService],
})
export class UsersModule {}

// src/modules/users/users.service.ts
import { Injectable, ConflictException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './entities/user.entity';

@Injectable()
export class UsersService {
  constructor(
    @InjectRepository(User)
    private readonly usersRepository: Repository<User>,
  ) {}

  async findByEmail(email: string): Promise<User | null> {
    return this.usersRepository.findOne({ where: { email } });
  }

  async create(email: string, passwordHash: string): Promise<User> {
    const existing = await this.findByEmail(email);
    if (existing) {
      throw new ConflictException('Email already in use');
    }

    const user = this.usersRepository.create({ email, passwordHash });
    return this.usersRepository.save(user);
  }
}

TypeOrmModule.forFeature([User]) registers the repository as a provider scoped to that module, which is why UsersModule needs to import it before UsersService can inject Repository<User>. This is a common early stumbling block: forgetting forFeature in the module that owns the entity produces a dependency injection error that doesn't obviously point back to a missing import.

The repository pattern here (usersRepository.findOne, .save, .create) covers straightforward CRUD well. Where it starts to strain is anything involving joins across several tables, aggregations, or conditional filtering built up from several optional parameters. TypeORM's QueryBuilder handles that case:

// src/modules/orders/orders.service.ts
async findOrdersForUser(userId: string, status?: string): Promise<Order[]> {
  const query = this.ordersRepository
    .createQueryBuilder('order')
    .leftJoinAndSelect('order.items', 'item')
    .where('order.userId = :userId', { userId });

  if (status) {
    query.andWhere('order.status = :status', { status });
  }

  return query.orderBy('order.createdAt', 'DESC').getMany();
}

QueryBuilder gives you closer control over the generated SQL, which matters once a query needs to be readable in EXPLAIN ANALYZE output rather than reverse-engineered from a chain of repository methods. It's also more verbose, so reach for it when the repository API genuinely can't express what you need, not as a default starting point for every query.

Relations, lazy loading, and the N+1 trap

OneToMany, ManyToOne, and ManyToMany decorators describe relationships in the entity, but the way you load them in a query is where most of the actual performance risk lives. TypeORM defaults to not loading relations at all unless you ask, which is the safer default, but it's easy to ask for a relation in a way that quietly issues one query per row instead of one query total.

// N+1 risk: relation loaded lazily inside a loop
const orders = await this.ordersRepository.find();
for (const order of orders) {
  const items = await order.items;
}

// One query, relation joined up front
const orders = await this.ordersRepository.find({
  relations: { items: true },
});

The first version, if items is configured as a lazy relation (a Promise property on the entity), issues a separate query for every order in the loop. That's invisible in local development with ten rows and a very real production incident with ten thousand. The fix is either eager relations declared with relations on the query, or a QueryBuilder with an explicit leftJoinAndSelect, both of which turn N+1 queries into one join. Turn on query logging in a staging environment periodically and count how many statements a single request actually issues; it's the fastest way to catch this before it reaches production traffic.

Transactions for multi-step writes

Any operation that touches more than one table and needs to succeed or fail as a unit belongs in a transaction. TypeORM exposes this through DataSource.transaction or an injected QueryRunner for more granular control.

// src/modules/orders/orders.service.ts
import { DataSource } from 'typeorm';

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

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

      for (const item of items) {
        await manager.decrement(
          Product,
          { id: item.productId },
          'stock',
          item.quantity,
        );
      }

      return savedOrder;
    });
  }
}

Everything inside the callback runs against the same connection and the same transaction; if any step throws, TypeORM rolls the whole thing back. This matters for anything resembling inventory decrements, ledger entries, or subscription state changes, where a partial write leaves the database in a state that doesn't correspond to anything that actually happened in the real world. The pitfall worth flagging: don't mix the injected repository (which uses the module's default connection) with the transactional manager inside the same operation. Every read and write inside the transaction needs to go through manager, or you'll end up querying a connection that can't see the uncommitted rows the transaction just wrote.

Production pitfalls

The synchronize: true mistake shows up more than it should, usually because it's the default in a lot of tutorials and nobody flips it off before deploying. Leave it on locally if you want, but gate it explicitly behind an environment check so it can never accidentally run against a database with real rows in it.

Connection pool exhaustion is the second common one, and it rarely announces itself clearly. Symptoms look like requests timing out under load with no obvious error in application logs, because the app is just waiting for a connection that never frees up. This tends to compound with the N+1 problem above: extra queries per request eat pool capacity faster than expected under real traffic. Size the pool deliberately and monitor active connections against the pool limit, alongside query latency.

The third is treating every entity relationship as something that should always be joined and hydrated. Loading a User with every associated Order on every request that just needs the user's name is wasted work at any real scale. Load relations deliberately, per query, based on what that specific endpoint actually needs, rather than defaulting every fetch to the fully populated object graph.