What is the difference between include and select in Prisma?

The 'include' keyword tells Prisma to perform a JOIN and return all fields of the related model. In contrast, 'select' allows you to choose specific fields from related records, which results in smaller MySQL query payloads and better performance.

How do you link existing records in Prisma?

You can use the 'connect' operator to link an existing record to another. To remove a link, use 'disconnect', and to replace the entire set of relations, use the 'set' operator.

When should I use Implicit vs. Explicit many-to-many relations?

Use Implicit relations when you want Prisma to manage the join table automatically and you don't need extra fields. Use Explicit relations when you need full control over the join table or need to store metadata like timestamps or extra relationship details.

What is the N+1 problem in ORMs like Prisma?

The N+1 problem is a performance anti-pattern where the application executes one query for parent records and then one additional query for each of the N parents to load their children. This leads to N+1 total database queries.

How can I debug the SQL queries generated by Prisma?

You can enable query logging by initializing the Prisma Client with the log property: 'new PrismaClient({ log: ["query"] })'. This allows you to see the exact SQL generated and catch performance issues like the N+1 problem in development.

Prisma Relations & Nested Queries

by Sanjewa March 23, 2026 Express Js

RELATIONAL

Mastering Relational Data with MySQL

Real applications don’t just have flat tables. A blog has users, posts, comments, tags, and likes. An e-commerce platform has products, categories, orders, and line items. This blog is about working with all of that complexity gracefully — using Prisma’s relational querying power with MySQL.

FETCHING

Fetching Related Data with `include`

`include` tells Prisma to JOIN and return related records. The type system knows exactly what shape the result will be.

`include` returns ALL fields of the related model. If you only need specific fields from related records, use nested `select` instead for smaller MySQL query payloads.

📘

const postWithRelations = await prisma.post.findUnique({
  where: { id: 1 },
  include: {
    author: true,       // JOIN users table
    comments: true,     // JOIN comments table
    tags: true,         // JOIN through _PostToTag (M:N join table)
  },
});

// TypeScript knows: postWithRelations.author.name
// TypeScript knows: postWithRelations.comments[0].body
// TypeScript knows: postWithRelations.tags[0].name

SELECT

Fine-Grained Control

Nested `select`

Combine `select` and `include` at any nesting level. prisma.io/docs/concepts/components/prisma-client/select-fields

📘

const post = await prisma.post.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    title: true,
    content: true,
    author: {
      select: { id: true, name: true, avatar: true },
      // password NOT included — safe for client responses
    },
    comments: {
      select: {
        id: true,
        body: true,
        createdAt: true,
        author: { select: { name: true } },
      },
      orderBy: { createdAt: 'desc' },
      take: 5,           // Latest 5 comments only
      where: { approved: true },
    },
    tags: {
      select: { id: true, name: true },
    },
    _count: {
      select: { comments: true, likes: true }, // Aggregate counts
    },
  },
});

WRITES

Creating Records with Relations

Nested Writes

Prisma lets you create a parent and its children in a single atomic MySQL transaction.

Nested creates are wrapped in a MySQL transaction automatically. Either all records are created or none — no partial data. prisma.io/docs/concepts/components/prisma-client/relation-queries#nested-writes

📘

// Create a User with Posts and Profile in one call
const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'Sarah',
    posts: {
      create: [
        { title: 'First Post', published: true },
        { title: 'Draft Post' },
      ],
    },
    profile: {
      create: { bio: 'Full-stack developer, MySQL enthusiast' },
    },
  },
  include: {
    posts: true,
    profile: true,
  },
});

WRITES

Creating Records with Relations

`connect`, `disconnect`, `set` for Relations

When you already have existing records and want to link/unlink them. prisma.io/docs/concepts/components/prisma-client/relation-queries#connect-an-existing-record

`connect` — Link Existing Records

📘

// Add an existing tag to a post (INSERT into _PostToTag join table)
await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: { connect: { id: 5 } },
  },
});

// Connect multiple at once
await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: { connect: [{ id: 3 }, { id: 7 }, { id: 12 }] },
  },
});

`disconnect` — Unlink Records

📘

// Remove tag #5 from this post (DELETE from join table)
await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: { disconnect: { id: 5 } },
  },

`set` — Replace the Entire Relation Set

📘

// Replace ALL tags on this post with exactly [tag 3, tag 7]
await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: { set: [{ id: 3 }, { id: 7 }] },
  },
});

MANY to MANY

Implicit vs Explicit Many-to-Many

Implicit (Prisma manages the MySQL join table)

📘

model Post {
  id   Int   @id @default(autoincrement())
  tags Tag[]
  @@map("posts")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique @db.VarChar(100)
  posts Post[]
  @@map("tags")
}
// Prisma creates "_PostToTag" join table in MySQL

Explicit (You control the join table)

Use explicit when you need metadata on the relation:

📘

model Post {
  id          Int       @id @default(autoincrement())
  tagRelations PostTag[]
  @@map("posts")
}

model Tag {
  id           Int       @id @default(autoincrement())
  name         String    @unique @db.VarChar(100)
  postRelations PostTag[]
  @@map("tags")
}

model PostTag {
  postId    Int
  tagId     Int
  addedBy   String   @db.VarChar(191)  // Who added this tag
  createdAt DateTime @default(now())
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag       Tag      @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([postId, tagId])         // Composite primary key
  @@index([tagId])              // Index the FK in MySQL!
  @@map("post_tags")
}

When to Use Which

Feature	Implicit	Explicit
Join table	Auto-generated (_ModelAToModelB)	You define it
Extra fields on relation	Not possible	Yes (timestamps, metadata, etc.)
Schema verbosity	Minimal	More verbose
Query complexity	Simple (direct relation)	Two hops needed
Control over join table	None	Full control
Use case	Simple links	Rich relationships

FILTERS

Querying Across Relations

Relational Filters

Filter parent records based on their children’s properties. prisma.io/docs/reference/api-reference/prisma-client-reference#some

📘

// Find users who have AT LEAST ONE published post (some)
const authors = await prisma.user.findMany({
  where: {
    posts: { some: { published: true } },
  },
});

// Find users where ALL posts are published (every)
const prolificAuthors = await prisma.user.findMany({
  where: { posts: { every: { published: true } } },
});

// Find users with NO posts (none)
const lurkers = await prisma.user.findMany({
  where: { posts: { none: {} } },
});

// Filter with nested conditions
const activeAuthors = await prisma.user.findMany({
  where: {
    posts: {
      some: {
        AND: [{ published: true }, { views: { gt: 100 } }],
      },
    },
  },
});

N + 1

And How Prisma Handles It

The N+1 Problem

The N+1 problem is one of the most common ORM performance bugs. It happens when you load N parent records and then fire 1 additional query per parent to load children — N+1 total MySQL queries.


Enable query logging

 to see exactly what SQL Prisma generates: `new PrismaClient({ log: ['query'] })`. This is invaluable for catching N+1 issues in development.
prisma.io/docs/concepts/components/prisma-client/working-with-prismaclient/logging

The Problem (Anti-Pattern)

📘

// WRONG — generates N+1 MySQL queries
const posts = await prisma.post.findMany();  // 1 query
for (const post of posts) {
  const author = await prisma.user.findUnique({ // N queries!
    where: { id: post.authorId },
  });
  console.log(`${post.title} by ${author?.name}`);
}
// 100 posts = 101 MySQL queries

The Solution — Use `include`

📘

// CORRECT — 1 optimized MySQL query with JOIN
const posts = await prisma.post.findMany({
  include: { author: true },
});
// 100 posts = 1 MySQL query with JOIN

Explore project snapshots or discuss custom web solutions.

More About Me

Weeks of coding can save you hours of planning.

widely attributed in software engineering communities

Thank You for Spending Your Valuable Time

I truly appreciate you taking the time to read blog. Your valuable time means a lot to me, and I hope you found the content insightful and engaging!

FAQ's

Frequently Asked Questions

Can I use `include` and `select` together on the same level?

No — you can't use both `include` and `select` at the root query level. They're mutually exclusive at the same level. However, within an `include`, you can use `select` to specify which fields of the relation to return.

Does Prisma support self-referencing relations (categories with subcategories)?

Yes — self-referencing models work in MySQL. Define: `parent Category? @relation('sub', fields: [parentId], references: [id]); children Category[] @relation('sub')`. Add `@@index([parentId])` for the FK.

Does MySQL foreign key `ON DELETE CASCADE` work with Prisma?

Yes — use `onDelete: Cascade` in your relation definition. Prisma generates the proper MySQL `ON DELETE CASCADE` constraint in the migration SQL. Always add `@@index([foreignKeyId])` on the child model for MySQL performance.

What's the maximum nesting depth for Prisma queries?

No hard limit, but deeply nested queries can generate complex MySQL JOINs. As a practical rule, avoid going beyond 3–4 levels of nesting. For deeper requirements, consider multiple queries or a GraphQL layer with DataLoader.

Blogs

Related Blogs

prisma-relations-nested-queries-mysql-typescript-2027

Express Js

23 Mar,2026 By Sanjewa

Shopping cart