Introduction

Prisma is the most widely adopted ORM in the Node.js ecosystem. It takes a schema-first approach: you define your data model in a schema.prisma file, and Prisma generates a fully typed client that mirrors that schema exactly. The result is a database client where TypeScript catches wrong field names, missing required arguments, and impossible query shapes at compile time — before your code ever touches a database.

This guide covers everything you need to be productive with Prisma: writing schema files, understanding generated types, querying with Prisma Client, modeling relations, running migrations, and knowing when Prisma is the right tool for your project (and when it isn't). All examples use PostgreSQL, but Prisma supports MySQL, SQLite, SQL Server, CockroachDB, and MongoDB with the same API.

Shortcut: If you have existing SQL tables, paste your CREATE TABLE statements into the SQL to Prisma Converter to generate a ready-to-use schema.prisma automatically.

How Prisma Works: Schema-First Design

Prisma's architecture has three distinct layers, and understanding all three makes the rest of the tool make sense.

The Prisma schema

The schema.prisma file is written in Prisma's own SDL (Schema Definition Language). It describes your data models, field types, relations, and database connection. This file is the single source of truth for everything Prisma does. Change the schema, run a command, and both your database migration and your generated TypeScript client update together.

Prisma Client (generated)

Running prisma generate produces a Node.js module at node_modules/@prisma/client. This is not a static library — it is generated code that reflects your exact schema. Every model, field, and relation you define appears as a typed property on the client. If you rename a column in schema.prisma, the old property name no longer exists in the generated client, so TypeScript will catch any stale references.

Prisma Migrate

Prisma Migrate compares your schema to the current database state and generates SQL migration files automatically. You never write raw ALTER TABLE or CREATE TABLE SQL by hand for routine changes. Migrations are versioned SQL files committed to your repository — they are your database's audit trail.

Defining Your Schema

Install Prisma and initialize the schema:

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

The prisma init command creates prisma/schema.prisma and a.env file for your database URL. Here is a realistic schema for a blogging platform with users, posts, and tags:

// prisma/schema.prisma

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

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

model User {
  id        Int       @id @default(autoincrement())
  uuid      String    @unique @default(uuid())
  email     String    @unique
  name      String
  role      Role      @default(USER)
  bio       String?
  isActive  Boolean   @default(true)
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  posts     Post[]
  @@map("users")
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  content     String
  published   Boolean   @default(false)
  views       Int       @default(0)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  authorId    Int
  author      User      @relation(fields: [authorId], references: [id])
  tags        Tag[]

  @@map("posts")
}

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

  @@map("tags")
}

enum Role {
  ADMIN
  USER
  MODERATOR
}

Prisma's type system maps cleanly to TypeScript. Common field types and their generated TypeScript equivalents:

Prisma Type	TypeScript Type	Notes
Int	number	Maps to 32-bit int
BigInt	bigint	JS BigInt — use with care in JSON
Float	number	64-bit IEEE float
Decimal	Decimal	Prisma Decimal object — not number
String	string	varchar, text, uuid, etc.
Boolean	boolean
DateTime	Date	JS Date object
Json	JsonValue	Prisma's JsonValue union type
Bytes	Buffer	Binary data
field?	type \| null	Optional fields become nullable
enum Role	Role (generated enum)	Type-safe enum from generator

Notice that Decimal does not map to JavaScript's number type. Prisma uses its own Decimal class (backed by decimal.js) to preserve arbitrary-precision arithmetic for monetary values. Always treat Decimal fields as objects, not primitives, and call .toNumber() or .toString()when you need a primitive.

Querying with Prisma Client

Instantiate the client once and reuse it across your application. In Next.js, use a module-level singleton to avoid creating too many connections during hot reloads:

// 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;
}

Read operations

import { prisma } from '@/lib/prisma';

// Find all active users
const users = await prisma.user.findMany({
  where: { isActive: true },
  orderBy: { createdAt: 'desc' },
  take: 20,
});

// Find one user by unique field — throws if not found
const alice = await prisma.user.findUniqueOrThrow({
  where: { email: '[email protected]' },
});

// Select specific columns + aggregate
const summary = await prisma.post.findMany({
  where: { published: true },
  select: {
    id: true,
    title: true,
    views: true,
    author: { select: { name: true, email: true } },
  },
  orderBy: { views: 'desc' },
  take: 10,
});

// Count
const publishedCount = await prisma.post.count({
  where: { published: true },
});

Write operations

// Create
const newUser = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'Bob',
    role: 'USER',
  },
});

// Update
const updated = await prisma.user.update({
  where: { email: '[email protected]' },
  data: { bio: 'Full-stack developer', updatedAt: new Date() },
});

// Upsert (create or update)
const upserted = await prisma.user.upsert({
  where: { email: '[email protected]' },
  create: { email: '[email protected]', name: 'Carol' },
  update: { name: 'Carol Updated' },
});

// Delete
await prisma.user.delete({ where: { id: 42 } });

// Delete many
await prisma.post.deleteMany({
  where: { published: false, createdAt: { lt: new Date('2024-01-01') } },
});

Filtering with operators

// Multiple conditions (AND by default)
const posts = await prisma.post.findMany({
  where: {
    published: true,
    views: { gte: 100 },           // views >= 100
    title: { contains: 'guide', mode: 'insensitive' },
    createdAt: { gte: new Date('2025-01-01') },
  },
});

// OR condition
const adminOrMod = await prisma.user.findMany({
  where: {
    OR: [{ role: 'ADMIN' }, { role: 'MODERATOR' }],
  },
});

// NOT
const nonAdmins = await prisma.user.findMany({
  where: { NOT: { role: 'ADMIN' } },
});

Querying Relations

Prisma does not fetch related records by default — this prevents N+1 queries and makes data fetching explicit. Use include to eagerly load relations, or selectto choose exactly which fields come back from each relation.

// Include relations — returns User & { posts: Post[] }
const userWithPosts = await prisma.user.findUniqueOrThrow({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
  },
});

// Nested include — posts with their tags
const users = await prisma.user.findMany({
  include: {
    posts: {
      include: { tags: true },
    },
  },
});

// Nested write — create a post with tags in one query
const post = await prisma.post.create({
  data: {
    title: 'Prisma Guide',
    content: 'Full content here...',
    author: { connect: { id: 1 } },        // connect existing user
    tags: {
      connectOrCreate: [
        { where: { name: 'TypeScript' }, create: { name: 'TypeScript' } },
        { where: { name: 'Databases' }, create: { name: 'Databases' } },
      ],
    },
  },
  include: { tags: true },
});

The connect operator links to an existing record by its unique identifier.connectOrCreate links if found, creates if not — useful for tag-style relationships where you want idempotent inserts. create creates a new nested record in the same transaction.

Many-to-many relations

In the schema above, Post and Tag have an implicit many-to-many relation (no @relation annotation needed). Prisma creates a hidden join table automatically. If you need extra fields on the join table (e.g., a sort order or timestamp), switch to an explicit join model:

// Explicit join table with extra fields
model PostTag {
  postId    Int
  tagId     Int
  addedAt   DateTime @default(now())
  addedBy   Int

  post Post @relation(fields: [postId], references: [id])
  tag  Tag  @relation(fields: [tagId],  references: [id])

  @@id([postId, tagId])
}

Managing Migrations

Prisma Migrate is a two-stage workflow: generate a migration during development,apply it in production.

# Development: generate + apply a new migration
npx prisma migrate dev --name add_user_bio

# This command:
# 1. Diffs schema against the database
# 2. Writes a new .sql file in prisma/migrations/
# 3. Applies it to the dev database
# 4. Re-generates Prisma Client

# Production: apply pending migrations (no generation)
npx prisma migrate deploy

# Reset dev database (drops + recreates + reseeds)
npx prisma migrate reset

# Inspect current database state (generates schema from live DB)
npx prisma db pull

# Push schema without creating migration files (prototyping)
npx prisma db push

A key distinction: migrate dev is for development only — it may reset data.migrate deploy is safe for production; it only applies migrations that have not yet been applied, tracked in the _prisma_migrations table.

The migration files are plain SQL. You can (and should) review them before committing. If Prisma generates a destructive migration (dropping a column), you can edit the SQL to add a RENAME or data backfill before applying. Prisma never prevents you from reading or modifying the generated SQL.

Advanced Patterns

Transactions

// Sequential transaction — each operation waits for the previous
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: '[email protected]', name: 'Dan' } }),
  prisma.post.create({ data: { title: 'First Post', content: '...', authorId: 1 } }),
]);

// Interactive transaction — full rollback on throw
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: '[email protected]', name: 'Eve' },
  });

  const post = await tx.post.create({
    data: { title: 'Eve's post', content: '...', authorId: user.id },
  });

  if (post.title.length > 200) {
    throw new Error('title too long'); // rolls back both operations
  }

  return { user, post };
});

Raw SQL when needed

// Tagged template literal — automatically parameterized (safe)
const userId = 1;
const posts = await prisma.$queryRaw<Post[]>`
  SELECT p.*, u.name AS author_name
  FROM posts p
  JOIN users u ON u.id = p.author_id
  WHERE p.author_id = ${userId}
  ORDER BY p.views DESC
  LIMIT 10
`;

// Execute (no return value — for DDL or bulk operations)
await prisma.$executeRaw`
  UPDATE posts SET views = views + 1 WHERE id = ${postId}
`;

Middleware (soft delete pattern)

// Intercept all delete operations and convert to soft deletes
prisma.$use(async (params, next) => {
  if (params.model === 'Post' && params.action === 'delete') {
    params.action = 'update';
    params.args.data = { deletedAt: new Date() };
  }

  if (params.model === 'Post' && params.action === 'findMany') {
    params.args.where = {
      ...params.args.where,
      deletedAt: null,    // automatically exclude soft-deleted rows
    };
  }

  return next(params);
});

Type utilities: Prisma namespace

import { Prisma } from '@prisma/client';

// Infer the type of a query with include/select
type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true }
}>;
// { id: number; email: string; ...; posts: Post[] }

// Type for create/update data
type CreatePostInput = Prisma.PostCreateInput;

// Type for where clauses
type UserWhereInput = Prisma.UserWhereInput;

// Catch Prisma-specific errors
try {
  await prisma.user.create({ data: { email: '[email protected]', name: 'X' } });
} catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError) {
    if (e.code === 'P2002') {
      console.log('Unique constraint violated on:', e.meta?.target);
    }
  }
}

Prisma vs Drizzle vs TypeORM: When to Choose Each

All three are production-grade tools used by teams shipping real software. The right choice depends on your specific constraints.

Choose Prisma when:

You prefer schema-first design — define models in one place, generate everything else
Your team wants a guided migration workflow with auto-generated SQL and rollback history
You need prisma studio — a visual data browser for your development database
You want the largest ecosystem: Prisma Accelerate (connection pooling), Pulse (real-time), and a large library of community examples
Your deployment target is a standard Node.js server (not an edge runtime where bundle size matters)
You want complex relation queries with a single readable include clause rather than writing joins

Choose Drizzle when:

You are deploying to edge runtimes (Cloudflare Workers, Vercel Edge) — Drizzle's bundle is ~35 KB vs Prisma's ~3–5 MB query engine
You want SQL-close syntax and full control over generated queries
Your schema is defined in TypeScript (not a separate SDL) and you prefer $inferSelect over generated types

Choose TypeORM when:

You are migrating a Java/Spring background and want active-record or data-mapper patterns familiar from Hibernate
Your project already uses TypeORM and the migration cost exceeds the benefit of switching
You need decorator-based entity definitions that colocate schema and TypeScript classes

Factor	Prisma	Drizzle	TypeORM
Schema language	Prisma SDL (.prisma file)	TypeScript builders	TypeScript decorators
Type generation	Auto-generated (prisma generate)	$inferSelect / $inferInsert	Decorator-inferred
Bundle size	~3–5 MB (query engine)	~35 KB	~500 KB
Edge runtime	No (needs native binary)	Yes	No
Migration tooling	Prisma Migrate (managed)	Drizzle Kit	TypeORM CLI
Visual DB browser	Prisma Studio (built-in)	None	None
Raw SQL escape hatch	$queryRaw / $executeRaw	sql`` tag	query() / createQueryBuilder()
Community/adoption	Largest	Growing fast	Established, declining

Using Prisma in a Next.js App Router Project

The App Router runs on the Node.js runtime by default, which is fully compatible with Prisma. The key practice is to import the singleton client rather than creating a newPrismaClient in each file — Next.js hot-reload would otherwise exhaust your connection pool during development.

// app/api/posts/route.ts
import { prisma } from '@/lib/prisma';
import { NextResponse } from 'next/server';

export async function GET() {
  const posts = await prisma.post.findMany({
    where: { published: true },
    include: { author: { select: { name: true } }, tags: true },
    orderBy: { createdAt: 'desc' },
    take: 20,
  });
  return NextResponse.json(posts);
}

export async function POST(request: Request) {
  const body = await request.json();

  const post = await prisma.post.create({
    data: {
      title: body.title,
      content: body.content,
      authorId: body.authorId,
    },
  });

  return NextResponse.json(post, { status: 201 });
}

For Server Components that fetch data directly (without an API route), import and callprisma directly inside the component function. Because Server Components run on the server, Prisma can access your database directly — no API call required. Mark the component async and await the Prisma query.

Independent Developer · Last updated June 2026

Back to Tutorials Compare: Drizzle ORM Guide

Prisma ORM Guide: Type-Safe Database Access for Node.js and TypeScript