Prisma•7mo ago

Help Needed with Nested CreateMany!

Hi everyone, I’m currently working on an API route to handle a POST request that does the following: 1. Accepts a single classroomId. 2. Retrieves all students in the classroom via memberships. 3. Creates an AssignmentSet for the classroom. 4. Iterates over the students to: • Create an Assignment for each student. • Create a Portfolio for each student. • Connect the Assignment to the AssignmentSet. I’m encountering issues with Prisma’s lack of support for nested CreateMany. Could anyone provide guidance or workarounds to achieve this functionality? Creating the records one by one is too slow when there are potentially hundreds of students to iterate over. Thanks for any suggestion/help!

7 Replies

testasOP•7mo ago

Here’s the minimal version of my Prisma schema:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Classroom {
  id          String                @id @default(cuid())
  teacherId   String
  students    ClassroomMembership[]
  assignmentSets AssignmentSet[]
  teacher     User                  @relation(fields: [teacherId], references: [id], onDelete: Cascade)
}

model ClassroomMembership {
  id          String     @id @default(cuid())
  classroomId String
  userId      String
  classroom   Classroom  @relation(fields: [classroomId], references: [id], onDelete: Cascade)
  user        User       @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Assignment {
  id            String     @id @default(cuid())
  classroomId   String
  studentId     String
  assignmentSetId String
  portfolios    Portfolio[]
  classroom     Classroom  @relation(fields: [classroomId], references: [id], onDelete: Cascade)
  student       User       @relation(fields: [studentId], references: [id], onDelete: Cascade)
  assignmentSet AssignmentSet @relation(fields: [assignmentSetId], references: [id], onDelete: Cascade)
}

model AssignmentSet {
  id            String      @id @default(cuid())
  classroomId   String
  classroom     Classroom   @relation(fields: [classroomId], references: [id], onDelete: Cascade)
  assignments   Assignment[]
}

model Portfolio {
  id            String       @id @default(cuid())
  assignmentId  String
  studentId     String
  assignment    Assignment   @relation(fields: [assignmentId], references: [id], onDelete: Cascade)
  student       User         @relation(fields: [studentId], references: [id], onDelete: Cascade)
}

model User {
  id           String              @id @default(cuid())
  assignments  Assignment[]
  portfolios   Portfolio[]
  classroomMembership ClassroomMembership[]
}

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Classroom {
  id          String                @id @default(cuid())
  teacherId   String
  students    ClassroomMembership[]
  assignmentSets AssignmentSet[]
  teacher     User                  @relation(fields: [teacherId], references: [id], onDelete: Cascade)
}

model ClassroomMembership {
  id          String     @id @default(cuid())
  classroomId String
  userId      String
  classroom   Classroom  @relation(fields: [classroomId], references: [id], onDelete: Cascade)
  user        User       @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Assignment {
  id            String     @id @default(cuid())
  classroomId   String
  studentId     String
  assignmentSetId String
  portfolios    Portfolio[]
  classroom     Classroom  @relation(fields: [classroomId], references: [id], onDelete: Cascade)
  student       User       @relation(fields: [studentId], references: [id], onDelete: Cascade)
  assignmentSet AssignmentSet @relation(fields: [assignmentSetId], references: [id], onDelete: Cascade)
}

model AssignmentSet {
  id            String      @id @default(cuid())
  classroomId   String
  classroom     Classroom   @relation(fields: [classroomId], references: [id], onDelete: Cascade)
  assignments   Assignment[]
}

model Portfolio {
  id            String       @id @default(cuid())
  assignmentId  String
  studentId     String
  assignment    Assignment   @relation(fields: [assignmentId], references: [id], onDelete: Cascade)
  student       User         @relation(fields: [studentId], references: [id], onDelete: Cascade)
}

model User {
  id           String              @id @default(cuid())
  assignments  Assignment[]
  portfolios   Portfolio[]
  classroomMembership ClassroomMembership[]
}

I had to separate it due to too much text limitation.

moosthuizen•7mo ago

Consider creating two bulk operations: - Create all the students' assignments - Create all the students' portfolios The flow of your code could then look something like this:

const students_in_classroom = await ClassroomMembership.findMany(...);

const new_assignment_set = await AssignmentSet.create(...);

const new_assignments = await Assignment.createMany({ data:
  students_in_classroom .map((student) => {
    ... // classroom, student ID, etc.
    assignmentSetId: new_assignment_set.id
  })
})

await Portfolio.createMany( /* Map over new_assignments */ )

const students_in_classroom = await ClassroomMembership.findMany(...);

const new_assignment_set = await AssignmentSet.create(...);

const new_assignments = await Assignment.createMany({ data:
  students_in_classroom .map((student) => {
    ... // classroom, student ID, etc.
    assignmentSetId: new_assignment_set.id
  })
})

await Portfolio.createMany( /* Map over new_assignments */ )

There will still be four DB calls, but better than N calls. Let me know if this helps. @testas

testasOP•7mo ago

Thanks for the suggestion. The cavieat with your approach is, createMany doesn't return the records. B\ut I re-read the docs and found this https://www.prisma.io/docs/orm/prisma-client/queries/crud#create-records-and-connect-or-create-related-records It seems like a more efficient way to go about it! It seems like a new thing, as it's only available at 5.14

CRUD (Reference) | Prisma Documentation

How to perform CRUD with Prisma Client.

jonfanz•7mo ago

Yes, createManyAndReturn is new as of 5.14.0 🙂

testasOP•7mo ago

I'm concerned that returning tons of records can be very costly/inefficient. I don't see an option to select specific properties to return using createManyAndReturn, but maybe i'm missing it? is there a way to for example select to return only IDs?

jonfanz•7mo ago

Ah, we should probably make that more clear. select is available: https://www.prisma.io/docs/orm/reference/prisma-client-reference#createmanyandreturn

Prisma Client API | Prisma Documentation

API reference documentation for Prisma Client.

jonfanz•7mo ago

super pseudocode:

const newAssignmentIds = await prisma.assignment.createManyAndReturn({ 
  select: { id: true },
  data: [...]
})

const newAssignmentIds = await prisma.assignment.createManyAndReturn({ 
  select: { id: true },
  data: [...]
})

Gaming

Programming

Help Needed with Nested CreateMany!