Thank you, guys. I just noticed Prisma has nice documentation explaining the differences, and I missed it before raising this discussion:

https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries#create-a-single-record-and-multiple-related-records

Excluding any discussion about performance, I think each type of transaction is a tradeoff of convenience vs. flexibility. So I would approach it like this:

1. if you can use one of the high-level doMany functions, use that. Its clear the intent and the code is straightforward.
2. If you need to create multiple things at once and ensure rollback if any of them fail, but they are not all of the same type, then use prisma.transaction([]). you simply pass in all the calls you want without awaiting them.
3. If you need even more complex logic, possibly other checks in between step in the transaction or possibly throwing an error, use what prisma calls "interactive" transactions. This is the more traditional approach where all work is done inside of a callback:

await prisma.$transaction(async (tx) => {
  // use tx instead of prisma
  await tx.users.create()
  await tx.emails.create()

I think which one is "best" is determined by which one you are able to use for your use-case, the more complex the problem you will have to use the option that supports it.

In terms of performance, its not clear from the docs if a batch op (e.g. createMany) is more performant. The docs only state that the batch is ran in a single transaction, not a single query (e.g. COPY)

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?

Hi @MatheusGNachtigall 👋

Thank you for raising this question.

Prisma does not support the createMany operation with nested writes, which includes the connect operation for relations. The createMany operation is designed to create multiple records in a single model without handling relations. See this section of the documentation.

However, you can use the create operation to create a single record along with its relations in a nested write. For example, to create a Post and connect it to existing Category records by their unique identifier, you would use the following code:

const createPostWithCategories = await prisma.post.create({
  data: {
    title: 'Post 1',
    categories: {
      connect: [{ id: 'category-id-1' }, { id: 'category-id-2' }],
    },
  },
});

In your case, since you want to create multiple posts and connect them to existing categories, you would need to loop through your postsData array and call prisma.post.create for each post. Here's an example of how you might do this:

async function createPostsAndConnectCategories(postsData) {
  return Promise.all(
    postsData.map(postData =>
      prisma.post.create({
        data: postData,
      })
    )
  );
}

const postsData = [
  {
    title: "Post 1",
    categories: {
      connect: [{ name: "Category1" }, { name: "Category2" }],
    },
  },
  {
    title: "Post 2",
    categories: {
      connect: [{ name: "Category2" }, { name: "Category3" }],
    },
  },
  // Add more posts as needed
];

const createdPosts = await createPostsAndConnectCategories(postsData);

This approach will execute the creation of each post and its connections to categories in parallel, which may help with performance compared to a sequential loop. However, it's important to note that this could still be a time-consuming operation given the large number of records you mentioned.

If this answers your question, it would be great if you could mark this Discussion as answered to indicate that it has been resolved.

Otherwise please let us know how else we can help you further or close the Discussion if it was resolved in some other way 🙏

Does it answer to your question ?

https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries#nested-writes

Edit : if you scroll down this page you will see how to createMany inside the create statement. Look at the given example :

const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    posts: {
      createMany: {
        data: [{ title: 'My first post' }, { title: 'My second post' }],
      },
    },
  },
  include: {
    posts: true,
  },
})

Ok I found that this was actually quite simple... just do it as usual insert and include the forign key id.

    const result = await prisma.posts.createMany([
      {
        ...postData,
       userId,
      },
    ]);