Source URL: https://docs.prisma.io/docs/orm/more/troubleshooting/many-to-many-relations

Many-to-many relations

Learn how to model, query, and convert many-to-many relations with Prisma ORM

Modeling and querying many-to-many relations in relational databases can be challenging. This guide shows how to work with implicit and explicit many-to-many relations, and how to convert between them.

Implicit relations

Implicit many-to-many relations let Prisma ORM handle the relation table internally:

    model Post {
      id    Int    @id @default(autoincrement())
      title String
      tags  Tag[]
    }

    model Tag {
      id    Int    @id @default(autoincrement())
      name  String @unique
      posts Post[]
    }

Creating records

    await prisma.post.create({
      data: {
        title: "Types of relations",
        tags: { create: [{ name: "dev" }, { name: "prisma" }] },
      },
    });

Querying with relations

    await prisma.post.findMany({
      include: { tags: true },
    });

Result:

    [
      {
        "id": 1,
        "title": "Types of relations",
        "tags": [
          { "id": 1, "name": "dev" },
          { "id": 2, "name": "prisma" }
        ]
      }
    ]

Connecting and creating tags simultaneously

    await prisma.post.update({
      where: { id: 1 },
      data: {
        title: "Prisma is awesome!",
        tags: { set: [{ id: 1 }, { id: 2 }], create: { name: "typescript" } },
      },
    });

Explicit relations

Explicit relations are needed when you need to store extra fields in the relation table or when introspecting an existing database:

    model Post {
      id    Int        @id @default(autoincrement())
      title String
      tags  PostTags[]
    }

    model PostTags {
      id     Int   @id @default(autoincrement())
      post   Post? @relation(fields: [postId], references: [id])
      tag    Tag?  @relation(fields: [tagId], references: [id])
      postId Int?
      tagId  Int?

      @@index([postId, tagId])
    }

    model Tag {
      id    Int        @id @default(autoincrement())
      name  String     @unique
      posts PostTags[]
    }

Creating records with explicit relations

    await prisma.post.create({
      data: {
        title: "Types of relations",
        tags: {
          create: [{ tag: { create: { name: "dev" } } }, { tag: { create: { name: "prisma" } } }],
        },
      },
    });

Querying with explicit relations

    await prisma.post.findMany({
      include: { tags: { include: { tag: true } } },
    });

Mapping the response

To get a cleaner response similar to implicit relations:

    const result = posts.map((post) => {
      return { ...post, tags: post.tags.map((tag) => tag.tag) };
    });

Converting implicit to explicit relations

Sometimes you need to transition from implicit to explicit relations, for example to add metadata like timestamps to the relation.

Step 1: Add the explicit relation model

Keep the implicit relation while adding the new model:

    model User {
      id        Int        @id @default(autoincrement())
      name      String
      posts     Post[]
      userPosts UserPost[]
    }

    model Post {
      id        Int        @id @default(autoincrement())
      title     String
      authors   User[]
      userPosts UserPost[]
    }

    model UserPost {
      id        Int       @id @default(autoincrement())
      userId    Int
      postId    Int
      user      User      @relation(fields: [userId], references: [id])
      post      Post      @relation(fields: [postId], references: [id])
      createdAt DateTime  @default(now())

      @@unique([userId, postId])
    }

Run the migration:

npm

pnpm

yarn

bun

    npx prisma migrate dev --name "added explicit relation"

Step 2: Migrate existing data

    import { PrismaClient } from "../prisma/generated/client";

    const prisma = new PrismaClient();

    async function main() {
      const users = await prisma.user.findMany({
        include: { posts: true },
      });

      for (const user of users) {
        for (const post of user.posts) {
          await prisma.userPost.create({
            data: {
              userId: user.id,
              postId: post.id,
            },
          });
        }
      }

      console.log("Data migration completed.");
    }

    main()
      .catch((e) => {
        throw e;
      })
      .finally(async () => {
        await prisma.$disconnect();
      });

Step 3: Remove implicit relation columns

After migrating the data, remove the implicit relation columns:

    model User {
      id        Int        @id @default(autoincrement())
      name      String
      userPosts UserPost[]
    }

    model Post {
      id        Int        @id @default(autoincrement())
      title     String
      userPosts UserPost[]
    }

Run the migration:

npm

pnpm

yarn

bun

    npx prisma migrate dev --name "removed implicit relation"

This will drop the implicit table _PostToUser.