Query engine fails for querying implicit back relation

[sdnts]

Originally pointed out by @mikeslade on #114 (comment)

I set up fresh docker environments running ubuntu:latest and postgres:latest instances. Installed prisma2@2.0.0-alpha.87, binary version: 2ada5347b0000b7fe5171e41a4bec5b3d11c61e3

Here is the datamodel:

model User {
  id    String  @default(cuid()) @id @unique
  name  String
}

model Group {
  id    String  @default(cuid()) @id @unique
  name  String
  childGroups Group[] @relation(name: "ChildGroups")
  parent Group? @relation(name: "ChildGroups")
  type String?
  employee Employee[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  updatedBy User
}

model Employee {
  id    String  @default(cuid()) @id @unique
  name  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  updatedBy User
}

here is the seed:

import Photon from "@generated/photon";

export const photon = new Photon();

async function main() {
  const systemUser = await photon.users.create({
    data: {
      name: "Mike"
    }
  });

  if (systemUser) {
    await photon.groups.create({
      data: {
        name: "PARENT_GROUP",
        childGroups: {
          create: [
            {
              name: "CHILD_GROUP1",
              updatedBy: {
                connect: {
                  id: systemUser.id
                }
              }
            },
            {
              name: "CHILD_GROUP2",
              updatedBy: {
                connect: {
                  id: systemUser.id
                }
              }
            },
            {
              name: "CHILD_GROUP3",
              updatedBy: {
                connect: {
                  id: systemUser.id
                }
              }
            }
          ]
        },
        updatedBy: {
          connect: {
            id: systemUser.id
          }
        }
      }
    });

    // await photon.employees.create({
    //   data: {
    //     name: "John Smith",
    //     updatedBy: {
    //       connect: {
    //         id: systemUser.id
    //       }
    //     },
    //     group: {
    //       connect: {
    //         id: group1.id
    //       }
    //     }
    //   }
    // });

    // await photon.employees.create({
    //   data: {
    //     name: "Jane Doe",
    //     updatedBy: {
    //       connect: {
    //         id: systemUser.id
    //       }
    //     },
    //     group: {
    //       connect: {
    //         id: group2.id
    //       }
    //     }
    //   }
    // });
  }
}

main()
  .catch(e => console.error(e))
  .finally(async () => {
    await photon.disconnect();
  });

Running prisma2 dev after migrating and seeding the db results in the following error:

Error in script {message: "Error in Photon: ↵", stack: "Error↵    at Photon.request (/usr/lib/node_modules…ode_modules/prisma2/build/photon-worker.js:22:18)"} photon.users.findMany({
  select: {
    id: true,
    name: true,
    group: true,
    employee: true
  }
})

Interestingly, it doesn't seem to have recognized that there is a 1:n relationship between Users and Groups/Employees.

If I change the datamodel to the following, then it works without error

model User {
  id    String  @default(cuid()) @id @unique
  name  String
  updatedGroups Group[]
  updatedEmployees Employee[]
}

model Group {
  id    String  @default(cuid()) @id @unique
  name  String
  childGroups Group[] @relation(name: "ChildGroups")
  parent Group? @relation(name: "ChildGroups")
  type String?
  employee Employee[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  updatedBy User
}

model Employee {
  id    String  @default(cuid()) @id @unique
  name  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  updatedBy User
}

[pantharshit00]

I can confirm this.

[sdnts]

Here's a reproduction repo with SQLite: https://github.com/madebysid/prisma2-implicit-relations

[timsuchanek]

For this query:

  photon query {
  photon   findManyUser {
  photon     id
  photon     name
  photon     group {
  photon       id
  photon       name
  photon       type
  photon       createdAt
  photon       updatedAt
  photon     }
  photon     employee {
  photon       id
  photon       name
  photon       createdAt
  photon       updatedAt
  photon     }
  photon   }
  photon } +0ms

We get this error: Expected at most 1 item for 'group', got 4

[mavilein]

I can't reproduce this on latest alpha. I used the reproduction repo provided by @madebysid and failed when seeding the database. I ran into the following error: Error in connector: Violating a relation GroupToUser between Group and User.
This is the right behavior given the prisma schema. The relation between group and user is a required one to one relation because there's a implicit singular back relation field on User to Group. The seed script tries to create multiple groups that are all connected to the same user. However this is not possible as a user may not be connected to multiple groups because it is a one to one relation.

[sdnts]

@mavilein Based on our discussion, I'll clarify behaviour here as well.

Quoting https://github.com/prisma/specs/tree/master/prisma-schema-language#one-to-one-11-relationships (visit link for context)

You may omit either User.customer or Customer.user and the relationship will remain intact. This makes either the back-relation or the forward-relation optional. If one side of the relation is missing, Prisma implies the field name based on the name of the model it is pointing to.

Following the spec above, in the original schema, an implicit field called group would be added to the User model that is a 1:1 relation to Group. So what the seed script is doing is illegal. (connecting multiple groups to the same user), and errors out like @mavilein says.

I believe the confusion comes from the difference in behaviour between Prisma 2 and Prisma 1. Implicit relations in Prisma 2 are treated as 1:1 relations. This is different from Prisma 1, where these implicit relations were treated as 1:N.

So this example behaves as expected. In order to achieve what @mikeslade expects (the same behaviour as Prisma 1), the resolution he provided (adding a new field to User) is the right way to do this. This field would override the implicit relation created by Prisma 2.

I'm closing this as this is all expected behaviour. Hope this makes it clear @mikeslade. Please feel free to let me know if something I've said is unclear!