Many-to-Many & Nested Writes

Prisma Fundamentals

Chapter 8 · Relations II: Many-to-Many & Nested Writes

A post can have several tags, and each tag can be on many posts. Neither side can hold a single foreign key, so this needs a third table in between. This chapter shows Prisma's two ways of modelling that, and then covers nested writes: creating or linking related records in the same call, which works for every kind of relation from Chapter 7 too.

How Many-to-Many Works in a Database

Post                   join table              Tag
┌────┬─────────┐      ┌────────┬───────┐      ┌────┬────────┐
│ id │ title   │      │ postId │ tagId │      │ id │ name   │
├────┼─────────┤      ├────────┼───────┤      ├────┼────────┤
│ 1  │ Intro   │      │ 1      │ 10    │      │ 10 │ prisma │
│ 2  │ Queries │      │ 1      │ 11    │      │ 11 │ sql    │
└────┴─────────┘      │ 2      │ 10    │      └────┴────────┘
                      └────────┴───────┘
      postId ──► Post.id              tagId ──► Tag.id

Each row of the join table links one post to one tag. Post 1 is tagged "prisma" and "sql"; post 2 is tagged "prisma".

Implicit Many-to-Many

The simplest way: put a list field on both sides and let Prisma manage the join table.

model Post {
  id   Int    @id @default(autoincrement())
  // ...
  tags Tag[]
}

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

Prisma creates a hidden join table, named from the two model names in alphabetical order — here _PostToTag, with columns A and B. You never refer to it in your code.

Implicit many-to-many has limits

Both models must have a single-field @id (no composite IDs).
You can't store anything else about the link, such as when a tag was added or by whom.
You can't set onDelete or onUpdate on it.
It doesn't work on MongoDB.

Explicit Many-to-Many

When the link itself carries information, make the join table a real model:

model Post {
  id   Int       @id @default(autoincrement())
  tags PostTag[]
}

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

model PostTag {
  postId     Int
  tagId      Int
  assignedAt DateTime @default(now())
  post       Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag        Tag      @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([postId, tagId])          // each post-tag pair only once
  @@index([tagId])
}

This is really two one-to-many relations (Chapter 7): a post has many PostTag rows, and so does a tag. The composite key stops the same tag being added to a post twice.

	Implicit	Explicit
Schema	Two list fields	A third model with two relations
Extra data on the link	No	Yes (`assignedAt`, `order`, …)
Queries	Simpler: `post.tags` are tags	One step more: `post.tags` are links, each with a `tag`
Delete behaviour	Managed by Prisma	You choose it
Use when	A plain link is enough	The link needs its own data or rules

Start implicit, switch if needed

Implicit relations are the sensible default. If you later need data on the link, you can move to an explicit model, but it means a migration that copies the existing links into the new table, so it's worth thinking ahead for links you suspect will grow.

Nested Writes

A nested write creates, links or unlinks related records inside the same create or update call. Prisma runs the whole thing as one transaction: either everything succeeds, or nothing is saved.

Operation	What it does
`create`	Create the related record(s) and link them
`connect`	Link existing records, found by a unique field
`connectOrCreate`	Link a record if it exists, create it if not
`disconnect`	Remove the link, keeping both records
`set`	Replace all links with exactly this list
`delete`, `update`, `upsert`	Change the related records themselves

Creating a user with posts and a profile in one call

const user = await prisma.user.create({
  data: {
    email: "alan@example.com",
    name:  "Alan",
    profile: { create: { bio: "Codebreaker" } },
    posts: {
      create: [
        { title: "Computing Machinery", slug: "computing-machinery" },
        { title: "Morphogenesis",       slug: "morphogenesis" },
      ],
    },
  },
});

You never set userId or authorId yourself: Prisma fills in the foreign keys because it knows how the records are related.

Tagging a post (implicit many-to-many)

await prisma.post.create({
  data: {
    title: "Nested Writes",
    slug:  "nested-writes",
    author: { connect: { email: "alan@example.com" } },   // existing user
    tags: {
      connectOrCreate: [
        { where: { name: "prisma" }, create: { name: "prisma" } },
        { where: { name: "orm" },    create: { name: "orm" } },
      ],
    },
  },
});

// Later: replace the post's tags with just "prisma"
await prisma.post.update({
  where: { slug: "nested-writes" },
  data:  { tags: { set: [{ name: "prisma" }] } },
});

Tagging a post (explicit many-to-many)

await prisma.post.update({
  where: { slug: "nested-writes" },
  data: {
    tags: {
      create: {
        tag: { connectOrCreate: { where: { name: "sql" }, create: { name: "sql" } } },
      },
    },
  },
});

With an explicit join model you create a PostTag row, and inside it connect or create the Tag. It's one level deeper than the implicit version.

Use either the foreign key or the relation, not both

In one data object, set a relation either by its foreign key (authorId: 1) or through the relation field (author: { connect: { id: 1 } }). Mixing the two styles in the same call gives a type error. Nested writes need the relation-field style.

Hands-On Exercises

Exercise 1

Add an implicit many-to-many relation between Post and Tag. In a single create call, create a post for an existing user, tagged with "prisma" (which already exists) and "typescript" (which doesn't). Then look at the join table in Prisma Studio.

📄 View solution

Exercise 2

Write a function setPostTags(slug, tagNames) that makes a post's tags exactly match the given list, creating any tags that don't exist yet.

📄 View solution

Exercise 3

A course platform links students to courses. For each link it must record when the student enrolled and whether they've completed the course. Design the models, and explain why an implicit relation won't do.

📄 View solution

Chapter 8 Quick Reference

Many-to-many needs a join table linking the two models
Implicit: list fields on both sides; Prisma manages a hidden _AToB table; no extra data, single-field IDs only, not on MongoDB
Explicit: a join model with two relations and @@id([aId, bId]); can store extra data and set delete rules
Nested writes: create, connect, connectOrCreate, disconnect, set, delete, update, upsert
A nested write runs as one transaction; Prisma fills in the foreign keys
Set a relation by foreign key or by relation field — not both in one call