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 authorId 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 one-to-many relation because multiple Post documents refer to the same User document.

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

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

Create a record and nested records

The following query creates a User record and two connected Post records:

const userAndPosts = await prisma.user.create({
  data: {
    posts: {
      create: [
        { title: 'Prisma Day 2020' }, // Populates authorId with user's id
        { title: 'How to write a Prisma schema' }, // Populates authorId with user's id
      ],
    },
  },
})

In the underlying database, this query:

1. Creates a User with an auto-generated id (for example, 20)
2. Creates two new Post records and sets the authorId of both records to 20

Retrieve a record and include related records

The following query retrieves a User by id and includes any related Post records:

const getAuthor = await prisma.user.findUnique({
  where: {
    id: "20",
  },
  include: {
    posts: true, // All posts where authorId == 20
  },
});

In the underlying database, this query:

1. Retrieves the User record with an id of 20
2. Retrieves all Post records with an authorId of 20

Associate an existing record to another existing record

The following query associates an existing Post record with an existing User record:

const updateAuthor = await prisma.user.update({
  where: {
    id: 20,
  },
  data: {
    posts: {
      connect: {
        id: 4,
      },
    },
  },
})

In the underlying database, this query uses a nested connect query to link the post with an id of 4 to the user with an id of 20. The query does this with the following steps:

• The query first looks for the user with an id of 20.
• The query then sets the authorID foreign key to 20. This links the post with an id of 4 to the user with an id of 20.

In this query, the current value of authorID does not matter. The query changes authorID to 20, no matter its current value.

Implicit and explicit many-to-many relations

Many-to-many relations in relational databases can be modelled in two ways:

• explicit many-to-many relations, where the relation table is represented as an explicit model in your Prisma schema
• implicit many-to-many relations, where Prisma manages the relation table and it does not appear in the Prisma schema.

Implicit many-to-many relations require both models to have a single @id. Be aware of the following:

• 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 many-to-many relation still manifests in a relation table in the underlying database. However, Prisma manages this relation table.

If you use an implicit many-to-many relation instead of an explicit one, it makes the Prisma Client API simpler (because, for example, you 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.

Annotated relation 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:

• one-to-one relations
• one-to-many relations
• many-to-many relations 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.

Relation scalar fields