So my little rant on AJAX support in Django apparently touched a nerve with a couple people; that means it’s time to write more about it.
Yes, SQL is just as important. But having ORM isn’t “hiding SQL”.
What ORM is
The intialism “ORM” stands for “object-relational mapping”, which should give away what it’s all about; an ORM system is not about keeping you from having to write SQL, it’s about providing a way to translate between objects in code and records in a relational database. In other words, ORM (in the simplest case) is a way of saying “this class maps to this table, and objects of that class map to rows in that table”. Instance attributes of the class can then be selectively mapped to database columns of varying types.
And the ORM systems in popular frameworks do pretty much that exact thing, falling mainly into two types of workflow:
- Create a database, then introspect it to get the basic class definitions, which can be fleshed out with a little extra code (this is the common pattern in Rails, for example).
- Define a class, then introspect it to get the basic SQL statements for database creation, which can be fleshed out with a little extra code (this is the common pattern in Django).
In the first case you need to write and execute the
CREATE TABLE statements yourself, so it’s hard to see how this “hides SQL”. In the second case you don’t have to (though you will, from time to time, want to edit them before they’re executed to do a little extra fine tuning), but you still need to be thinking in SQL terms as you create the model classes. Django’s model system enforces that somewhat further than necessary by using names like
ForeignKeyField which serve as reminders of exactly how your models will map onto database tables and relations.
ORM also isn’t everything
Once your database is created, you can, if you really want to, get by in Rails or Django without ever writing a line of SQL, but you’ll probably find yourself severely hampered because there are some queries you’ll want that just can’t be expressed cleanly in their ORM systems. For example, consider a discussion forum application written with Django, with the following models:
class Forum(models.Model): topic = models.CharField(maxlength=200) moderators = models.ManyToManyField(User) class Thread(models.Model): title = models.CharField(maxlength=250) started_by = models.ForeignKey(User) start_date = models.DateTimeField() forum = models.ForeignKey(Forum) class Post(models.Model): author = models.ForeignKey(User) pub_date = models.DateTimeField() body = models.TextField() thread = models.ForeignKey(Thread)
Now consider a common feature for such an application: listing the five most active threads in a forum, say over the past twenty-four hours. How would you get that using only Django’s ORM? I’m pretty certain that you can’t, and even if you could it’d probably be hellishly complex.
In straight SQL, on the other hand, getting a list of five “most active” thread ids in order of “activity” is a single query:
SELECT t.id AS thread_id, COUNT(*) AS score FROM forum_thread AS t INNER JOIN forum_post AS p ON t.id = p.thread_id INNER JOIN forum_forum AS f ON t.forum_id = f.id WHERE p.pub_date > [a datetime value twenty-four hours in the past] AND f.id = [the forum id] GROUP BY thread_id ORDER BY score DESC LIMIT 5;
Once you’ve got it you’ll probably want to feed that list of thread ids into Django’s ORM (using
in_bulk) to get the
Thread objects, but getting that list of ids was something you couldn’t do without writing SQL. Also, that reinforces once again that the primary purpose of ORM is not to “hide the database”, but rather to translate between database records and code objects — once you have the ids, you just ask the ORM to translate them into the appropriate instances of your model.
Laid to rest
Hopefully that will put to rest the notion that having an ORM system is meant to “hide SQL from the developer”. No matter what you’ll end up thinking in SQL terms, and while you can try to never write any SQL it probably won’t get you very far; any ORM sufficiently complex to really replace SQL would, pretty much by necessity, have all the warts and complexities that drive people away from SQL in the first place.