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

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

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

Step 5. Run the SqlLocationDB.msi setup routine.

Step 6. Click Next button.

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

Step 8. Click Install button.

Step 9. Click Finish button.

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.

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

Step 4. Click Next button on Feature Selection page.

Step 5. Click Install button.

Step 6. Click Finish button.

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.

Step 9. Accept license agreement and click Next button.

Step 10. Click Install button.

Step 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: 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.