Prisma Client & PostgreSQL

Overview

QMS uses Prisma as the ORM for type-safe database access and PostgreSQL as the primary database. Prisma provides auto-generated, type-safe database client and schema migrations.

What is Prisma?

Prisma is:

• Type-Safe: Generated client with full TypeScript support
• Developer-Friendly: Intuitive query API
• Schema-Driven: Single source of truth for database schema
• Migration Management: Version-controlled database changes
• Multi-Database: Supports PostgreSQL, MySQL, SQLite, MongoDB, etc.
• Performance: Query optimization and connection pooling

Setup

Installation

pnpm add @prisma/client
pnpm add -D prisma

Initialize Prisma

pnpm exec prisma init

# Creates:
# - prisma/schema.prisma (schema file)
# - .env (environment variables)

Schema Definition

Basic Schema

prisma/schema.prisma

// Prisma schema file. Learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

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

// Define models below...

Employee Model Example

model Employee {
  /// Unique identifier
  id        String   @id @default(cuid())
  name      String
  email     String   @unique
  department String
  role      String
  isActive  Boolean  @default(true)
  phone     String?  // Optional field

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime? // For soft deletes

  // Relations
  sessions  UserSession[]

  @@map("tb_m_employee") // Database table name
  @@index([email])       // Database index
}

model UserSession {
  id        String   @id @default(cuid())
  userId    String
  user      Employee @relation(fields: [userId], references: [id], onDelete: Cascade)
  token     String   @unique
  expiresAt DateTime

  createdAt DateTime @default(now())

  @@map("tb_r_user_session")
  @@unique([userId, token])
}

model System {
  id          String   @id @default(cuid())
  name        String
  description String?
  status      String   @default("active")

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("tb_m_system")
}

Advanced Schema Features

Enums

enum UserRole {
  ADMIN
  MANAGER
  EMPLOYEE
  VIEWER

  @@map("user_role_enum")
}

model User {
  id    String   @id @default(cuid())
  role  UserRole @default(EMPLOYEE)
}

Composite Unique Constraints

model UserDepartment {
  userId    String
  departmentId String
  joinedAt  DateTime @default(now())

  @@unique([userId, departmentId])
  @@map("user_departments")
}

Relations - One to Many

model Department {
  id        String   @id @default(cuid())
  name      String
  employees Employee[] // One-to-many relation
}

model Employee {
  id           String @id @default(cuid())
  name         String
  departmentId String
  department   Department @relation(fields: [departmentId], references: [id])
}

Relations - Many to Many

model User {
  id     String @id @default(cuid())
  name   String
  roles  Role[] @relation("UserRoles")
}

model Role {
  id    String @id @default(cuid())
  name  String
  users User[] @relation("UserRoles")

  @@map("roles")
}

Environment Configuration

.env

# Database
DATABASE_URL="postgresql://user:password@localhost:5432/qms?schema=public"

# Connection pool
DATABASE_POOL_MIN=2
DATABASE_POOL_MAX=10
DATABASE_POOL_IDLE_TIMEOUT=30000

Connection String Format

postgresql://[user[:password]@][netlocation][:port][/dbname][?param1=value1&...]

Database Operations

PrismaService

src/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();
  }
}

Basic Operations

// Create
await prisma.employee.create({
  data: {
    name: 'John Doe',
    email: 'john@example.com',
    department: 'Engineering',
  },
});

// Read
await prisma.employee.findUnique({
  where: { id: '123' },
});

// Read many
await prisma.employee.findMany({
  where: { department: 'Engineering' },
  skip: 0,
  take: 10,
  orderBy: { createdAt: 'desc' },
});

// Update
await prisma.employee.update({
  where: { id: '123' },
  data: { department: 'Management' },
});

// Delete
await prisma.employee.delete({
  where: { id: '123' },
});

// Delete many
await prisma.employee.deleteMany({
  where: { isActive: false },
});

Complex Queries

// With relations
const employee = await prisma.employee.findUnique({
  where: { id: '123' },
  include: {
    sessions: true,
    department: true,
  },
});

// Conditional filtering
const employees = await prisma.employee.findMany({
  where: {
    AND: [
      { department: 'Engineering' },
      { isActive: true },
      {
        OR: [
          { name: { contains: 'John', mode: 'insensitive' } },
          { email: { contains: 'john', mode: 'insensitive' } },
        ],
      },
    ],
  },
});

// Projections (select specific fields)
const names = await prisma.employee.findMany({
  select: {
    id: true,
    name: true,
    email: true,
  },
});

// Aggregations
const count = await prisma.employee.count({
  where: { department: 'Engineering' },
});

const count = await prisma.employee.aggregate({
  where: { department: 'Engineering' },
  _count: true,
});

Transactions

// Atomic operations
await prisma.$transaction(async (tx) => {
  // These operations all succeed or all fail
  const employee = await tx.employee.create({
    data: { name: 'John', email: 'john@example.com' },
  });

  await tx.userSession.create({
    data: {
      userId: employee.id,
      token: 'token123',
      expiresAt: new Date(Date.now() + 24 * 60 * 60 * 1000),
    },
  });

  return employee;
});

Migrations

Create Migration

# Create migration from schema changes
pnpm exec prisma migrate dev --name add_phone_to_employee

# Generates:
# - prisma/migrations/[timestamp]_add_phone_to_employee/
#   └── migration.sql

# Applies migrations and regenerates Prisma Client

Migration Files

Migrations are SQL-based and version-controlled:

-- prisma/migrations/20240115_add_phone/migration.sql
ALTER TABLE "tb_m_employee" ADD COLUMN "phone" TEXT;
CREATE INDEX "idx_phone" ON "tb_m_employee"("phone");

Reset Database

# Reset (WARNING: destructive!)
pnpm exec prisma migrate reset

# Drops database and re-runs all migrations

Deployment Migrations

# Apply migrations without prompting (for CI/CD)
pnpm exec prisma migrate deploy

Seeding

Seed Script

prisma/seed.ts

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  // Clear existing data
  await prisma.employee.deleteMany();

  // Seed data
  const emp1 = await prisma.employee.create({
    data: {
      name: 'Alice Johnson',
      email: 'alice@example.com',
      department: 'Engineering',
      role: 'Senior Developer',
    },
  });

  const emp2 = await prisma.employee.create({
    data: {
      name: 'Bob Smith',
      email: 'bob@example.com',
      department: 'Sales',
      role: 'Sales Manager',
    },
  });

  console.log('Seed data created:', emp1, emp2);
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Run Seed

# Setup in package.json
{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}

# Run seed
pnpm exec prisma db seed

# Run on migration reset
pnpm exec prisma migrate reset

Query Time Debugging

Enable Query Logging

// In PrismaService or main.ts
const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
});

// Or with event listeners
prisma.$on('query', (e) => {
  console.log('Query: ' + e.query);
  console.log('Params: ' + JSON.stringify(e.params));
  console.log('Duration: ' + e.duration + 'ms');
});

Performance Optimization

Connection Pooling

# In DATABASE_URL
postgresql://user:pass@localhost:5432/qms?schema=public&connection_limit=10

Select Only Needed Fields

// ✅ GOOD: Select specific fields
const employees = await prisma.employee.findMany({
  select: {
    id: true,
    name: true,
    email: true,
  },
});

// ❌ AVOID: Fetch all fields
const employees = await prisma.employee.findMany();

Batch Operations

// ✅ GOOD: Batch update
await prisma.employee.updateMany({
  where: { department: 'Engineering' },
  data: { department: 'Management' },
});

// ❌ AVOID: Loop and update each
for (const emp of employees) {
  await prisma.employee.update({
    where: { id: emp.id },
    data: { department: 'Management' },
  });
}

Use Pagination

const pageSize = 20;
const page = 1;

const employees = await prisma.employee.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize,
  orderBy: { createdAt: 'desc' },
});

Prisma Studio

View Data Visually

# Launch Prisma Studio
pnpm exec prisma studio

# Opens http://localhost:5555

Type Generation

Auto-Generated Types

Prisma generates types automatically:

import { Employee, Prisma } from '@prisma/client';

// Full type
const employee: Employee = {
  /* ... */
};

// Input type
const createData: Prisma.EmployeeCreateInput = {
  name: 'John',
  email: 'john@example.com',
};

// Type for database response
const result: Prisma.EmployeeGetPayload<{
  include: { sessions: true };
}>;

Best Practices

1. Use Transactions for Related Operations

// ✅ GOOD: Atomic
await prisma.$transaction(async (tx) => {
  const user = await tx.employee.create({
    /* ... */
  });
  await tx.userSession.create({
    data: { userId: user.id /* ... */ },
  });
});

// ❌ AVOID: Not atomic
const user = await prisma.employee.create({
  /* ... */
});
await prisma.userSession.create({
  data: { userId: user.id /* ... */ },
});

2. Use Where Clauses Effectively

// ✅ GOOD: Filter at database
const engineers = await prisma.employee.findMany({
  where: { department: 'Engineering' },
});

// ❌ AVOID: Filter in application
const all = await prisma.employee.findMany();
const engineers = all.filter((e) => e.department === 'Engineering');

3. Validate Foreign Keys First

// ✅ GOOD: Check FK exists
const department = await prisma.department.findUnique({
  where: { id: deptId },
});
if (!department) {
  throw new NotFoundException('Department not found');
}

// ❌ AVOID: Rely on DB error
try {
  await prisma.employee.create({
    data: { /* ... */, departmentId: invalidId },
  });
} catch (e) {
  // Handle FK error
}

4. Plan Indexes

// Index for lookups
model Employee {
  id     String @id
  email  String @unique  // Creates index automatically
  name   String
  @@index([name])       // Add index for name searches
}

Troubleshooting

Connection Issues

# Test connection
pnpm exec prisma db execute --stdin < /dev/null

# Check database
psql postgresql://user:pass@localhost:5432/qms

# Generate client
pnpm exec prisma generate

Type Errors

# Regenerate types
pnpm exec prisma generate

# Check schema validity
pnpm exec prisma validate

Related Documentation

• NestJS Framework
• Zod Validation
• Database Migrations (Liquibase)

Resources

• Prisma Official Documentation
• Prisma Schema Reference
• PostgreSQL Documentation
• Prisma YouTube Channel