Prisma: Create-if-not-exists multiple explicit many-to-many relations in one query

I have Playlist and Video models, with an explicit many-to-many relationship (to hold extra data). Say in my server code, I have an array of Video primary keys, how do I add these videos to a playlist, if they aren't already in it?

My closest working example is this. To my understanding, upsert is actually a "create-if-not-exists, otherwise update" operation, but with an empty object for update thus working similarly to what I want (albeit slightly more complicated). However, here I am only adding 1 video to the playlist at once. Is there a way to add multiple videos (aka creating multiple many-to-many relations to the join table between Playlist & Video), ideally in one query?

const id = /* Playlist's ID */ const videoUrl = /* Video's Url, which is its primary key */ // I want to use an array of videoUrls instead, not just 1 const updatedPlaylist = await prisma.playlist.update({ where: { id }, include: { videos: { include: { video: true } } }, data: { videos: { upsert: { where: { playlistId_videoUrl: { playlistId: id, videoUrl: videoUrl, }, }, create: { addedAt: new Date(), video: { connect: { url: videoUrl }, }, }, update: {}, }, }, }, }); 

I've tried reading Prisma docs, but there's too many different APIs and I got confused, even regarding where to start the query from (do I update the Playlist model and create multiple join relations? Do I updateMany the Video model and for each also create a join relation? Or do I create the join relation first, then somehow connect them to the Playlist and Video models on both sides?)

2 Answers

I had the same problem once, my solution was deleting all the rows in the join table and then add them again.

 const create = []; data.videos.forEach((video) => { create.push({ video: { connect: { url: videos.url, }, }, }); }); const playlist = await this.prisma.playlist.update({ where: { id: data.id }, data: { ...data, videos: { deleteMany: { playlist_id: id, }, create, }, updated_at: new Date(), }, }); 

in the previous code you can update the extra data in the join table row and remove video from the playlist too.

Prisma Documentation Says it is not Possible

According to the Prisma documentation this is not currently possible.

It leads you through this fun rabbit hole:

The Next Best Thing

I really do not like this option, but I believe the next best thing you could do would be to separate this out into multiple queries.

Setting the Scene

I'm going to go with the idea of Merchants and Transactions where transactions have a merchant, and that merchant may or may not exist yet but is required for the transaction.

Here's an example schema:

model Merchant { id String @id @default(uuid()) name String @unique Transaction Transaction[] } model Transaction { id String @id @default(uuid()) merchantId String merchant Merchant @relation(fields: [merchantId], references: [id]) description String } 

Some Raw Data

Some example data of what we are working with.

const transactions = [ { id: '1', description: 'Transaction 1', merchantName: 'Merchant 1', }, { id: '2', description: 'Transaction 2', merchantName: 'Merchant 2', }, { id: '3', description: 'Transaction 3', merchantName: 'Merchant 1', }, ] 

First Insert Your Relations

// Get a unique collection of merchant names const merchantNames = new Set(transactions.map((transaction) => transaction.merchantName)) // Note this only returns the created records that are not duplicates await prisma.merchants.createMany({ data: merchantNames, skipDuplicates: true // So it does not crash on a duplicate }) 

Collect Relevant Merchants and Create a Fast Lookup

This ends up working out since the merchant names are unique and thus can utilize the name to lookup the ID.

const merchants = await prisma.merchant.findMany({ // Note: use a select here if there's additional data not relevant to this where: { name: { in: merchantNames } }, }) const merchantsTuples = merchants.map((merchant) => [ merchant.name, // This will be the lookup key to the id merchant.id, // This is the relationship id ]) const merchantLookup = new Map(merchantsTuples) 

Create Many Transactions Using Merchant Lookup

const createManyTransactionsData = transactions.map(({ description, merchantName, }) => ({ description, merchant_id: merchantLookup.get(merchantName), })) await prisma.transactions.createMany({ data: createManyTransactionsData, skipDuplicates: true // Important if you're trying to reconcile data that may already exist })