RTFM: https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access#tagged-template-helpers

import { Prisma } from "@prisma/client";

const ids = [1, 3, 5, 10, 20];
const result = await prisma.$queryRaw`SELECT * FROM User WHERE id IN (${Prisma.join(
  ids
)})`;

Ended up doing something like this:

const searchConditions = args.fullSearch
  ?.split(" ")
  .map((separatedSearch, index) => {
    return raw(
      ` AND lower(concat(P."firstName", P."middleName", P."lastName", P.suffix, T.slug, T.name, PHI."contractNumber", IP.name, IP.slug)) like '%${separatedSearch.toLocaleLowerCase()}%'`
    ).text;
  })
  .join(" ")
  .replace(/, AND/g, " AND");

context.prisma.$queryRaw`
  ...
  ${raw(searchConditions)}
`

hopefully, this is the right way to do this.

note that the raw is imported like this: import { raw } from "@prisma/client";

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.

I have found a solution to my problem, by using prisma.$queryRaw which I actually didn't know it existed for the few months I've been using it and only stumbled upon it now. Here's the documentation link for reference: Prisma - Raw database access

The end solution was:

await prisma.$queryRaw`SELECT * FROM FIRST JOIN SECOND ON FIRST.LPR=SECOND.LPR`

P.S. The only issue I ran into by using the queryRaw method instead of the regular Prisma Client was that the BLOB type values are retrieved as strings, instead of as a Buffer, but that can be handled both on the front-end as well as in the back-end accordingly with minor modifications to the response.