I sort of agree here. I tend to stay away from ORMs in general, but I'd probably use one if it actually justified itself. Most ORMs I've used basically just abstract the data layer so you don't have to write own SQL, but that in itself comes at a cost (and almost all of them are bound to eventually write some really crappy SQL for you and cause performance bottlenecks). If there's not an appreciable benefit besides just not having to write as much SQL, then it's not really worth using.

In one way or other you cannot avoid the SQL builder, this ORM is building it in the user's code base.

I believe in ballance,

you need the query builder for simple queries (that makes most of your queries)

but the complex queries should be also supported (even if they are not portable)

We have written exactly this kind of ORM https://github.com/holdfenytolvaj/pogi

It saves you a lot of code writing, but is not taking away the power of postgresql (however it is anchored to postgresql)

Jdbi works the way you describe: https://jdbi.org/

Yep, I've been enjoying JDBI for a new microservice. /me makes sign of cross and throws holy water on legacy Hibernate code it's replacing.

Personally, all I want is something that can dynamically build SQL and can hydrate into specific objects.

I have no interest in automatically building relationships, I'm completely fine doing that manually, or not at all. Which is why dapper is probably my favorite DB solution out there.

    return conn.query<MyKlass>("select * from my_klass_table where status = :status", new { status=myStatus }).ToList();

The only thing that can be bothersome sometimes is inserts and updates. It would be really nice to have something that could auto-generate the SQL for you.

    var sql = SQLGenerator.insert(MyObject);

or

    var sql = SQLGenerator.insert(MyObject, MyKlass.InsertableProperties);

It would take care of one of the downsides of raw SQL, which is schema updates needing to be dealt with in multiple places, and would remain super simple.

I mean hell, if you wanted to get fancy you could create a generator that would figure out the columns necessary by reaching out to the DB the first time and caching it afterwards, and using a naming convention to map to the actual properties. But personally, I'm 100% fine not having that.