Improve error messages for missing back-relation field

[nikolasburk]

With this Prisma schema:

model Event {
  id        Int      @id @default(autoincrement())
  owner     Person   @relation(name: "EventOwners")
  recipient Person?  @relation(name: "EventRecipients")
}

model Person {
  id        Int      @id @default(autoincrement())

}

I get this error VS Code:

Error validating model "Event": Automatic related field generation would cause a naming conflict. Please add an explicit opposite relation field.

The solution here is fairly straightforward (i.e. adding back-relation fields) but it's not clear from the error message itself.

An improved error would be a bit more actionable, e.g.:

Please add the opposing relation field on the other model. 

Or even (if possibl):

Please add the opposing relation field on the `Person` model. 

[divyenduz]

Internal note: dropping from current sprint

[keepforever]

With the following prisma Schema:

model User {
  id      Int      @default(autoincrement()) @id
  email   String   @unique
  name    String?
  head  Note[]
  torso Note[]
}

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

I get the following error message:

Error validating model "User": Ambiguous relation detected. The fields head and torso in model User both refer to Note. Please provide different relation names for them by adding @relation(<name>).

However, there doesn't appear to be any examples of this sort of thing in the documentation.

What would I change about this schema to have two (or more) fields on User that relate to Note?

[nikolasburk]

Hey @keepforever, Prisma always requires both relation fields to be present per relation. Since you want to create two relations, you need to have two relation fields on each side:

model User {
  id    Int     @default(autoincrement()) @id
  email String  @unique
  name  String?
  head  Note[]  @relation("head")
  torso Note[]  @relation("torso")
}

model Note {
  id        Int    @default(autoincrement()) @id
  title     String
  content   String
  userTorso User   @relation("head", fields: [userId], references: [id])
  userHead  User   @relation("torso", fields: [userId], references: [id])
  userId    Int
}

You can find more info in the docs.

[keepforever]

Thanks, @nikolasburk! That did the trick!

I looked through the docs and didn't see any examples of this type of scenario, so, might be a good example to add?

I'm a front end dev not too familiar with creating data models. I'm was torn between making a "configurable" Note model or, simply creating more tables and having multiple one to many relations like:

head HeadNote[] torso TorsoNote[]

where HeadNote[] and TorsoNote[] have essentially the same properties as Note did.

The one "wonky" thing I noticed while playing around with prisma-nexus, is that, with the single Note model that services both head and torso I had to run a mutation with multiiple connect fields:

mutation MyCreateNote {
  createOneNote(
    data: {
      title: "Broken arm?"
      content: "Today, I fell down the stairs. I think I broke my arm on the way down."
      userHead: { connect: { id: 1 } }
      userTorso: { connect: { id: 1 } }
    }
  ) {
    id
    content
    title
  }
}

Would you recommend making more models or is having multi-faceted Note not such a bad idea?

[richardwardza]

I'm trying something very similar and it's not working for me. I have a user with a profile picture and an avatar picture. Using what @nikolasburk posted above, I did some basic changes and have:

model User {
  id         Int    @default(autoincrement()) @id
  name       String
  profilePic Image? @relation("ProfilePicture")
  avatar     Image? @relation("AvatarImage")
}

model Image {
  id             Int    @default(autoincrement()) @id
  name           String
  userProfilePic User?   @relation("ProfilePicture", fields: [userId], references: [id])
  userAvatar     User?   @relation("AvatarImage", fields: [userId], references: [id])
  userId         Int?
}

If I now try:

const user = await prisma.user.create({
    data: {
        name: "user 1",
        avatar: { create: { name: "avatar pic" } }
    },
    include: {
        profilePic: true,
        avatar: true
    }
})

I get the following back: (both profilePic and avatar linked to the same record)

user:  {
  id: 6,
  name: 'user 1',
  profilePic: { id: 7, name: 'avatar pic', userId: 6 },
  avatar: { id: 7, name: 'avatar pic', userId: 6 }
}

If I try create both profilePic and avatar at the same time:

const user = await prisma.user.create({
    data: {
        name: "user 1",
        profilePic: { create: { name: "profile pic" } },
        avatar: { create: { name: "avatar pic" } }
    },
    include: {
        profilePic: true,
        avatar: true
    }
})

I get:

Error:  PrismaClientKnownRequestError: 
Invalid `prisma.user.create()` invocation in xxxx/src/play.ts:20:32
  18 
  19 
→ 20 const user = await prisma.user.create(

Unique constraint failed on the fields: (`userId`)
    at PrismaClientFetcher.request (xxxxx/node_modules/@prisma/client/src/runtime/getPrismaClient.ts:644:17)
    at processTicksAndRejections (internal/process/task_queues.js:97:5) {
  code: 'P2002',
  meta: { target: [ 'userId' ] }
}

My db looks like:

CREATE TABLE "play"."User" (
  "id" int4 NOT NULL DEFAULT nextval('"play"."User_id_seq"'::regclass),
  "name" text COLLATE "pg_catalog"."default" NOT NULL,
  CONSTRAINT "User_pkey" PRIMARY KEY ("id")
)
;

CREATE TABLE "play"."Image" (
  "id" int4 NOT NULL DEFAULT nextval('"play"."Image_id_seq"'::regclass),
  "name" text COLLATE "pg_catalog"."default" NOT NULL,
  "userId" int4,
  CONSTRAINT "Image_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "Image_userId_fkey" FOREIGN KEY ("userId") REFERENCES "play"."User" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT "Image_userId_fkey1" FOREIGN KEY ("userId") REFERENCES "play"."User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
)
;

CREATE UNIQUE INDEX "Image_userId" ON "play"."Image" USING btree (
  "userId" "pg_catalog"."int4_ops" ASC NULLS LAST
);

[richardwardza]

Right - Changing my Image model to:

model User {
  id         Int    @default(autoincrement()) @id
  name       String
  profilePic Image? @relation("ProfilePicture")
  avatar     Image? @relation("AvatarImage")
}

model Image {
  id             Int    @default(autoincrement()) @id
  name           String
  userProfilePic User?  @relation("ProfilePicture", fields: [profileUserId], references: [id])
  userAvatar     User?  @relation("AvatarImage", fields: [avatarUserId], references: [id])
  profileUserId  Int?
  avatarUserId   Int?
}

does the trick!

const user = await prisma.user.create({
    data: {
        name: "user 1",
        profilePic: { create: { name: "profile pic" } },
        avatar: { create: { name: "avatar pic" } }
    },
    include: {
        profilePic: true,
        avatar: true
    }
})

now returns:

user:  {
  id: 1,
  name: 'user 1',
  profilePic: { id: 2, name: 'profile pic', profileUserId: 1, avatarUserId: null },
  avatar: { id: 1, name: 'avatar pic', profileUserId: null, avatarUserId: 1 }
}

Edit 1: Unfortunately it looks like if you want to have Image linked to multiple tables then you're going to need an extra column for each table....

Edit 2: This is in the docs under disambiguating-relations: https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-schema/relations#disambiguating-relations

[richardwardza]

To finish this off, my final solution is to reverse the references and store the id on the User tables and not the Image table:

model User {
  id         Int    @default(autoincrement()) @id
  name       String
  profilePic Image?  @relation("ProfilePicture", fields: [profileImageId], references: [id])
  avatar     Image?  @relation("AvatarImage", fields: [avatarImageId], references: [id])
  profileImageId  Int? @map("userProfilePic")
  avatarImageId   Int? @map("userAvatar")
}

model Image {
  id             Int    @default(autoincrement()) @id
  name           String
  userProfilePic User? @relation("ProfilePicture")
  userAvatar     User? @relation("AvatarImage")
 }

My database looks a lot saner:

CREATE TABLE "play"."User" (
  "id" int4 NOT NULL DEFAULT nextval('"play"."User_id_seq"'::regclass),
  "name" text COLLATE "pg_catalog"."default" NOT NULL,
  "userAvatar" int4,
  "userProfilePic" int4,
  CONSTRAINT "User_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "User_userAvatar_fkey" FOREIGN KEY ("userAvatar") REFERENCES "play"."Image" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT "User_userProfilePic_fkey" FOREIGN KEY ("userProfilePic") REFERENCES "play"."Image" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE UNIQUE INDEX "User_userAvatar" ON "play"."User" USING btree (
  "userAvatar" "pg_catalog"."int4_ops" ASC NULLS LAST
);

CREATE UNIQUE INDEX "User_userProfilePic" ON "play"."User" USING btree (
  "userProfilePic" "pg_catalog"."int4_ops" ASC NULLS LAST
);

CREATE TABLE "play"."Image" (
  "id" int4 NOT NULL DEFAULT nextval('"play"."Image_id_seq"'::regclass),
  "name" text COLLATE "pg_catalog"."default" NOT NULL,
  CONSTRAINT "Image_pkey" PRIMARY KEY ("id")
);

My query:

const user = await prisma.user.create({
    data: {
        name: "user 1",
        profilePic: { create: { name: "profile pic 1" } },
        avatar: { create: { name: "avatar pic" } }
    },
    include: {
        profilePic: true,
        avatar: true
    }
})

returns:

user:  {
  id: 1,
  name: 'user 1',
  profileImageId: 1,
  avatarImageId: 2,
  profilePic: { id: 1, name: 'profile pic 1' },
  avatar: { id: 2, name: 'avatar pic' }
}

This now looks like the ramblings of a madman but leaving them here to hopefully help anyone coming after.

[jhlabs]

I have read through the docs and the previous examples, closest the one by @nikolasburk, but cannot find a solution to my problem: I want to model an explicit many-to-many self-relationship. I need an explicit relationship since I also want to store the order of related fields in my many-to-many table. My schema looks currently simplified like this:

model Task {
    id              String     @id
    parents         Task[]     @relation("ChildRelationship")
    children        Task[]     @relation("ParentRelationship")
}

model TaskRelation {
    parent          Task       @relation("ParentRelationship", fields: [parentId], references: [id])
    parentId        String
    child           Task       @relation("ChildRelationship", fields: [childId], references: [id])
    childId         String
    order           Int
    @@id([parentId, childId])
}

When I try to run this migration I get the following error:

Error: P1012

error: Error validating model "Task": Automatic related field generation would cause a naming conflict. Please add an explicit opposite relation field.

What am I missing in order to make this work? Thanks!

[janpio]

Please open a new issue or discussion for your questions @jhlabs - thanks.

[janpio]

The original Prisma schema from @nikolasburk:

model Event {
  id        Int      @id @default(autoincrement())
  owner     Person   @relation(name: "EventOwners")
  recipient Person?  @relation(name: "EventRecipients")
}

model Person {
  id        Int      @id @default(autoincrement())

}

Today leads to these validation errors:

PS C:\Users\Jan\Documents\throwaway\1483> npx prisma validate
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma

Error: Prisma schema validation - (validate wasm)
Error code: P1012
error: Error parsing attribute "@relation": The given constraint name `Event__fkey` has to be unique in the following namespace: on model `Event` for primary key, indexes, unique constraints and foreign keys. Please provide a different name using the `map` argument.
  -->  prisma\schema.prisma:12
   | 
11 |   id        Int      @id @default(autoincrement())
12 |   owner     Person   @relation(name: "EventOwners")
13 |   recipient Person?  @relation(name: "EventRecipients")
   | 
error: Error validating field `owner` in model `Event`: The relation field `owner` on model `Event` is missing an opposite relation field on the model `Person`. Either run `prisma format` or add it manually.
  -->  prisma\schema.prisma:12
   | 
11 |   id        Int      @id @default(autoincrement())
12 |   owner     Person   @relation(name: "EventOwners")
13 |   recipient Person?  @relation(name: "EventRecipients")
   | 
error: Error parsing attribute "@relation": The given constraint name `Event__fkey` has to be unique in the following namespace: on model `Event` for primary key, indexes, unique constraints and foreign keys. Please provide a different name using the `map` argument.
  -->  prisma\schema.prisma:13
   | 
12 |   owner     Person   @relation(name: "EventOwners")
13 |   recipient Person?  @relation(name: "EventRecipients")
14 | }
   | 
error: Error validating field `recipient` in model `Event`: The relation field `recipient` on model `Event` is missing an opposite relation field on the model `Person`. Either run `prisma format` or add it manually.
  -->  prisma\schema.prisma:13
   | 
12 |   owner     Person   @relation(name: "EventOwners")
13 |   recipient Person?  @relation(name: "EventRecipients")
14 | }
   | 

Validation Error Count: 4
[Context: validate]

Prisma CLI Version : 5.14.0

The relevant error here today seems to be:

error: Error validating field `owner` in model `Event`: The relation field `owner` on model `Event` is missing an opposite relation field on the model `Person`. Either run `prisma format` or add it manually.

This probably satisfies Niko's original request for a improved error message.

[janpio]

To finish the fixing of the schema with these new error messages:

Using prisma format on that schema leads to these validation errors:

PS C:\Users\Jan\Documents\throwaway\1483> npx prisma format
Prisma schema loaded from prisma\schema.prisma

Error: Prisma schema validation - (validate wasm)
Error code: P1012
error: Field "personId" is already defined on model "Event".
  -->  prisma\schema.prisma:15
   | 
14 |   personId  Int
15 |   personId  Int?
   | 
error: Field "Event" is already defined on model "Person".
  -->  prisma\schema.prisma:22
   | 
21 |   Event Event[]
22 |   Event Event[]
   | 

Validation Error Count: 2
[Context: validate]

Prisma CLI Version : 5.14.0

Via our VS Code extension's "Format Document" functionality we can see the Prisma schema this was tried to turn into:

model Event {
  id        Int     @id @default(autoincrement())
  owner     Person  @relation(name: "EventOwners", fields: [personId], references: [id])
  recipient Person? @relation(name: "EventRecipients", fields: [personId], references: [id])
  personId  Int
  personId  Int?
}

model Person {
  id Int @id @default(autoincrement())

  Event Event[]
  Event Event[]
}
This is missing the relation names in the added back relations  Event Event[]. This is a bug tracked in https://github.com/prisma/prisma/issues/18599. With that fixed, the result would be:

model Event {
  id        Int     @id @default(autoincrement())
  owner     Person  @relation(name: "EventOwners", fields: [personId], references: [id])
  recipient Person? @relation(name: "EventRecipients", fields: [personId], references: [id])
  personId  Int
  personId  Int?
}

model Person {
  id Int @id @default(autoincrement())

  Event Event[] @relation(name: "EventOwners")
  Event Event[] @relation(name: "EventRecipients")
}

Now the validation errors are clearer:

PS C:\Users\Jan\Documents\throwaway\1483> npx prisma format
Prisma schema loaded from prisma\schema.prisma

Error: Prisma schema validation - (validate wasm)
Error code: P1012
error: Field "personId" is already defined on model "Event".
  -->  prisma\schema.prisma:15
   | 
14 |   personId  Int
15 |   personId  Int?
   | 
error: Field "Event" is already defined on model "Person".
  -->  prisma\schema.prisma:22
   | 
21 |   Event Event[] @relation(name: "EventOwners")
22 |   Event Event[] @relation(name: "EventRecipients")
   | 

Validation Error Count: 2
[Context: validate]

Prisma CLI Version : 5.14.0

This can easily manually be fixed by renaming the fields, to:

model Event {
  id        Int     @id @default(autoincrement())
  owner     Person  @relation(name: "EventOwners", fields: [personId], references: [id])
  recipient Person? @relation(name: "EventRecipients", fields: [personId2], references: [id])
  personId  Int
  personId2  Int?
}

model Person {
  id Int @id @default(autoincrement())

  Event Event[] @relation(name: "EventOwners")
  Event2 Event[] @relation(name: "EventRecipients")
}

(Of course in reality ownerId and recipientId etc might be better names)

[nikolasburk]

Sounds good, thanks @janpio 🙏