Limit implicit m-n relations to singular primary keys only

[albertoperdomo]

Problem

The implicit syntax for m-n relations today does not support all possible cases. Certain edge cases are not yet supported, making the Query Engine crash. To support these additional edge cases we would need to do additional engineering work and add more complexity to the m-n relation syntax.

Solution

We will reduce the complexity by dropping support for the composite keys edge cases.

Referencing tables without a single unique identifier will no longer be supported in the implicit syntax. Edge cases that will no longer supported with the implicit syntax:

Referenced model has @unique field, but no @id:

model Post {
  slug       Int        @unique // no @id
  categories Category[] @relation(references: [id])
}
model Category {
  id    Int    @id @default(autoincrement())
  posts Post[] @relation(references: [slug])
}

Referenced model has composite keys @@id:

model User {
  firstName     String
  lastName      String
  authoredPosts Post[] @relation(references: [id])

  @@id([firstName, lastName])
}

model Post {
  id      Int    @id
  authors User[] @relation(references: [firstName, lastName])
}

Referenced model has @@unique, but not @id:

model User {
  firstName     String
  lastName      String
  authoredPosts Post[] @relation(references: [id])

  @@unique([firstName, lastName])
}

model Post {
  id      Int    @id
  authors User[] @relation(references: [firstName, lastName])
}

Simplifying the Syntax

Given that only a subset of the cases is now supported, we can simplify the syntax, and the use of @relation to specify the foreign key field is no longer required.

Old syntax using @relation:

model Post {
  id         Int        @id @default(autoincrement())
  categories Category[] @relation(references: [id])
}
model Category {
  id    Int    @id @default(autoincrement())
  posts Post[] @relation(references: [id])
}

New syntax without @relation:

model Post {
  id         Int        @id @default(autoincrement())
  categories Category[]
}
model Category {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

Diff:

model Post {
  id         Int        @id @default(autoincrement())
-  categories Category[] @relation(references: [id])
+  categories Category[]
}
model Category {
  id    Int    @id @default(autoincrement())
-  posts Post[] @relation(references: [id])
+  posts Post[]
}

@relation would remain as optional feature to disambiguate a relation (specs). Example:

model Blog {
    id         Int @id
    author     User[] @relation("Authorship")
    subscriber User[] @relation("Subscription")
}

model User  {
    id           Int @id
    authorOf     Blog[] @relation("Authorship")
    subscribedTo Blog[] @relation("Subscription")
}

In the case of n-m self-relations, the use of @relations("Name") for disambiguation will still be required. Example:

model User {
  friends  User[] @relation("Friends")
  friendsOf User[] @relation("Friends")
}

Scope

• Limit implicit syntax to relations referencing single primary keys
• Simplify the syntax by removing the need for @relation
• Update documentation

Alternatives

• Expand feature to support relations w/o primary or composite keys
• Limit feature to support w/ primary or composite keys (make what we have today work without errors)
• Remove the implicit syntax for m-n relations

Additional context

Internal: https://www.notion.so/prismaio/Support-implicit-syntax-for-m-n-relations-w-composite-keys-865a5ae609d34f86a4a62e2f2410d866

[mavilein]

related: prisma/prisma-engines#566