I'm trying to use Prisma to create a query similar to:

SELECT * FROM user WHERE name ~* '\mhello';

Unforunately, something like this:

prisma.user.findMany({ where: { name: { contains: `\\mhello`, mode: 'insensitive' } } })

does not work

Did you try with fulltext search query?

Thanks for the suggestions Jarupong 👍 Do let us know if you still face an issue in getting this to work.

👋 Hello @Peter - did you have a chance to check the community's recommendation? Let us know if this is till a problem!

Thanks for the follow up but Prisma's "all or nothing" with regards to raw queries is extremely counter productive. Other ORMs allow you to partially pass in raw sub queries while still being able to take advantage of their query builder, but Prisma does not. The consequence of this is that anything that's non trivial forces you to use raw queries for the entire query, defeating the purpose of using an ORM in the first place. In a recent project, I ended up having to write raw queries 90% of the time because Prisma didn't allow me to do some trivial things. I will definitely not be using Prisma in my future projects unless it's a very simple app. Take my above question as an example. That select statement is part of a much larger query, meaning that instead of using the query builder, I had to convert the ENTIRE query into a raw query, which was very very counter productive. And all because Prisma decided the only valid use cases for ILIKE are

ILIKE '%$1%

,

ILIKE '%$1

, and

ILIKE '$1%'

Hey Peter, thank you for the detailed feedback. I’ll share this with the team so that we can make the experience better for you. We do have a Feature Request: #5560 to support partial raw subqueries, so we do plan to support this soon.

@Nurul That's good to know and I feel it would be a great addition to your product when it gets released. However, I think an even more pressing feature request would be full support for Views. This would probably be the feature that would become a great escape hatch and partial workaround for other feature requests.

Thank you for taking the time to share this feedback with us @Peter! I've shared your messages with our Product Team, but it would be fantastic if you could also comment on the relevant GitHub Issues or create a new one. 🙏 Upvoting and comments in GitHub are a very important signal that our engineering team uses to prioritise the next feature / bug they will be working on! 😊