How can I represent extra relation columns in a Prisma schema?-CodePudding

I'm using Prisma 3.7.0 and Postgresql 13. I need to create a many-to-many relation between two models that has some additional data, but I don't know how to do this in a Prisma schema. The fields I want to add are not appropriately added to either table.

Here's some simplified (contrived) SQL to illustrate the goal:

It's pretty straight-forward in SQL, but I don't quite understand from the Prisma docs how to do it.

CREATE TABLE animal (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL -- e.g. "tiger" or "armadillo"
)

CREATE TABLE animal_bodyparts (
  animal   INT,
  bodypart INT,

  -- I want to add these two fields to the relation.
  count    INT,     -- How many of this bodypart the animal has
  is_vital BOOLEAN, -- Does the creature die if this part is damaged?

  PRIMARY KEY (animal, bodypart)
  CONSTRAINT fk_animal   FOREIGN KEY (animal)   REFERENCES animal(id),
  CONSTRAINT fk_bodypart FOREIGN KEY (bodypart) REFERENCES bodypart(id)
)

CREATE TABLE bodypart (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL -- e.g. "head" or "leg"
)

And here's the best I could figure out with Prisma so far:

model Animal {
  id Int @id @default(autoincrement)
  name String // e.g. "tiger" or "armadillo"
  parts BodyPart[]
}

model BodyPart {
  id Int @id @default(autoincrement)
  name String // e.g. "head" or "leg"
  animals Animal[]
}

But where do I put the count and is_vital columns in the Prisma models? I see in a one-to-many relation there's some way to add some info through the @relation tag, but I don't think it addresses this need.

CodePudding user response：

You can do it as follow:

datasource mysql {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

generator erd {
  provider = "prisma-erd-generator"
  output = "entity-relationship-diagram.svg"
}

model Animal {
  id Int @id @default(autoincrement())
  name String
  animalAndBodyParts AnimalAndBodyParts[] @relation()
}

model AnimalAndBodyParts {
  id Int @id @default(autoincrement())
  count Int
  isVital Boolean @map("is_vital")
  animal Animal @relation(fields: [animalId], references: [id])
  animalId Int
  bodyPart BodyPart @relation(fields: [bodyPartId], references: [id])
  bodyPartId Int
}

model BodyPart {
  id Int @id @default(autoincrement())
  name String
  animalAndBodyParts AnimalAndBodyParts[] @relation()
}

It will generate a database as show the image:

You can save data in this way:

const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()

const saveData = async () => {
  const animal = await prisma.animal.create({
    data: {
      name: 'Lion',
      animalAndBodyParts: {
        create: {
          count: 2,
          isVital: true,
          bodyPart: {
            create: {
              name: 'Eye',
            },
          },
        },
      },
    },
    include: {
      animalAndBodyParts: {
        include: {
          bodyPart: true,
        },
      },
    },
  })

  console.log(JSON.stringify(animal, null, 2));
}

saveData()

And the data will look like in the image down below