Honestly, I hated Prisma for the longest time. Like, hated it. I tried to rip it out of projects multiple times. Why would you build a node library in Rust? (as one of many problems) But, I had a mental shift recently that helped me appreciate it more: I use Prisma only for features that fit neatly into an simple ORM (i.e. building a web page based on a bunch of joins). Anything else, I use raw SQL.
- If I want derived data, write views that encapsulate the transform. Prisma supports reading from views
- If I need something more complex, use DuckDB + python for analysis and write to the appropriate table.
- If I need to cache complex queries, just use a materialized view and read it as a prisma object
It's not perfect, but that let's me use prisma for what it's good at (Managing an ORM and deeply nested queries), then fall back into raw SQL for everything else.
Going straight to SQL has been a breath of fresh air, but, let's be honest, dealing with deeply nested joins really sucks when all you want to do is build a page that shows a company, all of it's people, and all of those people's relationships. ORMs are pretty handy for that last case, and I use SQL for everything else
I've looked into it a bit. I'm getting to the point where I either want to use a full ORM, or I want to write raw SQL and have it checked against the database.
Query builders are nice, but they sometimes end up in this middle ground where they have the worst parts of an ORM (needing to learn a new syntax and not getting access to all the DB features) and the worst parts of raw SQL (Unpacking relationships into objects w/ pointers is error prone boilerplate that sucks to write).
I love SqlAlchemy since it's still enough of an ORM to solve the main problem I want solved. Super curious to hear if you've had good experiences with Keysley and how it deals with transforming relationships into objects
