JavaScript, ORM and “hiding SQL”

July 4, 2006    Django, Frameworks, JavaScript

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.

One of the common points people have been raising, in comments and elsewhere, is that I shouldn’t rail against “hiding JavaScript from the developer” when Django’s ORM already hides SQL from the developer; from the perspective of a server-side developer, SQL is just as important, right?

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.

Meanwhile, the whole point of the sorts of JavaScript “helpers” people keep clamoring for from Django does seem to be to hide JavaScript from the developer; hence the repeated lament of “why should I learn JavaScript just to do this effect?”

Going beyond the laziness involved in asking such a question — you’re a web developer and it’s your job to know this stuff, so learn it already — I also don’t think that automated “helpers” could ever do much beyond the most basic operations. And those basic operations are obscenely easy with the various modern JavaScript toolkits, so actually writing some JavaScript to get them still seems like the simplest and most straightforward way to go, and gives you a good foundation for developing more complex things.