Relations

Relational databases

The following entity relationship diagram defines the same one-to-many relation between the User and Post tables in a relational database:

In SQL, you use a foreign key to create a relation between two tables. Foreign keys are stored on one side of the relation. Our example is made up of:

• A foreign key column in the Post table named authorId.
• A primary key column in the User table named id. The authorId column in the Post table references the id column in the User table.

In the Prisma schema, the foreign key / primary key relationship is represented by the @relation attribute on the author field:

author     User        @relation(fields: [authorId], references: [id])

Note: Relations in the Prisma schema represent relationships that exist between tables in the database. If the relationship does not exist in the database, it does not exist in the Prisma schema.

MongoDB

For MongoDB, Prisma currently uses a normalized data model design, which means that documents reference each other by ID in a similar way to relational databases.

The following document represents a User (in the User collection):

{ "_id": { "$oid": "60d5922d00581b8f0062e3a8" }, "name": "Ella" }

The following list of Post documents (in the Post collection) each have a userId field which reference the same user:

[
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3a9" },
    "title": "How to make sushi",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  },
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3aa" },
    "title": "How to re-install Windows",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  }
]

This data structure represents a 1-n relation because multiple Post documents refer to the same User document.

model User {
  id     String  @id @default(dbgenerated()) @db.ObjectId @map("_id")
  posts  Post[]
}

model Post {
  id        String  @id @default(dbgenerated()) @db.ObjectId @map("_id")
  author    User    @relation(fields: [authorId], references: [id])
  authorId  String  @db.ObjectId   // relation scalar field  (used in the `@relation` attribute above)
}

Relation fields

Relation fields are fields on a Prisma model that do not have a [scalar type(../data-model#scalar-fields). Instead, their type is another model.

Every relation must have exactly two relation fields, one on each model. In case of 1-1 and 1-n relations, an additional relation scalar field is required which gets linked by one of the two relation fields in the @relation attribute. This relation scalar is the direct representation of the foreign key in the underlying database.

Consider these two models:

model User {
  id      Int      @id @default(autoincrement())
  posts   Post[]
  profile Profile?
}

model Profile {
  id      Int     @id @default(autoincrement())
  user    User    @relation(fields: [userId], references: [id])
  userId  Int     // relation scalar field (used in the `@relation` attribute above)
}

model Post {
  id         Int         @id @default(autoincrement())
  author     User        @relation(fields: [authorId], references: [id])
  authorId   Int         // relation scalar field  (used in the `@relation` attribute above)
  categories Category[]
}

model Category {
  id     Int     @id @default(autoincrement())
  posts  Post[]
}

Note: This schema is the same as the example data model but has all scalar fields removed (except for the required relation scalars) so you can focus on the relation fields. Note: Implicit many-to-many relations do not require the @relation attribute unless you need to disambiguate relations (this is not specific to implicit n-m relations).

Implicit many-to-many relations 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 set up an explicit many-to-many instead.

The implicit m-n-relation still manifests in a relation table in the underlying database. However, this relation table is managed by Prisma.

Using an implicit instead of an explicit m-n relations makes the Prisma Client API for many-to-many relations a bit simpler (since you e.g. have one fewer level of nesting inside of nested writes).

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.

For relational databases, the following entity relationship diagram represents the database that corresponds to the sample Prisma schema:

For MongoDB, Prisma uses a normalized data model design, which means that documents reference each other by ID in a similar way to relational databases:

For example, the following MongoDB document represents a User:

{ "_id": { "$oid": "60d5922d00581b8f0062e3a8" }, "name": "Ella" }

The following list of Post documents each have a userId field which reference the same user:

[
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3a9" },
    "title": "How to make sushi",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  },
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3aa" },
    "title": "How to re-install Windows",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  }
]

Annotated relation fields and relation scalar fields

Relations that require one side of the relation to be annotated with the @relation attribute are referred to as annotated relation fields. This includes:

• 1-1
• 1-n
• m-n for MongoDB only

The side of the relation which is annotated with the @relation attribute represents the side that stores the foreign key in the underlying database. The "actual" field that represents the foreign key is required on that side of the relation as well, it's called relation scalar field, and is referenced inside @relation attribute:

author     User    @relation(fields: [authorId], references: [id])
authorId   Int

A scalar field becomes a relation scalar field when it's used in the fields of a @relation attribute.