I get where you are coming from, but from your comment I am guessing you have yet to try Prisma.
I'll speak to my personal experience...
I became allergic to ORMs after experiencing much of the pain that you describe. Like you, I quickly found ORMs were simply an additional domain language / abstraction over my database that provided more pain that usefulness. Every time I wanted to make I change to the code I had to wade through tons of docs and/or stackoverflow posts by other frustrated users. If I wanted type safety I had to express and maintain types/decoders/encoders myself. Huge pain, and things always got stale leading to a massive mistrust in my data layers.
Prisma doesn't feel like those experiences. Their schema-first, client code gen approach works surprisingly well. Using the generated API feels really intuitive, and TypeScript is there the whole time providing guidance and autocomplete for me. The object tree query syntax is quite refreshing compared to the builder pattern approach taken by the alternatives. I always found the builder pattern overwhelming and often a guessing game at how to compose them.
I think Prisma doesn't try to be too clever with their data API. They solve the 99% in a manner that is simple and convenient, for everything else you have the raw query API - much like other solutions.
That sounds awful, and completely different from my experience with ORMs. My experience has almost exclusively been entity framework, which despite having some warts (rank over partition queries are impossible) has been a very pleasant experience.
One advantage is the additional domain language is also the language of array/list manipulation, and not having to maintain any encoders/decoders (I honestly don't know what these are).
Interesting, coming from Eloquent ORM (PHP), I hated Entity Framework. It seemed to want to do far too much clever stuff (like save an entire object graph at once), and didn't have nearly as many escape hatches as I'm accustomed to (Eloquent will let you inject raw SQL nearly anywhere). I've had positive experiences like ORMs, but only when they are thin layers over the underlying SQL.
What was the problem with raw SQL you ran into with Entity Framework?
Entity framework follows the unit of work pattern from gang of four, and if I remember correctly eloquent follows the active record pattern.
When I started I found the active record pattern much more intuitive, but I prefer the unit of work pattern now. Mostly because I think unit of work works better with transactions/constraints then active record pattern.
You are talking about one where the goal is to make querying the database more idiomatic on your development language, at the cost of some flexibility. This works very well as long as you stay within the bounds of the abstraction, and breaks terribly when you step out of it. The engineering goal is to make the abstraction just broad enough to represent most of the common queries without making it less idiomatic.
The second kind is the type that tries to abstract databases into a specialized query language. The goal here is to bring things you don't get on plain SQL (like type integration or a single DBMS independent language) without losing expressive power. That's the one the GP is talking about.
Maybe you mean something else, but I haven't had any issues when I've had to break out of the ORM and write portions in SQL. (usually once or twice every couple of development man years)
I'm not sure what type integration is, but the ORM I'm most familiar with does allow spanning multiple DBMS's with the same code. (unless when you had to break into DBMS specific SQL for performance reasons)
Is type integration allowing static type checks against your query language? Entity Framework does this as well.
I think EF is the second type of ORM, unless I'm misunderstanding you.
I'll speak to my personal experience...
I became allergic to ORMs after experiencing much of the pain that you describe. Like you, I quickly found ORMs were simply an additional domain language / abstraction over my database that provided more pain that usefulness. Every time I wanted to make I change to the code I had to wade through tons of docs and/or stackoverflow posts by other frustrated users. If I wanted type safety I had to express and maintain types/decoders/encoders myself. Huge pain, and things always got stale leading to a massive mistrust in my data layers.
Prisma doesn't feel like those experiences. Their schema-first, client code gen approach works surprisingly well. Using the generated API feels really intuitive, and TypeScript is there the whole time providing guidance and autocomplete for me. The object tree query syntax is quite refreshing compared to the builder pattern approach taken by the alternatives. I always found the builder pattern overwhelming and often a guessing game at how to compose them.
I think Prisma doesn't try to be too clever with their data API. They solve the 99% in a manner that is simple and convenient, for everything else you have the raw query API - much like other solutions.
I'd suggest giving it a try. You may like it.