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

TypeORM:

Active Record & Data Mapper patterns
Decorator-based entity definitions
Mature ecosystem with many features
Flexible query builder
Works with TypeScript and JavaScript
Good for complex queries and relationships

Prisma:

Type-safe database client
Schema-first approach
Excellent developer experience
Automatic migrations
Great for rapid development
Strong TypeScript support

When to Use TypeORM:

Need complex queries
Prefer decorators
Want flexibility
Working with existing database

When to Use Prisma:

Want type safety
Prefer schema-first
Need rapid development
Starting new project

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({
      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],
      synchronize: process.env.NODE_ENV !== 'production', // Never true in 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

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

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

  @Column()
  name: string;

  @Column({ unique: true })
  email: string;

  @Column({ nullable: true })
  age: number;

  @CreateDateColumn()
  createdAt: Date;

  @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

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

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() {
    await this.$connect();
  }

  async onModuleDestroy() {
    await this.$disconnect();
  }
}

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 };
  });
}

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
  },
});

Error Handling

Handle database errors gracefully:

async create(dto: CreateUserDto) {
  try {
    return await this.userRepository.save(dto);
  } catch (error) {
    if (error.code === '23505') {  // Unique constraint violation
      throw new ConflictException('Email already exists');
    }
    throw new InternalServerErrorException('Failed to create user');
  }
}

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.

Testing & Code Quality

Crash Courses

AI Engineering

Math for ML - Understanding Linear Algebra

Probability & Statistics for ML

Math for ML - Understanding Calculus

ML Mastery

Deep Learning Mastery

NestJS Mastery

Microservices Mastery

Low Level Design

OOP Concepts

SOLID Principles

Design Patterns

LLD Case Studies

System Design (HLD)

Senior Level (L5+/Staff)

HLD Case Studies

Engineering Fundamentals

DevOps & Operations

AWS Cloud

AWS Monitoring & Observability

AWS Security Services

AWS Serverless

AWS Operations

AWS Advanced

AWS Case Studies

DevOps Tools

Database Engineering

HIPAA Compliance Mastery

Operating Systems

Linux Internals

Distributed Systems

Networking Mastery

Build Your Own X

Go Lang Mastery

C Programming

Distributed System Tools

​Chapter 5: Database Integration

​5.1 Choosing an ORM

​TypeORM vs Prisma

​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

​5.8 Migrations

​TypeORM Migrations

​Prisma Migrations

​5.9 Best Practices

​Use Environment Variables

​Connection Pooling

​Use Migrations

​Index Optimization

​Query Optimization

​Error Handling

​Testing

​5.10 Summary

Chapter 5: Database Integration

5.1 Choosing an ORM

TypeORM vs Prisma

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

5.8 Migrations

TypeORM Migrations

Prisma Migrations

5.9 Best Practices

Use Environment Variables

Connection Pooling

Use Migrations

Index Optimization

Query Optimization

Error Handling

Testing

5.10 Summary