Relation

Relations are a fundamental concept in relational databases. They connect models into a graph and allow you to query interconnected data efficiently. In ZModel, relations are modeled using the @relation attribute. In most cases, it involves one side of the relation defining a foreign key field that references the primary key of the other side. By convention, we call the model that holds the foreign key the "owner" side.

One-to-one relation

A typical one-to-one relation looks like this:

model User {
    id      Int      @id
    profile Profile?
}

model Profile {
    id     Int  @id
    user   User @relation(fields: [userId], references: [id])
    userId Int  @unique
}

The Profile model holds the foreign key userId and is the owner of the relation. The pk-fk association is established by the @relation attribute, where the fields parameter specifies the foreign key field(s) and the references parameter specifies the primary key field(s) of the other side.

In one-to-one relations, the "non-owner" side must declare the relation field as optional (here User.profile), because there's no way to guarantee a User row always has a corresponding Profile row at the database level. The owner side can be either optional or required.

Relations can also be explicitly named, and it's useful to disambiguate relations when a model has multiple relations to the same model, or to control the constraint name generated by the migration engine.

model User {
    id             Int      @id
    profile        Profile? @relation('UserProfile')
    privateProfile Profile? @relation('UserPrivateProfile')
}

model Profile {
    id            Int  @id
    user          User @relation('UserProfile', fields: [userId], references: [id])
    userId        Int  @unique
    userPrivate   User @relation('UserPrivateProfile', fields: [userPrivateId], references: [id])
    userPrivateId Int  @unique
}

Please note that even though both sides of the relation now have the @relation attribute, only the owner side can have the fields and references parameters.

If a relation involves a model with composite PK fields, the FK fields must match the PK fields' count and types, and the fields and references parameters must be specified with those field tuples with matching order.

model User {
    id1     Int
    id2     Int
    profile Profile?

    @@id([id1, id2])
}

model Profile {
    id      Int  @id
    user    User @relation(fields: [userId1, userId2], references: [id1, id2])
    userId1 Int
    userId2 Int
}

One-to-many relation

A typical one-to-many relation looks like this:

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

model Post {
    id        Int  @id
    author    User @relation(fields: [authorId], references: [id])
    authorId  Int
}

It's modeled pretty much the same way as one-to-one relations, except that the "non-owner" side (here User.posts) is a list of the other side's model type.

Many-to-many relation

Many-to-many relations are modeled in the database through a join table, which forms a many-to-one relation with each of the two sides.

In ZModel, there are two ways to model many-to-many relations: implicitly or explicitly.

Implicit many-to-many

An implicit many-to-many relation simply defines both sides of the relation as lists of the other side's model type, without modeling a join table explicitly.

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

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

Under the hood, the migration engine creates a join table named _PostToUser (model names are sorted alphabetically), and the ORM runtime transparently handles the join table for you.

You can also name the join table explicitly by adding the @relation attribute to both sides:

model User {
    id    Int    @id
    posts Post[] @relation('UserPosts')
}

model Post {
    id      Int    @id
    editors User[] @relation('UserPosts')
}

Explicit many-to-many

Explicit many-to-many relations are nothing but a join table with foreign keys linking the two sides.

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

model Post {
    id      Int        @id
    editors UserPost[]
}

model UserPost {
    userId Int
    postId Int
    user   User @relation(fields: [userId], references: [id])
    post   Post @relation(fields: [postId], references: [id])

    @@id([userId, postId])
}

Since the join table is explicitly defined, when using the ORM, you'll need to involve it in your queries with an extra level of nesting. Albeit the complexity, an explicit join table gives you the flexibility, e.g., to have extra fields.

Self relation

Self-relations are cases where a model has a relation to itself. They can be one-to-one, one-to-many, or many-to-many.

One-to-one

model Employee {
    id       Int       @id
    mentorId Int?      @unique
    mentor   Employee? @relation('Mentorship', fields: [mentorId], references: [id])
    mentee   Employee? @relation('Mentorship')
}

Quick notes:

• Both sides of the relation are defined in the same model.
• Both relation fields need to have @relation attributes with matching names.
• One side has a foreign key field (mentorId) that references the primary key.
• The foreign key field is marked @unique to guarantee one-to-one.

One-to-many

model Employee {
  id           Int        @id
  managerId    Int
  manager      Employee   @relation('Management', fields: [managerId], references: [id])
  subordinates Employee[] @relation('Management')
}

Quick notes:

• Both sides of the relation are defined in the same model.
• Both relation fields need to have @relation attributes with matching names.
• One side has a foreign key field (managerId) that references the primary key.
• The owner side (Employee.manager) can be either optional or required based on your needs.

Many-to-many

Defining an implicit many-to-many self-relation is very straightforward.

model Employee {
  id      Int        @id
  mentors Employee[] @relation('Mentorship')
  mentees Employee[] @relation('Mentorship')
}

You can also define an explicit one by modeling the join table explicitly.

model Employee {
  id      Int          @id
  mentors Mentorship[] @relation('Mentor')
  mentees Mentorship[] @relation('Mentee')
}

model Mentorship {
  mentorId Int
  menteeId Int
  mentor   Employee @relation('Mentor', fields: [mentorId], references: [id])
  mentee   Employee @relation('Mentee', fields: [menteeId], references: [id])

  @@id([mentorId, menteeId])
}

Referential Actions

When defining a relation, you can use referential action to control what happens when one side of a relation is updated or deleted by setting the onDelete and onUpdate parameters in the @relation attribute.

attribute @relation(
    _ name: String?,
    fields: FieldReference[]?,
    references: FieldReference[]?,
    onDelete: ReferentialAction?,
    onUpdate: ReferentialAction?,
    map: String?)

The ReferentialAction enum is defined as:

enum ReferentialAction {
    Cascade
    Restrict
    NoAction
    SetNull
    SetDefault
}

• Cascade

  • onDelete: deleting a referenced record will trigger the deletion of referencing record.
  • onUpdate: updates the relation scalar fields if the referenced scalar fields of the dependent record are updated.

• Restrict

  • onDelete: prevents the deletion if any referencing records exist.
  • onUpdate: prevents the identifier of a referenced record from being changed.

• NoAction

  Similar to 'Restrict', the difference between the two is dependent on the database being used.

• SetNull

  • onDelete: the scalar field of the referencing object will be set to NULL.
  • onUpdate: when updating the identifier of a referenced object, the scalar fields of the referencing objects will be set to NULL.

• SetDefault

  • onDelete: the scalar field of the referencing object will be set to the fields default value.
  • onUpdate: the scalar field of the referencing object will be set to the fields default value.

Example

model User {
    id String        @id
    profile Profile?
}

model Profile {
    id String     @id
    user User     @relation(fields: [userId], references: [id], onUpdate: Cascade, onDelete: Cascade)
    userId String @unique
}