
Ask HN: Do you think handwritten SQL is error prone? If so, why? - it
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&#x27;d like to understand why someone would say that.
======
BjoernKW
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.

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

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

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

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

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

~~~
potta_coffee
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?

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

~~~
potta_coffee
Thanks. How do you like GraphQL?

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

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

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

