What is Prisma and how does it differ from Mongoose?

Prisma is a next-generation ORM for SQL databases (PostgreSQL, MySQL, SQLite, SQL Server). Unlike Mongoose, which is an ODM for MongoDB, Prisma works with relational databases that enforce schemas at the database level. Prisma adds a schema file (schema.prisma), a migration system that generates and runs SQL, a type-safe auto-generated client, and a visual database browser (Prisma Studio). The key difference is type safety — Prisma generates TypeScript types from your schema, so your editor catches type errors before they reach the database.

What is the difference between prisma migrate dev and prisma migrate deploy?

prisma migrate dev is for development — it creates a new migration file from schema changes, applies it to the database, and regenerates Prisma Client. prisma migrate deploy is for production — it applies pending migration files that already exist without creating new ones or prompting interactively. Always run prisma migrate deploy in CI/CD and never prisma migrate dev in production.

What is Prisma Client and how does it work?

Prisma Client is an auto-generated, type-safe database client. After you run prisma generate, Prisma reads your schema.prisma and produces a Node.js module with methods for every model: prisma.user.findMany(), prisma.post.create(), and so on. The client types are derived from your schema, so TypeScript knows the exact shape of every query result. You import and instantiate it once (new PrismaClient()) and reuse it across the application.

Should I use one PrismaClient instance or create a new one per request?

Always use a single shared PrismaClient instance for the entire application. Prisma Client maintains a connection pool internally, and creating multiple instances creates multiple pools, quickly exhausting database connections. Export a singleton from a dedicated file (lib/prisma.js) and import it wherever you need database access.

How do I handle Prisma errors in Express?

Prisma throws typed errors from the @prisma/client/runtime namespace. The two most common are PrismaClientKnownRequestError (with error codes like P2002 for unique constraint violation and P2025 for record not found) and PrismaClientValidationError (invalid query shape). Catch these in the global Express error handler and map them to the appropriate HTTP status codes.

← Back to Node.js Full‑Stack Course

PostgreSQL & Prisma ORM in Node.js

PostgreSQL is one of the most capable open-source relational databases in existence. It has ACID transactions, foreign key enforcement, advanced indexing, JSON columns, full-text search, and a 30-year track record of reliability. When your data has clear relationships and integrity matters, PostgreSQL is the right choice.

Prisma makes PostgreSQL productive in Node.js. It replaces hand-written SQL with a type-safe, auto-generated client, and replaces manual schema management with a migration system that tracks every change.

This is Module 15 of the Node.js Full‑Stack Developer course.

Installing Prisma

bash

# Install Prisma CLI as a dev dependency
npm install --save-dev prisma

# Install Prisma Client (runtime dependency)
npm install @prisma/client

# Initialise Prisma — creates prisma/schema.prisma and .env
npx prisma init --datasource-provider postgresql

This creates:

text

prisma/
  schema.prisma    ← schema definition
.env               ← DATABASE_URL

Configuring the Database URL

text

# .env
DATABASE_URL="postgresql://postgres:password@localhost:5432/myapp?schema=public"

For production (Railway, Supabase, Neon, AWS RDS):

text

DATABASE_URL="postgresql://user:pass@host:5432/dbname?sslmode=require"

Run PostgreSQL locally with Docker:

yaml

# docker-compose.yml
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: myapp
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

The Prisma Schema

prisma/schema.prisma is the single source of truth for your database. Prisma reads it to generate migrations and Prisma Client.

prisma

// prisma/schema.prisma

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

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

model User {
  id        String   @id @default(cuid())
  name      String
  email     String   @unique
  password  String
  role      Role     @default(USER)
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts     Post[]
  profile   Profile?

  @@index([email])
  @@index([role, isActive])
}

model Profile {
  id     String  @id @default(cuid())
  bio    String?
  avatar String?

  userId String @unique
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Post {
  id        String   @id @default(cuid())
  title     String
  body      String
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  authorId String
  author   User   @relation(fields: [authorId], references: [id])

  tags     Tag[]  @relation("PostTags")

  @@index([authorId])
  @@index([published, createdAt(sort: Desc)])
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[] @relation("PostTags")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Schema Concepts

Concept	Purpose
`@id`	Primary key
`@default(cuid())`	Auto-generate a collision-resistant ID
`@unique`	Unique constraint
`@updatedAt`	Auto-update timestamp on every write
`@relation`	Defines foreign key and join
`@@index`	Composite database index
`onDelete: Cascade`	Delete related records when parent is deleted
`enum`	PostgreSQL enum type

Migrations

After writing your schema, create a migration:

bash

# Development — creates migration file + applies it + regenerates client
npx prisma migrate dev --name init

# After changing the schema, create the next migration
npx prisma migrate dev --name add_profile_table

# Production — apply existing migrations (never creates new ones)
npx prisma migrate deploy

# Reset database (dev only — wipes all data)
npx prisma db push --force-reset

Migrations live in prisma/migrations/ and must be committed to version control. Each migration is a timestamped folder with a migration.sql file.

text

prisma/
  migrations/
    20260512000000_init/
      migration.sql
    20260512120000_add_profile_table/
      migration.sql
  schema.prisma

Prisma Client Singleton

Never create new PrismaClient() inside route handlers. Create one shared instance:

// lib/prisma.js
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis;

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development' ? ['query', 'warn', 'error'] : ['error'],
  });

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

The globalThis trick prevents creating multiple Prisma Client instances during hot-reloading in development (Next.js / nodemon).

CRUD Operations

Create

import { prisma } from '../../lib/prisma.js';

// Create a single record
const user = await prisma.user.create({
  data: {
    name: 'Alice',
    email: 'alice@example.com',
    password: hashedPassword,
    role: 'ADMIN',
  },
});

// Create with nested record (user + profile in one transaction)
const user = await prisma.user.create({
  data: {
    name: 'Bob',
    email: 'bob@example.com',
    password: hashedPassword,
    profile: {
      create: {
        bio: 'Full-stack developer',
        avatar: 'https://example.com/avatar.jpg',
      },
    },
  },
  include: { profile: true },  // return the nested profile too
});

// Create many (skips duplicates with skipDuplicates)
await prisma.user.createMany({
  data: [
    { name: 'Carol', email: 'carol@example.com', password: pw1 },
    { name: 'Dave',  email: 'dave@example.com',  password: pw2 },
  ],
  skipDuplicates: true,
});

Read

// Find one by primary key
const user = await prisma.user.findUnique({
  where: { id: '...' },
});

// Find one by unique field
const user = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
  select: { id: true, name: true, email: true, role: true },  // projection
});

// Find first matching record
const user = await prisma.user.findFirst({
  where: { role: 'ADMIN', isActive: true },
  orderBy: { createdAt: 'desc' },
});

// Find many with filtering, sorting, pagination
const users = await prisma.user.findMany({
  where: {
    isActive: true,
    role: { in: ['USER', 'MODERATOR'] },
    createdAt: { gte: new Date('2025-01-01') },
    name: { contains: 'ali', mode: 'insensitive' },
  },
  orderBy: { createdAt: 'desc' },
  skip: 20,
  take: 10,
  select: {
    id: true,
    name: true,
    email: true,
    role: true,
    _count: { select: { posts: true } },  // count related posts
  },
});

// Count
const total = await prisma.user.count({ where: { isActive: true } });

Update

// Update by primary key
const user = await prisma.user.update({
  where: { id: '...' },
  data: { name: 'Alice Updated', isActive: false },
});

// Update or create (upsert)
const user = await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  create: { name: 'Alice', email: 'alice@example.com', password: pw },
  update: { name: 'Alice' },
});

// Update many
const result = await prisma.user.updateMany({
  where: { isActive: false, updatedAt: { lt: new Date('2025-01-01') } },
  data: { role: 'USER' },
});
console.log(result.count); // number of updated records

Delete

// Delete by primary key
const deleted = await prisma.user.delete({
  where: { id: '...' },
});

// Delete many
const result = await prisma.user.deleteMany({
  where: { isActive: false, createdAt: { lt: new Date('2024-01-01') } },
});

Relations

Include (Eager Loading)

// User with their posts
const user = await prisma.user.findUnique({
  where: { id },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
    profile: true,
  },
});

// Post with author and tags
const post = await prisma.post.findUnique({
  where: { id },
  include: {
    author: { select: { id: true, name: true, email: true } },
    tags: true,
  },
});

Many-to-Many (Tags)

// Connect existing tags to a post
const post = await prisma.post.create({
  data: {
    title: 'My Post',
    body: '...',
    authorId: userId,
    tags: {
      connectOrCreate: [
        { where: { name: 'nodejs' }, create: { name: 'nodejs' } },
        { where: { name: 'express' }, create: { name: 'express' } },
      ],
    },
  },
  include: { tags: true },
});

// Disconnect a tag
await prisma.post.update({
  where: { id: postId },
  data: {
    tags: { disconnect: { name: 'express' } },
  },
});

Transactions

Use transactions when multiple writes must succeed or fail together:

// Sequential operations in a transaction
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { ... } }),
  prisma.post.create({ data: { ... } }),
]);

// Interactive transaction (more control)
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id: senderId } });
  if (user.balance < amount) throw new Error('Insufficient balance');

  await tx.user.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } },
  });

  await tx.user.update({
    where: { id: recipientId },
    data: { balance: { increment: amount } },
  });

  return tx.transfer.create({
    data: { senderId, recipientId, amount },
  });
});

Handling Prisma Errors in Express

// middleware/errorHandler.js
import { Prisma } from '@prisma/client';

export function errorHandler(err, req, res, next) {
  // Unique constraint violation (e.g., duplicate email)
  if (err instanceof Prisma.PrismaClientKnownRequestError) {
    if (err.code === 'P2002') {
      const field = err.meta?.target?.[0] ?? 'field';
      return res.status(409).json({
        status: 'error',
        message: `${field} is already taken`,
      });
    }

    // Record not found
    if (err.code === 'P2025') {
      return res.status(404).json({
        status: 'error',
        message: err.meta?.cause ?? 'Record not found',
      });
    }

    // Foreign key constraint violation
    if (err.code === 'P2003') {
      return res.status(400).json({
        status: 'error',
        message: 'Related record not found',
      });
    }
  }

  // Invalid query (wrong types, missing required fields)
  if (err instanceof Prisma.PrismaClientValidationError) {
    return res.status(400).json({
      status: 'error',
      message: 'Invalid request data',
    });
  }

  const status = err.statusCode || 500;
  res.status(status).json({ status: 'error', message: err.message });
}

Common Prisma error codes:

Code	Meaning
P2002	Unique constraint failed
P2003	Foreign key constraint failed
P2025	Record not found
P2016	Query interpretation error
P1001	Can't reach database server

Pagination with Prisma

// features/posts/posts.service.js
export async function listPosts({ page = 1, limit = 20, authorId } = {}) {
  const pageNum = Math.max(1, +page);
  const take = Math.min(100, Math.max(1, +limit));
  const skip = (pageNum - 1) * take;

  const where = {
    published: true,
    ...(authorId && { authorId }),
  };

  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      where,
      orderBy: { createdAt: 'desc' },
      skip,
      take,
      include: {
        author: { select: { id: true, name: true } },
        tags: { select: { name: true } },
        _count: { select: { comments: true } },
      },
    }),
    prisma.post.count({ where }),
  ]);

  return {
    data: posts,
    meta: {
      total,
      page: pageNum,
      limit: take,
      totalPages: Math.ceil(total / take),
    },
  };
}

Prisma Studio

Prisma ships a visual database browser you can open in any browser:

bash

npx prisma studio

Navigate to http://localhost:5555 to browse, filter, create, and delete records without writing SQL. Use it during development — never expose it in production.

Seeding the Database

Create a seed script to populate development data:

// prisma/seed.js
import { prisma } from '../lib/prisma.js';
import bcrypt from 'bcrypt';

async function main() {
  const password = await bcrypt.hash('password123', 12);

  const admin = await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      name: 'Admin User',
      email: 'admin@example.com',
      password,
      role: 'ADMIN',
    },
  });

  await prisma.post.createMany({
    data: [
      { title: 'Hello World', body: 'First post', authorId: admin.id, published: true },
      { title: 'Draft Post',  body: 'Not ready',  authorId: admin.id, published: false },
    ],
    skipDuplicates: true,
  });

  console.log('Seed complete');
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

json

{
  "prisma": {
    "seed": "node prisma/seed.js"
  }
}

Run with:

bash

npx prisma db seed

Prisma vs Raw SQL

Prisma does not prevent you from writing raw SQL when you need it:

// Raw query — returns typed results
const users = await prisma.$queryRaw`
  SELECT id, name, email
  FROM "User"
  WHERE "createdAt" > ${new Date('2025-01-01')}
  ORDER BY "createdAt" DESC
  LIMIT 10
`;

// Raw mutation
await prisma.$executeRaw`
  UPDATE "User" SET "isActive" = false
  WHERE "lastLogin" < NOW() - INTERVAL '1 year'
`;

Use raw SQL for complex reporting queries or when Prisma's query builder cannot express what you need.

Node.js Full‑Stack Course — Module 15 of 32

You can now build a type-safe data layer with Prisma and PostgreSQL. Continue to Module 16 to learn how to model database relationships and joins.

Summary

Prisma makes PostgreSQL productive in Node.js:

Define your entire database schema in schema.prisma — Prisma generates the SQL
prisma migrate dev creates and applies migrations during development; prisma migrate deploy applies them in production
Export a singleton PrismaClient instance from lib/prisma.js — never create one per request
findUnique, findMany, create, update, delete cover all CRUD needs with full TypeScript safety
include and select control eager loading — always select only the fields you need
$transaction groups multiple writes into an atomic operation
Handle PrismaClientKnownRequestError codes (P2002, P2025, P2003) in the global error handler
npx prisma studio gives a visual database browser during development

Continue to Module 16: Database Relationships & Joins →