Hi, what kind of limitations and downsides you've faced when using Prisma2? What is difficult or nearly impossible when using Prisma2 (and what is easier using some other DB clients?)? In what situations I need to resort to raw queries? I've tinkered some basic stuff and it seems super smooth. But for basic usage most of db clients are more or less ok, and you only realize how bad decision you have made after going beyond basic

SELECT * FROM somewhere;

queries. I wonder how it works in more advanced use cases and so on. I've read this: https://www.prisma.io/docs/about/limitations which is a great start but does not contain that much information.

Hey @Ville 👋 Prisma mostly supports all querying operations and recently released

groupBy

. There are some operations currently not supported/partially supported and a couple of examples for those would be something like cascade deletes and counting for relation fields. You can get a better idea by viewing the open feature requests here. You would only need to resort to raw queries for cases like the above (counting relation fields) and for types not supported by Prisma i.e. Postgis Geospatial types. We have all the operators and supported operations in the docs so you can get a better idea 🙂

Thank you @Ryan 🙂 It is often hard to really grasp what you cant do until you need it, that's why I'm asking experiences of difficulties people have faced with Prisma. In overall all the basics seems much better thought out than most other clients.

Yes most of the basic querying and filtering use cases are supported out of the box so you wouldn’t need raw queries in those cases 🙂 Also even if you need to use raw queries for edge cases (like Postgis) Prisma supports typing the response so that you get type-safe results.

The only thing I've needed to use

raw

for have been when using

COALESCE

and

WITH

in queries, the rest has been through Prisma. (I see I need to update some raw

select count *

queries to use the aggregates..)

What about some more complex joins, recursive queries and bson etc?

Recursive queries (via CTE) would require a raw query for now and I’m not sure about bson. At the moment, we support

json

and

jsonb

types and basic querying for those. Advanced filtering is not yet supported directly but we have a request for that.

Don't forget that you can type the raw query:

const myStuff = await prisma.$queryRaw<StuffType>(theQuery);

Yeah I meant Jsonb 🙂 Just mixed it up with Mongos Bson which I believe is essentially the same thing. Have you guys taken a look at Slonik: https://github.com/gajus/slonik I think it nails the raw SQL side of clients very well. Maybe Prisma could incorporate something similar for the raw side of the library?

I'm sure they have a query builder that they were using but I can't find a reference to it now ..