prisma: Raw bulk insert failing

Bug description

I’m attempting to do a bulk raw insert. This is to avoid having to loop through every record, as this has performance implications. I need the IDs from the initial bulk insert as I then need to update other tables, and as per what’s discussed here, createMany does not return the inserted rows.

I have spoken to a Prisma Engineer about this, and have attempted to use the code he supplied me with (which, he says, was working for him).

See: https://github.com/prisma/prisma/issues/8131#issuecomment-1237992626

How to reproduce

I am running the following code, in Nest.js:

const rows = [{ status: 's' }, { status: 'x' }];

const values = rows.map((row) => Prisma.sql`(${row.status})`);

await this.prismaService
  .$queryRaw`INSERT INTO "test" (status) VALUES ${Prisma.join(
  values,
)} RETURNING *`;

When I run the above, I get the following error back:

prisma:query SELECT 1
prisma:query INSERT INTO "test" (status) VALUES $1 RETURNING *
[Nest] 43379  - 06/09/2022, 13:12:24   ERROR [ExceptionsHandler] 
Invalid `prisma.$queryRaw()` invocation:


  Raw query failed. Code: `42601`. Message: `db error: ERROR: syntax error at or near "$1"`
Error: 
Invalid `prisma.$queryRaw()` invocation:


  Raw query failed. Code: `42601`. Message: `db error: ERROR: syntax error at or near "$1"`

Expected behavior

No response

Prisma information

model test {
  id     Int     @id @default(autoincrement())
  status String? @db.VarChar
}

Environment & setup

OS: macOS
Database: Postgres
Node.js version: 16.13.1

Prisma Version

4.3.1

About this issue

Original URL
State: open
Created 2 years ago
Reactions: 1
Comments: 15 (7 by maintainers)

Most upvoted comments

Note: we could update our dependency and expose bulk https://github.com/prisma/prisma/pull/20977

It would look like this:

Accepts an array of arrays, and returns the SQL with the values joined together in a format useful for bulk inserts.

const query = `INSERT INTO users (name) VALUES ${bulk([
  ["Blake"],
  ["Bob"],
  ["Joe"],
])}`;

query.sql; //=> "INSERT INTO users (name) VALUES (?),(?),(?)"
query.values; //=> ["Blake", "Bob", "Joe"]

Jolg42 on Sep 6, 2023

That was me. I wasn’t able to locally reproduce the problem though. https://prisma.slack.com/archives/CA491RJH0/p1662461878343699

pantharshit00 on Sep 7, 2022

I’m having the same issue. I had to move to queryRawUnsafe.

this is my code that fails

prisma.$queryRaw`INSERT INTO "l_intent" ("english") VALUES ${Prisma.join(
          englishes.map(english => `($E$${english}$E$)`)
        )} RETURNING *`

the error is:

error - PrismaClientKnownRequestError: 
Invalid `prisma.$queryRaw()` invocation:


  Raw query failed. Code: `42601`. Message: `db error: ERROR: syntax error at or near "$1"`
    at Object.request (/Users/noitidart/Documents/The-App/web/node_modules/@prisma/client/runtime/index.js:45405:15)
    at async Proxy._request (/Users/noitidart/Documents/The-App/web/node_modules/@prisma/client/runtime/index.js:46301:18) {
  code: 'P2010',
  clientVersion: '3.14.0',
  meta: {
    code: '42601',
    message: 'db error: ERROR: syntax error at or near "$1"'
  },
  page: '/api/v1/l/admin/projects/upload'
}

It’s interesting because if I run this code it works just fine:

prisma.$queryRaw`INSERT INTO "l_intent" ("english") VALUES ($E$don't do it$E$),($E$build with spaces$E$) RETURNING *`

I’m currently using queryRawUnsafe:

prisma.$queryRawUnsafe(
          `INSERT INTO "l_intent" ("english") VALUES ${englishes
            .map(english => `($E$${english}$E$)`)
            .join(', ')} RETURNING *`
        )

Noitidart on Sep 30, 2022

Who did you talk to? Knowing that would allow us to skip the “reproduce with no knowledge” step. Thanks.

janpio on Sep 6, 2022