Prisma ORM Guide: Type-Safe Databases
Schema definition, migrations, type-safe queries, relation patterns, performance optimisation, and Next.js integration — from a team that ships Prisma in production weekly.
What Prisma Actually Is
Prisma is a type-safe ORM for Node.js and TypeScript. It replaces raw SQL queries with an auto-generated, fully-typed query client — every model, field, and relation is known to the TypeScript compiler at build time. The schema.prisma file defines the data model, Prisma Migrate generates SQL migrations from schema changes, and Prisma Client provides the typed query interface.
The core benefit: a database column rename updates the TypeScript types across the entire codebase instantly — and the compiler flags every call site that needs updating. This eliminates an entire class of runtime database errors that raw SQL and weakly-typed ORMs allow. 4Byte uses Prisma as the database layer in the majority of Next.js SaaS products we build.
▸ Core Components
The 3 Parts of Prisma ORM
Prisma Schema
A single schema.prisma file defines your entire data model — models, fields, types, relations, and database connection. The schema is the single source of truth for the database structure and generated types.
- Declarative data model definition
- Relations defined in the schema
- Generates TypeScript types
- Source of truth for migrations
Prisma Client
An auto-generated, type-safe query builder — every model, field, and relation is fully typed. Write database queries in TypeScript with autocomplete and compile-time error checking.
- Auto-generated from schema
- Full TypeScript autocompletion
- CRUD + advanced query methods
- Supports transactions & batch operations
Prisma Migrate
A declarative migration system — diff the schema against the database, generate SQL migration files, and apply them. All migrations are SQL files committed to version control.
- Auto-generates SQL migrations
- Migration history tracked in DB
- prisma migrate dev for local
- prisma migrate deploy for production
▸ Code Examples
Prisma in Production — 3 Real Patterns
Basic SaaS Schema — Users, Orgs, Projects
A typical B2B SaaS data model with organisations, members, and projects — including UUID primary keys, timestamps, and proper relations.
// 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?
createdAt DateTime @default(now())
members Member[]
}
model Organisation {
id String @id @default(cuid())
name String
createdAt DateTime @default(now())
members Member[]
projects Project[]
}
model Member {
id String @id @default(cuid())
role String @default("member")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
userId String
org Organisation @relation(fields: [orgId], references: [id], onDelete: Cascade)
orgId String
@@unique([userId, orgId])
}
model Project {
id String @id @default(cuid())
name String
createdAt DateTime @default(now())
org Organisation @relation(fields: [orgId], references: [id], onDelete: Cascade)
orgId String
}Always define onDelete behaviour on relations. Without it, attempting to delete a parent record that has children throws a foreign key constraint error in production.
Type-Safe Queries with Prisma Client
Reading, creating, updating, and deleting records with full TypeScript types — including eager-loaded relations in a single query.
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
// Fetch org with members and projects (single query)
const org = await prisma.organisation.findUnique({
where: { id: orgId },
include: {
members: {
include: { user: true },
},
projects: {
orderBy: { createdAt: 'desc' },
take: 10,
},
},
})
// Create a project
const project = await prisma.project.create({
data: {
name: 'New Project',
orgId: org.id,
},
})
// Update with type safety
const updated = await prisma.project.update({
where: { id: project.id },
data: { name: 'Renamed Project' },
})
// Delete
await prisma.project.delete({ where: { id: project.id } })Using include without a take limit on one-to-many relations fetches all related records — this causes slow queries as the table grows. Always paginate or limit eager-loaded collections.
Prisma in Next.js — Singleton Pattern
In Next.js development, hot reloading creates new PrismaClient instances on every module reload — exhausting the database connection pool. The singleton pattern prevents this.
// 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', 'error', 'warn']
: ['error'],
})
if (process.env.NODE_ENV !== 'production') {
globalForPrisma.prisma = prisma
}
// Usage in Server Component or API route
import { prisma } from '@/lib/prisma'
const projects = await prisma.project.findMany({
where: { orgId },
orderBy: { createdAt: 'desc' },
})Forgetting the singleton pattern in Next.js exhausts the PostgreSQL connection pool within minutes in development — you'll see "too many clients" errors. Add this file on day one.
▸ Performance
4 Prisma Performance Patterns Used in Production
Use select instead of include for partial data
include fetches the entire related record. select returns only the fields you need — reducing payload size and query time significantly on large tables.
// ❌ Fetches entire user object
include: { user: true }
// ✓ Fetches only what's needed
select: { user: { select: { name: true, email: true } } }Use findMany with cursor-based pagination
Offset pagination (skip/take) degrades at scale — the database scans all skipped rows. Cursor-based pagination using the last record's ID is faster and consistent.
// Cursor-based pagination
const projects = await prisma.project.findMany({
take: 20,
skip: cursor ? 1 : 0,
cursor: cursor ? { id: cursor } : undefined,
orderBy: { createdAt: 'desc' },
})Use transactions for multi-table writes
When multiple tables must be written together atomically — such as creating an org and its first member — use prisma.$transaction to ensure all-or-nothing behaviour.
const [org, member] = await prisma.$transaction([
prisma.organisation.create({
data: { name: 'Acme Inc' },
}),
prisma.member.create({
data: { userId, orgId: org.id, role: 'admin' },
}),
])Add @@index for frequently queried fields
Prisma's @@index attribute adds database indexes in migrations. Add indexes on any field used in WHERE, ORDER BY, or JOIN conditions — especially foreign keys.
model Project {
id String @id @default(cuid())
orgId String
name String
@@index([orgId]) // FK index
@@index([orgId, name]) // Composite index
}▸ Migrations
The Prisma Migration Workflow
Edit schema.prisma
Add a new model, field, or relation to the schema file.
prisma migrate dev
Generates a SQL migration file and applies it to the local dev database.
prisma generate
Regenerates the Prisma Client TypeScript types from the updated schema.
Commit migration file
The generated SQL migration file is committed to version control alongside the schema change.
prisma migrate deploy
CI/CD applies pending migrations to staging and production before deploying the app.
▸ Why 4Byte
Prisma Is in Every SaaS We Ship
Every pattern in this guide comes from real production codebases — not documentation examples. Prisma's type safety catches database errors before they reach users, and its migration system keeps schema changes predictable across environments.
If you're building a SaaS and want a type-safe database layer set up correctly from day one, our free strategy call is where we start.
Currently accepting new SaaS projects
Free strategy call · Response in ≤ 4 hours · No obligation
▸ FAQ
Prisma ORM — Common Questions
What is Prisma ORM?+
Prisma is a type-safe ORM (Object-Relational Mapper) for Node.js and TypeScript that simplifies database access. It consists of three tools: Prisma Schema (a declarative data model definition), Prisma Client (an auto-generated, type-safe query builder), and Prisma Migrate (a database migration system). It supports PostgreSQL, MySQL, SQLite, SQL Server, MongoDB, and CockroachDB.
Why use Prisma instead of writing raw SQL?+
Prisma provides TypeScript types for every database query — meaning the compiler catches type errors before they reach production. It also auto-generates the client from the schema, so adding a column instantly updates the types throughout the codebase. For teams working in TypeScript, this eliminates an entire class of runtime database errors.
Is Prisma good for Next.js applications?+
Yes. Prisma is one of the most popular database tools in the Next.js ecosystem. It integrates cleanly with Next.js Server Components, API routes, and Server Actions — using a singleton pattern to avoid creating too many database connections in development. It works with PostgreSQL, Supabase, Neon, AWS RDS, and any other PostgreSQL-compatible host.
How does Prisma handle database migrations?+
Prisma Migrate generates SQL migration files from schema changes automatically. When the Prisma schema is modified, running prisma migrate dev generates a migration file and applies it to the development database. Running prisma migrate deploy applies pending migrations to production. All migration files are committed to version control.
What is the N+1 problem in Prisma and how do I avoid it?+
The N+1 problem occurs when a query fetches N records and then makes N additional queries to fetch related data — one per record. In Prisma, avoid it by using include (eager loading) or select with nested relations in a single query rather than fetching relations in a loop. Prisma's fluent API makes eager loading straightforward.
Should I use Prisma or Drizzle ORM?+
Prisma is the more established choice with a larger community, better documentation, and more mature tooling — making it the safer default for most teams. Drizzle is a newer, lighter ORM that generates more performant SQL and has better edge runtime compatibility. 4Byte uses Prisma for the majority of projects due to its maturity and developer experience, but reaches for Drizzle for edge-deployed applications where bundle size matters.
▸ Continue Learning
Related Technology Stack Resources
Let's Build Your Type-Safe Database Layer Together.
Book a free 30-minute call with 4Byte. We'll review your data model and set up a production-grade Prisma schema from day one — no pressure, no obligation.
Type Safe
Full TypeScript coverage
28-Day MVP
Avg. delivery time
No Obligation
Zero pressure call