Disambiguating relations incorrectly auto formatting

[keckrich]

Bug description

When creating a model that has disambiguous relations with a relational db the formatter prisma format will generate the @relation(fields: [authorId], references: [id]) automatically for each field, but it will not automatically add the name argument.

See below for examples. Scenario 3 is how I normally make prisma files

How to reproduce

Scenario 1:

// NOTE: Before running prisma format
model User {
  id           Int     @id @default(autoincrement())
  name         String?
  writtenPosts Post[]
  pinnedPosts   Post[]
}

model Post {
  id         Int     @id @default(autoincrement())
  title      String?
}

currently generates:

// NOTE: after running prisma format
model User {
  id           Int     @id @default(autoincrement())
  name         String?
  writtenPosts Post[]
  pinnedPosts  Post[]
}

model Post {
  id     Int     @id @default(autoincrement())
  title  String?
  User   User?   @relation(fields: [userId], references: [id])
  userId Int?
  User   User?   @relation(fields: [userId], references: [id])
  userId Int?
}

Scenario 2:

// NOTE: Before running prisma format
model User {
  id           Int     @id @default(autoincrement())
  name         String?
  writtenPosts Post[] @relation("writtenPostsWithACustomName")
  pinnedPosts  Post[] @relation("pinnedPosts")
}

model Post {
  id     Int     @id @default(autoincrement())
  title  String?
}

currently generates:

// NOTE: After running prisma format
model User {
  id           Int     @id @default(autoincrement())
  name         String?
  writtenPosts Post[]  @relation("writtenPostsWithACustomName")
  pinnedPosts  Post[]  @relation("pinnedPosts")
}

model Post {
  id     Int     @id @default(autoincrement())
  title  String?
  User   User?   @relation(fields: [userId], references: [id])
  userId Int?
  User   User?   @relation(fields: [userId], references: [id])
  userId Int?
}

Scenario 3:

// NOTE: Before running prisma format
model User {
  id           Int     @id @default(autoincrement())
  name         String?
}

model Post {
  id     Int     @id @default(autoincrement())
  title  String?
  writtenBy   User?
  pinnedBy   User?
}

currently generates:

// NOTE: After running prisma format
model User {
  id   Int     @id @default(autoincrement())
  name String?
  Post Post[]
  Post Post[]
}

model Post {
  id       Int     @id @default(autoincrement())
  title    String?
  writtenBy User?   @relation(fields: [userId], references: [id])
  pinnedBy User?   @relation(fields: [userId], references: [id])
  userId   Int?
  userId   Int?
}

Scenario 4:

// NOTE: Before running prisma format
model User {
  id            Int     @id @default(autoincrement())
  name          String?
}

model Post {
  id              Int     @id @default(autoincrement())
  title           String?
  writtenBy       User?   @relation("writtenPostsWithACustomName")
  pinnedBy        User?   @relation("pinnedBy")
}

currently generates:

// NOTE: After running prisma format
model User {
  id   Int     @id @default(autoincrement())
  name String?
  Post Post[]
  Post Post[]
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String?
  writtenBy User?   @relation("writtenPostsWithACustomName", fields: [userId], references: [id])
  pinnedBy  User?   @relation("pinnedBy", fields: [userId], references: [id])
  userId    Int?
  userId    Int?
}

Expected behavior

Scenario 1:

model User {
  id           Int     @id @default(autoincrement())
  name         String?
  writtenPosts Post[]  @relation("WrittenPosts")
  pinnedPosts  Post[]  @relation("PinnedPost")
}

model Post {
  id                 Int     @id @default(autoincrement())
  title              String?
  WrittenPostsUser   User?   @relation("WrittenPosts", fields: [writtenPostsUserId], references: [id])
  writtenPostsUserId Int?
  PinnedPostsUser    User?   @relation("PinnedPost", fields: [pinnedPostsUser], references: [id])
  pinnedPostsUserId  Int?
}

Scenario 2:

model User {
  id           Int     @id @default(autoincrement())
  name         String?
  writtenPosts Post[]  @relation("writtenPostsWithACustomName")
  pinnedPosts  Post[]  @relation("pinnedPosts")
}

model Post {
  id                                Int     @id @default(autoincrement())
  title                             String?
  writtenPostsWithACustomNameUser   User?   @relation("writtenPostsWithACustomName", fields: [writtenPostsWithACustomNameUserId], references: [id])
  writtenPostsWithACustomNameUserId Int?
  pinnedPostsUser                   User?   @relation("pinnedPosts", fields: [pinnedPostsUserId], references: [id])
  pinnedPostsUserId                 Int?
}

Scenario 3:

model User {
  id            Int     @id @default(autoincrement())
  name          String?
  writtenByPost Post[]  @relation("writtenBy")
  pinnedByPost  Post[]  @relation("pinnedBy")
}

model Post {
  id              Int     @id @default(autoincrement())
  title           String?
  writtenBy       User?   @relation("writtenBy", fields: [writtenByUserId], references: [id])
  pinnedBy        User?   @relation("pinnedBy", fields: [pinnedByUserId], references: [id])
  writtenByUserId Int?
  pinnedByUserId  Int?
}

Scenario 4:

model User {
  id            Int     @id @default(autoincrement())
  name          String?
  writtenByPost Post[]  @relation("writtenPostsWithACustomName")
  pinnedByPost  Post[]  @relation("pinnedBy")
}

model Post {
  id                                Int     @id @default(autoincrement())
  title                             String?
  writtenBy                         User?   @relation("writtenPostsWithACustomName", fields: [writtenPostsWithACustomNameUserId], references: [id])
  pinnedBy                          User?   @relation("pinnedBy", fields: [pinnedByUserId], references: [id])
  writtenPostsWithACustomNameUserId Int?
  pinnedByUserId                    Int?
}

Prisma information

included in previous sections

Environment & setup

• OS: macOS
• Database: PostgreSQL
• Node.js version: v18.9.0

Prisma Version

Environment variables loaded from .env
prisma                  : 5.13.0
@prisma/client          : 5.13.0
Computed binaryTarget   : darwin-arm64
Operating System        : darwin
Architecture            : arm64
Node.js                 : v18.9.0
Query Engine (Node-API) : libquery-engine b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine           : schema-engine-cli b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm             : @prisma/prisma-schema-wasm 5.13.0-23.b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b
Default Engines Hash    : b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b
Studio                  : 0.500.0

Sorry for the long post.

[Ivor808]

I am also able to repo this. We are unable to auto format our schema when using disambiguating relations. It attempts to insert without the @relation tag. This looks to be previously filed here: #18599

[janpio]

Generally it seems that prisma format currently does not handle multiple relations at all, and generates a schema that is supposed to be fixed by the user. That it does not take existing relation "names" into account to craft more correct schemas is a shame though (Scenarios 2 and 4), that information is in theory present and could be used.

I am marking this as confirmed as I can confirm the behavior you observed, but that might potentially still not mean we can or want to implement all the suggestions as given and prefer to let the user fix this manually.

---

So to summarize the suggestions:

1. In Scenario 1, you suggest Prisma to "invent" the @relation names so the relations can be disambiguated, and adapt the created scalar relation field names so that they do not overlap each other.
   
2. In Scenario 2, you suggest Prisma to use the existing relation names (from the other model) to disambiguate, and adapt the created scalar relation field names so that they do not overlap.
   
3. In Scenario 3, you suggest Prisma to "invent" the @relation names so the relations can be disambiguated, and adapt the created (back) relation names so that they do not overlap each other.
   
4. In Scenario 4, you suggest Prisma to use the existing relation names (from the other model) to disambiguate, and adapt the created (back) relation names so that they do not overlap each other.
   

All correct?

[janpio]

Some of your suggestion loose the optionality via ? on one of the two relations in your suggestions (scenarios 1 and 2). Should that also happen automatically somehow or is that a manual change that just snuck in?

[janpio]

@Ivor808 #18599, that you linked to, is essentially a bug report for what I wrote above:

That it does not take existing relation "names" into account to craft more correct schemas is a shame though (Scenarios 2 and 4), that information is in theory present and could be used.

Fixing this should at least partially improve scenarios 2 and 4.

[keckrich]

Generally it seems that prisma format currently does not handle multiple relations at all, and generates a schema that is supposed to be fixed by the user. That it does not take existing relation "names" into account to craft more correct schemas is a shame though (Scenarios 2 and 4), that information is in theory present and could be used.

...

All correct?

Yes. That is a good summary

[keckrich]

Some of your suggestion loose the optionality via ? on one of the two relations in your suggestions (scenarios 1 and 2). Should that also happen automatically somehow or is that a manual change that just snuck in?

Good catch, it was a "manual change that just snuck in" I have fixed the examples

[Ivor808]

Yep in general, it should act how defining foreign keys in SQL does. AKA we define the foreign key and unique naming, and format is responsible for propagating that onto the referenced table. In the case of posts, the user is responsible for adding and naming each relation. Format can take those and put them onto the referenced table with the correct naming.

Note: The relation naming can be the same it looks like (reference the model below). I checked, and the relation name is just used to generate the FK name on the original table (in this case brand and listing application). So maybe format takes the model name and appends it to the relation name in the parent model (in this case user).

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  description   String?
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
  userType      UserType  @default(BASIC)
  platforms     String?
  links         Json?

  BrandCreatedBy Brand[] @relation("creatingUser")
  BrandUpdatedBy Brand[] @relation("updatingUser")

  ListingCreatedBy Listing[] @relation("creatingUser")
  ListingUpdatedBy Listing[] @relation("updatingUser")

  ListingsApplicationCreatedBy    ListingApplication[] @relation("creatingUser")
  ListingsApplicationUpdatedBy    ListingApplication[] @relation("updatingUser")
  ListingsApplicationApplyingUser ListingApplication[] @relation("applyingUser")
}