Relational Queries: Include, Select, and Connect

Prisma Client provides a type-safe API for querying and mutating related data across multiple tables. Let us explore relation queries.

1. Fetching Related Records (include)

By default, Prisma does not load related models (lazy loading) to save database bandwidth. To join and fetch related tables, use the include option:

const users = await db.user.findMany({
  // Joins the Post table and returns posts for each user
  include: {
    posts: true,
  },
});

You can nest filters inside the include block to limit the returned relation records:

const users = await db.user.findMany({
  include: {
    posts: {
      where: { published: true },
      take: 5,
    },
  },
});

2. Projecting Fields columns (select)

To return only specific columns from a model (and reduce network payload sizes), use the select option:

const userNames = await db.user.findMany({
  select: {
    id: true,
    email: true,
    // You can also select specific nested relation fields
    posts: {
      select: {
        title: true,
      },
    },
  },
});

Note: You cannot use both select and include at the root level of the same query block. To load relations while selecting fields, nest the select block inside the relation field.

3. Writing Related Data (connect and create)

When inserting or updating records, you can link them to other tables using relation queries.

A. Creating and Linking Nested Data

You can create a parent record and its children in a single transaction block:

const user = await db.user.create({
  data: {
    email: "carol@example.com",
    name: "Carol",
    posts: {
      create: [
        { title: "First nested post" },
        { title: "Second nested post" },
      ],
    },
  },
});

B. Linking to Existing Records (connect)

If the related record already exists, link it to the new record using its unique ID with the connect helper:

const post = await db.post.create({
  data: {
    title: "Post with connected author",
    // Connect to the user with ID 5
    author: {
      connect: {
        id: 5,
      },
    },
  },
});