Skip to content

Instantly share code, notes, and snippets.

@carefree-ladka

carefree-ladka/Prisma with Node.js & TypeScript: Complete Tutorial.mdx

Created March 20, 2026 15:10
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save carefree-ladka/e48751a5ace7e8b9d1dae05fc2b22b19 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save carefree-ladka/e48751a5ace7e8b9d1dae05fc2b22b19 to your computer and use it in GitHub Desktop.
Download ZIP
Prisma with Node.js & TypeScript: Complete Tutorial
Raw
Prisma with Node.js & TypeScript: Complete Tutorial.mdx

Prisma with Node.js & TypeScript: Complete Tutorial

From Basics to CRUD and Beyond

Table of Contents

  1. What is Prisma?
  2. Core Concepts & Theory
  3. Prerequisites
  4. Project Setup
  5. Prisma Initialization
  6. Defining the Schema
  7. Migrations
  8. Generating Prisma Client
  9. CRUD Operations
  10. Advanced Querying
  11. Seeding the Database
  12. Prisma Studio
  13. Error Handling
  14. Best Practices
  15. Quick Reference Cheat Sheet

1. What is Prisma?

Prisma is a next-generation, open-source ORM (Object-Relational Mapper) for Node.js and TypeScript. Unlike traditional ORMs, Prisma gives you:

  • Auto-generated, fully type-safe client — no more guessing query return types
  • A declarative schema — define your data models in a simple .prisma file
  • First-class migrations — version-control your database schema changes
  • An intuitive query API — reads like plain English, no raw SQL needed for most tasks

Prisma supports PostgreSQL, MySQL, SQLite, SQL Server, MongoDB, and CockroachDB.

Why Prisma over other ORMs? Traditional ORMs like TypeORM or Sequelize use class-based models and decorators, which can lead to runtime type errors. Prisma generates its client from your schema at build time, so TypeScript knows the exact shape of every query result — catching bugs before they hit production.

2. Core Concepts & Theory

2.1 ORM vs Query Builder vs Raw SQL

Approach Example Type Safety Abstraction
Raw SQL pg, mysql2 ❌ None Low
Query Builder knex ⚠️ Partial Medium
Traditional ORM TypeORM, Sequelize ⚠️ Partial High
Prisma @prisma/client ✅ Full High

2.2 Prisma Architecture

Prisma is composed of three main tools:

┌──────────────────────────────────────────────────────┐
│                   Your Application                   │
│                  (Node.js + TypeScript)               │
└────────────────────────┬─────────────────────────────┘
                         │  uses
┌────────────────────────▼─────────────────────────────┐
│               Prisma Client (@prisma/client)          │
│   Auto-generated, type-safe database query client    │
└────────────────────────┬─────────────────────────────┘
                         │  communicates via
┌────────────────────────▼─────────────────────────────┐
│                  Prisma Query Engine                  │
│  (Rust binary — translates Prisma queries to SQL)    │
└────────────────────────┬─────────────────────────────┘
                         │  talks to
┌────────────────────────▼─────────────────────────────┐
│                      Database                        │
│         PostgreSQL / MySQL / SQLite / etc.           │
└──────────────────────────────────────────────────────┘

The three Prisma tools:

  • Prisma Client — Auto-generated query client used in application code
  • Prisma Migrate — Declarative migration system to manage schema evolution
  • Prisma Studio — Web-based GUI to view and edit data in your database

2.3 Prisma Schema Language (PSL)

The Prisma Schema (schema.prisma) is the single source of truth for:

  1. Your database connection (datasource block)
  2. The Prisma Client generator (generator block)
  3. Your data models (model blocks)

Everything flows from this one file — Prisma generates your migrations AND your TypeScript types from it.

2.4 Prisma Client & Type Safety

When you run prisma generate, Prisma reads schema.prisma and emits a fully-typed client into node_modules/@prisma/client. This means:

// TypeScript KNOWS this is: { id: number; name: string; email: string }
const user = await prisma.user.findUnique({ where: { id: 1 } })

// ✅ This is fine
console.log(user?.name)

// ❌ TypeScript error — 'phone' doesn't exist on User
console.log(user?.phone)

No manual interface definitions needed. Your schema is your type definition.

3. Prerequisites

Before starting, ensure you have:

  • Node.js v18+ installed (node --version)
  • npm or pnpm or yarn
  • A running database (we'll use SQLite for simplicity, then show PostgreSQL)
  • Basic TypeScript knowledge

4. Project Setup

4.1 Initialize Node.js + TypeScript Project

# Create and enter your project directory
mkdir prisma-tutorial
cd prisma-tutorial

# Initialize a new npm project
npm init -y

4.2 Install Dependencies

# Runtime dependency
npm install @prisma/client

# Development dependencies
npm install -D prisma typescript ts-node @types/node nodemon
Package Purpose
@prisma/client The auto-generated Prisma query client
prisma Prisma CLI (migrations, generate, studio)
typescript TypeScript compiler
ts-node Run TypeScript files directly
@types/node Node.js type definitions
nodemon Auto-restart on file changes

4.3 Configure TypeScript

Create tsconfig.json in the project root:

{
  "compilerOptions": {
    "target": "ES2020",
    "module": "CommonJS",
    "lib": ["ES2020"],
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true,
    "outDir": "./dist",
    "rootDir": "./src",
    "resolveJsonModule": true
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules", "dist"]
}

Add scripts to package.json:

{
  "scripts": {
    "build": "tsc",
    "start": "node dist/index.js",
    "dev": "nodemon --exec ts-node src/index.ts",
    "db:migrate": "prisma migrate dev",
    "db:generate": "prisma generate",
    "db:studio": "prisma studio",
    "db:seed": "ts-node prisma/seed.ts"
  }
}

Create the source directory:

mkdir src

5. Prisma Initialization

5.1 Initialize Prisma

npx prisma init

This command creates:

prisma/
  schema.prisma     ← Your schema definition file
.env                ← Database connection URL (auto-created)

5.2 Configure the Database Provider

Option A — SQLite (easiest, no server needed):

Edit prisma/schema.prisma:

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

And remove the DATABASE_URL from .env (not needed for SQLite file path).

Option B — PostgreSQL:

.env file:

DATABASE_URL="postgresql://username:password@localhost:5432/mydb?schema=public"

prisma/schema.prisma:

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

Option C — MySQL:

.env file:

DATABASE_URL="mysql://username:password@localhost:3306/mydb"

prisma/schema.prisma:

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

Security: Always add .env to .gitignore — never commit database credentials.

6. Defining the Schema

6.1 Models & Fields

A model maps to a database table. Each field maps to a column.

model User {
  id    Int    @id @default(autoincrement())
  name  String
  email String @unique
}

This defines a users table with three columns: id, name, and email.

6.2 Field Types

Prisma Type Maps To (PostgreSQL) Maps To (SQLite)
String TEXT / VARCHAR TEXT
Int INTEGER INTEGER
BigInt BIGINT INTEGER
Float DOUBLE PRECISION REAL
Decimal DECIMAL DECIMAL
Boolean BOOLEAN INTEGER (0/1)
DateTime TIMESTAMP DATETIME
Json JSONB TEXT
Bytes BYTEA BLOB

6.3 Attributes & Modifiers

model Example {
  // Field attributes (single-field)
  id        Int      @id                    // Primary key
  uuid      String   @default(uuid())       // Auto UUID
  createdAt DateTime @default(now())        // Auto timestamp
  updatedAt DateTime @updatedAt             // Auto-updated timestamp
  email     String   @unique               // Unique constraint
  name      String?                         // Optional field (nullable)
  role      Role     @default(USER)        // Enum with default
  score     Float    @default(0.0)         // Numeric default
  data      Json?                           // Nullable JSON

  // Block attributes (multi-field)
  @@unique([email, name])                  // Composite unique
  @@index([createdAt])                     // Index for fast queries
  @@map("examples_table")                  // Custom table name
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Modifier suffixes:

Modifier Meaning Example
? Optional (nullable) String?
[] List / array relation Post[]
(none) Required String

6.4 Relations

One-to-Many (most common):

One User has many Posts.

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  posts Post[]              // "virtual" field — not in DB, enables dot-access
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  authorId Int
  author   User   @relation(fields: [authorId], references: [id])
}

One-to-One:

One User has one Profile.

model User {
  id      Int      @id @default(autoincrement())
  profile Profile?
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String
  userId Int    @unique
  user   User   @relation(fields: [userId], references: [id])
}

Many-to-Many (implicit — Prisma manages join table):

model Post {
  id   Int    @id @default(autoincrement())
  tags Tag[]
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

Many-to-Many (explicit — you manage join table):

model Post {
  id         Int         @id @default(autoincrement())
  categories CategoryOnPost[]
}

model Category {
  id    Int              @id @default(autoincrement())
  name  String
  posts CategoryOnPost[]
}

model CategoryOnPost {
  postId     Int
  categoryId Int
  assignedAt DateTime @default(now())
  post       Post     @relation(fields: [postId], references: [id])
  category   Category @relation(fields: [categoryId], references: [id])

  @@id([postId, categoryId])
}

6.5 Full Schema Example

// prisma/schema.prisma

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

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

enum Role {
  USER
  ADMIN
}

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

  @@index([email])
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  userId Int     @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Post {
  id          Int        @id @default(autoincrement())
  title       String
  content     String?
  published   Boolean    @default(false)
  authorId    Int
  author      User       @relation(fields: [authorId], references: [id])
  createdAt   DateTime   @default(now())
  tags        Tag[]

  @@index([authorId])
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

7. Migrations

7.1 What is a Migration?

A migration is a recorded change to your database schema. Prisma tracks these in the prisma/migrations/ folder. Each migration is a SQL file that transforms your database from one state to another.

prisma/
  migrations/
    20240101_init/
      migration.sql         ← SQL that creates initial tables
    20240115_add_profile/
      migration.sql         ← SQL that adds the Profile table
  schema.prisma

This makes your schema changes version-controlled and reproducible — every developer runs the same migrations.

7.2 Running Migrations

# Create and apply a new migration (development)
npx prisma migrate dev --name init

# Apply pending migrations (production)
npx prisma migrate deploy

# Reset database (drops all data, re-runs migrations) — dev only!
npx prisma migrate reset

# Check migration status
npx prisma migrate status

7.3 Migration Workflow

Edit schema.prisma
       │
       ▼
npx prisma migrate dev --name describe_change
       │
       ▼
Prisma diffs old ↔ new schema
       │
       ▼
Generates SQL migration file
       │
       ▼
Applies migration to dev DB
       │
       ▼
Runs `prisma generate` automatically

Important: migrate dev is for development only. Use migrate deploy in CI/CD and production pipelines.

8. Generating Prisma Client

After any schema change, regenerate the client:

npx prisma generate

Note: prisma migrate dev calls prisma generate automatically. You only need to run generate manually when the schema changes without a migration (e.g., adding/removing generators or when pulling from an existing database).

The generated client lives at node_modules/@prisma/client and contains types for every model, field, and query.

9. CRUD Operations

9.1 Setup the Client

Create src/client.ts — a singleton Prisma client to share across your app:

// src/client.ts
import { PrismaClient } from "@prisma/client"

const prisma = new PrismaClient({
  log: ["query", "info", "warn", "error"], // Log SQL queries in dev
})

export default prisma

Why singleton? PrismaClient manages a database connection pool. Instantiating it multiple times creates multiple pools and exhausts connections. Always reuse a single instance.

Create src/index.ts as the entry point:

// src/index.ts
import prisma from "./client"

async function main() {
  // Your code here
}

main()
  .catch(console.error)
  .finally(async () => {
    await prisma.$disconnect()
  })

9.2 CREATE — Insert Records

create — Insert a single record:

import prisma from "./client"

// Create a single user
const user = await prisma.user.create({
  data: {
    name: "Alice Johnson",
    email: "alice@example.com",
    role: "ADMIN",
  },
})

console.log(user)
// { id: 1, name: 'Alice Johnson', email: 'alice@example.com', role: 'ADMIN', ... }

create with nested relations — Create user + profile in one query:

const userWithProfile = await prisma.user.create({
  data: {
    name: "Bob Smith",
    email: "bob@example.com",
    profile: {
      create: {
        bio: "Software developer based in London.",
      },
    },
    posts: {
      create: [
        { title: "Hello World", content: "My first post!", published: true },
        { title: "Draft Post", published: false },
      ],
    },
  },
  include: {
    profile: true,
    posts: true,
  },
})

createMany — Insert multiple records at once:

const result = await prisma.user.createMany({
  data: [
    { name: "Carol", email: "carol@example.com" },
    { name: "Dave",  email: "dave@example.com"  },
    { name: "Eve",   email: "eve@example.com"   },
  ],
  skipDuplicates: true, // Skip records that violate unique constraints
})

console.log(result) // { count: 3 }

Note: createMany does NOT support nested relation creation. Use multiple create calls or a transaction for that.

9.3 READ — Query Records

findUnique — Fetch by unique field (id, email, etc.):

// By primary key
const userById = await prisma.user.findUnique({
  where: { id: 1 },
})

// By unique email
const userByEmail = await prisma.user.findUnique({
  where: { email: "alice@example.com" },
})

// Returns null if not found — always check!
if (!userById) {
  throw new Error("User not found")
}

findFirst — Fetch the first match:

const latestPost = await prisma.post.findFirst({
  where: { published: true },
  orderBy: { createdAt: "desc" },
})

findMany — Fetch multiple records:

// Get all users
const allUsers = await prisma.user.findMany()

// Get all published posts
const publishedPosts = await prisma.post.findMany({
  where: { published: true },
  orderBy: { createdAt: "desc" },
  take: 10,   // LIMIT 10
  skip: 0,    // OFFSET 0
})

findMany with related data:

const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: "desc" },
    },
    profile: true,
  },
})

// usersWithPosts[0].posts is fully typed as Post[]

select — Return only specific fields:

const userNames = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
    // password: true ← safely exclude sensitive fields
  },
})

// Returns: { id: number; name: string; email: string }[]
// NOT the full User type

include vs select: Use include to add related models on top of all fields. Use select to cherry-pick specific fields. You cannot use both at the top level simultaneously.

9.4 UPDATE — Modify Records

update — Update a single record (must match by unique field):

const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: {
    name: "Alice Johnson-Smith",
    role: "ADMIN",
  },
})

update with atomic operations:

// Increment, decrement, multiply, divide
await prisma.post.update({
  where: { id: 5 },
  data: {
    viewCount: { increment: 1 },   // viewCount + 1
    score:     { multiply: 1.1 },  // score * 1.1
  },
})

// For strings: set, push (for lists in PostgreSQL)
await prisma.user.update({
  where: { id: 1 },
  data: {
    name: { set: "New Name" },  // same as just: name: "New Name"
  },
})

upsert — Update if exists, create if not:

const user = await prisma.user.upsert({
  where: { email: "alice@example.com" },
  update: {
    name: "Alice Updated",
  },
  create: {
    name: "Alice New",
    email: "alice@example.com",
  },
})

updateMany — Update multiple records:

const result = await prisma.post.updateMany({
  where: { authorId: 1 },
  data: { published: true },
})

console.log(result) // { count: 5 } — number of records updated

Note: updateMany returns { count: number }, not the updated records. Use findMany after if you need the updated data.

9.5 DELETE — Remove Records

delete — Delete a single record:

const deletedUser = await prisma.user.delete({
  where: { id: 1 },
})
// Returns the deleted record

deleteMany — Delete multiple records:

// Delete all unpublished posts
const result = await prisma.post.deleteMany({
  where: { published: false },
})

console.log(result) // { count: 3 }

// Delete ALL records (use with caution!)
await prisma.user.deleteMany({})

Cascades: If you define onDelete: Cascade in your schema, deleting a User will also delete their Posts and Profile. Without it, deleting a User with existing Posts will throw a foreign key constraint error.

10. Advanced Querying

10.1 Filtering

Prisma's where clause supports rich filter operators:

const results = await prisma.user.findMany({
  where: {
    // Equality (implicit)
    role: "ADMIN",

    // String filters
    name: {
      contains: "Alice",      // LIKE '%Alice%'
      startsWith: "Al",       // LIKE 'Al%'
      endsWith: "ce",         // LIKE '%ce'
      mode: "insensitive",    // Case-insensitive (PostgreSQL)
    },

    // Numeric filters
    id: {
      gt: 5,   // >  5
      gte: 5,  // >= 5
      lt: 10,  // <  10
      lte: 10, // <= 10
      not: 7,  // != 7
    },

    // Date filters
    createdAt: {
      gte: new Date("2024-01-01"),
      lt:  new Date("2025-01-01"),
    },

    // IN / NOT IN
    id: { in: [1, 2, 3] },
    role: { notIn: ["BANNED"] },

    // Null checks
    profile: { is: null },     // WHERE profile IS NULL
    profile: { isNot: null },  // WHERE profile IS NOT NULL
  }
})

Combining filters with AND / OR / NOT:

const posts = await prisma.post.findMany({
  where: {
    AND: [
      { published: true },
      { createdAt: { gte: new Date("2024-01-01") } },
    ],
    OR: [
      { title: { contains: "TypeScript" } },
      { title: { contains: "Prisma" } },
    ],
    NOT: {
      authorId: 99,
    },
  },
})

Filtering on related models:

// Users who have at least one published post
const activeAuthors = await prisma.user.findMany({
  where: {
    posts: {
      some: { published: true },
    },
  },
})

// Users with NO posts
const usersNoPosts = await prisma.user.findMany({
  where: {
    posts: { none: {} },
  },
})

// Users where ALL posts are published
const fullyPublishedAuthors = await prisma.user.findMany({
  where: {
    posts: {
      every: { published: true },
    },
  },
})

10.2 Sorting & Pagination

Sorting:

// Sort by single field
const posts = await prisma.post.findMany({
  orderBy: { createdAt: "desc" },
})

// Sort by multiple fields
const users = await prisma.user.findMany({
  orderBy: [
    { role: "asc" },
    { name: "asc" },
  ],
})

// Sort by related field count
const popularAuthors = await prisma.user.findMany({
  orderBy: {
    posts: { _count: "desc" },
  },
})

Offset pagination (simple):

const PAGE_SIZE = 10

async function getPage(page: number) {
  return prisma.post.findMany({
    skip: (page - 1) * PAGE_SIZE,
    take: PAGE_SIZE,
    orderBy: { createdAt: "desc" },
  })
}

const page1 = await getPage(1) // First 10
const page2 = await getPage(2) // Next 10

Cursor-based pagination (efficient for large datasets):

// First page
const firstPage = await prisma.post.findMany({
  take: 10,
  orderBy: { id: "asc" },
})

// Next page — pass the last item's cursor
const lastId = firstPage[firstPage.length - 1].id

const nextPage = await prisma.post.findMany({
  take: 10,
  skip: 1,           // Skip the cursor itself
  cursor: { id: lastId },
  orderBy: { id: "asc" },
})

10.3 Select & Include (Relations)

include — Eager load related models:

const post = await prisma.post.findUnique({
  where: { id: 1 },
  include: {
    author: true,         // Include full User
    tags: true,           // Include all Tags
  },
})

// post.author is User, post.tags is Tag[]

Nested includes:

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      include: {
        tags: true,       // Include tags inside each post
      },
      where: { published: true },
      orderBy: { createdAt: "desc" },
      take: 5,
    },
    profile: true,
  },
})

select — Return specific fields only:

const userSummary = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    _count: {
      select: { posts: true }, // Include count of related posts
    },
  },
})

// userSummary[0]._count.posts → number

10.4 Aggregations

// Count
const totalUsers = await prisma.user.count()
const adminCount = await prisma.user.count({
  where: { role: "ADMIN" },
})

// Aggregate (min, max, sum, avg, count)
const stats = await prisma.post.aggregate({
  _count: { id: true },
  _avg:   { viewCount: true },
  _sum:   { viewCount: true },
  _min:   { createdAt: true },
  _max:   { createdAt: true },
  where:  { published: true },
})

console.log(stats._avg.viewCount) // average views

// GroupBy
const postsByAuthor = await prisma.post.groupBy({
  by: ["authorId"],
  _count: { id: true },
  _sum:   { viewCount: true },
  orderBy: { _count: { id: "desc" } },
})

10.5 Transactions

Sequential transactions (one fails → all roll back):

// Method 1: $transaction with array (parallel execution order not guaranteed)
const [newUser, newPost] = await prisma.$transaction([
  prisma.user.create({ data: { name: "Frank", email: "frank@example.com" } }),
  prisma.post.create({ data: { title: "Transaction Post", authorId: 1 } }),
])

Interactive transactions (full control with rollback):

const result = await prisma.$transaction(async (tx) => {
  // Use `tx` instead of `prisma` — all ops share one transaction
  const user = await tx.user.create({
    data: { name: "Grace", email: "grace@example.com" },
  })

  const post = await tx.post.create({
    data: {
      title: "Grace's First Post",
      authorId: user.id,
    },
  })

  // If anything throws here, the entire transaction rolls back
  if (!user || !post) {
    throw new Error("Creation failed")
  }

  return { user, post }
})

Use transactions when: you need multiple writes to succeed or fail together — e.g., transferring funds between accounts, creating a user + their default settings simultaneously.

11. Seeding the Database

Create prisma/seed.ts:

// prisma/seed.ts
import { PrismaClient } from "@prisma/client"

const prisma = new PrismaClient()

async function main() {
  console.log("🌱 Seeding database...")

  // Clean up existing data (optional)
  await prisma.post.deleteMany({})
  await prisma.user.deleteMany({})

  // Create users with nested posts
  const alice = await prisma.user.create({
    data: {
      name: "Alice Johnson",
      email: "alice@example.com",
      role: "ADMIN",
      profile: {
        create: { bio: "Full-stack developer and coffee enthusiast." },
      },
      posts: {
        create: [
          {
            title: "Getting Started with Prisma",
            content: "Prisma makes database access easy and type-safe...",
            published: true,
          },
          {
            title: "Advanced TypeScript Patterns",
            content: "Let's explore some advanced TypeScript techniques...",
            published: true,
          },
        ],
      },
    },
  })

  const bob = await prisma.user.create({
    data: {
      name: "Bob Smith",
      email: "bob@example.com",
      posts: {
        create: [
          { title: "My Draft Post", published: false },
        ],
      },
    },
  })

  console.log("✅ Seeded:", { alice: alice.name, bob: bob.name })
}

main()
  .catch((e) => {
    console.error("❌ Seeding failed:", e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

Register the seed script in package.json:

{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}

Run the seed:

npx prisma db seed
# or with npm script:
npm run db:seed

Seeding also runs automatically after prisma migrate reset.

12. Prisma Studio

Prisma Studio is a visual database browser. Launch it with:

npx prisma studio

This opens a web UI at http://localhost:5555 where you can:

  • Browse all tables and records
  • Filter, sort, and paginate data
  • Create, edit, and delete records manually
  • Explore relations between models

Great for debugging during development — no SQL client needed.

13. Error Handling

Prisma throws specific error types you can catch and handle:

import { PrismaClient, Prisma } from "@prisma/client"

const prisma = new PrismaClient()

async function createUser(email: string, name: string) {
  try {
    const user = await prisma.user.create({
      data: { email, name },
    })
    return user
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      // P2002 — Unique constraint violation
      if (error.code === "P2002") {
        const field = error.meta?.target as string[]
        throw new Error(`A user with this ${field?.join(", ")} already exists.`)
      }

      // P2025 — Record not found
      if (error.code === "P2025") {
        throw new Error("Record not found.")
      }

      // P2003 — Foreign key constraint failed
      if (error.code === "P2003") {
        throw new Error("Related record does not exist.")
      }

      console.error("Prisma error code:", error.code)
    }

    if (error instanceof Prisma.PrismaClientValidationError) {
      throw new Error("Invalid data provided to Prisma.")
    }

    throw error // Re-throw unknown errors
  }
}

Common Prisma error codes:

Code Meaning
P2002 Unique constraint violation
P2003 Foreign key constraint failed
P2025 Record not found
P2014 Relation violation
P2000 Value too long for column
P1001 Can't reach database server
P1002 Database server timeout

14. Best Practices

1. Singleton Prisma Client

Always export and reuse a single PrismaClient instance. In Next.js (which hot-reloads in dev), use this pattern:

// src/lib/prisma.ts
import { PrismaClient } from "@prisma/client"

declare global {
  var __prisma: PrismaClient | undefined
}

const prisma = globalThis.__prisma ?? new PrismaClient()

if (process.env.NODE_ENV !== "production") {
  globalThis.__prisma = prisma
}

export default prisma

2. Always disconnect in scripts

async function main() { /* ... */ }

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

3. Use select for sensitive data

Never return the full model when only a subset is needed:

// ✅ Safe — never accidentally expose password hash
const user = await prisma.user.findUnique({
  where: { id },
  select: { id: true, name: true, email: true },
})

4. Validate before writing

Use Zod or similar to validate input before passing to Prisma:

import { z } from "zod"

const CreateUserSchema = z.object({
  name: z.string().min(2).max(50),
  email: z.string().email(),
})

const validated = CreateUserSchema.parse(requestBody)
const user = await prisma.user.create({ data: validated })

5. Use transactions for multi-step writes

Any time two or more writes must succeed together, use $transaction.

6. Add indexes for frequently queried fields

model Post {
  id       Int     @id @default(autoincrement())
  authorId Int
  // ↓ Without this, queries like findMany({ where: { authorId } }) do a full scan
  @@index([authorId])
}

7. Enable query logging in development

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

15. Quick Reference Cheat Sheet

// ── SETUP ──────────────────────────────────────────────
import { PrismaClient } from "@prisma/client"
const prisma = new PrismaClient()

// ── CREATE ─────────────────────────────────────────────
await prisma.user.create({ data: { name, email } })
await prisma.user.createMany({ data: [...], skipDuplicates: true })

// ── READ ───────────────────────────────────────────────
await prisma.user.findUnique({ where: { id } })
await prisma.user.findFirst({ where: { role: "ADMIN" } })
await prisma.user.findMany({ where: {...}, orderBy: {...}, take, skip })

// ── UPDATE ─────────────────────────────────────────────
await prisma.user.update({ where: { id }, data: { name } })
await prisma.user.upsert({ where: { email }, update: {...}, create: {...} })
await prisma.user.updateMany({ where: {...}, data: {...} })

// ── DELETE ─────────────────────────────────────────────
await prisma.user.delete({ where: { id } })
await prisma.user.deleteMany({ where: {...} })

// ── RELATIONS ──────────────────────────────────────────
// Eager load
await prisma.user.findUnique({ where: { id }, include: { posts: true } })
// Nested create
await prisma.user.create({ data: { ..., posts: { create: [...] } } })

// ── FILTERS ────────────────────────────────────────────
where: { name: { contains: "Alice", mode: "insensitive" } }
where: { id: { gt: 5, lte: 20 } }
where: { AND: [...], OR: [...], NOT: {...} }
where: { posts: { some: { published: true } } }

// ── PAGINATION ─────────────────────────────────────────
{ take: 10, skip: 20 }                          // Offset
{ take: 10, cursor: { id: lastId }, skip: 1 }  // Cursor

// ── AGGREGATION ────────────────────────────────────────
await prisma.post.count({ where: { published: true } })
await prisma.post.aggregate({ _avg: { views: true } })
await prisma.post.groupBy({ by: ["authorId"], _count: true })

// ── TRANSACTIONS ───────────────────────────────────────
await prisma.$transaction([op1, op2, op3])
await prisma.$transaction(async (tx) => { /* use tx.model... */ })

// ── CLI ────────────────────────────────────────────────
// npx prisma init
// npx prisma migrate dev --name <name>
// npx prisma migrate deploy
// npx prisma generate
// npx prisma db seed
// npx prisma studio
// npx prisma migrate reset

Generated with ❤️ — Prisma v5.x | Node.js v18+ | TypeScript v5.x

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment