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.

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";

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
)})`;

Doing this seems to have worked but I would have prefered some under the hood magic as this will be tedious for many includes (helper function time I think ...) 😄

const books = await this.photonService.$queryRaw`select * from book where id in (${join(
  models.map(m => m.bookId)
)})`

models.forEach(arc => {
  arc.book = books.find(f => f.id === arc.bookId)
})