ORM is nice. As programmers in our languages of choice, SQL can be ugly and annoying. But I think it’s a big mistake to think if a little ORM is good, then a lot must be great. ActiveRecord is arguably the most criticized component of Rails. I think this is fundamentally because of the ugly nature of ORM. Rows aren’t objects and never will be. I don’t think an ORM system that fully supports SQL via an API would be pleasant to work with. I like ActiveRecord’s approach: make the easy things trivial, and just reveal SQL for the hard stuff. I’m deliberately not commenting on criticisms about ActiveRecord’s support for database-level constraints and triggers.

I’m not sure why someone would think every SQL statement should be covered by an ORM-layer. On the other side you have database purists who claim ORM is totally evil and that no true relational db exists. The arguments are solid, but it strikes me as academic quibbling. SQL is still far more expressive of complex relationships than any long-winded API ever could be (and I’m just using MySQL!). Take the following query I wrote yesterday:

SELECT t.txnid, tp.qty, COUNT(ncl.nid) as used_quantity, tp.expires 
  FROM
    ec_transaction AS t INNER JOIN
    ec_transaction_product AS tp ON t.txnid=tp.txnid LEFT JOIN
    node_content_listing AS ncl ON ncl.field_transaction_id_value=t.txnid LEFT JOIN
    node as n ON ncl.nid=n.nid AND n.type = 'content_listing' AND n.uid = ?
  WHERE
    t.payment_status = 2 AND
    t.uid = ? AND
    tp.nid = 5
  GROUP BY t.txnid
  HAVING tp.qty > used_quantity
  ORDER BY t.created

I spent about half an hour concocting this query for a Drupal module. The purpose of this module is to allow people to purchase the right to list their business on a website. The problem to be solved is that people can purchase the listings in advance, and then create the nodes later. I needed to associate each created listing with the transaction which authorized it even though they don’t happen at the same time. So this query maps each of the user’s existing listings to its transaction and returns only the transactions which are not ‘filled’ (because a transaction could be for more than one listing).

By doing this in one query I’m using the database for what its optimized to do. The typical ORM solution would probably involve two queries, at least if it were to be readable. What I can’t imagine (and I welcome counterexamples), is how ORM could make this more expressive. While developing this I had to shuffle quite a few bits around and keep testing the query directly in the database to get where I wanted to go. I changed join types, I moved clauses from WHERE to ON, I tested it without the HAVING clause. The end result is semantically rich, computationally efficient, and readable by anyone who knows SQL. What possible purpose could you have to dilute that?