Designing a Prisma Schema That Scales

A Prisma schema is not just a type definition. It is a migration plan, an index strategy and a deletion policy all in one file.

Explicit join tables beat implicit ones

Prisma will happily manage a many-to-many for you:

model Post {
  tags Tag[]
}
 
model Tag {
  posts Post[]
}

This works right up until you need a column on the relationship — an ordering, a timestamp, who added it. Then you are writing a migration against a table you never named. Declare it yourself:

model PostTag {
  postId String
  tagId  String
 
  post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag  Tag  @relation(fields: [tagId], references: [id], onDelete: Cascade)
 
  @@id([postId, tagId])
  @@index([tagId])
}

The composite primary key gives you uniqueness for free and an index on postId. The extra @@index([tagId]) covers the other direction — without it, "all posts with this tag" is a sequential scan.

Index what you actually query

The rule is boring and reliable: look at your real where and orderBy clauses, then index those columns together in that order.

const posts = await prisma.post.findMany({
  where: { status: "PUBLISHED" },
  orderBy: { publishedAt: "desc" },
});

@@index([status, publishedAt])

A composite index serves this query completely. Two separate single-column indexes do not — Postgres will pick one and sort the rest.

Decide what deletion means

onDelete is a product decision wearing a technical costume:

Deleting a category should not delete a year of writing, so that relation is SetNull. Deleting a user should take their sessions with them, so that one is Cascade.

Select only what you need

include is convenient and quietly expensive. On a list endpoint, name the fields:

const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    slug: true,
    excerpt: true,
    category: { select: { name: true, slug: true } },
    tags: { select: { tag: { select: { name: true, slug: true } } } },
  },
});

One query, no N+1, and no article bodies loaded to render a card that shows an excerpt.

None of this is clever. It is just deciding on purpose instead of by default.