Relation queries

A key feature of Prisma Client is the ability to query relations between two or more models. Relation queries include:

Nested reads (sometimes referred to as eager loading) via select and include
Nested writes with transactional guarantees
Filtering on related records

Prisma Client also has a fluent API for traversing relations.

Nested reads

Nested reads allow you to read related data from multiple tables in your database - such as a user and that user's posts. You can:

Use include to include related records, such as a user's posts or profile, in the query response.
Use a nested select to include specific fields from a related record. You can also nest select inside an include.

Relation load strategies (Preview)

Since version 5.8.0, you can decide on a per-query-level how you want Prisma Client to execute a relation query (i.e. what load strategy should be applied) via the relationLoadStrategy option for PostgreSQL databases.

Since version 5.10.0, this feature is also available for MySQL.

Because the relationLoadStrategy option is currently in Preview, you need to enable it via the relationJoins preview feature flag in your Prisma schema file:

schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins"]
}

After adding this flag, you need to run prisma generate again to re-generate Prisma Client. This feature is currently available on PostgreSQL, CockroachDB and MySQL.

Prisma Client supports two load strategies for relations:

join (default): Uses a database-level LATERAL JOIN (PostgreSQL) or correlated subqueries (MySQL) and fetches all data with a single query to the database.
query: Sends multiple queries to the database (one per table) and joins them on the application level.

Another important difference between these two options is that the join strategy uses JSON aggregation on the database level. That means that it creates the JSON structures returned by Prisma Client already in the database which saves computation resources on the application level.

Note: Once relationLoadStrategy moves from Preview into General Availability, join will universally become the default for all relation queries.

Examples

You can use the relationLoadStrategy option on the top-level in any query that supports include or select.

Here is an example with include:

const users = await prisma.user.findMany({
  relationLoadStrategy: 'join', // or 'query'
  include: {
    posts: true,
  },
})

And here is another example with select:

const users = await prisma.user.findMany({
  relationLoadStrategy: 'join', // or 'query'
  select: {
    posts: true,
  },
})

When to use which load strategy?

The join strategy (default) will be more effective in most scenarios. On PostgreSQL, it uses a combination of LATERAL JOINs and JSON aggregation to reduce redundancy in result sets and delegate the work of transforming the query results into the expected JSON structures on the database server. On MySQL, it uses correlated subqueries to fetch the results with a single query.
There may be edge cases where query could be more performant depending on the characteristics of the dataset and query. We recommend that you profile your database queries to identify these situations.
Use query if you want to save resources on the database server and do heavy-lifting of merging and transforming data in the application server which might be easier to scale.

Include a relation

The following example returns a single user and that user's posts:

const user = await prisma.user.findFirst({
  include: {
    posts: true,
  },
})

Show query results

Include all fields for a specific relation

The following example returns a post and its author:

const post = await prisma.post.findFirst({
  include: {
    author: true,
  },
})

Show query results

Include deeply nested relations

You can nest include options to include relations of relations. The following example returns a user's posts, and each post's categories:

const user = await prisma.user.findFirst({
  include: {
    posts: {
      include: {
        categories: true,
      },
    },
  },
})

Show query results

Select specific relation fields

You can use a nested select to choose a subset of relation fields to return. For example, the following query returns the user's name and the title of each related post:

const user = await prisma.user.findFirst({
  select: {
    name: true,
    posts: {
      select: {
        title: true,
      },
    },
  },
})

Show query results

You can also nest a select inside an include - the following example returns all User fields and the title field of each post:

const user = await prisma.user.findFirst({
  include: {
    posts: {
      select: {
        title: true,
      },
    },
  },
})

Show query results

Note that you cannot use select and include on the same level. This means that if you choose to include a user's post and select each post's title, you cannot select only the users' email:

// The following query returns an exception
const user = await prisma.user.findFirst({
  select: { // This won't work!
    email:  true
  }
  include: { // This won't work!
    posts: {
      select: {
        title: true
      }
    }
  },
})

Show CLI results

Instead, use nested select options:

const user = await prisma.user.findFirst({
  select: {
    // This will work!
    email: true,
    posts: {
      select: {
        title: true,
      },
    },
  },
})

Relation count

In 3.0.1 and later, you can include or select a count of relations alongside fields - for example, a user's post count.

const relationCount = await prisma.user.findMany({
  include: {
    _count: {
      select: { posts: true },
    },
  },
})

Show query results

Filter a list of relations

When you use select or include to return a subset of the related data, you can filter and sort the list of relations inside the select or include.

For example, the following query returns all users and a list of titles of the unpublished posts associated with each user:

const result = await prisma.user.findFirst({
  select: {
    posts: {
      where: {
        published: false,
      },
      orderBy: {
        title: 'asc',
      },
      select: {
        title: true,
      },
    },
  },
})

You can also write the same query using include as follows:

const result = await prisma.user.findFirst({
  include: {
    posts: {
      where: {
        published: true,
      },
      orderBy: {
        title: 'asc',
      },
    },
  },
})

Nested writes

A nested write allows you to write relational data to your database in a single transaction.

Nested writes:

Provide transactional guarantees for creating, updating or deleting data across multiple tables in a single Prisma Client query. If any part of the query fails (for example, creating a user succeeds but creating posts fails), Prisma Client rolls back all changes.
Support any level of nesting supported by the data model.
Are available for relation fields when using the model's create or update query. The following section shows the nested write options that are available per query.

You can create a record and one or more related records at the same time. The following query creates a User record and two related Post records:

const result = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'Elsa Prisma',
    posts: {
      create: [
        { title: 'How to make an omelette' },
        { title: 'How to eat an omelette' },
      ],
    },
  },
  include: {
    posts: true, // Include all posts in the returned object
  },
})

Show query results

There are two ways to create or update a single record and multiple related records - for example, a user with multiple posts:

Use a nested create query
Use a nested createMany query

Each technique has pros and cons:

Feature	`create`	`createMany`	Notes
Creates one record at a time	✔	✘	Potentially less performant.
Creates all records in one query	✘	✔	Potentially more performant.
Supports nesting additional relations	✔	✘ *	For example, you can create a user, several posts, and several comments per post in one query. * You can manually set a foreign key in a has-one relation - for example: `{ authorId: 9}`
Supports skipping duplicate records	✘	✔	Use `skipDuplicates` query option.
Supports has-many relations	✔	✔	For example, you can create a user and multiple posts (one user has many posts)
Supports many-to-many relations	✔	✘	For example, you can create a post and several categories (one post can have many categories, and one category can have many posts)

Using nested `create`

The following query uses nested create to create:

One user
Two posts
One post category

The example also uses a nested include to include all posts and post categories in the returned data.

const result = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'Yvette',
    posts: {
      create: [
        {
          title: 'How to make an omelette',
          categories: {
            create: {
              name: 'Easy cooking',
            },
          },
        },
        { title: 'How to eat an omelette' },
      ],
    },
  },
  include: {
    // Include posts
    posts: {
      include: {
        categories: true, // Include post categories
      },
    },
  },
})

Show query results

Using nested `createMany`

The following query uses a nested createMany to create:

One user
Two posts

The example also uses a nested include to include all posts in the returned data.

const result = await prisma.user.create({
  data: {
    email: '[email protected]',
    posts: {
      createMany: {
        data: [{ title: 'My first post' }, { title: 'My second post' }],
      },
    },
  },
  include: {
    posts: true,
  },
})

Show query results

warning

Note: It is not possible to nest an additional create or createMany inside the highlighted query, which means that you cannot create a user, posts, and post categories at the same time.

You cannot access relations in a createMany query, which means that you cannot create multiple users and multiple posts in a single nested write. The following is not possible:

const createMany = await prisma.user.createMany({
  data: [
    {
      name: 'Yewande',
      email: '[email protected]',
      posts: {
        // Not possible to create posts!
      },
    },
    {
      name: 'Noor',
      email: '[email protected]',
      posts: {
        // Not possible to create posts!
      },
    },
  ],
})

Connect multiple records

The following query creates (create ) a new User record and connects that record (connect ) to three existing posts:

const result = await prisma.user.create({
  data: {
    email: '[email protected]',
    posts: {
      connect: [{ id: 8 }, { id: 9 }, { id: 10 }],
    },
  },
  include: {
    posts: true, // Include all posts in the returned object
  },
})

Show query results

Note: Prisma Client throws an exception if any of the post records cannot be found: connect: [{ id: 8 }, { id: 9 }, { id: 10 }]

Connect a single record

You can connect an existing record to a new or existing user. The following query connects an existing post (id: 11) to an existing user (id: 9)

const result = await prisma.user.update({
  where: {
    id: 9,
  },
  data: {
    posts: {
      connect: {
        id: 11,
      },
    },
  },
  include: {
    posts: true,
  },
})

Connect or create a record

If a related record may or may not already exist, use connectOrCreate to connect the related record:

Connect a User with the email address [email protected] or
Create a new User with the email address [email protected] if the user does not already exist

const result = await prisma.post.create({
  data: {
    title: 'How to make croissants',
    author: {
      connectOrCreate: {
        where: {
          email: '[email protected]',
        },
        create: {
          email: '[email protected]',
          name: 'Viola',
        },
      },
    },
  },
  include: {
    author: true,
  },
})

Show query results

To disconnect one out of a list of records (for example, a specific blog post) provide the ID or unique identifier of the record(s) to disconnect:

const result = await prisma.user.update({
  where: {
    id: 16,
  },
  data: {
    posts: {
      disconnect: [{ id: 12 }, { id: 19 }],
    },
  },
  include: {
    posts: true,
  },
})

Show query results

To disconnect one record (for example, a post's author), use disconnect: true:

const result = await prisma.post.update({
  where: {
    id: 23,
  },
  data: {
    author: {
      disconnect: true,
    },
  },
  include: {
    author: true,
  },
})

Show query results

To disconnect all related records in a one-to-many relation (a user has many posts), set the relation to an empty list as shown:

const result = await prisma.user.update({
  where: {
    id: 16,
  },
  data: {
    posts: {
      set: [],
    },
  },
  include: {
    posts: true,
  },
})

Show query results

Delete all related Post records:

const result = await prisma.user.update({
  where: {
    id: 11,
  },
  data: {
    posts: {
      deleteMany: {},
    },
  },
  include: {
    posts: true,
  },
})

Update a user by deleting all unpublished posts:

const result = await prisma.user.update({
  where: {
    id: 11,
  },
  data: {
    posts: {
      deleteMany: {
        published: false,
      },
    },
  },
  include: {
    posts: true,
  },
})

Update a user by deleting specific posts:

const result = await prisma.user.update({
  where: {
    id: 6,
  },
  data: {
    posts: {
      deleteMany: [{ id: 7 }],
    },
  },
  include: {
    posts: true,
  },
})

You can use a nested updateMany to update all related records for a particular user. The following query unpublishes all posts for a specific user:

const result = await prisma.user.update({
  where: {
    id: 6,
  },
  data: {
    posts: {
      updateMany: {
        where: {
          published: true,
        },
        data: {
          published: false,
        },
      },
    },
  },
  include: {
    posts: true,
  },
})

const result = await prisma.user.update({
  where: {
    id: 6,
  },
  data: {
    posts: {
      update: {
        where: {
          id: 9,
        },
        data: {
          title: 'My updated title',
        },
      },
    },
  },
  include: {
    posts: true,
  },
})

The following query uses a nested upsert to update "[email protected]" if that user exists, or create the user if they do not exist:

const result = await prisma.post.update({
  where: {
    id: 6,
  },
  data: {
    author: {
      upsert: {
        create: {
          email: '[email protected]',
          name: 'Bob the New User',
        },
        update: {
          email: '[email protected]',
          name: 'Bob the existing user',
        },
      },
    },
  },
  include: {
    author: true,
  },
})

You can nest create or createMany inside an update to add new related records to an existing record. The following query adds two posts to a user with an id of 9:

const result = await prisma.user.update({
  where: {
    id: 9,
  },
  data: {
    posts: {
      createMany: {
        data: [{ title: 'My first post' }, { title: 'My second post' }],
      },
    },
  },
  include: {
    posts: true,
  },
})

Relation filters

Filter on "-to-many" relations

Prisma Client provides the some, every, and none options to filter records by the properties of related records on the "-to-many" side of the relation. For example, filtering users based on properties of their posts.

For example:

Requirement	Query option to use
"I want a list of every `User` that has at least one unpublished `Post` record"	`some` posts are unpublished
"I want a list of every `User` that has no unpublished `Post` records"	`none` of the posts are unpublished
"I want a list of every `User` that has only unpublished `Post` records"	`every` post is unpublished

For example, the following query returns User that meet the following criteria:

No posts with more than 100 views
All posts have less than, or equal to 50 likes

const users = await prisma.user.findMany({
  where: {
    posts: {
      none: {
        views: {
          gt: 100,
        },
      },
      every: {
        likes: {
          lte: 50,
        },
      },
    },
  },
  include: {
    posts: true,
  },
})

Filter on "-to-one" relations

Prisma Client provides the is and isNot options to filter records by the properties of related records on the "-to-one" side of the relation. For example, filtering posts based on properties of their author.

For example, the following query returns Post records that meet the following criteria:

Author's name is not Bob
Author is older than 40

const users = await prisma.post.findMany({
  where: {
    author: {
      isNot: {
        name: 'Bob',
      },
      is: {
        age: {
          gt: 40,
        },
      },
    },
  },
  include: {
    author: true,
  },
})

Filter on absence of "-to-many" records

For example, the following query uses none to return all users that have zero posts:

const usersWithZeroPosts = await prisma.user.findMany({
  where: {
    posts: {
      none: {},
    },
  },
  include: {
    posts: true,
  },
})

Filter on absence of "-to-one" relations

The following query returns all posts that don't have an author relation:

const postsWithNoAuthor = await prisma.post.findMany({
  where: {
    author: null, // or author: { }
  },
  include: {
    author: true,
  },
})

The following query returns all users with at least one post:

const usersWithSomePosts = await prisma.user.findMany({
  where: {
    posts: {
      some: {},
    },
  },
  include: {
    posts: true,
  },
})

Fluent API

The fluent API lets you fluently traverse the relations of your models via function calls. Note that the last function call determines the return type of the entire query (the respective type annotations are added in the code snippets below to make that explicit).

This query returns all Post records by a specific User:

const postsByUser: Post[] = await prisma.user
  .findUnique({ where: { email: '[email protected]' } })
  .posts()

This is equivalent to the following findMany query:

const postsByUser = await prisma.post.findMany({
  where: {
    author: {
      email: '[email protected]',
    },
  },
})

The main difference between the queries is that the fluent API call is translated into two separate database queries while the other one only generates a single query (see this GitHub issue)

Note: You can use the fact that .findUnique({ where: { email: '[email protected]' } }).posts() queries are automatically batched by the Prisma dataloader in Prisma Client to avoid the n+1 problem in GraphQL resolvers.

This request returns all categories by a specific post:

const categoriesOfPost: Category[] = await prisma.post
  .findUnique({ where: { id: 1 } })
  .categories()

Note that you can chain as many queries as you like. In this example, the chaining starts at Profile and goes over User to Post:

const posts: Post[] = await prisma.profile
  .findUnique({ where: { id: 1 } })
  .user()
  .posts()

The only requirement for chaining is that the previous function call must return only a single object (e.g. as returned by a findUnique query or a "to-one relation" like profile.user()).

The following query is not possible because findMany does not return a single object but a list:

// This query is illegal
const posts = await prisma.user.findMany().posts()

Nested reads​

Relation load strategies (Preview)​

Examples​

When to use which load strategy?​

Include a relation​

Include all fields for a specific relation​

Include deeply nested relations​

Select specific relation fields​

Relation count​

Filter a list of relations​

Nested writes​

Create a related record​

Create a single record and multiple related records​

Using nested create​

Using nested createMany​

Create multiple records and multiple related records​

Connect multiple records​

Connect a single record​

Connect or create a record​

Disconnect a related record​

Disconnect all related records​

Delete all related records​

Delete specific related records​

Update all related records (or filter)​

Update a specific related record​

Update or create a related record​

Add new related records to an existing record​

Relation filters​

Filter on "-to-many" relations​

Filter on "-to-one" relations​

Filter on absence of "-to-many" records​

Filter on absence of "-to-one" relations​

Filter on presence of related records​

Fluent API​

Nested reads

Relation load strategies (Preview)

Examples

When to use which load strategy?

Include a relation

Include all fields for a specific relation

Include deeply nested relations

Select specific relation fields

Relation count

Filter a list of relations

Nested writes

Create a related record

Create a single record and multiple related records

Using nested `create`

Using nested `createMany`

Create multiple records and multiple related records

Connect multiple records

Connect a single record

Connect or create a record

Disconnect a related record

Disconnect all related records

Delete all related records

Delete specific related records

Update all related records (or filter)

Update a specific related record

Update or create a related record

Add new related records to an existing record

Relation filters

Filter on "-to-many" relations

Filter on "-to-one" relations

Filter on absence of "-to-many" records

Filter on absence of "-to-one" relations

Filter on presence of related records

Fluent API