14.5 NestJS + Prisma — Database Integration

Prisma Setup

npm install prisma @prisma/client
npx prisma init

Prisma Schema

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

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

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String
  password  String
  role      Role     @default(USER)
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

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

enum Role {
  ADMIN
  USER
  GUEST
}

---

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

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

@Global()  // Global module — injectable anywhere without explicit imports
@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

// app.module.ts
import { Module } from '@nestjs/common'
import { PrismaModule } from './prisma/prisma.module'
import { UsersModule } from './users/users.module'

@Module({
  imports: [PrismaModule, UsersModule],
})
export class AppModule {}

---

Repository Pattern

// users/users.repository.ts
import { Injectable } from '@nestjs/common'
import { PrismaService } from '../prisma/prisma.service'
import { Prisma, User } from '@prisma/client'

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

  async findById(id: string): Promise<User | null> {
    return this.prisma.user.findUnique({ where: { id } })
  }

  async findByEmail(email: string): Promise<User | null> {
    return this.prisma.user.findUnique({ where: { email } })
  }

  async findAll(params: {
    skip?: number
    take?: number
    where?: Prisma.UserWhereInput
    orderBy?: Prisma.UserOrderByWithRelationInput
  }): Promise<User[]> {
    return this.prisma.user.findMany(params)
  }

  async create(data: Prisma.UserCreateInput): Promise<User> {
    return this.prisma.user.create({ data })
  }

  async update(id: string, data: Prisma.UserUpdateInput): Promise<User> {
    return this.prisma.user.update({ where: { id }, data })
  }

  async delete(id: string): Promise<User> {
    return this.prisma.user.delete({ where: { id } })
  }

  async count(where?: Prisma.UserWhereInput): Promise<number> {
    return this.prisma.user.count({ where })
  }
}

---

Service Layer

// users/users.service.ts
import { Injectable, NotFoundException, ConflictException } from '@nestjs/common'
import { User } from '@prisma/client'
import { UsersRepository } from './users.repository'
import { CreateUserDto } from './dto/create-user.dto'
import { UpdateUserDto } from './dto/update-user.dto'

export type UserWithoutPassword = Omit<User, 'password'>

@Injectable()
export class UsersService {
  constructor(private readonly usersRepository: UsersRepository) {}

  async findAll(page = 1, limit = 10): Promise<{
    data: UserWithoutPassword[]
    total: number
    page: number
    limit: number
  }> {
    const skip = (page - 1) * limit
    const [users, total] = await Promise.all([
      this.usersRepository.findAll({ skip, take: limit }),
      this.usersRepository.count(),
    ])

    return {
      data: users.map(({ password, ...user }) => user),
      total,
      page,
      limit,
    }
  }

  async findOne(id: string): Promise<UserWithoutPassword> {
    const user = await this.usersRepository.findById(id)
    if (!user) {
      throw new NotFoundException(`User ID ${id} not found.`)
    }
    const { password, ...result } = user
    return result
  }

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

  async create(createUserDto: CreateUserDto): Promise<UserWithoutPassword> {
    const existing = await this.usersRepository.findByEmail(createUserDto.email)
    if (existing) {
      throw new ConflictException('Email is already in use.')
    }
    const user = await this.usersRepository.create(createUserDto)
    const { password, ...result } = user
    return result
  }

  async update(id: string, updateUserDto: UpdateUserDto): Promise<UserWithoutPassword> {
    await this.findOne(id)  // Check existence
    const user = await this.usersRepository.update(id, updateUserDto)
    const { password, ...result } = user
    return result
  }

  async remove(id: string): Promise<void> {
    await this.findOne(id)  // Check existence
    await this.usersRepository.delete(id)
  }
}

---

Transaction Handling

// Process multiple operations atomically
import { PrismaService } from '../prisma/prisma.service'

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

  async createOrderWithItems(
    userId: string,
    items: Array<{ productId: string; quantity: number; price: number }>
  ) {
    // $transaction: rollback all if any operation fails
    return this.prisma.$transaction(async (tx) => {
      // Create order
      const order = await tx.order.create({
        data: { userId, status: 'PENDING' },
      })

      // Create order items
      await tx.orderItem.createMany({
        data: items.map(item => ({
          orderId: order.id,
          ...item,
        })),
      })

      // Decrement inventory
      for (const item of items) {
        await tx.product.update({
          where: { id: item.productId },
          data: { stock: { decrement: item.quantity } },
        })
      }

      return tx.order.findUnique({
        where: { id: order.id },
        include: { items: true },
      })
    })
  }
}

---

Prisma Type Utilities

import { Prisma, User } from '@prisma/client'

// Useful types auto-generated by Prisma

// Create input type
type CreateUserInput = Prisma.UserCreateInput

// Update input type
type UpdateUserInput = Prisma.UserUpdateInput

// Type with selected fields only
type UserProfile = Prisma.UserGetPayload<{
  select: { id: true; name: true; email: true; createdAt: true }
}>

// Type with relations included
type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true }
}>

// Where condition type
type UserWhereInput = Prisma.UserWhereInput

// OrderBy type
type UserOrderBy = Prisma.UserOrderByWithRelationInput

---

Pro Tips

Soft Delete Middleware

// prisma/prisma.service.ts
@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
  async onModuleInit() {
    await this.$connect()

    // Soft delete middleware — set deletedAt instead of actual deletion
    this.$use(async (params, next) => {
      if (params.action === 'delete') {
        params.action = 'update'
        params.args.data = { deletedAt: new Date() }
      }
      if (params.action === 'deleteMany') {
        params.action = 'updateMany'
        if (params.args.data !== undefined) {
          params.args.data.deletedAt = new Date()
        } else {
          params.args.data = { deletedAt: new Date() }
        }
      }
      return next(params)
    })

    // Auto-filter deleted records
    this.$use(async (params, next) => {
      const modelsWithSoftDelete = ['User', 'Post']
      if (modelsWithSoftDelete.includes(params.model ?? '')) {
        if (params.action === 'findUnique' || params.action === 'findFirst') {
          params.action = 'findFirst'
          params.args.where = { ...params.args.where, deletedAt: null }
        }
        if (params.action === 'findMany') {
          params.args.where = { ...params.args.where, deletedAt: null }
        }
      }
      return next(params)
    })
  }
}