Hello, when interacting with DB, I prefer to avoid ORM and write raw queries (I can easily use another tool if I want to switch or if it is not maintained anymore). I was about to use something like slonik/zapatos/postgres.js/pgtyped instead of prisma but I saw you have typesafe raw queries (https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access). 1. Does prisma does the same thing as the aforementioned libraries do? Or there is an additional abstraction layer resulting in poorer performance compared to those libraries? 2. In the case prisma is slower, is it possible to use of one those library jointly with prisma based on the types generated by prisma (like leverage prisma for basic crud and one of those tools for more complex queries)? It would also allow to use some field type (like ltree) which are still not supported by prisma even in a raw query. Also I saw there is a huge list of issues with prisma raw queries so I am a bit hesitant (https://github.com/prisma/prisma/issues/12367). Thanks in advance

I would steer you away from Prisma if you wanted to exclusively use raw queries

Thanks a lot for your input and advice Austin. What do you mean by "use Prisma's schema convention to get much use out of Prisma"? When you use knex for some advanced queries, do you use the tables generated by prisma that you defined in the prisma schema or you handle the creation of those tables also with knex? By any chance would you have a small repo to see how everything is connected?

Sure -- you'll want to use Prisma schema or else the typings for Prisma would be incomplete/not useful. So if you were using some other library for migrations or to handle your db models, you'd have to also maintain your Prisma schema, which could be a headache.

Sure, having a single source of truth is preferable. I would also be interested to have an opinion from prisma developers on the subjects mentionned above (about mixing prisma with other tools, about the implementation of the postgres driver in prisma, if it is as performant as postgres.js for raw queries etc.), maybe @janpio @Nurul @nikolasburk?

So to start with your questions: 1. Prisma does different things and might have different performance characteristics than these (both better or worse - ORMs are complex beasts and every one is different). Generally Prisma does some things on top of plain native database drivers, which makes it hard for us to achieve the same performance as a tool that just gives you a plain object of data back with no additional validation logic, relation mangement and so on. 2. You can use Prisma and another library to access your database. The Prisma generated Typescript types will probably not be useful for other libraries out of the box - they will not know how to use them without you writing additional code. But you can of course send some queries with Prisma (and benefit from the types) and some with another tools (that works differently). That also makes sense for queries that use types that Prisma does not support yet. If with "types generated by prisma" you mean the database schema that is created via Prisma Migrations, yes that is perfectly possible. You write the schema for Prisma, migrate with Prisma, and then can use whatever to access the generated database. 3. Raw queries are very complex to get right, especially if you try to still make the results typed. Things that Prisma does not fully support make it explode easily - and then issues get created. We will be working on making the list of unsupported things smaller, which will also remove many of these bugs and issues. Quite a few people actually use Prisma for the Prisma schema to design their schema, Prisma Migrations to migrate the database schema based on that, use the Prisma Client queries they like, even use the raw queries where it works well as the typed results are nice - and then fall back to whatever other database library they like for where Prisma does not work yet or they just like their other tool better. Does that answer you questions?

I could not wish for a better answer @janpio, thanks a lot for taking the time to explain with such details, I really appreciate. 1. Thanks for claryfing. That would indeed be amazing that under a same abstraction you could leverage best in class tools (like postgres.js) for raw queries, but as you are trying to support multiple databases, I get that it is hard to do. 2. By "types generated by prisma" I meant both the prisma client types generated from the schema and the types of the queries. I just found this article which describes good possible solutions but don't know if they also apply to non prisma raw queries https://www.prisma.io/docs/concepts/components/prisma-client/advanced-type-safety/operating-against-partial-structures-of-model-types#problem-using-variations-of-the-generated-model-type. I could do a non prisma raw query and declare the type myself according to what the query returns (in the SELECT statement), but as mentionned in the article, this approach increases the maintenance burden upon changes to the Prisma schema as we need to manually maintain the types. Thus I'd like to leverage the types generated by prisma. Exemple: in prisma schema I declare a user and prisma generates type like

type User = { name: string, age: number, address: string, etc. }

. For a non prisma raw query, I'd like to be able to create a type or partial type that use that information directly from prisma, like what is done with :

const userPersonalData = Prisma.validator<Prisma.UserArgs>()({ select: { email: true, name: true }, })

, so I have a single source of truth for my types. I guess I could not infer the types with "PromiseReturnType" / "PromiseLike" as they are only usable on prisma queries? In that case is "Prisma.validator" usable to define the partial type of my non prisma queries? 3. My main usecase for raw queries was for creating relay pagination queries that supports conditional filtering + sort on nested tables for which I would like optimal performance. The goal would however to be able to switch from raw queries to prisma queries when the performance improves (and if you reimplement the relay style in the future as seen here https://github.com/prisma/prisma/issues/5016), thus having a setup where prisma is used together with another tool would be great. I start to have a good view how everything could be layered but if someone has a boilerplate or a small repo as an example, that would be awesome. Anyway, thanks again for all you work for making our lives easier.

Uh yeah, your second question is above my knowledge. I suggest you simplify the question a bit and ask again in the channel or in a GitHub discussion. By itself I hope someone will be able to understand and reply.