CRUD

This page describes how to perform CRUD operations with your generated Prisma Client API. CRUD is an acronym that stands for:

• Create
• Read
• Update
• Delete

Refer to the Client API Reference documentation for a complete list of all available methods.

Example schema and provider

All examples are based on the following Prisma schema:

Expand to view the schema (schema.prisma)

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "dart run orm"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id           Int      @id @default(autoincrement())
  email        String   @unique
  name         String?
  age          Int?
  role         Role     @default(USER)
  posts        Post[]
  Profile      Profile?
  country      String?
  city         String?
  profileViews Int      @default(0)
}

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  content    String?
  published  Boolean    @default(false)
  author     User?      @relation(fields: [authorId], references: [id])
  authorId   Int?
  categories Category[]
  views      Int        @default(0)
  likes      Int        @default(0)
  tags       String[]   @default([])
}

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

model Category {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

enum Role {
  USER
  ADMIN
}

Expand to view the provider (prisma.dart)

import 'dart:async';

import 'prisma/generated_dart_client/client.dart';

/// Create a new instance of PrismaClient
final _client = PrismaClient();

/// Provide a PrismaClient instance to a function.
///
/// Wrapped in a function to ensure that the instance is diconnected
/// after the function is done.
FutureOr<T> providePrisma<T>(
    FutureOr<T> Function(PrismaClient prisma) main) async {
  try {
    return await main(_client);
  } finally {
    await _client.$disconnect();
  }
}

Create

Create a single record

The following query creates (create) a single record of the User model with two fields:

final user = await prisma.user.create(
  data: PrismaUnion.$1(UserCreateInput(
    email: "seven@odroe.com",
    name: PrismaUnion.$1("Seven Du"),
  )),
);

show query results

{id: 1, email: seven@odroe.com, name: Seven Du, role: USER}

The user's id is automatically generated by the database.

Create multiple records

The following createMany query creates multiple users and skips any duplicates ( `email`` must be unique):

final affectedRows = await prisma.user.createMany(
  data: PrismaUnion.$2([
    UserCreateManyInput(
        name: PrismaUnion.$1("Bob1"), email: "bob@prisma.pub"),
    UserCreateManyInput(
        name: PrismaUnion.$1("Bob2"), email: "bob@prisma.pub"),
    UserCreateManyInput(
        name: PrismaUnion.$1("Yewande"), email: "yewande@prisma.pub"),
    UserCreateManyInput(
        name: PrismaUnion.$1("Ange"), email: "ange@prisma.pub"),
  ]),
  skipDuplicates: true, // Skip 'Bob2'
);

show query results

{count: 3}

TIP

The skipDuplicates is not support when using MongoDB and SQL Server.

Create records and connect or create related records

See Related queries -> Nested writes for information about creating a record and one or more related at the same time.

Read

Get record by ID or unique field

The following queries return a single record (findUnique) by ID or unique field:

get_record_by_id.dart

final user = await prisma.user.findUnique(
  where: UserWhereUniqueInput(id: 1),
);

get_record_by_unique_field.dart

final user = await prisma.user.findUnique(
  where: UserWhereUniqueInput(email: "seven@odroe.com"),
);

Get all records

The following findMany query return all User records:

final users = await prisma.user.findMany();

You cal also Paginate you resules.

Get the first record that matches a specific criteria

The following findFirst`` query returns the most recently created User with at least one `post that has more than 100 likes:

1. Order users by descending ID (largest first) - the largest ID is the most recently created user.
2. Filter users by the existence of at least one post with more than 100 likes.

final user = await prisma.user.findFirst(
  where: UserWhereInput(
    posts: PostListRelationFilter(
      some: PostWhereInput(
        likes: PrismaUnion.$1(
          IntFilter(gt: PrismaUnion.$1(100)),
        ),
      ),
    ),
  ),
  orderBy: PrismaUnion.$2(
    UserOrderByWithRelationInput(id: SortOrder.desc),
  ),
);

Get a filtered list of records

Prisma Dart client supports Filtering on record fields and related fields.

Filter by a single field value

The following query returns all User records with an email that ends in "odroe.com":

final users = await prisma.user.findMany(
  where: UserWhereInput(
    email: PrismaUnion.$1(
      StringFilter(endsWith: PrismaUnion.$1('@odroe.com')),
    ),
  ),
);

Filter by multiple field values

The following query users combination of operators to return users whser name start with S or role is ADMIN:

final users = await prisma.user.findMany(
  where: UserWhereInput(
    OR: [
      UserWhereInput(
        name: PrismaUnion.$1(
          StringNullableFilter(startsWith: PrismaUnion.$1("S")),
        ),
      ),
      UserWhereInput(
        role: PrismaUnion.$2(Role.admin),
      ),
    ],
  ),
);

Filter by related record field values

The following query returns users with an email that domain is odroe.com and have at least one post (somo) that is not published:

final users = await prisma.user.findMany(
  where: UserWhereInput(
    email: PrismaUnion.$1(
      StringFilter(endsWith: PrismaUnion.$1("@odroe.com")),
    ),
    posts: PostListRelationFilter(
      some: PostWhereInput(
        published: PrismaUnion.$2(false),
      ),
    ),
  ),
);

See Working with relations for more examples of filtering on related field values.

Select a subset of fields

The following findUnique query uses select to return the name and email fields of a specific User record:

final user = await prisma.user.findUnique(
  where: UserWhereUniqueInput(email: "seven@odroe.com"),
  select: UserSelect(
    name: true,
    email: true,
  ),
);

For more information about including relations, refer to:

• Select fields
• Relation queries

Select a subset of related record fields

The following query uses a nested select to return:

• The User's email field
• The likes field of each Post

final user = await prisma.user.findUnique(
  where: UserWhereUniqueInput(email: "seven@odroe.com"),
  select: UserSelect(
    email: true,
    posts: PrismaUnion.$2(
      UserPostsArgs(
        select: PostSelect(likes: true),
      ),
    ),
  ),
);

Include related records

The following query returns all ADMIN users and includes each user's posts in the result:

final users = await prisma.user.findMany(
  where: UserWhereInput(
    role: PrismaUnion.$2(Role.admin),
  ),
  include: UserInclude(
    posts: PrismaUnion.$1(true),
  ),
);

For more information about including relations, see Select fields -> Nested reads.

Update

Update a single record

The following query uses update to find and update a single User record by email:

final updatedUser = await prisma.user.update(
  where: UserWhereUniqueInput(email: "seven@odroe.com"),
  data: PrismaUnion.$2(
    UserUncheckedUpdateInput(
      name: PrismaUnion.$1("Seven Odroe"),
    ),
  ),
);

show query results

{id: 1, email: seven@odroe.com, name: Seven Odroe, role: USER}

Update multiple records

The following query uses updateMany to update all User records role to ADMIN that contain odroe.com in their email:

final affectedRows = await prisma.user.updateMany(
  where: UserWhereInput(
    email: PrismaUnion.$1(
      StringFilter(endsWith: PrismaUnion.$1("@odroe.com")),
    ),
  ),
  data: PrismaUnion.$1(
    UserUpdateManyMutationInput(
      role: PrismaUnion.$1(Role.admin),
    ),
  ),
);

show query results

{ count: 1 }

Update or create records

The following query uses upsert to update a User record with a specific email address, or create that User record if it does not exist:

final user = await prisma.user.upsert(
  where: UserWhereUniqueInput(email: "medz@prisma.pub"),
  create: PrismaUnion.$1(UserCreateInput(
    email: "medz@prisma.pub",
    name: PrismaUnion.$1("Seven at GitHub username"),
  )),
  update: PrismaUnion.$1(
    UserUpdateInput(
      name: PrismaUnion.$1("Seven at GitHub username"),
    ),
  ),
);

From version 4.6.0, Prisma carries out upserts with database native SQL commands where possible.

Learn more

Find or create

You can use Dart extension to create a findOrCreate method:

extension UserFindOrCreate on UserDelegate {
  ActionClient<User> findOrCreate({
    required UserWhereUniqueInput where,
    required UserCreateInput data,
    // ... More fields can be added here
  }) {
    return upsert(
      where: where,
      create: PrismaUnion.$1(data),
      // Create a empty update input, make sure the update input is not null
      update: PrismaUnion.$2(UserUncheckedUpdateInput()),
    );
  }
}

Now you can use findOrCreate method:

final user = await prisma.user.findOrCreate(
  where: UserWhereUniqueInput(email: "seven@odroe.com"),
  data: UserCreateInput(
    email: "seven@odroe.com",
    name: PrismaUnion.$1("Seven Du"),
  ),
);

WARNING

Prisma does not have a findOrCreate query. You can use upsert as a workaround. To make upsert behave like a findOrCreate method, provide an empty update parameter to upsert.

Update a number field

Use atomic number operations to update a number field based on its current value - for example, increment or multiply. The following query increments the views and likes fields by 1:

final affectedRows = await prisma.post.updateMany(
  data: PrismaUnion.$1(
    PostUpdateManyMutationInput(
      views: PrismaUnion.$2(
        IntFieldUpdateOperationsInput(increment: 1),
      ),
      likes: PrismaUnion.$2(
        IntFieldUpdateOperationsInput(increment: 1),
      ),
    ),
  ),
);

Delete

Delete a single record

The following query uses delete to delete a single User record:

final deletedUser = await prisma.user.delete(
  where: UserWhereUniqueInput(
    email: "bob@prusma.pub",
  ),
);

Attempting to delete a user with one or more posts result in an error, as every Post requires an author - See 👉 Cascading deletes

Delete multiple records

The following query uses deleteMany to delete all Use records where email domain is odroe.com:

final affectedRows = await prisma.user.deleteMany(
  where: UserWhereInput(
    email: PrismaUnion.$1(
      StringFilter(
        endsWith: PrismaUnion.$1('@odroe.com'),
      ),
    ),
  ),
);

Attempting to delete a user with one or more posts result in an error, as every Post requires an author - See 👉 Cascading deletes

Delete all records

The following query uses deleteMany to delete all User records:

final affectedRows = await prisma.user.deleteMany();

Be aware that this query will fail if the user has any related records (such as posts). In this case, you need to delete related records first.

Cascading deletes (deleting related records)

The following query uses delete to delete a single User record:

final deletedUser = await prisma.user.delete(
  where: UserWhereUniqueInput(
    email: "bob@prusma.pub",
  ),
);

However, the example schema includes a required relation between Post and User, which means that you cannot delete a user with posts:

The change you are trying to make would violate the required relation 'PostToUser' between the `Post` and `User` models.

To resolve this error, you can:

1. Make the relation optional:

   model Post {
        id       Int   @id @default(autoincrement())
        author   User? @relation(fields: [authorId], references: [id]) 
        authorId Int?
        author   User  @relation(fields: [authorId], references: [id]) 
        authorId Int
   }

2. Change the author of the posts to another user before deleting the user.
3. Delete a user and all their posts with two separate queries in a transaction (all queries must succeed):

   await prisma.$transaction((prisma) async {
        await prisma.post.deleteMany();
        await prisma.user.deleteMany();
   });

Unserializable query values

Prisma Client Dart supports the following unserializable query values:

final data = await prisma.user.findMany().unserialized();

All models actions support unserialized method.