Yes, in my opinion the biggest problem with straight SQL is dynamic filters. It easily becomes a huge mess and that filter is only good for that one query, sure you can layer on stuff to make it better, but then you might as well use a library.
Sometimes you can avoid writing multiple queries with different filters by creating single parameterized query with conditions like:
WHERE (name LIKE :name OR :name IS NULL)
AND (city = :city OR :city IS NULL)
AND ...
By no means it is perfect, but can save you from writing many different queries for different filters while being easy to optimize by db (:name and :city are known before query execution).
Still, I prefer explicit SQL in webservices/microservices/etc. the code and its logic is "irrelevant" - we care only about external effects: database content, result of a db query, calls to external services (db can be considered to be nothing more than an external service). And it's easier to understand what's going on when there is one less layer of abstraction (orm)
In general, I tend to prefer straight SQL, but for a project I expect to maintain over time, I like having an orm. To go further, I like to write and maintain my own orm(s), that auto-generate the classes and functions that I'll be using.
This is mostly for making sure my code is up to date with the database. A migration _requires_ a code-change due to the orm code-gen and thus i can't deploy the migration until I ensure my codebase is ready for it
Overall, I would much prefer native SQL support in whatever language I'm working in. But a light ORM tends not to be a terrible trade-off.
Also I like this style of orm because sometimes the order of definining SQL is annoying to me. I prefer to start with the "from" and the joins, then add the conditions, and finally, the columns, which likely reference the other parts and thus make more sense at the end.
No, not really, but they are composable, which in a practical setting is way nicer than having to write a thousand different SQL queries that are almost the same.
Why SQL itself isn't designed to composable, and why we are happy with that remaining the status quo, will remain one of life's mysteries.
If you are tossing around documents or objects with metadata etc, want to remain open for future features and schema modifications, having just 100k users not 100m - so so so comfortable.
AFTER you learned to use it hahahhahahhhhahha - evil laughter.
