# Many-to-many relations (/docs/orm/prisma-schema/data-model/relations/many-to-many-relations)

Location: ORM > Prisma Schema > Data Model > Relations > Many-to-many relations

Many-to-many (m-n) relations connect zero or more records on one side to zero or more on the other. They can be [implicit](#implicit-many-to-many-relations) (Prisma manages the relation table) or [explicit](#explicit-many-to-many-relations) (you define the relation table).

Relational databases [#relational-databases]

Use [implicit](#implicit-many-to-many-relations) m-n unless you need to store additional metadata in the relation table.

Explicit many-to-many relations [#explicit-many-to-many-relations]

The relation table is represented as a model in the schema:

```prisma
model Post {
  id         Int                 @id @default(autoincrement())
  title      String
  categories CategoriesOnPosts[]
}

model Category {
  id    Int                 @id @default(autoincrement())
  name  String
  posts CategoriesOnPosts[]
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int
  assignedAt DateTime @default(now())
  assignedBy String
  @@id([postId, categoryId])
}
```

The relation table can store additional fields like `assignedAt` and `assignedBy`.

Querying explicit many-to-many [#querying-explicit-many-to-many]

```ts
// Create post with new category
const post = await prisma.post.create({
  data: {
    title: "How to be Bob",
    categories: {
      create: [
        {
          assignedBy: "Bob",
          category: { create: { name: "New category" } },
        },
      ],
    },
  },
});

// Connect to existing categories
await prisma.post.create({
  data: {
    title: "My Post",
    categories: {
      create: [
        { assignedBy: "Bob", category: { connect: { id: 9 } } },
        { assignedBy: "Bob", category: { connect: { id: 22 } } },
      ],
    },
  },
});

// Query posts by category
const posts = await prisma.post.findMany({
  where: { categories: { some: { category: { name: "New Category" } } } },
});
```

Implicit many-to-many relations [#implicit-many-to-many-relations]

Prisma manages the relation table automatically:

```prisma
model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[]
}

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

Querying implicit many-to-many [#querying-implicit-many-to-many]

```ts
// Create post with categories
const post = await prisma.post.create({
  data: {
    title: "How to become a butterfly",
    categories: {
      create: [{ name: "Magic" }, { name: "Butterflies" }],
    },
  },
});

// Get posts with categories
const posts = await prisma.post.findMany({
  include: { categories: true },
});
```

Rules for implicit m-n [#rules-for-implicit-m-n]

* Both models must have a single `@id` (no composite IDs or `@unique`)
* No `@relation` attribute needed (unless disambiguating)
* Cannot use `fields`, `references`, `onUpdate`, or `onDelete` in `@relation`

Relation table conventions [#relation-table-conventions]

For `prisma db pull` to recognize implicit m-n tables:

* Table name: `_CategoryToPost` (underscore + model names alphabetically + `To`)
* Columns: `A` (FK to first model alphabetically) and `B` (FK to second)
* Unique index on both columns, non-unique index on `B`

Configuring relation table name [#configuring-relation-table-name]

Use `@relation("MyRelationTable")` on both sides to customize the table name.

MongoDB [#mongodb]

MongoDB requires explicit ID arrays on both sides:

```prisma
model Post {
  id          String     @id @default(auto()) @map("_id") @db.ObjectId
  categoryIDs String[]   @db.ObjectId
  categories  Category[] @relation(fields: [categoryIDs], references: [id])
}

model Category {
  id      String   @id @default(auto()) @map("_id") @db.ObjectId
  name    String
  postIDs String[] @db.ObjectId
  posts   Post[]   @relation(fields: [postIDs], references: [id])
}
```

Querying MongoDB m-n [#querying-mongodb-m-n]

```ts
// Find posts by category IDs
const posts = await prisma.post.findMany({
  where: { categoryIDs: { hasSome: [id1, id2] } },
});

// Find posts by category name
const posts = await prisma.post.findMany({
  where: { categories: { some: { name: { contains: "Servers" } } } },
});
```

## Related pages

- [`One-to-many relations`](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/one-to-many-relations): How to define and work with one-to-many relations in Prisma.
- [`One-to-one relations`](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/one-to-one-relations): How to define and work with one-to-one relations in Prisma.
- [`Referential actions`](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/referential-actions): Referential actions let you define the update and delete behavior of related models on the database level
- [`Relation mode`](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/relation-mode): Manage relations between records with relation modes in Prisma
- [`Self-relations`](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/self-relations): How to define and work with self-relations in Prisma.