Do you think handwritten SQL is error prone? If so, why?
Someone on my team made the claim today that writing SQL by hand is somehow error prone and that for this reason we are better off using an ORM. I'd like to understand why someone would say that.
No, I absolutely don't think so.
If you're used to thinking imperatively SQL's declarative approach might seem unfamiliar and somewhat less robust (because you don't explicitly tell the machine what to do but to some extent rely on it doing the right thing), even though it's not.
I'd even say that for anything else but trivial queries using an ORM framework tends to be more error-prone than using plain SQL precisely because of the well-known impedance mismatch between the two.
With ORM frameworks more complex queries often can't be expressed as concisely as with SQL, which leads to more code and clumsy work-arounds. More code, in turn means more potential for errors.
Only in the same way that hand written javascript or ruby is error prone.
Come to think about it, we actually are seeing something of this attitude applied to javascript among front end developers. You could point to the fashion of complete reliance on NPM for every piece of code over two lines as an example of the same thought process.
One possibility comes to mind. "Someone" does not, themselves, know how to write SQL by hand, having always relied upon an ORM to do so for them. From that vantage point, to them, attempting to write some SQL by hand is an error prone experience (because trying to do anything you do not yet know how to do is going to create some mistakes along the way).
While we need more context as to where this handwritten SQL is going (into a codebase [with parameterization ] to get code reviewed / PRed / compiled , vs. being typed in by a user to some textbox etc. ) to more specifically comment, I have a tendency to avoid ORMs at all costs since (dogma ahead!) they are never worth it long term. In very specific circumstances, they can make translating domain types into database access feel simpler, but their indirect costs almost always outweigh their use in my (limited, granted) experience.
The main reason handwritten SQL is error-prone is because there's a tendency to have security vulnerabilities, which ORMs automatically protect against. ORMs tend to have better IDE integration so its easier to refactor or errors may be flagged by the IDE. That said, there's no real difference in logical error rates between hand-written sql and an orm except that any developer may be more familiar with the syntax of one versus another.
You can use an ORM and hand written SQL. They are not mutually exclusive.
I think hand written sql is overall safer than an ORM. With SQL it's easy to select only the fields you need, and only update fields you need. ORM's want to fill up an object. The ORM will try to load some binary file field that has no relevance to the current task. You have to jump through hoops to not load a field of an object. But now your ORM updates are broken. That field you didn't load might cause it to be nulled out when you update back to the DB. It's a tragedy this pattern has caught on.
Data should not be ham-fisted into an object. You select 2 fields, not 10. The result is a set, not a model object. I think the clojure ecosystem is good at treating data in it's true form without forcing transformations to fit an object model.