For some background, I've been working with Django for a few years now and for the past year have been a developer on a rather large django application at work. I did a few projects with SQLAlchemy about 2 years ago when I was put on a project whose requirements seemed to imply the need for a database but had no web component. I know of and have used other python ORMs (notably SQLObject), but this post is about SQLA and Django's ORMs, and how I consider the differences between them to be important but also neutral.

I started a small project recently that was going to require some light querying, and have had aborted re-starts on the business logic layer using first Elixir, then SQLObject, and now going straight ext.declarative with SQLAlchemy. The project has relatively few tables, with only a couple one-to-many and a single many-to-many relationship; as such it probably represents a fairly standard use case of an ORM. I've learned a lot about these different projects in the process, but was finally able to formalize some thoughts I've had with respect to SQLA and Django's ORMs.

The major takeaway from all of it is that SQLAlchemy's ORM and Django's ORM solve different problems. They aren't completely different problems, but they are different enough that you should have a completely different approach using each.

SQLAlchemy is a toolkit for python programmers who want or need to use a database to write programs that do so. Django's ORM is a tool for python programmers to quickly model business-level data relationships and use a database for persistent storage. This is really an important distinction; the Django ORM interface is focused on the application's data model, whereas SQLAlchemy's interface is focused on the underlying data store. In Django, your application is first class; in Alchemy, it isn't. As an example, look at a simple usage of a many-to-many relationship in each:

  • Django: Book.objects.filter(author__age=27)
  • SQLAlchemy: session.query(Book).join(Book, Author).filter(Author.age==27)

Note that Django's API is entirely concerned with the relationship between the application-level abstractions 'Book' and 'Author', whereas the Alchemy version includes far more details about what is actually going to happen when executed. This example exposes the different assumptions the ORMs have to live with, as well: implicit within Django's design are decisions on the way connections are made to the database, how to evaluate queries (lazily or eagerly), whereas in SQLAlchemy these details are in plain sight.

There's a large degree of negative/positive choice tradeoff going on between the two ORMs that took a while to dawn on me. As an application developer first and a database administrator and tuner maybe 5th at best, I tend to lean towards Django's ORM as the type of thing I'd rather use. I see that as a result of familiarity, my own weaknesses, and also the strengths of the Django ORM to do a good enough job most of the time.

Of course, there are things you can't do easily with Django's ORM, like fetch all books with 2 authors, or fetch all authors whose first and last names are the same, that are relatively simple to do in SQLAlchemy. The conceptual leap, of course, is understanding how to answer these questions from the standpoint of your database, not your data model. There's a case to be made that, if you have not already made this leap, your ability to solve problems will be constrained by the tools the Django ORM gives you, and I can see some truth in the blame critics place on Django's ORM for keeping some folks unenlightened. I still think that in the end, the python programmer is better off having both of these projects to learn from, and the python world better for having both to choose from.

update: This post got a bit of action at reddit. The comments there rightly pointed out that the examples I pointed to above that were 'not easily done' via Django are fairly easy to do with the new aggregation features in Django 1.1. It was a mistake not to acknowledge the improvements in the Django ORM post-1.0. My main point wasn't to highlight a laundry list of things that are hard with Django or easy with SQLAlchemy; I'm not really qualified to do this, but I felt that a couple examples of this would perhaps illustrate the differences I saw better than mere exposition.

They also pointed to the fact that 'natural orm' features are available via SQLAlchemy. The code examples I included above are already using relations, and at least according to the latest documentation they are the canonical way to deal with these types of relationships in SQLA 0.5.x. However, the fact that there are multiple ways of doing this speaks to the main point of this post; that the focus of SQLAlchemy is to allow you to do whatever it is you want without touching raw SQL, and the focus of Django's ORM is to provide your application with an easy-to-use data model.

The fact that Django's ORM is heavily influenced by the relationships SQL is good at is to me an unavoidable impurity; you could provide a Django-orm style data model for a non RDBMS, but SQLAlchemy outside of that context is completely meaningless. Which is fine; the difference is neutral, and SQLAlchemy is meant to be the best there is within that context. In fact, I think it clearly is; the whole point is that Django's ORM is only incidentally playing in the same sandbox, and is focusing on solving a slightly different problem.

Jun 21 2009