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
andinclude
- 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 nestselect
inside aninclude
.
The following example returns a single user and that user's posts:
const getUser = await prisma.user.findUnique({where: {id: 19,},include: {posts: true,},})
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},})
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,},},},})
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,},},},})
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,},},},})
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 exceptionconst getUser = await prisma.user.findUnique({where: {id: 19,},select: { // This won't work!email: true}include: { // This won't work!posts: {select: {title: true}}},})
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.
Create a related record
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},})
Create a single record and multiple related records
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 postsposts: {include: {categories: true, // Include post categories},},},})
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,},})
Create multiple records and multiple related records
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},})
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 addressviola@prisma.io
or - Create a new
User
with the email addressviola@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,},})
Disconnect a related record
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,},})
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,},})
Disconnect all related records
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}})
Delete all related records
Delete all related Post
records:
const update = await prisma.user.update({where: {id: 11,},data: {posts: {deleteMany: {},},},})
Delete specific related records
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 }],},},})
Update all related records (or filter)
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,},},},},})
Update a specific related record
const update = await prisma.user.update({where: {id: 6,},data: {posts: {update: {where: {id: 9,},data: {title: 'My updated title',},},},},})
Update or create a related record
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',},},},},})
Add new related records to an existing record
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}}}}},})
Filter on presence of related records
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 illegalconst posts = await prisma.user.findMany().posts()