Relations

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 on User)
“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 and Post
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 and 22)

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 the references, fields, onUpdate or onDelete 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 the field and references arguments.
One relation field must be backed by a foreign key. The successor field is backed by the successorId foreign key, which references a value in the id field. The successorId 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 a bestfriend2.

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.