41–50 of 76 posts
Earlier quoted context omitted.
Little Bobby Tables would love that to pieces.
SQL injection is totally optional. If you use prepared statements, which are easier to use than formatting arguments yourself, it’s not a possibility.
There are very real reasons why writing raw SQL is a pain. You can make arguments about typesafety of queries, maintainability, mapping into application level data structures/types yadayada. Imho the primary argument is that you cannot efficiently write a query that doesn't suffer from duplication of data. If you have an entity A that has many B's, and that same entity A also has many C's, then if you join on both you now are loading A * B * C rows. This is going to be slow too, and is difficult to detangle.
ORMs in their current form are terrible at tackling this problem, and people are right to call them out for that. "Just" lazy loading or "just" eager loading both do not scale, nor does doing firing event listeners on a per-entity basis. But rawdogging SQL is not a viable solution either, and I wish people advocating that would stop this attitude of 'oh you prissy dev using your puny ORM, not willing to write a line of SQL, look at me with my glorious hair on my chest and superior intellect writing pristine queries'.
I think the N+1 problem is overblown. The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.
I see all these comments stating 'oh ORMs are bad' and 'just write some SQL'. Yes, you should probably not be afraid of SQL, and yes, using an ORM for everything is probably not great, and no, ORMs aren't a full replacement for writing SQL occasionally, but taking an extremist pro-SQL point-of-view is not doing any favors to this debate. There are very real reasons why writing raw SQL is a pain. You can make argument…
Better yet, the wire formats should have efficient representations for things like column store compression.
Pushing the subresource fetching down to the database requires using JOINs and fails badly when you have multiple one-to-many relations in one fetch.
Just do a single, separate query per table.
I see all these comments stating 'oh ORMs are bad' and 'just write some SQL'. Yes, you should probably not be afraid of SQL, and yes, using an ORM for everything is probably not great, and no, ORMs aren't a full replacement for writing SQL occasionally, but taking an extremist pro-SQL point-of-view is not doing any favors to this debate. There are very real reasons why writing raw SQL is a pain. You can make argument…
The fundamental problem is with the wire protocol. It’s inherently tabular, but should actually be more like a binary JSON format, with hierarchical representation for joins to avoid repeating the data. Better yet, the wire formats should have efficient representations for things like column store compression.
If you look at the end-to-end problem of 'what is the minimum amount of data I need during this request' vs 'how much data do I fetch, and what is my total latency / number of roundtrips to the db doing so?' I think for most ORM patterns that use lazy loading your primary target is reducing roundtrips, and for most hand rolled queries or ORMs tweaked to do eager loading, the primary target is deduplicating the results.
My take on this is that a decent approximation is a query per relation you're fetching, so if you have 10 entities A in a transaction, and each has 20 entities B attached, ideally you want 2 queries: one for the 10 entities A, and one for the 200 entities B. Lazy loading will give you 1 query for A + 10 queries for B, and eager loading will duplicate the 10 A entities data 20 times each (and that problem gets worse as your graph gets bigger with more one-to-many relations).
Once you run into the raw data transfer between database and backend being the limit, trying to optimize that protocol comes into play, but at least in the use cases I tend to have this is not usually a bottleneck. Besides, I'll typically serialize the data fetched to send out over HTTP again, which essentially has the same challenges if you're not using protobuf or so.
I think the N+1 problem is overblown. The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.
Earlier quoted context omitted.
SQL injection is totally optional. If you use prepared statements, which are easier to use than formatting arguments yourself, it’s not a possibility.
Sure, if you do it right, you can avoid the problem, but why not use a tool that prevents the problem from happening in the first place?
Go's endless verbosity and lack of dynamic features is a blessing, not a curse. Because you have to write your own data access layer, you can break this blatantly false assumption that what you need in the UI is the same as what you need in the database.
To break the N+1 problem, you can do funky stuff like pull back sub-components as array fields in the main component, or pull multiple result sets, or concatenate the sub-component data into a single text field, whatever your UI requires. Because you're not forcing the databases query to map 1:1 with whatever structs you've got inside your application, you can get creative with how you structure the database storage, and how you query that storage. You can create funcs in the database that do crazy shit with the data to return exactly what the UI requires in a custom struct. Because it all runs on the database itself it's fast and painless. Because you have to manually map all the structs in your application yourself (thanks Go!) then you are not constrained to mirror the database structure in your code. Your UI code does what it needs, your database does what it needs, and you can map between them however you want.
ActiveRecord is easy to use, and very concise, which is what it optimises for. Go is not optimising for the same thing. The author is trying to recreate ActiveRecord in Go without realising that ActiveRecord is a straightjacket, and obviously struggling. If you free yourself from those constraints, the world becomes a simpler, better, place.
Earlier quoted context omitted.
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-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…
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.…
https://learn.microsoft.com/en-us/ef/core/querying/single-sp...