How to do SQL insert for Postgres using Prisma with condition on count of rows?

Question

I am trying to insert a row into database provided that - number of rows satisfying some condition already in the table is less than certain threshold, say 10. For example, in below model, I don't want to have a project to have more than 10 users; so count(projectId) should be less than 10:

model User {
  id            BigInt      @id @default(autoincrement())

  firstName     String      @map("first_name")
  lastName      String      @map("last_name")
  email         String      @unique

  password      String
  passwordHash  String      @map("password_hash")

  createdAt     DateTime    @db.Timestamptz() @default(now()) @map("created_at")
  updatedAt     DateTime    @db.Timestamptz() @updatedAt @map("updated_at")

  project       Project     @relation(fields: [projectId], references: [id])
  projectId     BigInt?     @map("project_id")

  @@map("app_user")
}

model Project {
  id            BigInt    @id @default(autoincrement())
  name          String

  users         User[]

  @@map("project")
}

In general SQL world, I would rely on transaction with Optimistic Concurrency Control and then attempt the insert only after reading the count of rows matching project_id. Since, Prisma doesn't provide traditional long running transaction, I am stuck. I cannot just simply run the count query first and then do the insert since it won't be atomic in nature.

How to handle this scenario with Prisma?

Answer 1

You can do this in two ways:

1. Add a version field in your model and perform Optimistic Concurrency Control in your application logic as shown here.

2. Use Prisma's raw query mechanism to run a native transaction.