Skip to content

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.

Note

We've deprecated use of Microsoft SQL Server due to lack of support for the django database backend library. You should use MySQL, including the AWS Aurora Serverless MySQL flavor, or Postgresql, if you prefer.

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 postgresql that are configured via environment variables.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
if os.environ.get('MYSQL_HOST', None):
    password = os.environ.get('MYSQL_PASSWORD', None)
    # unable to pass None/null value in environment
    if password and password.lower() == 'none':
        password = None
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.mysql',
            'NAME': os.environ.get('MYSQL_DB','training'),
            'USER': os.environ.get('MYSQL_USER','admin'),
            'PASSWORD': password,
            'HOST': os.environ['MYSQL_HOST'],
            'PORT': os.environ.get('MYSQL_PORT','3306'),
            'OPTIONS': {
                # make mysql 5.6 work sort of right
                'init_command': 'SET default_storage_engine=INNODB,character_set_connection=utf8mb4,'
                                'collation_connection=utf8mb4_unicode_ci,'
                                'sql_mode="STRICT_TRANS_TABLES"'
            }
        }
    }
elif os.environ.get('PGSQL_HOST', None):
    DATABASES = {
        "default": {
            "ENGINE": "django.db.backends.postgresql",
            "NAME": os.environ.get('PGSQL_DB'),
            "USER": os.environ.get('PGSQL_USER'),
            "PASSWORD": os.environ.get('PGSQL_PASS',''),
            "HOST": os.environ['PGSQL_HOST'],
            "PORT": os.environ.get('PGSQL_PORT','5432'),
        }
    }

# otherwise, using local sqlite3:
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. You may have version compatibility problems on RHEL, but if you are to the point of running on RHEL, you should be using a MySQL database.

MySQL server

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

1
2
3
4
(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

For Windows see https://dev.mysql.com/downloads/mysql/.

database CLI tools

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

sqlite3 client

For the sqlite3 database, use sqlite3. For example: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
(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>

MySQL client

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
(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)

Reminder: MYSQL 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: 

1
2
3
4
5
6
7
8
(env) django-training$ cat mysql.sh
#!/bin/sh
export MYSQL_HOST=127.0.0.1
export MYSQL_USER=root
export MYSQL_PASSWORD="foo!"
export MYSQL_DB=training
$*
(env) django-training$ source mysql.sh

If you forget, you'll end up with sqlite3 as the default database.

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

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:

1
(env) django-training$ ./manage.py sqlmigrate myapp 0004_instructor

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
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;

And here's what a mysql migration looks like: 

1
(env) django-training$ ./mysql.sh ./manage.py sqlmigrate myapp 0004_instructor

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
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;

As you can see, Django's database layer hides the differences between different backend databases, so you can focus on what's important.