5 ms·

In real world, most of our "pattern" become technical debt as codebase grows very fast. My solution is just simply using raw sql query. Hide complexity of query

In real world, most of our "pattern" become technical debt as codebase grows very fast. My solution is just simply using raw sql query. Hide complexity of query behind another complex "ActiveRecord" is not the solution. The benefit is separation of concern when your team has a SQL specialist. And no more code technical debt as your project scales.

aantix 7y ago
Most of the relationships are belongs_to and has_many. How is a raw SQL more readable and expressive than that? Sure, there may be an occasional AR expression that could be optimized with a raw SQL query? But for most associations for information based systems, AR is incredibly descriptive and performant.
- ratww 7y ago
  ActiveRecord is far from straightforward when it comes to joins, unfortunately. Even if you use belongs_to/has_many you still have to manually perform the join, otherwise you'll get an N+1 issue which will affect your performance. On top of that there are four ways to perform that join: joins(:table), include(:table), eager_load(:table) or preload(:table). In SQL you can basically achieve the same level of expressiveness by using NATURAL JOIN.
- aikah 7y ago
  Most SQL databases now support returning rows in the form of JSON allowing N,N+1 queries to be fetched in a single call, so it's easier to deal with many to many relationships in SQL directly without an ORM. Query builders are still useful though.
kburman 7y ago
I highly disagree with you. I'm currently working on a Rails project which does exactly this. And it's a true nightmare to maintain or add a feature in it. Only exception for writing raw SQL is when you want CTE.
learc83 7y ago
I take a hybrid approach and try to build view backed models for complex queries. Then the rest of the team can use those models with ActiveRecord like they normally would.
revscat 7y ago
SQL is a living, breathing technical debt. So what I'm envisioning in your environment is that you've just shifted your problems from a functional OO language to... SQL, which is neither. And when those queries evolve into the unmanageable beasts that they almost always do, what then? At least with languages that have sensible means for code organization you can break them out into things like TFA is discussing. SQL -- the language -- is shit, and this is the emperor-wears-no-clothes of our industry. The belief that extensive use of SQL in application code is something all wizards should aspire for is just completely nonsensical.
- brightball 7y ago
  SQL the language isn't perfect but it is extremely powerful and useful. I'm not advocating for the OP on his extreme take, but yours is equally extreme in the other direction. Both are at odds with any semblance of an ideal approach.
mhd 7y ago
I've got some mixed feelings about this, although I don't disagree when it comes to the heart of that suggestion. For one, ORMs certainly reach their limits far too soon, and after that dealing with it is beyond the average use case, maybe even beyond the average developer working on the project. The problem is that the same is true for SQL when it gets complicated. Do you inner/left/lateral join, do you use 'exists' or 'in', CTEs, window functions etc. You're talking about an "SQL specialists", and there's a definite need for that here. Now, that takes either someone who had the experience in addition to their regular programming practice (i.e. someone senior), or a dedicated specialists -- which is a hard sell for small teams ('though probably a better idea than a 'build engineer' or 'UX expert', but I digress). If at all, structure the data and the application features so that you don't need to have anything too complex. If that doesn't seem possible right at the beginning, maybe work out the complex queries/views etc. when you're starting out and everyone is still cooperating, it's really, really troublesome if some lone engineer working on some feature suddenly has to whip out the stack of Celko books. But beyond that: If you can't fully factor out the complexity out of your application (i.e. in some external reporting tool, ETL job etc.), you'll have to put the query/statement somewhere. Whether that's something with ActiveRecord or a long literal statement. A lot of MVC frameworks have no proper place for that (definitely not in "C", and if it's just in the "M" part, you just postponed the issue). I've seen projects with MVC + Service and/or Query and/or Grid and/or Report and/or SQL directories. And then possibly splitting the content into client code and stored procedures/views/materialized views. This ain't really a solved problem, nor do we have clearly established best practices here.
lobo_tuerto 7y ago
I think Ecto has the best of both worlds (a useful abstraction over SQL and is low level enough) and the right approach: "Ecto is a toolkit for data mapping and language integrated query for Elixir." https://github.com/elixir-ecto/ecto https://github.com/elixir-ecto/ecto

aantix 7y ago

Most of the relationships are belongs_to and has_many. How is a raw SQL more readable and expressive than that? Sure, there may be an occasional AR expression that could be optimized with a raw SQL query? But for most associations for information based systems, AR is incredibly descriptive and performant.

ratww 7y ago
ActiveRecord is far from straightforward when it comes to joins, unfortunately. Even if you use belongs_to/has_many you still have to manually perform the join, otherwise you'll get an N+1 issue which will affect your performance. On top of that there are four ways to perform that join: joins(:table), include(:table), eager_load(:table) or preload(:table). In SQL you can basically achieve the same level of expressiveness by using NATURAL JOIN.
aikah 7y ago
Most SQL databases now support returning rows in the form of JSON allowing N,N+1 queries to be fetched in a single call, so it's easier to deal with many to many relationships in SQL directly without an ORM. Query builders are still useful though.

kburman 7y ago

I highly disagree with you. I'm currently working on a Rails project which does exactly this. And it's a true nightmare to maintain or add a feature in it. Only exception for writing raw SQL is when you want CTE.

learc83 7y ago

I take a hybrid approach and try to build view backed models for complex queries. Then the rest of the team can use those models with ActiveRecord like they normally would.

revscat 7y ago

SQL is a living, breathing technical debt. So what I'm envisioning in your environment is that you've just shifted your problems from a functional OO language to... SQL, which is neither. And when those queries evolve into the unmanageable beasts that they almost always do, what then? At least with languages that have sensible means for code organization you can break them out into things like TFA is discussing. SQL -- the language -- is shit, and this is the emperor-wears-no-clothes of our industry. The belief that extensive use of SQL in application code is something all wizards should aspire for is just completely nonsensical.

brightball 7y ago
SQL the language isn't perfect but it is extremely powerful and useful. I'm not advocating for the OP on his extreme take, but yours is equally extreme in the other direction. Both are at odds with any semblance of an ideal approach.

mhd 7y ago

I've got some mixed feelings about this, although I don't disagree when it comes to the heart of that suggestion. For one, ORMs certainly reach their limits far too soon, and after that dealing with it is beyond the average use case, maybe even beyond the average developer working on the project. The problem is that the same is true for SQL when it gets complicated. Do you inner/left/lateral join, do you use 'exists' or 'in', CTEs, window functions etc. You're talking about an "SQL specialists", and there's a definite need for that here. Now, that takes either someone who had the experience in addition to their regular programming practice (i.e. someone senior), or a dedicated specialists -- which is a hard sell for small teams ('though probably a better idea than a 'build engineer' or 'UX expert', but I digress). If at all, structure the data and the application features so that you don't need to have anything too complex. If that doesn't seem possible right at the beginning, maybe work out the complex queries/views etc. when you're starting out and everyone is still cooperating, it's really, really troublesome if some lone engineer working on some feature suddenly has to whip out the stack of Celko books. But beyond that: If you can't fully factor out the complexity out of your application (i.e. in some external reporting tool, ETL job etc.), you'll have to put the query/statement somewhere. Whether that's something with ActiveRecord or a long literal statement. A lot of MVC frameworks have no proper place for that (definitely not in "C", and if it's just in the "M" part, you just postponed the issue). I've seen projects with MVC + Service and/or Query and/or Grid and/or Report and/or SQL directories. And then possibly splitting the content into client code and stored procedures/views/materialized views. This ain't really a solved problem, nor do we have clearly established best practices here.

lobo_tuerto 7y ago

I think Ecto has the best of both worlds (a useful abstraction over SQL and is low level enough) and the right approach: "Ecto is a toolkit for data mapping and language integrated query for Elixir." https://github.com/elixir-ecto/ecto https://github.com/elixir-ecto/ecto