Set up a database for a Django project

Problem

Every time you start and stop a Django application all its data is lost because you don't have a database to persist data for a later time. You want to take the first steps to persist data from a Django project to a database.

Solution

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 creates a SQLite database for your project.

In addition to SQLite, Django also has support to connect to other popular databases that include: PostgreSQL, MySQL and Oracle. The Django configuration to connect to a database is done inside the settting.py file of a Django project in the DATABASES variable.

How it works

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.

Listing 1 - Default Django DATABASES dictionary

# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
import os
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}
Note Use SQLite if you want the quickest database setup

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 a SQLite database is a flat file and Django creates the SQLite database based on the NAME variable value. In the case of listing 1, under a Django project's BASE_DIR and as a flat file named db.sqlite3.

If you use SQLite, you can skip to the last step 'Test Django database connection and build Django base tables'

The 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 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 for 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.

Table 1 - Django ENGINE value for different databases
DatabaseDjango ENGINE value
MySQLdjango.db.backends.mysql
Oracledjango.db.backends.oracle
PostgreSQLdjango.db.backends.postgresql_psycopg2
SQLitedjango.db.backends.sqlite3

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, for SQLite NAME will indicate the location of a flat file, where as for MySQL it will indicate the logical name of an instance.

The full set of Django database connection parameters is described in Table 2.

Table 2 - Django database conection parameters based on database brand
Django connection parameterDefault valueNotes
ATOMIC_REQUESTSFalseEnforces (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.
AUTOCOMMITTrueBy default set to True, because otherwise it would require explicit transactions to perform commits.
CONN_MAX_AGE0The 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 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.

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).
OPTIONS{} (Empty dictionary)Extra parameters to use when connecting to the database. Available parameters vary depending on database backend, consult the backend module's own documentation. For a list of backend modules see Table 1.
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.
USER'' (Empty string)The username to use when connecting to the database. Not used with SQLite.

Install Python database packages

Besides configuring Django to connect to a database, you'll also need to install the necessary Python packages to communicate with your database brand -- the only exception to this is SQLite which is included in the Python distribution.

Each database relies on different packages, but the installation process is straightforward with the pip package manager. If you don't have the pip executable on your system, see the 'Install Django' recipe, 'Install pip' section.

The Python packages for each database supported in Django in its 'out-of-the-box' state are enumerated in table 3. In addition, table 3 also includes the pip command to install each package.

Table 3 - Python packages for different databases
DatabasePython packagepip installation syntax
PostgreSQLpsycopg2pip install psycopg2
MySQLmysql-pythonpip install mysql-python
Oraclecx_Oraclepip install cx_Oracle
SQLiteIncluded with Python 2.5+N/A
Note Database development libraries

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

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-get task: apt-get install libmysqlclient-dev).

Test Django database connection and build Django base tables

Once you update the Django settings.py file with your database credentials, you can test 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'll do and 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 2.

Listing 2 - 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 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 sessions.0001_initial... OK

As illustrated in listing 2, 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.

Note Connect directly to the database

If you receive an error trying to connect to the database or migrating the Django project to create the base tables, try to connect directly to the database using the same Django parameters.

On many occasions a typo in the NAME, USER, PASSWORD, HOST and PORT Django variables can cause the process to fail or inclusively the credentials aren't even valid to connect directly to the database.