Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
1278 lines (1141 sloc) 70.3 KB

Database Backends

When playing around with our demo app, the default sqlite3 is plenty. Before moving the app to production, you'll want to use a "real" database, or Microsoft SQL Server (as the case may be;-).

Running a local database server

We want to be able to do all our development in a local environment (mine is MacOS). Fortunately, this is feasible with all the common databases.

See training/settings.py for an example of alternative database settings for sqlite3, MySQL, and SQL Server, that are configured via environment variables.

# Database
# https://docs.djangoproject.com/en/2.1/ref/settings/#databases

if SQLSERVER:
    # Use the following if using with MS SQL:
    DATABASES = {
        'default': {
            'ENGINE': 'sql_server.pyodbc',
            'NAME': os.environ['DJANGO_SQLSERVER_DB'],
            'USER': os.environ['DJANGO_SQLSERVER_USER'],
            'PASSWORD': os.environ['DJANGO_SQLSERVER_PASS'],
            'HOST': os.environ['DJANGO_SQLSERVER_HOST'],
            'PORT': '1433',
            'OPTIONS': {
                'driver': 'ODBC Driver 17 for SQL Server',
            },
        },
    }

    # override the standard migrations because they break due to SQL Server deficiences.
    # MIGRATION_MODULES = {
    #     'oauth2_provider': 'myapp.migration_overrides.oauth2_provider',
    # }
elif MYSQL:
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.mysql',
            'OPTIONS': {
                'read_default_file': '/Users/alan/my.cnf',
            },
        }
    }
else:
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
            'OPTIONS': {
                'timeout': 20,
            }
        }
    }

sqlite3 server

sqlite3 is by definition a local database. It's "just there" on MacOS.

MS SQL Server

A free non-production SQL Server is available as a Linux-based Docker image:

sudo docker pull mcr.microsoft.com/mssql/server:2017-latest
sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' \
   -p 1433:1433 --name sql1 \
   -d mcr.microsoft.com/mssql/server:2017-latest
MS SQL Client

See the Microsoft instructions to install the SQL Server ODBC client code.

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install --no-sandbox msodbcsql17 mssql-tools

Use sqlcmd to create a new database and user that owns it:

(env) django-training$ sqlcmd -U SA -S 127.0.0.1,1433 -P '<YourStrong!Passw0rd>'
1> CREATE LOGIN foo WITH PASSWORD = "foobar123!";
2> go
1> CREATE DATABASE foo;
2> go
1> USE DATABASE foo;
2> go
Changed database context to 'foo'.
1> CREATE USER [foo] FROM LOGIN [foo];
2> go
1> exec sp_addrolemember 'db_owner','foo';
2> go

TODO: Change above to use ALTER ROLE

MySQL server

You can install a MacOS MySQL server and client using homebrew:

(env) django-training$ brew install mysql
(env) django-training$ mysql.server start
(env) django-training$ mysqladmin -u root create foo
(env) django-training$ mysql -uroot foo

Make a ~/my.cnf:

[client]
database = foo
user = root
# password = PASSWORD
default-character-set = utf8

database CLI tools

Following are some examples of how to use your database from the command line for sqlite3, sqlserver or mysql.

sqlite3 client

For the sqlite3 database, use sqlite3. For example:

(env) django-training$ sqlite3 db.sqlite3 
-- Loading resources from /Users/ac45/.sqliterc
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .tables
auth_group                    django_migrations           
auth_group_permissions        django_session              
auth_permission               myapp_course                
auth_user                     myapp_courseterm            
auth_user_groups              oauth2_provider_accesstoken 
auth_user_user_permissions    oauth2_provider_application 
django_admin_log              oauth2_provider_grant       
django_content_type           oauth2_provider_refreshtoken
sqlite> .schema --indent myapp_course
CREATE TABLE IF NOT EXISTS "myapp_course"(
  "id" char(32) NOT NULL PRIMARY KEY,
  "effective_start_date" date NULL,
  "effective_end_date" date NULL,
  "last_mod_date" date NOT NULL,
  "school_bulletin_prefix_code" varchar(10) NOT NULL,
  "suffix_two" varchar(2) NOT NULL,
  "subject_area_code" varchar(10) NOT NULL,
  "course_number" varchar(10) NOT NULL,
  "course_identifier" varchar(10) NOT NULL UNIQUE,
  "course_name" varchar(80) NOT NULL,
  "course_description" text NOT NULL,
  "last_mod_user_name" varchar(80) NULL
);
sqlite> select * from myapp_course limit 5;
id                                effective_start_date  effective_end_date  last_mod_user_name  last_mod_date  school_bulletin_prefix_code  suffix_two  subject_area_code  course_number  course_identifier  course_name     course_description
--------------------------------  --------------------  ------------------  ------------------  -------------  ---------------------------  ----------  -----------------  -------------  -----------------  --------------  ------------------
000f21d9c303426394cc0c8b4bfcd859                                            admin               2018-10-07     0                            00          DVSP               87847          BUSI5330K          LEADING PEOPLE  LEADING PEOPLE    
001f1ec8584a4cecba476f8e75b2cac3                                            admin               2018-10-07     B                            00          FINC               24650          FINC8368B          Security Analy  Security Analysis 
0025a7938ae54678bb8598328aecc83f                                            admin               2018-10-07     L                            00          LAW                70101          LAW 8941L          C INTERNATIONA  C INTERNATIONAL CR
002979f72f7042c8a9d56f06c569d8e5                                            admin               2018-10-07     GIU                          00          HNUT               71847          NUTR9011G          DOCTORAL RESEA  DOCTORAL RESEARCH 
002b66ce0506447090b609020d763afb                                            admin               2018-10-07     R                            00          FILM               21435          FILM5010W          CINEMA HIST I:  CINEMA HIST I: BEG
sqlite> 
sqlite> select * from myapp_courseterm where course_id='000f21d9c303426394cc0c8b4bfcd859';
id                                effective_start_date  effective_end_date  last_mod_user_name  last_mod_date  term_identifier  audit_permitted_code  exam_credit_flag  course_id                       
--------------------------------  --------------------  ------------------  ------------------  -------------  ---------------  --------------------  ----------------  --------------------------------
fbee2ba9b4d649a2b4e58d13317d65bc                                            admin               2018-10-07     20181            0                     0                 000f21d9c303426394cc0c8b4bfcd859
sqlite> 

MS SQL Server: sqlcmd

Use sqlcmd:

django-training$ sqlcmd -S 127.0.0.1,1433 -d foo -U foo -P 'foo!'
1> select top(4) * from myapp_course;
2> go
id                               effective_start_date effective_end_date last_mod_user_name                                                               last_mod_date    school_bulletin_prefix_code suffix_two subject_area_code course_number course_identifier course_name                                                                      course_description                                                                                                                                                                                                                                              
-------------------------------- -------------------- ------------------ -------------------------------------------------------------------------------- ---------------- --------------------------- ---------- ----------------- ------------- ----------------- -------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
000f21d9c303426394cc0c8b4bfcd859                 NULL               NULL admin                                                                                  2018-10-07 0                           00         DVSP              87847         BUSI5330K         LEADING PEOPLE                                                                   LEADING PEOPLE                                                                                                                                                                                                                                                  
001f1ec8584a4cecba476f8e75b2cac3                 NULL               NULL admin                                                                                  2018-10-07 B                           00         FINC              24650         FINC8368B         Security Analysis                                                                Security Analysis                                                                                                                                                                                                                                               
0025a7938ae54678bb8598328aecc83f                 NULL               NULL admin                                                                                  2018-10-07 L                           00         LAW               70101         LAW 8941L         C INTERNATIONAL CRIMINAL LAW                                                     C INTERNATIONAL CRIMINAL                                                                                                                                                                                                                                        
002979f72f7042c8a9d56f06c569d8e5                 NULL               NULL admin                                                                                  2018-10-07 GIU                         00         HNUT              71847         NUTR9011G         DOCTORAL RESEARCH IN NUTRITION                                                   DOCTORAL RESEARCH IN NUTR                                                                                                                                                                                                                                       

(4 rows affected)
1> select * from myapp_courseterm where course_id='000f21d9c303426394cc0c8b4bfcd859';
2> go
id                               effective_start_date effective_end_date last_mod_user_name                                                               last_mod_date    term_identifier                                                                                                                                                                                                                                                  audit_permitted_code exam_credit_flag course_id                       
-------------------------------- -------------------- ------------------ -------------------------------------------------------------------------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- ---------------- --------------------------------
fbee2ba9b4d649a2b4e58d13317d65bc                 NULL               NULL admin                                                                                  2018-10-07 20181                                                                                                                                                                                                                                                                               0                0 000f21d9c303426394cc0c8b4bfcd859

(1 rows affected)

MS SQL Server: mssql-cli

See mssql-cli for a more powerful CLI than sqlcmd.

(env) django-training$ pip install mssql-cli

Use mssql-cli:

(env) django-training$ mssql-cli -U foo -P 'foo!' -d foo
Version: 0.15.0
Mail: sqlcli@microsoft.com
Home: http://github.com/dbcli/mssql-cli
proposal> select top(4) * from myapp_course;
-[ RECORD 1 ]-------------------------
id                          | 001b55e09a60438698c74c856bb840b4
effective_start_date        | NULL
effective_end_date          | NULL
last_mod_user_name          | loader
last_mod_date               | 2018-08-03
school_bulletin_prefix_code | XCEFK9
suffix_two                  | 00
subject_area_code           | ANTB
course_number               | 04961
course_identifier           | ANTH3160V
course_name                 | THE BODY AND SOCIETY
course_description          | THE BODY AND SOCIETY
-[ RECORD 2 ]-------------------------
id                          | 00fb17bbe4a049a0a27e6939e3e04b62
effective_start_date        | NULL
effective_end_date          | NULL
last_mod_user_name          | loader
last_mod_date               | 2018-08-03
school_bulletin_prefix_code | B
suffix_two                  | 00
subject_area_code           | ACCT
course_number               | 73272
course_identifier           | ACCT8122B
course_name                 | Accounting for Consultants
course_description          | Accounting for Consultants
-[ RECORD 3 ]-------------------------
id                          | 016659e9e29f49b4b85dd25da0724dbb
effective_start_date        | NULL
effective_end_date          | NULL
last_mod_user_name          | loader
last_mod_date               | 2018-08-03
school_bulletin_prefix_code | B
suffix_two                  | 00
subject_area_code           | ACCT
course_number               | 73290
course_identifier           | ACCT7022B
course_name                 | Accounting for Value
course_description          | Accounting for Value
-[ RECORD 4 ]-------------------------
id                          | 01ca197fc00c4f24a743091b62f1d500
effective_start_date        | NULL
effective_end_date          | NULL
last_mod_user_name          | loader
last_mod_date               | 2018-08-03
school_bulletin_prefix_code | XCEFK9
suffix_two                  | 00
subject_area_code           | AMSB
course_number               | 00373
course_identifier           | AMST3704X
course_name                 | SENIOR RESEARCH ESSAY SEMINAR
course_description          | SENIOR RESEARCH ESSAY SEMINAR
(4 rows affected)
Time: 0.372s
proposal> ?
+-----------+----------------------------------+---------------------------------------------------+
| Command   | Shortcut                         | Description                                       |
|-----------+----------------------------------+---------------------------------------------------|
| \d        | \d OBJECT                        | List or describe database objects. Calls sp_help. |
| \dn       | \dn [name]                       | Delete a named query.                             |
| \e        | \e [file]                        | Edit the query with external editor.              |
| \ld       | \ld[+] [pattern]                 | List databases.                                   |
| \lf       | \lf[+] [pattern]                 | List functions.                                   |
| \li       | \li[+] [pattern]                 | List indexes.                                     |
| \ll       | \ll[+] [pattern]                 | List logins and associated roles.                 |
| \ls       | \ls[+] [pattern]                 | List schemas.                                     |
| \lt       | \lt[+] [pattern]                 | List tables.                                      |
| \lv       | \lv[+] [pattern]                 | List views.                                       |
| \n        | \n[+] [name] [param1 param2 ...] | List or execute named queries.                    |
| \sf       | \sf FUNCNAME                     | Show a function's definition.                     |
| \sn       | \sn name query                   | Save a named query.                               |
| help      | \?                               | Show this help.                                   |
+-----------+----------------------------------+---------------------------------------------------+
Time: 0.000s
proposal> \lt
+----------------+-------------------------------+
| table_schema   | table_name                    |
|----------------+-------------------------------|
| dbo            | oauth2_provider_accesstoken   |
| dbo            | oauth2_provider_application   |
| dbo            | oauth2_provider_grant         |
| dbo            | oauth2_provider_refreshtoken  |
| dbo            | foo                           |
| dbo            | django_migrations             |
| dbo            | django_content_type           |
| dbo            | auth_permission               |
| dbo            | auth_group                    |
| dbo            | auth_group_permissions        |
| dbo            | auth_user                     |
| dbo            | auth_user_groups              |
| dbo            | auth_user_user_permissions    |
| dbo            | django_admin_log              |
| dbo            | myapp_course                  |
| dbo            | myapp_courseterm              |
| dbo            | myapp_instructor              |
| dbo            | myapp_instructor_course_terms |
| dbo            | django_session                |
+----------------+-------------------------------+
(19 rows affected)
Time: 0.364s
proposal> \d myapp_course
+--------------+---------+------------+-------------------------+
| Name         | Owner   | Type       | Created_datetime        |
|--------------+---------+------------+-------------------------|
| myapp_course | dbo     | user table | 2018-11-28 17:57:03.773 |
+--------------+---------+------------+-------------------------+
 
-[ RECORD 1 ]-------------------------
Column_name          | id
Type                 | char
Computed             | no
Length               | 32
Prec                 |      
Scale                |      
Nullable             | no
TrimTrailingBlanks   | no
FixedLenNullInSource | no
Collation            | SQL_Latin1_General_CP1_CI_AS
-[ RECORD 2 ]-------------------------
Column_name          | effective_start_date
Type                 | date
Computed             | no
Length               | 3
Prec                 | 10   
Scale                | 0    
Nullable             | yes
TrimTrailingBlanks   | (n/a)
FixedLenNullInSource | (n/a)
Collation            | NULL
-[ RECORD 3 ]-------------------------
Column_name          | effective_end_date
Type                 | date
Computed             | no
Length               | 3
Prec                 | 10   
Scale                | 0    
Nullable             | yes
TrimTrailingBlanks   | (n/a)
FixedLenNullInSource | (n/a)
Collation            | NULL
-[ RECORD 4 ]-------------------------
Column_name          | last_mod_user_name
Type                 | nvarchar
Computed             | no
Length               | 160
Prec                 |      
Scale                |      
Nullable             | yes
TrimTrailingBlanks   | (n/a)
FixedLenNullInSource | (n/a)
Collation            | SQL_Latin1_General_CP1_CI_AS
-[ RECORD 5 ]-------------------------
Column_name          | last_mod_date
Type                 | date
Computed             | no
Length               | 3
Prec                 | 10   
Scale                | 0    
Nullable             | no
TrimTrailingBlanks   | (n/a)
FixedLenNullInSource | (n/a)
Collation            | NULL
-[ RECORD 6 ]-------------------------
Column_name          | school_bulletin_prefix_code
Type                 | nvarchar
Computed             | no
Length               | 20
Prec                 |      
Scale                |      
Nullable             | no
TrimTrailingBlanks   | (n/a)
FixedLenNullInSource | (n/a)
Collation            | SQL_Latin1_General_CP1_CI_AS
-[ RECORD 7 ]-------------------------
Column_name          | suffix_two
Type                 | nvarchar
Computed             | no
Length               | 4
Prec                 |      
Scale                |      
Nullable             | no
TrimTrailingBlanks   | (n/a)
FixedLenNullInSource | (n/a)
Collation            | SQL_Latin1_General_CP1_CI_AS
-[ RECORD 8 ]-------------------------
Column_name          | subject_area_code
Type                 | nvarchar
Computed             | no
Length               | 20
Prec                 |      
Scale                |      
Nullable             | no
TrimTrailingBlanks   | (n/a)
FixedLenNullInSource | (n/a)
Collation            | SQL_Latin1_General_CP1_CI_AS
-[ RECORD 9 ]-------------------------
Column_name          | course_number
Type                 | nvarchar
Computed             | no
Length               | 20
Prec                 |      
Scale                |      
Nullable             | no
TrimTrailingBlanks   | (n/a)
FixedLenNullInSource | (n/a)
Collation            | SQL_Latin1_General_CP1_CI_AS
-[ RECORD 10 ]-------------------------
Column_name          | course_identifier
Type                 | nvarchar
Computed             | no
Length               | 20
Prec                 |      
Scale                |      
Nullable             | no
TrimTrailingBlanks   | (n/a)
FixedLenNullInSource | (n/a)
Collation            | SQL_Latin1_General_CP1_CI_AS
-[ RECORD 11 ]-------------------------
Column_name          | course_name
Type                 | nvarchar
Computed             | no
Length               | 160
Prec                 |      
Scale                |      
Nullable             | no
TrimTrailingBlanks   | (n/a)
FixedLenNullInSource | (n/a)
Collation            | SQL_Latin1_General_CP1_CI_AS
-[ RECORD 12 ]-------------------------
Column_name          | course_description
Type                 | nvarchar
Computed             | no
Length               | -1
Prec                 |      
Scale                |      
Nullable             | no
TrimTrailingBlanks   | (n/a)
FixedLenNullInSource | (n/a)
Collation            | SQL_Latin1_General_CP1_CI_AS
 
+-----------------------------+--------+-------------+-----------------------+
| Identity                    | Seed   | Increment   | Not For Replication   |
|-----------------------------+--------+-------------+-----------------------|
| No identity column defined. | NULL   | NULL        | NULL                  |
+-----------------------------+--------+-------------+-----------------------+
 
+-------------------------------+
| RowGuidCol                    |
|-------------------------------|
| No rowguidcol column defined. |
+-------------------------------+
 
+-----------------------------+
| Data_located_on_filegroup   |
|-----------------------------|
| PRIMARY                     |
+-----------------------------+
 
+--------------------------------+-----------------------------------------------------+-------------------+
| index_name                     | index_description                                   | index_keys        |
|--------------------------------+-----------------------------------------------------+-------------------|
| PK__myapp_co__3213E83F500C3B0B | clustered, unique, primary key located on PRIMARY   | id                |
| UQ__myapp_co__845E0C43A4915951 | nonclustered, unique, unique key located on PRIMARY | course_identifier |
+--------------------------------+-----------------------------------------------------+-------------------+
 
+-------------------------+--------------------------------+-----------------+-----------------+------------------+--------------------------+-------------------+
| constraint_type         | constraint_name                | delete_action   | update_action   | status_enabled   | status_for_replication   | constraint_keys   |
|-------------------------+--------------------------------+-----------------+-----------------+------------------+--------------------------+-------------------|
| PRIMARY KEY (clustered) | PK__myapp_co__3213E83F500C3B0B | (n/a)           | (n/a)           | (n/a)            | (n/a)                    | id                |
| UNIQUE (non-clustered)  | UQ__myapp_co__845E0C43A4915951 | (n/a)           | (n/a)           | (n/a)            | (n/a)                    | course_identifier |
+-------------------------+--------------------------------+-----------------+-----------------+------------------+--------------------------+-------------------+
 
+---------------------------------------------------------------------------------------+
| Table is referenced by foreign key                                                    |
|---------------------------------------------------------------------------------------|
| proposal.dbo.myapp_courseterm: myapp_courseterm_course_id_edb833e8_fk_myapp_course_id |
+---------------------------------------------------------------------------------------+

MySQL client

(env) django-training$ mysql -uroot foo
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.12 Homebrew

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from myapp_course limit 4;
+----------------------------------+----------------------+--------------------+--------------------+---------------+-----------------------------+------------+-------------------+---------------+-------------------+-------------------------------+-------------------------------+
| id                               | effective_start_date | effective_end_date | last_mod_user_name | last_mod_date | school_bulletin_prefix_code | suffix_two | subject_area_code | course_number | course_identifier | course_name                   | course_description            |
+----------------------------------+----------------------+--------------------+--------------------+---------------+-----------------------------+------------+-------------------+---------------+-------------------+-------------------------------+-------------------------------+
| 001b55e09a60438698c74c856bb840b4 | NULL                 | NULL               | loader             | 2018-08-03    | XCEFK9                      | 00         | ANTB              | 04961         | ANTH3160V         | THE BODY AND SOCIETY          | THE BODY AND SOCIETY          |
| 00fb17bbe4a049a0a27e6939e3e04b62 | NULL                 | NULL               | loader             | 2018-08-03    | B                           | 00         | ACCT              | 73272         | ACCT8122B         | Accounting for Consultants    | Accounting for Consultants    |
| 016659e9e29f49b4b85dd25da0724dbb | NULL                 | NULL               | loader             | 2018-08-03    | B                           | 00         | ACCT              | 73290         | ACCT7022B         | Accounting for Value          | Accounting for Value          |
| 01ca197fc00c4f24a743091b62f1d500 | NULL                 | NULL               | loader             | 2018-08-03    | XCEFK9                      | 00         | AMSB              | 00373         | AMST3704X         | SENIOR RESEARCH ESSAY SEMINAR | SENIOR RESEARCH ESSAY SEMINAR |
+----------------------------------+----------------------+--------------------+--------------------+---------------+-----------------------------+------------+-------------------+---------------+-------------------+-------------------------------+-------------------------------+
4 rows in set (0.00 sec)

mysql> show tables;
+-------------------------------+
| Tables_in_foo                 |
+-------------------------------+
| auth_group                    |
| auth_group_permissions        |
| auth_permission               |
| auth_user                     |
| auth_user_groups              |
| auth_user_user_permissions    |
| django_admin_log              |
| django_content_type           |
| django_migrations             |
| django_session                |
| myapp_course                  |
| myapp_courseterm              |
| myapp_instructor              |
| myapp_instructor_course_terms |
| oauth2_provider_accesstoken   |
| oauth2_provider_application   |
| oauth2_provider_grant         |
| oauth2_provider_refreshtoken  |
| test                          |
+-------------------------------+
19 rows in set (0.00 sec)

mysql> describe myapp_course;
+-----------------------------+-------------+------+-----+---------+-------+
| Field                       | Type        | Null | Key | Default | Extra |
+-----------------------------+-------------+------+-----+---------+-------+
| id                          | char(32)    | NO   | PRI | NULL    |       |
| effective_start_date        | date        | YES  |     | NULL    |       |
| effective_end_date          | date        | YES  |     | NULL    |       |
| last_mod_user_name          | varchar(80) | YES  |     | NULL    |       |
| last_mod_date               | date        | NO   |     | NULL    |       |
| school_bulletin_prefix_code | varchar(10) | NO   |     | NULL    |       |
| suffix_two                  | varchar(2)  | NO   |     | NULL    |       |
| subject_area_code           | varchar(10) | NO   |     | NULL    |       |
| course_number               | varchar(10) | NO   |     | NULL    |       |
| course_identifier           | varchar(10) | NO   | UNI | NULL    |       |
| course_name                 | varchar(80) | NO   |     | NULL    |       |
| course_description          | longtext    | NO   |     | NULL    |       |
+-----------------------------+-------------+------+-----+---------+-------+
12 rows in set (0.01 sec)

Advanced Topic: SQL Server Workarounds

GIT TAG: v0.2.1

Unfortunately, Microsoft SQL Server and the django-pyodbc-azure package have a number of implementation deficiences and failures to comply with the ANSI SQL standard. SQL Server is not one of the popular database engines used by the Django community (sqlite3, mysql, postgresql).

Several features used by Django Migrations can cause problems when using SQL Server. Things we've seen so far and have had to work around after prototyping our app using sqlite3:

Reminder: DJANGO_SQLSERVER environnment variables

Because we conditionalized the database in settings.py via environment variables, don't forget to set them in the environment. I did this with a script which I can either source or use to run one-off commands:

(env) django-training$ cat sqlserver.sh
#!/bin/sh
export DJANGO_SQLSERVER=true
export DJANGO_SQLSERVER_DB=foo
export DJANGO_SQLSERVER_USER=foo
export DJANGO_SQLSERVER_PASS="foo!"
export DJANGO_SQLSERVER_HOST=127.0.0.1,1433
$*
(env) django-training$ source sqlserver.sh

See "Set up Run/Debug Configurations for the Project", above, for how to set environment variables in PyCharm.

Workarounds for these deficiences are described here.

TextField can't be unique

In fact our update of CourseTerm breaks with SQL Server with this error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'term_identifier' in table 'myapp_courseterm' is of a type that is invalid for use as a key column in an index. (1919) (SQLExecDirectW)")

In this case, it was probably because we inadvertently used a TextField instead of CharField in the original model.

Let's fix that:

  1. Make the TextField to CharField change happen before making it unique in our manual migration (myapp/migrations/0003_unique_term_identifier.py).
  2. Remove the 0004 automigration that tries to make the TextField a unique index since we've already done it. Make sure to fix the dependency for any following migration to skip the removed one.
  3. Migrate.

(Note that I also had to specify the CharField length a second time -- that shouldn't be necessary -- probably due to a bug in the django-pyodbc-azure package which passed None for the field length to the SQL Server!)

diff --git a/myapp/migrations/0003_unique_term_identifier.py b/myapp/migrations/0003_unique_term_identifier.py
index 1e0d26d..04dcda7 100644
--- a/myapp/migrations/0003_unique_term_identifier.py
+++ b/myapp/migrations/0003_unique_term_identifier.py
@@ -34,7 +34,7 @@ class Migration(migrations.Migration):
         migrations.AlterField(
             model_name='courseterm',
             name='term_identifier',
-            field=models.TextField(max_length=14),
+            field=models.CharField(max_length=14),
         ),
         migrations.RunPython(
             fix_term_id,
@@ -43,6 +43,6 @@ class Migration(migrations.Migration):
         migrations.AlterField(
             model_name='courseterm',
             name='term_identifier',
-            field=models.TextField(unique=True),
+            field=models.CharField(max_length=14, unique=True),
         ),
     ]
diff --git a/myapp/migrations/0005_instructor.py b/myapp/migrations/0005_instructor.py
index 93e7de9..ff41366 100644
--- a/myapp/migrations/0005_instructor.py
+++ b/myapp/migrations/0005_instructor.py
@@ -7,7 +7,7 @@ import uuid
 class Migration(migrations.Migration):
 
     dependencies = [
-        ('myapp', '0004_auto_20181109_2050'),
+        ('myapp', '0003_unique_term_identifier'),
     ]
 
     operations = [     
(env) django-training$ git rm myapp/migrations/0004_auto_20181109_2050.py 
rm 'myapp/migrations/0004_auto_20181109_2050.py'
(env) django-training$ ./manage.py showmigrations myapp
myapp
 [X] 0001_initial
 [X] 0002_auto_20181019_1821
 [ ] 0003_unique_term_identifier
 [ ] 0005_instructor
 [ ] 0006_auto_20181116_2058
(env) django-training$ ./manage.py migrate myapp

The above still now blows up on 0005 but at least 0003 worked:

(env) django-training$ ./manage.py showmigrations myapp
myapp
 [X] 0001_initial
 [X] 0002_auto_20181019_1821
 [X] 0003_unique_term_identifier
 [ ] 0005_instructor
 [ ] 0006_auto_20181116_2058
(env) django-training$ ./manage.py migrate myapp

It turns out I had the same TextField vs. CharField error with the Instructor.name (since changed to Person.name).

New Way: A fix for django-pyodbc-azure

django-pyodbc-azure PR #189 fixes the latter two of the problems outlined above. It has still not been merged and published, so we pull it from the submitter's github for now with this entry in requirements-sqlserver.txt:

(Note that as of pip>=10.0.1 you can now reference a PR directly instead of having to find the source of the PR.)

git+https://github.com/michiya/django-pyodbc-azure@refs/pull/189/head

Old Way: Overriding migrations

See the new way that fixes the deficiences via django-pyodbc-azure, above. For historical purposes, here's the old way:

The migrations used by DOT break for SQL Server. One approach to fix this is to fork the project and keep a local copy with edited migrations that work around the problems. Another is to use the standard package but override the migrations locally. In both cases, we are "fixing" existing migrations; it's just a matter of how we keep track of them. Let's look at the latter approach.

  1. Override the migrations in settings only for SQL Server.
  2. Undo the partial oauth2_provider migrations.
  3. Copy the current oauth2_provder migrations to myapp/migration_overrides/oauth2_provider
  4. Manually edit the migrations to squash in later changes that SQL Server can't do (like AutoFields).
Override migrations for oauth2_provider

We only want to maintain this kludge if using SQL Server, so conditionalize the overridden migration:

diff --git a/training/settings.py b/training/settings.py
index 327dafd..c7468b0 100644
--- a/training/settings.py
+++ b/training/settings.py
@@ -83,7 +83,7 @@ WSGI_APPLICATION = 'training.wsgi.application'
 # https://docs.djangoproject.com/en/2.1/ref/settings/#databases
 
 if SQLSERVER:
-    # Use the following if testing with MS SQL:
+    # Use the following if using with MS SQL:
     DATABASES = {
         'default': {
             'ENGINE': 'sql_server.pyodbc',
@@ -97,6 +97,12 @@ if SQLSERVER:
             },
         },
     }
+
+    # override the standard migrations because they break due to SQL Server deficiences.
+    MIGRATION_MODULES = {
+        'oauth2_provider': 'myapp.migration_overrides.oauth2_provider',
+    }
+
 else:
     DATABASES = {
         'default': {

Undo any partial migrations (0001-4 worked just fine; 0005 is where things start to break down.)

(env) django-training$ ./manage.py migrate oauth2_provider zero
Operations to perform:
  Unapply all migrations: oauth2_provider
Running migrations:
  Rendering model states... DONE
  Unapplying oauth2_provider.0004_auto_20160525_1623... OK
  Unapplying oauth2_provider.0003_auto_20160316_1503... OK
  Unapplying oauth2_provider.0002_08_updates... OK
  Unapplying oauth2_provider.0001_initial... OK

Edit the 0001_initial migration to make the later schema changes happen at the outset. We have no old oauth2_provider data that we care about so this is not a problem.

--- a/myapp/migration_overrides/oauth2_provider/0001_initial.py
+++ b/myapp/migration_overrides/oauth2_provider/0001_initial.py
@@ -1,4 +1,5 @@
 from django.conf import settings
+import django.db.models.deletion
 from django.db import migrations, models
 
 import oauth2_provider.generators
@@ -7,7 +8,15 @@ from oauth2_provider.settings import oauth2_settings
 
 
 class Migration(migrations.Migration):
-
+    """
+    The following migrations are squashed here:
+    - 0001_initial.py
+    - 0002_08_updates.py
+    - 0003_auto_20160316_1503.py
+    - 0004_auto_20160525_1623.py
+    - 0005_auto_20170514_1141.py
+    - 0006_auto_20171214_2232.py
+    """
     dependencies = [
         migrations.swappable_dependency(settings.AUTH_USER_MODEL)
     ]
@@ -16,14 +25,17 @@ class Migration(migrations.Migration):
         migrations.CreateModel(
             name='Application',
             fields=[
-                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
+                ('id', models.BigAutoField(serialize=False, primary_key=True)),
                 ('client_id', models.CharField(default=oauth2_provider.generators.generate_client_id, unique=True, max_length=100, db_index=True)),
                 ('redirect_uris', models.TextField(help_text='Allowed URIs list, space separated', blank=True)),
                 ('client_type', models.CharField(max_length=32, choices=[('confidential', 'Confidential'), ('public', 'Public')])),
                 ('authorization_grant_type', models.CharField(max_length=32, choices=[('authorization-code', 'Authorization code'), ('implicit', 'Implicit'), ('password', 'Resource owner password-based'), ('client-credentials', 'Client credentials')])),
                 ('client_secret', models.CharField(default=oauth2_provider.generators.generate_client_secret, max_length=255, db_index=True, blank=True)),
                 ('name', models.CharField(max_length=255, blank=True)),
-                ('user', models.ForeignKey(to=settings.AUTH_USER_MODEL, on_delete=models.CASCADE)),
+                ('user', models.ForeignKey(related_name="oauth2_provider_application", blank=True, to=settings.AUTH_USER_MODEL, null=True, on_delete=models.CASCADE)),
+                ('skip_authorization', models.BooleanField(default=False)),
+                ('created', models.DateTimeField(auto_now_add=True)),
+                ('updated', models.DateTimeField(auto_now=True)),
             ],
             options={
                 'abstract': False,
@@ -33,12 +45,16 @@ class Migration(migrations.Migration):
         migrations.CreateModel(
             name='AccessToken',
             fields=[
-                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
-                ('token', models.CharField(max_length=255, db_index=True)),
+                ('id', models.BigAutoField(serialize=False, primary_key=True)),
+                ('token', models.CharField(unique=True, max_length=255)),
                 ('expires', models.DateTimeField()),
                 ('scope', models.TextField(blank=True)),
-                ('application', models.ForeignKey(to=oauth2_settings.APPLICATION_MODEL, on_delete=models.CASCADE)),
-                ('user', models.ForeignKey(to=settings.AUTH_USER_MODEL, on_delete=models.CASCADE)),
+                ('application', models.ForeignKey(blank=True, null=True, on_delete=django.db.models.deletion.CASCADE, to=oauth2_settings.APPLICATION_MODEL)),
+                ('user', models.ForeignKey(blank=True, null=True, on_delete=django.db.models.deletion.CASCADE, related_name='oauth2_provider_accesstoken', to=settings.AUTH_USER_MODEL)),
+                ('created', models.DateTimeField(auto_now_add=True)),
+                ('updated', models.DateTimeField(auto_now=True)),
+                # Circular reference. Can't add it here.
+                #('source_refresh_token', models.OneToOneField(blank=True, null=True, on_delete=django.db.models.deletion.SET_NULL, to=oauth2_settings.REFRESH_TOKEN_MODEL, related_name="refreshed_access_token")),
             ],
             options={
                 'abstract': False,
@@ -48,13 +64,15 @@ class Migration(migrations.Migration):
         migrations.CreateModel(
             name='Grant',
             fields=[
-                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
-                ('code', models.CharField(max_length=255, db_index=True)),
+                ('id', models.BigAutoField(serialize=False, primary_key=True)),
+                ('code', models.CharField(unique=True, max_length=255)),
                 ('expires', models.DateTimeField()),
                 ('redirect_uri', models.CharField(max_length=255)),
                 ('scope', models.TextField(blank=True)),
                 ('application', models.ForeignKey(to=oauth2_settings.APPLICATION_MODEL, on_delete=models.CASCADE)),
-                ('user', models.ForeignKey(to=settings.AUTH_USER_MODEL, on_delete=models.CASCADE)),
+                ('user', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='oauth2_provider_grant', to=settings.AUTH_USER_MODEL)),
+                ('created', models.DateTimeField(auto_now_add=True)),
+                ('updated', models.DateTimeField(auto_now=True)),
             ],
             options={
                 'abstract': False,
@@ -64,15 +82,24 @@ class Migration(migrations.Migration):
         migrations.CreateModel(
             name='RefreshToken',
             fields=[
-                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
-                ('token', models.CharField(max_length=255, db_index=True)),
-                ('access_token', models.OneToOneField(related_name='refresh_token', to=oauth2_settings.ACCESS_TOKEN_MODEL, on_delete=models.CASCADE)),
+                ('id', models.BigAutoField(serialize=False, primary_key=True)),
+                ('token', models.CharField(max_length=255)),
+                ('access_token', models.OneToOneField(blank=True, null=True, related_name="refresh_token", to=oauth2_settings.ACCESS_TOKEN_MODEL, on_delete=models.SET_NULL)),
                 ('application', models.ForeignKey(to=oauth2_settings.APPLICATION_MODEL, on_delete=models.CASCADE)),
-                ('user', models.ForeignKey(to=settings.AUTH_USER_MODEL, on_delete=models.CASCADE)),
+                ('user', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='oauth2_provider_refreshtoken', to=settings.AUTH_USER_MODEL)),
+                ('created', models.DateTimeField(auto_now_add=True)),
+                ('updated', models.DateTimeField(auto_now=True)),
+                ('revoked', models.DateTimeField(null=True)),
             ],
             options={
                 'abstract': False,
                 'swappable': 'OAUTH2_PROVIDER_REFRESH_TOKEN_MODEL',
+                'unique_together': set([("token", "revoked")]),
             },
         ),
+        migrations.AddField(
+            model_name='AccessToken',
+            name='source_refresh_token',
+            field=models.OneToOneField(blank=True, null=True, on_delete=django.db.models.deletion.SET_NULL, to=oauth2_settings.REFRESH_TOKEN_MODEL, related_name="refreshed_access_token"),
+        ),
     ]
(env) django-training$ git rm myapp/migration_overrides/oauth2_provider/000[23456]*
rm 'myapp/migration_overrides/oauth2_provider/0002_08_updates.py'
rm 'myapp/migration_overrides/oauth2_provider/0003_auto_20160316_1503.py'
rm 'myapp/migration_overrides/oauth2_provider/0004_auto_20160525_1623.py'
rm 'myapp/migration_overrides/oauth2_provider/0005_auto_20170514_1141.py'
rm 'myapp/migration_overrides/oauth2_provider/0006_auto_20171214_2232.py'
Workaround mishandling of NULL UNIQUE indexes

Since sqlserver is broken we'll want to work around the NULL UNIQUE constraint. Here's the error that is thrown:

('23000', "[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Violation of UNIQUE KEY constraint 'UQ__oauth2_p__A1C69CA6C6465E5D'. Cannot insert duplicate key in object 'dbo.oauth2_provider_accesstoken'. The duplicate key value is (<NULL>). (2627) (SQLExecDirectW)")

Let's start by seeing what SQL DDL is generated by our migration:

(env) django-training$ ./manage.py sqlmigrate oauth2_provider 0001_initial
BEGIN TRANSACTION
--
-- Create model Application
--
CREATE TABLE [oauth2_provider_application] ([id] bigint IDENTITY (1, 1) NOT NULL PRIMARY KEY, [client_id] nvarchar(100) NOT NULL UNIQUE, [redirect_uris] nvarchar(max) NOT NULL, [client_type] nvarchar(32) NOT NULL, [authorization_grant_type] nvarchar(32) NOT NULL, [client_secret] nvarchar(255) NOT NULL, [name] nvarchar(255) NOT NULL, [user_id] int NULL, [skip_authorization] bit NOT NULL, [created] datetime2 NOT NULL, [updated] datetime2 NOT NULL);
--
-- Create model AccessToken
--
CREATE TABLE [oauth2_provider_accesstoken] ([id] bigint IDENTITY (1, 1) NOT NULL PRIMARY KEY, [token] nvarchar(255) NOT NULL UNIQUE, [expires] datetime2 NOT NULL, [scope] nvarchar(max) NOT NULL, [application_id] bigint NULL, [user_id] int NULL, [created] datetime2 NOT NULL, [updated] datetime2 NOT NULL);
--
-- Create model Grant
--
CREATE TABLE [oauth2_provider_grant] ([id] bigint IDENTITY (1, 1) NOT NULL PRIMARY KEY, [code] nvarchar(255) NOT NULL UNIQUE, [expires] datetime2 NOT NULL, [redirect_uri] nvarchar(255) NOT NULL, [scope] nvarchar(max) NOT NULL, [application_id] bigint NOT NULL, [user_id] int NOT NULL, [created] datetime2 NOT NULL, [updated] datetime2 NOT NULL);
--
-- Create model RefreshToken
--
CREATE TABLE [oauth2_provider_refreshtoken] ([id] bigint IDENTITY (1, 1) NOT NULL PRIMARY KEY, [token] nvarchar(255) NOT NULL, [access_token_id] bigint NULL UNIQUE, [application_id] bigint NOT NULL, [user_id] int NOT NULL, [created] datetime2 NOT NULL, [updated] datetime2 NOT NULL, [revoked] datetime2 NULL);
--
-- Add field source_refresh_token to AccessToken
--
ALTER TABLE [oauth2_provider_accesstoken] ADD [source_refresh_token_id] bigint NULL UNIQUE;
ALTER TABLE [oauth2_provider_application] ADD CONSTRAINT [oauth2_provider_application_user_id_79829054_fk_auth_user_id] FOREIGN KEY ([user_id]) REFERENCES [auth_user] ([id]);
CREATE INDEX [oauth2_provider_application_client_secret_53133678] ON [oauth2_provider_application] ([client_secret]);
CREATE INDEX [oauth2_provider_application_user_id_79829054] ON [oauth2_provider_application] ([user_id]);
ALTER TABLE [oauth2_provider_accesstoken] ADD CONSTRAINT [oauth2_provider_accesstoken_application_id_b22886e1_fk_oauth2_provider_application_id] FOREIGN KEY ([application_id]) REFERENCES [oauth2_provider_application] ([id]);
ALTER TABLE [oauth2_provider_accesstoken] ADD CONSTRAINT [oauth2_provider_accesstoken_user_id_6e4c9a65_fk_auth_user_id] FOREIGN KEY ([user_id]) REFERENCES [auth_user] ([id]);
CREATE INDEX [oauth2_provider_accesstoken_application_id_b22886e1] ON [oauth2_provider_accesstoken] ([application_id]);
CREATE INDEX [oauth2_provider_accesstoken_user_id_6e4c9a65] ON [oauth2_provider_accesstoken] ([user_id]);
ALTER TABLE [oauth2_provider_grant] ADD CONSTRAINT [oauth2_provider_grant_application_id_81923564_fk_oauth2_provider_application_id] FOREIGN KEY ([application_id]) REFERENCES [oauth2_provider_application] ([id]);
ALTER TABLE [oauth2_provider_grant] ADD CONSTRAINT [oauth2_provider_grant_user_id_e8f62af8_fk_auth_user_id] FOREIGN KEY ([user_id]) REFERENCES [auth_user] ([id]);
CREATE INDEX [oauth2_provider_grant_application_id_81923564] ON [oauth2_provider_grant] ([application_id]);
CREATE INDEX [oauth2_provider_grant_user_id_e8f62af8] ON [oauth2_provider_grant] ([user_id]);
ALTER TABLE [oauth2_provider_refreshtoken] ADD CONSTRAINT [oauth2_provider_refreshtoken_access_token_id_775e84e8_fk_oauth2_provider_accesstoken_id] FOREIGN KEY ([access_token_id]) REFERENCES [oauth2_provider_accesstoken] ([id]);
ALTER TABLE [oauth2_provider_refreshtoken] ADD CONSTRAINT [oauth2_provider_refreshtoken_application_id_2d1c311b_fk_oauth2_provider_application_id] FOREIGN KEY ([application_id]) REFERENCES [oauth2_provider_application] ([id]);
ALTER TABLE [oauth2_provider_refreshtoken] ADD CONSTRAINT [oauth2_provider_refreshtoken_user_id_da837fce_fk_auth_user_id] FOREIGN KEY ([user_id]) REFERENCES [auth_user] ([id]);
ALTER TABLE [oauth2_provider_refreshtoken] ADD CONSTRAINT [oauth2_provider_refreshtoken_token_revoked_af8a5134_uniq] UNIQUE ([token], [revoked]);
CREATE INDEX [oauth2_provider_refreshtoken_application_id_2d1c311b] ON [oauth2_provider_refreshtoken] ([application_id]);
CREATE INDEX [oauth2_provider_refreshtoken_user_id_da837fce] ON [oauth2_provider_refreshtoken] ([user_id]);
ALTER TABLE [oauth2_provider_accesstoken] ADD CONSTRAINT [oauth2_provider_accesstoken_source_refresh_token_id_e66fbc72_fk_oauth2_provider_refreshtoken_id] FOREIGN KEY ([source_refresh_token_id]) REFERENCES [oauth2_provider_refreshtoken] ([id]);
COMMIT;

Let's start by reproducing the error in sqlcmd so that we can confirm we've fixed it. We'll insert two NULL source_refresh_token_id's:

1> insert into oauth2_provider_accesstoken(token,expires,created,updated,scope)
2> values('abc','2018-12-01','2018-11-18','2018-11-18','foo')
3> go

(1 rows affected)
1> insert into oauth2_provider_accesstoken(token,expires,created,updated,scope)
2> values('def','2018-12-01','2018-11-18','2018-11-18','foo')
3> go
Msg 2627, Level 14, State 1, Server ad864306b113, Line 1
Violation of UNIQUE KEY constraint 'UQ__oauth2_p__A1C69CA698B1DABA'. Cannot insert duplicate key in object 'dbo.oauth2_provider_accesstoken'. The duplicate key value is (<NULL>).
The statement has been terminated.

Looks like there's not actually an index but maybe the bigint NULL UNIQUE constraint. Let's try:

  1. Undo the migration.
  2. Change accesstoken(source_refresh_token_id) from 'NULL UNIQUE' to 'NULL' in 0001 by making it a ForeignKey.
  3. Add a UNIQUE INDEX to enforce uniqueness to get back the OneToOneField behavior.
(env) django-training$ ./sqlserver.sh ./manage.py migrate oauth2_provider zero
Operations to perform:
  Unapply all migrations: oauth2_provider
Running migrations:
  Rendering model states... DONE
  Unapplying oauth2_provider.0001_initial... OK

We'll add some SQL to remove the NULL UNIQUE from the column constraints and instead implement proper NULL UNIQUE enforcement by manually adding a "CREATE UNIQUE INDEX ... WHERE ... IS NOT NULL".

Due to the various referential intregrity constraints in the models, we have to do a bit of extra work:

  1. Dummy up an Application.
  2. Count on a User already existing (via ./manage.py createsuperuser)
  3. Dummy up a RefreshToken.
  4. Add a couple of AccessTokens that have NULL source_refresh_token.
  5. Try to add an AccessToken that references a non-existant RefreshToken id (1).
  6. Add an AccessToken that references an existing RefreshToken id (4).
  7. Try to add it again and get a duplicate key error.
  8. Confirm that we have multiple NULL source_refresh_token id's as well as a unique value (4).
  9. Drop the unique index to prove we can screw things up with a duplicate.
  10. Finally, drop and recreate the database so we have a clean starting point to test our fix.
1> SET QUOTED_IDENTIFIER ON
2> go
1> CREATE UNIQUE INDEX [oauth2_provider_accesstoken_source_refresh_token] ON [oauth2_provider_accesstoken][(source_refresh_token_id)] where [source_refresh_token_id] IS NOT NULL;
2> go
1> insert into oauth2_provider_application(client_id,redirect_uris,client_type,authorization_grant_type,client_secret,name,skip_authorization,created,updated)
2> values('foofoo','http://go.away','foo','foo','secret','name',1,'2018-11-18','2018-11-18')
3> go

(1 rows affected)
1> insert into oauth2_provider_refreshtoken(token,created,updated,application_id,user_id)
2> values('ghi','2018-11-18','2018-11-18',1,1)
3> go

(1 rows affected)
1> insert into oauth2_provider_accesstoken(token,expires,created,updated,scope)
2> values('123','2018-12-01','2018-11-18','2018-11-18','foo')
3> go

(1 rows affected)
1> insert into oauth2_provider_accesstoken(token,expires,created,updated,scope)
2> values('321','2018-12-01','2018-11-18','2018-11-18','foo')
3> go

(1 rows affected)
1> insert into oauth2_provider_accesstoken(token,expires,created,updated,scope,source_refresh_token_id)
2> values('999','2018-12-01','2018-11-18','2018-11-18','foo',1)
3> go
Msg 547, Level 16, State 1, Server ad864306b113, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "oauth2_provider_accesstoken_source_refresh_token_id_e66fbc72_fk_oauth2_provider_refreshtoken_id". The conflict occurred in database "proposal", table "dbo.oauth2_provider_refreshtoken", column 'id'.
The statement has been terminated.
1> select id from oauth2_provider_refreshtoken;
2> go
id                  
--------------------
                   4

(1 rows affected)
1> insert into oauth2_provider_accesstoken(token,expires,created,updated,scope,source_refresh_token_id)
2> values('1010','2018-12-01','2018-11-18','2018-11-18','foo',4)
3> go

(1 rows affected)
1> insert into oauth2_provider_accesstoken(token,expires,created,updated,scope,source_refresh_token_id)
2> values('111','2018-12-01','2018-11-18','2018-11-18','foo',4)
3> go
Msg 2601, Level 14, State 1, Server ad864306b113, Line 1
Cannot insert duplicate key row in object 'dbo.oauth2_provider_accesstoken' with unique index 'oauth2_provider_accesstoken_source_refresh_token'. The duplicate key value is (4).
The statement has been terminated.
1> select id,token,source_refresh_token_id from oauth2_provider_accesstoken
2> go
id                   token                                                                                                                                                                                                                                                           source_refresh_token_id
-------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
                   1 abc                                                                                                                                                                                                                                                                                NULL
                   2 def                                                                                                                                                                                                                                                                                NULL
                   3 ghi                                                                                                                                                                                                                                                                                NULL
                   6 123                                                                                                                                                                                                                                                                                NULL
                   7 321                                                                                                                                                                                                                                                                                NULL
                   9 999                                                                                                                                                                                                                                                                                   4

(6 rows affected)
1> drop index oauth2_provider_accesstoken_source_refresh_token on oauth2_provider_accesstoken
2> go
1> insert into oauth2_provider_accesstoken(token,expires,created,updated,scope,source_refresh_token_id)
2> values('111','2018-12-01','2018-11-18','2018-11-18','foo',4)
3> go

(1 rows affected)
1> select id,token,source_refresh_token_id from oauth2_provider_accesstoken
2> go
id                   token                                                                                                                                                                                                                                                           source_refresh_token_id
-------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
                   1 abc                                                                                                                                                                                                                                                                                NULL
                   2 def                                                                                                                                                                                                                                                                                NULL
                   3 ghi                                                                                                                                                                                                                                                                                NULL
                   6 123                                                                                                                                                                                                                                                                                NULL
                   7 321                                                                                                                                                                                                                                                                                NULL
                   9 999                                                                                                                                                                                                                                                                                   4
                  11 111                                                                                                                                                                                                                                                                                   4

(7 rows affected)
1> ^C
(env) django-training$ sqlcmd -S 127.0.0.1,1433  -U foo -P 'foo!' -Q "drop database foo"
(env) django-training$ sqlcmd -S 127.0.0.1,1433  -U foo -P 'foo!' -Q "create database foo"

Adding that Raw SQL to the migration was pretty easy.

--- a/myapp/migration_overrides/oauth2_provider/0001_initial.py
+++ b/myapp/migration_overrides/oauth2_provider/0001_initial.py
@@ -9,14 +9,32 @@ from oauth2_provider.settings import oauth2_settings
 
 class Migration(migrations.Migration):
     """
-    The following migrations are squashed here:
+    This migration is a workaround for django-pyodbc-azure and/or sqlserver deficiencies that cause the
+    standard oauth2_provider migrations to fail, specifically:
+    - changing AutoField to BigAutoField is not implemented
+    - sqlserver botches indexing UNIQUE NULL fields, acting as if two NULLs equal each other.
+
+    To use this, override the standard migrations in settings:
+    .. code-block:: python
+
+        MIGRATION_MODULES = {
+        'oauth2_provider': 'myapp.migration_overrides.oauth2_provider',
+        }
+
+    The following :py:mod:`oauth2_provider.migrations` are squashed here:
     - 0001_initial.py
     - 0002_08_updates.py
     - 0003_auto_20160316_1503.py
     - 0004_auto_20160525_1623.py
     - 0005_auto_20170514_1141.py
     - 0006_auto_20171214_2232.py
+
+    And SQL to work around the UNIQUE NULL issue is added.
     """
+    # TODO: Any future migrations will need to be copied here and the dependencies corrected until such time as
+    #       the django-oauth-toolkit project gets around to squashing migrations with a major release.
+    #       OR, fix django-pyodbc-azure.
+
     dependencies = [
         migrations.swappable_dependency(settings.AUTH_USER_MODEL)
     ]
@@ -97,9 +115,34 @@ class Migration(migrations.Migration):
                 'unique_together': set([("token", "revoked")]),
             },
         ),
+        # trying to change this to ForeignKey triggers a new migration so either:
+        # - add RunSQL to undo the NULL UNIQUE to just NULL and add a UNIQUE index
+        # - use a fixed version of django-pyodbc-azure that does that.
         migrations.AddField(
             model_name='AccessToken',
             name='source_refresh_token',
             field=models.OneToOneField(blank=True, null=True, on_delete=django.db.models.deletion.SET_NULL, to=oauth2_settings.REFRESH_TOKEN_MODEL, related_name="refreshed_access_token"),
         ),
+        migrations.RunSQL(
+            sql='ALTER TABLE [oauth2_provider_refreshtoken] ALTER COLUMN [access_token_id] bigint NULL',
+            reverse_sql='ALTER TABLE [oauth2_provider_refreshtoken] ALTER COLUMN [access_token_id] bigint NULL UNIQUE'
+        ),
+        migrations.RunSQL(
+            sql='CREATE UNIQUE INDEX [oauth2_provider_refreshtoken_access_token] '
+                'ON [oauth2_provider_refreshtoken]([access_token_id]) '
+                'where [access_token_id] IS NOT NULL',
+            reverse_sql='DROP INDEX [oauth2_provider_refreshtoken_access_token] '
+                        'on [oauth2_provider_refreshtoken]',
+        ),
+        migrations.RunSQL(
+            sql='ALTER TABLE [oauth2_provider_accesstoken] ALTER COLUMN [source_refresh_token_id] bigint NULL',
+            reverse_sql='ALTER TABLE [oauth2_provider_accesstoken] ALTER COLUMN [source_refresh_token_id] bigint NULL UNIQUE'
+        ),
+        migrations.RunSQL(
+            sql='CREATE UNIQUE INDEX [oauth2_provider_accesstoken_source_refresh_token] '
+                'ON [oauth2_provider_accesstoken]([source_refresh_token_id]) '
+                'where [source_refresh_token_id] IS NOT NULL',
+            reverse_sql='DROP INDEX [oauth2_provider_accesstoken_source_refresh_token] '
+                        'on [oauth2_provider_accesstoken]',
+        ),
     ]

Here's the difference for the resulting DDL:

21a22,37
> --
> -- Raw SQL operation
> --
> ALTER TABLE [oauth2_provider_refreshtoken] ALTER COLUMN [access_token_id] bigint NULL;
> --
> -- Raw SQL operation
> --
> CREATE UNIQUE INDEX [oauth2_provider_refreshtoken_access_token] ON [oauth2_provider_refreshtoken]([access_token_id]) where [access_token_id] IS NOT NULL;
> --
> -- Raw SQL operation
> --
> ALTER TABLE [oauth2_provider_accesstoken] ALTER COLUMN [source_refresh_token_id] bigint NULL;
> --
> -- Raw SQL operation
> --
> CREATE UNIQUE INDEX [oauth2_provider_accesstoken_source_refresh_token] ON [oauth2_provider_accesstoken]([source_refresh_token_id]) where [source_refresh_token_id] IS NOT NULL;```

And that does it!

Squashing Migrations

If you don't have data in the database that you care about, an easier approach is to squash migrations or just remove all the migration scripts and re-do ./manage.py makemigrations.

Debugging Migration DDL

If you want to see the SQL DDL statements that are used, use the ./manage.py sqlmigrate command.

Here's what a sqlite3 migration looks like:

(env) django-training$ ./manage.py sqlmigrate myapp 0004_instructor
BEGIN;
--
-- Create model Instructor
--
CREATE TABLE "myapp_instructor" ("id" char(32) NOT NULL PRIMARY KEY, "effective_start_date" date NULL, "effective_end_date" date NULL, "last_mod_user_name" varchar(80) NULL, "last_mod_date" date NOT NULL, "instr_name" varchar(100) NOT NULL UNIQUE);
CREATE TABLE "myapp_instructor_course_terms" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "instructor_id" char(32) NOT NULL REFERENCES "myapp_instructor" ("id") DEFERRABLE INITIALLY DEFERRED, "courseterm_id" char(32) NOT NULL REFERENCES "myapp_courseterm" ("id") DEFERRABLE INITIALLY DEFERRED);
CREATE UNIQUE INDEX "myapp_instructor_course_terms_instructor_id_courseterm_id_8f50dbb5_uniq" ON "myapp_instructor_course_terms" ("instructor_id", "courseterm_id");
CREATE INDEX "myapp_instructor_course_terms_instructor_id_c1121f18" ON "myapp_instructor_course_terms" ("instructor_id");
CREATE INDEX "myapp_instructor_course_terms_courseterm_id_5af9ffbe" ON "myapp_instructor_course_terms" ("courseterm_id");
COMMIT;

Compare that to a sqlserver migration:

(env) django-training$ ./sqlserver.sh ./manage.py sqlmigrate myapp 0004_instructor
BEGIN TRANSACTION
--
-- Create model Instructor
--
CREATE TABLE [myapp_instructor] ([id] char(32) NOT NULL PRIMARY KEY, [effective_start_date] date NULL, [effective_end_date] date NULL, [last_mod_user_name] nvarchar(80) NULL, [last_mod_date] date NOT NULL, [instr_name] nvarchar(100) NOT NULL UNIQUE);
CREATE TABLE [myapp_instructor_course_terms] ([id] int IDENTITY (1, 1) NOT NULL PRIMARY KEY, [instructor_id] char(32) NOT NULL, [courseterm_id] char(32) NOT NULL);
ALTER TABLE [myapp_instructor_course_terms] ADD CONSTRAINT [myapp_instructor_course_terms_instructor_id_c1121f18_fk_myapp_instructor_id] FOREIGN KEY ([instructor_id]) REFERENCES [myapp_instructor] ([id]);
ALTER TABLE [myapp_instructor_course_terms] ADD CONSTRAINT [myapp_instructor_course_terms_courseterm_id_5af9ffbe_fk_myapp_courseterm_id] FOREIGN KEY ([courseterm_id]) REFERENCES [myapp_courseterm] ([id]);
ALTER TABLE [myapp_instructor_course_terms] ADD CONSTRAINT [myapp_instructor_course_terms_instructor_id_courseterm_id_8f50dbb5_uniq] UNIQUE ([instructor_id], [courseterm_id]);
CREATE INDEX [myapp_instructor_course_terms_instructor_id_c1121f18] ON [myapp_instructor_course_terms] ([instructor_id]);
CREATE INDEX [myapp_instructor_course_terms_courseterm_id_5af9ffbe] ON [myapp_instructor_course_terms] ([courseterm_id]);
COMMIT;

And here's what a mysql migration looks like:

(env) django-training$ ./mysql.sh ./manage.py sqlmigrate myapp 0004_instructor
BEGIN;
--
-- Create model Instructor
--
CREATE TABLE `myapp_instructor` (`id` char(32) NOT NULL PRIMARY KEY, `effective_start_date` date NULL, `effective_end_date` date NULL, `last_mod_user_name` varchar(80) NULL, `last_mod_date` date NOT NULL, `instr_name` varchar(100) NOT NULL UNIQUE);
CREATE TABLE `myapp_instructor_course_terms` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `instructor_id` char(32) NOT NULL, `courseterm_id` char(32) NOT NULL);
ALTER TABLE `myapp_instructor_course_terms` ADD CONSTRAINT `myapp_instructor_cou_instructor_id_c1121f18_fk_myapp_ins` FOREIGN KEY (`instructor_id`) REFERENCES `myapp_instructor` (`id`);
ALTER TABLE `myapp_instructor_course_terms` ADD CONSTRAINT `myapp_instructor_cou_courseterm_id_5af9ffbe_fk_myapp_cou` FOREIGN KEY (`courseterm_id`) REFERENCES `myapp_courseterm` (`id`);
ALTER TABLE `myapp_instructor_course_terms` ADD CONSTRAINT `myapp_instructor_course__instructor_id_courseterm_8f50dbb5_uniq` UNIQUE (`instructor_id`, `courseterm_id`);
COMMIT;
You can’t perform that action at this time.