Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 36 ยท Relations

One-to-One and Many-to-Many Relations in Prisma

Model one-to-one relations with a unique foreign key and many-to-many relations with implicit or explicit join tables, and learn when each style is the right choice.

One-to-many covers most relationships, but two more shapes are essential: one-to-one (a user has one profile) and many-to-many (posts have many tags, and tags belong to many posts). This lesson shows how to model both in Prisma and how the database represents them.

One-to-one

A one-to-one relation is a one-to-many relation where the foreign key is also unique: at most one row can point at a given parent.

PRISMA
model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  profile Profile?                      // optional: a user may have no profile yet
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @unique                 // @unique makes it one-to-one
}
  • userId Int @unique is what turns "many profiles per user" into "at most one".
  • The side with the foreign key (Profile) holds a required user field. The other side (User.profile) must be optional (Profile?), because a user can exist before a profile does, and Prisma cannot make the back side required.
  • If every profile must belong to a user, keep userId non-optional. If a profile may exist without a user, make both userId Int? and user User? optional.

Queries:

TypeScript
await prisma.user.create({
  data: {
    email: "[email protected]",
    profile: { create: { bio: "Mathematician" } },
  },
});

const ada = await prisma.user.findUnique({
  where: { email: "[email protected]" },
  include: { profile: true },
});
console.log(ada?.profile?.bio);    // profile may be null, so use ?.

Sharing the primary key is another option: Profile.id can itself be the foreign key (id Int @id plus @relation(fields: [id], references: [id])), which saves a column and enforces the one-to-one shape through the primary key.

Many-to-many: implicit

In a many-to-many relation each side holds a list of the other:

PRISMA
model Post {
  id    Int    @id @default(autoincrement())
  title String
  tags  Tag[]
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

Prisma sees two list fields pointing at each other and creates a hidden join table for you, named _PostToTag, with two columns, A and B, referencing the two ids. You never see it in the client; you use the relation fields directly:

TypeScript
await prisma.post.create({
  data: {
    title: "Prisma relations",
    tags: {
      connectOrCreate: [
        { where: { name: "prisma" }, create: { name: "prisma" } },
        { where: { name: "orm" }, create: { name: "orm" } },
      ],
    },
  },
  include: { tags: true },
});

await prisma.post.findMany({ where: { tags: { some: { name: "prisma" } } } });

Implicit relations are the simplest option, and perfect when the link itself carries no information. Limitations: no extra columns on the link, and the hidden table has Prisma-controlled names and no easy way to add a composite index of your own.

Many-to-many: explicit

When the link needs its own data, such as when a student enrolled, a role, a quantity, or a sort order, model the join table yourself as a normal model:

PRISMA
model Student {
  id          Int          @id @default(autoincrement())
  name        String
  enrollments Enrollment[]
}

model Course {
  id          Int          @id @default(autoincrement())
  title       String
  enrollments Enrollment[]
}

model Enrollment {
  student    Student  @relation(fields: [studentId], references: [id])
  studentId  Int
  course     Course   @relation(fields: [courseId], references: [id])
  courseId   Int
  enrolledAt DateTime @default(now())
  grade      Int?

  @@id([studentId, courseId])          // each pair once
  @@index([courseId])
}

Now Enrollment is a model like any other, so you can query and update grade, and you read the "other side" by going through it:

TypeScript
const course = await prisma.course.findUnique({
  where: { id: 1 },
  include: { enrollments: { include: { student: true } } },
});
const names = course?.enrollments.map((e) => e.student.name);
Implicit Explicit
Join table hidden, managed by Prisma your own model
Extra columns no yes
Query direct (post.tags) through the join model (course.enrollments)
Best for tags, categories, likes enrollments, order items, memberships with roles

If you start implicit and later need an extra column, migrating to explicit requires moving data. When in doubt about a relationship that carries data ("when", "how many", "which role"), choose explicit from the beginning.

What the database holds

Here is the join table idea in real SQL: two entity tables, one link table with a composite primary key, and the queries a tag filter and "list a post's tags" turn into.

Node.js A join table in SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Connecting and disconnecting

For to-many relations, update uses connect, disconnect and set:

TypeScript
await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: {
      connect: [{ name: "sql" }],         // add links
      disconnect: [{ name: "orm" }],      // remove links, tags stay
    },
  },
});

await prisma.post.update({
  where: { id: 1 },
  data: { tags: { set: [{ name: "prisma" }] } },   // replace all links
});

disconnect and set remove the link, never the tag itself. On an explicit join model you delete or create Enrollment rows instead. This stand-in shows connect, disconnect and set semantics on an in-memory link set.

Node.js Stand-in: connect, disconnect, set

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What makes a relation one-to-one rather than one-to-many?

    A unique foreign key (userId Int @unique) allows at most one child per parent.
  2. 2. Why must the back side of a one-to-one relation (User.profile) be optional?

    The foreign key lives on the child, so the parent row can exist without a child row.
  3. 3. When should you use an explicit many-to-many join model?

    Explicit join models let you store extra columns on the relationship.
  4. 4. What does tags: { disconnect: [{ name: 'orm' }] } do?

    disconnect removes the relationship, never the related record itself.
  5. 5. What is the name of the hidden table Prisma creates for an implicit Post-Tag relation?

    Implicit relations use a table named _ModelAToModelB with columns A and B.

Summary

  • One-to-one: a foreign key with @unique; the back-relation is optional.
  • Implicit many-to-many: two list fields and a hidden _AToB table; simple, no extra columns.
  • Explicit many-to-many: your own join model with a composite @@id; needed for data on the link.
  • connect, disconnect and set change links, never the related rows themselves.

Next: include, select and Nested Writes