3 ms·
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
by rwmj 4y ago
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.
- eitland 4y agoMight 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.
- rwmj 4y agoKind 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.
- steve-chavez 4y agoSQL injection is always possible with an ORM, since they always allow executing raw SQL as an escape hatch.
- eitland 4y agoTrue. 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 ;-)
- sodapopcan 4y agoORMs 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.
- aeyes 4y agoThis 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.
- tough 4y agoI 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
- emaginniss 4y agoI 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.
- hot_gril 4y agoIt'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.
- hot_gril 4y agoAnd 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."
- bagol 4y agoAnyone 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".