I agree that infinite ranges are best!
However, I would order the other two differently, or perhaps label them equals.
Contributors to Rails have repeatedly stated that Arel is considered an internal, private API, and it is not recommended for use in application code. To my knowledge, they also do not explicitly call out changes to Arel in release notes. I realize their pleading gets little attention. They also know their pleading gets little attention. That does not make it a good idea to ignore those pleas.
In the case of raw SQL for a comparison operator, the two proposed drawbacks are less impactful (in my opinion) than requests from the Rails team.
Yes, raw SQL is not preferable in a general sense. It also technically has a higher risk of injection, in general cases. However, when used with keyword interpolation, the values will ultimately run through ActiveRecord::ConnectionAdapters::Quoting#quote. If your Ruby Date object (or an ActiveSupport::TimeWithZone object, or any other comparable database type with a Ruby equivalent) would cause an issue in that code, we’ve all got much bigger problems than just less-than and greater-than operators.
With regards to “database adapter compatibility”, I question whether less-than and greater-than are, in reality, not portable across different SQL databases? I am ignorant where this might be so, and would be happy to learn of those cases.
But if so, is that transition between two database engines (with such wildly different comparison operators, and therefore presumably other differences?) more likely than changes to a private/internal API, or less likely? It’s a bet on one risk or another, I think either one can be said to be crappy bet in a general sense.
In the case of these comparison operators (rather than “in general”), it feels like an incredibly minor difference, but one that leans toward the raw SQL. They are both changes which could bring pain. One of the changes you are possibly in of control of: Are you likely to change databases to one which does not support the > and < operators? The other change you do not control: does the Rails core team change something internal to Arel?
I really really wish queries in ActiveRecord could be built like in Sequel. It’s so much nicer than Arel, which like you said you really shouldn’t be using in production anyway. Honestly, the only way to do anything relatively complex with the database in ActiveRecord involves string interpolation and sanitization. It’s the biggest complaint I have with the entire stack.
I have only used Sequel on one side project. I really, really enjoyed it, and wish I had the opportunity to use it at work. Alas, decisions made years ago about this-or-that ORM are not worth the literal business cost to undo at the expense of more impactful, revenue-driving features.
One of the ideas of ActiveRecord in its early days, as stated by DHH himself, is not that SQL is bad and we should avoid writing it at all costs for some ideological reason. Instead his idea was that the simplest of SQL queries (e.g. a bunch of WHERE clauses with a LIMIT or JOIN thrown in) should be able to be expressed in very simple, application-native code. Not exactly his words, but something like that, as well as some comment about how ActiveRecord works very purposefully to let you write raw SQL when you feel you need to. If I could find the right Rails book I purchased once-upon-a-2010 I would find the exact quote, but I think the idea remains.
Sequel is great, but I have not used it “in anger” to know where the warts are. ActiveRecord has warts, and I know where they are. Despite those, it is good enough in many cases, and in the cases where it is not, was explicitly built to allow the programmer an “out”, and to write SQL when they really need to.
I have listened to the The Bike Shed podcast for many years running. During the era when Sean Griffin was a host, he was both paid to contribute to ActiveRecord full-time (I think?) and was building a new, separate ORM in Rust. Some of the discussions provided a very interesting lens into some of the tradeoffs in ActiveRecord: which were inherent, and which were just incidental choices or entrenched legacies that need not remain in an ideal world.
EDIT: Followup thought. You really do need a mental model for ActiveRecord::Relation when using “ActiveRecord”. Something I contributed at work (and which I hope to open source somehow in 2020) was an extension (patch?) to the awesome_print gem that previews ActiveRecord::Relation more intelligently. After building it, I realized that both junior and mid-level engineers on my team did not completely grok ActiveRecord::Relation, and how that just being able to see bits of it splayed out, in chunks more discrete than just calling #to_sql, helped them feel more confident that what they were building was the right thing.
The problem with interpolating to_sql or using any form of SQL strings is that ActiveRecord scopes can no longer be composed for any mildly more complicated/useful queries, especially if ActiveRecord tries to alias a sub-query one way or another as strings are exempt from aliasing. ActiveRecord doesn’t parse any SQL strings. This is a problem as you don’t know who or what will consume/compose queries with those scopes using SQL strings later. Changing a scope which is used in many contexts to use literal SQL becomes a very dangerous undertaking as it might break many of its consumers due to the above. So I’m with @colonelpanic on this one. IMO, Rails Core team should either embrace Arel and its direct use or maybe replace it with something better.
The other thing I’ve had success with in rails: PostgreSQL supports updatable views.
Turning a monster query into a view is a big, ugly undertaking - but so far I’ve only needed it after a project has become a success (at which point I don’t mind too much) and tends to happen to the least-churned tables (I’ve only had to modify these kind of views once or twice).
The bigger concern with SQL injection is future developers adding into the string unsafe code, so avoiding them is preferable.
As far as database compatibility, there are plenty of non-SQL database adapters available, and sticking to some form of Arel or built-in syntax, rather than SQL, keeps it more likely to translate to many different databases. It’s not “a must”, but it’s pretty sweet to swap out adapters on an app and have everything “Just work”
As far as database compatibility, there are plenty of non-SQL database adapters available, and sticking to some form of Arel or built-in syntax, rather than SQL, keeps it more likely to translate to many different databases.
I am highly skeptical that there are actually that many databases in use which wouldn’t be just as happy with the simpler greater-than/less-than formulations.
With regards to “database adapter compatibility”, I question whether less-than and greater-than are, in reality, not portable across different SQL databases? I am ignorant where this might be so, and would be happy to learn of those cases.
FWIW here are the ORM-to-SQL operator mappings for Django’s four built-in database backends:
I agree that infinite ranges are best!
However, I would order the other two differently, or perhaps label them equals.
Contributors to Rails have repeatedly stated that Arel is considered an internal, private API, and it is not recommended for use in application code. To my knowledge, they also do not explicitly call out changes to Arel in release notes. I realize their pleading gets little attention. They also know their pleading gets little attention. That does not make it a good idea to ignore those pleas.
In the case of raw SQL for a comparison operator, the two proposed drawbacks are less impactful (in my opinion) than requests from the Rails team.
Yes, raw SQL is not preferable in a general sense. It also technically has a higher risk of injection, in general cases. However, when used with keyword interpolation, the values will ultimately run through
ActiveRecord::ConnectionAdapters::Quoting#quote. If your Ruby Date object (or anActiveSupport::TimeWithZoneobject, or any other comparable database type with a Ruby equivalent) would cause an issue in that code, we’ve all got much bigger problems than just less-than and greater-than operators.With regards to “database adapter compatibility”, I question whether less-than and greater-than are, in reality, not portable across different SQL databases? I am ignorant where this might be so, and would be happy to learn of those cases.
But if so, is that transition between two database engines (with such wildly different comparison operators, and therefore presumably other differences?) more likely than changes to a private/internal API, or less likely? It’s a bet on one risk or another, I think either one can be said to be crappy bet in a general sense.
In the case of these comparison operators (rather than “in general”), it feels like an incredibly minor difference, but one that leans toward the raw SQL. They are both changes which could bring pain. One of the changes you are possibly in of control of: Are you likely to change databases to one which does not support the
>and<operators? The other change you do not control: does the Rails core team change something internal to Arel?I really really wish queries in ActiveRecord could be built like in Sequel. It’s so much nicer than Arel, which like you said you really shouldn’t be using in production anyway. Honestly, the only way to do anything relatively complex with the database in ActiveRecord involves string interpolation and sanitization. It’s the biggest complaint I have with the entire stack.
I’ve had some success using interpolation with
to_sql(which sanitizes for you).It’s still a bit yuck but it’s the least bad alternative I’ve found in rails.
I have only used Sequel on one side project. I really, really enjoyed it, and wish I had the opportunity to use it at work. Alas, decisions made years ago about this-or-that ORM are not worth the literal business cost to undo at the expense of more impactful, revenue-driving features.
One of the ideas of ActiveRecord in its early days, as stated by DHH himself, is not that SQL is bad and we should avoid writing it at all costs for some ideological reason. Instead his idea was that the simplest of SQL queries (e.g. a bunch of WHERE clauses with a LIMIT or JOIN thrown in) should be able to be expressed in very simple, application-native code. Not exactly his words, but something like that, as well as some comment about how ActiveRecord works very purposefully to let you write raw SQL when you feel you need to. If I could find the right Rails book I purchased once-upon-a-2010 I would find the exact quote, but I think the idea remains.
Sequel is great, but I have not used it “in anger” to know where the warts are. ActiveRecord has warts, and I know where they are. Despite those, it is good enough in many cases, and in the cases where it is not, was explicitly built to allow the programmer an “out”, and to write SQL when they really need to.
I have listened to the The Bike Shed podcast for many years running. During the era when Sean Griffin was a host, he was both paid to contribute to ActiveRecord full-time (I think?) and was building a new, separate ORM in Rust. Some of the discussions provided a very interesting lens into some of the tradeoffs in ActiveRecord: which were inherent, and which were just incidental choices or entrenched legacies that need not remain in an ideal world.
EDIT: Followup thought. You really do need a mental model for
ActiveRecord::Relationwhen using “ActiveRecord”. Something I contributed at work (and which I hope to open source somehow in 2020) was an extension (patch?) to the awesome_print gem that previewsActiveRecord::Relationmore intelligently. After building it, I realized that both junior and mid-level engineers on my team did not completely grokActiveRecord::Relation, and how that just being able to see bits of it splayed out, in chunks more discrete than just calling#to_sql, helped them feel more confident that what they were building was the right thing.The problem with interpolating
to_sqlor using any form of SQL strings is that ActiveRecord scopes can no longer be composed for any mildly more complicated/useful queries, especially if ActiveRecord tries to alias a sub-query one way or another as strings are exempt from aliasing. ActiveRecord doesn’t parse any SQL strings. This is a problem as you don’t know who or what will consume/compose queries with those scopes using SQL strings later. Changing a scope which is used in many contexts to use literal SQL becomes a very dangerous undertaking as it might break many of its consumers due to the above. So I’m with @colonelpanic on this one. IMO, Rails Core team should either embrace Arel and its direct use or maybe replace it with something better.The other thing I’ve had success with in rails: PostgreSQL supports updatable views.
Turning a monster query into a view is a big, ugly undertaking - but so far I’ve only needed it after a project has become a success (at which point I don’t mind too much) and tends to happen to the least-churned tables (I’ve only had to modify these kind of views once or twice).
I have very little sympathy for this position because the official query interface is simply not adequate for even mildly complicated use-cases.
I’ve been using Arel directly, and even patching in new features, for ten years. Can’t think of a time it’s ever been an issue.
I will continue to use Arel until a better alternative presents itself. String interpolation is not a serious alternative.
In Rails’ code, the core example of utilizing #arel_table is exactly greater_than: https://github.com/rails/rails/blob/c56d49c26636421afa4f088dc6d6e3c9445aa891/activerecord/lib/active_record/core.rb#L266
The bigger concern with SQL injection is future developers adding into the string unsafe code, so avoiding them is preferable.
As far as database compatibility, there are plenty of non-SQL database adapters available, and sticking to some form of Arel or built-in syntax, rather than SQL, keeps it more likely to translate to many different databases. It’s not “a must”, but it’s pretty sweet to swap out adapters on an app and have everything “Just work”
I am highly skeptical that there are actually that many databases in use which wouldn’t be just as happy with the simpler greater-than/less-than formulations.
FWIW here are the ORM-to-SQL operator mappings for Django’s four built-in database backends:
So if there’s a database where
>and<aren’t the greater-than/less-than operators, it isn’t one of those four.