Prisma ORM: From Zero to Production

Most ORMs make you describe your data twice — once in the database, once in application code — and the two descriptions drift apart quietly. Prisma collapses this into one file. schema.prisma declares your models, their fields, and their relations, and everything else is derived from it: the SQL migrations that build the database, and the TypeScript client you query it with.

generator client {
  provider = "prisma-client"
  output   = "../lib/generated/prisma"
}

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

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

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

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

Two things in that file deserve a closer look. The author field on Post is virtual — it never becomes a column. The real foreign key is authorId; the @relation attribute tells Prisma how the two models connect, and the generated client turns that into a typed relation you can query through. The same pattern scales to every relation shape: a unique foreign key gives you one-to-one, a plain one gives you one-to-many, and for many-to-many Prisma manages the join table for you.

The second thing is the trio at the bottom of Post. Timestamps on every model are a habit, not a requirement — they cost nothing now and answer the question of when something broke later. The nullable deletedAt enables soft deletes: instead of destroying a row, you stamp it, filter it out of queries, and keep the option of undeleting. For anything a user would mourn — posts, orders, accounts — soft delete is the default I reach for. Hard delete is for session tokens and other data nobody will ever ask about again.

The payoff of one source of truth is mechanical. Rename published to isPublished, regenerate the client, and every query that still uses the old name fails to compile. The data bugs that used to surface as runtime 500s — a typo in a field name, a string where the database expects a date — become red squiggles before the code ever runs.

Prisma gives you two ways to get schema changes into a database, and they exist for different phases of a project. npx prisma db push diffs your schema against the database and applies whatever it takes to make them match. No files, no history. While you are sketching — the schema changing three times an hour, the data disposable — this is exactly right. Demanding a named, reviewed migration for every experiment is friction with no payoff.

The moment real data exists, the calculation flips. npx prisma migrate dev generates a SQL file for each change, and those files are the audit trail: committed to git, reviewed in pull requests, replayed in order on every environment. Production never sees migrate dev — it runs npx prisma migrate deploy, which applies pending migrations and refuses to do anything creative.

# Prototyping: mirror the schema, keep no history
npx prisma db push

# Development: generate a migration file and apply it
npx prisma migrate dev --name add_soft_delete_to_posts

# Production (CI/CD): apply pending migrations, nothing else
npx prisma migrate deploy

The classic ORM failure is the N+1 query, and Prisma makes it easy to write by accident because every individual line looks innocent. You fetch twenty posts, then loop over them to fetch each author. That is twenty-one round trips to the database. At one millisecond each against local Postgres you never notice. From a serverless function to a pooled database, each round trip is more like five to ten milliseconds — and your fast page quietly becomes a 200ms page.

// The N+1 shape: one query, then one more per row
const posts = await prisma.post.findMany({ where: { published: true } });
for (const post of posts) {
  const author = await prisma.user.findUnique({
    where: { id: post.authorId },
  });
}

// One round trip, and only the fields the page renders
const list = await prisma.post.findMany({
  where: { published: true, deletedAt: null },
  orderBy: { createdAt: "desc" },
  take: 20,
  select: {
    id: true,
    title: true,
    slug: true,
    author: { select: { name: true } },
  },
});

include solves the round trips; select solves the payload, and the payload matters more than it looks. A post's content column might be 50KB of markdown, and a list page that pulls twenty full rows is moving a megabyte to render twenty titles. The habit that holds up in production is to treat every findMany as a question — which fields does this page actually render? — and select exactly those.

Indexes are why the same query is fast in week one and slow in month six

An index is a sorted structure the database maintains so it can find rows without scanning the whole table. Without one, a query that filters on published and sorts by createdAt reads every row — fine at a thousand posts, painful at a million. The rule of thumb: index your foreign keys, and index the column combinations your real where and orderBy clauses use. That is what @@index([published, createdAt]) in the schema above is doing — it serves the exact query the blog list page makes.

When a query feels slow, do not guess. Turn on query logging in development with new PrismaClient({ log: ["query"] }) to see the actual SQL and how long it took, then ask Postgres to explain itself:

EXPLAIN ANALYZE
SELECT id, title, slug FROM "Post"
WHERE "published" = true AND "deletedAt" IS NULL
ORDER BY "createdAt" DESC
LIMIT 20;

-- "Seq Scan on Post" in the output means the database
-- is reading every row. An index is usually the fix.

Reading a full query plan is a skill of its own, but one pattern gets you started: Seq Scan on a large table under a WHERE clause means no index was used, and an index on the filtered columns almost always fixes it.

Some operations are money-shaped even when no money is involved: two or more writes that must succeed or fail together, because a half-finished state is corrupt. Transferring credits between accounts. Creating an order and decrementing stock. Deleting a user and everything they own. If the second write fails and the first one stays, you have not lost data — you have invented data, which is worse.

const transfer = await prisma.$transaction(async (tx) => {
  const from = await tx.account.update({
    where: { id: fromId },
    data: { balance: { decrement: amount } },
  });

  if (from.balance < 0) {
    throw new Error("Insufficient funds"); // rolls everything back
  }

  return tx.account.update({
    where: { id: toId },
    data: { balance: { increment: amount } },
  });
});

Throwing inside the callback rolls back every write, which makes invariant checks natural: do the write, inspect the result, throw if the numbers do not hold. Two rules keep transactions healthy in production. Keep them short — a transaction holds one of your pool's database connections for its entire life. And never call external services inside one: a Stripe request that takes three seconds means a connection pinned for three seconds, and under load that is how pools die.

On a traditional server this problem barely exists: one long-lived process, one PrismaClient, one pool of connections. Serverless breaks the assumption. Every function instance is its own process with its own client and its own pool, and a traffic spike that spins up 200 instances is suddenly asking your database for hundreds of connections. A small Postgres instance allows around 100. The error — remaining connection slots are reserved — arrives precisely when you have the most users to lose.

The fix is a connection pooler such as PgBouncer sitting in front of Postgres, multiplexing many short-lived clients onto a few real connections. Hosted providers ship this — Supabase and Neon both hand you two connection strings, a pooled one and a direct one — and Prisma's schema has a field for each:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")  // pooled — runtime queries
  directUrl = env("DIRECT_URL")    // direct — prisma migrate
}

The split exists because migrations need a direct connection — they take locks and rely on session state that a transaction-mode pooler does not preserve — while runtime traffic should go through the pooler. On serverless, also cap each instance's appetite by appending connection_limit=1 to the pooled URL: one function instance handles one request at a time, so a bigger per-instance pool buys nothing and starves the neighbours. In development the leak looks different — Next.js hot reload creating a fresh client on every file save — and the standard fix is the singleton:

import { PrismaClient } from "@/lib/generated/prisma/client";

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

export const prisma = globalForPrisma.prisma ?? new PrismaClient();

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

An honest assessment: the query API covers perhaps 95 percent of what a typical application needs, and the remaining 5 percent is real. Heavy reporting queries — window functions, recursive CTEs, ranking within groups — cannot be expressed in it, and trying usually means fetching thousands of rows into JavaScript to do work the database would do in milliseconds. The same goes for the exotic corners of Postgres: full-text search configurations, PostGIS, partial indexes, triggers. If your project is mostly that kind of work, a SQL-first tool is the better foundation, and choosing Prisma anyway would be fighting your own stack.

For everyone else, the escape hatch is built in. $queryRaw takes a tagged template in which every interpolated value becomes a bound parameter — the database receives the query and the values separately, so SQL injection is structurally impossible:

import { Prisma } from "@/lib/generated/prisma/client";

const minViews = 1000;

const topPages = await prisma.$queryRaw<
  { slug: string; views: bigint }[]
>(Prisma.sql`
  SELECT slug, COUNT(*) AS views
  FROM "PageView"
  WHERE "createdAt" > now() - interval '30 days'
  GROUP BY slug
  HAVING COUNT(*) > ${minViews}
  ORDER BY views DESC
  LIMIT 10
`);

The one rule with no exceptions: never build SQL by concatenating strings, and treat $queryRawUnsafe as the loaded gun its name suggests. The tagged template costs nothing and removes the entire class of injection bugs. Raw SQL also composes with the migration system — npx prisma migrate dev --create-only gives you an empty migration file you can write by hand, which is how you add a partial index or a trigger while keeping the migration history intact.

Before you point real users at a Prisma-backed app, walk this list. Each item is roughly ten minutes of work; each item skipped is a future incident.

• Every model has createdAt and updatedAt; anything users would mourn has deletedAt, and queries filter on it
• Migration files are committed and reviewed, CI runs migrate deploy, and db push is retired
• The hot pages have been checked for N+1 patterns, and every list query uses select and take
• Foreign keys and your real where/orderBy combinations are indexed, and you have run EXPLAIN ANALYZE on your slowest query at least once
• Multi-write invariants live inside transactions, and no transaction makes an external network call
• Runtime traffic uses the pooled connection string, migrations use the direct one, and connection_limit is set on serverless
• Raw SQL exists only behind Prisma.sql tagged templates — no string concatenation anywhere
• prisma generate runs in postinstall, so a fresh clone builds without the missing-client error

The fastest way to make any of this stick is to apply it to a project that already exists. Turn on query logging for a day, find the slowest query in the log, and fix it — with a select, an index, or a raw query, whichever the problem actually calls for. One slow query, diagnosed and fixed by you, teaches more about your data layer than another tutorial ever will, because the next time something is slow you will already know where to look.