> ## Documentation Index
> Fetch the complete documentation index at: https://resources.devweekends.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Integration

# 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

```bash theme={null}
# 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

```typescript theme={null}
// 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`:

```typescript theme={null}
// 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**

```text theme={null}
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."

```typescript theme={null}
// 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

```typescript theme={null}
@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:**

```typescript theme={null}
// 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:**

```typescript theme={null}
// 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:**

```typescript theme={null}
// 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

```typescript theme={null}
// 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

```typescript theme={null}
// 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:**

```typescript theme={null}
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:**

```typescript theme={null}
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:**

```typescript theme={null}
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

```bash theme={null}
npm install @prisma/client prisma
npx prisma init
```

### Schema Definition

```prisma theme={null}
// 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.

```typescript theme={null}
// 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

```typescript theme={null}
// 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**

```text theme={null}
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

```typescript theme={null}
// 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:**

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

**Filtering:**

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

**Pagination:**

```typescript theme={null}
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:**

```typescript theme={null}
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:**

```typescript theme={null}
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:**

```typescript theme={null}
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:**

```typescript theme={null}
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:**

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

**Interactive Transactions:**

```typescript theme={null}
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.

```typescript theme={null}
// 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:

```typescript theme={null}
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:**

```bash theme={null}
npx typeorm migration:generate -n CreateUsers
```

**Migration File:**

```typescript theme={null}
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:**

```bash theme={null}
npx typeorm migration:run
npx typeorm migration:revert
```

### Prisma Migrations

**Create Migration:**

```bash theme={null}
npx prisma migrate dev --name init
```

**Apply Migrations:**

```bash theme={null}
npx prisma migrate deploy
```

**Reset Database:**

```bash theme={null}
npx prisma migrate reset
```

**Generate Client:**

After schema changes:

```bash theme={null}
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:

```typescript theme={null}
// .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:

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

### Use Migrations

Never use `synchronize: true` in production:

```typescript theme={null}
// Development
synchronize: true

// Production
synchronize: false  // Use migrations instead
```

### Index Optimization

Add indexes for frequently queried columns:

```typescript theme={null}
// TypeORM
@Index()
@Column()
email: string;

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

### Query Optimization

**TypeORM:**

```typescript theme={null}
// 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:**

```typescript theme={null}
// 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.

```typescript theme={null}
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:

```typescript theme={null}
// 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

<AccordionGroup>
  <Accordion title="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.
  </Accordion>

  <Accordion title="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.
  </Accordion>

  <Accordion title="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.
  </Accordion>

  <Accordion title="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.
  </Accordion>
</AccordionGroup>