Relations in Prisma

Relations are where an ORM either saves you a great deal of work or quietly issues four hundred queries. This lesson covers all three kinds, and the performance problem that follows from getting them wrong.

One-to-many

The commonest, and already in your schema:

model Category {
  id       String    @id @default(cuid())
  products Product[]
}

model Product {
  id         String   @id @default(cuid())
  categoryId String
  category   Category @relation(fields: [categoryId], references: [id])
}

Only one side holds the foreign key — Product.categoryId is a real column. Category.products is not a column at all; it is Prisma knowing how to find the rows.

The @relation attribute goes on the side with the field. Putting it on the wrong side is a common early error and the message says so.

Optional relations use ?:

categoryId String?
category   Category? @relation(fields: [categoryId], references: [id])

Both must be optional together, or Prisma refuses.

Many-to-many

A product in several collections, a collection holding several products.

The explicit form, which is what you want:

model Collection {
  id       String              @id @default(cuid())
  slug     String              @unique
  name     String
  products ProductCollection[]
}

model ProductCollection {
  productId    String
  collectionId String
  sortOrder    Int      @default(0)
  addedAt      DateTime @default(now())

  product    Product    @relation(fields: [productId], references: [id], onDelete: Cascade)
  collection Collection @relation(fields: [collectionId], references: [id], onDelete: Cascade)

  @@id([productId, collectionId])
  @@map("product_collections")
}

@@id([productId, collectionId]) makes the pair the primary key, so the same product cannot be added to the same collection twice.

Prisma also supports an implicit form:

model Product {
  collections Collection[]
}
model Collection {
  products Product[]
}

It creates the join table for you and hides it. Convenient until you need a column on the relationship — a sort order, who added it, when — and then you are migrating.

Use the explicit form. Join tables almost always grow an extra column, and converting later is more work than writing it now.

One-to-one

model User {
  id      String   @id @default(cuid())
  profile Profile?
}

model Profile {
  id     String @id @default(cuid())
  userId String @unique
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

@unique on the foreign key is what makes it one-to-one rather than one-to-many.

Worth asking whether you need it. A profile could be columns on User. It earns a separate table when the fields are large, rarely read, or genuinely optional.

include brings the whole related record:

const product = await this.prisma.product.findUnique({
  where: { slug },
  include: { category: true, variants: true },
});

select picks fields, and is usually the better habit:

const product = await this.prisma.product.findUnique({
  where: { slug },
  select: {
    id: true,
    name: true,
    category: { select: { name: true, slug: true } },
    variants: {
      where: { isActive: true },
      orderBy: { pricePaise: "asc" },
      select: { id: true, label: true, pricePaise: true, stock: true },
    },
  },
});

Three reasons to prefer select:

It cannot leak. Adding a column later does not change what the API returns — the controllers lesson's password-hash problem, prevented structurally.

Less data. Not fetching a long description for a list of forty products is a real saving.

The types narrow. The result type contains exactly the fields you asked for, so using one you did not select is a compile error.

You cannot use include and select at the same level — pick one.

Note the nested where and orderBy on variants: relations can be filtered and sorted inside the query.

The N+1 problem

The performance bug that ORMs make easy to write:

const products = await this.prisma.product.findMany();      // 1 query

for (const product of products) {
  const variants = await this.prisma.variant.findMany({     // 1 query each
    where: { productId: product.id },
  });
}

Forty products means forty-one queries. Each is fast; the round trips are not. A page that felt instant with five products takes two seconds with a hundred.

const products = await this.prisma.product.findMany({
  include: { variants: true },
});

One query. Prisma fetches the products and their variants together.

Turn on query logging and count. From the setup lesson:

log: ["query"]

If a list endpoint prints thirty queries, you have found an N+1. This is the single most valuable thing that log gives you.

The shape to recognise: a query inside a loop. Once you see it that way, it is obvious everywhere.

Filtering by a relation

// Products in a category, by slug
this.prisma.product.findMany({
  where: { category: { slug: "staples" } },
});

// Products with at least one variant in stock
this.prisma.product.findMany({
  where: { variants: { some: { stock: { gt: 0 }, isActive: true } } },
});

// Products with nothing in stock
this.prisma.product.findMany({
  where: { variants: { every: { stock: 0 } } },
});

// Products with no variants at all
this.prisma.product.findMany({
  where: { variants: { none: {} } },
});

some, every and none cover most questions about a collection.

every on an empty collection is true. A product with no variants matches every: { stock: 0 }, which is logically correct and rarely what you meant. Combine it with some: {} when you want products that actually have variants.

Counting

const products = await this.prisma.product.findMany({
  include: {
    _count: { select: { variants: true } },
  },
});

products[0]._count.variants;

_count avoids fetching rows you only wanted to count.

Nested create, in one statement:

await this.prisma.product.create({
  data: {
    slug: "aashirvaad-atta",
    name: "Aashirvaad Select Atta",
    category: { connect: { slug: "staples" } },
    variants: {
      create: [
        { sku: "AAS-ATTA-5", label: "5kg", unit: "KILOGRAM", quantity: 5, pricePaise: 28500 },
        { sku: "AAS-ATTA-10", label: "10kg", unit: "KILOGRAM", quantity: 10, pricePaise: 56000 },
      ],
    },
  },
  include: { variants: true },
});

connect links an existing record; create makes a new one. connectOrCreate does whichever applies.

This is a transaction. If any variant fails, the product is not created either — which is what you want, and what separate create calls would not give you.

Deletes and referential integrity

onDelete: Cascade      // delete the children too
onDelete: Restrict     // refuse if children exist (the default)
onDelete: SetNull      // null the foreign key — requires an optional relation

The choice is a business decision:

Product → Variant: Cascade. A variant has no meaning without its product.

Category → Product: Restrict. Deleting a category should not silently delete products. The error forces someone to decide.

Order → OrderItem: Cascade, though you should not be deleting orders at all.

Never cascade from something that history references. A cascading delete that removes order lines because a product was deleted destroys your sales records. This is the argument for isActive from the last lesson.

Check your work

Which side holds the foreign key: the one with @relation(fields: [...]). The other side is not a column.

Why the explicit many-to-many form: join tables usually need extra columns, and converting from the implicit form later is a migration.

What makes a relation one-to-one: @unique on the foreign key.

Why prefer select over include: it cannot leak new columns, it fetches less, and it narrows the result type.

What N+1 looks like: a query inside a loop — one for the list plus one per row. Fix it with include or select on the relation.

How to detect it: query logging, and counting the statements for one request.

Why every on an empty collection is true: vacuously — there is no member that fails. Combine with some: {} when you mean "has variants, and all of them".

Why nested create is better than separate creates: it runs in one transaction, so a partial failure leaves nothing behind.

When not to cascade: from anything historical data references. Deleting a product must not delete order lines.

Practice

Add the Collection and ProductCollection models. Migrate.
Add a product to two collections with different sort orders.
Fetch a product with its category and active variants, sorted by price, using select.
Rewrite it with include and compare the returned data volume.
Write the N+1 version deliberately. Count the queries in your log.
Fix it with include and confirm the count drops to one.
Query products with at least one variant in stock.
Query products where every variant is out of stock. Add a product with no variants and watch it appear. Fix the query.
Create a product with two variants in one nested call. Make the second SKU a duplicate and confirm nothing was created.
Set Category → Product to Cascade, delete a category, and watch products vanish. Change it to Restrict and read the error instead.

Next: querying, filtering and paginating for real.