Replacing Django's ORM with SQLAlchemy

In the first example of Django's loose coupling we looked at replacing the templating system with Jinja2. In this article we're going to look at replacing the standard Django ORM with SQLAlchemy. This is a pretty big leap, and before we jump in too deeply lets take a look at how switching to SQLAlchemy is going to impact our application.

In the introduction to this series, I said that Django's implementation follows the loose coupling philosophy, but we may have to add several grains of salt to that statement as we consider the changes caused by switching from Django's ORM to SQLAlchemy. First, we're going to no longer be able to use the Django models.py files to describe our models. Instead, we'll be using SQLAlchemy's ORM for defining tables and fields (which will look pretty similar).

Following from that, we won't be able to use a simple python manage.py syncdb to create the tables for our project. We won't be able to use python manage.py loaddata or python manage.py dumpdata to create fixtures to use as initial data or scaffolding for the Django testing framework. If we're strict about only using SQLAlchemy, we won't even be able to use the contributed resources like the Django Admin, the sessions middleware, or the authentication middleware.

The take away message here is that while Django is loosely coupled, it is loosely coupled does not imply decoupling has no consequences. Changing some systems, like template rendering, have very few consequences, but others--like the Django ORM--are a far from plug and play. This is unavoidable, because perfect decoupling places additional onus upon the individual developer, and would make it slower (and more complex) to get Django projects up and running. When you first begin using Django it is difficult to gauge which subsystems will be costly to decouple, and that is why its highly recommendable to start out using the default Django stack, and then deviate responsibly as you gain more experience.

(Full disclosure: it is possible to use the Django ORM and SQLAlchemy together. This means you could still take advantage of the Django sessions middleware (and most other stuff), while using SQLAlchemy for some parts of your app. This tutorial won't build that Frankenstein, since it's only interested in exploring the loose coupling aspect, but it wouldn't be prohibitively difficult to do so. Although, it would be awkward in some regards.)

Normally we'd use include a urls.py file from the
with_sqlalchemy app into the project's urls.py file,
but we're trying to keep things simple.

Now we're going to use the SQLAlchemy ORM to create a simple
table to play around with. Because the file already exists, we're
going to throw the table definition into the models.py,
so go ahead and open up with_sqlalchemy/models.py.

We can see that SQLAlchemy can define models very similar to
those we'd define for Django. We won't delve into it here, but SQLAlchemy can also get much
more low level by defining database tables and then
mapping them onto a Python class.
You could even skip out on using ORM completely and take advantage
of their interface for constructing SQL.

And now we'll write our views.py file. First, we have some imports
to begin our file with.

The sqlite:///loose.sqlite instruction creates an SQLite database
at the relative path loose.sqlite, i.e. within the with_sqlalchemy
directory. SQLAlchemy makes it equally easy to specifiy an absolute path,
or to create the database in memory. If we wanted to make this more Django-like,
we could have specified the DATABASE_NAME setting in settings.py, and
then loaded the value like this:

Also, the Base.metadata.create_all(engine) only really needs to be
done once when SQLAlchemy first creates the table (although doing it multiple
times doesn't hurt anything), but that would involve creating a counter-part
to the python manage.py syncdb functionality in a script, and would
make the example more complex, so we're skipping that at the moment.

These simple functions are going to be used to populate our database with
some data. It also provides a minimal example of the SQLAlchemy querying
and object creation syntaxes, which are very similar to those in the Django ORM.

Which looks almost identical to what it would have looked like writing it
with the Django ORM (just a slight syntax difference for retrieving data).

And now we need to create the with_sqlalchemy/index.html
template that we are rendering. First we'll create a base
template, located in loose_coupling/templates, and named
base.html. It will look like this:

The most important thing to notice about these templates is that
we are passing them instances generated by the SQLAlchemy ORM,
but it looks identical to how it would look if the template was dealing
with the Django ORM: the template language is ignorant of the
details of the Django ORM, and can be used to anything from
normal Django Model instances to normal Python dicts.

Now everything is finished, just waiting for us to test.

python manage.py runserver

Then navigate over the http://127.0.0.1:8000/.
You'll see a simple display of the four programming languages and
their extension.

Now, this example is much less transparently useful than the previous one working
with Jinja2, but it still demonstrates two important points about Django. First,
Django is Python, so you can do anything in Django that you could do outside of
Django. Second, Django is loosely coupled, but sometimes it is
loosely coupled with consequences, and decoupling some subsystems--especially
the ORM--isn't painless.

As a final point, I'd like to apologize for the disservice that is done to SQLAlchemy
in this entry. SQLAlchemy is an extremely full-featured and versatile system, and
the usage here makes it look like a mirror image of the Django ORM. It isn't. Not by
a long shot. SQLAlchemy can abstract things similarly to the Django ORM, but makes it
much easier to get dirty, and also exposes more advanced functionality like connection
pools.

Okay, and a final final point: I think this tutorial speaks for itself, but I wouldn't
recommend decoupling from the Django ORM without an extremely compelling reason. It is
the most coupled of all the subparts of Django, and certainly not trivial to replace.

For those who feel disappointed that I've been unfair to Django by not looking at
using both the Django ORM and the SQLAlchemy ORM together, have heart, the next
example in this series will take a look at something along those lines (but featuring
a slightly more realistic situation).

I felt like I was obligated to make at least one lame alchemy related joke and/or pun. Consider the itch scratched.↩

Hi folks. I'm Will, known as @lethain on Twitter.
I write about software and management topics,
and love email at lethain[at]gmail.
Get email from me by subscribing to
my weekly newsletter.