Insert multiple rows with multiple fields with prisma raw query

Question

I couldn't find any examples showing how to do this with integers and my options didn't work out(

My table contains 2 columns with integers and I need to add multiple rows to it using prisma raw queries.

If I add just 1 row and use an array of numbers with Prisma.join() - it works fine:

const testArr = [1,3]
return await this.prisma.$executeRaw`
    INSERT INTO public."_CategoryToItem" ("B", "A")
    VALUES (${Prisma.join(testArr)})
    ON CONFLICT DO NOTHING
;`

But when using a template string to add multiple rows at once I'm getting the syntax error:

// Exception:
INSERT INTO public."_CategoryToItem" ("B", "A")
    VALUES $1
    ON CONFLICT DO NOTHING
  ; ["(1,2),(2,2)"]
Duration: 0ms
[Nest] 1226  - 10/08/2022, 11:41:45 AM   ERROR [ExceptionsHandler] 
Invalid `prisma.$executeRaw()` invocation:
    
Raw query failed. Code: `42601`. Message: `db error: ERROR: syntax error at or near "$1"`

I was trying this:

const testArr = ['(1,2)', '(2,2)']
return await this.prisma.$executeRaw`
    INSERT INTO public."_CategoryToItem" ("B", "A")
    VALUES (${Prisma.join(testArr)})
    ON CONFLICT DO NOTHING
;`

and this:

const testArr = ['(1,2)', '(2,2)']
await this.prisma.$executeRaw`
    INSERT INTO public."_CategoryToItem" ("B", "A")
    VALUES ${testArr.join(',')}
    ON CONFLICT DO NOTHING
;`

It looks like prisma is adding string parentheses around inserted values (but I'm not sure as logs are not very descriptive but it looks like the issue).

Please advise the workaround.

Note: I'm using raw query because of working with unsupported type in other related queries so I need the help with the raw query.

Answer 1

If you want to avoid unsafe raw queries, you cannot take parts of the SQL expression like '(1,2)' as input. That prevents prisma from avoiding SQL injections.

So, you have to have your data in a form like this instead:

const testArr = [
    [1, 2],
    [2, 2],
  ];

Assuming a sample schema:

model Foo {
  id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  a  Int
  b  Int
}

you can then execute your raw query like this:

await prisma.$executeRaw`
  INSERT INTO "Foo" ("b", "a")
  VALUES ${Prisma.join(
    testArr.map((row) => Prisma.sql`(${Prisma.join(row)})`)
  )}
  ON CONFLICT DO NOTHING;`;

As you can see, you have to use Prisma.join and Prisma.sql to build your query. You cannot work with raw strings.