Connecting many-to-many relation with prisma releases existing connection

Question

Using prisma and nest, I have the following many-to-many relation schema.

model Message {
  id        Int       @id @default(autoincrement())
  message   String
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  tags      Tags[]
}

model Tags {
  id        Int      @id @default(autoincrement())
  tag       String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  Message   Message? @relation(fields: [messageId], references: [id])
  messageId Int?
}

When I create a message and assign the tags, it works fine. Here the part of the create function.

create(createMessageDto: CreateMessageDto) {
    return this.prisma.message.create({
    data: {
        message: createMessageDto.message,
        tags: {
            connectOrCreate: createMessageDto.tag.map((tag) => ({
            where: { tag: tag },
            create: { tag: tag },
            })),
            },
        },
    });
}

This works fine for every single message. But when I use the same tags in different messages, only the latest message are linked to the tags. It seems that this is not a many-to-many relation, but a one-to-many.

Where is the thing I made the mistake?

All the documentation in prima.io and also SO could not help.

Answer 1

In your current Prisma schema, the relation between Message and Tags is not defined as a many-to-many relationship, but a one-to-many relationship. An explicit many-to-many relationship involves an intermediary "join table" that connects the two other tables.

model Message {
  id        Int       @id @default(autoincrement())
  message   String
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  tags      MessageTag[]
}

model Tags {
  id        Int      @id @default(autoincrement())
  tag       String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  messages  MessageTag[]
}

model MessageTag {
  messageId Int
  tagId     Int
  message   Message @relation(fields: [messageId], references: [id])
  tag       Tags    @relation(fields: [tagId], references: [id])

  @@id([messageId, tagId])
}

In this schema, MessageTag is the join table that connects Message and Tags. Each row in MessageTag represents a link between a message and a tag, and a message or tag can be linked to multiple rows.