Many-to-many relations (Prisma ORM v7) (/docs/orm/v7/prisma-schema/data-model/relations/many-to-many-relations)
For the complete Prisma documentation index, see llms.txt. A markdown version of any docs page is available by appending
.mdto its URL.
How to define and work with many-to-many relations in Prisma.
Location: ORM > v7 > Prisma Schema > Data Model > Relations > Many-to-many relations
Many-to-many (m-n) relations connect zero or more records on one side to zero or more on the other. They can be implicit (Prisma manages the relation table) or explicit (you define the relation table).
Relational databases
Section titled “Relational databases”Use implicit m-n unless you need to store additional metadata in the relation table.
Explicit many-to-many relations
Section titled “Explicit many-to-many relations”The relation table is represented as a model in the schema:
model Post {
id Int @id @default(autoincrement())
title String
categories CategoriesOnPosts[]
}
model Category {
id Int @id @default(autoincrement())
name String
posts CategoriesOnPosts[]
}
model CategoriesOnPosts {
post Post @relation(fields: [postId], references: [id])
postId Int
category Category @relation(fields: [categoryId], references: [id])
categoryId Int
assignedAt DateTime @default(now())
assignedBy String
@@id([postId, categoryId])
}The relation table can store additional fields like assignedAt and assignedBy.
Querying explicit many-to-many
Section titled “Querying explicit many-to-many”// Create post with new category
const post = await prisma.post.create({
data: {
title: "How to be Bob",
categories: {
create: [
{
assignedBy: "Bob",
category: { create: { name: "New category" } },
},
],
},
},
});
// Connect to existing categories
await prisma.post.create({
data: {
title: "My Post",
categories: {
create: [
{ assignedBy: "Bob", category: { connect: { id: 9 } } },
{ assignedBy: "Bob", category: { connect: { id: 22 } } },
],
},
},
});
// Query posts by category
const posts = await prisma.post.findMany({
where: { categories: { some: { category: { name: "New Category" } } } },
});Implicit many-to-many relations
Section titled “Implicit many-to-many relations”Prisma manages the relation table automatically:
model Post {
id Int @id @default(autoincrement())
title String
categories Category[]
}
model Category {
id Int @id @default(autoincrement())
name String
posts Post[]
}Querying implicit many-to-many
Section titled “Querying implicit many-to-many”// Create post with categories
const post = await prisma.post.create({
data: {
title: "How to become a butterfly",
categories: {
create: [{ name: "Magic" }, { name: "Butterflies" }],
},
},
});
// Get posts with categories
const posts = await prisma.post.findMany({
include: { categories: true },
});Rules for implicit m-n
Section titled “Rules for implicit m-n”- Both models must have a single
@id(no composite IDs or@unique) - No
@relationattribute needed (unless disambiguating) - Cannot use
fields,references,onUpdate, oronDeletein@relation
Relation table conventions
Section titled “Relation table conventions”For prisma db pull to recognize implicit m-n tables:
- Table name:
_CategoryToPost(underscore + model names alphabetically +To) - Columns:
A(FK to first model alphabetically) andB(FK to second) - Unique index on both columns, non-unique index on
B
Configuring relation table name
Section titled “Configuring relation table name”Use @relation("MyRelationTable") on both sides to customize the table name.
MongoDB
Section titled “MongoDB”MongoDB requires explicit ID arrays on both sides:
model Post {
id String @id @default(auto()) @map("_id") @db.ObjectId
categoryIDs String[] @db.ObjectId
categories Category[] @relation(fields: [categoryIDs], references: [id])
}
model Category {
id String @id @default(auto()) @map("_id") @db.ObjectId
name String
postIDs String[] @db.ObjectId
posts Post[] @relation(fields: [postIDs], references: [id])
}Querying MongoDB m-n
Section titled “Querying MongoDB m-n”// Find posts by category IDs
const posts = await prisma.post.findMany({
where: { categoryIDs: { hasSome: [id1, id2] } },
});
// Find posts by category name
const posts = await prisma.post.findMany({
where: { categories: { some: { name: { contains: "Servers" } } } },
});Related pages
Section titled “Related pages”One-to-many relations: How to define and work with one-to-many relations in Prisma.One-to-one relations: How to define and work with one-to-one relations in Prisma.Referential actions: Referential actions let you define the update and delete behavior of related models on the database levelRelation mode: Manage relations between records with relation modes in PrismaSelf-relations: How to define and work with self-relations in Prisma.