> The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic access to the SQL AST, so you can generate syntax as opposed to concatenate strings together. Kind of like a DOM API, but for SQL.
Congratulations, you just described Arel.
I liberally use rails/active_record where it shines (operating on a single record, or writing composable scopes) but very often find myself leveraging Arel (accessible via YourModel.arel_table) to generate a carefully crafted SQL AST.
At my current job we have two gems leveraging this power: "Massive Record", allowing one to perform bulk operations (insert, upsert) on huge lists of records in an efficient way (instantiating hashes instead of full-blown ActiveRecord instances), and "Chains", which allows one to handle authorization at a per-entity or per-record level with a iptables-like system. There is no way the generated queries could be sanely written by hand, nor as concatenated strings. Arel allows us to build highly dynamic queries while still tuning for performance. Database independence comes as a bonus, and we can easily extend Arel with more nodes, possibly some database specific ones that get selectively added depending on the configured database.
Arel also allows us to write clean and efficient database migrations, where we basically use your typical ActiveRecord faux models merely for datatable reflection to obtain column names and types.
Unfortunately SQL leaks through Arel's abstractions and make it behave in surprising ways. Arel falls short of achieving the goal of being able to modularise and compose queries. The same is true of nearly every SQL connectivity library (or so this article claims: http://www.try-alf.org/blog/2013-10-21-relations-as-first-cl...).
> Unfortunately SQL leaks through Arel's abstractions
This is voluntary. Arel allows one to write SQL almost as is, only with native types and features that make it possible to compose and aggregate queries. It's not a 1:1 mapping but it's not a blackbox abstraction either since the goal is to finely control your SQL. Of note, Arel is neither ActiveRecord nor ActiveRecord::Relation (which is what you get when you use #all, #where and scopes, sadly often mistaken for Arel)
The article you linked to is wrong, Arel is perfectly able to compose projections and joins, see this gist[0] implementing what the author demands.
Congratulations, you just described Arel.
I liberally use rails/active_record where it shines (operating on a single record, or writing composable scopes) but very often find myself leveraging Arel (accessible via YourModel.arel_table) to generate a carefully crafted SQL AST.
At my current job we have two gems leveraging this power: "Massive Record", allowing one to perform bulk operations (insert, upsert) on huge lists of records in an efficient way (instantiating hashes instead of full-blown ActiveRecord instances), and "Chains", which allows one to handle authorization at a per-entity or per-record level with a iptables-like system. There is no way the generated queries could be sanely written by hand, nor as concatenated strings. Arel allows us to build highly dynamic queries while still tuning for performance. Database independence comes as a bonus, and we can easily extend Arel with more nodes, possibly some database specific ones that get selectively added depending on the configured database.
Arel also allows us to write clean and efficient database migrations, where we basically use your typical ActiveRecord faux models merely for datatable reflection to obtain column names and types.
[0]: https://github.com/rails/arel