Relation queries

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

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:

dart

final user = await prisma.user.findFirst(
  include: UserInclude(
    posts: PrismaUnion.$1(true),
  ),
);

Include all fields for a specific relation

The following example returns a post and its author:

dart

final user = await prisma.post.findFirst(
  include: PostInclude(
    author: PrismaUnion.$1(true),
  ),
);

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:

dart

final user = await prisma.user.findFirst(
  include: UserInclude(
    posts: PrismaUnion.$2(
      UserPostsArgs(
        include: PostInclude(
          categories: PrismaUnion.$1(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:

dart

final user = await prisma.user.findFirst(
  select: UserSelect(
    name: true,
    posts: PrismaUnion.$2(
      UserPostsArgs(
        select: PostSelect(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:

dart

final user = await prisma.user.findFirst(
  include: UserInclude(
    posts: PrismaUnion.$2(
      UserPostsArgs(
        select: PostSelect(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 user's email:

dart

final user = await prisma.user.findFirst(
    select: UserSelect( // This won't work!
        email: true,
    ),
    include: UserInclude( // This won't work!
        posts: PrismaUnion.$2(
          UserPostsArgs(
            select: PostSelect(title: true),
          ),
        ),
    ),
);

Instead, use nested select options:

dart

final user = await prisma.user.findFirst(
  select: UserSelect(
    name: true,
    posts: PrismaUnion.$2(
      UserPostsArgs(
        select: PostSelect(title: true),
      ),
    ),
  ),
);

Relation count

you can include or select a count of relations alongside fields - for example, to return a user for example, a user's post count:

dart

final relationCount = await prisma.user.findMany(
  include: UserInclude(
    $count: PrismaUnion.$2(
      UserCountArgs(
        select: UserCountOutputTypeSelect(posts: true),
      ),
    ),
  ),
);

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:

dart

final filterListOfRelations = await prisma.user.findFirst(
  select: UserSelect(
    posts: PrismaUnion.$2(
      UserPostsArgs(
        where: PostWhereInput(
          published: PrismaUnion.$2(false),
        ),
        orderBy: PrismaUnion.$2(
          PostOrderByWithRelationInput(
            title: SortOrder.asc,
          ),
        ),
        select: PostSelect(title: true),
      ),
    ),
  ),
);

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

dart

final filterListOfRelationsInclude = await prisma.user.findFirst(
  include: UserInclude(
    posts: PrismaUnion.$2(
      UserPostsArgs(
        where: PostWhereInput(
          published: PrismaUnion.$2(false),
        ),
        orderBy: PrismaUnion.$2(
          PostOrderByWithRelationInput(
            title: SortOrder.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:

dart

final createRelatedRecord = await prisma.user.create(
  data: PrismaUnion.$1(
    UserCreateInput(
      email: "elsa@examole.com",
      name: PrismaUnion.$1("Elsa"),
      posts: PostCreateNestedManyWithoutAuthorInput(
        create: PrismaUnion.$2(
          PrismaUnion.$1([
            PostCreateWithoutAuthorInput(
              title: "Join the Resistance",
            ),
            PostCreateWithoutAuthorInput(
              title: "Join the Resistance 2",
            ),
          ]),
        ),
      ),
    ),
  ),
  include: UserInclude(
    posts: PrismaUnion.$1(true),
  ),
);

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 to create the related records.
Use a nested createMany query to create the related records.

Each technique has pros and cons:

Feature	`crteate`	`createMany`	Description
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 for that user
One post category for each post

The example uses a nested include include all posts and post categories:

dart

await prisma.user.create(
  data: PrismaUnion.$1(
    UserCreateInput(
      email: "vv@prisma.pub",
      name: PrismaUnion.$1("Vivian"),
      posts: PostCreateNestedManyWithoutAuthorInput(
        create: PrismaUnion.$2(
          PrismaUnion.$1([
            PostCreateWithoutAuthorInput(
              title: "Join the Resistance",
              categories: CategoryCreateNestedManyWithoutPostsInput(
                create: PrismaUnion.$1(
                  CategoryCreateWithoutPostsInput(name: "Easy cooking"),
                ),
              ),
            ),
            PostCreateWithoutAuthorInput(
              title: "Join the Resistance 2",
            ),
          ]),
        ),
      ),
    ),
  ),
  include: UserInclude(
    posts: PrismaUnion.$2(
      UserPostsArgs(
        include: PostInclude(
          categories: PrismaUnion.$1(true),
        ),
      ),
    ),
  ),
);

The following query uses a nested createMany to create:

One user
Two posts for that user

The example uses a nested include to include all posts:

dart

await prisma.user.create(
  data: PrismaUnion.$1(
    UserCreateInput(
      email: "saanvi@examole.com",
      posts: PostCreateNestedManyWithoutAuthorInput(
        createMany: PostCreateManyAuthorInputEnvelope(
          data: PrismaUnion.$2([
            PostCreateManyAuthorInput(title: "My first post"),
            PostCreateManyAuthorInput(title: "My second post"),
          ]),
        ),
      ),
    ),
  ),
  include: UserInclude(
    posts: PrismaUnion.$1(true),
  ),
);

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:

dart

await prisma.user.createMany(
  data: PrismaUnion.$2([
    UserCreateManyInput(
      email: "yewande@a.com",
      name: PrismaUnion.$1("Yewande"),
      // posts: ... // Not possible to create posts! Type-safe, not `posts` field on `UserCreateManyInput`
    ),
    UserCreateManyInput(
      email: "noor@a.com",
      name: PrismaUnion.$1("Noor"),
      // posts: ... // Not possible to create posts! Type-safe, not `posts` field on `UserCreateManyInput`
    ),
  ]),
);

Connect multiple records

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

dart

await prisma.user.create(
  data: PrismaUnion.$1(
    UserCreateInput(
      email: 'vlad@prisma.pub',
      posts: PostCreateNestedManyWithoutAuthorInput(
        connect: PrismaUnion.$2([
          PostWhereUniqueInput(id: 1),
          PostWhereUniqueInput(id: 2),
          PostWhereUniqueInput(id: 3),
          // ... More `PostWhereUniqueInput` objects
        ]),
      ),
    ),
  ),
  include: UserInclude(
    posts: PrismaUnion.$1(true),
  ),
);

INFO

Prisma Client throws an exception if any of the post records cannot be found:

connect: [{ id: 1 }, { id: 2 }, { id: 3 }]

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)

dart

await prisma.user.update(
  where: UserWhereUniqueInput(id: 9),
  data: PrismaUnion.$1(
    UserUpdateInput(
      posts: PostUpdateManyWithoutAuthorNestedInput(
        connect: PrismaUnion.$1(
          PostWhereUniqueInput(id: 11),
        ),
      ),
    ),
  ),
  include: UserInclude(
    posts: PrismaUnion.$1(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 viola@prisma.io or Create a new User with the email address viola@prisma.io if the user does not already exist

dart

await prisma.post.create(
  data: PrismaUnion.$1(
    PostCreateInput(
      title: "My first post",
      author: UserCreateNestedOneWithoutPostsInput(
        connectOrCreate: UserCreateOrConnectWithoutPostsInput(
          where: UserWhereUniqueInput(email: "viola@prisma.io"),
          create: PrismaUnion.$1(
            UserCreateWithoutPostsInput(
              email: "viola@prisma.io",
              name: PrismaUnion.$1("Viola"),
            ),
          ),
        ),
      ),
    ),
  ),
  include: PostInclude(
    author: PrismaUnion.$1(true),
  ),
);

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:

dart

await prisma.user.update(
  where: UserWhereUniqueInput(id: 10),
  data: PrismaUnion.$1(
    UserUpdateInput(
      posts: PostUpdateManyWithoutAuthorNestedInput(
        disconnect: PrismaUnion.$2([
          PostWhereUniqueInput(id: 11),
          PostWhereUniqueInput(id: 12),
        ]),
      ),
    ),
  ),
  include: UserInclude(
    posts: PrismaUnion.$1(true),
  ),
);

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

dart

await prisma.post.update(
  where: PostWhereUniqueInput(id: 11),
  data: PrismaUnion.$1(
    PostUpdateInput(
      author: UserUpdateOneWithoutPostsNestedInput(
        disconnect: PrismaUnion.$1(true),
      ),
    ),
  ),
  include: PostInclude(
    author: PrismaUnion.$1(true),
  ),
);

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:

dart

await prisma.user.update(
  where: UserWhereUniqueInput(id: 10),
  data: PrismaUnion.$1(
    UserUpdateInput(
      posts: PostUpdateManyWithoutAuthorNestedInput(
        set: PrismaUnion.$2([]),
      ),
    ),
  ),
);

Delete all related Post records for a specific User:

dart

await prisma.user.update(
  where: UserWhereUniqueInput(id: 10),
  data: PrismaUnion.$1(
    UserUpdateInput(
      posts: PostUpdateManyWithoutAuthorNestedInput(
        deleteMany: PrismaUnion.$1(
          PostScalarWhereInput(),
        ),
      ),
    ),
  ),
);

Update a user by deleting all unpublished posts:

dart

await prisma.user.update(
  where: UserWhereUniqueInput(id: 10),
  data: PrismaUnion.$1(
    UserUpdateInput(
      posts: PostUpdateManyWithoutAuthorNestedInput(
        deleteMany: PrismaUnion.$1(
          PostScalarWhereInput(
            published: PrismaUnion.$2(false),
          ),
        ),
      ),
    ),
  ),
);

Update a user by deleting specific posts:

dart

await prisma.user.update(
  where: UserWhereUniqueInput(id: 10),
  data: PrismaUnion.$1(
    UserUpdateInput(
      posts: PostUpdateManyWithoutAuthorNestedInput(
        deleteMany: PrismaUnion.$2([
          PostScalarWhereInput(id: PrismaUnion.$2(11)),
          PostScalarWhereInput(id: PrismaUnion.$2(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:

dart

await prisma.user.update(
  where: UserWhereUniqueInput(id: 10),
  data: PrismaUnion.$1(
    UserUpdateInput(
      posts: PostUpdateManyWithoutAuthorNestedInput(
        updateMany: PrismaUnion.$1(
          PostUpdateManyWithWhereWithoutAuthorInput(
            where: PostScalarWhereInput(
              published: PrismaUnion.$2(true),
            ),
            data: PrismaUnion.$1(
              PostUpdateManyMutationInput(
                published: PrismaUnion.$1(false),
              ),
            ),
          ),
        ),
      ),
    ),
  ),
);

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

dart

await prisma.user.findMany(
  where: UserWhereInput(
    posts: PostListRelationFilter(
      none: PostWhereInput(
        views: PrismaUnion.$1(
          IntFilter(gt: PrismaUnion.$1(100)),
        ),
      ),
      every: PostWhereInput(
        likes: PrismaUnion.$1(
          IntFilter(lte: PrismaUnion.$1(50)),
        ),
      ),
    ),
  ),
);

Filter on "-to-one" relations

Prisma Dart 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 all posts that meet the following criteria:

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

dart

await prisma.post.findMany(
  where: PostWhereInput(
    author: PrismaUnion.$1(
      UserNullableRelationFilter(
        isNot: PrismaUnion.$1(
          UserWhereInput(
            name: PrismaUnion.$2(
              PrismaUnion.$1("Bob"),
            ),
          ),
        ),
        $is: PrismaUnion.$1(
          UserWhereInput(
            age: PrismaUnion.$1(
              IntNullableFilter(
                gt: PrismaUnion.$1(40),
              ),
            ),
          ),
        ),
      ),
    ),
  ),
);

Filter on absence of "-to-many" records

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

dart

await prisma.user.findMany(
  where: UserWhereInput(
    posts: PostListRelationFilter(
      none: PostWhereInput(),
    ),
  ),
);

Filter on absence of "-to-one" relations

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

dart

await prisma.post.findMany(
  where: PostWhereInput(
    author: PrismaUnion.$2(
      PrismaUnion.$2(const PrismaNull()),
    ),
  ),
);

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

dart

await prisma.user.findMany(
  where: UserWhereInput(
    posts: PostListRelationFilter(
      some: PostWhereInput(),
    ),
  ),
);

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 ​

Nested writes ​

Create a related record ​

Create a single record and multiple related records ​

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) ​

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 ​

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

Nested writes

Create a related record

Create a single record and multiple related records

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)

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