Prisma ORM - Type-safe database access with auto-generated client and migrations

Recipe

npm install prisma @prisma/client
npx prisma init

// 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())
}

npx prisma migrate dev --name init
npx prisma generate

// lib/prisma.ts
import { PrismaClient } from "@prisma/client";
 
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
 
export const prisma = globalForPrisma.prisma ?? new PrismaClient();
 
if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma;

When to reach for this: You need type-safe database queries in a Next.js app with auto-generated types, relation handling, and schema migrations.

Working Example

// app/posts/page.tsx (Server Component)
import { prisma } from "@/lib/prisma";
 
export default async function PostsPage() {
  const posts = await prisma.post.findMany({
    where: { published: true },
    include: { author: { select: { name: true, email: true } } },
    orderBy: { createdAt: "desc" },
    take: 20,
  });
 
  return (
    <div className="max-w-3xl mx-auto p-6">
      <h1 className="text-2xl font-bold mb-6">Published Posts</h1>
      {posts.map((post) => (
        <article key={post.id} className="border-b py-4">
          <h2 className="text-xl font-semibold">{post.title}</h2>
          <p className="text-gray-600 text-sm">
            by {post.author.name ?? "Anonymous"}
          </p>
          {post.content && <p className="mt-2">{post.content}</p>}
        </article>
      ))}
    </div>
  );
}

// app/posts/actions.ts
"use server";
import { prisma } from "@/lib/prisma";
import { revalidatePath } from "next/cache";
 
export async function createPost(formData: FormData) {
  const title = formData.get("title") as string;
  const content = formData.get("content") as string;
  const authorId = Number(formData.get("authorId"));
 
  await prisma.post.create({
    data: { title, content, authorId, published: false },
  });
 
  revalidatePath("/posts");
}
 
export async function publishPost(postId: number) {
  await prisma.post.update({
    where: { id: postId },
    data: { published: true },
  });
 
  revalidatePath("/posts");
}
 
export async function deletePost(postId: number) {
  await prisma.post.delete({ where: { id: postId } });
  revalidatePath("/posts");
}

// app/posts/new/page.tsx
"use client";
import { createPost } from "../actions";
 
export default function NewPostPage() {
  return (
    <form action={createPost} className="max-w-md mx-auto p-6 space-y-4">
      <input type="hidden" name="authorId" value="1" />
      <div>
        <label className="block text-sm font-medium">Title</label>
        <input
          name="title"
          required
          className="w-full border rounded px-3 py-2"
        />
      </div>
      <div>
        <label className="block text-sm font-medium">Content</label>
        <textarea
          name="content"
          rows={5}
          className="w-full border rounded px-3 py-2"
        />
      </div>
      <button
        type="submit"
        className="bg-blue-600 text-white px-4 py-2 rounded"
      >
        Create Post
      </button>
    </form>
  );
}

What this demonstrates:

Direct Prisma queries in Server Components (no API layer needed)
Server Actions for mutations with revalidatePath
Relation includes with field selection
Singleton pattern to prevent connection exhaustion in development
Type-safe queries with auto-completed fields

Deep Dive

How It Works

Prisma generates a TypeScript client from your schema.prisma file, providing auto-completed model fields, relation traversal, and query filters
The generated client lives in node_modules/.prisma/client and is regenerated on prisma generate or prisma migrate dev
Queries return plain JavaScript objects (not class instances), making them serializable and compatible with Server Components
Relations use foreign keys defined in the schema; include and select control which related data is fetched
Migrations are SQL files stored in prisma/migrations/, tracked by a _prisma_migrations table in your database
The singleton pattern (globalForPrisma) prevents Next.js hot reload from creating new PrismaClient instances, which would exhaust database connections

Variations

Filtering and pagination:

const results = await prisma.post.findMany({
  where: {
    AND: [
      { published: true },
      { title: { contains: searchQuery, mode: "insensitive" } },
    ],
  },
  skip: (page - 1) * pageSize,
  take: pageSize,
  orderBy: { createdAt: "desc" },
});
 
const total = await prisma.post.count({
  where: { published: true },
});

Transactions:

const [post, user] = await prisma.$transaction([
  prisma.post.create({ data: { title: "Hello", authorId: 1 } }),
  prisma.user.update({
    where: { id: 1 },
    data: { name: "Updated Name" },
  }),
]);
 
// Interactive transaction
await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id: 1 } });
  if (!user) throw new Error("User not found");
  await tx.post.create({ data: { title: "Hello", authorId: user.id } });
});

Upsert (create or update):

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

Raw SQL for complex queries:

const results = await prisma.$queryRaw<
  { id: number; title: string }[]
>`SELECT id, title FROM "Post" WHERE "published" = true LIMIT ${limit}`;

TypeScript Notes

Prisma generates types for every model: User, Post, etc.
Query result types are inferred based on include and select — no manual type definitions needed
Use Prisma.PostCreateInput for create data types, Prisma.PostWhereInput for filters
Prisma.PostGetPayload<{ include: { author: true } }> gives you the exact return type for a query with includes

import { Prisma } from "@prisma/client";
 
type PostWithAuthor = Prisma.PostGetPayload<{
  include: { author: true };
}>;
 
function renderPost(post: PostWithAuthor) {
  return `${post.title} by ${post.author.name}`;
}

Gotchas

Connection exhaustion in dev — Next.js hot reload creates new PrismaClient instances. Fix: Use the singleton pattern shown in the Recipe section. Store the client on globalThis.
Stale generated client — After changing schema.prisma, queries may not reflect new fields. Fix: Run npx prisma generate after schema changes. prisma migrate dev does this automatically.
N+1 queries — Accessing relations in a loop without include triggers a query per iteration. Fix: Use include or select to eagerly load relations in the initial query.
BigInt serialization — BigInt fields cannot be serialized to JSON for client components. Fix: Convert BigInt to Number or String before passing to client components: Number(post.viewCount).
Enum changes require migration — Adding values to a Prisma enum requires a migration, not just prisma generate. Fix: Run npx prisma migrate dev --name add-enum-value.
DateTime timezone confusion — Prisma stores DateTime as UTC. Fix: Always handle timezone conversion on the client side, not in queries.

Alternatives

Library	Best For	Trade-off
Prisma	Type-safe ORM with migrations	Heavier runtime, generated client
Drizzle ORM	Lightweight, SQL-like syntax	Less abstraction, manual migrations
Kysely	Type-safe query builder	No schema management or migrations
TypeORM	Decorator-based ORM	Less type safety, heavier
Knex.js	Raw SQL query builder	No type generation, manual types

FAQs

Why do I need the singleton pattern for PrismaClient in Next.js development?

Next.js hot module replacement creates a new module scope on every code change
Each new scope would instantiate a new PrismaClient, opening fresh database connections
Databases have a connection limit (e.g., PostgreSQL defaults to 100)
Storing the client on globalThis ensures only one instance persists across hot reloads
In production this is not an issue because the server starts once

What is the difference between include and select in Prisma queries?

include fetches all scalar fields on the parent model plus the specified relations
select fetches only the fields you explicitly list, including relations
You cannot use both include and select at the same top level
Use select when you want to minimize the data returned

How do I run Prisma queries inside a Server Component?

import { prisma } from "@/lib/prisma";
 
export default async function Page() {
  const users = await prisma.user.findMany();
  return <ul>{users.map(u => <li key={u.id}>{u.name}</li>)}</ul>;
}

No API layer is needed -- Server Components run on the server and can query the database directly.

When should I use prisma.$transaction and what are the two forms?

Use transactions when multiple operations must succeed or fail together
Sequential array form: prisma.$transaction([query1, query2]) runs queries in order
Interactive form: prisma.$transaction(async (tx) => { ... }) lets you use intermediate results
Interactive transactions hold a database connection for the duration, so keep them short

Gotcha: Why do my queries not reflect new schema fields after I edit schema.prisma?

The Prisma Client is generated code living in node_modules/.prisma/client
Editing schema.prisma alone does not regenerate the client
Run npx prisma generate after every schema change
npx prisma migrate dev runs generate automatically

How do I handle pagination with Prisma?

const page = 2;
const pageSize = 10;
 
const [posts, total] = await Promise.all([
  prisma.post.findMany({
    skip: (page - 1) * pageSize,
    take: pageSize,
    orderBy: { createdAt: "desc" },
  }),
  prisma.post.count(),
]);

Gotcha: Why does passing a BigInt field to a client component throw an error?

BigInt values cannot be serialized to JSON
Server Components pass props to client components via JSON serialization
Convert BigInt to Number or String before passing: Number(post.viewCount)
If the value exceeds Number.MAX_SAFE_INTEGER, use String() instead

How do I type a Prisma query result that includes relations in TypeScript?

import { Prisma } from "@prisma/client";
 
type PostWithAuthor = Prisma.PostGetPayload<{
  include: { author: true };
}>;

Prisma.PostGetPayload infers the exact shape based on include/select
This stays in sync with your schema automatically

How do I use Prisma.PostCreateInput and similar generated types?

import { Prisma } from "@prisma/client";
 
const data: Prisma.PostCreateInput = {
  title: "Hello",
  author: { connect: { id: 1 } },
};

Prisma generates *CreateInput, *UpdateInput, *WhereInput, and *OrderByInput for every model
These types enforce required fields and valid relation operations at compile time

What is the difference between prisma migrate dev and prisma db push?

migrate dev creates a SQL migration file, applies it, and regenerates the client
db push applies schema changes directly without creating migration files
Use migrate dev for production workflows where you need migration history
Use db push for rapid prototyping or when you do not need a migration trail

How do I write a raw SQL query with Prisma while keeping type safety?

const results = await prisma.$queryRaw<
  { id: number; title: string }[]
>`SELECT id, title FROM "Post" WHERE published = true`;

Use a tagged template literal to prevent SQL injection
Provide a generic type parameter for the result shape
Prisma does not validate the generic against the actual query at compile time

How do I avoid N+1 queries when rendering a list of posts with authors?

Accessing post.author in a loop without include triggers one query per post
Use include: { author: true } in the initial findMany to eagerly load relations
Alternatively, use select to fetch only the specific author fields you need

Next.js Server Actions — Mutations with Prisma
Next.js Server Components — Fetching data with Prisma
TanStack Query — Client-side caching for Prisma-backed APIs
NextAuth.js — Database adapters work with Prisma