Set up a database for a Django project

Django in its 'out-of-the-box' state is set up to communicate with SQLite -- a lightweight relational database included with the Python distribution. So by default, Django automatically connects an SQLite database to your project.

Django officially supports (i.e. included in Django itself) five relational databases and unofficially (i.e. with third party packages) Django supports many more relational databases.

The Django configuration to connect to a database is done inside the settting.py file of a Django project in the DATABASES variable.

If you open the settings.py file of a Django project you'll notice the DATABASES variable has a default Python dictionary with the values illustrated in listing 1-18.

Listing 1-18. Default Django DATABASES dictionary

from pathlib import Path

BASE_DIR = Path(__file__).resolve().parent.parent


DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    }
}
Tip Use SQLite if you want the quickest database setup. If you use SQLite, you can skip to the last step in this section 'Test Django database connection and build Django base tables'

A database setup by itself can be time consuming. If you want the quickest setup to enable Django with a database leave the previous configuration as is. SQLite doesn't require additional credentials or Python packages to establish a Django database connection. Just be aware an SQLite database is a flat file and Django creates the SQLite database based on the NAME variable value. In the case of listing 1-18, under a Django project's BASE_DIR and as a flat file named db.sqlite3.

The Django DATABASES variable defines key-value pairs. Each key represents a database reference name and the value is a Python dictionary with the database connection parameters. In listing 1-18 you can observe the default database reference. The default reference name is used to indicate that any database related operation declared in a Django project be executed against this connection. This means that unless otherwise specified, all database CRUD (Create-Read-Update-Delete) operations are done against the database defined with the default key.

The database connection parameters for the default database in this case are the keys ENGINE and NAME, which represent a database engine (i.e. brand) and the name of the database instance, respectively.

The most important parameter of a Django database connection is the ENGINE value. The Django application logic associated with a database is platform neutral, which means that you always write database CRUD operations in the same way irrespective of the database selection. Nevertheless, there are minor differences between CRUD operations done against different databases which need to be taken into account.

Django takes care of this issue by supporting different backends or engines. Therefore, depending on the database brand you plan to use for a Django application, the ENGINE value has to be one of the values illustrated in table 1-2.

Table 1-2. Django ENGINE value for different databases

Database Django ENGINE value Required Django package
Officially supported MariaDB †django.db.backends.mysql None, it's included with Django
MySQL django.db.backends.mysql None, it's included with Django
Oracle django.db.backends.oracle None, it's included with Django
PostgreSQL django.db.backends.postgresql_psycopg2 None, it's included with Django
SQLite django.db.backends.sqlite3 None, it's included with Django
Unofficially supported Cockroach django_cockroachdb django-cockroachdb[10]
Firebird django.db.backends.firebird django-firebird[11]
Google Cloud Spanner django_spanner django-google-spanner[12]
IBM DB2 ibm_db_django ibm_db_django[13]
Microsoft SQL Server & Azure SQL mssql mssql-django[14]
SAP (Sybase) SQL Anywhere sqlany_django sqlany_django[15]
† MariaDB is supported through the same built-in ENGINE used by MySQL: django.db.backends.mysql.

The Django database connection parameter NAME is used to identify a database instance, and its value convention can vary depending on the database brand. For example, in listing 1-18 for the SQLite database the NAME value indicates the location of a flat file, whereas for a MySQL database it indicates the logical name of a database instance.

In addition to the ENGINE and NAME connection parameters, many other database parameters can be used to influence how Django connects to a database. Table 1-3 lists Django's built-in database connection parameters.

Table 1-3. Django built-in database connection parameters

Applies to Django connection parameter Default value Notes
All databases ATOMIC_REQUESTS False Enforces (or not) a transaction for each view request. By default set to False, because opening a transaction for every view has additional overhead. The impact on performance depends on the query patterns of an application and on how well a database handles locking.
AUTOCOMMIT True By default set to True, because otherwise it would require explicit transactions to perform commits.
CONN_MAX_AGE 0 The lifetime of a database connection in seconds. By default 0 which closes the database connection at the end of each request. Use None for unlimited persistent connections.
ENGINE '' (Empty string) The database backend to use. See Table 1-2 for value options.
HOST '' (Empty string) Defines a database host, where an empty string means localhost.For MySQL: If this value starts with a forward slash ('/'), MySQL will connect via a Unix socket to the specified socket (e.g."HOST": '/var/run/mysql'). If this value doesn't start with a forward slash, then this value is assumed to be the host.For PostgreSQL: By default(''), the connection to the database is done through UNIX domain sockets ('local' lines in pg_hba.conf). If the UNIX domain socket is not in the standard location, use the same value of unix_socket_directory from postgresql.conf. If you want to connect through TCP sockets, set HOST to 'localhost' or '127.0.0.1' ('host' lines in pg_hba.conf). On Windows, you should always define HOST, as UNIX domain sockets are not available.
*OPTIONS {} (Empty dictionary) Extra parameters to use when connecting to the database. Available parameters vary depending on database brand, consult the Django database engine package's documentation. See Table 1-2 for a list of Django database engine packages.
NAME '' (Empty string) The name of the database to use. For SQLite, it's the full path to the database file. When specifying the path, always use forward slashes, even on Windows (e.g. C:/www/STORE/db.sqlite3).
PASSWORD '' (Empty string) The password to use when connecting to the database. Not used with SQLite.
PORT '' (Empty string) The port to use when connecting to the database. An empty string means the default port. Not used with SQLite.
TEST {} (Empty dictionary) Defines a test database with connection parameters like the ones described in this 'All databases' table section. It's used to configure a Django project's test database.
TIME_ZONE None (Empty) Specifies the time zone for date times stored in a database. A rarely used option to streamline usage of database records that store times in a local timezone rather than UTC.
USER '' (Empty string) The username to use when connecting to the database. Not used with SQLite.
PostgreSQL databases DISABLE_SERVER_SIDE_CURSORS False Disables use of server-side cursors, used in scenarios with transaction pooling and server-side cursors.
*OPTIONS is the fallback parameter to specify all database parameters not supported via built-in Django parameter names.
Tip Database brand specific connection parameters (e.g. PostgreSQL service name) can be configured in Django through the OPTIONS parameter dictionary. (e.g. "OPTIONS": { "service": ...))

Install Python database packages

Besides configuring Django to connect to a database, you'll also need to prepare your Python installation to work with a Python database driver of your selected database brand.

Not to be confused with the database requirements in table 1-2 -- which are Django specific -- Python itself also requires a database driver package to interact with each database brand. This package installation process is straightforward with the pip package manager. If you don't have the pip executable on your system, see the previous section in this chapter 'Install Django' in the 'Install pip' sub-section.

With the exception of SQLite, all officially supported Django databases -- MariaDB, MySQL, Oracle & PostgreSQL -- require a Python database driver. Table 1-4 describes the pip installation instructions for each of these officially supported Django databases. If you opted for an unofficially supported Django database, it's sufficient to install the Django required package -- as described in table 1-2 -- for the Python database driver to be installed as a dependency.

Table 1-4 Python database driver packages for different Django databases

Database

Python package

pip installation syntax

MariaDB

mariadb

python3 -m pip install mariadb

MySQL

mysql-python

python3 -m pip install PyMySQL
AND
python3 -m pip install mysqlclient

Oracle

cx_Oracle

python3 -m pip install cx_Oracle

PostgreSQL

psycopg2

python3 -m pip install psycopg2

SQLite

Included with the Python distribution

N/A

All other databases described in table 1-2

Automatically downloaded by pip, when installing Django database specific package described in table 1-2

See table 1-2
Database development libraries

If you receive an error trying to install one of the Python database packages in table 1-4, ensure the database development libraries are installed on your system. Database development libraries are necessary to build software that connects to a database.

Database development libraries are not related to Python or Django, so you'll need to consult the database vendor or operating system documentation (e.g. On a Debian Linux or Ubuntu Linux system you can install the MySQL development libraries with the following apt task: apt install libmysqlclient-dev).

Test Django database connection and build Django base tables

Once you update the Django settings.py file with database credentials, you can test it to see if the Django application can communicate with the database. There are several tasks you'll do throughout a Django project that will communicate with the database, but one of the most common tasks you can do right now to test a database connection is migrate the project's data structures to the database.

The Django database migration process ensures all Django project logic associated with a database is reflected in the database itself (e.g. the database has the necessary tables expected by a Django project). When you start a Django project, there are a series of migrations Django requires that create tables to keep track of administrators and sessions. This is always the first migration process a Django project runs against a database. So to test the Django database connection, lets run this first migration on the database to create this set of base tables.

To run a migration on a Django project against a database use the manage.py script in a project's BASE_DIR with the migrate argument (e.g. python manage.py migrate). The first time you execute this command the output should be similar to listing 1-19.

Listing 1-19. Run first Django migrate operation to create base database tables

[user@coffeehouse ~]$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying sessions.0001_initial... OK

As illustrated in listing 1-19, if the connection to the database is successful, Django applies a series of migrations that create database tables to manage users, groups, permissions and sessions for a project. For the moment, don't worry too much about how these Django migrations work or where they are located -- I'll provide details later -- just be aware these migrations are needed by Django to provide some basic functionality.

Tip Connect directly to the database. If you receive an error trying to connect to the database or migrating the Django project to create the initial set of database tables, try to connect directly to the database using the same Django parameters. On many occasions a typo in the Django variables NAME, USER, PASSWORD, HOST or PORT can cause the process to fail or inclusively the credentials aren't even valid to connect directly to the database.

  1. https://github.com/cockroachdb/django-cockroachdb    ↑

  2. https://github.com/maxirobaina/django-firebird    ↑

  3. https://github.com/googleapis/python-spanner-django    ↑

  4. https://github.com/ibmdb/python-ibmdb-django    ↑

  5. https://github.com/microsoft/mssql-django    ↑

  6. https://github.com/sqlanywhere/sqlany-django    ↑