You should probably tell us at the start of the article what eDSL is.
Postgres's lateral joins allow for quite the good eDSL
11–20 of 26 posts
Re: Postgres's lateral joins allow for quite the good eDSL
#12Only time I've ever really used CROSS LATERAL JOIN in postgres is when working with JSONB documents that I'd like to put into a relational schema, e.g. given the data
{"data": [
{"id":1,"value":"foo"},
{"id":2,"value":"bar"},
...
]}
CREATE TABLE my_documents(doc JSONB); SELECT t.id,t.value FROM my_documents CROSS JOIN LATERAL jsonb_to_recordset(doc #> '{data}') AS t(id INTEGER, value TEXT);
id | value
----+-------
1 | foo
2 | bar
IIRC it's really only in a LATERAL JOIN because laterals are the only production rules that let you alias a function call (jsonb_to_recordset()) with explicitly declared column types.
Re: Postgres's lateral joins allow for quite the good eDSL
#13Earlier 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…
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 what makes sense for that use case.
Re: Postgres's lateral joins allow for quite the good eDSL
#14Only time I've ever really used CROSS LATERAL JOIN in postgres is when working with JSONB documents that I'd like to put into a relational schema, e.g. given the data {"data": [ {"id":1,"value":"foo"}, {"id":2,"value":"bar"}, ... ]} the following SQL: CREATE TABLE my_documents(doc JSONB); SELECT t.id,t.value FROM my_documents CROSS JOIN LATERAL jsonb_to_recordset(doc #> '{data}') AS t(id INTEGER, value TEXT); ... sho…
Yes, I have a similar only usage but using JSONB_EACH. You can actually replace "CROSS JOIN LATERAL" with a comma which I think is clearer.
However, it working as a lateral join is critical as you need the function to fire for every row.
Re: Postgres's lateral joins allow for quite the good eDSL
#15You should probably tell us at the start of the article what eDSL is.
An eDSL is an (e)mbedded (D)omain-(S)pecific (L)anguage. In other words, it's a language for describing domain-specific entities and operations, that happens to be embedded into an existing (host) language rather than being given its own standalone parser, interpreter, compiler, etc. An eDSL gets to piggy-back off of the syntax and semantics of the host language, but extends it with domain-specific concepts in (hopefully) a way that integrates well with the host language.
Lots of things that are "just" libraries could also reasonably be thought of as eDSLs.
Re: Postgres's lateral joins allow for quite the good eDSL
#16Only time I've ever really used CROSS LATERAL JOIN in postgres is when working with JSONB documents that I'd like to put into a relational schema, e.g. given the data {"data": [ {"id":1,"value":"foo"}, {"id":2,"value":"bar"}, ... ]} the following SQL: CREATE TABLE my_documents(doc JSONB); SELECT t.id,t.value FROM my_documents CROSS JOIN LATERAL jsonb_to_recordset(doc #> '{data}') AS t(id INTEGER, value TEXT); ... sho…
Yes, I have a similar only usage but using JSONB_EACH. You can actually replace "CROSS JOIN LATERAL" with a comma which I think is clearer. However, it working as a lateral join is critical as you need the function to fire for every row.
> However, it working as a lateral join is critical as you need the function to fire for every row.
Right, what I mean is, for functions in the standard expression position, e.g. `SELECT COALESCE(..., 0)`, those functions will also fire for every row. jsonb_to_recordset however needs to know the schema of the table it will output, and the only way syntactically to declare the column types output by a function returning a recordset is in LATERAL clauses [0].
Re: Postgres's lateral joins allow for quite the good eDSL
#17Earlier quoted context omitted.
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…
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 to build from the ground up by summoning expert SQL skills they probably haven't use for a while - Seek external help from an SQL expert than doesn't know how the system was build in the first place*
If you know you'll need database specific SQL optimizations on complex query in the end, I guess using SQL everywhere could also make sense. Same language and code logic from simple to complex query and a complexity gradient in between.
*I guess same point could be made about vibe coding actually.
Re: Postgres's lateral joins allow for quite the good eDSL
#18[flagged]
Re: Postgres's lateral joins allow for quite the good eDSL
#19Haskells beam, squeal and others do this. With lateral join queries form a lawful monad.
Re: Postgres's lateral joins allow for quite the good eDSL
#20Earlier 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…
Not OP but as I understand it they say RAW sql works great for the very basic stuff. But when it becomes a bit more complicated it is easier to mess up and an ORM is preferred. Then you have the very complex queries where ORM just become more difficult or creates bad performance. Then you have to use raw sql.
So basically raw sql or orm does not matter for very simple queries. For more standard queries with lots of joins and where clauses ORM is better to not deal with complex sql. Then you reach a point where you must use raw sql because the alternative is worse.
In most apps most queries is probably in that middle chunk.