Add support for SQL expressions in Prisma queries

[MurzNN]

Problem

Sometimes we need to implement some SQL side modifications of retrieved data, for this SQL Expressions are commonly used. But seems Prisma doesn't support them yet?

Suggested solution

Will be good to add support for expressions, something like SELECT CONCAT(firstname, " ", lastname) AS fullname FROM users WHERE 1; to execute on the database, side, without client-side parsing.

Additionally, it could help to retrieve data from Unsupported field types.

Alternatives

Alternatively (or additionally) to describe a specific expression in a query, maybe we can describe some virtual fields, that match desired expressions?

[jkomyno]

Hi @MurzNN, if I understood correctly, you'd like to manipulate the records retrieved via Prisma queries with SQL expressions?

Have you considered using prisma.$queryRaw or $queryRawUnsafe? They allow you expressing any SQL query as long as it's supported by the database, but they don't leverage Prisma's type safety and in-editor autocompletion features.

On the other hand, with middlewares you could manipulate the shape of the query records retrieved by Prisma, but they only supports TypeScript expressions.

---

Update: Gathering some context also from https://github.com/prisma/prisma/issues/18544, it seems you're indeed looking for a feature that doesn't yet exist in Prisma.

[MurzNN]

I know about $queryRaw and $queryRawUnsafe, but jumping to raw queries just to get the value of the one "unsupported" field, together with dozens of other "supported" fields is a too tough step!

And retrieving the "unsupported" field value via an additional separate query - is not a good way too!

So, having a way to retrieve field data via SQL expression - gives us the very flexible ability to retrieve data from unsupported fields and in many other cases.

[janpio]

Is this issue about what you described in the original issue description, which is focussed on SQL expressions, or about making it possible to retrieve data of Unsupported fields?

[MurzNN]

This issue is about SQL expressions, that can be used for wide purposes, and about Unsupported fields - there is the separate issue https://github.com/prisma/prisma/issues/18544 that could be resolved (or workarounded) via this issue too, depending on the realization.

[jkurei]

I've myself needing this a couple times and it's a pain to have to get out of the comfort of the usual Prisma queries to use queryRaw.

Just recently I needed to fetch all posts for an app I'm working on, and I needed some basic stats (length, and whether the post is empty) about them. I would've loved to have something like:

const allEntries = await prismaClient.entry.findMany({
  where: ...,
  select: {
    id: true,
    title: true,
    url: true,

    empty: {
      content: { isFalsy: true }
    }, 
    length: {
      content: { length: true }
    },
  }
})

May be adding some of the most common expressions used of this, like checks for emptyness and length, concatenations, and little else, would go a long way.

[Hexmm]

If I can simply use some expressions in the prisma API in "orderBy" like below. It may lose some type-safe that can't check the type in the expression, but I think it is actually good and convenient for me.

prisma.job.findMany({
                skip,
                take,
                where,
                orderBy: [
                    {
                        expression: "regexp_split_to_array(batch_number, '[^[:alnum:]]+') ASC"
                    }
                ]
            }),