Many-to-Many Relationships

Objectives

By the end of this lesson, you should be able to:

• Model an implicit many-to-many relationship in Prisma
• Explain what a join table is, and why many-to-many needs one
• Connect existing records to each other without creating duplicates

💡 Why this matters: A book can have many tags (“programming”, “beginner-friendly”), and a tag can apply to many books. Neither side has a single foreign key, that’s what makes this relationship shape different from Lesson 1’s.

⚠️ A note on verification: as throughout this module, the Prisma CLI can’t run inside this course’s own sandboxed tooling. The schema syntax and migration output below reflect Prisma’s stable, current, documented behavior. Try it yourself, on your own machine, against a real database.

The Shape of a Many-to-Many Relationship

Book (many) >──────< (many) Tag

A Book can have many Tags, and a Tag can apply to many Books. Neither Book nor Tag can hold a single foreign key pointing at the other, there isn’t one value that would work.

The Join Table

Relational databases solve this with a join table, a third table storing pairs of IDs, one row per book-tag connection:

_BookToTag
+--------+-------+
| bookId | tagId |
+--------+-------+
| 1      | 1     |
| 1      | 2     |
| 2      | 1     |
+--------+-------+

Modeling It in Prisma

Prisma can manage this join table implicitly, no separate model needed for the simplest case:

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

model Tag {
  id    Int    @id @default(autoincrement())
  name  String
  books Book[]
}

Both sides just declare an array of the other model, Tag[] on Book, Book[] on Tag. No authorId-style foreign key field appears on either model, that’s the signal this is many-to-many, not one-to-many.

The Generated Migration

npx prisma migrate dev --name add_book_tags

-- CreateTable
CREATE TABLE "Book" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    CONSTRAINT "Book_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Tag" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,
    CONSTRAINT "Tag_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "_BookToTag" (
    "A" INTEGER NOT NULL,
    "B" INTEGER NOT NULL
);

-- CreateIndex
CREATE UNIQUE INDEX "_BookToTag_AB_unique" ON "_BookToTag"("A", "B");

-- AddForeignKey
ALTER TABLE "_BookToTag" ADD CONSTRAINT "_BookToTag_A_fkey"
    FOREIGN KEY ("A") REFERENCES "Book"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_BookToTag" ADD CONSTRAINT "_BookToTag_B_fkey"
    FOREIGN KEY ("B") REFERENCES "Tag"("id") ON DELETE CASCADE ON UPDATE CASCADE;

Prisma generated the entire _BookToTag join table automatically, A and B columns referencing Book.id and Tag.id, with a unique constraint preventing the same pair from being connected twice. This table is never referenced directly in application code, Prisma manages it entirely behind the relationship fields.

Connecting Existing Records

const tag = await prisma.tag.create({ data: { name: 'programming' } });

const book = await prisma.book.create({
  data: {
    title: 'Clean Code',
    tags: {
      connect: [{ id: tag.id }]
    }
  }
});

connect links to a record that already exists, by its ID, as opposed to create (Lesson 1), which makes a brand-new related record. Connecting an already-existing tag to multiple books, rather than creating a new “programming” tag every time, is exactly why connect matters here, tags are meant to be shared.

Try It

1. Model Book and Tag with an implicit many-to-many relationship, as shown, and migrate it.
2. Read the generated migration, and find the _BookToTag table Prisma created automatically.
3. Create one tag, then connect it to two different books using connect, without creating the tag twice.
4. Explain, in your own words, why Book and Tag can’t just each hold a single foreign key field the way Book.authorId did in the last lesson.

Recap

• A many-to-many relationship needs a join table, storing pairs of IDs, Prisma generates and manages this table implicitly when both models declare an array of each other.
• No foreign key field appears directly on either model, that absence is the signal it’s many-to-many, not one-to-many.
• connect links to an existing record by ID, create makes a new one, both work inside a relationship field.

Next lesson: loading related data efficiently, with include and select.