Prisma ORM: A Complete Guide for TypeScript Developers

Prisma is the ORM I recommend to most TypeScript developers working on relational databases. The developer experience is excellent, the generated types match your schema exactly, and the migration workflow is reliable. After shipping dozens of production applications with Prisma, I have a clear picture of where it shines and where to be careful.

This guide covers the patterns I use in production — not the documentation examples, but the real patterns that hold up under load and through team scaling.

Schema Design Fundamentals

The Prisma schema is where your data model lives. Design it deliberately:

// 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?
  avatarUrl   String?
  role        Role      @default(VIEWER)
  posts       Post[]
  comments    Comment[]
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  deletedAt   DateTime?

  @@index([email])
  @@index([deletedAt])
}

model Post {
  id          String    @id @default(cuid())
  title       String
  slug        String    @unique
  content     String
  published   Boolean   @default(false)
  author      User      @relation(fields: [authorId], references: [id])
  authorId    String
  tags        Tag[]
  comments    Comment[]
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

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

enum Role {
  ADMIN
  EDITOR
  VIEWER
}

Key schema decisions:

Use cuid() for IDs. UUIDs work but CUID2 generates IDs that are sortable by creation time (like a timestamp prefix), which improves database locality for sequential writes.

Add @@index for every foreign key. Prisma does not create them automatically. Unindexed foreign keys cause sequential scans on JOINs.

Add soft delete with deletedAt. For most business applications, deleted records need audit trails or recovery capabilities. Filter with where: { deletedAt: null } in queries.

Use @updatedAt on mutable models. Prisma automatically sets this on every update — it is a useful timestamp for cache invalidation and audit purposes.

The Client Singleton

Always use a singleton for the Prisma client. Without it, each hot reload in development creates a new client and exhausts database connections:

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

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

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
}

Query Patterns

Select only what you need. The default findMany with include fetches all columns. For list views where you only show a few fields, use select to reduce data transfer:

// Instead of this (fetches all columns including large content field)
const posts = await prisma.post.findMany({
  include: { author: true },
})

// Use this for a posts list page
const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    slug: true,
    createdAt: true,
    author: {
      select: {
        id: true,
        name: true,
        avatarUrl: true,
      },
    },
  },
  where: { published: true, deletedAt: null },
  orderBy: { createdAt: 'desc' },
  take: 20,
})

Avoid N+1 queries. This is the most common Prisma performance mistake. Fetching posts and then fetching the author for each post in a loop:

// BAD: N+1 — 1 query for posts + 1 query per post for author
const posts = await prisma.post.findMany()
for (const post of posts) {
  const author = await prisma.user.findUnique({ where: { id: post.authorId } })
  // ...
}

// GOOD: Single query with include
const posts = await prisma.post.findMany({
  include: { author: { select: { id: true, name: true } } },
})

Enable query logging in development to catch N+1 patterns:

const prisma = new PrismaClient({ log: ['query'] })

Any route that logs 10+ queries for a single request is an N+1 candidate.

Transactions

Use transactions for operations that must succeed or fail together:

// Transfer credits between accounts
async function transferCredits(
  fromId: string,
  toId: string,
  amount: number
): Promise<void> {
  await prisma.$transaction(async (tx) => {
    const from = await tx.account.findUniqueOrThrow({
      where: { id: fromId },
    })

    if (from.credits < amount) {
      throw new Error('Insufficient credits')
    }

    await tx.account.update({
      where: { id: fromId },
      data: { credits: { decrement: amount } },
    })

    await tx.account.update({
      where: { id: toId },
      data: { credits: { increment: amount } },
    })

    await tx.transaction.create({
      data: {
        fromId,
        toId,
        amount,
        type: 'TRANSFER',
      },
    })
  })
}

If any operation inside $transaction throws, all operations roll back automatically.

For long-running transactions, configure the timeout:

await prisma.$transaction(
  async (tx) => {
    // Long operation
  },
  { timeout: 10000, maxWait: 5000 }
)

Migrations in Production

The migration workflow for production:

# Development: create and apply a migration
prisma migrate dev --name add_user_role

# Production: apply pending migrations
prisma migrate deploy

Never run prisma migrate dev in production — it may generate and apply unanticipated migrations. Always use prisma migrate deploy.

For zero-downtime migrations with large tables, be careful about blocking operations:

• Adding a NOT NULL column with no default locks the table while it sets the default
• Adding an index on a large table can cause I/O pressure that slows other queries
• Renaming a column requires updating application code simultaneously

For these cases, use multi-step migrations:

1. Add the new column as nullable (no lock)
2. Backfill existing rows (can do this in batches without locks)
3. Add the NOT NULL constraint
4. Deploy application code that uses the new column

Raw Queries for Complex Operations

When Prisma's query API is not expressive enough, drop to raw SQL:

// Complex query that Prisma cannot express efficiently
const results = await prisma.$queryRaw<UserStats[]>`
  SELECT
    u.id,
    u.name,
    COUNT(p.id) AS post_count,
    MAX(p.created_at) AS last_post_at,
    COALESCE(SUM(p.view_count), 0) AS total_views
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id AND p.published = true
  WHERE u.deleted_at IS NULL
  GROUP BY u.id, u.name
  ORDER BY total_views DESC
  LIMIT 10
`

$queryRaw uses parameterized queries by default (via template literals), preventing SQL injection. Never concatenate user input directly into raw query strings.

Middleware for Audit Logging

Prisma middleware intercepts queries and can add cross-cutting behavior:

prisma.$use(async (params, next) => {
  const before = Date.now()
  const result = await next(params)
  const after = Date.now()

  if (after - before > 100) {
    console.warn(
      `Slow Prisma query: ${params.model}.${params.action} (${after - before}ms)`
    )
  }

  return result
})

// Soft delete middleware
prisma.$use(async (params, next) => {
  if (params.action === 'delete') {
    params.action = 'update'
    params.args.data = { deletedAt: new Date() }
  }

  if (params.action === 'deleteMany') {
    params.action = 'updateMany'
    params.args.data = { deletedAt: new Date() }
  }

  return next(params)
})

Prisma's middleware system is powerful for cross-cutting concerns but adds overhead to every query. Use it judiciously.

Prisma is a mature, excellent ORM for TypeScript backend applications. The schema-first approach, the generated types, and the migration workflow are all well-designed. Use the patterns above consistently and you will avoid most of the pitfalls.

Building with Prisma and running into schema design questions, migration challenges, or performance issues? Book a call and we can work through it: calendly.com/jamesrossjr.

Keep Reading

• Drizzle ORM vs Prisma: Which Should You Use in 2026?
• Building REST APIs With TypeScript: Patterns From Production
• TypeScript for Backend Development: Patterns I Use on Every Project
• TypeScript in Nuxt: Getting the Type Safety You Actually Want