Prisma relations
Hi friends, In this post, you’ll learn about Prisma relation
Relations
A relation is a connection between two models in the Prisma schema.
Types of relations
There are three different types of relations in Prisma
- One-to-One (also called 1–1-relation)
- One-to-Many (also called 1-n-relation)
- Many-to-Many (also called m-n-relation)
The following Prisma schema includes every type of relation:
- 1–1:
User
↔Profile
- 1-n:
User
↔Post
- m-n:
Post
↔Category
One-to-one relations
One-to-one (1–1) relations refer to relations where at most one record can be connected on both sides of the relation. In the example below, there is a one-to-one relation between User
and Profile
:
The userId
relation scalar is a direct representation of the foreign key in the underlying database. This one-to-one relation expresses the following:
- “a user can have zero or one profile” (because the
profile
field is optional onUser
) - “a profile must always be connected to one user”
Multi-field relations in relational database
In relational databases only, you can also define use multi-field IDs to define a 1–1 relation:
Required and optional 1–1 relation fields
In a one-to-one relation, the side of the relation without a relation scalar (the field representing the foreign key in the database) must be optional:
Choosing which side should store the foreign key in a 1–1 relation
In 1–1 relations, you can decide yourself which side of the relation you want to annotate with the @relation
attribute (and therefore holds the foreign key).
In the following example, the relation field on the Profile
model is annotated with the @relation
attribute. userId
is a direct representation of the foreign key in the underlying database:
note: You can also annotate the other side of the relation with the @relation attribute
One-to-many relations
One-to-many (1-n) relations refer to relations where one record on one side of the relation can be connected to zero or more records on the other side. In the following example, there is one one-to-many relation between the User
and Post
models:
The posts
field does not "manifest" in the underlying database schema. On the other side of the relation, the annotated relation fieldauthor
and its relation to scalar authorId
represent the side of the relation that stores the foreign key in the underlying database.
This one-to-many relation expresses the following:
- “a user can have zero or more posts”
- “a post must always have an author”
Multi-field relations in relational database
In relational databases only, you can also define this relation using multi-field IDs:
Comparing one-to-one and one-to-many relations
In relational databases, the main difference between a 1–1 and a 1-n-relation is that in a 1–1-relation the foreign key must have a UNIQUE
constraint defined on it.
Required and optional relational relation fields in one-to-many relations
A 1-n-relation always has two relation fields:
- a list relation field that is not annotated with
@relation
- the annotated relation field(including its relation scalar)
The list side of a 1-n relation is always mandatory. On the other side of the relation, the annotated relation field and relation scalar can either both be optional, or both be mandatory.
In the following example, you can create a Post
without assigning a User
:
In the following example, you must assign a User
when you create a Post
:
Many-to-many relations
It refers 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.
Relational databases
In relational databases, m-n-relations are typically modeled 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
):
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 assignment, or
CategoriesOnPosts
(assigned by Bob, assigned today) - Creates a 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
)
Explicit 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 make 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
:
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:
The following query creates a single Category
and multiple Post
records:
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 relation 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 an
@unique
in place of an@id
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:
Self-relations
A relation field can also reference its own model, in this case, the relation is called a self-relation. Self-relations can be of any cardinality, 1–1, 1-n and m-n.
Note that self-relations always require the @relation
attribute.
One-to-one self-relations
The following example models a one-to-one self-relation:
This relation expresses the following:
- “a user can have one or zero predecessors” (for example, Sarah is Mary’s predecessor as blog owner)
- “a user can have one or zero successors” (for example, Mary is Sarah’s successor as blog owner)
Note: One-to-one self-relations cannot be made required on both sides. One or both sides must be optional, otherwise it becomes impossible to create the first
User
record.
To create a one-to-one self-relation:
- Both sides of the relation must define a
@relation
attribute that share the same name - in this case, BlogOwnerHistory. - One relation field must be a fully annotated. In this example, the
successor
field defines both thefield
andreferences
arguments. - One relation field must be backed by a foreign key. The
successor
field is backed by thesuccessorId
foreign key, which references a value in theid
field. ThesuccessorId
scalar relation field also requires a@unique
attribute to guarantee a one-to-one relation.
Note: One-to-one self relations require two sides even if both sides are equal in the relationship. For example, to model a ‘best friends’ relation, you would need to create two relation fields:
bestfriend1
and abestfriend2
.
Alternatively, you could rewrite this so that predecessor
is backed by predecessorId
:
No matter which side is backed by a foreign key, the Prisma Client surfaces both the predecessor
and successor
fields:
One-to-many self relations
A one-to-many self-relation looks as follows:
This relation expresses the following:
- “a user has zero or one teacher “
- “a user can have zero or more students”
Note that you can also require each user to have a teacher by making the teacher
field required.
many-to-many self relations
A many-to-many self-relation looks as follows:
This relation expresses the following:
- “a user can be followed by zero or more users”
- “a user can follow zero or more users”
Note that for relational databases, this many-to-many-relation is implicit. This means Prisma maintains a relation table for it in the underlying database.