Relation queries (Concepts)

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.

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


const getUser = await prisma.user.findUnique({
  where: {
    id: 19,
  },
  include: {
    posts: true,
  },
})

Show CLI results

Include all fields for a specific relation

The following example returns all posts with the word cookies in the title field and the author of each post. The result includes all author fields.


const getPosts = await prisma.post.findMany({
  where: {
    title: {
      contains: 'cookies',
    },
  },
  include: {
    author: true, // Return all fields
  },
})

Show CLI 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.findMany({
  include: {
    posts: {
      include: {
        categories: true,
      },
    },
  },
})

Show CLI 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 getUser = await prisma.user.findUnique({
  where: {
    id: 19,
  },
  select: {
    name: true,
    posts: {
      select: {
        title: true,
      },
    },
  },
})

Show CLI 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 getUser = await prisma.user.findUnique({
  where: {
    id: 1,
  },
  // select: { name: true } <-- Can't do this!
  include: {
    posts: {
      select: {
        title: true,
      },
    },
  },
})

Show CLI 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 getUser = await prisma.user.findUnique({
  where: {
    id: 19,
  },
  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 getUser = await prisma.user.findUnique({
  where: {
    id: 19,
  },
  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.

Filter a list of relations

When you use select to return a subset of data from related records (for example, a user's posts), you can filter and sort that list of relations. For example, you might want to query all users with a prisma.io email address and select the titles of their published posts.

Return all `User` records where `email` contains `prisma.io`, and select the `title` of all `Post` records that have not been published yet


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

You can use the post model property to write the same query:


const result = await prisma.post.findMany({
  where: {
    published: false,
    User: {
      email: {
        contains: 'prisma.io',
      },
    },
  },
  select: {
    title: true,
  },
})

Nested writes

A nested write allows you to write relational data to your database in a single transaction. For example, the following nested write creates a User and two related Post records:


const createUserAndPost = await prisma.user.create({
  data: {
    email: 'elsa@prisma.io',
    name: 'Elsa Prisma',
    posts: {
      create: [
        { title: 'How to make an omelette' },
        { title: 'How to eat an omelette' },
      ],
    },
  },
})

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 user = await prisma.user.create({
  data: {
    email: 'elsa@prisma.io',
    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 CLI 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)

The following query uses nested create to create:

One user
Two posts
One post category

The example uses a nested include to include all posts and post categories.


const user = await prisma.user.create({
  data: {
    email: 'yvette@prisma.io',
    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 CLI results

The following query uses a nested createMany to create:

One user
Two posts

The example uses a nested include to include all posts. Note that 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:


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

Show CLI results

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: 'yewande@prisma.io',
      posts: {
        // Not possible to create posts!
      },
    },
    {
      name: 'Noor',
      email: 'noor@prisma.io',
      posts: {
        // Not possible to create posts!
      },
    },
  ],
})

Connect an existing record

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


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

Show CLI results

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

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)
Adds a new related post (title: "My new post title")


const getUser = await prisma.user.update({
  where: {
    id: 9
  },
  data: {
    posts: {
      connect: {
        id: 11
      },
      create: {
        title: "My new post title"
      }
    }
  }
})

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 viola@prisma.io or
Create a new User with the email address viola@prisma.io if the user does not already exist


const createPost = await prisma.post.create({
  data: {
    title: 'How to make croissants',
    author: {
      connectOrCreate: {
        where: {
          email: 'viola@prisma.io',
        },
        create: {
          email: 'viola@prisma.io',
          name: 'Viola',
        },
      },
    },
  },
  include: {
    author: true,
  },
})

Show CLI 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 updatePost = await prisma.user.update({
  where: {
    id: 16,
  },
  data: {
    posts: {
      disconnect: [{ id: 12 }, { id: 19 }],
    },
  },
  select: {
    posts: true,
  },
})

Show CLI results

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


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

Show CLI 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 updateUser = await prisma.user.update({
  where: {
    id: 16
  },
  data: {
    posts: {
      set: []
    }
  },
  include: {
    posts: true
  }
})

Show CLI results

Delete all related Post records:


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

Update a user by deleting all unpublished posts:


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

Update a user by deleting specific posts:


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

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 update = await prisma.user.update({
  where: {
    id: 6,
  },
  data: {
    posts: {
      updateMany: {
        where: {
          published: true,
        },
        data: {
          published: false,
        },
      },
    },
  },
})


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

The following query uses a nested upsert to update "bob@prisma.io" if that user exists, or create the user if they do not exist:


const update = await prisma.post.update({
  where: {
    id: 6,
  },
  data: {
    author: {
      upsert: {
        create: {
          email: 'bob@prisma.io',
          name: 'Bob the New User',
        },
        update: {
          email: 'bob@prisma.io',
          name: 'Bob the existing user',
        },
      },
    },
  },
})

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 user = await prisma.user.update({
  where: {
    id: 9,
  },
  data: {
    posts: {
      createMany: {
        data: [{ title: 'My first post' }, { title: 'My second post' }],
      },
    },
  },
})

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,
        },
      },
    },
  },
})

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
          }
        }
      }
    }
  },
})

Filtering by count of relations is not yet supported - however, you can filter on whether a record has any related records at all. For example, the following query uses none to return all users that have zero posts:


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

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


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

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: 'alice@prisma.io' } })
  .posts()

This is equivalent to the following findMany query:


const postsByUser = await prisma.post.findMany({
  where: { author: { email: 'alice@prisma.io' } },
})

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: 'alice@prisma.io' } }).posts() queries are automatically batched by the Prisma dataloader 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()

Was this helpful?

Edit this page on GitHub

Prisma ORM

Prisma Data Platform

Relation queries

Nested reads

Include all fields for a specific relation

Include deeply nested relations

Select specific relation fields

Relation count

Filter a list of relations

Return all `User` records where `email` contains `prisma.io`, and select the `title` of all `Post` records that have not been published yet

Nested writes

Connect an existing record

Connect or create a record

Relation filters

Filter on "-to-many" relations

Filter on "-to-one" relations

Fluent API

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter

Prisma ORM

Prisma Data Platform

Latest from the blog

Relation queries

Nested reads

Include all fields for a specific relation

Include deeply nested relations

Select specific relation fields

Relation count

Filter a list of relations

Return all User records where email contains prisma.io, and select the title of all Post records that have not been published yet

Nested writes

Create a related record

Create a single record and multiple related records

Create multiple records and multiple related records

Connect an existing 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 presence of related records

Fluent API

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter

Return all `User` records where `email` contains `prisma.io`, and select the `title` of all `Post` records that have not been published yet