Reading Related Data

Prisma Fundamentals

Chapter 9 · Reading Related Data

Chapters 7 and 8 connected users, profiles, posts and tags. This chapter is about reading them back: loading a record together with its related records, choosing exactly which fields come back, filtering records by their relations, and avoiding the most common performance mistake in any ORM, the N+1 query problem.

include: Add Related Records

By default a query returns only the model's own fields. include adds relations on top:

const post = await prisma.post.findUnique({
  where: { slug: "nested-writes" },
  include: {
    author: true,     // the whole User record
    tags:   true,     // all its tags
  },
});
// post.title, post.author.email, post.tags[0].name ... all typed

Includes can be nested, to follow relations further:

const user = await prisma.user.findUnique({
  where: { email: "alan@example.com" },
  include: {
    profile: true,
    posts: { include: { tags: true } },   // each post with its tags
  },
});

Filtering, Sorting and Limiting Included Lists

// A user with only their 5 latest published posts
const user = await prisma.user.findUnique({
  where: { email: "alan@example.com" },
  include: {
    posts: {
      where:   { published: true },
      orderBy: { publishedAt: "desc" },
      take:    5,
    },
  },
});

Unbounded includes grow with your data

include: { posts: true } is fine with ten posts and a problem with ten thousand: every one is loaded into memory. For lists that can grow, add take, or load them separately with pagination (Chapter 6).

select: Choose Exactly What Comes Back

select returns only the fields you list, including relations. It's the tool for keeping responses small — and for keeping private fields private.

// For a post list: title, slug, and just the author's name
const list = await prisma.post.findMany({
  where: { published: true },
  select: {
    title: true,
    slug:  true,
    author: { select: { name: true } },
    tags:   { select: { name: true } },
  },
});
// { title: string; slug: string; author: { name: string | null }; tags: { name: string }[] }[]

	`include`	`select`
Model's own fields	All of them	Only the ones you list
Relations	The ones you list, added on	The ones you list
Use for	"This record, plus its related data"	"Exactly these fields," such as for an API response

One or the other at each level

You can't use include and select side by side at the same level of a query. You can mix them at different levels, though: include: { author: { select: { name: true } } } returns all the post's fields plus only the author's name.

include can leak data

include: { author: true } returns the whole user record. If your User model later gains a passwordHash or other private field, every response built this way now contains it. For data sent to browsers or other systems, select the fields you mean to share.

Filtering by Relations

You can also filter records by what their related records look like:

Filter	For	Matches records where…
`some`	Lists	At least one related record matches
`every`	Lists	All related records match
`none`	Lists	No related record matches
`is` / `isNot`	Single relations	The related record matches / doesn't match

// Users who have published at least one post
await prisma.user.findMany({ where: { posts: { some: { published: true } } } });

// Posts tagged "prisma"
await prisma.post.findMany({ where: { tags: { some: { name: "prisma" } } } });

// Users who have written nothing at all
await prisma.user.findMany({ where: { posts: { none: {} } } });

// Posts written by admins
await prisma.post.findMany({ where: { author: { is: { role: "ADMIN" } } } });

every is true for an empty list

"Users where every post is published" also returns users with no posts at all, because there's no post that breaks the rule. If you mean "users who have posts, and all of them are published," combine every with some: {}.

The N+1 Query Problem

This code works, and it's a classic mistake:

// Listing 50 posts with their authors' names -- the slow way
const posts = await prisma.post.findMany({ take: 50 });   // 1 query

for (const post of posts) {
  const author = await prisma.user.findUnique({    // +1 query per post
    where: { id: post.authorId },
  });
  console.log(post.title, "by", author?.name);
}

One query for the list, then one more for every row: N+1 queries, 51 in this case. Each is quick, but they add up, especially when the database is on another machine and every query is a network round trip. The fix is to ask for the related data as part of the list query:

const posts = await prisma.post.findMany({
  take: 50,
  include: { author: { select: { name: true } } },
});
for (const post of posts) console.log(post.title, "by", post.author.name);

Prisma now loads the authors for all 50 posts together. By default it does this with one extra query for the relation, however many posts there are, so the total stays fixed. On PostgreSQL, CockroachDB and MySQL, a preview feature (relationJoins) can load everything in a single joined query instead.

Seeing the queries

// lib/prisma.ts: print every SQL query Prisma runs
export const prisma = new PrismaClient({ adapter, log: ["query"] });

Count the queries

With query logging on, run both versions of the loop and count the lines printed. N+1 problems are easy to miss in development with five rows and very visible in production with five thousand.

Hands-On Exercises

Exercise 1

Write a query for a blog's home page: the 10 newest published posts, each with its title, slug, publication date, author's name and tag names — and nothing else. Explain why you chose select or include.

📄 View solution

Exercise 2

Write queries for: (a) tags that aren't used on any post; (b) authors who have written at least one post tagged "prisma"; (c) users who have written posts and whose posts are all published.

📄 View solution

Exercise 3

Turn on query logging, create at least 10 posts, and run this chapter's slow loop and fast version. Count the queries each makes, then explain how the counts would change with 1,000 posts.

📄 View solution

Chapter 9 Quick Reference

include adds relations to all of a model's fields; nest it to go deeper
Included lists accept where, orderBy and take — bound lists that can grow
select returns only the fields you list; prefer it for data you send elsewhere
Don't use include and select at the same level; nesting them is fine
Relation filters: some, every, none for lists; is, isNot for single relations; every matches empty lists
N+1: one query per row in a loop; fix it by loading the relation in the list query
log: ["query"] on the client shows every query Prisma runs