Prisma ORM - Complete Guide

Every backend app needs a database. The question is how you talk to it. You have options: raw SQL, a query builder like Knex, a traditional ORM like TypeORM or Sequelize, or Prisma. Each solves a different problem.

The Problem Prisma Solves

Raw SQL

Raw SQL is powerful, but as your application grows. The core problem with Raw SQL in a codebase is that your code editor and your database are completely blind to each other.

• You write SQL as strings. This means you get no autocomplete suggestions to help you write queries faster or accurately.
• Because the code editor doesn’t know exactly what data the database is returning, it cannot catch your mistakes. If you accidentally type user.emai instead of user.email, there are no “compile-time errors”.
• If you decide to rename a column in your database (for example, changing email to email_address), your raw SQL strings won’t automatically update or warn you.Your application will just silently break.

// No type safety - result is `any`
const result = await db.query(
  'SELECT * FROM users WHERE id = $1',
  [userId]
);
const user = result.rows[0]; // user.emai? no error. typo flies.

Traditional ORMs (TypeORM & Sequelize)

They fix the raw SQL problems, but they bring their own headaches:

• Messy Code: You have to add lots of confusing tags, squishing your database rules and your regular code into one place.
• Sneaky Slowdowns: They try to fetch data automatically in the background, which can accidentally overload your database and slow down your app.
• Out of Sync: Your code and your actual database easily stop matching up, which causes things to break when you make updates.
• Poor Safety Nets: They get confused when dealing with linked data, so they stop warning you about typos and errors.

What Prisma Does Differently

Prisma separates three concerns cleanly:

1. Prisma Schema: single source of truth for your data model (.prisma file)
2. Prisma Migrate: generates and tracks SQL migrations from schema changes
3. Prisma Client: fully type-safe, auto-generated query client

Your Schema  ->  Prisma Migrate  ->  Database
                                       |
             Prisma Client  <-  Generated Types

The Client is generated, not written by hand. Change your schema, regenerate the client, and TypeScript immediately shows you every place in your app that broke.

Installation

1. Install Dependencies

npm install prisma --save-dev
npm install @prisma/client

2. Initialize Prisma

npx prisma init

This creates:

prisma/
└── schema.prisma   # your data model
.env                # DATABASE_URL goes here

3. Set Database URL

Edit .env:

# PostgreSQL
DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

# MySQL
DATABASE_URL="mysql://user:password@localhost:3306/mydb"

# SQLite (great for local dev)
DATABASE_URL="file:./dev.db"

4. Define Your Schema

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

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

• The generator client block tells Prisma to use the prisma-client-js provider. When you run npx prisma generate or npx prisma migrate, it uses this setup to automatically create the Prisma Client library, which is used to query your database set up in datasource db block.

5. Run Migration

npx prisma migrate dev --name init

• It generates the SQL, updates your database to match your models, and automatically regenerates your Prisma Client.

6. Generate Client (Only when needed)

npx prisma generate

Note: You do not need to run this after a migration. You only need this command when you clone an existing project from GitHub or add a new package, and you just need to set up the Prisma Client without altering the database.

Schema Syntax

Data Types

model Example {
  id        Int      @id @default(autoincrement())  // integer PK, auto-increment
  uuid      String   @id @default(uuid())            // UUID PK alternative
  name      String                                   // NOT NULL varchar
  bio       String?                                  // nullable (? = optional)
  age       Int
  score     Float
  isActive  Boolean  @default(true)
  data      Json                                     // JSON column
  bytes     Bytes                                    // binary
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt                      // auto-updates on save
}

Field Attributes

model User {
  id        Int    @id @default(autoincrement())
  firstName String @map("first_name")     // DB column = first_name
  lastName  String @map("last_name")

  @@map("users")                           // DB table = users
  @@index([firstName, lastName])
}

Relations

One-to-Many (one user has many posts):

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]   // virtual field - no DB column
}

model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id])
  authorId Int  // actual FK column in DB
}

One-to-One (user has one profile):

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

model Profile {
  id     Int  @id @default(autoincrement())
  user   User @relation(fields: [userId], references: [id])
  userId Int  @unique   // @unique makes it one-to-one
}

Many-to-Many (posts have many tags, tags belong to many posts):

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

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}
// Prisma auto-creates the join table - no explicit join model needed

Explicit Many-to-Many (with extra fields on the join):

model Post {
  id     Int          @id @default(autoincrement())
  tagged PostOnTag[]
}

model Tag {
  id    Int        @id @default(autoincrement())
  posts PostOnTag[]
}

model PostOnTag {
  post      Post     @relation(fields: [postId], references: [id])
  postId    Int
  tag       Tag      @relation(fields: [tagId], references: [id])
  tagId     Int
  assignedAt DateTime @default(now())

  @@id([postId, tagId])  // composite PK
}

Enums

enum Role {
  USER
  ADMIN
  MODERATOR
}

model User {
  id   Int  @id @default(autoincrement())
  role Role @default(USER)
}

Prisma Client - Querying

Import and instantiate once (singleton pattern):

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

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

export const prisma =
  globalForPrisma.prisma ?? new PrismaClient({ log: ['query'] });

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

The singleton pattern prevents creating hundreds of connections during hot-reload in development.

CRUD

Create:

// Single record
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
  },
});

// Create with nested relation
const userWithPost = await prisma.user.create({
  data: {
    email: 'bob@example.com',
    posts: {
      create: {
        title: 'Hello World',
        content: 'My first post',
      },
    },
  },
});

// Create many
await prisma.user.createMany({
  data: [
    { email: 'a@example.com' },
    { email: 'b@example.com' },
  ],
  skipDuplicates: true,
});

Read:

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

// Find first matching
const user = await prisma.user.findFirst({
  where: { name: { contains: 'ali' } },
});

// Find many with filters
const users = await prisma.user.findMany({
  where: {
    name: { not: null },
    createdAt: { gte: new Date('2025-01-01') },
  },
  orderBy: { createdAt: 'desc' },
  skip: 0,
  take: 10,
});

Update:

// Update one by unique field
const updated = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Updated' },
});

// Upsert (create if not exists, update if exists)
const user = await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  update: { name: 'Alice' },
  create: { email: 'alice@example.com', name: 'Alice' },
});

// Update many
await prisma.post.updateMany({
  where: { published: false },
  data: { published: true },
});

Delete:

await prisma.user.delete({ where: { id: 1 } });
await prisma.post.deleteMany({ where: { authorId: 1 } });

Filtering

// String filters
where: { name: { contains: 'alice', mode: 'insensitive' } }
where: { email: { startsWith: 'admin' } }
where: { email: { endsWith: '@company.com' } }

// Number filters
where: { age: { gt: 18, lte: 65 } }

// Null checks
where: { bio: null }          // IS NULL
where: { bio: { not: null } } // IS NOT NULL

// IN / NOT IN
where: { id: { in: [1, 2, 3] } }
where: { id: { notIn: [4, 5] } }

// AND / OR / NOT
where: {
  AND: [
    { email: { contains: '@example.com' } },
    { name: { not: null } },
  ],
}

where: {
  OR: [
    { role: 'ADMIN' },
    { role: 'MODERATOR' },
  ],
}

Including Relations

// Include related records
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,           // include all posts
    profile: true,
  },
});

// Nested include
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      include: {
        tags: true,
      },
      where: { published: true },
      orderBy: { createdAt: 'desc' },
    },
  },
});

// Select only specific fields (more efficient than include)
const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    email: true,
    posts: {
      select: { title: true, published: true },
    },
  },
});

Aggregations

const stats = await prisma.post.aggregate({
  _count: { id: true },
  _avg: { viewCount: true },
  _max: { createdAt: true },
  where: { published: true },
});

// Group by
const postsByUser = await prisma.post.groupBy({
  by: ['authorId'],
  _count: { id: true },
  having: { id: { _count: { gt: 5 } } },
});

Transactions

// Sequential transactions (result of one feeds next)
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: 'x@x.com' } }),
  prisma.post.create({ data: { title: 'Post', authorId: 1 } }),
]);

// Interactive transactions (async callback - full rollback on throw)
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email: 'y@y.com' } });
  await tx.post.create({
    data: { title: 'Post', authorId: user.id },
  });
  // if anything throws here, entire transaction rolls back
});

Raw Queries

Sometimes you need raw SQL for complex queries:

// Raw query - returns typed result with $queryRaw
const users = await prisma.$queryRaw<User[]>`
  SELECT * FROM users WHERE email LIKE ${`%${domain}`}
`;

// Raw execute (INSERT/UPDATE/DELETE) - returns count
const count = await prisma.$executeRaw`
  UPDATE users SET last_seen = NOW() WHERE id = ${userId}
`;

Template literals in $queryRaw are automatically parameterized, so they are safe from SQL injection.

Migrations

Migrations track how your schema changes over time. Every migration is a SQL file stored in prisma/migrations/.

# Create and apply a migration in dev
npx prisma migrate dev --name add_user_avatar

# Apply pending migrations in production (no prompt, no schema changes)
npx prisma migrate deploy

# See migration status
npx prisma migrate status

# Reset DB (drop all data + re-apply all migrations) - dev only
npx prisma migrate reset

Generated migration file looks like:

-- prisma/migrations/20260430000000_add_user_avatar/migration.sql
ALTER TABLE "User" ADD COLUMN "avatar" TEXT;

Prisma generates the diff automatically, so you rarely write SQL by hand.

Prototyping shortcut (skip migration files):

npx prisma db push

Pushes schema directly to DB without creating migration files. Good for rapid prototyping, not for production since there is no history and no rollback.

How Prisma Works Under the Hood

Architecture

Your Code (TypeScript)
        |
Prisma Client (generated JS)
        |
Query Engine (Rust binary / WASM)
        |
Database Driver (pg, mysql2, etc.)
        |
Database

The Query Engine

Prisma’s query engine is written in Rust. It sits between your TypeScript code and the database driver. It handles:

1. Translating Prisma’s query DSL into SQL
2. Connection pooling: manages a pool of DB connections
3. Query optimization: batching, relation resolution
4. Protocol handling: speaks the database wire protocol

The engine ships as a compiled binary alongside your project (in node_modules/.prisma/client/). When you run prisma generate, the correct binary for your OS/architecture is downloaded.

In edge environments (Cloudflare Workers, Vercel Edge), Prisma uses a WASM-compiled version of the engine instead of the native binary.

Generated Client

npx prisma generate reads your schema.prisma and outputs:

node_modules/@prisma/client/
├── index.js          # runtime client
├── index.d.ts        # all TypeScript types (fully inferred from schema)
└── ...

Every model, field, relation, and enum becomes a TypeScript type. You never write these by hand. This is why TypeScript errors appear immediately when schema changes: the types are regenerated.

N+1 Prevention

Traditional ORMs with lazy loading cause N+1 queries:

GET /users           -> SELECT * FROM users         (1 query)
user[0].posts        -> SELECT * FROM posts WHERE authorId = 1
user[1].posts        -> SELECT * FROM posts WHERE authorId = 2
user[2].posts        -> SELECT * FROM posts WHERE authorId = 3
...                  -> N more queries

Prisma uses eager loading. You declare what you want upfront with include. The query engine then uses JOIN or a batched IN query depending on relation type, loading everything in minimal round-trips.

// 2 queries total regardless of how many users
const users = await prisma.user.findMany({
  include: { posts: true },
});
// Query 1: SELECT * FROM users
// Query 2: SELECT * FROM posts WHERE authorId IN (1, 2, 3, ...)

Connection Pooling

Prisma Client maintains a connection pool via the query engine. Default pool size is num_physical_cpus * 2 + 1.

Configure in the connection URL:

DATABASE_URL="postgresql://user:pass@host/db?connection_limit=10&pool_timeout=10"

In serverless environments (Lambda, Vercel Functions), each function instance creates its own connection pool. This can exhaust DB connections fast. Use PgBouncer or Prisma Accelerate for connection pooling at the infrastructure level.

Prisma Studio

GUI for browsing and editing your database:

npx prisma studio

Opens at http://localhost:5555. Useful for debugging data in development.

Best Practices

1. Singleton Client

Never create new PrismaClient() in every request. One instance, shared across the app (see the singleton pattern shown above).

2. Select Only What You Need

// Bad - fetches all columns including large blobs
const users = await prisma.user.findMany();

// Good - only what the endpoint needs
const users = await prisma.user.findMany({
  select: { id: true, email: true, name: true },
});

3. Declare Relations Explicitly

Don’t load relations unless you need them. Every include is extra queries.

4. Index Your Filter Fields

If you frequently query by a field, add an index in the schema:

model Post {
  id        Int      @id @default(autoincrement())
  authorId  Int
  published Boolean
  createdAt DateTime @default(now())

  @@index([authorId])
  @@index([published, createdAt])
}

Run prisma migrate dev after adding indexes.

5. Use select Over include for Performance

include fetches the full related model. select lets you pick exact fields across relations:

const posts = await prisma.post.findMany({
  select: {
    title: true,
    author: {
      select: { name: true },  // only name, not the whole User
    },
  },
});

6. Use Interactive Transactions for Multi-Step Operations

Any operation that must be atomic (transfer funds, create user + send email record) should use $transaction with a callback.

7. Never Expose Prisma Client Directly in API Responses

Prisma returns full DB objects. Strip sensitive fields before sending:

const user = await prisma.user.findUnique({ where: { id } });
const { passwordHash, ...safeUser } = user; // never send passwordHash
return safeUser;

Or use select to never fetch them in the first place.

8. Handle Prisma Errors

import { PrismaClientKnownRequestError } from '@prisma/client/runtime/library';

try {
  await prisma.user.create({ data: { email: 'duplicate@example.com' } });
} catch (e) {
  if (e instanceof PrismaClientKnownRequestError) {
    if (e.code === 'P2002') {
      // Unique constraint violation
      throw new Error('Email already in use');
    }
  }
  throw e;
}

Common error codes:

• P2002: Unique constraint failed
• P2025: Record not found (for update/delete on missing record)
• P2003: Foreign key constraint failed

9. Logging in Development

const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
});

Shows every SQL query in the terminal. Set to ['warn', 'error'] in production.

10. Keep Migrations in Version Control

prisma/migrations/ must be committed to git. This is how production knows what SQL to apply during deployment. Never delete migration files.

Prisma vs Alternatives

Use Prisma when: TypeScript project, you want type safety, you want migrations managed.

Use raw SQL / Knex when: performance-critical queries, complex reporting, or you want full SQL control.