Integrate MS SQL Server with Django

Installation

Start by installing the package through your preferred python package manager:

pip install mssql-django

We chose to add the support for MS SQL using Django's Multi-DBfunctionality, so that we can choose which of the databases we want to connect to. If you want the same experience, update DATABASES in settings.py as follows:

DATABASES = {
    # Access with `Model.objects.using("postgresql")`
    "postgresql": {
         ...
    },
    # Access with `Model.objects.using("mssql")`
    "mssql": {
        "ENGINE": "mssql",
        "HOST": os.getenv("DB_HOST", "localhost"),
        "PORT": os.getenv("DB_PORT", 1433),
        "NAME": os.getenv("DB_NAME", "master"),
        "USER": os.getenv("DB_USER", "sa"),
        "PASSWORD": os.getenv("DB_PASSWORD"),
        "OPTIONS": {
            "driver": "ODBC Driver 17 for SQL Server",  # Default
            "extra_params": (
                # Use this if connecting without SSL certs
                "TrustServerCertificate=yes;"
            ),
        },
    },
}


# If you prefer a drop-in replacement
DATABASES = {
    "default": DATABASES["mssql"]
}

Use these env variables to check the connection:

• DB_HOST: The address of the database server
• DB_PORT: The gateway to the database server (default for MS SQL is 1433)
• DB_NAME: The name of the database (default for MS SQL is masts)
• DB_USER: The user who connects (the default for MS SQL is said)
• DB_PASSWORD: The password of the user connecting

Additional configurable options for the adapter are described in the official documentation: https://github.com/microsoft/mssql-django#options

Testing

To test the connection before deployment one can spin up a local database server. Microsoft offers a Docker image for easier installation. For an even simpler solution, we've created a Docker compose file that can be downloaded here: https://github.com/Ur-Solutions/MS-SQL-Server

services:
  mssql:
    # Documentation: https://mcr.microsoft.com/en-us/product/mssql/server/about
    image: mcr.microsoft.com/mssql/server:2019-latest
    restart: unless-stopped
    environment:
      ACCEPT_EULA: Y
      # Default username = "sa"
      MSSQL_SA_PASSWORD: "passWORD1" # At least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.
      MSSQL_PID: Developer
    ports:
      - 1433:1433
    volumes:
      - ./data:/var/opt/mssql

Replace the Docker image tag (2019-latest) to select the preferred version of the database.

Django

In addition to the database server, you will need to install the following packages locally on your machine to connect through Django:

# Install database driver for use with Django
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql17 mssql-tools

# mssql-django depends on pyodbc, which needs to be installed separately
brew install unixodbc
python -m pip install pyodbc

Note that the same packages also need to be installed in the container if you are building a Docker image of your Django application!

For more information, see this installation wizard.

Now you can test the connection by e.g. running:

python manage.py showmigrations --database=mssql

Pitfalls

While it's playfully easy to get started with MS SQL in Django, it's important to point out that this is a separate database technology with both pros and cons to be aware of. The first thing to check before making such a change is whether you are using any PostgreSQL specific field types, e.g. ArrayField, and investigate if there is an equivalent type in MS SQL.

The database adapter also has some limitations, among other things related to date fields with time zone. We recommend reading through the list to see if there are any dealbreakers for your particular application. In our case, we had to make manual changes to some migration files in order for these to be executed on the MS SQL server without any problems.

Finally a caveat if you're writing raw SQL queries; the MS SQL syntax is dissimilar e.g. Its PostgreSQL. We assume most people who use Django largely relate to ORM, but if you have sites that don't, you'll have to rewrite the queries.