How to createMany with a relation? Looping CREATE and getting "too many connections"?

[fotoflo]

hello! Let me see if i got this straight - I have what i believe to be a very simple issue:

1. I create a batch of reports scores
2. I create a bunch of scores in the batch
   • Because you can't use createMany with "nested relations"... i have to loop through this simple call
3. I run out of connections all the time

Simplified Schema

model reportingBatch {
  id                    BigInt                  @id @unique(map: "id") @default(autoincrement()) @db.UnsignedBigInt
  status                ReportingStatus
  createdAt             DateTime                @default(now()) @db.Timestamp(0)
  scores                scores[]
}

model scores {
  id                BigInt          @id @unique(map: "id") @default(autoincrement()) @db.UnsignedBigInt
  reportingBatch    reportingBatch  @relation(fields: [reportingBatchId], references: [id], onDelete: Cascade)
  reportingBatchId  BigInt          @db.UnsignedBigInt
  title             String?         @db.VarChar(2048)
  score             Int?
  createdAt             DateTime                @default(now()) @db.Timestamp(0)
}

Now i can create a score like this:

async function createScores(batch, data){
    return await prisma.scores.create({
      data: {
        reportingBatch: {
          connect: {
            id: batch.id
          },
        },
        ...data,
      },
    });

}

But i can't use createMany because of the connect call (no create many with "nested relations" - isn't this a simple relation?) so then i have to throw the above in a loop:

async function createScoresFromArray(batch, data){
 const promises = data.forEach( item => {
      return await createScores(batch, item)
   });
 return await Promise.all(promises)
}

Which, beside being sub-optimally performant, runs out of connections before my test suite finishes running... is there a better way to do it? How do i avoid running out of connections? Seems like a simple insert many, with the batchId in the table... am i missing something?

[nurul3101]

Hello @fotoflo 👋

If you just need to connect an id, can you try directly setting the foreign key if you are sure that it exists?
Here's an example of what I am suggesting:

import { Prisma, PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// A `main` function so that you can use async/await
async function main() {
  await prisma.reportingBatch.create({
    data: {
      id: 1,
    },
  });

  await prisma.scores.createMany({
    data: [
      {
        reportingBatchId: 1,
        score: 1,
        title: "title1",
      },
    ],
  });
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Based on your schema, first a reporting batch is created and then a createMany query on scores model directly sets the foreign key reportingBatchId instead of using connect. Would this work for your case?

[fotoflo]

Yesss! thank you @nurul3101, it appears to work Thank you! I think i tried that before doing connect and it didn't work for whatever reason lol!

So then my question is what is the point of connect?

[nurul3101]

You could directly set the foreign key or use connect. Both do the same thing and should work.
However, it is better to use connect to link a foreign key because prisma would throw an error in case it cannot find the connecting record, so connect would provide an extra layer of validation.

[juanramoncarceles]

In my case I have a many-to-many relation, so I don't have an explicit foreing key (also I'm using Planet Scale so no foreign keys anyway) and I was using connect with an array value. So I guess the only way for me is to not use createMany and instead loop and call create ? 😔

model Collaborator {
  id                String         @id
  name              String
  email             String?
  categories        Category[]
}

db.collaborator.createMany({
    data: collaborator.map((col) => ({
        id: ID.Collaborator(),
        name: lead.name,
        email: lead.email,
        // ERROR HERE
        // Invalid `prisma.collaborator.createMany()` invocation: ...
        // Unknown argument `categories`. Available options are marked with ?
        categories: {
            connect: lead.categories.map((id) => ({ id })), 
        },
    })),
});

[divyaswormakai]

+1

[CodrinSocol]

+1

[birgersp]

+100

[mmartinez-hd]

+1

[Qbason]

+1