whereRaw model query option for Prisma Client queries

[paulm17]

Problem

There is no ability to filter outside of what query options are currently available for where: https://www.prisma.io/docs/concepts/components/prisma-client/filtering-and-sorting

Suggested solution

Have the ability to use whereRaw.

whereRaw: ('price > IF(state = "TX", ?, 100)', [200])
whereRaw: ('DATEDIFF(next_review_dt, added_dt) <= ?', [30])
whereRaw: ('sources in (?)', ["pimsleur", "iknow", "tango"])

Additional context

whereRaw can be a substitute until actual functionality for specific queries can be introduced into the prisma client.

[paulm17]

Additional functionality

Would be awesome if selectRaw could be added as well, to compliment whereRaw.

selectRaw: ('price * ? as price_with_tax', [1.0825])
selectRaw: ('count(episodeSentence.id) as count')

Finally, raw all the things?

orderByRaw: ('CHAR_LENGTH(kanji)')
orderByRaw: ('updated_at - created_at DESC')

fyi, I am actually using all these from a project that I first developed using laravel and using it's own database builder. But now migrating over to using BlitzJS and Prisma.

[umlx5h]

This is definitely needed. TypeORM can do this.
Prisma's type checking and auto-completion are awesome but the amount of freedom is restricted.
It would be better we can choose controlled or freedom way.

whereRaw can be a substitute until actual functionality for specific queries can be introduced into the prisma client.

IMO If feature like whereRaw is implemented, there's no need to delete it in the future.
Because implementing all the functions in the all databases is too difficult to maintain and will produce bugs, and no benefit is worth the cost.
So to give the ability to user to choose between controlled way or more free own way is the best design IMO.

[gifuhabu]

I want to use PGroonga's special syntax in where clause.
PGroonga is a postgresql extension that supports super very fast full text search engine.

pgroonga special syntax is like instead of 'LIKE'

select * from message &@ 'keyword';

https://pgroonga.github.io/tutorial/

It seems quite difficult to implement this feature in SELECT,
but in WHERE it seems so easy bacause simply allowing any string is enough. No need to tackle mapping object or generating type definition or anything like that.

and If this feature is implemented, prisma/prisma-client-js#690 issue is also covered by this.

full text search is relatively common usage. Would you please consider this feature?

[mittalyashu]

I also have a use-case, where I want to lowercase the column value LOWER(db_column) LIKE LOWER(?)

It is easy to lowercase the right part, currently there is no way to lowercase the left part before matching the value.

[Luke265]

Needed feature. At this moment you have to resort to raw query or use query builder if your query is a bit more dynamic. I like both TypeORM and MikroORM approaches. Here are some examples, how this could look in Prisma using template literals:

prisma.user.findMany({
    select: {
        [raw`TIME(deletedAt)`]:/* AS */ 'time'
    },
    where: raw`TIME(deletedAt) > '12:00:00'`,
    orderBy: {
        [raw`TIME(deletedAt)`]: 'desc'
    }
});
prisma.user.findMany({
    select: {
        [raw`TIME(deletedAt)`]:/* AS */ 'time'
    },
    where: {
        [raw`TIME(deletedAt)`]: {
            gt: '12:00:00'
        }
    },
    orderBy: raw`TIME(deletedAt) DESC, TIME(createdAt) ASC`
});
prisma.user.delete({
    where: {
        [raw`LOWERCASE(name)`]: {
            eq: 'my name'
        }
    }
});
prisma.user.updateMany({
    data: {
        name: {
            set: raw`LOWERCASE(name)`
        }
    }
});
const email = '';
const name = '';
prisma.user.updateMany({
    data: raw`name = ${name}, email = ${email}`
});

[luxaritas]

Having more fine-grained escape hatches like this would be great. It's disappointing to need to resort to a raw query when 95% of your query can be represented in the query builder, but that last 5% means you lose the ergonomics and type safety.

[mathias22osterhagen22]

This would be totally awesome!
It would allow transforming fields before applying the "where" condition.

Ex: I need to make match 1 input to 5 different fields for a "dynamic" search.
1 field is the id (number), 3 are strings, and 1 is a date.

With a RAW SQL query I can match the id field without converting it to "number" + I can apply date local transformation to adapt the input field, like converting it to "DD-MM-YYY" before comparing.

SELECT id, datetime FROM table WHERE id = 'input' OR DATE_FORMAT(datetime, '%d/%m/%Y') = 'input';

[meotimdihia]

it looks like we may need to wait an another year for this feature: https://www.notion.so/Prisma-Roadmap-50766227b779464ab98899accb98295f

[cantoute]

One would presume that adding a rawWhere should not be to difficult but I can kind off see why it's not such a g8 idea at global level.

In practice, i consider it a 'must have' for developing small "real world" apps.

An other approche could be to have the ability to pass a callback.
Something like a (prisma: PrismaClient) => {}

const mySelect:PrismaQueryBuilder<User> = (qb) => qb.$queryRaw`TIME(deletedAt)`;

const noon = '12:00:00';

prisma.user.findMany({
    select: {
        $prisma: mySelect,
    },
    where: {
        $prisma: (qb) => qb.$queryRaw`TIME(deletedAt) > ${noon} AND name = firstname`,
    },
});

// I could be completely wrong with naming, it's just to picture the idea.
class PrismaQueryBuilder implements Partial<PrismaClient> {}

Obviously far more challenging to implement but far more interesting too.

Perhaps this suggestion could have more interest for the core team? (Of course with a better written suggestion than this draft)
Probably someone is already working on this... hidden in the open space? Please let us know :)

[lilletech]

it looks like we may need to wait an another year for this feature: https://www.notion.so/Prisma-Roadmap-50766227b779464ab98899accb98295f

I work for a national project.
we are really surprised that there is not this feature. Some conditions require calls to SQL functions that cannot be called).
Switching to queryraw is not a maintainable solution. We lose interest in using Prisma!

We are starting a study to change of orm.

[DmitryKazanok-LP]

it looks like we may need to wait an another year for this feature: https://www.notion.so/Prisma-Roadmap-50766227b779464ab98899accb98295f

I work for a national project. we are really surprised that there is not this feature. Some conditions require calls to SQL functions that cannot be called). Switching to queryraw is not a maintainable solution. We lose interest in using Prisma!

We are starting a study to change of orm.

I worked around it using views (migrate support for which required a workaround too). So you can embed your function calls into a view and then you can use it with prisma treating it as a table
However this is still not a good way

[smeijer]

This could also help using the postgres jsonb some -> nested ->> id = 'here' selectors.

[andrewmclagan]

it looks like we may need to wait an another year for this feature: https://www.notion.so/Prisma-Roadmap-50766227b779464ab98899accb98295f

I work for a national project. we are really surprised that there is not this feature. Some conditions require calls to SQL functions that cannot be called). Switching to queryraw is not a maintainable solution. We lose interest in using Prisma!

We are starting a study to change of orm.

We are also in a similar situation, after having already invested much time in writing our data layer in Prisma there are so many unfinished features its very concerning and limiting.

[GarryOne]

May I ask what's the status on this one? What's this issue's priority?
Is there a way the community can upvote features like this to set up a higher priority for them?
Thanks

[janpio]

We are busy with other things, when we get to this it will be added to our roadmap at https://pris.ly/roadmap and we will most probably add a comment here asking for more input, share a design or similar. You can "vote" but leaving reaction on features or commenting with your specfic use cases that help us understand the complexity and use case of a feature.

[xinluh]

This is such an essential feature - would love to see it prioritized on the roadmap. There are so many scenarios where the select part is simple (select *) but the where statement is too complex or impossible to translate to the ORM interface, or there are database-specific optimized query features that would be unlikely to be implemented in a database-agnostic ORM interface like Prisma.

Right now we are forced to go full raw sql statement in these cases, and we ended up needing to write our own "db row -> object" converters for these raw queries - and if we have to do that in the first place, what's the point of needing an ORM?

Naively this seems like a relatively simple feature to implement given that mechanism for accept raw sql already exists (haven't looked into Prisma codebase in details, so I could be completely off base) and API interface doesn't need to be complex , and it would go a really long way to make Prisma usable in real world scenarios that don't fit neatly into existing Prisma filtering capabilities. IMO this sits in the sweet "low effort high impact" quadrant and I hope to see it prioritize over some of the larger / more complex projects on the current roadmap. @janpio

[vimutti77]

It should support mixing between non-raw and raw.

prisma.user.findMany({
  where: {
    AND: [
      { name: 'Hello' },
      raw`updated_at > created_at + INTERVAL '1 hour'`,
    ]
  },
})

[RNKushwaha]

Oh God!

it looks like we may need to wait an another year for this feature: https://www.notion.so/Prisma-Roadmap-50766227b779464ab98899accb98295f

I work for a national project. we are really surprised that there is not this feature. Some conditions require calls to SQL functions that cannot be called). Switching to queryraw is not a maintainable solution. We lose interest in using Prisma!
We are starting a study to change of orm.

We are also in a similar situation, after having already invested much time in writing our data layer in Prisma there are so many unfinished features its very concerning and limiting.

I am in the same boat.

[thgh]

If you only need the @> operator, there is a workaround:

// Before
prisma.$queryRaw(`... WHERE "fields" @> ${fields || {}}::jsonb)`

// After
    const fieldArray = Object.entries(fieldsObject || {})
    prisma.widget.findMany({
      where: {
        AND: fieldArray.length
          ? fieldArray.map(([key, value]) => ({ fields: { path: [key], equals: value as any } }))
          : undefined,
      },
      orderBy: { id: 'asc' },
      take: 5
    })

[GarryOne]

@janpio it passed almost a year. Any news on this issue?

[JosephHalter]

@janpio it passed almost a year. Any news on this issue?

I'm pretty sure the only thing that's lacking is a pull request. Opening issues is one thing, but without pull request it's just a wishlist.

[hanayashiki]

It's 2024 and we still cannot use raw where like other ORMs

[xegulon]

The people want whereRaw

[xegulon]

And orderByRaw

[Zakini]

I'm pretty sure the only thing that's lacking is a pull request. Opening issues is one thing, but without pull request it's just a wishlist.

This is exactly it: we all want whereRaw but the fastest route to getting it is one of us putting the work in to make a PR

[nairihar]

Are there any plans for orderByRaw and whereRaw in near future?

[Cat7373]

I wish to write a query with where condition like:

JSON_SEARCH(uids, 'one', '6') IS NOT NULL

It seems that it is currently impossible to support frameworks like knex, which will allow the use of raw queries in this small set of conditions, and prisma seems to be able to only fully write sql, while giving up all orm functions

[GarryOne]

The way this issue (which is so upvoted/required/discussed/old) is handled by the prisma, shows much disrespect and lack of care towards their community.