Many-to-many relations
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 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[]
}await prisma.post.create({
data: {
title: "Types of relations",
tags: { create: [{ name: "dev" }, { name: "prisma" }] },
},
});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
Section titled “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 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[]
}await prisma.post.create({
data: {
title: "Types of relations",
tags: {
create: [{ tag: { create: { name: "dev" } } }, { tag: { create: { name: "prisma" } } }],
},
},
});await prisma.post.findMany({
include: { tags: { include: { tag: true } } },
});To get a cleaner response similar to implicit relations:
const result = posts.map((post) => {
return { ...post, tags: post.tags.map((tag) => tag.tag) };
});Sometimes you need to transition from implicit to explicit relations, for example to add metadata like timestamps to the relation.
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:
bunx prisma migrate dev --name "added explicit relation"pnpm prisma migrate dev --name "added explicit relation"yarn prisma migrate dev --name "added explicit relation"npx prisma migrate dev --name "added explicit relation"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();
});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:
bunx prisma migrate dev --name "removed implicit relation"pnpm prisma migrate dev --name "removed implicit relation"yarn prisma migrate dev --name "removed implicit relation"npx prisma migrate dev --name "removed implicit relation"This will drop the implicit table _PostToUser.