Want to stay on top? Ruby Weekly is a once-weekly e-mail newsletter covering the latest Ruby and Rails news.
     Feed Icon

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

  1. Robert says:

    That looks to be pretty cool.

  2. court3nay says:

    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/

  3. Henrik N says:

    Isn't that code missing a couple of dos?

  4. Peter Cooper says:

    You're right. Fixed. :)

  5. John O says:

    Looks cool but, but using SQL contradicts with Rails - ActiveRecord philosophy does it not?

  6. Sebastian says:

    Sure, John. But not all database programming is Rails after all.

  7. Derek says:

    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.

  8. Brendan Baldwin says:

    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

  9. jc says:

    Ick. I'm not sure writing a DSL ontop a DSL is always the best idea. But then again, whatever floats your boat.

Other Posts to Enjoy

Twitter Mentions