6 ms·
"Keep all logic out of the database. It already is the slowest point. And hardest to scale up." I don't think this is true in all cases. Sure, ORMs are awesome
by rahimnathwani 4y ago
"Keep all logic out of the database. It already is the slowest point. And hardest to scale up."
I don't think this is true in all cases. Sure, ORMs are awesome, but sometimes you need to write SQL queries by hand, and those queries necessarily implement some business logic (even if they're just retrieving data).
Nice explanation here:
https://tapoueh.org/blog/2017/06/sql-and-business-logic/ https://tapoueh.org/blog/2017/06/sql-and-business-logic/
- smt88 4y agoYeah, this is bad advice. For example, Postgres has fantastic "upsert" and data integrity checks that reduce round trips to the database, meaning they're incredibly fast and result in less-complex client code.
- lmm 4y agoThere is always an alternative to writing SQL queries by hand, and it's usually a better one IME. Any ORM worth its salt will let you do the query in your blog post via the ORM, as a single query.
- rahimnathwani 4y ago"Say now we want to display the album list sorted by album’s duration, shortest first." How would we do this in an ORM like ActiveRecord or Django ORM, without generating multiple queries?
- strken 4y agoI haven't used Rails or ActiveRecord for a long time, but from memory it gives you an escape hatch in the form of Arel that lets you represent parts of your query as SQL while still using the ORM for the rest. Edit: I think it's something like Album .select(:id, :name, "SUM(songs.length) AS total_length") .left_outer_joins(:songs) .group(:id) .order("SUM(songs.length) ASC") I think you don't even need to explicitly wrap it in Arel.
- kayodelycaon 4y agoI use rails every day. Looks close enough. You’d just need to specify the fields with the table name, I.e. ‘albums.id’. Also, for anyone reading, a total_length method dynamically will be added objects returned. :)
- slimdave 4y ago
- lmm 4y agoI don't know about ActiveRecord or Django specifically, but: the obvious way in their query builder? Adding a joined collection, doing an aggregation on it, and sorting by it, are all easy in any ORM worth bothering with. What did you try and where did you get stuck?
- deleted 4y ago[deleted]
- ColonelPhantom 4y agoIn Django, you would write an annotation as documented at https://docs.djangoproject.com/en/4.1/topics/db/aggregation/#generating-aggregates-for-each-item-in-a-queryset https://docs.djangoproject.com/en/4.1/topics/db/aggregation/... So something like Album.objects.annotate(duration=Sum('track__length')).order_by('duration') There are also libraries that enable you to define an annotation or aggregation inside a model, that you can then get with a call like select_properties, similar to the built-in select_related (which you use to get a foreign key in one query) and prefetch_related (which is select_related for many-to-many fields).
- rahimnathwani 4y agoThanks. I haven't used Django for ages, but do recall using annotate() in the past. At that time, I didn't need to worry about performance, so didn't look into what happens under the hood, i.e. whether the call generates more than one SQL query.
- WkndTriathlete 4y ago> There is always an alternative to writing SQL queries by hand, and it's usually a better one IME. I spent years writing code using Spring/Hibernate, and I can state with certainty that both of those statements are demonstrably false. Every application starts with good intentions, a simple CRUD webapp, and an ORM, then at some point the business requirements yield an N+1 problem in ORMs or several non-trivial left joins into records that don't map the shape of the entities. At that point it's far easier to write the query in straight SQL and produce a straightforward mapping into the record structure, which doesn't play well with the ORM because that bypasses its entity cache, which causes another huge set of problems on its own. So now not only is there ORM maintenance and SQL maintenance, there is now a problem with the conjunction of the two technologies.
- caladin 4y agoI agree but I think Hibernate is an extreme. There is a middleground, query builders, which enable dynamic query construction (e.g. dynamically appending filter predicates) with reduced cognitive load of something like Hibernate. I personally was heavily in the camp of "write raw queries ideally with code generation for statically typed/generated code" (as exist in Rust, Go, TypeScript, etc.), but I have since tempered my position since it does become a bit brittle and repetitive. Lately I've been playing with Jooq and it seems great. There are tradeoffs everywhere though, so with Jooq you still aren't 1-to-1 with raw SQL, there is a bit to learn, but I consider it a worthwhile investment (and a minor one relative to an actual ORM).
- int_19h 4y agoA pure query builder is just writing SQL on syntax tree level, more or less. This makes sense for the same reason why you want your macros to operate on ASTs and not raw text. But I would argue that it's still much closer to plain text SQL in the code than to any ORM.
- rahimnathwani 4y agoRight, but it lives with your application code and has the same syntax as the application code. That's probably preferable to SQL stored procedures (which often live outside source control).
- dimgl 4y ago> Keep all logic out of the database. It already is the slowest point. And hardest to scale up. That's... weird advice. I think a lot of what happens in Rails projects (based on my very limited experience) is that developers start to rely on this easy syntax that ActiveRecord provides and stop thinking about the queries that the ORM is creating. So you end up with these massive N+1 queries that kill performance. This is one of the reasons I like to stay away from ORMs at all costs. A majority of what an ORM provides can be solved by a view or a function. As to Ruby's performance... yeah... it's pretty terrible. We had to completely abandon Docker for Ruby on Rails because the performance was absolutely abysmal, even with VirtioFS enabled.
- boredtofears 4y agoThat's not a good reason to stay away from ActiveRecord. AR has plenty of ways to write optimal queries (all the way down to raw SQL). That said, green developers often won't ever go that far due to not knowing SQL well enough. No matter what, you gotta have some competency with SQL whether you're using an ORM or not. Things like DB views come with their own problems and constraints, as well as DB level functions (stored procedures).
- CoolCold 4y ago> We had to completely abandon Docker for Ruby on Rails because the performance was absolutely abysmal, even with VirtioFS enabled. can you share more details on this? not sure I get how any kind of VirtioFS comes into the game here
- 4y ago