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

Prisma is a type-safe ORM for Node.js and TypeScript. You describe your data model in a schema file, Prisma turns it into SQL migrations, and it generates a client whose queries are checked by the TypeScript compiler. This guide sets up Prisma with PostgreSQL in Docker, uses it from NestJS and Next.js, and covers the production habits that matter: migrations, transactions, error handling and deployment.

1. When Prisma is a good fit

Prisma suits TypeScript projects that want end-to-end type safety and a clear migration history. If you rename a column, every query that uses the old name fails to compile, not in production.

2. Local PostgreSQL with Docker Compose

Run the database in a container so every developer has the same version. Match your production major version; the Domain India App Platform runs PostgreSQL 16.

# compose.yaml
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
      POSTGRES_DB: appdb
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata: {}

Start it with docker compose up -d, and put the connection string in .env (listed in .gitignore):

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

Binding the port to 127.0.0.1 keeps the development database off your network.

3. Install and initialise

npm install prisma --save-dev
npm install @prisma/client @prisma/adapter-pg dotenv
npx prisma init --datasource-provider postgresql

This creates prisma/schema.prisma and prisma.config.ts. The config file tells the CLI where the schema and migrations are and which database to use:

// prisma.config.ts
import 'dotenv/config';
import { defineConfig, env } from 'prisma/config';

export default defineConfig({
  schema: 'prisma/schema.prisma',
  migrations: { path: 'prisma/migrations' },
  datasource: { url: env('DATABASE_URL') },
});

In a monorepo, put Prisma in a shared package (for example packages/db) and import the client from there in both the API and the web app, so there is one schema and one migration history.

4. Model the schema

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "postgresql"
}

enum Role {
  USER
  ADMIN
}

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

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

  @@index([authorId, createdAt])
}

Add an @@index for every column combination you filter or sort on often. Foreign keys are not indexed automatically in PostgreSQL.

5. The migration workflow

For SQL that Prisma can't express, such as a partial unique index for soft deletes, run npx prisma migrate dev --create-only, add the SQL to the generated file, then apply it:

CREATE UNIQUE INDEX post_title_active
  ON "Post" ("authorId", "title")
  WHERE "deletedAt" IS NULL;

6. One client per process

Each Prisma Client holds a connection pool, so create one and share it. In development, hot reload re-runs your modules, so keep the instance on globalThis:

// src/db.ts
import { PrismaClient } from './generated/prisma/client';
import { PrismaPg } from '@prisma/adapter-pg';

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

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({ adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }) });

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

NestJS

Wrap the shared client in a provider so it can be injected, and close it on shutdown:

import { Injectable, OnModuleDestroy } from '@nestjs/common';
import { prisma } from '../db';

@Injectable()
export class PrismaService implements OnModuleDestroy {
  readonly db = prisma;
  async onModuleDestroy() { await prisma.$disconnect(); }
}

Register PrismaService in a global module, then inject it into your services and call this.prisma.db.user.findMany(...).

Next.js (App Router)

Query directly in server components, route handlers and server actions. Never import the client into a client component.

// app/api/posts/route.ts
import { prisma } from '@/db';

export async function GET() {
  const posts = await prisma.post.findMany({
    where: { published: true, deletedAt: null },
    select: { id: true, title: true, createdAt: true },
    orderBy: { createdAt: 'desc' },
    take: 20,
  });
  return Response.json(posts);
}

7. Query patterns worth copying

• Select only what you return. select keeps responses small and avoids leaking fields such as password hashes.
• Paginate every list. For large tables use a cursor: findMany({ take: 20, skip: 1, cursor: { id: lastId }, orderBy: { id: 'asc' } }). The skip: 1 excludes the cursor row itself.
• Scope every query in multi-tenant apps. Add where: { tenantId } in a shared helper rather than in each call, so one forgotten filter can't leak another tenant's data.
• Use raw SQL safely. prisma.$queryRaw with a tagged template parameterises values for you. Never build SQL strings with $queryRawUnsafe from user input.

Transactions

Use a transaction when several writes must all succeed or all fail:

await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email } });
  await tx.post.create({ data: { title: 'Welcome', authorId: user.id } });
});

Keep interactive transactions short and don't call external APIs inside them, because they hold database locks.

Error handling

Map Prisma's known error codes to HTTP responses:

import { Prisma } from './generated/prisma/client';

try {
  await prisma.user.create({ data: { email } });
} catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError && e.code === 'P2002') {
    return Response.json({ error: 'Email already registered' }, { status: 409 });
  }
  throw e;
}

P2002 is a unique-constraint violation and P2025 means the record to update or delete wasn't found. For cross-cutting behaviour such as audit logging, use Prisma Client extensions ($extends); the old $use middleware has been removed.

8. Testing and CI

Run tests against a real PostgreSQL, not mocks. In GitHub Actions, add a postgres:16 service container, set DATABASE_URL for the job, then run npm ci, npx prisma generate, npx prisma migrate deploy and your tests. Locally, a second Compose service on another port keeps test data apart from development data.

9. Running this on Domain India

• App Platform. Every plan includes a managed PostgreSQL 16 database, and the platform sets DATABASE_URL on your app as a system variable. Run migrations in your start command so each release updates the schema before serving traffic, for example "start": "prisma migrate deploy && node dist/main.js". Node.js apps are detected automatically; anything else needs a Dockerfile. The database is reachable only from your app, so Prisma Studio and psql on your laptop can't connect to it. Guides: PostgreSQL on the App Platform and App Platform: getting started.
• VPS. Self-managed with full root access: run PostgreSQL and your app in Docker Compose exactly as in development, and take your own database backups.
• Shared hosting. The databases on shared plans are MySQL, which Prisma supports, and Node.js apps run through the panel's Node.js tools on cPanel and DirectAdmin. MySQL port 3306 is closed from outside, so run migrations over jailed SSH (available on request, key login) or through an SSH tunnel. For a NestJS and PostgreSQL stack, the App Platform or a VPS is the better fit.

Prices on the cards are Domain India list prices and exclude 18% GST.

Ready to deploy your Prisma app? Compare App Platform plans with a PostgreSQL database included, or VPS servers for full control, and open a support ticket if a deploy can't reach its database.