Relations

Relation is a fundamental concept in relational databases. It connect models into a graph, and allows you to query interconnected data efficiently. In ZModel, relations are modeled using the @relation attribute. For 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 defines the foreign key the "owner" side.

One-to-one relations

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 parameters 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')
}

model Profile {
    id        Int  @id
    user      User @relation('UserProfile', fields: [userId], references: [id])
    userId    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 relations

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 of list of the other side's model type.

Many-to-many relations

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 defining 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.

Self relations

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 (here mentor) 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 (here manager) 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('Mentorship')
  mentees Mentorship[] @relation('Mentorship')
}

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

  @@id([mentorId, menteeId])
}