ManyToMany inserts in one step

[fimbault]

Problem

Many to many relationships work, but usually require a two step process. This creates potentially error prone operations, where one part could fail, making rollbacks complex.

Example to illustrate what we do today

In the following example, we have users and teams. Each user can belong to M teams and each team can include N users.

A user can create a new team (we provide a unique name and the id of the user that creates it):
const result = await prisma.team.create({ data: { name, owner_id, } });

Then we can update the _TeamToUser joint table with an update, since we have the id from the previous insert.
const many_to_many = await prisma.team.update({ data: { user_id: { connect: { id: owner_id } } }, where: { id: result.id } })

Suggested solution

As described, the reason that we need a two step process is to get the ID of the new team, before we can update the joint table.
Not sure it would be possible, but ideally it would be great to provide an API that deals with errors automatically.

Here we would have a single create operation, using a unique name as a constraint (additional hypothesis)
const result = await prisma.team.create({ data: { name, owner_id, user_id: { connect: { id: owner_id } } }, where: { name: name } });

Currently this fails with a 500 internal error.

What we think would be interesting:

• encapsulate the logic of creating a row and mapping the joint between the 2 tables
• if it fails (for instance if the name is not unique), rollback to previous state

Viewed from a user of prisma, it would appear as a single operation.

Alternatives

Suggest a clear way to deal with errors when operations need to be chained.

[pantharshit00]

Hey @fimbault

I am not sure if I understand you problem correctly, but can you please explain why you can't just do the following query:

const data = await prisma.team.create({
  data:{
    name: "Sample Team",
    users:{
      connect:{
        id: owner_id
      }
    }
  }
})

This will create a new team and write the connect it to new owner. If the owner team is not unique, it will throw an error with P2002 error code(https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-client/error-reference#error-codes). It will also not write the nested part because this whole query is wrapped inside of a database transaction in Prisma.

[fimbault]

Seems nice, will test and let you know.

[fimbault]

Seems to work fine, thanks.

[laniltee]

Check if this helps
Modeling and querying many-to-many relations