Working with SQL Server in Django (+ schemas!)

TLDR; It's pretty brittle and pretty slow. However, I think the issues were mostly because I was connecting to an existing database, and that database was fairly complex (and used schemas). Not sure if the slowness came from Azure Data Warehouse, or FreeTDS or what.

Caveat - this doesn't work on Django 2.0 as of 2017-01-08. And I haven't actually looked at the project using this in a while, so some details might be a little fuzzy.

Some background: the database I was accessing was a large existing MSSQL database with a few different schemas. It might have been a little too complicated compared to what Django is used to dealing with, making a full integration difficult. It was also hosted on Azure, which could have added some speed issues. Nevertheless, this ends up working enough for selects and writes.

  • I'm running OS X
  • Install freetds: brew install freetds --with-unixodbc
  • Install django-pyodbc-azure
  • Get your settings working. Mine look like:
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': 'database.db'
    'sql_db': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'MyDatabase',
        'HOST': 'DBServer',
        'PORT': '1433',
        'USER': config.get('django', 'SQL_USERNAME'),
        'PASSWORD': config.get('django', 'SQL_PASSWORD'),
        'OPTIONS': {
            'driver': 'FreeTDS',
            'host_is_server': True,
            'extra_params': 'TDS_VERSION=7.3',
  • This github issue really helped me set everything up on my machine.
    • brew install unixodbc
    • brew install freetds --with-unixodbc
    • edit /usr/local/etc/odbcinst.ini:
Description=FreeTDS Driver  
  • Here is the schema workaround for now. It's pretty gross unless you find SQL injection beautiful.
    • This blog post is where I found the idea, and it has a pretty interesting explanation.
    • Django has been thinking about adding support for schemas for like 10 years - issue
    • My own question on django-pyodbc-azure - here
class MyModel(models.Model):

    class Meta:
        is_managed = False
        db_table = 'MySchema].[MyModel'  # Note the missing brackets at the beginning and end

In order to access a table through a schema, we'd usually want to write something like [MySchema].[MyModel]. But Django doesn't have any support for this. Instead, it simply wraps the table name in brackets like [MyModel]. So we'll just hack it and inject what we need - MySchema].[MyModel.

So that'll all get you up and running. However, I found that some of my queries were painfully slow. For instance, a simple SELECT query was taking 6.5 seconds. Digging into it, making the query non-parameterized reduced it to a (more) reasonable speed. I think I also ended up using SQLAlchemy. However, that made testing really difficult, and I wouldn't do it for any real project.

All in all, I don't recommend using Django for directly accessing a large, existing SQL database with schemas. Especially not like an Azure Data Lake. In fact, I'd probably extend that recommendation to include any database that Django doesn't support out of the box (except maybe the extent that mongo could ever really be recommended). And the libraries I've looked at don't seem to be kept that up to date because I don't think there's a whole lot of community need for them. Instead, write another service around the DB in Java or something and use an API. It's a good excuse to start using a microservice architecture, regardless of whether that's something you want to do.