One of the main features of Prisma Client is its API for sending relation queries. Relation queries refer to queries that operate on a relation between two or more models:

This page explains which relation queries exist and how to use them.

The examples on this page based on the following data model:

datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
model User {
id Int @id @default(autoincrement())
posts Post[]
profile Profile?
}
model Profile {
id Int @id @default(autoincrement())
user User @relation(fields: [userId], references: [id])
userId Int
}
model Post {
id Int @id @default(autoincrement())
author User? @relation(fields: [authorId], references: [id])
authorId Int?
categories Category[] @relation(references: [id])
}
model Category {
id Int @id @default(autoincrement())
posts Post[] @relation(references: [id])
}

Note. This schema is the same as the example data model but has all scalar fields removed so you can focus on the relation fields.

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

Note that this call is equivalent to this Prisma Client query:

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

The main difference between the two 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).

This request returns all categories by a specific post:

const categoriesOfPost: Category[] = await prisma.post.findOne({ 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
.findOne({ 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 findOne 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()
}

Relation filters

A relation filter is a filter operation that's applied to a related object of a model. Relation filter options can be passed to the last chained query in a fluent API call if it returns a list.

Retrieve all Post records of a particular User record that start with "Hello"

const posts: Post[] = await prisma.user
.findOne({
where: { email: 'ada@prisma.io' },
})
.posts({
where: {
title: { startsWith: 'Hello' },
},
})

Note that this query is equivalent to the following one which is initiated via the post instead of the user field (in other words, it doesn't use the fluent API):

const posts = await prisma.post.findMany({
where: {
author: { email: 'bob@prisma.io' },
title: { startsWith: 'Hello' },
},
})
console.log(posts)

The main difference between the two 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).

Nested writes

Nested writes provide a way for writing relational data in your database. They further provide transactional guarantees for creating, updating or deleting data across multiple tables in a single Prisma Client query.

Nested writes can be nested arbitrarily deep.

Nested writes 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.

One-to-one relations

This section shows examples for nested writes on one-to-one relations. It uses the UserProfile relation from the sample data model above. For illustration purposes, the email and bio fields have been added:

model User {
id Int @id @default(autoincrement())
email String @unique
profile Profile
}
model Profile {
id Int @id @default(autoincrement())
bio String
user User? @relation(fields: [userId], references: [id])
userId Int?
}

The following nested write options for Profile are available in prisma.user.create(...) queries:

  • create: Create a new user and a new profile
  • connect: Create a new user and connect it to an existing profile
  • connectOrCreate (preview): Create a new user and connect it to an existing profile, or create a new profile if the supplied profile ID does not exist

The following nested write options for Profile are available in prisma.user.update(...) queries:

  • create: Update an existing user by creating a new profile
  • connect: Update an an existing user by connecting it to an existing profile
  • connectOrCreate (preview): Update an existing user by connecting it to an existing profile, or create a new profile if the supplied profile ID does not exist
  • update: Update an existing user by updating their existing profile
  • upsert: Update an existing user by updating their existing profile or by creating a new profile
  • delete (only if relation is optional): Update an existing user by deleting their existing profile
  • disconnect (only if relation is optional): Update an existing user by removing the connection to their existing profile

Create a new User record with a new Profile record

const user = await prisma.user.create({
data: {
email: 'alice@prisma.io',
profile: {
create: { bio: 'Hello World' },
},
},
})

This example uses the user model property, but you could also run the query from the profile side:

const user = await prisma.profile.create({
data: {
bio: 'Hello World',
user: {
create: { email: 'alice@prisma.io' },
},
},
})

Create a new Profile record and connect it to an existing User record

const user = await prisma.profile.create({
data: {
bio: 'Hello World',
user: {
connect: { email: 'alice@prisma.io' },
},
},
})

Note that this requires that a User record with an email of "alice@prisma.io" already exists in the database. If that's not the case, the query will fail with an exception.

You can provide any unique or ID property to the connect option, so in this case you could also provide the id field to identify the target User record:

const user = await prisma.profile.create({
data: {
bio: 'Hello World',
user: {
connect: { id: 42 },
},
},
})

Create a new Profile record, then connect it to an existing User record or create a new User

The following example:

  1. Creates a Profile
  2. Attempts to connect the profile to a User where the email address is alice@prisma.io
  3. Creates a new user if a user if a matching user does not exist
const user = await prisma.profile.create({
data: {
bio: 'The coolest Alice on the planet',
user: {
connectOrCreate: { // connectOrCreate is a preview feature and must be enabled!
where: { email: 'alice@prisma.io' },
create: { email: 'alice@prisma.io'}
},
},
})

Note: connectOrCreate is a preview feature. To enable this feature, add the following to your schema.prisma file and re-generate the client:

generator client {
provider = "prisma-client-js"
experimentalFeatures = ["connectOrCreate"]
}

Update an existing User record by creating a new Profile record

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
profile: {
create: { bio: 'Hello World' },
},
},
})

Update an existing User record by connecting it to an existing Profile record

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
profile: {
connect: { id: 24 },
},
},
})

Update an existing User record by connecting it to an existing Profile record, or creating a new Profile record

The following example:

  1. Attempts to connect the user to a Profile with an id of 20
  2. Creates a new profile if a matching profile does not exist
const updateUser = await prisma.user.update({
where: { email: "alice@prisma.io" },
data: {
profile: {
connectOrCreate: { // connectOrCreate is a preview feature and must be enabled!
where: { id: 20 },
create: {
bio: "The coolest Alice in town",
},
},
},
},
});

Note: connectOrCreate is a preview feature. To enable this feature, add the following to your schema.prisma file and re-generate the client:

generator client {
provider = "prisma-client-js"
experimentalFeatures = ["connectOrCreate"]
}

Update an existing User record by updating the Profile record it's connected to

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
profile: {
update: { bio: 'Hello World' },
},
},
})

Update an existing User record by updating the Profile record it's connected to or creating a new one (upsert)

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
profile: {
upsert: {
create: { bio: 'Hello World' },
update: { bio: 'Hello World' },
},
},
},
})

Update an existing User record by deleting the Profile record it's connected to

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
profile: {
delete: true,
},
},
})

Update an existing User record by disconnecting the Profile record it's connected to

const user = await prisma.user.update({
where: { email: 'bob @prisma.io' },
data: {
profile: {
disconnect: true,
},
},
})

Note that this query is actually illegal with the data model from above because the profile field on User is required. In order to make this query succeed, you'd need to make both relation fields optional (by adding '?'):

model User {
id Int @id @default(autoincrement())
email String @unique
profile Profile?
}
model Profile {
id Int @id @default(autoincrement())
bio String
user User? @relation(fields: [userId], references: [id])
userId Int?
}

One-to-many relations

This section shows examples for nested writes on one-to-many relations. It uses the UserPost relation from the sample data model above. For illustration purposes, the email, title and published fields have been added:

model User {
id Int @id @default(autoincrement())
email String @unique
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
published Boolean @default(false)
author User? @relation(fields: [authorId], references: [id])
authorId Int?
}

The following nested write options for Post are available in prisma.user.create(...) queries:

  • create: Create a new user and one or more new posts
  • connect: Create a new user and connect it to one or more existing posts
  • connectOrCreate (preview): Create a new user and connect it to one or more existing posts, or create a new posts if the supplied post ID does not exist

The following nested write options for Post are available in prisma.post.update(...) queries:

  • create: Update an existing user by creating one or more new posts
  • connect: Update an existing user by connecting it to one or more existing posts
  • connectOrCreate (preview): Update an existing user by connecting it to one or more existing posts, or create a new posts if the supplied post ID does not exist
  • update: Update an existing user by updating one or more of their existing posts
  • upsert: Update an existing user by updating one or more of their existing posts or by creating one or more new posts
  • delete: Update an existing user by deleting one or more of their existing posts
  • disconnect: Update an existing by removing the connection(s) to one or more of their existing posts
  • set: Update an existing user by replacing their existing posts with one or more existing posts
  • updateMany: Update an existing user by updating one or more of their existing posts
  • deleteMany: Update an existing user by deleting one or more of their existing posts

Create a new User record with a new Post record:

const user = await prisma.user.create({
data: {
email: 'alice@prisma.io',
posts: {
create: { title: 'Hello World' },
},
},
})

This example uses the user model property, but you could also run the query from the post side:

const user = await prisma.post.create({
data: {
title: 'Hello World',
author: {
create: { email: 'alice@prisma.io' },
},
},
})

Create a new User record with two new Post records

Because it's a one-to-many relation, you can also create several Post records at once by passing an array to create:

const user = await prisma.user.create({
data: {
email: 'alice@prisma.io',
posts: {
create: [{ title: 'This is my first post' }, { title: 'Here comes a second post' }],
},
},
})

Create a new Post record and connect it to an existing User record

const user = await prisma.post.create({
data: {
title: 'Hello World',
author: {
connect: { email: 'alice@prisma.io' },
},
},
})

Note that this requires that a User record with an email of "alice@prisma.io" already exists in the database. If that's not the case, the query will fail with an exception.

You can provide any unique or ID property to the connect option, so in this case you could also provide the id field to identify the target User record:

const user = await prisma.post.create({
data: {
title: 'Hello World',
author: {
connect: { id: 42 },
},
},
})

Create a new Post record and connect it to an existing User record, or create a new User

const user = await prisma.post.create({
data: {
title: "Hello World",
author: {
connectOrCreate: { // connectOrCreate is a preview feature and must be enabled!
where: { email: "alice@prisma.io" },
create: { email: "alice@prisma.io" },
},
},
},
});

Note: connectOrCreate is a preview feature. To enable this feature, add the following to your schema.prisma file and re-generate the client:

generator client {
provider = "prisma-client-js"
experimentalFeatures = ["connectOrCreate"]
}

Update an existing User record by creating a new Post record

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
create: { title: 'Hello World' },
},
},
})

Update an existing User record by connecting it to two existing Post records

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
connect: [{ id: 24 }, { id: 42 }],
},
},
})

Update an existing User record by connect it to two existing Post records, or creating two new Post records

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
connectOrCreate: [ // connectOrCreate is a preview feature and must be enabled!
{
where: { id: 32 },
create: { title: 'This is my first post' },
},
{
where: { id: 19 },
create: { title: 'This is my second post' },
},
],
},
},
})

Note: connectOrCreate is a preview feature. To enable this feature, add the following to your schema.prisma file and re-generate the client:

generator client {
provider = "prisma-client-js"
experimentalFeatures = ["connectOrCreate"]
}

Update an existing User record by updating two Post records it's connected to

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
update: [
{
data: { published: true },
where: { id: 32 },
},
{
data: { published: true },
where: { id: 23 },
},
],
},
},
})

Update an existing User record by updating two Post record it's connected to or creating new ones (upsert)

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
upsert: [
{
create: { title: 'This is my first post' },
update: { title: 'This is my first post' },
where: { id: 32 },
},
{
create: { title: 'This is mt second post' },
update: { title: 'This is mt second post' },
where: { id: 23 },
},
],
},
},
})

Update an existing User record by deleting two Post records it's connected to

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
delete: [{ id: 34 }, { id: 36 }],
},
},
})

Update an existing User record by disconnecting two Post records it's connected to

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
disconnect: [{ id: 44 }, { id: 46 }],
},
},
})

Update an existing User record by disconnecting any previous Post records and connect two other exiting ones

const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
set: [{ id: 32 }, { id: 42 }],
},
},
})

Nested reads

With nested reads, you can modify the structures of the model objects that are returned by your Prisma Client queries by including their relations. In ORM terminology, this is sometimes called eager loading.

You can load relations of your models with the include and select options which you can pass to any Prisma Client query (except for the batch operations updateMany and deleteMany). include is more commonly used for relations, select is used for selecting specific fields.

Include the posts and profile relation when loading User records

const users = await prisma.user.findMany({
include: {
posts: true,
profile: true,
},
})

Include the posts relation on the returned objects when creating a new User record with two Post records

const user = await prisma.user.create({
data: {
email: 'alice@prisma.io',
posts: {
create: [{ title: 'This is my first post' }, { title: 'Here comes a second post' }],
},
},
include: { posts: true },
})

Retrieve deeply nested data by loading several levels of relations

const users = await prisma.user.findMany({
include: {
posts: {
include: {
categories: {
include: {
posts: true,
},
},
},
},
},
})
Edit this page on GitHub