I think there is no one answer for this.
In some cases pure SQL is better, in other cases, you need higher level constructs to be efficient, consistent and less error prone.
We have lots of experience with ORMs based on dynamic languages (i.e. Objective-C and Ruby) and if not careful, you can indeed go sideways pretty quickly.
Recently, we've been using https://ash-hq.org. It tries to solve the same problems as an ORM, but using a pure functional language (Elixir). You are using structs instead of objects, so it can feel very close to using raw dictionaries/hashes.
It also makes it super easy to drop down to raw sql, while maintaining that struct interface at the top.
While it does take some getting used to (especially coming from a dynamic, OO language), I'm liking this alternative a lot!
Yes, this is a good description. The way I explain it is that "Ash is a declarative, composable application framework."
It is the middle layer between DB (Ecto) and Phoenix (with or without LiveView) -- where your actual application lives.
The difference between a Resource and a Model (or ActiveRecord) is Models typically define attributes and relationships vs a Resource defines attributes, relationships, actions (read, write, destroy, custom...), behaviours (i.e. multi-tenancy, jobs) and what I call facets (i.e. JSON api, GraphQL).
When you read the code for a Resource, you grok all of this in a nice, consistent way (we have 100* resources - all are consistent). This is because of the DSL.
When compiled, it all boils down to a Struct that you can easily inspect. That Struct enables all kinds of stuff.
We have lots of experience with ORMs based on dynamic languages (i.e. Objective-C and Ruby) and if not careful, you can indeed go sideways pretty quickly.
Recently, we've been using https://ash-hq.org. It tries to solve the same problems as an ORM, but using a pure functional language (Elixir). You are using structs instead of objects, so it can feel very close to using raw dictionaries/hashes. It also makes it super easy to drop down to raw sql, while maintaining that struct interface at the top.
While it does take some getting used to (especially coming from a dynamic, OO language), I'm liking this alternative a lot!