Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres's lateral joins allow for quite the good eDSL

bensimms.moe

Post reply on HN
Print view View original

21–26 of 26 posts

Re: Postgres's lateral joins allow for quite the good eDSL

#21

Post by Izkata »

Twisell wrote: post #17
kovacs_ wrote: post #13

Earlier quoted context omitted.

I think the point they were trying to make is for using ORM for everything until you need a query complex enough or performant enough to drop back to a raw SQL layer. That's the pattern I've seen the most with ORM setups these days. That or dropping performance heavy sql into stored procedures but in the end it's all a matrix of ease of use/maintainability in some scenarios vs full control and performance tuning and…

Then please forgive I was triggered on their introductory expression "Raw SQL is great for simple queries, (...)" I understand your point but I'm not sure it's efficiency to use an ORM that abstract the easy stuff away from the programmer but rely on expert level knowledge to solve what remain. Because at this point the developer that build everything with an ORM will either: - Face complexs SQL query that he'll have…

> Then please forgive I was triggered on their introductory expression "Raw SQL is great for simple queries, (...)"

I think they might have had a second point in there that they removed but accidentally left part in. This part of the first sentence:

> Raw SQL is great for simple queries, but gets old quick

Sounds like it belongs on a point about boilerplate around sending queries and reading results, that ORMs do for you.

Re: Postgres's lateral joins allow for quite the good eDSL

#22

Post by nijave »

deadbabe wrote: post #7
trueno wrote: post #4

guy i used to work with once said "you scared to rawdog sql or something" when talking about orms lmao. ive spent a good bit of time querying sql directly, to the point that even when im coding other stuff i actually kind of get his point. i highly prefer using the query language thats specifically specifically to query the database and idk if ill ever become an orm enjoyer side note: man i hate how people write sql,…

Unless you are working with different types of database systems and don’t want to rewrite the same queries in different languages for different databases, there is no reason to use an ORM.

Imo they work well for dynamic query composition.

Say you want to combine a few sets with dynamic where clauses then intersect a couple other sets. For example, you have a "products" API that lets the user pick from a bunch of different filters

It's pretty easy to composite all that together with a decent ORM

Also migration management and having a programmatic DB schema to object schema mapping is very convenient

I do tend to see a lot of bastardized queries, though from treating ORM objects like they're native in memory objects (N+1, using programming functions where SQL equiv would have been more efficient, pulling data back only to dump it into the where clause of another query)

Re: Postgres's lateral joins allow for quite the good eDSL

#23

Post by nicwolff »

nijave wrote: post #22
deadbabe wrote: post #7

Earlier quoted context omitted.

Unless you are working with different types of database systems and don’t want to rewrite the same queries in different languages for different databases, there is no reason to use an ORM.

Imo they work well for dynamic query composition. Say you want to combine a few sets with dynamic where clauses then intersect a couple other sets. For example, you have a "products" API that lets the user pick from a bunch of different filters It's pretty easy to composite all that together with a decent ORM Also migration management and having a programmatic DB schema to object schema mapping is very convenient I d…

You don't need an ORM for any of that, just a query builder. In Python e.g. you can use SQLAlchemy Core instead of the SQLAlchemy ORM.

Re: Postgres's lateral joins allow for quite the good eDSL

#24

Post by pphysch »

Twisell wrote: post #9
pphysch wrote: post #6

Earlier quoted context omitted.

Raw SQL is great for simple queries, but gets old quick when you are dealing with "higher order" parameterized queries with lots of joins / optional clauses and you may end up creating an awful bespoke query builder to address these challenges. Or just piles of big similar queries that make it impossible to refactor your data model. The sweet spot is an ORM that embraces dropping down into raw SQL where needed, inste…

Sorry for the burn, but you managed to contradict yourself mid writting (or maybe you used an AI that messed up your point?) >Raw SQL is great for simple queries, but gets old quick when you are dealing with "higher order" parameterized queries (...) >The sweet spot is an ORM that embraces dropping down into raw SQL where needed {...) So basically you said that ORM are great only for the sweet spot of "mildy complex…

Not a good look to accuse others of AI spam because you can't immediately grasp their point. Others clarified it well.

ORM has substantial utility beyond just abstracting across different backends (I personally think that is one of the worst reasons to use an ORM, migrating or joining across entire production database systems is not something you ever take lightly).

> So I'd stick with my strategy of mastering raw SQL. I never felt the need of switching tool specifically for mildy difficult query.

FWIW this attitude reminds me of devs who insist Haskell or Emacs whatever is the one true technology that can do everything. Hard to work with. They build ivory towers, complicated systems that only the author can enter.

SQL is great but has its limits. Same for ORMs. Sweet spot is in the gray zone.

Re: Postgres's lateral joins allow for quite the good eDSL

#25

Post by jon_richards »

nicwolff wrote: post #23
nijave wrote: post #22

Earlier quoted context omitted.

Imo they work well for dynamic query composition. Say you want to combine a few sets with dynamic where clauses then intersect a couple other sets. For example, you have a "products" API that lets the user pick from a bunch of different filters It's pretty easy to composite all that together with a decent ORM Also migration management and having a programmatic DB schema to object schema mapping is very convenient I d…

You don't need an ORM for any of that, just a query builder. In Python e.g. you can use SQLAlchemy Core instead of the SQLAlchemy ORM.

The main value I get from sqlalchemy is parsing the result into useful structures. Getting the two models out of a join, prefetching related objects, etc. Though I much prefer rust diesel’s approach of no lazy queries (prefetches returned as list[tuple[object, list[related_object]], though diesel had other issues for me). My policy with sqlachemy is to unwrap all results to that if I’m passing/returning them. No relationship access outside of the function making the query.

Re: Postgres's lateral joins allow for quite the good eDSL

#26

Post by forinti »

trueno wrote: post #4

guy i used to work with once said "you scared to rawdog sql or something" when talking about orms lmao. ive spent a good bit of time querying sql directly, to the point that even when im coding other stuff i actually kind of get his point. i highly prefer using the query language thats specifically specifically to query the database and idk if ill ever become an orm enjoyer side note: man i hate how people write sql,…

I went through a decade-long battle to get the veterans to use ANSI syntax with Oracle and we only really managed to change the culture because most of them retired.
Post reply on HN