Chapter 5: Database Integration

Integrating a database is a core part of any backend application. NestJS supports multiple ORMs, with TypeORM and Prisma being the most popular. This chapter covers setup, modeling, querying, transactions, migrations, relationships, and best practices for both. We’ll walk through the process step by step, so you can confidently connect your app to a database.

5.1 Choosing an ORM

Before diving into setup, understand the differences between TypeORM and Prisma to choose the right tool for your project.

TypeORM vs Prisma

Choosing an ORM is one of those decisions that is hard to reverse later, so it pays to understand the trade-offs upfront. Think of it this way: TypeORM is like a Swiss Army knife — lots of tools, lots of flexibility, but you need to know which blade to pull out. Prisma is like a purpose-built power tool — fewer options, but the ones it has are exceptionally well-designed and type-safe. TypeORM:

Active Record and Data Mapper patterns — you choose the architectural style that fits your team
Decorator-based entity definitions — feels natural if you are coming from NestJS or Angular
Mature ecosystem with many features (subscribers, listeners, custom repositories)
Flexible query builder for complex SQL that ORMs usually struggle with
Works with TypeScript and JavaScript — broader adoption
Good for complex queries, legacy databases, and teams that want fine-grained SQL control

Prisma:

Type-safe database client — your IDE catches query errors before you even run the code
Schema-first approach — a single schema.prisma file is the source of truth for your data model
Excellent developer experience — Prisma Studio for visual data browsing, automatic migrations
Automatic client generation — run prisma generate and get a perfectly typed client
Great for rapid development and teams that prioritize type safety over SQL flexibility
Strong TypeScript support — arguably the best TypeScript database experience available

When to Use TypeORM:

Complex queries with joins, subqueries, and raw SQL
You prefer the decorator pattern (consistent with the rest of NestJS)
Working with an existing database schema you cannot change
Need database-specific features (stored procedures, materialized views)

When to Use Prisma:

Starting a new project with a clean schema
Type safety is a top priority (fewer runtime query errors)
Rapid prototyping — schema changes and migrations are fast
Your team is less experienced with raw SQL

Honest Take: If I am starting a greenfield NestJS project in 2025, I reach for Prisma first. The developer experience and type safety are hard to beat. But if I am working with a legacy database or need complex multi-table joins, TypeORM’s query builder gives me more control where it matters.

Detailed Feature Comparison

Feature	TypeORM	Prisma
Entity Definition	Decorators on classes	`schema.prisma` file
Type Safety	Partial (runtime errors possible)	Full (compile-time query validation)
Raw SQL	`queryBuilder` + `.query()`	`$queryRaw` (typed with `Prisma.sql`)
Migrations	Generate from entity diff, manual edit	Auto-generated from schema diff
Migration Rollback	Manual `down()` methods	`prisma migrate reset` (destructive)
Seeding	Manual scripts	`prisma db seed` (built-in)
Relations (Eager)	`{ eager: true }` on relation	`include:` in query
Relations (Lazy)	Lazy-loaded via promises	Not supported (explicit `include` only)
Transactions	QueryRunner or DataSource.transaction	`$transaction` (sequential or interactive)
Connection Pooling	Built-in via `extra` config	Built-in, auto-managed
Multiple Databases	Multiple DataSource instances	Multiple `schema.prisma` files
MongoDB Support	Yes (experimental)	Yes (stable since Prisma 3.12)
Visual Database Tool	None built-in (use DBeaver, pgAdmin)	Prisma Studio (`npx prisma studio`)
Bundle Size Impact	Moderate	Large (generated client)
NestJS Integration	`@nestjs/typeorm` (official)	Manual PrismaService (simple)

Migration Strategy Decision Framework

Situation	Recommended Approach
Greenfield project, small team	Prisma `migrate dev` — fast iteration, auto-generated migrations
Greenfield project, large team	Either ORM + review migration SQL before applying
Existing database you cannot modify	TypeORM with `synchronize: false` — introspect and map entities to existing tables
Existing database, switching to Prisma	`prisma db pull` to introspect, then manage with Prisma going forward
Need custom SQL in migrations	TypeORM (migration files are plain SQL) or Prisma with `prisma migrate diff` + manual edits
Multiple environments (dev/staging/prod)	Both ORMs: apply migrations in CI pipeline, never run `migrate dev` in production

5.2 Setting Up TypeORM

TypeORM is a mature ORM that works seamlessly with NestJS. Let’s set it up step by step.

Installation

# For PostgreSQL
npm install @nestjs/typeorm typeorm pg

# For MySQL
npm install @nestjs/typeorm typeorm mysql2

# For SQLite
npm install @nestjs/typeorm typeorm sqlite3

Basic Configuration

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

@Module({
  imports: [
    // TypeOrmModule.forRoot() is a dynamic module that creates a database
    // connection pool and registers it as a global provider. Every module
    // in your app can then use TypeOrmModule.forFeature() to access
    // repositories for specific entities.
    TypeOrmModule.forRoot({
      type: 'postgres',
      host: process.env.DB_HOST || 'localhost',
      port: parseInt(process.env.DB_PORT, 10) || 5432,
      username: process.env.DB_USERNAME || 'postgres',
      password: process.env.DB_PASSWORD || 'password',
      database: process.env.DB_NAME || 'mydb',
      entities: [User],
      // CRITICAL: synchronize auto-creates/alters tables to match your entities.
      // This is convenient in development but DANGEROUS in production -- it can
      // drop columns, lose data, or cause downtime. Always use migrations in production.
      synchronize: process.env.NODE_ENV !== 'production',
      logging: process.env.NODE_ENV === 'development',
    }),
  ],
})
export class AppModule {}

Using Configuration Module

Better approach using @nestjs/config:

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

@Module({
  imports: [
    ConfigModule.forRoot({ isGlobal: true }),
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      useFactory: (configService: ConfigService) => ({
        type: 'postgres',
        host: configService.get('DB_HOST'),
        port: configService.get('DB_PORT'),
        username: configService.get('DB_USERNAME'),
        password: configService.get('DB_PASSWORD'),
        database: configService.get('DB_NAME'),
        entities: [__dirname + '/**/*.entity{.ts,.js}'],
        synchronize: configService.get('NODE_ENV') !== 'production',
        logging: configService.get('NODE_ENV') === 'development',
      }),
      inject: [ConfigService],
    }),
  ],
})
export class AppModule {}

Diagram: TypeORM Integration

AppModule
    ↓
TypeOrmModule.forRoot()
    ↓
Database Connection
    ↓
Entities Registered
    ↓
Repository Available

5.3 Defining Entities with TypeORM

Entities define your database structure using decorators.

Basic Entity

Entities are TypeScript classes that map to database tables. Each property decorated with @Column() becomes a column in the table. Think of an entity as a contract between your code and your database — it says “this table has these columns with these types.”

// users/entities/user.entity.ts
import { Entity, Column, PrimaryGeneratedColumn, CreateDateColumn, UpdateDateColumn } from 'typeorm';

// @Entity('users') maps this class to the "users" table.
// Without the string argument, TypeORM uses the class name lowercased.
@Entity('users')
export class User {
  // @PrimaryGeneratedColumn() creates an auto-incrementing primary key.
  // For UUIDs, use @PrimaryGeneratedColumn('uuid') instead.
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  // { unique: true } adds a UNIQUE constraint at the database level.
  // This is your last line of defense -- even if application validation
  // fails, the database will reject duplicate emails.
  @Column({ unique: true })
  email: string;

  // { nullable: true } means this column can be NULL.
  // By default, TypeORM columns are NOT NULL.
  @Column({ nullable: true })
  age: number;

  // @CreateDateColumn() auto-sets the timestamp when the row is first inserted.
  // You never set this manually -- TypeORM handles it.
  @CreateDateColumn()
  createdAt: Date;

  // @UpdateDateColumn() auto-updates the timestamp on every save/update.
  @UpdateDateColumn()
  updatedAt: Date;
}

Column Options

@Entity('users')
export class User {
  @Column({
    type: 'varchar',
    length: 255,
    nullable: false,
    unique: true,
    default: 'guest',
    comment: 'User display name',
  })
  name: string;

  @Column('text')  // Text type
  bio: string;

  @Column('int')   // Integer type
  age: number;

  @Column('decimal', { precision: 10, scale: 2 })
  balance: number;

  @Column('boolean', { default: true })
  isActive: boolean;

  @Column('json', { nullable: true })
  metadata: Record<string, any>;
}

Relationships

One-to-Many:

// users/entities/user.entity.ts
import { Entity, OneToMany } from 'typeorm';
import { Post } from '../../posts/entities/post.entity';

@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @OneToMany(() => Post, post => post.author)
  posts: Post[];
}

// posts/entities/post.entity.ts
import { Entity, ManyToOne, JoinColumn } from 'typeorm';
import { User } from '../../users/entities/user.entity';

@Entity('posts')
export class Post {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @ManyToOne(() => User, user => user.posts)
  @JoinColumn({ name: 'author_id' })
  author: User;

  @Column({ name: 'author_id' })
  authorId: number;
}

Many-to-Many:

// users/entities/user.entity.ts
import { Entity, ManyToMany, JoinTable } from 'typeorm';
import { Role } from '../../roles/entities/role.entity';

@Entity('users')
export class User {
  @ManyToMany(() => Role, role => role.users)
  @JoinTable({
    name: 'user_roles',
    joinColumn: { name: 'user_id', referencedColumnName: 'id' },
    inverseJoinColumn: { name: 'role_id', referencedColumnName: 'id' },
  })
  roles: Role[];
}

// roles/entities/role.entity.ts
import { Entity, ManyToMany } from 'typeorm';
import { User } from '../../users/entities/user.entity';

@Entity('roles')
export class Role {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @ManyToMany(() => User, user => user.roles)
  users: User[];
}

One-to-One:

// users/entities/user.entity.ts
import { Entity, OneToOne, JoinColumn } from 'typeorm';
import { Profile } from '../../profiles/entities/profile.entity';

@Entity('users')
export class User {
  @OneToOne(() => Profile, profile => profile.user, { cascade: true })
  @JoinColumn()
  profile: Profile;
}

5.4 Using Repositories with TypeORM

Repositories provide methods to interact with entities.

Injecting Repository

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

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

Using Repository in Service

// users/users.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './entities/user.entity';
import { CreateUserDto } from './dto/create-user.dto';

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

  async create(createUserDto: CreateUserDto): Promise<User> {
    const user = this.userRepository.create(createUserDto);
    return this.userRepository.save(user);
  }

  async findAll(): Promise<User[]> {
    return this.userRepository.find();
  }

  async findOne(id: number): Promise<User> {
    return this.userRepository.findOne({ where: { id } });
  }

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

  async update(id: number, updateUserDto: Partial<CreateUserDto>): Promise<User> {
    await this.userRepository.update(id, updateUserDto);
    return this.findOne(id);
  }

  async remove(id: number): Promise<void> {
    await this.userRepository.delete(id);
  }
}

Advanced Queries

Query Builder:

async findActiveUsers(): Promise<User[]> {
  return this.userRepository
    .createQueryBuilder('user')
    .where('user.isActive = :isActive', { isActive: true })
    .andWhere('user.age > :age', { age: 18 })
    .orderBy('user.createdAt', 'DESC')
    .getMany();
}

Relations:

async findUserWithPosts(id: number): Promise<User> {
  return this.userRepository.findOne({
    where: { id },
    relations: ['posts'],
  });
}

// Multiple relations
async findUserWithAll(id: number): Promise<User> {
  return this.userRepository.findOne({
    where: { id },
    relations: ['posts', 'profile', 'roles'],
  });
}

Pagination:

async findAllPaginated(page: number = 1, limit: number = 10) {
  const [users, total] = await this.userRepository.findAndCount({
    skip: (page - 1) * limit,
    take: limit,
    order: { createdAt: 'DESC' },
  });

  return {
    data: users,
    total,
    page,
    limit,
    totalPages: Math.ceil(total / limit),
  };
}

5.5 Setting Up Prisma

Prisma provides a type-safe database client with excellent developer experience.

Installation

npm install @prisma/client prisma
npx prisma init

Schema Definition

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  age       Int?
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  posts     Post[]
  profile   Profile?
  roles     Role[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  userId Int    @unique
  user   User   @relation(fields: [userId], references: [id])
}

model Role {
  id    Int    @id @default(autoincrement())
  name  String @unique
  users User[]
}

Prisma Service

The PrismaService is the bridge between NestJS’s DI system and Prisma’s client. By extending PrismaClient and implementing NestJS lifecycle hooks, we get automatic connection management — the database connects when the module initializes and disconnects when the app shuts down.

// prisma/prisma.service.ts
import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
// Extending PrismaClient gives this service all of Prisma's query methods
// (this.user.findMany(), this.post.create(), etc.)
// Implementing OnModuleInit/OnModuleDestroy hooks into NestJS lifecycle.
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() {
    // Called automatically when the module is initialized.
    // Opens the database connection pool.
    await this.$connect();
  }

  async onModuleDestroy() {
    // Called automatically on app shutdown (SIGTERM, SIGINT).
    // Cleanly closes all database connections to prevent resource leaks.
    await this.$disconnect();
  }
}

Practical Tip: Register the PrismaService in a @Global() PrismaModule so every feature module can inject it without explicit imports. This mirrors how @nestjs/config’s ConfigModule works.

Registering Prisma Service

// prisma/prisma.module.ts
import { Module, Global } from '@nestjs/common';
import { PrismaService } from './prisma.service';

@Global()
@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

Diagram: Prisma Integration

AppModule
    ↓
PrismaModule
    ↓
PrismaService
    ↓
Prisma Client
    ↓
Database

5.6 Using Prisma in Services

Prisma provides a type-safe, intuitive API for database operations.

Basic CRUD Operations

// users/users.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';

@Injectable()
export class UsersService {
  constructor(private prisma: PrismaService) {}

  async create(createUserDto: CreateUserDto) {
    return this.prisma.user.create({
      data: createUserDto,
    });
  }

  async findAll() {
    return this.prisma.user.findMany();
  }

  async findOne(id: number) {
    return this.prisma.user.findUnique({
      where: { id },
    });
  }

  async update(id: number, updateUserDto: UpdateUserDto) {
    return this.prisma.user.update({
      where: { id },
      data: updateUserDto,
    });
  }

  async remove(id: number) {
    return this.prisma.user.delete({
      where: { id },
    });
  }
}

Advanced Queries

Including Relations:

async findUserWithPosts(id: number) {
  return this.prisma.user.findUnique({
    where: { id },
    include: {
      posts: true,
      profile: true,
      roles: true,
    },
  });
}

Filtering:

async findActiveUsers() {
  return this.prisma.user.findMany({
    where: {
      isActive: true,
      age: {
        gt: 18,
      },
    },
    orderBy: {
      createdAt: 'desc',
    },
  });
}

Pagination:

async findAllPaginated(page: number = 1, limit: number = 10) {
  const skip = (page - 1) * limit;

  const [users, total] = await Promise.all([
    this.prisma.user.findMany({
      skip,
      take: limit,
      orderBy: { createdAt: 'desc' },
    }),
    this.prisma.user.count(),
  ]);

  return {
    data: users,
    total,
    page,
    limit,
    totalPages: Math.ceil(total / limit),
  };
}

Complex Queries:

async searchUsers(query: string) {
  return this.prisma.user.findMany({
    where: {
      OR: [
        { name: { contains: query, mode: 'insensitive' } },
        { email: { contains: query, mode: 'insensitive' } },
      ],
    },
  });
}

5.7 Transactions

Transactions ensure multiple operations succeed or fail together.

TypeORM Transactions

Using DataSource:

import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';

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

  async createUserWithProfile(userData: CreateUserDto, profileData: CreateProfileDto) {
    return this.dataSource.transaction(async manager => {
      const user = manager.create(User, userData);
      const savedUser = await manager.save(user);

      const profile = manager.create(Profile, {
        ...profileData,
        userId: savedUser.id,
      });
      await manager.save(profile);

      return savedUser;
    });
  }
}

Using Query Runner:

async createUserWithProfile(userData: CreateUserDto, profileData: CreateProfileDto) {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction();

  try {
    const user = queryRunner.manager.create(User, userData);
    const savedUser = await queryRunner.manager.save(user);

    const profile = queryRunner.manager.create(Profile, {
      ...profileData,
      userId: savedUser.id,
    });
    await queryRunner.manager.save(profile);

    await queryRunner.commitTransaction();
    return savedUser;
  } catch (err) {
    await queryRunner.rollbackTransaction();
    throw err;
  } finally {
    await queryRunner.release();
  }
}

Prisma Transactions

Sequential Operations:

async createUserWithProfile(userData: CreateUserDto, profileData: CreateProfileDto) {
  return this.prisma.$transaction(async (tx) => {
    const user = await tx.user.create({
      data: userData,
    });

    const profile = await tx.profile.create({
      data: {
        ...profileData,
        userId: user.id,
      },
    });

    return { user, profile };
  });
}

Batch Operations:

async createMultipleUsers(usersData: CreateUserDto[]) {
  return this.prisma.$transaction(
    usersData.map(userData =>
      this.prisma.user.create({ data: userData })
    ),
  );
}

Interactive Transactions:

async transferBalance(fromId: number, toId: number, amount: number) {
  return this.prisma.$transaction(async (tx) => {
    const fromUser = await tx.user.findUnique({ where: { id: fromId } });
    const toUser = await tx.user.findUnique({ where: { id: toId } });

    if (fromUser.balance < amount) {
      throw new Error('Insufficient balance');
    }

    await tx.user.update({
      where: { id: fromId },
      data: { balance: { decrement: amount } },
    });

    await tx.user.update({
      where: { id: toId },
      data: { balance: { increment: amount } },
    });

    return { success: true };
  });
}

Transaction Edge Cases

Edge Case 1: Transactions and connection pool exhaustion Every open transaction holds a database connection. If your transaction calls an external API (payment gateway, email service) that takes 10 seconds, that connection is blocked for 10 seconds. Under load, this exhausts the connection pool and cascades into failures for all requests. Rule: keep transactions as short as possible. Do external calls before or after the transaction, not inside it.

// BAD: External API call inside transaction holds the connection
await this.dataSource.transaction(async manager => {
  const order = await manager.save(Order, orderData);
  await this.paymentGateway.charge(order.total); // 2-5 second external call
  await manager.save(Payment, paymentData);
});

// GOOD: External call outside transaction
const paymentResult = await this.paymentGateway.charge(orderData.total);
await this.dataSource.transaction(async manager => {
  const order = await manager.save(Order, orderData);
  await manager.save(Payment, { ...paymentData, gatewayId: paymentResult.id });
});

Edge Case 2: Prisma’s $transaction timeout Prisma interactive transactions have a default timeout of 5 seconds. If your transaction exceeds this, it is automatically rolled back. Increase it for complex operations:

await this.prisma.$transaction(async (tx) => {
  // complex operations...
}, {
  timeout: 15000,          // 15 seconds
  maxWait: 5000,           // Max time to wait for a connection from the pool
  isolationLevel: 'Serializable', // Strictest isolation for financial operations
});

Edge Case 3: TypeORM save() vs insert() in transactions save() does a SELECT first to check if the entity exists, then INSERT or UPDATE. Inside a tight loop, this doubles your queries. Use insert() when you know the entity is new, and update() when you know it exists. save() is convenient but expensive at scale.

5.8 Migrations

Migrations keep your database schema in sync with your code.

TypeORM Migrations

Generate Migration:

npx typeorm migration:generate -n CreateUsers

Migration File:

import { MigrationInterface, QueryRunner } from 'typeorm';

export class CreateUsers1234567890 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      CREATE TABLE "users" (
        "id" SERIAL NOT NULL,
        "name" character varying NOT NULL,
        "email" character varying NOT NULL,
        "createdAt" TIMESTAMP NOT NULL DEFAULT now(),
        CONSTRAINT "PK_users" PRIMARY KEY ("id")
      )
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP TABLE "users"`);
  }
}

Run Migrations:

npx typeorm migration:run
npx typeorm migration:revert

Prisma Migrations

Create Migration:

npx prisma migrate dev --name init

Apply Migrations:

npx prisma migrate deploy

Reset Database:

npx prisma migrate reset

Generate Client: After schema changes:

npx prisma generate

5.9 Best Practices

Following best practices ensures your database integration is robust and maintainable.

Use Environment Variables

Never hard-code database credentials:

// .env
DB_HOST=localhost
DB_PORT=5432
DB_USERNAME=postgres
DB_PASSWORD=secret
DB_NAME=mydb
DATABASE_URL=postgresql://postgres:secret@localhost:5432/mydb

Connection Pooling

Configure connection pooling for production:

TypeOrmModule.forRoot({
  // ... other options
  extra: {
    max: 10,  // Maximum connections
    min: 2,   // Minimum connections
    idleTimeoutMillis: 30000,
  },
})

Use Migrations

Never use synchronize: true in production:

// Development
synchronize: true

// Production
synchronize: false  // Use migrations instead

Index Optimization

Add indexes for frequently queried columns:

// TypeORM
@Index()
@Column()
email: string;

// Prisma
model User {
  email String @unique @map("email_idx")
}

Query Optimization

TypeORM:

// Use select to limit fields
this.userRepository.find({
  select: ['id', 'name', 'email'],
});

// Use relations carefully
this.userRepository.find({
  relations: ['posts'],  // Only load what you need
});

Prisma:

// Select specific fields
this.prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
  },
});

// Use include judiciously
this.prisma.user.findMany({
  include: {
    posts: true,  // Only include what you need
  },
});

Query Performance Decision Table

Symptom	Likely Cause	Fix
Endpoint returns 50+ fields but frontend uses 5	Over-fetching (no `select`)	Add `select` to limit returned columns
Loading a list of items is slow despite small result set	Missing database index	Add index on WHERE/ORDER BY columns
Loading an entity with relations triggers N+1 queries	Lazy loading or separate queries per relation	Use `relations` (TypeORM) or `include` (Prisma) for eager loading
Pagination on large tables is slow at high page numbers	`OFFSET` scans and discards rows	Use cursor-based pagination (WHERE id > last_seen_id)
Writes are slow on tables with many indexes	Each INSERT/UPDATE maintains all indexes	Review indexes, remove unused ones, consider partial indexes
Read-heavy endpoint causes write contention	Reads and writes compete for locks	Add a read replica, route reads to it
Memory usage spikes when loading large datasets	Loading all rows into memory	Stream results (`createQueryBuilder().stream()` in TypeORM)

Error Handling

Database errors are cryptic by default — your users should never see “ERROR: duplicate key value violates unique constraint.” Translate database-level errors into meaningful HTTP exceptions.

async create(dto: CreateUserDto) {
  try {
    return await this.userRepository.save(dto);
  } catch (error) {
    // PostgreSQL error codes: 23505 = unique_violation, 23503 = foreign_key_violation
    // MySQL error codes are different (1062 for duplicate key).
    // Consider creating a reusable error handler if you support multiple databases.
    if (error.code === '23505') {
      throw new ConflictException('Email already exists');
    }
    if (error.code === '23503') {
      throw new BadRequestException('Referenced record does not exist');
    }
    // For unexpected errors, log the full error for debugging but return
    // a generic message to the client. Never expose internal details.
    this.logger.error('Database error during user creation', error.stack);
    throw new InternalServerErrorException('Failed to create user');
  }
}

Common Mistake: Catching all errors silently and returning a generic 500. Always translate known error codes (unique violations, foreign key errors) into specific 4xx responses so your API consumers can handle them programmatically.

Testing

Use test databases for integration tests:

// test database configuration
TypeOrmModule.forRoot({
  type: 'sqlite',
  database: ':memory:',
  entities: [User],
  synchronize: true,
})

5.10 Summary

You’ve learned how to integrate databases with NestJS: Key Concepts:

TypeORM: Decorator-based ORM with flexible queries
Prisma: Type-safe database client with excellent DX
Entities/Models: Define database structure
Repositories: Abstract data access
Relationships: One-to-one, one-to-many, many-to-many
Transactions: Ensure data consistency
Migrations: Version control for database schema

Best Practices:

Use environment variables for configuration
Use migrations, never synchronize in production
Optimize queries and add indexes
Handle errors gracefully
Use connection pooling in production
Write integration tests

Next Chapter: Learn about authentication, authorization, JWT, OAuth, and security best practices.

Interview Deep-Dive

Walk me through your decision process for choosing between TypeORM and Prisma for a new NestJS project.

Strong Answer:

The decision starts with the team and project constraints. If the team is experienced with SQL and needs complex joins, raw queries, or stored procedures, TypeORM’s query builder gives more control. If the team prioritizes developer experience and type safety, Prisma’s generated client catches query errors at compile time.
For greenfield projects, I lean toward Prisma. The schema-first approach means a single schema.prisma file is the source of truth. Migrations are automatic, the generated client is fully typed, and Prisma Studio provides a visual data browser for debugging.
TypeORM’s strength is flexibility with legacy databases. If I am integrating with an existing database that has 200 tables, custom naming conventions, and stored procedures, TypeORM handles it better. Prisma’s introspection can reverse-engineer a schema, but complex views and stored procedures require workarounds.
The setup includes: connection pooling (max connections based on available connections divided by app instances), retry logic, and a health check that pings the database.

Follow-up: Six months after choosing Prisma, the team needs a complex recursive CTE query. What do you do?Prisma supports raw queries via prisma.$queryRaw. For a recursive CTE, write the raw SQL and type the result manually. This loses Prisma’s type safety for that specific query, but it is a pragmatic escape hatch. If the team needs many raw queries, consider using Prisma for simple CRUD and a lightweight query builder like Kysely for complex queries — they can share the same connection pool.

Explain database transactions in NestJS. What are the gotchas with Prisma's interactive transactions?

Strong Answer:

A transaction ensures multiple database operations either all succeed or all fail. In NestJS with TypeORM, use dataSource.transaction(async (manager) => { ... }). The critical mistake is using the regular repository inside the callback — those queries run outside the transaction.
In Prisma, use prisma.$transaction(async (tx) => { ... }). The gotcha: Prisma’s interactive transactions have a default timeout of 5 seconds. If your transaction takes longer, it is automatically rolled back. Increase with { timeout: 30000 }.
Another gotcha: the array form prisma.$transaction([...]) runs operations in parallel without conditional logic. The callback form runs sequentially with if/else support.
The production rule: keep transactions short. Never call external APIs inside a transaction — they hold database connections and locks. Fetch external data before starting the transaction, then do all writes inside it.

Follow-up: How would you implement optimistic locking in NestJS to prevent concurrent update conflicts?Add a version column to the entity. When updating, include the version in the WHERE clause: UPDATE users SET name = 'new', version = version + 1 WHERE id = 1 AND version = 5. If another user changed the record, the WHERE matches zero rows. In Prisma, use prisma.user.update({ where: { id: 1, version: 5 }, data: { name: 'new', version: { increment: 1 } } }). Prisma throws P2025 if no rows match, which you catch and return 409 Conflict.

Your NestJS API is slow and database queries are the bottleneck. Walk me through your diagnosis and optimization approach.

Strong Answer:

Step 1: Enable query logging. In TypeORM: logging: true. In Prisma: prisma.$on('query', (e) => console.log(e.query, e.duration)). This shows every query and its duration.
Step 2: Look for N+1 queries — the most common ORM performance problem. Loading 100 users then loading each user’s posts in a loop is 101 queries. Fix with eager loading: TypeORM relations: ['posts'], Prisma include: { posts: true }.
Step 3: Check missing indexes. Run EXPLAIN ANALYZE on slow queries. “Seq Scan” on millions of rows means you need an index. TypeORM: @Index(). Prisma: @@index([email]).
Step 4: Optimize SELECT. Only fetch needed columns. Prisma: select: { id: true, name: true }.
Step 5: Add pagination. Never load all rows from large tables.
Step 6: Consider caching frequent, rarely-changing queries in Redis.

Follow-up: How do you handle the N+1 problem specifically in a GraphQL NestJS application?GraphQL makes N+1 worse because the client controls which fields to fetch. The solution is DataLoader, which batches individual lookups into a single query per event loop tick. When a resolver calls dataLoader.load(userId), DataLoader collects all requested IDs and fires one WHERE author_id IN (1, 2, 3, ...) query. Register DataLoaders as request-scoped providers to avoid cache leaks between requests.

Why should you never use synchronize: true in production, and what is your migration workflow?

Strong Answer:

synchronize: true tells TypeORM to auto-alter the schema on every startup. If you rename a column, TypeORM drops the old column (destroying data) and creates a new one. No rollback, no confirmation, no audit trail.
I witnessed a production incident where adding nullable: true to a column triggered an ALTER TABLE on a 50-million-row table, locking it for 12 minutes and causing a complete API outage.
The correct workflow: TypeORM migration:generate compares entities to the database and generates SQL. You review it, commit to version control, and run with migration:run. In Prisma: prisma migrate dev creates migration files, prisma migrate deploy applies them in production.
My CI pipeline: (1) Run migrations against a test database. (2) Run tests. (3) Build Docker image. (4) During deployment, run migrations before starting the new app version. (5) If migration fails, roll back the deployment.

Follow-up: How do you handle a migration that takes 30 minutes on a 100-million-row table?Never run long migrations during deployment. Use online schema migration tools like pg_repack (PostgreSQL) or pt-online-schema-change (MySQL) that copy the table without locking. For PostgreSQL 11+, ALTER TABLE ADD COLUMN ... DEFAULT ... is instant (metadata-only). For indexes, use CREATE INDEX CONCURRENTLY. Plan the strategy before writing the migration file.

​Chapter 5: Database Integration

​5.1 Choosing an ORM

​TypeORM vs Prisma

​Detailed Feature Comparison

​Migration Strategy Decision Framework

​5.2 Setting Up TypeORM

​Installation

​Basic Configuration

​Using Configuration Module

​5.3 Defining Entities with TypeORM

​Basic Entity

​Column Options

​Relationships

​5.4 Using Repositories with TypeORM

​Injecting Repository

​Using Repository in Service

​Advanced Queries

​5.5 Setting Up Prisma

​Installation

​Schema Definition

​Prisma Service

​Registering Prisma Service

​5.6 Using Prisma in Services

​Basic CRUD Operations

​Advanced Queries

​5.7 Transactions

​TypeORM Transactions

​Prisma Transactions

​Transaction Edge Cases

​5.8 Migrations

​TypeORM Migrations

​Prisma Migrations

​5.9 Best Practices

​Use Environment Variables

​Connection Pooling

​Use Migrations

​Index Optimization

​Query Optimization

​Query Performance Decision Table

​Error Handling

​Testing

​5.10 Summary

​Interview Deep-Dive

Chapter 5: Database Integration

5.1 Choosing an ORM

TypeORM vs Prisma

Detailed Feature Comparison

Migration Strategy Decision Framework

5.2 Setting Up TypeORM

Installation

Basic Configuration

Using Configuration Module

5.3 Defining Entities with TypeORM

Basic Entity

Column Options

Relationships

5.4 Using Repositories with TypeORM

Injecting Repository

Using Repository in Service

Advanced Queries

5.5 Setting Up Prisma

Installation

Schema Definition

Prisma Service

Registering Prisma Service

5.6 Using Prisma in Services

Basic CRUD Operations

Advanced Queries

5.7 Transactions

TypeORM Transactions

Prisma Transactions

Transaction Edge Cases

5.8 Migrations

TypeORM Migrations

Prisma Migrations

5.9 Best Practices

Use Environment Variables

Connection Pooling

Use Migrations

Index Optimization

Query Optimization

Query Performance Decision Table

Error Handling

Testing

5.10 Summary

Interview Deep-Dive