4 ms·

So a super complicated work around instead of just doing sql queries or using a query builder ???

lainga 2y ago
Or else, if the post is talking about a "public-facing API resource", can someone tell me why the API wouldn't implement querying for multiple of the same record type at once? It just seems to me that choosing between getting 1 owner, and "get ALL owners" (as TFA puts it), is like a law of the excluded middle
- metadat 2y ago
  It's tricky because in some cases you might be able to batch all the queries up front, but in others you will only know the IDs you need to fetch after you get one or more intermediate results back and apply business logic to arrive at a decision. As of today there's no silver bullet beyond having a solid and principles-first understanding of your database and related infrastructure.
- Ozzie_osman 2y ago
  This might be true for some APIs but if you're letting the caller control what they fetch, you don't really have that luxury. An extreme example is Graphql-based APIs but even Rest-based APIs might allow some flexibility to decide what they fetch.
sgarland 2y ago
That was my first thought as well. Devs will do anything to avoid learning SQL. You’re spending 20x the code and probably 1000x the time coming up with this nonsense, and for what – so you can say there isn’t any raw SQL in your code base? It’s as ridiculous as denouncing someone for occasionally dropping into Assembly for a specific purpose, or writing a bit of C to call from Python, etc.
- stephen 2y ago
  I can assure you that brandur knows SQL. :-) (I don't know him personally, but have been following his blog for years.) What these "just write SQL" rants are missing is encapsulation--let's say you've got a business logic, like "if the account is disabled, render the account name as 'Foo (disabled)'". You want to write this logic in your preferred backend language, Go/TS/C/etc. This works fine, in the /account/X endpoint (just load the account, and apply the logic). But now what about the /accounts/client:Y endpoint (load all accounts, all the logic for all of their accounts) As time goes by, you end up having 10-20 endpoints that all "return some part of account" as their payload, and you want the same "Foo (disabled)" business logic. Your options are: 1. Build a single, giant SQL statement that strings together every snippet of business logic applicable to this endpoint (bulk friendly b/c the db is doing all the cross-entity work w/joins) 2. Push the business logic down into the db layer (simple for this, just string concate with an if, but what about anything that is a loop? doable in SQL but tedious) 3. Use your language's abstractions, like functions, to organize the business logic (what Brandur is attempting to do). Nearly everyone wants to do 3, because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures).
  - d0100 2y ago
    Since I mainly use Postgresql, I mix 1, 2 and 3: I create CTE's in code which are then reused in queries sqrl.Select("id", "cte1.*", "cte2.*").From("mytable").Join(CTE1{}).Join(CTE2{}).Where(etc)
  - xarope 2y ago
    for 3, you could write a stored proc to handle the various situations, and call that stored proc appropriately, letting the DB engine optimize appending "(disabled)". however, I do wish Go had a map function ala python map() as opposed to just verbosely writing more for loops or a complicated thread-safe goroutine. Seems almost strange that Google, who popularized the mapreduce model, doesn't want it in Go.
    - jimbokun 2y ago
- unrealhoang 2y ago
  TFA is using raw SQL (via sqlc) if you read carefully. And no, raw SQL doesn't get rid of N+1 issues.
  - dventimi 2y ago
    PostgREST for instance avoids the alleged problem by generating a single SQL statement. So does Hasura, PostGraphile, and probably Prisma for that matter.
    - sgarland 2y ago
      Prisma “avoids” problems (at least until recently) by just doing SELECT * UNION ALL SELECT *… and then handling joins internally. Utter madness.
      - deleted 2y ago
        [deleted]
- ec109685 2y ago
  Without two phases: figure out what you need for the resource, execute query, how can you avoid N+1 problem, raw sql or not.
  - sgarland 2y ago
    Recursive CTEs, LEFT JOINs, Stored Procedures…
deleted 2y ago
[deleted]
tedunangst 2y ago
Some of us just aren't smart enough for sql. I'm perpetually running into the situation where I want to join one table with another that has multiple rows. Like a blog post with tags. Exactly like this: https://stackoverflow.com/questions/8201462/join-with-another-table-containing-tags https://stackoverflow.com/questions/8201462/join-with-anothe... For which the answer is oh, just use GROUP_CONCAT, which isn't even SQL. And I've still got to fix it up by running split when I get it back. Nor does it work particularly well if you're joining against something that isn't a single string column. So I just bang the rocks together like a caveman and write a second query to go back and fetch all the tags, then loop around assign them to the matching posts.
- Mavvie 2y ago
  I think that's exactly correct. You either do split queries (with more latency) or you do a join (and risk Cartesian explosion). Most ORMs should do this for you.
- superice 2y ago
  This seems fundamentally unfixable with current ORMs. You either have to pick between lazy loading per entity, or eager load and carthesian product everything, which breaks badly if you are dealing with multiple one-to-many relations. Our solution was to write our own ORM-like system that “remembers” which entities you’ve loaded within the context of the transaction, and then will fetch the relation for all of them. So if you access a1.b then it will also fetch a2.b and cache it if you loaded a2 within that same transaction. The call to a2.b will then resolve instantly. So instead of n queries with n being the number of entities loaded in lazy loading, you’re doing n queries with n being the number of relations touched. The one bad failure case is if you update entities one-by-one but also accessing their relations in a loop since you invalidate the cache for that entity type on every write.
  - Izkata 2y ago
    Django (python) does lazy loading by default, cartesian product with "select_related()", and has a third option called "prefetch_related()" where it does only 2 queries then does the join programmatically for you instead of in the database. It's kind of like your custom system except you do have to specify ahead of time which fields to prefetch. It's also had this for over a decade so I have to wonder how rare it actually is in ORMs in general...

Or else, if the post is talking about a "public-facing API resource", can someone tell me why the API wouldn't implement querying for multiple of the same record type at once? It just seems to me that choosing between getting 1 owner, and "get ALL owners" (as TFA puts it), is like a law of the excluded middle

metadat 2y ago
It's tricky because in some cases you might be able to batch all the queries up front, but in others you will only know the IDs you need to fetch after you get one or more intermediate results back and apply business logic to arrive at a decision. As of today there's no silver bullet beyond having a solid and principles-first understanding of your database and related infrastructure.
Ozzie_osman 2y ago
This might be true for some APIs but if you're letting the caller control what they fetch, you don't really have that luxury. An extreme example is Graphql-based APIs but even Rest-based APIs might allow some flexibility to decide what they fetch.

sgarland 2y ago

That was my first thought as well. Devs will do anything to avoid learning SQL. You’re spending 20x the code and probably 1000x the time coming up with this nonsense, and for what – so you can say there isn’t any raw SQL in your code base? It’s as ridiculous as denouncing someone for occasionally dropping into Assembly for a specific purpose, or writing a bit of C to call from Python, etc.

stephen 2y ago
I can assure you that brandur knows SQL. :-) (I don't know him personally, but have been following his blog for years.) What these "just write SQL" rants are missing is encapsulation--let's say you've got a business logic, like "if the account is disabled, render the account name as 'Foo (disabled)'". You want to write this logic in your preferred backend language, Go/TS/C/etc. This works fine, in the /account/X endpoint (just load the account, and apply the logic). But now what about the /accounts/client:Y endpoint (load all accounts, all the logic for all of their accounts) As time goes by, you end up having 10-20 endpoints that all "return some part of account" as their payload, and you want the same "Foo (disabled)" business logic. Your options are: 1. Build a single, giant SQL statement that strings together every snippet of business logic applicable to this endpoint (bulk friendly b/c the db is doing all the cross-entity work w/joins) 2. Push the business logic down into the db layer (simple for this, just string concate with an if, but what about anything that is a loop? doable in SQL but tedious) 3. Use your language's abstractions, like functions, to organize the business logic (what Brandur is attempting to do). Nearly everyone wants to do 3, because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures).
- d0100 2y ago
  Since I mainly use Postgresql, I mix 1, 2 and 3: I create CTE's in code which are then reused in queries sqrl.Select("id", "cte1.*", "cte2.*").From("mytable").Join(CTE1{}).Join(CTE2{}).Where(etc)
- xarope 2y ago
  for 3, you could write a stored proc to handle the various situations, and call that stored proc appropriately, letting the DB engine optimize appending "(disabled)". however, I do wish Go had a map function ala python map() as opposed to just verbosely writing more for loops or a complicated thread-safe goroutine. Seems almost strange that Google, who popularized the mapreduce model, doesn't want it in Go.
  - jimbokun 2y ago
unrealhoang 2y ago
TFA is using raw SQL (via sqlc) if you read carefully. And no, raw SQL doesn't get rid of N+1 issues.
- dventimi 2y ago
  PostgREST for instance avoids the alleged problem by generating a single SQL statement. So does Hasura, PostGraphile, and probably Prisma for that matter.
  - sgarland 2y ago
    Prisma “avoids” problems (at least until recently) by just doing SELECT * UNION ALL SELECT *… and then handling joins internally. Utter madness.
    - deleted 2y ago
      [deleted]
ec109685 2y ago
Without two phases: figure out what you need for the resource, execute query, how can you avoid N+1 problem, raw sql or not.
- sgarland 2y ago
  Recursive CTEs, LEFT JOINs, Stored Procedures…

deleted 2y ago

[deleted]

tedunangst 2y ago

Some of us just aren't smart enough for sql. I'm perpetually running into the situation where I want to join one table with another that has multiple rows. Like a blog post with tags. Exactly like this: https://stackoverflow.com/questions/8201462/join-with-another-table-containing-tags https://stackoverflow.com/questions/8201462/join-with-anothe... For which the answer is oh, just use GROUP_CONCAT, which isn't even SQL. And I've still got to fix it up by running split when I get it back. Nor does it work particularly well if you're joining against something that isn't a single string column. So I just bang the rocks together like a caveman and write a second query to go back and fetch all the tags, then loop around assign them to the matching posts.

Mavvie 2y ago
I think that's exactly correct. You either do split queries (with more latency) or you do a join (and risk Cartesian explosion). Most ORMs should do this for you.
superice 2y ago
This seems fundamentally unfixable with current ORMs. You either have to pick between lazy loading per entity, or eager load and carthesian product everything, which breaks badly if you are dealing with multiple one-to-many relations. Our solution was to write our own ORM-like system that “remembers” which entities you’ve loaded within the context of the transaction, and then will fetch the relation for all of them. So if you access a1.b then it will also fetch a2.b and cache it if you loaded a2 within that same transaction. The call to a2.b will then resolve instantly. So instead of n queries with n being the number of entities loaded in lazy loading, you’re doing n queries with n being the number of relations touched. The one bad failure case is if you update entities one-by-one but also accessing their relations in a loop since you invalidate the cache for that entity type on every write.
- Izkata 2y ago
  Django (python) does lazy loading by default, cartesian product with "select_related()", and has a third option called "prefetch_related()" where it does only 2 queries then does the join programmatically for you instead of in the database. It's kind of like your custom system except you do have to specify ahead of time which fields to prefetch. It's also had this for over a decade so I have to wonder how rare it actually is in ORMs in general...