django SQL server

Using Django with SQL Server LocalDB

Updated for 2021.

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, and is compatible with other editions of SQL Server.

LocalDB is lightweight – the setup routine is just 53MB. 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: These steps have been tested with Microsoft SQL Server 2019, Django 2.2 and 3.0. The pyodbc and django-mssql-backend packages will be used to connect Django to SQL Server.

Installing LocalDB

Step 1. Download SQL Server 2019 Express from https://go.microsoft.com/fwlink/?LinkID=866658

Step 2. Run the setup routine and click Download Media

SQL Server Installing Local DB

Step 3. Choose LocalDB, specify a download location, and click Download.

SQL Servier Installer

Step 4. Click Close button to close the setup routine.

SQL Server Download Successful

Step 5. Run the SqlLocationDB.msi setup routine.

Step 6. Click Next button.

Installation Wizard for SQL Servier LocalDB

Step 7. Accept the license agreement and click Next button.

SQL Server LocalDB License Agreement

Step 8. Click Install button.

SQL LocalDB installation

Step 9. Click Finish button.

SQL Server Local DB Finish Installation

Installing related database utilities and drivers

Step 1. Download Microsoft ODBC Driver 17 for SQL Server from https://go.microsoft.com/fwlink/?linkid=2137027 and run the setup routine.
Note: This is required for the Command Line Utitlities for SQL Server.

Step 2. Click Next button on Welcome page.

ODBC Driver Setup on SQL Server for Django

Step 3. Accept the license agreement and click Next button.

ODBC Driver for SQL Server License Agreement

Step 4. Click Next button on Feature Selection page.

Selecting ODBC Driver Program Features

Step 5. Click Install button.

Installiong ODBC Driver

Step 6. Click Finish button.

Complete SQL Server Installation

Step 7. Download Microsoft Command Line Utilities 15.0 for SQL Server from https://go.microsoft.com/fwlink/?linkid=2142258 and run the setup routine.
Note: This is required to create the database and database user account from the command line.

Step 8. Click Next button on Welcome page.

Download Microsoft Command Line Utilities to Support Django Database

Step 9. Accept license agreement and click Next button.

Accept Microsoft Command Line Ultilities License Agreement

Step 10. Click Install button.

Install Microsoft Command Line Utilities

Step 11. Click Finish button.

Finish install of Microsoft Command Line Utilities to create Django Database

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: django-mssql-backend supports Django version 2.2 and 3.0. If you need to work with Django 2.1 or earlier, consider using django-pyodbc-azure.
  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 17 for SQL Server',
		        },
		    },
		}

If you’re looking for business IT support in Michigan or in the cloud, reach out to us at support@eclipse-online.com or by calling 586.263.1775.

Similar Posts