Working with SQL Server in Django

TLDR; I wouldn't.

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. 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:
DATABASES = {  
    '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:
[FreeTDS]
Description=FreeTDS Driver  
Driver=/usr/local/lib/libtdsodbc.so  
Setup=/usr/local/lib/libtdsodbc.so  
FileUsage=1  
  • 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. In fact, I'd probably extend that recommendation to include any database that Django doesn't support out of the box (except maybe mongo...to the extent that mongo could ever really be recommended). I guess that should be implicit in Django not supporting those DBs out of the box. 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.