Ask HN: Do you think handwritten SQL is error prone? If so, why? | Hacker News Reader

Ask HN: Do you think handwritten SQL is error prone? If so, why?

6 pointsit12 comments

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.

View on HN

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.

This has indeed been my own experience. I do wish we could get rid of our ORM and use Postgrest instead but the opinion was put forward that we won't be able to hire people who can write correct SQL.

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

I see. So it's a face-saving way to talk about this. That seems plausible.

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.

ORMs are omnipresent in web development, but I've seen this point of view that they're harmful being kicked around. I'm interested in knowing the tradeoffs, and also what it looks like to build applications without ORMs. What kind of stack are you using? Any information about the pros/cons?

We're using Typescript with typeorm and a Postgres backend. The backend speaks graphql to a frontend written with Typescript and React.

Pro: Lots of people seem to be doing it this way, so there's plenty of info out there and it's probably easier to hire more people to work on it.

Cons: It's easy to make mistakes about authorization and even just wiring things up since typescript's type system doesn't catch some bugs that it seems like it should catch. It's tedious compared to postgrest, at least from what I've seen so far.

Thanks. How do you like GraphQL?

I like its RPCishness, but it also seems to require a lot of repetitive coding.

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.

Reply on news.ycombinator.com