Many-to-many relations
Many-to-many (m-n) relations refer to relations where zero or more records on one side of the relation can be connected to zero or more records on the other side.
Prisma schema syntax and the implementation in the underlying database differs between relational databases and MongoDB.
Relational databases
In relational databases, m-n-relations are typically modelled via relation tables. m-n-relations can be either explicit or implicit in the Prisma schema.
Explicit many-to-many relations
In an explicit many-to-many relation, the relation table is represented as a model in the Prisma schema and can be used in queries. Explicit many-to-many relations define three models:
- Two models that have a many-to-many relation, such as
Category
andPost
- One model that represents the relation table, such as
CategoriesOnPosts
(also sometimes called JOIN, link or pivot table) in the underlying database
In this example, the model representing the relation table defines additional fields that describe the Post
/Category
relationship - who assigned the category (assignedBy
), and when the category was assigned (assignedAt
):
model Post {id Int @id @default(autoincrement())title Stringcategories CategoriesOnPosts[]}model Category {id Int @id @default(autoincrement())name Stringposts CategoriesOnPosts[]}model CategoriesOnPosts {post Post @relation(fields: [postId], references: [id])postId Int // relation scalar field (used in the `@relation` attribute above)category Category @relation(fields: [categoryId], references: [id])categoryId Int // relation scalar field (used in the `@relation` attribute above)assignedAt DateTime @default(now())assignedBy String@@id([postId, categoryId])}
The underlying SQL looks like this:
CREATE TABLE "Post" ("id" SERIAL NOT NULL,"title" TEXT NOT NULL,CONSTRAINT "Post_pkey" PRIMARY KEY ("id"));CREATE TABLE "Category" ("id" SERIAL NOT NULL,"name" TEXT NOT NULL,CONSTRAINT "Category_pkey" PRIMARY KEY ("id"));-- Relation table + indexes --CREATE TABLE "CategoriesOnPosts" ("postId" INTEGER NOT NULL,"categoryId" INTEGER NOT NULL,"assignedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,"assignedBy" TEXT NOT NULL,CONSTRAINT "CategoriesOnPosts_pkey" PRIMARY KEY ("postId","categoryId"));ALTER TABLE "CategoriesOnPosts" ADD CONSTRAINT "CategoriesOnPosts_postId_fkey" FOREIGN KEY ("postId") REFERENCES "Post"("id") ON DELETE RESTRICT ON UPDATE CASCADE;ALTER TABLE "CategoriesOnPosts" ADD CONSTRAINT "CategoriesOnPosts_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
Querying an explicit many-to-many
The following section demonstrates how to query an explicit many-to-many relation. You can query the relation model directly (prisma.categoriesOnPosts(...)
), or use nested queries to go from Post
-> CategoriesOnPosts
-> Category
or the other way.
The following query:
- Creates a
Post
- Creates a category assigment, or
CategoriesOnPosts
(assigned by Bob, assigned today) - Creates a new
Category
const createCategory = await prisma.post.create({data: {title: 'How to be Bob',categories: {create: [{assignedBy: 'Bob',assignedAt: new Date(),category: {create: {name: 'New category',},},},],},},})
The following query:
- Creates a new
Post
- Creates a new category assignment, or
CategoriesOnPosts
- Connects the category assignment to existing categories (with IDs
9
and22
)
const assignCategories = await prisma.post.create({data: {title: 'How to be Bob',categories: {create: [{assignedBy: 'Bob',assignedAt: new Date(),category: {connect: {id: 9,},},},{assignedBy: 'Bob',assignedAt: new Date(),category: {connect: {id: 22,},},},],},},})
The following query returns all Post
records where at least one (some
) category assignment (categories
) refers to a category named "New category"
:
const getPosts = await prisma.post.findMany({where: {categories: {some: {category: {name: 'New Category',},},},},})
The following query returns all categories where at least one (some
) related Post
record titles contain the words "Cool stuff"
and the category was assigned by Bob.
const getAssignments = await prisma.category.findMany({where: {posts: {some: {assignedBy: 'Bob',post: {title: {contains: 'Cool stuff',},},},},},})
The following query gets all category assignments (CategoriesOnPosts
) records that were assigned by "Bob"
to one of 5 posts:
const getAssignments = await prisma.categoriesOnPosts.findMany({where: {assignedBy: 'Bob',post: {id: {in: [9, 4, 10, 12, 22],},},},})
Implicit many-to-many relations
Implicit many-to-many relations define relation fields as lists on both sides of the relation. Although the relation table exists in the underlying database, it is managed by Prisma and does not manifest in the Prisma schema. Implicit relation tables follow a specific convention.
Implicit m-n relations makes the Prisma Client API for many-to-many relations a bit simpler (since you have one fewer level of nesting inside of nested writes).
In the example below, there's one implicit m-n-relation between Post
and Category
:
model Post {id Int @id @default(autoincrement())title Stringcategories Category[]}model Category {id Int @id @default(autoincrement())name Stringposts Post[]}
Querying an implicit many-to-many
The following section demonstrates how to query an implicit many-to-many relation. The queries require less nesting than explicit many-to-many queries.
The following query creates a single Post
and multiple Category
records:
const createPostAndCategory = await prisma.post.create({data: {title: 'How to become a butterfly',categories: {create: [{ name: 'Magic' }, { name: 'Butterflies' }],},},})
The following query creates a single Category
and multiple Post
records:
const createCategoryAndPosts = await prisma.category.create({data: {name: 'Stories',posts: {create: [{ title: 'That one time with the stuff' },{ title: 'The story of planet Earth' },],},},})
The following query returns all Post
records with a list of that post's assigned categories:
const getPostsAndCategories = await prisma.post.findMany({include: {categories: true,},})
Rules for defining an implicit m-n relation
Implicit m-n relations:
Use a specific convention for relation tables
Do not require the
@relation
attribute unless you need to disambiguate relations with a name, e.g.@relation("MyRelation")
or@relation(name: "MyRelation")
.If you do use the
@relation
attribute, you cannot use thereferences
,fields
,onUpdate
oronDelete
arguments. This is because these take a fixed value for implicit m-n relations and cannot be changed.Require both models to have a single
@id
. Be aware that:- You cannot use a multi-field ID
- You cannot use a
@unique
in place of an@id
To use either of these features, you must use an explicit many-to-many instead.
Conventions for relation tables in implicit m-n relations
If you obtain your data model from introspection, you can still use implicit many-to-many relations by following Prisma's conventions for relation tables.
The following example assumes you want to create a relation table to get an implicit many-to-many relation for two models called Post
and Category
.
Table names
If you want a relation table to be picked up by introspection as an implicit many-to-many relation, the name must follow this exact structure:
- It must start with an underscore
_
- Then the name of the first table in alphabetical order (in this case
Category
) - Then
To
- Then the name of the second table in alphabetical order (in this case
Post
)
In the example, the correct table name is _CategoryToPost
.
When creating an implicit many-to-many relation yourself in the Prisma schema file, you can configure the relation to have a different name. This will change the name given to the relation table in the database. For example, for a relation named "MyRelation"
the corresponding table will be called _MyRelation
.
Columns
A relation table for an implicit-many-to-many relation must have exactly two columns:
- A foreign key column that points to
Category
calledA
- A foreign key column that points to
Post
calledB
The columns must be called A
and B
where A
points to the model that comes first in the alphabet and B
points to the model which comes last in the alphabet.
Indexes
There further must be:
A unique index defined on both foreign key columns:
CREATE UNIQUE INDEX "_CategoryToPost_AB_unique" ON "_CategoryToPost"("A" int4_ops,"B" int4_ops);A non-unique index defined on B:
CREATE INDEX "_CategoryToPost_B_index" ON "_CategoryToPost"("B" int4_ops);
Example
This is a sample SQL statement that would create the three tables including indexes (in PostgreSQL dialect) that are picked up as a implicit many-to-many relation by Prisma Introspection:
CREATE TABLE "_CategoryToPost" ("A" integer NOT NULL REFERENCES "Category"(id) ,"B" integer NOT NULL REFERENCES "Post"(id));CREATE UNIQUE INDEX "_CategoryToPost_AB_unique" ON "_CategoryToPost"("A" int4_ops,"B" int4_ops);CREATE INDEX "_CategoryToPost_B_index" ON "_CategoryToPost"("B" int4_ops);CREATE TABLE "Category" (id integer SERIAL PRIMARY KEY);CREATE TABLE "Post" (id integer SERIAL PRIMARY KEY);
Configuring the name of the relation table in implicit many-to-many relations
When using Prisma Migrate, you can configure the name of the relation table that's managed by Prisma using the @relation
attribute. For example, if you want the relation table to be called _MyRelationTable
instead of the default name _CategoryToPost
, you can specify it as follows:
model Post {id Int @id @default(autoincrement())categories Category[] @relation("MyRelationTable")}model Category {id Int @id @default(autoincrement())posts Post[] @relation("MyRelationTable")}
Relation tables
A relation table (also sometimes called JOIN, link or pivot table) connects two or more other tables and therefore creates a relation between them. Creating relation tables is a common data modeling practice in SQL to represent relationships between different entities. In essence it means that "one m-n relation is modeled as two 1-n relations in the database".
When using Prisma, you can create relation tables by defining models similar to how you would model them as tables. The main difference is that the fields of the relation table are both annotated relation fields with a corresponding relation scalar field.
Relation tables are also often used to add "meta-information" to a relation. For example, to store when the relation was created.
Here is an example for a relation table called CategoriesOnPosts
:
model Post {id Int @id @default(autoincrement())title Stringcategories CategoriesOnPosts[]}model Category {id Int @id @default(autoincrement())name Stringposts CategoriesOnPosts[]}model CategoriesOnPosts {post Post @relation(fields: [postId], references: [id])postId Intcategory Category @relation(fields: [categoryId], references: [id])categoryId IntassignedAt DateTime @default(now())@@id([postId, categoryId])}
In this example, the assignedAt
field adds additional information about the relation between Post
and Category
: it specifies when the post was assigned to the category.
Note that the same rules as for 1-n-relations apply (because Post
↔ CategoriesOnPosts
and Category
↔ CategoriesOnPosts
are both in fact 1-n-relations), which means one side of the relation needs to be annotated with the @relation
attribute.
When you don't need to attach additional information to the relation, you can model m-n-relations as implicit many-to-many relations. If you're not using Prisma Migrate but obtain your data model from introspection, you can still make use of implicit many-to-many relations by following Prisma's conventions for relation tables.
MongoDB
In MongoDB, many-to-many relations are represented by:
- relation fields on both sides, that each have a
@relation
attribute, with mandatoryfields
andreferences
arguments - a scalar list of referenced IDs on each side, with a type that matches the ID field on the other side
The following example demonstrates a many-to-many relation between posts and categories:
model Post {id String @id @default(auto()) @map("_id") @db.ObjectIdcategoryIDs String[] @db.ObjectIdcategories Category[] @relation(fields: [categoryIDs], references: [id])}model Category {id String @id @default(auto()) @map("_id") @db.ObjectIdname StringpostIDs String[] @db.ObjectIdposts Post[] @relation(fields: [postIDs], references: [id])}
Prisma validates many-to-many relations in MongoDB with the following rules:
- The fields on both sides of the relation must have a list type (in the example above,
categories
have a type ofCategory[]
andposts
have a type ofPost[]
) - The
@relation
attribute must definefields
andreferences
arguments on both sides - The
fields
argument must have only one scalar field defined, which must be of a list type - The
references
argument must have only one scalar field defined. This scalar field must exist on the referenced model and must be of the same type as the scalar field in thefields
argument, but singular (no list) - The scalar field to which
references
points must have the@id
attribute - No referential actions are allowed in
@relation
The implicit many-to-many relations used in relational databases are not supported on MongoDB.
Querying MongoDB many-to-many relations
This section demonstrates how to query many-to-many relations in MongoDB, using the example schema above.
The following query finds posts with specific matching category IDs:
const newId1 = new ObjectId()const newId2 = new ObjectId()const posts = await prisma.post.findMany({where: {categoryIDs: {hasSome: [newId1.toHexString(), newId2.toHexString()],},},})
The following query finds posts where the category name contains the string 'Servers'
:
const posts = await prisma.post.findMany({where: {categories: {some: {name: {contains: 'Servers',},},},},})