Hacker News Reader

Comment by rwmj | Hacker News Reader

The main tip I learned from using PostgreSQL (or relational databases in general) is never use an ORM. They cause far more trouble than they are worth and it's far easier to see what is going on when you're writing SQL queries directly.

View on HN

ORMs can be great in many situations. Any ORM worth its salt has a way to spit out exactly what SQL it is generating. I have worked on apps, though, where we end with a disproportionate amount of raw SQL so obviously in those scenarios they become useless. It's almost always been writing some sort of reporting system in Postgres instead of using a column store, though.

I am strongly biased against ORMs, but I still recognize their use in CRUD operations. In any case where you are doing bulk operations, true upserts, or more complex queries, then I 100% agree.

It's not just the fact that it hides the queries. ORMs are all-around cancerous. I've been on several teams that's tried to use one, and there were regrets every time.

And by regrets I don't mean "this ORM wasn't worth," I mean "the ORM ruined everything to the point where we have to rewrite it."

This is ancient knowledge and I would have agreed with you 15 years ago, today the only reason to not use an ORM is analytical queries.

Since the Postgres planner doesn't really allow you to tune your query there aren't many ways to construct your query in a way which would to a much worse execution plan. Over the years we have migrated most raw SQL back to using the ORM without taking performance hits, pretty much the only remaining raw queries are CTEs where we force a certain order of query execution.

Usually these ORM problems are caused by schema design anyways. If you need 10+ joins you are going to have a hard time with or without an ORM.

Do CTEs actually force an order of execution? I thought the point was that they are declarative

Before Postgres 12 yes - always, starting with Postgres 12 you have to add "AS MATERIALIZED" to force the order.

Might be a good idea if you arr the only one on the team or if everyone is like you.

If not, be prepared to check for sql injection vulnerabilities the first n PRs from any new team member.

Also to explain how to do it and your reasoning for it.

Kind of goes without saying that any framework/library you use must not allow you to write SQL injection vulnerabilities, and if it does you should stop using it right now.

SQL injection is always possible with an ORM, since they always allow executing raw SQL as an escape hatch.

True. But unlike with the alternative that many end on, raw sql, it doesn't funnel anyone into a place where writing

  "select * from sometable where id=" + id

feels like a logical next step unless you actually have studied the subject or read the manual ;-)

I dunno, sounds like a great way to get rekt to deal with SQL queries directly, there's some ORM's that let you do this when needed but default to the ORM DSL when needed.

Im a happy user of prisma, I value It supports several databases and not only pg. using for example sqlite for localhost dev has its perks, and it's easy to move later to other stuff if you have already planned for it and not using types that are incompatible between your two targets

Anyone not using ORM, will eventually build his own ORM (or at least query builder). I think the argument is not about "not using ORM", but more about "not using ORM made by someone else".

Reply on news.ycombinator.com