What ORMs have taught me: just learn SQL (2014)

>A simple job might be 3 lines of code in an ORM to update a record. In SQL that will be a lot more code especially if that's wiring up a foreign relationships.

That's not a fair comparison unless you include the time and effort involved in creating your ORM models, before you can even write those 3 lines of ORM code. The effort to construct those models isn't even fully amortized over your project, since it must be maintained through schema migrations.

ORMs are like putting on an exoskeleton to go buy groceries because it will let you carry your bags more easily. In my opinion, the complexity and indirection they introduce for accomplishing simple tasks do not justify their existence in the vast majority of cases.

Composing in this context means that one part of your application (eg the list controller) builds part of a query (the select from) and another part (eg the filter controller) builds another part of the query (the where part) and yet another part of you application (the paginator) alters there where part, adds limits and offset and build another query based on the same filter conditions to calculate the total row count. All that without the different controllers knowing each others in advance. This is not possible without complicated string manipulation or building some not-sql-query-algebra.

Some api like prepared statements (supported by the db itself) for building up complex queries step by step would be nice. Does something like this exist?

I have to congratulate you for having worked with a quite sophisticated and complex piece of technology for several years and never run into any issues.

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.

This is also tapping into one of the many optimizations you have to know about when using ORMs. Can you "select" 10M rows from a table? Is an object instantiated for each one? If they're lazily created, when are they destroyed, and where is the buffer of rows held, client side or server side? How do you efficiently update each one without incurring a sql statement for each update?

All of these questions require deep knowledge of the inner workings of the ORM, when in SQL, this is a lot more straightforward.