Modeling and querying many-to-many relations

Problem

Modeling and querying many-to-many relations in relational databases can be challenging. This article shows two examples how this can be approached with Prisma ORM. The first example uses an implicit and the second one uses an explicit many-to-many relation.

Solution

Implicit relations

This is a type of many-to-many relation where Prisma ORM handles the relation table internally. A basic example for an implicit many-to-many relation would look like this:

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

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

To create a post and its tags, one can write this with Prisma Client:

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

In the above example, we can directly query for posts along with their tags as follows:

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

And the response obtained would be:

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

Another use case for this is if you want to add new tags as well as connect to existing tags to a post. An example for this is where a user has created new tags for their post and has also selected existing tags to be added as well. In this case, we can perform this in the following way:

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 mostly need to be created in cases where you need to store extra fields in the relation table or if you're introspecting an existing database that already has many-to-many relations setup. This is the same schema used above but with an explicit relation table:

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[]
}

Adding tags to a post would be a create into the relation table (PostTags) as well as into the tags table (Tag):

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

Also querying for posts along with their tags would require an extra include as follows:

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

This will provide the following output:

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

Sometimes, it's not ideal to show the data for the relation table in your UI. In this case, it's best to map the data after fetching it on the server itself and sending that response to the frontend.

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

This will provide an output similar to the one you received with implicit relations.

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

This article showed how you can implement implicit and explicit many-to-many relations and query them using Prisma Client.