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?

Audette

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.

Tokay

I should also note that I think ORM designers should continuously look at how to push that line further, so that more complex queries can be handled by finders more simply than with SQL. I'm not convinced yet we'll ever completely eliminate SQL though. – Tokay

Which ORM is best to use with a new Rails 3 app against a legacy MySQL 5.1 database? I do not expect to ever run a migration against this database (though the schema may change from time to time), but I will certainly be writing to it. – Eichhorn

Mark, unless your schema matches the ActiveRecord conventions your choices are DataMapper and Sequel. Obviously I'm biased towards Datamapper, but Sequel is a fine ORM too. I will say though that one of the primary goals with DataMapper was to allow it to map to legacy schemas. Sequel is better if you want to go down to the metal and write SQL-like statements when constructing more complex queries. – Tokay

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.

Quits

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.

Forbore

Answer 4

ORM stands for Object Relational Mapping - but looking at the query your friend seems to be wanting a pretty specific table of sums and other items... I've not used Ruby's Sequel, but I've used Hibernate, and Python's SQLAlchemy (for Django/Turbogears) and while you can do these sorts of queries, I don't believe that is their strength.

The power of ORM comes from being able to finding Foo->Bar object relationships, say you want all the Bar objects for Foo's field greater then X... That sort of thing. Therefore I would not classify an ORM as a "good" solution, though moving to a real programming language like Ruby and doing the SQL through it instead of Excel... that in itself is a win.

Just my 2 cents.

Epicalyx

Answer 5

In a situation like that, I'd probably write them by hand or use a View (if the DB you're using supports views)

Ervin

Answer 6

ORM's are used when you have Objects (Business Objects). I am therefore assuming that you have an application with which you creating and Managing the Business Objects that are ultimately saved into the database. If you have then you have almost definitely got some representation of the relationships and probably many of the calculations you are going to use in reports. The problem with using SQL to directly access your database for reports is simply maintainability. You typically put a lot of effort into ensuring that your Business Objects hide any details of their database. You implement business rules and do common calculations in your Business Objects. Build a common language for all members of the team etc etc. You then use an ORM to map to the database and use Habanero or NHibernate or something like that to do this. This is all great. We do this all in the name of Maintainability and is great. You can migrate your application change your design etc etc.

You now go and write SQL to run reports over time you have hundreds of report. Firstly they often duplicate logic you already have in your BusinessObjects (Usually without any tests) and even worse Bham Damb sorry maintainability is now stuffed forget about moving a that field from one table to another forget about splitting that table into two changing that relationship etc you have a number of reports that are going to break unexpectedly.

The problem with quering through your Domain Objects/Business Objects is simply one of performance.

In summary if you are using Domain Driven Design or Business Object concepts try to use these for reports. (You will probably run directly from DB using SQL or stored procs for performance reasons but try limit these use your Business Objects first and then use SQL). The other option of course is using a separate reporting database (Like some of the BI concepts) The mapping from your transactional DB to your reporting DB is therefore in one place and easily changeable in cases where you want to change your design.

Domain Objects (Business Objects) and ORMs have all the knowledge to allow you to start building high performing queries that run directly on the Database while using the Domain Terminology. Lets hope that these continue to evolve to a point where this is a reality.

Until then if you are using Business Objects in your application try use them for Reporting when performance is an issue resort to SQL.

Coprology