Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 36 ยท Relations

include, select and Nested Writes in Prisma

Load related data with include and nested select, filter and sort relations, count relations, and create or update several related records in one call with nested writes.

Relations pay off when you can read and write related data in one call. Prisma offers include and select for reading across relations and nested writes (create, connect, connectOrCreate, update, delete) for writing. This lesson builds on the User and Post models from the one-to-many lesson:

PRISMA
model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  name  String
  posts Post[]
}

model Post {
  id        Int       @id @default(autoincrement())
  title     String
  published Boolean   @default(false)
  createdAt DateTime  @default(now())
  author    User      @relation(fields: [authorId], references: [id])
  authorId  Int
  comments  Comment[]
}

model Comment {
  id     Int    @id @default(autoincrement())
  text   String
  post   Post   @relation(fields: [postId], references: [id])
  postId Int
}

include: add relations to the result

include returns all scalar fields of the model plus the relations you name:

TypeScript
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: true },
});
// user.posts is Post[]

You can nest includes to go deeper, and shape each relation with its own where, orderBy and take:

TypeScript
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: "desc" },
      take: 5,
      include: { comments: true },
    },
  },
});

Each level is typed: user.posts[0].comments[0].text is checked by TypeScript.

select: choose fields, including relations

select picks exactly the fields you want, at every level:

TypeScript
const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    name: true,
    posts: {
      select: { id: true, title: true },
      where: { published: true },
    },
  },
});
// { name: 'Ada', posts: [{ id: 1, title: 'Engines' }] }

You cannot use include and select on the same level. Rule of thumb: use include when you want "everything, plus the relation", and select when you want a lean result (an API response, a list page).

TypeScript
const users = await prisma.user.findMany({
  select: {
    name: true,
    _count: { select: { posts: true } },
  },
});
// [{ name: 'Ada', _count: { posts: 2 } }, ...]

_count runs a count in the database, so you do not load every post just to display "2 posts". You can filter inside _count too: _count: { select: { posts: { where: { published: true } } } }. You can also sort by relation count: orderBy: { posts: { _count: "desc" } }.

Query strategy

By default Prisma loads relations with extra queries and stitches them together. A relationLoadStrategy option on include/select lets you request a single database-level join ("join") instead (supported on PostgreSQL and MySQL). Either way, you describe the shape you want and Prisma decides the SQL; chapter 7 covers the N+1 problem and how this fits.

Stand-in: include and select

The stand-in below stores users, posts and comments in arrays and implements include/select (with where, orderBy, take and _count) so you can try shapes and compare results.

Node.js Stand-in: include, select, _count

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Nested writes

A nested write changes related records in the same call, and Prisma wraps it in a transaction, so either everything is saved or nothing is.

Create with relations

TypeScript
// A user and two posts, in one call
await prisma.user.create({
  data: {
    email: "[email protected]",
    name: "Ada",
    posts: {
      create: [
        { title: "First post" },
        { title: "Second post", published: true },
      ],
    },
  },
  include: { posts: true },
});

// A post linked to an existing user
await prisma.post.create({
  data: { title: "Linked", author: { connect: { email: "[email protected]" } } },
});

// Link if present, otherwise create
await prisma.post.create({
  data: {
    title: "Maybe new author",
    author: {
      connectOrCreate: {
        where: { email: "[email protected]" },
        create: { email: "[email protected]", name: "Bo" },
      },
    },
  },
});

Update with relations

TypeScript
await prisma.user.update({
  where: { id: 1 },
  data: {
    posts: {
      create: { title: "Another" },                              // add a new post
      update: { where: { id: 3 }, data: { published: true } },   // change one existing post
      updateMany: { where: { published: false }, data: { title: "Draft" } },
      delete: { id: 4 },                                         // delete one post
      deleteMany: { createdAt: { lt: new Date("2025-01-01") } }, // delete matching posts
      upsert: {
        where: { id: 9 },
        update: { title: "Updated" },
        create: { title: "Created" },
      },
    },
  },
});

Keep nested writes readable: two levels deep is fine, five levels is a sign to split the work into separate steps (inside a transaction, chapter 6).

Stand-in: all or nothing

Because a nested write runs in a transaction, a failure in the second post must also undo the first. This stand-in uses real SQLite to show what that guarantee means.

Node.js A nested create is one transaction

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The second call leaves no trace of Bo, because the whole nested write was undone.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the difference between include and select?

    include = all scalars plus the named relations. select = exactly what you list. You cannot use both at one level.
  2. 2. How do you get the number of posts per user without loading the posts?

    _count asks the database to count related rows, so the rows are not transferred.
  3. 3. Which nested write links a new post to an existing user?

    connect links to an existing record; create makes a new one.
  4. 4. What happens if the second nested create fails in prisma.user.create({ data: { posts: { create: [a, b] } } })?

    Nested writes are atomic: all changes are saved or none.
  5. 5. What does connectOrCreate do?

    connectOrCreate avoids duplicates: it connects when found and creates otherwise.

Summary

  • include = all scalars plus relations; select = only the fields you list; never both at one level.
  • Shape relations with where, orderBy, take and count them cheaply with _count.
  • Nested writes (create, connect, connectOrCreate, update, delete, upsert, set, disconnect) change related records in one atomic call.
  • Keep nesting shallow, and select only what you need.

Next: Self-Relations and Referential Actions