prisma update two items, switch around unique constraint on index

Question

with the following model I want to switch around the index of two different tracks

model Track {
    index  Int
    trackId String @map("track_id") @db.VarChar(22)

    room Room @relation(fields: [roomId], references: [pin], onDelete: Cascade)
    roomId String @map("room_id")

    @@id([roomId, trackId])
    @@unique([roomId, index])
    @@map("tracks")
}

I tried to update the tracks in a transaction like

await this.db.$transaction(async (transaction) => {
  const promises = sorted.map(({ trackId }, index) => {
    return transaction.track.update({
      where: { roomId_trackId: { roomId: pin, trackId } },
      data: { index },
    });
  });

  await Promise.all(promises);
});

But I get the following error Unique constraint failed on the fields: ('room_id', 'index')

Edit:

I also tried to update all the indexes in a single transaction like

const update = sorted
  .map(({ trackId, index }, newIndex) => {
    const updateIndexTo =
       room.currentIndex - unshiftCurrentIndex + newIndex + 1;
       if (index === updateIndexTo) return; // No need to update

       return {
         where: { roomId_trackId: { roomId: pin, trackId } },
         data: { index: updateIndexTo },
     };
})
.filter(Boolean);

await transaction.room.update({
  where: { pin },
  data: { tracks: { update } },
});

Answer 1

In the meantime for people who also struggle with a similar issue, I found a workaround using two transactions.

const update = sorted
.map(({ trackId, index }, newIndex) => {
  const updateIndexTo =
    room.currentIndex - unshiftCurrentIndex + newIndex + 1;
  if (index === updateIndexTo) return; // No need to update

  return {
    where: { pin_trackId: { pin, trackId } },
    data: { index: updateIndexTo },
  };
})
.filter(Boolean);

const updateToHighIndex = update.map((x, index) => ({
  ...x,
  data: {
    ...x.data,
    index: maxCurrentIndex + index,
  },
}));

// (1) Clear out the indexes
await transaction.room.update({
  where: { pin },
  data: { tracks: { update: updateToHighIndex } },
});

// (2) Set the correct indexes
await transaction.room.update({
  where: { pin },
  data: { tracks: { update } },
});

Ps. I guess this should also be possible using the raw database access