

Scuttle – SQL and Arel Editor - linc01n
http://www.scuttle.io/

======
emiller829
Hi all. Arel committer here. This is a very cool application, and I really
enjoyed attending (and helping answer questions) at Cameron's RailsConf talk.

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?

~~~
sudhirj
Is there any way to use the OR clause in a Rails SQL query without dropping
down to Arel? Can't seem to find a way.

~~~
sudhirj
Programatically, I mean. For instance, I can call `where` multiple times in a
loop to chain AND clauses.

~~~
emiller829
Not chainably, out of the box, though you can abuse build_where to do this and
join with strings easily enough.

------
pilif
I'm probably totally old-fashioned and crazy, but I personally think the SQL
code is much more readable and understandable than the arel code that's
generated.

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.

~~~
al2o3cr
"Sure. There's the issue of SQL injections, but libraries have gotten good
enough for these not to happen any more"

Yes. And in this case, the "library" is Arel...

------
nemasu
The button just deletes the first character for me.

------
codegeek
I would love something like this for SQLAlchemy. I even thought about writing
one myself that converts SQL query into a high level SQlAlchemy query
(assuming class/object names based on table names) but not sure if I have the
SQlAlchemy expertise.

------
ozh
(Not the best name maybe, there's a long established OSS project named Scuttle
already)

~~~
yebyen
I think you're referring to the Delicious-style "web bookmarks manager" clone
-- it has forks like SemanticScuttle as well, I agree, the name is taken. Came
here to say this.

------
justplay
awesome. I have been waiting for this.

