Access to raw sql functions using Prisma APIs.

[Perry-Olsson]

Problem

Having to use raw sql query when I would like to make use of a native database function.

Suggested solution

In my case I'm using Postgresql and would love to be able to use the extract function to retrieve information from a timestamp.

SELECT * FROM "Appointment" WHERE extract(month from timestamp) = 2;

I've done this with Mikro-orm in a prior project and a similar implementation with Prisma could look something like this:

prisma.appointment.findMany({ where: { "extract(month from timestamp)": 2 } as any });

Alternatives

My primary frustration with having to use a raw query is that the results of the query aren't mapped as they would be with a query using the API.

So an alternative could be an option to have raw queries also map the results of the query.

[jpalumickas]

Hello,

I have similar problem. I want to use findMany with custom raw SQL inside, because I want to have built in Cursor-based pagination and not write it in custom $queryRaw.

[darrylyoung]

I'd also like to mix raw queries with the Prisma API. Here's an example model to help explain my use case.

model Product {
  id          String   @id @default(cuid())
  name        String   @unique
  description Json
  message     Message
}

Previously, the description field was String but I'm looking into adding translations at a database level so I decided to use Json for this field. There, instead of a single English string, I could now store something like this:

{
  "en": {
    "greeting": "Hello"
  },
  "de": {
    "greeting": "Hallo"
  },
  "es": {
    "greeting": "Hola"
  }
}

Then, when querying for Products, a user could pass their locale as an argument and it could grab the correct translation for them. I managed to get that working with the following.

const result = await ctx.prisma.$queryRaw<
  Product[]
>`SELECT id, name, description -> ${locale} as "description" from "Product"`

That works as expected but I obviously want to get everything for an individual Product and that, in the case of the example model above, would include relations – in this case, to Message (in reality, my model is much more complicated and has a bunch of relations and other fields). I was hoping I could get Prisma to do its thing and allow me to specify how I want the Json fields, while doing everything else automatically, but I found out that it's not the case. To filter JSON like that, and also get everything else like I would so easily with findMany, I'd have to write everything manually with $queryRaw.

[brandonmpetty]

I would like to use this too... so that I can add a "FOR UPDATE" at the end of my select while inside a transaction.
That way I can safely lock a row for delete and then return the resulting row that was deleted.
The current "delete" method does not work for me because I need to call "deleteMany" in order to have a more powerful WHERE clause, something this could also fix. But that also causes the issue, as the record could be updated before my delete happens.

[spence-novata]

Would also like this feature. My use case is to be able produce something like:

`SELECT *, GREATEST("ChildRelation"."updatedAt", "Resource"."updatedAt") as "lastUpdateAt" FROM "Resource" JOIN "ChildRelation" ON "Resource"."id" = "ChildRelation"."parentResourceId" ORDER BY lastUpdateAt"

With I imagine something like this:

prisma.resource.findMany({
  select: {
    '*': true,
    lastUpdateAt: 'GREATEST("ChildRelation"."updatedAt", "Resource"."updatedAt")',
  },
  include: {
    childRelation: true
  },
  orderBy: [
    {
      lastUpdateAt: 'desc',
    }
})

[Luke265]

Possible duplicate? #5560