SQLDSL: A Ruby way to build SQL queries

By Peter Cooper / March 13, 2007

SQLDSL, by Jay Fields, is, simply, "a library for creating SQL statements using Ruby code." Jay posted separately about the pros and cons of SQLDSL on his weblog, although he might be accused of bias with only one con to six pros (compelling as they may be).

Despite being another DSL to learn, SQLDSL follows SQL pretty closely (in contrast to ORM frameworks like ActiveRecord, Sequel, or Og):

Select[:column1].from[:table1].where do
  name = person_name
end

And it even affords some clever logical tricks:

Select[:column1].from[:table1, :table2].where do
  table1.column1 = table2.table1_id
  table1.column2 >= quantity if quantity > 0
end

One major future benefit of SQLDSL is that it provides a programatic abstraction of SQL that's still pretty close to the database layer, and could allow for easy vendor-specific SQL manipulation in future. As Jay says:

The point is, as long as your sql is nothing more than a string it is not able to be easily modified. However, an object representation of your sql could be evaluated in various contexts to produce different results."

Comments

Robert says:
March 13, 2007 at 2:11 pm
That looks to be pretty cool.
court3nay says:
March 13, 2007 at 3:42 pm
This looks very similar to Ezra Zygmuntowicz's "Ez-Where". We've been using it for over a year :)

http://opensvn.csie.org/ezra/rails/plugins/dev/ez_where/
Henrik N says:
March 13, 2007 at 5:41 pm
Isn't that code missing a couple of dos?
Peter Cooper says:
March 13, 2007 at 7:36 pm
You're right. Fixed. :)
John O says:
March 15, 2007 at 8:10 am
Looks cool but, but using SQL contradicts with Rails - ActiveRecord philosophy does it not?
Sebastian says:
March 16, 2007 at 1:43 pm
Sure, John. But not all database programming is Rails after all.
Derek says:
March 16, 2007 at 3:33 pm
Not at all, John O. No experienced Rails developer would tell you to avoid SQL at all costs. Sometimes, you've just got to get your hands dirty. A library like this could alleviate some of the pain involved. This is rather cool stuff.
Brendan Baldwin says:
March 16, 2007 at 7:08 pm
You can't capture some kinds of things properly to generate the sql like ruby wont tell you the difference in method calls between a == b and a != b, since they both utilize ==

You eventually wind up with something more like this I think:

@user_list = select(:u => [:id, :name], :g => [:name_as_group_name]).
from(:users => :u, :memberships => :m, :groups => :g).
inner_join(:u, :m, :u => :id, :m => :member_id).
inner_join(:g, :m, :g => :id, :m => :group_id).
condition do |q|
q.u.status.not == 'inactive'
q.m.registration_date.less_than Date.yesterday
end.
order_by :group_name, :name
jc says:
March 16, 2007 at 10:57 pm
Ick. I'm not sure writing a DSL ontop a DSL is always the best idea. But then again, whatever floats your boat.