When to use an ORM (Sequel, Datamapper, AR, etc.) vs. pure SQL for querying

Question

A colleague of mine is currently designing SQL queries like the one below to produce reports, which are displayed in excel files through an external data query. At present, only reporting processes on the DB are required (no CRUD operations).

I am trying to convince him that it would be better to use a ruby ORM in order to be able to display the data in a rails/sinatra app.

Despite the obvious advantages in displaying the data, what advantages are there for him in learning to use an ORM like Sequel or Datamapper?

The SQL queries he is writing are clearly quite complex, and being relatively new to SQL, he often complains that it is very time-consuming and confusing. Is it possible to write extremely complex queries with an ORM? and if so, which is the most suitable(I have heard Sequel is good for legacy dbs)? and what are the advantages of learning ruby and using an ORM versus sticking with plain SQL, in making complex database queries?

Answer 1

I'm the DataMapper maintainer, and I think for complex reporting you should use SQL.

While I do think someday we'll have a DSL that provides the power and conciseness of SQL, everything I've seen so far requires you to write more Ruby code than SQL for complex queries. I would much rather maintain a 5 line SQL query than 10-15 lines of Ruby code to describe the same complex operation.

Please note I say complex.. if you have something simple, use the ORM's build-in finders. However, I do believe there is a line you can cross where SQL becomes simpler. Now, most apps aren't just reporting. You may have alot of CRUD type operations, for which an ORM is perfectly suited and far better than doing those things by hand.

One thing that an ORM will usually provide is some sort of organization to your application logic. You can group code based around each model in the same file. It's usually there that I'll put the complex SQL query, rather than embedding it in the controller, eg:

class User
  include DataMapper::Resource

  property :id,   Serial
  property :name, String,  :length => 1..100, :required => true
  property :age,  Integer, :min => 1, :max => 130

  def self.some_complex_query
    repository.adapter.select <<-SQL
      SELECT ...
        FROM ...
       WHERE ...
       ... more complex stuff here ...
    SQL
  end
end

Then I can just generate the report using User.some_complex_query. You could also push the SQL query into a view if you wanted to further cleanup this code.

EDIT: By "view" in the above sentence I meant RDBMS view, rather than view in the MVC context. Just wanted to clear up any potential confusion.

Answer 2

If you are writing your queries by hand you have the chance to optimize them. When I look at that query I see some potential for optimizations (E.ICGROUPNAME LIKE '%san-fransisco%' or E.ICGROUPNAME LIKE '%bordeaux%' wont use an index = Table Scan).

When using an OR Mapper (the native Objects/Tables) for reporting you have no or little control over the resulting SQL Query.

But: You could put that query in an View or Stored Procedure and map that View/Proc with an OR Mapper. You can optimize your queries and you can use all features of your Application Framework.

Answer 3

Unless you're dealing with objects, an ORM is not necessary. It sounds like your friend simply needs to generate reports, in which case pure SQL is just fine so long as he knows what he's doing (e.g. avoiding SQL injection issues).

ORM stands for "Object-Relational Mapping". If you don't have the "O" (objects), then it's probably not a good fit for your app. Where ORMs really shine is in persisting objects to the database and loading them from a database.