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

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

You can do this in two ways:

Add a version field in your model and perform Optimistic Concurrency Control in your application logic as shown here.
Use Prisma’s raw query mechanism to run a native transaction.

Advertisement

Answer