nest_13_database - Database Integration¶

Your API is useless without data persistence NestJS gives you first-party modules for TypeORM , Mongoose , Prisma , and Sequelize - each integrates through the same module/provider pattern , so your DI container knows about your database connections and repositories without you manually wiring every query

what's in here¶

@nestjs/typeorm setup and entities
@nestjs/mongoose for MongoDB
@nestjs/prisma integration
Repository pattern and dependency injection
Database migrations
Transactions and error handling
Connection pooling and configuration

TypeORM with PostgreSQL¶

npm install @nestjs/typeorm typeorm pg

defining an entity¶

import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn, UpdateDateColumn, OneToMany } from 'typeorm'

@Entity('users')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string

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

  @Column({ select: false })   // excluded from SELECT queries by default
  passwordHash: string

  @Column({ default: 'user' })
  role: string

  @Column({ default: true })
  isActive: boolean

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

  @CreateDateColumn()
  createdAt: Date

  @UpdateDateColumn()
  updatedAt: Date
}

@Entity('users') maps the class to the users table @PrimaryGeneratedColumn('uuid') creates a UUID primary key - auto-increment integers leak your user count @Column({ select: false }) prevents passwordHash from being included in queries unless explicitly selected - if you forget this , your auth endpoint returns hashes in API responses

wiring the module¶

import { Module } from '@nestjs/common'
import { TypeOrmModule } from '@nestjs/typeorm'
import { ConfigModule, ConfigService } from '@nestjs/config'
import { User } from './entities/user.entity'
import { UsersController } from './users.controller'
import { UsersService } from './users.service'

@Module({
  imports: [
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (config: ConfigService) => ({
        type: 'postgres',
        host: config.get('DB_HOST'),
        port: config.get<number>('DB_PORT'),
        username: config.get('DB_USER'),
        password: config.get('DB_PASSWORD'),
        database: config.get('DB_NAME'),
        entities: [__dirname + '/**/*.entity{.ts,.js}'],
        synchronize: config.get('NODE_ENV') !== 'production',
        // NOTE: synchronize:true in prod drops and recreates tables
        // Use migrations in production
        logging: config.get('NODE_ENV') === 'development',
        poolSize: 10,  // connection pool size
      })
    }),
    TypeOrmModule.forFeature([User])  // registers User repository
  ],
  controllers: [UsersController],
  providers: [UsersService]
})
export class UsersModule {}

forRootAsync reads database config from environment variables - never hardcode credentials synchronize: true is fine in development but will drop your production data if the schema changes Use migrations in production - they're version-controlled and reversible

repository injection¶

import { Injectable } from '@nestjs/common'
import { InjectRepository } from '@nestjs/typeorm'
import { Repository } from 'typeorm'
import { User } from './entities/user.entity'

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

  async findByEmail(email: string): Promise<User | null> {
    return this.usersRepository.findOne({
      where: { email },
      relations: ['orders']  // eager-load related entities when needed
    })
  }

  async create(data: Partial<User>): Promise<User> {
    const user = this.usersRepository.create(data)
    return this.usersRepository.save(user)
  }

  async updatePassword(userId: string, newHash: string): Promise<void> {
    await this.usersRepository.update(userId, { passwordHash: newHash })
  }
}

@InjectRepository(User) gives you the TypeORM repository for the User entity Repository methods are your query interface - findOne , find , create , save , update , delete Always specify where clauses to prevent accidentally returning all rows

Mongoose (MongoDB)¶

npm install @nestjs/mongoose mongoose

import { Module } from '@nestjs/common'
import { MongooseModule } from '@nestjs/mongoose'
import { ConfigModule, ConfigService } from '@nestjs/config'

@Module({
  imports: [
    MongooseModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (config: ConfigService) => ({
        uri: config.get('MONGODB_URI'),
        // connection pool and timeout settings
        maxPoolSize: 10,
        serverSelectionTimeoutMS: 5000,
        socketTimeoutMS: 45000,
      })
    }),
    MongooseModule.forFeature([
      { name: 'User', schema: UserSchema }
    ])
  ]
})
export class DatabaseModule {}

import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose'
import { Document } from 'mongoose'

@Schema({ timestamps: true })
export class User extends Document {
  @Prop({ required: true, unique: true })
  email: string

  @Prop({ required: true, select: false })
  passwordHash: string

  @Prop({ default: false })
  isVerified: boolean
}

export const UserSchema = SchemaFactory.createForClass(User)

Mongoose follows the same module/provider pattern as TypeORM timestamps: true auto-adds createdAt and updatedAt fields select: false on passwordHash prevents it from being returned in queries

Prisma integration¶

npm install @prisma/client
npx prisma init

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id           String   @id @default(uuid())
  email        String   @unique
  passwordHash String
  role         String   @default("user")
  isActive     Boolean  @default(true)
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  orders       Order[]
}

model Order {
  id        String   @id @default(uuid())
  userId    String
  user      User     @relation(fields: [userId], references: [id])
  total     Decimal
  createdAt DateTime @default(now())
}

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

import { Module } from '@nestjs/common'
import { PrismaService } from './prisma.service'
import { UsersController } from './users.controller'
import { UsersService } from './users.service'

@Module({
  controllers: [UsersController],
  providers: [PrismaService, UsersService]
})
export class UsersModule {}

Prisma gives you type-safe queries - the generated client knows your schema at TypeScript level No raw SQL strings unless you explicitly use $queryRaw TypeORM and Mongoose are more flexible ; Prisma is safer and has better DX

transactions¶

import { Injectable } from '@nestjs/common'
import { InjectEntityManager } from '@nestjs/typeorm'
import { EntityManager } from 'typeorm'

@Injectable()
export class OrdersService {
  constructor(
    @InjectEntityManager()
    private entityManager: EntityManager
  ) {}

  async createOrder(userId: string, items: OrderItem[]) {
    // use transaction to ensure atomicity
    await this.entityManager.transaction(async (manager) => {
      const total = items.reduce((sum, item) => sum + item.price, 0)

      // check user balance within same transaction
      const user = await manager.findOne(User, { where: { id: userId } })
      if (!user || user.balance < total) {
        throw new BadRequestException('insufficient funds')
      }

      // deduct balance and create order atomically
      await manager.update(User, userId, {
        balance: () => `balance - ${total}`  // SQL expression - race condition safe
      })

      const order = manager.create(Order, { userId, total, items })
      await manager.save(order)

      return order
    })
  }
}

Transactions ensure that all operations succeed or all fail together In money-handling code , transactions are non-negotiable - without them , a crash between checking balance and deducting creates free money The () => 'balance - ${total}' syntax uses a raw SQL expression - TypeORM generates UPDATE SET balance = balance - 100 which is atomic in the database

migrations¶

# TypeORM migration
npx typeorm migration:create src/migrations/CreateUsersTable
npx typeorm migration:run

# Prisma migration
npx prisma migrate dev --name add_orders_table  # development
npx prisma migrate deploy                         # production

Migration files are version-controlled SQL scripts Run them as part of deployment , not at application startup synchronize: true (TypeORM) or prisma db push should never run in production

connection pooling and security¶

TypeOrmModule.forRootAsync({
  useFactory: (config: ConfigService) => ({
    type: 'postgres',
    extra: {
      max: 10,                  // pool size - 10 concurrent connections
      idleTimeoutMillis: 30000,  // close idle connections after 30s
      connectionTimeoutMillis: 5000,  // fail fast if DB unreachable
    },
    ssl: config.get('NODE_ENV') === 'production'
      ? { rejectUnauthorized: true }
      : false
  })
})

Connection pooling prevents your app from exhausting database connections during traffic spikes SSL in production prevents man-in-the-middle attacks on your database traffic - yes , even if your DB is in the same VPC Connection timeouts prevent your app from hanging when the database goes down

prerequisites¶

nest_12_security - Security Best Practices

next -> nest_14_testing - Testing