LLlloeki·Aug 5, 2014
> 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
Read the rest
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