skip to content

In Prisma 7, when can a many-to-many between Order and Product stay implicit, and when does it need an explicit join model?

level: middleimportance: must knowfreq 72%

answer

  1. who owns the join table
  2. two list fields and nothing else
  3. _OrderToProduct with columns A and B
  4. a quantity needs its own model
  5. one more nesting level in writes

basics

~20 s

Keep it implicit while the link carries no data: two list fields make Prisma manage a hidden _OrderToProduct table. Once the link needs a column such as quantity, its own referential actions or a composite-key model, write an explicit OrderItem model.

solid answer

~50 s

An **implicit** many-to-many is just two list fields, `products Product[]` and `orders Order[]`. Prisma Migrate then creates and owns a relation table, `_OrderToProduct`, with columns `A` and `B` pointing at the two models, and on PostgreSQL both foreign keys are `ON DELETE CASCADE`. It keeps writes short (`products: { connect: [{ id: 1 }] }`), but it has hard limits. The table can hold no extra columns, `onDelete` and `onUpdate` are rejected, and both models need a single `@id`. An order line with a `quantity` therefore needs an **explicit** model: `OrderItem` with two `@relation` fields, the `quantity` column, `@@id([orderId, productId])`, and an `items OrderItem[]` back-relation on both `Order` and `Product`. Writes then go one level deeper, through `items: { create: [{ quantity: 2, product: { connect: { id: 1 } } }] }`. Switching later is not free: the generated migration drops the implicit table.

go deeper

for a junior

Recall that two list fields create an implicit many-to-many and that Prisma manages a hidden join table for it.

for a middle

Explain the limits that force an explicit model: extra columns, referential actions, composite ids. Also show how nested writes gain a level through the join model.

for a senior

Show the production view: implicit join rows cascade on delete with no override, and converting to explicit drops the table unless the migration copies the links first.

for a principal

Weigh a convention for new links: explicit models cost a nesting level everywhere but avoid a data-moving migration when a link later needs attributes.

## What an implicit many-to-many is A **many-to-many** relation lets each order hold many products and each product appear in many orders. A relational database stores that in a third table with one row per link. Prisma ORM 7 offers two ways to declare it. In the **implicit** form you write only the two list fields and let Prisma own the third table: ```prisma model Order { id Int @id @default(autoincrement()) products Product[] } model Product { id Int @id @default(autoincrement()) orders Order[] } ``` No `@relation` attribute is needed unless you want to name the relation, and the relation table never appears in the schema. ## What Prisma Migrate creates for it On PostgreSQL, Prisma 7.10 generates this table for the schema above: ```sql CREATE TABLE "_OrderToProduct" ( "A" INTEGER NOT NULL, "B" INTEGER NOT NULL, CONSTRAINT "_OrderToProduct_AB_pkey" PRIMARY KEY ("A","B") ); CREATE INDEX "_OrderToProduct_B_index" ON "_OrderToProduct"("B"); ALTER TABLE "_OrderToProduct" ADD CONSTRAINT "_OrderToProduct_A_fkey" FOREIGN KEY ("A") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE "_OrderToProduct" ADD CONSTRAINT "_OrderToProduct_B_fkey" FOREIGN KEY ("B") REFERENCES "Product"("id") ON DELETE CASCADE ON UPDATE CASCADE; ``` The naming follows fixed conventions: - the table is an underscore plus both model names in alphabetical order joined by `To`; - column `A` points at the alphabetically first model, `B` at the second; - a shared relation name on both sides, such as `@relation("OrderLines")`, renames the table to `_OrderLines`. Those conventions also let `prisma db pull` recognise an existing table as an implicit relation. ## Limits of the implicit form The implicit form is convenient precisely because it is fixed. You cannot: 1. **Add columns** to the link. There is nowhere to put `quantity`, a unit price or `addedAt`. 2. **Choose referential actions.** `@relation(onDelete: Restrict)` on an implicit list fails validation with "Referential actions on implicit many-to-many relations are not supported". Both foreign keys always cascade, so deleting a product silently removes it from every order. 3. **Use composite or unique-only identifiers.** Both models need a single `@id`; with an `@@id` the validator tells you to "use an explicit intermediate Model". ## The explicit join model When any of those limits bites, the link becomes a model of its own: ```prisma model Order { id Int @id @default(autoincrement()) items OrderItem[] } model Product { id Int @id @default(autoincrement()) items OrderItem[] } model OrderItem { order Order @relation(fields: [orderId], references: [id], onDelete: Cascade) orderId Int product Product @relation(fields: [productId], references: [id]) productId Int quantity Int @default(1) @@id([orderId, productId]) } ``` Technically this is **two one-to-many relations**, so both `Order` and `Product` need an `items OrderItem[]` back-relation; without them validation fails with a missing opposite relation field. A common mistake is to keep the old `products Product[]` and `orders Order[]` lists as well, which Prisma reads as a second, implicit relation beside the join model. In return, each side gets its own action: an order's lines cascade with it, while the product side keeps the default `Restrict` for a required relation, so a product that was sold cannot be deleted. ## How writes differ | Task | Implicit | Explicit | | --- | --- | --- | | Link an existing product | `products: { connect: [{ id: 1 }] }` | `items: { create: [{ quantity: 2, product: { connect: { id: 1 } } }] }` | | Unlink | `products: { disconnect: [{ id: 1 }] }` | delete the `OrderItem` row | | Store a quantity | impossible | a column on `OrderItem` | | Set delete behaviour | always cascade | per side, with `onDelete` | The explicit form costs one level of nesting in every write and read, which is the price the Prisma docs name when they recommend the implicit form unless you need extra data in the relation table. ## Switching later Moving from implicit to explicit is a schema change Prisma Migrate does not treat as a rename: the generated migration drops `_OrderToProduct` and creates `OrderItem`, so every existing link would be lost. To keep them: 1. Create the migration without applying it. 2. Before the `DROP TABLE`, add an `INSERT INTO "OrderItem" ("orderId", "productId") SELECT "A", "B" FROM "_OrderToProduct"`, giving new columns such as `quantity` a default. 3. Apply it and regenerate the client, whose nested writes now go through `items`. Choosing explicit from the start is cheap when a link plausibly carries data; for pure tags or categories the implicit form stays simpler.

  • What happens to an implicit relation's rows when a Product is deleted?
    Both foreign keys of the Prisma-managed table are created `ON DELETE CASCADE`, so the product's link rows vanish with it and every order silently loses that product. You cannot change this, because referential actions are rejected on implicit many-to-many relations. If order history must block the delete, model `OrderItem` explicitly and leave its product side required, which defaults to `Restrict`.
  • How do you move an existing implicit relation to an explicit model without losing links?
    The migration Prisma Migrate generates drops `_OrderToProduct` and creates `OrderItem`, so the links would be lost. Generate the migration without applying it, add an `INSERT ... SELECT "A", "B" FROM "_OrderToProduct"` into the new table before the drop, give new columns a default, then apply it and update client code to write through `items`.
  • Can an existing join table be used as an implicit relation after introspection?
    Only if it follows Prisma's conventions: a name made of an underscore plus both model names in alphabetical order joined by `To`, columns `A` and `B` referencing the first and second model, a unique index (or primary key) over both, and an index on `B`. A table that breaks these conventions, for example one with extra columns, has to be modelled as an explicit join model.

  • An implicit many-to-many has no table; Prisma joins the models in memory.
  • A quantity can be added to Prisma's implicit join table with an attribute.
  • onDelete: Restrict on an implicit relation stops sold products being deleted.
  • An explicit join model needs no back-relation lists on Order and Product.
  • Prisma Migrate keeps the rows when an implicit relation becomes explicit.