django sql server localdb

Using Django with SQL Server LocalDB

How to use Django with SQL Server LocalDB

LocalDB is a version of Microsoft SQL Server Express Edition designed for developers. LocalDB is:

  • easy to install
  • requires no management
  • is compatible with other editions of SQL Server

LocalDB is also lightweight. The setup routine is just 45MB. LocalDB instances will start automatically upon the first connection request and stop automatically shortly after the last connection is closed.

Databases hosted on LocalDB are limited to 10 GB in size.

Follow these steps to install LocalDB, create a database, and connect Django.

Note: This post has been tested with Microsoft SQL Server 2017, Django 1.11.x and 2.x. The pyodbc and django-pyodbc-azure packages will be used to connect Django to SQL Server. The version of django-pyodbc-azure must match your version of Django.

Installing LocalDB

  1. Download SQL Server 2017 Express from https://www.microsoft.com/en-us/sql-server/sql-server-downloads.
  2. Run the setup routine and click Download Media.
  3. Choose LocalDB, specify a download location, and click Download.
  4. Click Close button to close the setup routine.
  5. Run the SqlLocalDB.msi setup routine.
  6. Click Next button.
  7. Accept the license agreement and click Next button.
  8. Click Install button.
  9. Click Finish button.
  10. Download most recent Cumulative Update for SQL Server 2017 (CU11 as of this post) from https://www.microsoft.com/en-us/download/details.aspx?id=56128 and run the setup routine.
  11. Accept the license agreement and click Next button.
  12. Click Next button on Features page.
  13. Click Next button on Check Files In Use page.
  14. Click Update button on Ready to update page.
  15. Click close button on Complete page.

Note: There is a bug in versions prior to CU6 that prevent databases from being created in the default location with LocalDB.

  1. Download Microsoft ODBC Driver 13.1 for SQL Server from https://www.microsoft.com/en-us/download/details.aspx?id=53339 and run the setup routine.

    Note: This is required for the Command Line Utilities for SQL Server
  2. Click Next button on Welcome page.
  3. Accept the license agreement and click Next button.
  4. Click Next button on Feature Selection page.
  5. Click Install button.
  6. Click Finish button.
  7. Download Microsoft Command Line Utilities 14.0 for SQL Server from https://www.microsoft.com/en-us/download/details.aspx?id=53591 and run the setup routine.
  8. Click Next button on Welcome page.
  9. Accept license agreement and click Next button.
  10. Click Install button.
  11. Click Finish button.

Creating database

LocalDB supports multiple instances. We recommend created a named instance for each Django project.

  1. Open command prompt as Administrator
  2. Create a named instance for your project (replace “Project” with the name of your project)
    sqllocaldb create ProjectLocalDB
    
  3. Start the new instance
    sqllocaldb start ProjectLocalDB
    
  4. Verify the instance is running
    sqllocaldb info ProjectLocalDB
    
  5. Connect to the named instance
    sqlcmd -S (LocalDB)\ProjectLocalDB
    
  6. Create login for new instance
    create login my_user with password='my_password'
    go
    
  7. Assign login to the admin role
    sp_addsrvrolemember my_user, sysadmin
    go
    
  8. Create user
    create user my_user
    go
    
  9. Create database
    create database my_db
    go
    exit
    

Configure Django to connect to LocalDB database

  1. Install Django dependencies for connecting to SQL Server
    pip install pyodbc
    pip install django-pyodbc-azure
    

    Note: The version of django-pyodbc-azure must match your version of Django.

  2. Update DATABASES settings in your Django application
    DATABASES = {
        'default': {
            'ENGINE': 'sql_server.pyodbc',
            'HOST': '(LocalDB)\ProjectLocalDB',
            'PORT': '',
            'NAME': 'my_db',
            'USER': 'my_user',
            'PASSWORD': 'my_password',
            'OPTIONS': {
                'driver': 'ODBC Driver 13 for SQL Server',
            },
        },
    }
    

Note: Django will connect to a SQL Server database with ODBC Driver 11, 13, and 17, but version 13 is required for the sqlcmd utility included in Command Line Utilities 14.