LLM Best Practices

Prisma Best Practices

3 min read

View markdown source

Overview

Prisma 5 is a TypeScript-first ORM that generates a typed client from a single schema.prisma file. It is the default ORM for Node and Bun services backed by postgres or sqlite. This page covers schema modeling, the migration workflow, transactions, connection pooling, and when to step outside the query builder.

Model the schema explicitly

schema.prisma is the source of truth. Every table, column, index, and relation lives there.

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  createdAt DateTime @default(now())
  orders    Order[]
 
  @@index([createdAt])
}
 
model Order {
  id     String @id @default(cuid())
  userId String
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  total  Int
 
  @@index([userId, id])
}
  • Use @id on every model. Prefer cuid() or uuid() over auto-incrementing ints for distributed systems.
  • Use @unique for natural keys you query by (email, slug). Add @@unique([a, b]) for composite uniqueness.
  • Declare relations on both sides. onDelete is explicit and matches the postgres FOREIGN KEY clause.
  • Add @@index for every query that filters or sorts on a non-key column.

Use migrate dev locally and migrate deploy in production

The two commands serve different masters.

  • prisma migrate dev creates a new SQL migration from your schema diff, runs it locally, and regenerates the client. Use during development.
  • prisma migrate deploy applies pending migrations in order without prompting. Use in CI and production.
  • Never edit a migration that has shipped. Write a follow-up migration. See postgres for the migration contract.
  • Commit the generated SQL file along with the schema change. Reviewers read the SQL, not the diff of schema.prisma alone.

Wrap multi-step writes in $transaction

Atomicity is opt-in. Group writes that must succeed or fail together.

await prisma.$transaction([
  prisma.order.create({ data: { userId, total } }),
  prisma.inventory.update({
    where: { sku },
    data: { stock: { decrement: 1 } },
  }),
]);

Use the interactive form for read-modify-write logic:

await prisma.$transaction(async (tx) => {
  const item = await tx.inventory.findUnique({ where: { sku } });
  if (!item || item.stock < 1) throw new Error("out of stock");
  await tx.inventory.update({
    where: { sku },
    data: { stock: { decrement: 1 } },
  });
  await tx.order.create({ data: { userId, total, sku } });
});

Set isolationLevel: "Serializable" when concurrent writers can race on the same rows.

Pool with PgBouncer or Prisma Accelerate

A single Prisma client opens one connection per concurrent query. Serverless and edge workloads multiply that fast.

  • Long-running Node servers: PgBouncer in transaction mode in front of Postgres. Append ?pgbouncer=true&connection_limit=1 to DATABASE_URL.
  • Serverless or edge: Prisma Accelerate or a dedicated pooler (Supabase, Neon). Direct connections from Lambda or Workers will exhaust Postgres under load.
  • See postgres for the PgBouncer sizing rules.

Trust the generated client types

The generated @prisma/client is the API contract.

  • Run prisma generate after every schema change. Wire it into postinstall so CI never ships a stale client.
  • Use Prisma.UserGetPayload<{ include: { orders: true } }> to derive return types instead of writing them by hand.
  • Keep schema and client in sync across services that share a database. Drift produces silent runtime errors.

Drop to $queryRaw when the query builder is in the way

The query builder is excellent for CRUD. It is poor at recursive CTEs, window functions, JSONB path queries, full-text search, and any plan you need to hand-tune.

const rows = await prisma.$queryRaw<Array<{ id: string; rank: number }>>`
  SELECT id, ts_rank(search_tsv, plainto_tsquery(${q})) AS rank
  FROM articles
  WHERE search_tsv @@ plainto_tsquery(${q})
  ORDER BY rank DESC
  LIMIT 20;
`;

Use the tagged template form. It parameterizes values and prevents injection. Reach for $executeRaw for writes that the builder cannot express.

Graph View

Backlinks