Update multiple rows using Prisma without manual loops

Question

I have following prisma.schema:

model Tag {
  id    Int       @id @default(autoincrement())
  name  String    @unique
  files FileTag[]
}

model FileTag {
  tag    Tag  @relation(fields: [tagId], references: [id], onDelete: Cascade)
  tagId  Int
  file   File @relation(fields: [fileId], references: [id], onDelete: Cascade)
  fileId Int

  @@id([fileId, tagId])
}

Here is my code to update database:

for (const tagId of tagIds){
    const tag = await Tags.addFileToTag(parseInt(tagId), fileId);
};

async addFileToTag(tagId: number, fileId: number) {
    const client = await getDbClient();

    return await client.tag.update({
      where: {
        id: tagId,
      },

      data: {
        files: {
          create: {
            fileId
          }
        }
      }
    })
  }

My goal is reached by this implementation. But the problem is, I don't like this implementation. I am using a loop and calling same update query repeatedly.

Now, I am wondering is there any alternative procedure, (i.e change prisma update to updateMany query) by removing the loop... that will do the same change on database?

score 10 · Answer 1 · answered Mar 17 '22 at 08:21

updateMany is used to update the same data in many rows but in your case, you want to update different data, so updateMany won't be useful here.

Optionally you could use transactions if atomicity is required and there is a need to make sure that either all tags are updated or if there is any error then no tags gets updated.

score 0 · Answer 2 · answered Jan 31 '23 at 22:57

I had a similar issue using Prisma in a NestJs project, and I'm leaving my solution here that may be helpful to someone someday.

I had something like that:

model Job {
  id                  Int @id @default(autoincrement())
  authorId            Int
  position            String
  type                String
  description         String
  requirement         String
  requirementItems    RequirementItem[] 
  task                String
  location            String
  company             String
  companyWebsite      String  
  createdAt           DateTime @default(now())
  updatedAt           DateTime @updatedAt
  author              User @relation(fields: [authorId], references: [id], onDelete: Cascade)
  application         Application[]

}

model RequirementItem {
  jobId     Int
  item      String
  job       Job     @relation(fields: [jobId], references: [id], onDelete: Cascade)
}

I had to update the Job model and also the RequirementItem with new values coming from the frontend.

I'm not sure my solution is the best one, but I deleted all the items in RequirementItem with the same jobId and created them with new values in the same query as below:

async editJob(jobId: number, job: CreateJobDto, userId: number) {
    const publishedJob = await this.prisma.job.findUnique({
      where: {
        id: jobId,
      },
    });

    if (!publishedJob) {
      throw new NotFoundException('There is no job with this id');
    }

    if (publishedJob.authorId !== userId) {
      throw new ForbiddenException(
        "You cannot edit a job that you didn't published",
      );
    }

    const { requirementItems, taskItems } = job;

    return await this.prisma.job.update({
      where: { id: jobId },
      data: {
        ...job,
        requirementItems: {
          // deleted all records
          deleteMany: {
            jobId: jobId,
          },
          // created new records
          create: requirementItems,
        },
      },
    });
  }

Update multiple rows using Prisma without manual loops

2 Answers2

Linked