Having DataMapper Build You A SQL Query

ORMs are great and they can handle a lot of operations that I don't particularly want to write from scratch.  But sometimes you need to get in and use the SQL or the power of whatever back-end you want to play.  DataMapper allows you to do this and you can find the documentation here under "talking directly to your datastore".  But lets say I want to take advantage of the way DataMapper builds my query and sanitizes it because I'm too lazy or their are way more injection attacks then I can keep up with but a community ORM adapter can.  Also,  sometimes I don't want DataMapper to construct a ruby object for me that consumes more resources and I can get by with just the structs when I'm getting back 100,000s of records.  So in summary I want DataMapper to build my sanitized query but I want to   talk directly to my backend.

Here is the solution that we came up with to extend DataMapper in our application and in this case we are assuming that we are using some sql version of an adapter.

module DataMapper
  def self.generate_sql(dm_query)
    statement, bind_vars = repository.adapter.send(:select_statement, dm_query.query)
    repository.adapter.send(:open_connection).create_command(statement).send(:escape_sql, bind_vars)
  end
end

With this in our application we can now make a call like:

  sql = DataMapper.generate_sql(User.all(:name.like=>'%Bob%') 

This will return an SQL statement for selecting all the users in the User Table that match Bob in them.  If we want to execute this SQL against our backend then all we need is:

  results = repository(:default).adapter.select(sql)

And now results holds the records as structs.  This is useful when dealing with a large number of records and you just want the results. Other fun things you may want to do include building transactions out of your DataMapper generated statements since I find some things to be lacking with the DataMapper transactions.