Skip to content

Latest commit

 

History

History
300 lines (230 loc) · 7.38 KB

relations.md

File metadata and controls

300 lines (230 loc) · 7.38 KB
Raw

Relations

This is an extension of the data modeling chapter that discusses relations in the data model definition in detail.

The examples on this page are based on this schema file:

// schema.prisma

datasource mysql {
  url      = "file:data.db"
  provider = "sqlite"
}

model User {
  id        Int      @id
  posts     Post[]
  profile   Profile?
}

model Profile {
  id   Int    @id
  user User
}

model Post {
  id         Int        @id
  author     User
  categories Category[]
}

model Category {
  id    Int    @id
  posts Post[]
}

enum Role {
  USER
  ADMIN
}

Note that here all scalars and embeds have been removed from the example data model so you can focus on the relations.

It contains the following relations:

  • 1:1: User <-> Profile
  • 1:n: User <-> Post
  • m:n: Post <-> Category

The @relation attribute

The @relation attribute disambiguates relationships when needed.

It has the following signature:

@relation(_name: String?, references: Identifier[]?, onDelete: OnDeleteEnum?)
  • references (optional): List of field names to reference.
  • name (optional): Defines the name of the relationship. If this a m:m-relation, the name also determines the name of the relation table in the underlying database.
  • onDelete (optional): Defines what to do when the referenced relation is deleted.
    • NONE (default): Set the field to null.
    • CASCADE: Also delete this entry.

Note: Cascading deletes are not yet implemented. You can track the progress of this feature in this GitHub issue.

1:1

The return value on both sides is a nullable single value. Prisma prevents accidentally storing multiple records in the relation.

model User {
  id        Int      @id
  profile   Profile?
}

model Profile {
  id   Int    @id
  user User
}

For 1:1 relationships, it doesn't matter on which side you store the foreign key. Prisma has a convention that the foreign key is added to the model which appears first alphanumerically in your data model. In the example above, that's the Profile model.

Under the hood, the models looks like this:

User
id integer
Profile
id integer
user integer

You may omit either User.profile or Profile.user and the relationship will remain intact. This makes either the back-relation or the forward-relation optional. If one side of the relation is missing, Prisma implies the field name based on the name of the model it is pointing to.

If you're introspecting an existing database and the foreign key does not follow the alphanumeric convention, then Prisma uses the @relation attribute to clarify.

model User {
  id        Int        @id
  customer  Profile?   @relation(references: id)
}

model Profile {
  id    Int     @id
  user  User?
}

1:n

The return value on one side is a optional single value, on the other side a list that might be empty.

model User {
  id        Int      @id
  posts     Post[]
}

model Post {
  id         Int        @id
  author     User
}

In this example, Post.author points to the primary key on User.

Connectors for relational databases will implement this as two tables with a foreign key constraint on the Post table:

User
id integer
Post
id integer
author integer

You may omit Post.author and the relationship will remain intact. If one side of the relation is missing, Prisma implies the field name based on the name of the model it is pointing to. If you omitted User.posts, Prisma would add an implicit User.post field, making the relation 1:1 instead of 1:n.

You may also map to composite primary keys:

model User {
  first_name  String  @id
  last_name   String
  blogs       Blog[]

  @@id([ first_name, last_name ])
}

model Post {
  id         Int @id
  author     Writer
}

This results in the following tables:

User
first_name text
last_name text
Post
id Int
author_first_name text
author_last_name text

m:n

The return value on both sides is a list that might be empty. This is an improvement over the standard implementation in relational databases that require the application developer to deal with implementation details such as an intermediate table / join table. In Prisma, each connector will implement this concept in the way that is most efficient on the given storage engine and expose an API that hides the implementation details.

model Post {
  id         Int        @id
  categories Category[]
}

model Category {
  id    Int    @id
  posts Post[]
}

Self-relations

Prisma supports self-referential relations:

model Employee {
  id         Int       @id
  reportsTo  Employee
}

This results in the following table:

Employee
id integer
reportsTo integer

Relations in the generated Photon API

The generated Photon API comes with many helpful features for relations (find examples below):

  • Fluent API to traverse relations on the returned object
  • Eagerly load relations via select or include
  • Relation filters (a filter on a related object, i.e. a JOIN is performed before the filter is applied)
  • Nested creates, updates and connects (also referred to as nested writes)

Fluent API

// Retrieve the posts of a user
const postsByUser: Post[] = await photon
  .users
  .findOne({ where: { email: "[email protected]" }})
  .posts()
// Retrieve the categories of a post
const categoriesOfPost: Category[] = await photon
  .posts
  .findOne({ where: { id: 1 }})
  .categories()

Eager loading

// The returned post objects will only have the  `id` and
// `author` property which carries the respective user object
const allPosts: Post[] = await photon.posts.findMany({
  select: ['id', 'author']
})
// The returned posts objects will have all scalar fields of the `Post` model and additionally all the categories for each post
const allPosts: Post[] = await photon.posts.findMany({
  include: ["categories"]
})

Relation filters

// Retrieve all posts of a particular user 
// that start with "Hello"
const posts: Post[] = await photon.users.findOne({
  where: { email: '[email protected]' },
}).posts({
  where: {
    title: { startsWith: "Hello" }
  }
})

Nested writes

// Create a new user with two posts in a 
// single transaction
const newUser: User = await photon.users.create({
  data: {
    email: '[email protected]',
    posts: {
      create: [
        { title: 'Join the Prisma Slack on https://slack.prisma.io' },
        { title: 'Follow @prisma on Twitter' },
      ],
    },
  },
})
// Change the author of a post in a single transaction
const updatedPost: Post = await photon.posts.update({
  where: { id: 5424 },
  data: {
    author: {
      connect: { email: '[email protected]' },
    },
  },
})