Working with SQL Server in Django (+ schemas!)

Update as of 2019-10-16:

This is pretty outdated and all of the issues are not difficult to solve. I recommend reading my new posts here for the general fix, and here for performance. I'd probably just delete this whole article if it didn't account for like half the hits of this whole website.

There are a couple of quirks, but nothing crazy, as explained in the new article. I hope those tips help make your development experience an absolute pleasure. Feel free to blast me online if I missed something or if you violently agree/disagree.

TLDR; It's pretty brittle and pretty slow [editor's note: performance issues were fixed in this post]. 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 [editor's note: pyodbc treats everything as unicode by default, which is slow for db's with a different collation].

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:

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. 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 mongo...to 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.

Menu