createMany() should return the created records

[arcticmatt]

Initially brought up here: #4998 (comment)

Problem

The fact that the createMany API does not return the created records (which should be possible for Postgres) makes it difficult/impossible to use in some circumstances, meaning we must fall back to raw SQL. Here's an example.

Let's say I have these two tables

CREATE TABLE IF NOT EXISTS post (
  "id" SERIAL PRIMARY KEY,
  ...
);

CREATE TABLE IF NOT EXISTS comment (
  "id" SERIAL PRIMARY KEY,
  "postId" INTEGER NOT NULL REFERENCES post ("id") ON DELETE CASCADE,
  ...
);

Let's say I want to create many posts and many comments. The data to be created could be represented like so: Array<{post: PostData, comments: Array<CommentData>}>. If createMany returned the created data, I could do something like this:

const inputs: Array<{post: PostData, comments: Array<CommentData>}> = [...];
const posts = prisma.post.createMany({data: [...]});
const commentCreateData = inputs.map((input, index) => input.comments.map((commentData) => ...))).flat();
prisma.comment.createMany({data: commentCreateData});

However, since createMany does not return the created data, it is difficult to create many posts and then create many comments linked to those posts in an efficient manner. The remaining options are to create records one-by-one or use raw SQL. Using a combination of createMany and findMany is possible if you manually specify IDs, but does not work if you rely on Postgres (or some other DB) to generate IDs by default.

Suggested solution

createMany should return an array of created records (like how create returns the single created record).

Alternatives

• Use create (inefficient if creating many records)
• Use raw SQL (efficient but inconvenient)
• Use createMany + findMany (only works if you manually specify IDs, and also less efficient than if createMany returned created records)

Additional context

Personally, I've used raw SQL as a workaround since it's the most efficient alternative.

[timvandam]

This would be great to have.

From what I've gathered, Postgres, SQLite and MariaDB support INSERT RETURNING which could be used for this, but MySQL does not.

I suppose this would open the door to also supporting nested writes inside createMany. This would have some overhead as INSERT RETURNING does not guarantee the order in which rows are returned, so additional row mapping would have to be done by prisma.

[haisapan]

great if have this feature

[joeyfigaro]

+1 would love support for this.

[jemink]

Yeah, it will great if we support one flag that will return the newly created data from createMany. Same for the deleteMany too

[GauravRajSharma]

return await this.prisma.$transaction(
   users.map((user) => prisma.user.create({ data: userCreateData })),
);

Currently using this type of abstraction for handling this. If createMany would return the array of entities, that would be great.

[AnthonyLzq]

Are there any plans to include this feature in the future? I have checked the roadmap, but it seems that there isn't.

[reubenporterjisc]

Yeah we could really make use of this feature, are there any updates? Thanks!

[matthewmueller]

Hey there. Unfortunately no plans yet because not all natively databases support this and we're not quite at the stage where we can make fine-grain adjustments to our APIs depending on the database.

For now, we recommend the suggestion that @GauravRajSharma mentioned. Please let me know if you run into any problems with that approach and we can try to find alternatives for you.

[ledburyb]

I'm also disappointed by this. My issue with the suggested solution is performance, for inserting 100 rows it is around 20 times slower to do multiple inserts vs createMany.

I think my workaround will have to be to generate UUID primary keys on the client side rather than in the database and then do a createMany followed by a findMany.

[georgekrax]

+1 Why people would not want such behavior? 😥

[deemeetree]

+1 this is absolutely necessary. otherwise we have to query the DB again to get the data we added with the correct Ids. Imagine if you have multiple records.

[hlspablo]

I cannot understand why a such common use case has no solution yet, the developers keep adding things to the lib while it can't handle simple things, It makes me sad about software development, I think simplicity and performance should be the the direction of every framework, and have to add records one by one or use raw SQL to it simply nullify the usage of the tool.

[georgekrax]

@hlspablo Yes, it is a very common case! However, Prisma has already set a roadmap, and they do not seem to decline from their goals. Basic features like this and soft delete are by default included in TypeORM. It is sad that they do not try to provide at least the fundamental features of their competition.

[kevinmichaelchen]

otherwise we have to query the DB again to get the data we added with the correct Ids. Imagine if you have multiple records.

For tables whose primary key / ID is overwritten via trigger, being able to use RETURNING would be useful 😄

[byoungd]

This is absolutely necessary.

[okomarov]

@andrewicarlson Following our chat, this is the issue with the <create/update/delete>Many not returning the models but the count only.

[hlspablo]

Hey there. Unfortunately no plans yet because not all natively databases support this and we're not quite at the stage where we can make fine-grain adjustments to our APIs depending on the database.

For now, we recommend the suggestion that @GauravRajSharma mentioned. Please let me know if you run into any problems with that approach and we can try to find alternatives for you.

I think that's a bad project decision to keep adding support to new databases while the ORM can't handle so primitive things like this, we are doing weird things to compensate these decisions and not hurt performance, if we have to make RAW queries for ALMOST anything that is not a simple CRUD, JOIN, or GROUP, whats the real point of an ORM? switch from databases in one line of code?

[hlspablo]

to complement my comment, I think that returning items array as part of result for supported databases and empty for non supported is better than returning anything, and that wouldn't be a breaking change.
something like

{
    count: number;
    items: []
}

[vsinghipcium]

This is one of the basic features that ORM should support. Voting for consideration
+1

[andreasvh-conceto]

This is an absolutely basic feature for bulk requests in nearly every application, where you need to insert many data. I am working now in 2 big projects where performance and bulk requests are really important. When i insert 1000 records, i need feedback what records i created, not just the count. Maybe an includes flag in create many would be great here. Then you could decide what data should be returned. Same as for Nested create many operation Feature. These features are now more than one year open as well as the nested createMany feature. Is it still on the priority list? I like prisma and love how much effort the devs put into this project. But sometimes i get the impression that such basic features get a to low priorization. I know that there are many tasks to do, but if it takes to long, there are and will be other framework alternatives, which handle bulks much better. Would like to see this in the near future. It would be a waste of potential for this amazing project.
So thanks for all your time you spent in here 🙌
Best regards
Andreas

[hlspablo]

@andreasvh-conceto this remember me react native few months ago where some issues had 4 year birthday, fortunately things there are pretty good now, most of them were fixed, lets hope prisma project get the same attention to important matters like this one.
We have one year birthday to the simplest use case of an ORM, let see what happens. Unfortunately, for JS, there is no many good ORM options around.

[davecarlson]

It isn't neccesary to return the whole object either.

A better solution might be do return the primaryKey value ( assuming there is one ) and let the user do with that as they please

[marcus13371337]

Just in case someone misses my solution to the problem among all the comments here.

As I stated it works for Postgres, where there is one incremental primary key (BigInt or Int) with the name id, and that your table name is snake-cased compared to your model (i.e MyModel lives in table my_model). I've made some patches in my code since I wrote the comment. So as for today, my extension looks like this:

import { snakeCase } from "change-case";

const basePrismaClient = new PrismaClient();

export const prisma = basePrismaClient.$extends({
  name: "createManyAndReturn",
  model: {
    $allModels: {
      async createManyAndReturn<T, A>(
                this: T,
                args: Prisma.Exact<A, Prisma.Args<T, "createMany">>
              ): Promise<
                Prisma.Result<T, A, "createMany"> & {
                  ids: Prisma.Result<T, A, "create"> extends { id: bigint }
                    ? bigint[]
                    : Prisma.Result<T, A, "create"> extends { id: number }
                      ? number[]
                      : never;
                }
              > {
                const thisAny = this as any;
      
                const argsAny = args as any;
                const ctx = Prisma.getExtensionContext(this);
      
                argsAny.data.forEach((record: any) => {
                  if (typeof record.id !== "undefined") {
                    throw new Error("Providing own ids is not supported");
                  }
                });
      
                if (!ctx.$name) {
                  throw new Error("No name found in context");
                }
      
                const ids = await basePrismaClient.$queryRaw<
                  {
                    nextval: bigint;
                  }[]
                >`
                SELECT nextval(pg_get_serial_sequence(${snakeCase(
                  ctx.$name
                )},'id')) FROM generate_series(1, ${argsAny.data.length}) n
              `;
      
                const idField = thisAny.fields.id;
      
                if (!["Int", "BigInt"].includes(idField.typeName)) {
                  throw new Error(
                    `createManyAndReturn is not supported for id of type ${idField.typeName}`
                  );
                }
      
                const isNumberIdModel = idField.typeName === "Int";
      
                const newRecords = argsAny.data.map((record: any, index: number) => {
                  const idToUse = ids[index]?.nextval;
      
                  return {
                    ...record,
                    id: isNumberIdModel ? Number(idToUse) : idToUse,
                  };
                });
      
                const result = await thisAny.createMany({
                  ...argsAny,
                  data: newRecords,
                });
      
                return {
                  ...result,
                  ids: ids.map((id) =>
                    isNumberIdModel ? Number(id.nextval) : id.nextval
                  ),
                };
              }
         },
    },
  });

It works really well (we have quite a lot of parallel queries)

[janpio]

This might be of interest for some of you: #24064 (comment)
(Feedback in the PR if this works as expected would be very welcome.)

[ChristophP]

Amazing, thanks for sharing. This will allow simplifying a bunch if insertion code. Looking forward to 5.14.

[Weakky]

Hey folks, this feature will be released tomorrow as part of the 5.14.0 release for PostgreSQL, CockroachDB and SQLite. You'll find more about it in the release notes and the docs. Thanks!

[timvandam]

woohoo! finally

[janpio]

And it's out, check our release notes at https://github.com/prisma/prisma/releases/tag/5.14.0 or the documentation at https://www.prisma.io/docs/orm/reference/prisma-client-reference#createmanyandreturn 🍾

If anything does not work, please open a new issue so we can directly triage and handle those. Thanks.

[ChristophP]

Thanks for tackling this

[DavidVaness]

thanks prisma team

[rzzo]

Thanks team P!

[sebastian-dor]

CreateManyAndReturn does not only return results but implementing it also returns a lot of thanks!
Thanks!

[pyanda2]

If you could add MongoDB to that list would be much appreciated 😃

[janpio]

Please open a new feature request for createtManyAndReturn() to support MongoDB @pyanda2, happy to take that into account.