Prisma – A Complete Practical Guide (NestJS • Next.js • Postgres • Docker) Print

Executive Summary:
Prisma is a type‑safe ORM for Node.js and TypeScript. It replaces most handwritten SQL and builds a strongly‑typed client from a schema you control. In this guide, you'll set up Prisma with PostgreSQL 15, use it from NestJS (API) and Next.js (web), run it in Docker Compose, and adopt production‑grade workflows--migrations, seeding, transactions, RBAC, multi‑tenancy, performance, observability, and CI/CD. Copy‑paste snippets are included.

Table of Contents

1. What is Prisma? When to use it

2. Architecture overview (Prisma schema → Client → DB)

3. Quick Start: Docker Compose + Postgres + Prisma

4. Initialize Prisma in a monorepo (apps/api, apps/web, packages/db)

5. Modeling the schema (Users, Roles, Sessions, Audit Logs)

6. Migrations workflow (dev → staging → prod)

7. Using Prisma Client from NestJS (services, modules, DI)

8. Using Prisma Client from Next.js (server actions / API routes)

9. CRUD patterns: select/include, pagination, filtering

10. Transactions and consistency (interactive + batch)

11. RBAC in practice (policies, guards, scoping)

12. Multi‑tenancy strategies (single DB, per‑tenant schema, per‑DB)

13. Seeding strategies (idempotent seeders)

14. Performance and indexing best practices

15. Soft deletes, partial unique indexes, and archival

16. Middleware, hooks, and auditing

17. Error handling (Prisma errors → API responses)

18. Testing with a disposable Postgres (Jest/Vitest)

19. CI/CD with GitHub Actions (migrate deploy)

20. Observability (metrics, slow query logs)

21. Backups and restore basics

22. Common pitfalls & troubleshooting

23. Prisma CLI cheat‑sheet

1) What is Prisma? When to use it

Prisma turns your DB schema into a type‑safe client. You define models in schema.prisma; Prisma generates a client (@prisma/client) that knows your tables, relations, and types.

Use Prisma when you want:

• End‑to‑end TypeScript safety (compile time catching of schema mismatches).

• Faster iteration with migrations and a declarative model.

• Rich relations and query ergonomics without fragile SQL string building.

Skip Prisma when you need vendor‑specific features extensively, custom SQL tuning everywhere, or your team prefers a query builder/raw SQL approach for full control.

2) Architecture overview

┌──────────────┐   prisma migrate   ┌─────────────┐     type‑safe queries     ┌──────────────┐
│ schema.prisma├────────────────────▶│   database  │◀──────────────────────────│ Prisma Client │
└──────┬───────┘                      └─────────────┘                           └──────┬───────┘
       │  prisma generate                                                         import/use
       ▼
   node_modules/@prisma/client (generated)

3) Quick Start: Docker Compose + Postgres + Prisma

docker-compose.yml (dev):

services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
      POSTGRES_DB: appdb
    ports: ["5432:5432"]
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata: {}

.env (root):

DATABASE_URL="postgresql://app:app@localhost:5432/appdb?schema=public"

Install & init

pnpm add -D prisma
pnpm add @prisma/client
npx prisma init --datasource-provider postgresql

This creates prisma/schema.prisma and .env (uses DATABASE_URL).

4) Initialize Prisma in a monorepo

Recommended structure:

apps/
  api/ (NestJS)
  web/ (Next.js)
packages/
  db/
    prisma/
      schema.prisma
    src/
      client.ts

packages/db/src/client.ts (singleton):

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

const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };

export const prisma =
  globalForPrisma.prisma ?? new PrismaClient({
    log: [{ level: 'query', emit: 'event' }, 'info', 'warn', 'error'],
  });

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

Then import prisma from packages/db in both api and web.

5) Modeling the 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?
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  sessions  Session[]
  audit     AuditLog[]
}

enum Role {
  USER
  ADMIN
}

model Session {
  id        String   @id @default(cuid())
  userId    String
  user      User     @relation(fields: [userId], references: [id])
  token     String   @unique
  expiresAt DateTime
  createdAt DateTime @default(now())
}

model AuditLog {
  id        String   @id @default(cuid())
  userId    String?
  user      User?    @relation(fields: [userId], references: [id])
  action    String
  entity    String?
  entityId  String?
  meta      Json?
  createdAt DateTime @default(now())
}

Apply migration:

npx prisma migrate dev --name init

Generate client:

npx prisma generate

6) Migrations workflow

• Local dev: migrate dev creates SQL and applies it.

• CI/CD: use prisma migrate deploy (applies existing migrations only; no drift).

• Hot‑fix: create a new migration; avoid editing old migrations after they shipped.

• Branching: rebase or re‑create migrations on feature branches to minimize conflicts.

Production command (idempotent):

npx prisma migrate deploy

7) NestJS usage

PrismaModule (simple):

import { Module, OnModuleDestroy, OnModuleInit } from '@nestjs/common';
import { prisma } from '@acme/db';

@Module({ providers: [{ provide: 'PRISMA', useValue: prisma }], exports: ['PRISMA'] })
export class PrismaModule implements OnModuleInit, OnModuleDestroy {
  async onModuleInit() { await prisma.$connect(); }
  async onModuleDestroy() { await prisma.$disconnect(); }
}

UsersService:

import { Inject, Injectable } from '@nestjs/common';
import { PrismaClient, Role } from '@prisma/client';

@Injectable()
export class UsersService {
  constructor(@Inject('PRISMA') private db: PrismaClient) {}

  findPage = (page=1, size=20) => this.db.user.findMany({
    skip: (page-1)*size,
    take: size,
    select: { id: true, email: true, role: true, createdAt: true },
    orderBy: { createdAt: 'desc' },
  });

  create = (email: string, name?: string) => this.db.user.create({
    data: { email, name, role: Role.USER },
    select: { id: true, email: true },
  });
}

8) Next.js usage

Server action / route handler:

// apps/web/app/api/me/route.ts
import { prisma } from '@acme/db';

export async function GET() {
  const me = await prisma.user.findFirst({ select: { id: true, email: true } });
  return Response.json(me);
}

Avoid multiple clients by importing the shared singleton from packages/db.

9) CRUD patterns

Create

await prisma.user.create({ data: { email: '[email protected]', name: 'A' } });

Read with relation

await prisma.user.findUnique({
  where: { id },
  include: { sessions: { select: { id: true, expiresAt: true } } },
});

Update (partial)

await prisma.user.update({ where: { id }, data: { name: 'New' } });

Delete

await prisma.user.delete({ where: { id } });

Pagination

await prisma.user.findMany({
  cursor: cursorId ? { id: cursorId } : undefined,
  take: 25,
  orderBy: { createdAt: 'desc' },
});

Filtering

await prisma.user.findMany({ where: { email: { contains: '@domainindia.com', mode: 'insensitive' } } });

10) Transactions

Batch

await prisma.$transaction([
  prisma.user.create({ data: { email: '[email protected]' } }),
  prisma.auditLog.create({ data: { action: 'USER_CREATE', entity: 'User' } }),
]);

Interactive

await prisma.$transaction(async (tx) => {
  const u = await tx.user.create({ data: { email: '[email protected]' } });
  await tx.auditLog.create({ data: { action: 'USER_CREATE', entity: 'User', entityId: u.id } });
});

11) RBAC in practice

• Add Role enum in schema (already shown) and read it from JWT/session.

• Guard DB queries by scoping (e.g., tenantId) and role checks at service layer.

• Example: only admins can list all users; users can only read themselves.

if (role !== 'ADMIN') return prisma.user.findUnique({ where: { id: userId } });
return prisma.user.findMany({ take: 50 });

12) Multi‑tenancy strategies

1. Single DB, tenantId column (simplest)

2. Single DB, per‑tenant schema (requires connection switching or search_path)

3. Per tenant DB (isolation; heavier ops)

Tenant column approach:

model Project {
  id        String @id @default(cuid())
  tenantId  String @index
  name      String
  createdAt DateTime @default(now())
}

Always add where: { tenantId } in queries (wrap in service/helpers to avoid leaks).

13) Seeding strategies

Enable prisma/seed.ts in package.json:

{
  "prisma": { "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts" }
}

prisma/seed.ts

import { PrismaClient, Role } from '@prisma/client';
const prisma = new PrismaClient();
async function main(){
  await prisma.user.upsert({
    where: { email: '[email protected]' },
    update: {},
    create: { email: '[email protected]', role: Role.ADMIN, name: 'Admin' },
  });
}
main().finally(()=>prisma.$disconnect());

Run: npx prisma db seed

14) Performance & indexing

• Use select to avoid over‑fetching.

• Add indexes for frequent filters (@@index([createdAt]), @@index([tenantId, createdAt])).

• Prefer cursor pagination for large lists.

• Batch with $transaction to reduce round trips.

• For heavy analytics, use prisma.$queryRaw (parameterized!) or a warehouse.

Example index:

model AuditLog {
  id        String   @id @default(cuid())
  action    String
  createdAt DateTime @default(now())
  @@index([action, createdAt])
}

15) Soft deletes & unique constraints

Add deletedAt and partial unique indexes (Postgres):

model Customer {
  id        String   @id @default(cuid())
  email     String
  deletedAt DateTime?
  @@unique([email], map: "ux_customer_email_active")
}

Then enforce uniqueness only for active rows with a partial index via SQL migration:

-- migration.sql
CREATE UNIQUE INDEX ux_customer_email_active
  ON "Customer" ("email")
  WHERE "deletedAt" IS NULL;

Queries should add { where: { deletedAt: null } } by default.

16) Middleware & auditing

Prisma supports middlewares:

prisma.$use(async (params, next) => {
  const result = await next(params);
  if (['create','update','delete'].includes(params.action)) {
    await prisma.auditLog.create({
      data: { action: `${params.model}.${params.action}`, entity: params.model, meta: params.args }
    });
  }
  return result;
});

Use caution to avoid recursion (log with a separate client or conditionally skip AuditLog).

17) Error handling

Map known errors:

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

try {
  // ...
} catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError) {
    if (e.code === 'P2002') return { status: 409, message: 'Unique constraint failed' };
    if (e.code === 'P2025') return { status: 404, message: 'Record not found' };
  }
  throw e;
}

18) Testing

Spin up test DB via Docker; run migrations before tests:

docker run --rm -p 5433:5432 -e POSTGRES_PASSWORD=test -e POSTGRES_USER=test -e POSTGRES_DB=testdb postgres:15
DATABASE_URL="postgresql://test:test@localhost:5433/testdb" npx prisma migrate deploy

Use a fresh schema per test file or transactions with rollback.

19) CI/CD (GitHub Actions)

name: ci
on: [push]
jobs:
  build:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_USER: app
          POSTGRES_PASSWORD: app
          POSTGRES_DB: appdb
        ports: ["5432:5432"]
        options: >-
          --health-cmd="pg_isready -U app" --health-interval=10s --health-timeout=5s --health-retries=5
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with: { node-version: '20' }
      - run: pnpm i --frozen-lockfile
      - run: npx prisma generate
      - run: npx prisma migrate deploy
      - run: pnpm test

Deploy step (container): run prisma migrate deploy on startup before serving traffic.

20) Observability

• Enable Prisma query logging in dev.

• Pipe Postgres logs to your stack (e.g., Grafana Loki).

• Sample slow query detector: wrap calls and log when > N ms.

21) Backups & restore

• Nightly logical backups with pg_dump (retain 7/30/90 days).

• Periodic restore drills in a staging DB.

• For Docker, mount a backup volume or push to S3/Backblaze.

22) Common pitfalls

• Multiple clients in hot‑reload: use a global singleton (provided above).

• Missing migrate deploy in prod: schema drift and runtime errors.

• Forgetting indexes on filter columns → slow queries.

• Unbounded findMany → memory pressure; always page.

• Recursive middleware when logging AuditLog; guard it.

23) Prisma CLI cheat‑sheet

prisma init                    # create prisma/ and schema.prisma
prisma generate                # regen client after schema change
prisma migrate dev --name xyz  # create & apply migration (dev)
prisma migrate deploy          # apply pending migrations (prod/stage)
prisma db seed                 # run seed script
prisma studio                  # web UI for DB browsing (dev only)

Next steps for your stack (Domain India / HostCore)

• Put packages/db in the monorepo and import prisma in NestJS API and Next.js web.

• Add baseline tables you already planned (users/roles/clients/settings/audit_logs) and migrate.

• Build idempotent seeds for the admin user and mandatory settings.

• Wire CI/CD to run migrate deploy before starting containers in production.

If you want, I can tailor this to your exact monorepo (service names, existing models, and GitHub Actions you're running) and include guarded raw SQL examples for reporting/perf‑critical paths.