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

Executive Summary:
Prisma is a typesafe ORM for Node.js and TypeScript. It replaces most handwritten SQL and builds a stronglytyped 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 productiongrade workflows--migrations, seeding, transactions, RBAC, multitenancy, performance, observability, and CI/CD. Copypaste 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. Multitenancy strategies (single DB, pertenant schema, perDB)

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 cheatsheet

---

1) What is Prisma When to use it

Prisma turns your DB schema into a typesafe 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:

• Endtoend 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 vendorspecific features extensively, custom SQL tuning everywhere, or your team prefers a query builder/raw SQL approach for full control.

---

2) Architecture overview

 prisma migrate typesafe 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/appdbschema=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).

• Hotfix: create a new migration; avoid editing old migrations after they shipped.

• Branching: rebase or recreate 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) Multitenancy strategies

1. Single DB, tenantId column (simplest)

2. Single DB, pertenant 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 overfetching.

• 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 hotreload: 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 cheatsheet

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/perfcritical paths.