Prisma explicit many to many field already defined

Question

I have comics which has many writers, artists and characters and i'm trying to join everything together in prisma, but struggling to understand what I'm doing wrong.

Comic schema:

model comics {
  uniqueCoverId             String                    @id
  name                      String
  artists                   comic_contributors[]   @relation("artists")
  writers                   comic_contributors[]   @relation("writers")
}

Artists schema

model comic_artists {
  id                       String                    @id @default(uuid())
  name                     String?
  comics                   comic_contributors[]
}

Writers schema

model comic_writers {
  id     String                    @id @default(uuid())
  name   String?
  comics comic_contributors[]
}

Intermediary table

model comic_contributors {
  writer_id String              @unique
  writer    comic_writers? @relation("writers", fields: [writer_id], references: [id])

  artist_id String              @unique
  artist    comic_artists? @relation("artists", fields: [artist_id], references: [id])

  @@id([writer_id, artist_id])
}

When I try to format prisma it returns the errors

error: Field "comics" is already defined on model "comic_contributors".
comicsUniqueCoverId String?
comics              comics?        @relation(fields: [comicsUniqueCoverId], references: [uniqueCoverId])


error: Field "comicsUniqueCoverId" is already defined on model "comic_contributors".
comics              comics?        @relation(fields: [comicsUniqueCoverId], references: [uniqueCoverId])
comicsUniqueCoverId String?

Any suggestions on what I'm doing wrong please?

Answer 1

You don't need to explicit a many-to-many relationship table, Prisma is doing it for you!

Prisma implicit many-to-many

You only have to say:

model comics {
  uniqueCoverId String          @id
  name          String
  artists       comic_artists[]
  writers       comic_writers[]
}

model comic_artists {
  id     String   @id @default(uuid())
  name   String?
  comics comics[]
}

model comic_writers {
  id     String   @id @default(uuid())
  name   String?
  comics comics[]
}

and Prisma will create hidden relationship tables as _comic_artitsTocomics

column A -> comic_artits.id
column B -> comics.uniqueCoverId

_comic_writersTocomics

column A -> comic_writers.id
column B -> comics.uniqueCoverId

This allow you to do

const comic = await prisma.comics.findUnique({
  where: {
    uniqueCoverId: 'UniqueCoverId',
  },
  include: {
    artists: true,
    writers: true,
  },
});

And comic will looks like

{
  uniqueCoverId: 'UniqueCoverId',
  name: 'Name',
  artists: [
    { id: '210059e6-00ab-448c-aea9-b706251ade52', name: 'Artist1' },
    { id: '38cd8efa-2a66-4fe5-ad47-ec4f511647c0', name: 'Artist2' },
    { id: '86d6c908-f17b-4fbd-b0ee-c314f06aeaa9', name: 'Artist3' }
  ],
  writers: [
    { id: '9cf97bf4-9c43-4579-924b-15a7f5dcb3f9', name: 'Writer1' },
    { id: 'afe6eb2d-6d69-44f2-a491-14827dc94e66', name: 'Writer2' },
    { id: 'cdac61cf-339d-460d-960b-6581fa2d7a57', name: 'Writer3' }
  ]
}

Prisma explicit many-to-many

But if you really need an explicit many-to-many relationship to add some data on the relation it will looks like this schema

model comics {
  uniqueCoverId String                  @id
  name          String
  artists       comic_artistsOncomics[]
  writers       comic_writersOncomics[]
}

model comic_artists {
  id     String                  @id @default(uuid())
  name   String?
  comics comic_artistsOncomics[]
}

model comic_artistsOncomics {
  uniqueCoverId String
  comic         comics        @relation(fields: [uniqueCoverId], references: [uniqueCoverId])
  artistId      String
  artist        comic_artists @relation(fields: [artistId], references: [id])
  // Some data

  @@id([uniqueCoverId, artistId])
}

model comic_writers {
  id     String                  @id @default(uuid())
  name   String?
  comics comic_writersOncomics[]
}

model comic_writersOncomics {
  uniqueCoverId String
  comic         comics        @relation(fields: [uniqueCoverId], references: [uniqueCoverId])
  writerId      String
  writer        comic_writers @relation(fields: [writerId], references: [id])
  // Some data

  @@id([uniqueCoverId, writerId])
}

The previous findUnique will now looks like

  const test = await prisma.comics.findUnique({
    where: {
      uniqueCoverId: 'uniqueCoverId',
    },
    include: {
      artists: {
        include: {
          artist: true,
        },
      },
      writers: {
        include: {
          writer: true,
        },
      },
    },
  });

And the result

{
  uniqueCoverId: "UniqueCoverId",
  name: "Name",
  artists: [
    {
      uniqueCoverId: "UniqueCoverId",
      artistId: "210059e6-00ab-448c-aea9-b706251ade52",
      artist: {
        id: "210059e6-00ab-448c-aea9-b706251ade52",
        name: "Artist1",
      },
    },
    {
      uniqueCoverId: "UniqueCoverId",
      artistId: "38cd8efa-2a66-4fe5-ad47-ec4f511647c0",
      artist: {
        id: "38cd8efa-2a66-4fe5-ad47-ec4f511647c0",
        name: "Artist2",
      },
    },
    {
      uniqueCoverId: "UniqueCoverId",
      artistId: "86d6c908-f17b-4fbd-b0ee-c314f06aeaa9",
      artist: {
        id: "86d6c908-f17b-4fbd-b0ee-c314f06aeaa9",
        name: "Artist3",
      },
    },
  ],
  writers: [
    {
      uniqueCoverId: "UniqueCoverId",
      writerId: "9cf97bf4-9c43-4579-924b-15a7f5dcb3f9",
      writer: {
        id: "9cf97bf4-9c43-4579-924b-15a7f5dcb3f9",
        name: "Writer1",
      },
    },
    {
      uniqueCoverId: "UniqueCoverId",
      writerId: "afe6eb2d-6d69-44f2-a491-14827dc94e66",
      writer: {
        id: "afe6eb2d-6d69-44f2-a491-14827dc94e66",
        name: "Writer2",
      },
    },
    {
      uniqueCoverId: "UniqueCoverId",
      writerId: "cdac61cf-339d-460d-960b-6581fa2d7a57",
      writer: {
        id: "cdac61cf-339d-460d-960b-6581fa2d7a57",
        name: "Writer3",
      },
    },
  ],
}

EDIT FOR YOU TO TEST

model comics {
  id            String          @id @default(uuid())
  uniqueCoverId String          @unique
  name          String
  artists       comic_artists[]
  writers       comic_writers[]
}

model comic_artists {
  id     String   @id @default(uuid())
  name   String?
  comics comics[]
}

model comic_writers {
  id     String   @id @default(uuid())
  name   String?
  comics comics[]
}