I think there's often an underlying confusion between different tools and what they are supposed to do.
An ORM - as the acronym says - is helpful to map database records to objects in the system.
The meaning of the acronym already says that an ORM is not really designed for scenarios like aggregations and reporting. Within those contexts, you don't normally reason in terms of list of "objects" and "relationships" between them.
A "SQL builder" gives you a nice programming interface to build and manipulate SQL statements. Manually building complicated SQL strings is tedious, error prone and it makes it hard to reuse the same queries. With a SQL builder instead you can easily add dynamic conditions, joins etc, based on the logic of your application. Think of building a filterable Rest API that needs to support custom fields and operators passed through the URL querystring: concatenating strings would be hard to scale in terms of complexity.
Some people prefers to use templates instead of SQL builder to add conditions, dynamic values, select fields etc. I personally find that this approach is like a crippled version of a proper SQL builder interface. I prefer to use the expressiveness of a real programming language instead of some (awkward?) template engine syntax.
I think the confusion between these two different tools is caused by the fact that in some popular frameworks as Django or Rails you just get to use the ORM, even if behind the scenes the ORM uses some internal query builder.
Other ORMs like SQLAlchemy instead gives you both tools. You can indeed use SQLAlchemy as a ORM and you can also use it directly as a SQL builder when the ORM abstraction doesn't really work.
Normally, if someone tells me that it's better to write SQL queries by concatenating strings, I'd ask them how they'd build a webpage that filters products in the catalog with a series of filters specified by the user (by price, by title, by reviews, sorting, etc.). Try and build that concatenating raw SQL bits, without making a huge mess.
Also, the "just learn SQL" may apply to ORMs, but certainly not to a SQL builder.
Query builders can be so good at making it easy to work with the database ... the popularity of ORMs over query builders is a really big collective reasoning failure in my opinion.
With a good query builder in hand - it is very unclear to me why anyone would ever want to use an orm.
I like query builders but if everything were done with query builders there'd still be an awful lot of DRY pertaining to business logic that needs to go somewhere. Maybe you replace the ORM with some sort of 'results act as <something>' abstraction, but things like that often work out much better with consistent scaffolding of some sort, which is mostly what successful ORMs seem to be.
An ORM - as the acronym says - is helpful to map database records to objects in the system. The meaning of the acronym already says that an ORM is not really designed for scenarios like aggregations and reporting. Within those contexts, you don't normally reason in terms of list of "objects" and "relationships" between them.
A "SQL builder" gives you a nice programming interface to build and manipulate SQL statements. Manually building complicated SQL strings is tedious, error prone and it makes it hard to reuse the same queries. With a SQL builder instead you can easily add dynamic conditions, joins etc, based on the logic of your application. Think of building a filterable Rest API that needs to support custom fields and operators passed through the URL querystring: concatenating strings would be hard to scale in terms of complexity. Some people prefers to use templates instead of SQL builder to add conditions, dynamic values, select fields etc. I personally find that this approach is like a crippled version of a proper SQL builder interface. I prefer to use the expressiveness of a real programming language instead of some (awkward?) template engine syntax.
I think the confusion between these two different tools is caused by the fact that in some popular frameworks as Django or Rails you just get to use the ORM, even if behind the scenes the ORM uses some internal query builder.
Other ORMs like SQLAlchemy instead gives you both tools. You can indeed use SQLAlchemy as a ORM and you can also use it directly as a SQL builder when the ORM abstraction doesn't really work.
Normally, if someone tells me that it's better to write SQL queries by concatenating strings, I'd ask them how they'd build a webpage that filters products in the catalog with a series of filters specified by the user (by price, by title, by reviews, sorting, etc.). Try and build that concatenating raw SQL bits, without making a huge mess.
Also, the "just learn SQL" may apply to ORMs, but certainly not to a SQL builder.