What's Wrong with SQL?
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?