Supported Databases

Django provides built-in support for several relational database management systems (RDBMS). It includes backends that handle the communication between Django’s ORM and the underlying database.

Officially Supported

WARNING

Django uses SQLite by default, but it’s meant for testing and development. Avoid using it in production, because it doesn't scale well.

Django officially supports the following databases:

SQLite (default)
PostgreSQL
MySQL
MariaDB
Oracle

Although database backends differ, Django attempts to provide consistent support for as many features as possible across all of them.

Third-Party Database Backends

In addition to the officially supported databases, there are backends provided by 3rd parties that allow you to use other databases with Django:

Note that the Django versions and ORM features supported by these unofficial backends vary considerably.

Settings

The databases can be configured using DATABASES dictionary in your project's settings.py file.

Options

NOTE

MariaDB is largely compatible with MySQL, so Django uses the MySQL backend to connect to it, eliminating the need for a separate MariaDB backend.

Here are some commonly used options for configuring databases in Django:

Option	Description
ENGINE	Specifies the database backend. Built-in options: `django.db.backends.sqlite3` `django.db.backends.postgresql` `django.db.backends.mysql` `django.db.backends.oracle`
HOST	Hostname or IP address of the database server. An empty string uses `localhost`. Not used with SQLite. - For MySQL backend, if this starts with a forward slash `/`, Django connects via a Unix socket instead of TCP. - For PostgreSQL backend, by default (empty `HOST`), Django connects using a Unix domain socket (‘local’ lines in `pg_hba.conf`). - The socket location is usually standard, but if it’s custom, use the `unix_socket_directory` path from `postgresql.conf`.
PORT	Port to connect to. An empty string uses the default port. Not used with SQLite.
NAME	Name of the database. For SQLite, this is the full path to the database file.
USER	Username for connecting to the database. Not used with SQLite.
PASSWORD	Password for connecting to the database. Not used with SQLite.
ATOMIC_REQUESTS	Set this to `True` to wrap each view in a transaction for this database. Default is `False`.
AUTOCOMMIT	Set this to `False` to disable Django’s autocommit behavior. Default is `True`.
CONN_MAX_AGE	Maximum lifetime of a database connection. Available options: - `0` - Close the connection at the end of each request (default). - Positive integer - Keep connection open for that many seconds. - `None` - Keep connections open indefinitely.
CONN_HEALTH_CHECKS	Set this to `True` to perform health checks on persistent database connections before they are reused. If the health check fails, the database connection will be reestablished without failing the request. Default is `False`.
TIME_ZONE	Timezone for the database connection. This accepts same values as that of general `TIME_ZONE` setting, which is covered in detail later.
OPTIONS	Additional backend-specific parameters. The available parameters will vary depending on the database backend. Default is `{}`.

For a complete list of all database options, refer this official doc.

Design Decisions

Some important design choices in Django’s database handling include:

Persistent Connections

Persistent connections avoid the overhead of reconnecting to the database in each HTTP request. As mentioned above, the CONN_MAX_AGE parameter defines the maximum lifetime of a connection.

Django opens a connection to the database when it first makes a database query. It keeps this connection open and reuses it until:

The connection exceeds CONN_MAX_AGE.
The database closes it.
The connection becomes unusable due to an error.

If your database terminates idle connections after some time, use a lower CONN_MAX_AGE value to prevent Django from using a terminated connection.

Caveats

Since each thread maintains its own connection, your database must support at least as many simultaneous connections as you have worker threads defined in your web server.
If DB access is rare, set CONN_MAX_AGE low/0 to avoid wasting connections.
The development server creates a new thread for each request it handles, negating the effect of persistent connections. Therefore, avoid enabling persistent connections in development.
When Django establishes a connection to the database, it sets up appropriate parameters, depending on the backend being used. If you enable persistent connections, this setup is no longer repeated every request. If you change connection parameters (e.g., isolation level or time zone), either reset them after each request, enforce them at the start of each request, or disable persistent connections.
If a connection is created in a long-running process, outside of Django’s request-response cycle, the connection will remain open until explicitly closed, or timeout occurs. You can use django.db.close_old_connections() to close all old or unusable connections.

Encoding

Django assumes that all databases use UTF-8 encoding. Using a different encoding can cause unexpected issues, such as “value too long” errors for data that is otherwise valid in Django.

Multiple Databases

Django can be configured to use multiple databases, however, the DATABASES setting must configure a default database. The default database will be used for all operations unless another database is explicitly specified.

python

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "postgres_db",
        "USER": "postgres_user",
        "PASSWORD": "postgres_pwd",
        "HOST": "localhost",
        "PORT": "5432",
    },
    "analytics": {
        "ENGINE": "django.db.backends.mysql",
        "NAME": "mysql_db",
        "USER": "mysql_user",
        "PASSWORD": "mysql_pwd",
        "HOST": "localhost",
        "PORT": "3306",
    },
}

TODO: Migrations and Database Routers, basically this link: https://docs.djangoproject.com/en/5.2/topics/db/multi-db/

You can target a specific database by using following approaches:

python

# Using the 'default' database
# Query objects
users = User.objects.all()
# Saving an object
User(name="Alice").save()

# Using the 'analytics' database
# Query objects
users = User.objects.using('analytics').all()
# Saving an object
User(name="Alice").save(using='analytics')

Modify Database Backend

You can modify built-in database backends to change its behavior, features, or configuration.

First create a new directory with a base module in it:

markdown

my_site/
├── ...
└── my_db_engine/
    ├── __init__.py
    └── base.py

The base.py must contain a class named DatabaseWrapper that subclasses an existing engine. Here is an example that subclasses the PostgreSQL engine to change a feature class allows_group_by_selected_pks_on_model:

python

from django.db.backends.postgresql import base, features

class DatabaseFeatures(features.DatabaseFeatures):
    def allows_group_by_selected_pks_on_model(self, model):
        return True

class DatabaseWrapper(base.DatabaseWrapper):
    features_class = DatabaseFeatures

Finally, use the custom engine in settings.py file:

python

DATABASES = {
    "default": {
        "ENGINE": "my_db_engine",
        # ...
    },
}

Database-Specific Notes

This section provides information on how to set up your database correctly.

SQLite

Django supports SQLite and it's an excellent development alternative for applications that are predominantly read-only or require a smaller installation footprint.

These are some key limitations and behaviors of SQLite backend:

String Matching
- contains is always case-insensitive (Ex: "aa" matches "Aabb").
- For non-ASCII characters, iexact behaves like exact (case-sensitive).
Decimals
- SQLite has no real decimal type. They are internally stored as floats (REAL), so rounding may be inaccurate.
JSON Field
- To use JSONField on SQLite, you'll need to enable JSON1 extension.
Database Locked Errors
- SQLite is meant to be a lightweight database, and thus can’t support a high level of concurrency. This error means that one thread or process has an exclusive lock on the database connection and another thread timed out waiting for the lock the be released.
- There are certain ways to address this:
  - Increase timeout in OPTIONS.
  - Shorten transactions and reduce concurrency.
  - Switch to another database backend (Ex: Postgres, MySQL).
Transactions
- SQLite supports three transaction modes: DEFERRED (default), IMMEDIATE, and EXCLUSIVE.
- DEFERRED mode gets a shared lock only while reading, and a reserved lock only while writing data.
- IMMEDIATE mode gets a reserved lock immediately when the transaction starts, preventing other writers from starting transactions while still allowing reads.
- EXCLUSIVE mode gets an exclusive lock immediately when the transaction starts, preventing other readers and writers from accessing the database. This is bad for concurrency.
- For the best performance with IMMEDIATE and EXCLUSIVE, transactions should be short and ATOMIC_REQUESTS shouldn't be enabled.
Unsupported Features
- SQLite doesn't support SELECT ... FOR UPDATE syntax and calling it has no effect.
- In SQLite, if you're looping over rows in a query and modify the same table at the same time, you may end up with inconsistent results.
PRAGMA Options
- PRAGMAs in SQLite are like configuration switches or special commands that tweak how the database behaves.
- You can set SQLite PRAGMA settings at connection time via init_command option.
  - The example code shown below controls durability (synchronous) and memory (cache_size).

NOTE

The JSON1 extension is enabled by default on SQLite 3.38+.

SQLite OPTIONS Example:

python

"OPTIONS": {
    # ...
    "timeout": 20,
    "transaction_mode": "IMMEDIATE",
    "init_command": "PRAGMA synchronous=3; PRAGMA cache_size=2000;",
    # ...
}

PostgreSQL

NOTE

Django supports PostgreSQL 14 and above and works with both psycopg and psycopg2 libraries, though support for psycopg2 may be removed in the future.

Connection Settings
- You can connect to postgres using a service name from a service file (.pg_service.conf) and a password file (.pgpass), using the service and passfile parameters under OPTIONS respectively.
Optimized Configuration
- Django expects following parameters for database connections. If these are already configured in PostgreSQL instance (using postgresql.conf), Django won't set them for every connection and that improves performance slightly.
  - client_encoding='UTF8'
  - isolation_level='READ COMMITTED' (default)
  - timezone=UTC (when USE_TZ is True)
Advanced Settings
- Isolation Level can be changed using isolation_level option.
- You can switch to a different role for database connections, using assume_role option.
- To use a connection pool with psycopg, you can use pool option. It can accepts a dict to be passed to ConnectionPool or True to accept defaults.
- If you want to enable server-side binding (to send the query and parameters separately to the server), use server_side_binding option.
Indexes & Extensions
- When you use db_index=True on your model field, Django typically outputs a single CREATE INDEX statement. However, if the field type is either varchar/text, Django creates an extra index using appropriate PostgreSQL operator class, to perform lookups that use LIKE operator in SQL.
Server-side Cursors
- Django uses server-side cursors, to stream query results row by row, instead of loading them all into memory.
- When using a connection pooler in transaction pooling mode (e.g., PgBouncer), server-side cursors must be disabled. You can do this by setting DISABLE_SERVER_SIDE_CURSORS parameter under DATABASES setting.
- Alternatively, you can wrap queries in an atomic() block, which temporarily disables autocommit. This ensures the server-side cursor lives only for the duration of that transaction, avoiding connection reuse issues.
Auto-increment PKs
- Django uses PostgreSQL's identity columns to store auto-incrementing primary keys. The values of this identity column are populated using a sequence that keeps track of the next available value. Manually adding a value to this field, won't auto-update that sequence, which will lead to conflict later.
- To address this issue, use the sqlsequencereset management command.
Testing
- Use TEST['TEMPLATE'] to pick a template for creating test database.
- You can speed up tests by using non-durable settings in PostgreSQL. But note that this is dangerous and should be done only for local development.

PostgreSQL OPTIONS Example:

python

from django.db.backends.postgresql.psycopg_any import IsolationLevel

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "DISABLE_SERVER_SIDE_CURSORS": True,
        "OPTIONS": {
            "service": "my_service",
            "passfile": ".my_pgpass",
            "isolation_level": IsolationLevel.SERIALIZABLE,
            "assume_role": "my_application_role",
            "pool": True,
            "server_side_binding": True
        },
    }
}

Here is a sample service file:

toml

[my_service]
host=localhost
user=USER
dbname=NAME
port=5432

Here is a sample passfile:

txt

localhost:5432:NAME:USER:PASSWORD

Supported Databases ​

Officially Supported ​

Third-Party Database Backends ​

Settings ​

Options ​

Design Decisions ​

Persistent Connections ​

Caveats ​

Encoding ​

Multiple Databases ​

Modify Database Backend ​

Database-Specific Notes ​

SQLite ​

PostgreSQL ​

MySQL / MariaDB ​

Oracle ​

Supported Databases

Officially Supported

Third-Party Database Backends

Settings

Options

Design Decisions

Persistent Connections

Caveats

Encoding

Multiple Databases

Modify Database Backend

Database-Specific Notes

SQLite

PostgreSQL

MySQL / MariaDB

Oracle