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)

Hi @smithy747 👋

The answer is no, because databases do not explicitly guarantee that themselves. The order of the returned data from createManyAndReturn() is not guaranteed to match the order of the input data.

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?

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,
  },
})

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