Prisma•6mo ago

Best Practices for Error Handling When Updating Bridge Table (Composite IDs)

What are the best practices when it comes to going about error handling for a function that is updating a bridge table? Is it necessary to verify to check if the two records that comprises its composite ID (e.g., facilityUuid for the Facility model and workerUuid for the Worker model) before updating it? Or will it check for that on its own as a consequential result? Here's are some snippet examples:

export function updateFacilityWorker(
  facilityUuid: FacilityWorker["facilityUuid"],
  workerUuid: Worker["uuid"],
  data: Omit<Prisma.FacilityWorkerUpdateInput, TimestampFields>
): Promise<UpdatedFacilityWorker> {
  return prisma.facilityWorker.update({
    select: {
      facilityUuid: true,
      workerUuid: true,
      rating: true,
      status: true,
    },
    where: {
      facility_worker_id: { facilityUuid, workerUuid },
    },
    data,
  });
}

export function updateFacilityWorker(
  facilityUuid: FacilityWorker["facilityUuid"],
  workerUuid: Worker["uuid"],
  data: Omit<Prisma.FacilityWorkerUpdateInput, TimestampFields>
): Promise<UpdatedFacilityWorker> {
  return prisma.facilityWorker.update({
    select: {
      facilityUuid: true,
      workerUuid: true,
      rating: true,
      status: true,
    },
    where: {
      facility_worker_id: { facilityUuid, workerUuid },
    },
    data,
  });
}

model FacilityWorker {
    facilityUuid String               @db.Uuid
    facility     HealthCareFacility   @relation(fields: [facilityUuid], references: [uuid], onDelete: Cascade)

    workerUuid   String               @db.Uuid
    worker       Worker               @relation(fields: [workerUuid], references: [uuid], onDelete: Cascade)

    rating       Int?                 @db.SmallInt
    status       FacilityWorkerStatus @default(Available)

    @@id(fields: [facilityUuid, workerUuid], name: "facility_worker_id")
    @@index(fields: [facilityUuid, workerUuid, status], name: "worker_status_at_facility")
}

model FacilityWorker {
    facilityUuid String               @db.Uuid
    facility     HealthCareFacility   @relation(fields: [facilityUuid], references: [uuid], onDelete: Cascade)

    workerUuid   String               @db.Uuid
    worker       Worker               @relation(fields: [workerUuid], references: [uuid], onDelete: Cascade)

    rating       Int?                 @db.SmallInt
    status       FacilityWorkerStatus @default(Available)

    @@id(fields: [facilityUuid, workerUuid], name: "facility_worker_id")
    @@index(fields: [facilityUuid, workerUuid, status], name: "worker_status_at_facility")
}

10 Replies

Olyno•6mo ago

Hi :vmathi: Tt is necessary to verify that the two records comprising its composite ID exist before updating the bridge table. This is to ensure data integrity and to avoid potential errors during the update process. You can do it using something like that:

export async function updateFacilityWorker(
  facilityUuid: FacilityWorker["facilityUuid"],
  workerUuid: Worker["uuid"],
  data: Omit<Prisma.FacilityWorkerUpdateInput, TimestampFields>
): Promise<UpdatedFacilityWorker> {
  // Check if the facility and worker exist
  const facilityExists = await prisma.facility.findUnique({
    where: { uuid: facilityUuid },
  });

  const workerExists = await prisma.worker.findUnique({
    where: { uuid: workerUuid },
  });

  if (!facilityExists) {
    throw new Error(`Facility with UUID ${facilityUuid} does not exist.`);
  }

  if (!workerExists) {
    throw new Error(`Worker with UUID ${workerUuid} does not exist.`);
  }

  // Proceed with the update inside a transaction
  return prisma.$transaction(async (prisma) => {
    return prisma.facilityWorker.update({
      select: {
        facilityUuid: true,
        workerUuid: true,
        rating: true,
        status: true,
      },
      where: {
        facility_worker_id: { facilityUuid, workerUuid },
      },
      data,
    });
  });
}

export async function updateFacilityWorker(
  facilityUuid: FacilityWorker["facilityUuid"],
  workerUuid: Worker["uuid"],
  data: Omit<Prisma.FacilityWorkerUpdateInput, TimestampFields>
): Promise<UpdatedFacilityWorker> {
  // Check if the facility and worker exist
  const facilityExists = await prisma.facility.findUnique({
    where: { uuid: facilityUuid },
  });

  const workerExists = await prisma.worker.findUnique({
    where: { uuid: workerUuid },
  });

  if (!facilityExists) {
    throw new Error(`Facility with UUID ${facilityUuid} does not exist.`);
  }

  if (!workerExists) {
    throw new Error(`Worker with UUID ${workerUuid} does not exist.`);
  }

  // Proceed with the update inside a transaction
  return prisma.$transaction(async (prisma) => {
    return prisma.facilityWorker.update({
      select: {
        facilityUuid: true,
        workerUuid: true,
        rating: true,
        status: true,
      },
      where: {
        facility_worker_id: { facilityUuid, workerUuid },
      },
      data,
    });
  });
}

KinglyEndeavorsOP•6mo ago

Thanks! I figured that may have been the case. but I wasn't 100%. I'm brand new to Prisma (and ORMs in general), and a part of me was thinking that Prisma would already have something like that built in or something. That's not a knock against Prisma or anything; I'm assuming it's just something under-the-hood I'm not aware of. . Also, I understand what $transaction does and what it is for, but why is it necessary within your code sample? Wouldn't the first two checks render that transaction check unneeded? I thought it was just for use cases where you need to upsert multiple models/entities. This isn't me questioning you. This is just me as a Prisma-noob who's seeking to better understand the $transaction feature in this particular use case.

Olyno•6mo ago

Wrapping the update in a transaction can ensure that all operations within the transaction scope either complete successfully or roll back together, which is crucial for maintaining database consistency, especially if the update operation depends on multiple steps or includes additional related changes. Using a transaction ensures atomicity. If any part of the update process fails, the transaction will roll back, leaving the database in a consistent state. This is especially useful if there are multiple dependent operations or if future modifications might require additional steps within the update process.

KinglyEndeavorsOP•6mo ago

I guess the part I'm lost on is the fact that the first two first are both read operations that don't update anything that would both throw an error, terminating the flow of operations before reading the actual write operations. Do the read/select queries effect the database in anyway that I'm not considering? Or is it more of a just-in-case anything goes wrong with the actual update/write operation itself?

Olyno•6mo ago

This is mainly something goes wrong, they act as safeguard

KinglyEndeavorsOP•6mo ago

Gotcha. Thanks. 🙂 @Olyno Is there a reason for it to be returning an array? It seems to require an array of promises and not just a single promise. Oh, wait. I see. It was released with 4.7.0 and this project is still using ^4.4.0. @Olyno Last question: Do I need to confirm the existence of the FacilityWorker entity/record as well before attempting to update it? Or is that handled under the hood?

Olyno•6mo ago

It should be handled, but you can give it a try to be sure

KinglyEndeavorsOP•6mo ago

Would it throw an error or will it just return nil?

Olyno•6mo ago

It should throw an error

KinglyEndeavorsOP•6mo ago

Looking over the documentation more closely, it does appear to throw an error. Thank you for all your help, nevertheless! I appear to be good now, so again, thank you! 🙂

Gaming

Programming

Best Practices for Error Handling When Updating Bridge Table (Composite IDs)