Prisma1: Enhance database structure for many-to-many relations with additional fields

[beeplin]

Is your feature request related to a problem? Please describe.

I am new to Prisma, so it might be already done, or maybe even not feasible to achieve it, but here is the idea:)

Currently Prisma handles many-to-many relations without additional fields very well. Say we have:

type A {
  id: ID! @unique
  as: [B!]!
}
type B {
  id: ID! @unique
  bs: [A!]!
}

Then after prisma deploy we have three tables in the DB: A, B and _AToB. Perfect.

But if we want a many-to-many relation with extra fields, it seems that the only way is to use a intermediate type:

type A {
  id: ID! @unique
  abs: [AB!]!
}
type B {
  id: ID! @unique
  abs: [AB!]!
}
type AB {
  id: ID! @unique
  a: A!
  b: B!
  moreField: xxx
}

Then in the database we will see five tables: A, B, 'AB', _AtoAB, _BtoAB.

And during queries it has to jump twice A -> _AtoAB -> AB to get the relation, which is relatively slow.

Describe the solution you'd like

In old days for these many-to-many relations with extra info, we just create ONE cross table: AB: {Aid, Bid, extraField1, extraField2, ...}

Is it possible to automatically create such ONE cross table in prisma, so we still have three tables: A, B and AB.?

(I believe this is related to #746)

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 10 days if no further activity occurs. Thank you for your contributions.

[chris-rock]

I also think, 3 tables should be enough and would be much more efficient. Are there any estimates, when this improvement is coming to Prisma?

Related to https://github.com/prisma/prisma/issues/3406

[Fh-abeyer]

I need this to happen before I can fully use Prisma.

[omidahourai]

This is bottlenecking me too, and caused my docker postgres to crash when I tried to remove the relationship... spent a day trying to resolve it but felt defeating and makes me weary about the lack of control.

[gotexis]

+1, any major backend framework should support this, and indeed they do.