Two not nullable relations between two the same models and creating

[yarikos]

Let's say we have User and Organisation. Each User is a member of an Organisation and each Organisation has an owner. Both relations are not nullable.

This could be implemented like with this schema.prisma:

model User {
  id           String @default(cuid()) @id

  organisation Organisation @relation("OrgMembers", fields: [orgId], references: [id])
  orgId        String

  Organisation Organisation[] @relation("Owner")
}

model Organisation {
  id   String @default(cuid()) @id

  User  User[]  @relation("OrgMembers")

  owner   User   @relation("Owner", fields: [ownerId], references: [id])
  ownerId String
}

How to create a new pair of User and Organisation with prisma?

As we realized #3483, this could not be made by the nested writes.

Because of "NOT NULL" constraints for both relations, this issue looks similar to the chicken-egg problem.

But we can use the Transaction API and create both records in one transaction.

But one more thing before that – we also have to tweak the time when foreign constraints are actually checked. Just like that:

ALTER TABLE "public"."Organisation"
    ALTER CONSTRAINT "Organisation_ownerId_fkey" DEFERRABLE INITIALLY DEFERRED

ALTER TABLE "public"."User"
    ALTER CONSTRAINT "User_orgId_fkey" DEFERRABLE INITIALLY DEFERRED

And now let's try to create both User and Organisation in one transaction:

const userId = cuid();
const orgId = cuid();
const writeUser = prisma.user.create({
  data: {
    id: userId,
    organisation: {
      connect: {
        id: orgId,
      }
    }
  }
});
const writeOrg = prisma.organisation.create({
  data: {
    id: orgId,
    owner: {
      connect: {
        id: userId
      }
    }
  }
});
await ctx.db.$transaction([writeUser, writeOrg]);

Expected Result: both records created.

Actual Result: the prisma tries to ensure, that the Organisation already exists at the time of the creation of a User (I can observe this in the prisma:query logs).

And the error:

PrismaClientKnownRequestError:
       |        Invalid `prisma.user.create()` invocation in
       |        .../api/src/graphql/Mutation/signUp.ts:80:51
       |          Query interpretation error. Error for binding '1': AssertionError("[Query Graph] Expected a valid parent ID to be present for a nested connect on a one-to-many relation.")
       |            at PrismaClientFetcher.request (.../api/node_modules/@prisma/client/runtime/index.js:1:211015)
       |            at processTicksAndRejections (internal/process/task_queues.js:97:5) {
       |          code: 'P2016',
       |          meta: {
       |            details: `Error for binding '1': AssertionError("[Query Graph] Expected a valid parent ID to be present for a nested connect on a one-to-many relation.")`

Question: – is there is a way to deal with this case without falling to using raw SQL?

[ryands17]

Hey @yarikos 👋
Yes I think the required inter-dependency here would make it impossible to use in a Prisma transaction, so a transactional raw query would be the only way to go here.

It would be great if you could create a feature request here with the above use case especially for deferreble constraints :)

[yarikos]

Thanks, @ryands17.
Here is the feature request #3502