Query Builder: You build a query, just not in SQL. So you can get around SQL's limitations (like composition)
Try this:
> ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query.
or possibly:
> ORM: You ask for something and you accept the tradeoffs vs hand-written queries but you're content that it's the correct tradeoff for your use-case.
There's plenty of places for bottlenecks to hide and premature optimization is the root of at least some evils.
Ha. More like:
ORM: You've just joined a project already using an ORM selected by an 'architect' that no longer works here. Everything is fine until you start testing your system with a database sufficiently populated with real-world data. You and the DBA spend the next next 6 months trying to get the damn ORM to generate performant SQL (you even call Oracle support, which promptly dispatches an "engineer" who tries to sell you on another $500k of crap you don't need that won't really address the problem). You eventually just start writing queries by hand wherever you find a bottleneck caused by the naive ORM, which you could have done at the outset, but no one who actually knew SQL well enough was on the team back then.
Ironically the last time I had a big ORM performance problem was Hibernate eager loading all joins. Diagnosing and fixing it didn't take more than an hour. (Though we did have a very experienced DBA at the time.) YMMV
... that you understand and can be sure are sensible.
> while an ORM builds queries for you
... that you have to hope are sensible.
That's one of the biggest flaws of the ORM for me - you have limited visibility of what it's doing to your DB.
And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place?
The point may be that 98% of the queries are just fine, and you've saved time vs writing by hand, and it may be easier to read/understand for the next people to have to touch the code.
Most software work is maintenance work. Optimizing for initial deployment is shortsighted.
I've also worked at a companies where all of the logic was in ungodly stored procedures and getting anything released took months and we had a whole two weeks "hardening sprint" because we had to coordinate with the "database developers" and of course the entirety of the business logic was In stored procedures.
Most software being maintenance work is even more of a reason to optimize deployment. What good is unreleased software? The most important part of the business is releasing software. Most of my emphasis asan Architect is making sure we can release fast.
* The 'developers' weren't allowed to write the sprocs. We were at the biz meetings, but the DB guys were hardly ever there - their meetings were separate for some reason, but because devs were at the meetings, the devs were the ones who also were the face of the project. When the project was behind, we caught it in the neck, even if we were bottlenecked waiting for the DB team to write their logic and expose it to us.
* The DB team was always fewer people, juggling more projects, and other things like system uptime, maintenance, backups, etc.
* The sprocs were never part of version control or part of any source code that we could ever see as part of normal development. They typically weren't subject to any unit testing process.
The answer to all of this is mostly human management, structuring resources differently, coming up with different processes, etc. But any of those things would have changed the power dynamic, which seemed to be the purposes in those environments. Hey, I can write a stored procedure too - let me write them, and if a DB wants to 'review' them - or really, anyone on the team - please review and let's hammer it out and make it better. But roadblocking projects until the DB guys can 'get around' to writing our mission critical procs is just silly.
One other 'weird' division I saw a few places was this "developers can never have access to production systems - that violates XYZ" (a regulation, or some 'law' that was never produced, etc). I asked what the core issue was, and it was "you can't just have developers going on to production and just making changes on live systems - that's ... (against our policy, etc)". This was particularly challenging in a situation where a critical bug only happened on one production system, and we weren't allowed to replicate the database to another system, nor was anyone with any knowledge of the deployed code allowed to get on to the production system to even see if what was deployed was what we'd developed. But... this was still "our problem". Yet... the DBA in this case was "allowed" to get on the system and hand-write new triggers and sprocs to 'fix' our problem, all without documenting/testing his code, nor committing the code to any repo for us to even have visibility in to the data manipulation he was doing to 'fix' the problem we supposedly cause but couldn't investigate.
Again, I know this isn't a problem specifically with stored procedures. When sprocs have been promoted as the primary interface, however, it's usually been a political/power grab more than a technical benefit. And yes, again, I know there are technical advantages in some cases, but usually not enough to outweigh the drawbacks I've encountered.
Yes I optimize when my automated performance testing/stress testing, tells me I need to and may write handcrafted sql, but I've also handcrafted some classes in C back in the day when my old Windows Mobile app using the C# compact framework wasn't performing.
From what I've seen (couple of home-grown ones, Class::DBI, ActiveRecord), "more often than you want".
(I'm willing to admit they may not be class-beating examples. :)
Of course most ORMs do fall short because most languages don't have the powerful concept of "code as data".
SQL syntax is extremely verbose, and compounds the more tables are involved in a query. You're not counting the time the ORM has saved you before having to resort to logging SQL.
But of course, as the queries get more and more complex, the flexibility of the ORM syntax approaches the flexibility SQL. In the end, there are many situations one would rather just use SQL.
For example, in Go, I use Gorp, which has a Select() function where you pass in the SELECT query string (plus bound values) and the target type, and it loads every result row into an object of that type. So you can have an arbitrarily complex SQL query as long as it starts with `SELECT one_table.* FROM`. That's a marvelous design.
And when you have to do a query that returns results from multiple tables? Guess what, you just use the normal SQL module from the standard library.
postgres=# SELECT * FROM domains WHERE id = 'foo';
ERROR: invalid input syntax for integer: "foo"
LINE 1: SELECT * FROM domains WHERE id = 'foo';
^Your scenario happens with people that either don't know or don't care. They will write crappy queries with any tool.
I built a moderately complex application in Django at a previous workplace, using the ORM for most things, until the queries were too complex for the ORM.
Another guy connected to the same database and built some graphs using PHP and SQL. Guess who had to help him write the SQL when the queries got too complex for him? The ORM user.
What's the difference? To me, an ORM is largely a query builder.