Prisma Question (Many-to-Many)

Get Help and Help Others Solutions
1

Hope you guys don’t mind as I asking Prisma question here.
Is there a way to directly add data to the bridge table?

My schema:

model role {
  id          Int         @id @default(autoincrement())
  displayName String      @db.VarChar(100)
  users       userRoles[]
  createdAt   DateTime    @default(now())
  createdBy   Int?
  creator     user?       @relation("RoleCreator", fields: [createdBy], references: [id])
  updatedAt   DateTime    @updatedAt
  updatedBy   Int?
  updater     user?       @relation("RoleUpdater", fields: [updatedBy], references: [id])
}

model user {
  id                  Int         @id @default(autoincrement())
  email               String      @unique
  roles               userRoles[]
  hashedPassword      String
  salt                String
  resetToken          String?
  resetTokenExpiresAt DateTime?
  createdAt           DateTime    @default(now())
  createdBy           Int?
  creator             user?       @relation("Creator", fields: [createdBy], references: [id])
  updatedAt           DateTime    @updatedAt
  updatedBy           Int?
  updater             user?       @relation("Updater", fields: [updatedBy], references: [id])
  roleCreator         role[]      @relation("RoleCreator")
  roleUpdater         role[]      @relation("RoleUpdater")
  userCreator         user[]      @relation("Creator")
  userUpdater         user[]      @relation("Updater")
  userRoles           userRoles[] @relation("RoleAssigner")
}

model userRoles {
  role         role     @relation(fields: [roleId], references: [id])
  roleId       Int
  user         user     @relation(fields: [userId], references: [id])
  userId       Int
  assignedAt   DateTime @default(now())
  assignedBy   Int
  roleAssigner user     @relation("RoleAssigner", fields: [assignedBy], references: [id])

  @@id([roleId, userId])
}

Currently, there are two rows in the role table, when I create a new user I want to assign roles to them.

My code:

db.user.create({
        data: {
          email: username,
          hashedPassword: hashedPassword,
          salt: salt,
          userRoles: {
            create: [{
              roleId: 1, 
            }]
          }
        },
      })

It is not working, having some typing issues. Any help would be great, thanks.

2 
return await db.$transaction(async (prisma) => {
        const user = await prisma.user.create({
          data: {
            email: username,
            hashedPassword: hashedPassword,
            salt: salt,
          },
        })

        const customerRole = await prisma.role.findUnique({where: {id: 2}})

       await prisma.userRoles.create({
          data: {
            role: {connect: {id: customerRole.id}},
            user: {connect: {id: user.id }}
          }
        })

        return user
      })
    },

This is working but I not sure is there any other way to do it.

3

Yeah Prisma is weird when it comes to many-to-many relationships. Did you look through the explicit many-to-many docs? According to those, you can connect to an existing role by doubly-referencing the name of the other table you actually want to join to? (I haven’t tried this, I’m just extrapolating from their docs):

db.user.create({
  data: {
    email: username,
    hashedPassword: hashedPassword,
    salt: salt,
    roles: {
      create: [
        role: {
          connect: {
            id: 1
          }
        }
      ]
    }
  },
})

If you wanted to set some data on that join table at the same time (like assignedBy) you just add that in under create apparently?

db.user.create({
  data: {
    email: username,
    hashedPassword: hashedPassword,
    salt: salt,
    roles: {
      create: [
        assignedBy: 123,
        role: {
          connect: {
            id: 1
          }
        }
      ]
    }
  },
})

Bonkers. I have to look up Prisma syntax each time because a lot of it is so unintuitive. :frowning:

2 Likes
4

Work like a charm, thanks Rob. Missed out on this part, will go through the docs once again. :sweat_smile:

1 Like