Scuttle – SQL and Arel Editor

That being said, I've become increasingly convinced that constructing queries with Arel is a fun exercise, but ultimately not terribly worthwhile for those of us who are just writing applications.

There are three reasons I can think of that people write their queries with Arel:

1. Construction of queries with a large list of predicates taking advantage of Enumerable#inject and Arel's closure under composition without mucking about manipulating strings.

2. Unease about whether or not code is protected from SQL injections when written using more traditional methods.

3. Achieving database independence.

In the case of #1, there are some situations where this may make sense, as a convenience, however, there are ways to ensure data is properly quoted. Not least of these is using things like prepared statements or the pg gem's "exec_params" method. This means that the goals of #1 and #2 can be met in an arguably more readable way by composing strings and param substitution.

In any case, it's #3 that I regularly hear people advocate as the reason they write their queries in Arel. My problem with that is that database independence is a myth.

Oh, I don't mean that you can't write some subset of queries that will work on any database you like, nor that you can't abstract away some of the differences on the surface of things like limiting result sets, pagination strategies, and so on. In fact, this is where Arel shines and a big part of why it's being used by ActiveRecord in the first place.

But I don't believe it makes much sense for typical app developers to attempt to write their queries in Arel in the attempt to achieve database-independence because most of the things that Arel can do for you in that regard are already exposed at the ActiveRecord level, with possible exception of the case-insensitive matching via ILIKE in PostgreSQL.

I've come to believe that database-independence is something you will only achieve if you aren't doing anything interesting with your database, and you're trying to treat it as a dumb data store. This is what ActiveRecord would prefer you do -- you see this guidance in things like validations, callbacks, and the like. That doesn't mean it's the best of ideas.

Turns out that relational database management systems are really good at managing relational data, and to treat them as a dumb place to store your bits is to miss out on a lot of that power. Along with ORMs has come some degree of learned helplessness when it comes to the harnessing of that power.

So, before you write your next query in Arel, ask yourself whether or not you're really gaining anything by doing so. It's probable that the idea you're trying to express would be more clearly and concisely expressed by showing the SQL you're going to run.

Again, none of this is intended to take away from how cool this app is -- just don't go replacing every single query in your app with Arel equivalents, okay?

Why does everybody hate SQL so much that they have to invent their own query language which will eventually evolve to have the same capabilities as SQL but which will look differently, and will be specific to a host language?

Sure. There's the issue of SQL injections, but libraries have gotten good enough for these not to happen any more, to the point that writing straight SQL only has advantages: It works in any host language/framework, everybody understands it even if they are new to a language/framework and everybody can use the same language in their database GUI that they use while programming.