Possibility to create two entities in one transaction with cross- and not nullable relations.

[yarikos]

Problem

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.

How the solution might be look like?

// `cuid` allows us to generate collision free IDs before the actual records creating and use them in relations
const userId = cuid();
const orgId = cuid();

// use generated IDs and DO NOT executa the query  (no await below):
const writeUser = prisma.user.create({
  data: {
    id: userId,
    organisation: {
      connect: {
        id: orgId,
      }
    }
  }
});
// the same for `organisation` and again without `await`
const writeOrg = prisma.organisation.create({
  data: {
    id: orgId,
    owner: {
      connect: {
        id: userId
      }
    }
  }
});

// execute both writes in a single transaction to give the DB a chance to check not-null and foreing-keys constraints in the end of the commit, not after each record
await prisma.$transaction([writeUser, writeOrg]);

Why the solution doesn't work now?

1. by default the FOREIGN KEY CONSTRAINT is conducted instantly after insertion. Even if the insertion is within a transaction.
   This is how it's being overcome in "raw" Postgres:

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

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

prisma doesn't allow us to adjust this behavior.

2. even if the constraint is made deferrable via direct SQL (bypassing prisma), the code still doesn't work, because prisma checking the existence of foreign key before insertion. As a result – AssertionError with Expected a valid parent ID to be present for a nested connect on a one-to-many relation.

Suggested solution

1. possibility to tweak foreign key constants like DEFERRABLE in Postgres

[Pat-Ayres]

we have a similar case where allowing DEFERRABLE constraints in Postgres would be helpful as well. we have a table with a priority order that needs to be unique and adjustable.

model Contact {
  id                          String           @id @default(cuid())
  name                    String
  relationshipType   String
  priority                   Int
  @@unique([priority], name: "person_contacts_priority_unique_constraint") 
}

without being able to make the unique constraint on Priority DEFERRABLE we cannot swap priorities in a single mutation, for now I think we will have to do without the unique constraint in the database and rely on code to manage uniqueness.

[robindiddams]

this would be really helpful too for seeding a db, we have relations all over the place and cant seed a complicated test without disabling the foreign key constraints

[yume-chan]

I want to help implement this feature, will Prisma team accept a PR? #14064

[janpio]

The feature is now tracked at #8807
Keeping this one open for now as a specific use case.