> A query builder aids you at constructing queries

... 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.

Isn't that the same reasoning as "I don't like using high-level languages because they limit my visibility of what's running on my processor"? Do you write all your code in assembly?

I think the issue is that if you don't understand what a high-level language is doing under the hood, then you will constantly be surprised by side-effects. You shouldn't be using an ORM to substitute for your lack of understanding SQL; you should be using it to automate tasks.

How so? I can log the the sql being generated and look at the logs. Something I should be doing either way.

If you have to log the generated SQL to understand what's happening, you're already behind the curve.

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?

> 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.

"saved time" at the least optional time to save time. ORM's are a maintenance burden. They obfuscate DB performance behind usually an enormous API surface.

Most software work is maintenance work. Optimizing for initial deployment is shortsighted.

What "enormous" API surface? It's not about "optimizing initial deployment". It's about "optimizing continuous deployment" and having "always releasable Software". I've worked for departments with 15 devs all working on the same codebase and we could release and rollback releases (A/B deployments) every week because we treated the database as a dumb data store. We had multiple branches at the same time, etc.

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.

I like the idea of stored procedures. I get some of the value they bring. However, the few times I've been on projects where sprocs where the primary focus of logic/truth/app... it was always a pain.

* 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.

Your complaints about stored procedures seem to be about the nature of the company where they were used rather than any inherent flaw with them.

I believe I pointed out that I realize it's a human issue more than a technology one. It was easier for some people to get suckered in to the power dynamics being played because "DBA" was already seen as more of black-magic art sort of thing, and those guys were the "real wizards" and so forth, so whatever they say goes. It's not been everywhere I've ever worked where sprocs were used, but it seems to have been at the places where "stored procedures are law".

And it's because most developers can do sql but most "database developers" can't code. So of course they are protective over their code.

You haven't saved time compared to using a query builder though; in fact you've lost time due to the hit to readability and debugability.

How do you know this? You have no idea what bugs were encountered or how easy they were to fix.

We're conjecturing about hypothetical bugs. No one in this thread knows what bugs were encountered nor how easy they were to fix; they aren't real bugs. They're symbolic of the bugs that we encounter every day, which give us the experience on which we base our conjecture.

How often is the ORM generating junk? To say that the ORM is useless because occasionally it doesn't generate performing code (with EF and Linq more often than not it does generate performant code) could be applied to any high level construct. But I don't see people giving up modern languages to go back to writing everything in Assembly or even C.

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.

> How often is the ORM generating junk?

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. :)

I meant a good ORM. But then again, my definition of a good ORM is an ORM with a language that treats queries as a first class citizen. EF with Linq doesn't really act feel like a separate framework since Linq and Expressions are built into the language.

Those are more of a query framework than an ORM. If that's your bar then most ORMs are going to fail to meet it.

How is it a "query framework"? Linq works with objects and the Linq expression provider treats the Linq as data that translates the objects to Sql at runtime. That is by definition an Object Relational Mapping.

Of course most ORMs do fall short because most languages don't have the powerful concept of "code as data".

>you're already behind the curve.

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.