Pirate Wiki

Toggle Theme

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:

OptionDescription
ENGINESpecifies the database backend. Built-in options:
django.db.backends.sqlite3
django.db.backends.postgresql
django.db.backends.mysql
django.db.backends.oracle
HOSTHostname 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.
PORTPort to connect to. An empty string uses the default port. Not used with SQLite.
NAMEName of the database. For SQLite, this is the full path to the database file.
USERUsername for connecting to the database. Not used with SQLite.
PASSWORDPassword for connecting to the database. Not used with SQLite.
ATOMIC_REQUESTSSet this to True to wrap each view in a transaction for this database. Default is False.
AUTOCOMMITSet this to False to disable Django’s autocommit behavior. Default is True.
CONN_MAX_AGEMaximum 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_CHECKSSet 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_ZONETimezone for the database connection. This accepts same values as that of general TIME_ZONE setting, which is covered in detail later.
OPTIONSAdditional 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 to 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 (Ex: 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",
    },
}

Automatic Routing ​

The most efficient way to handle multiple databases is with Database Routers. A router is a Python class that directs database operations based on the model or app being used.

Create the Router Class ​

This example router directs all read and write operations for the analytics_app to the analytics database configured above:

python
# routers.py

class AnalyticsRouter:
    """
    A router to control all database operations on models in the
    analytics application.
    """
    route_app_labels = {'analytics_app'}

    def db_for_read(self, model, **hints):
        """
        Attempts to read analytics_app models go to analytics db.
        """
        if model._meta.app_label in self.route_app_labels:
            return 'analytics'
        return None

    def db_for_write(self, model, **hints):
        """
        Attempts to write analytics_app models go to analytics db.
        """
        if model._meta.app_label in self.route_app_labels:
            return 'analytics'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the analytics_app is involved.
        """
        if (
            obj1._meta.app_label in self.route_app_labels or
            obj2._meta.app_label in self.route_app_labels
        ):
           return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        Make sure the analytics_app only appears in the 'analytics'
        database.
        """
        if app_label in self.route_app_labels:
            return db == 'analytics'
        return None

Configure the Settings ​

You configure these classes in the DATABASE_ROUTERS setting:

python
# settings.py

DATABASE_ROUTERS = ['myproject.routers.AnalyticsRouter']
  • Django checks the first router in DATABASE_ROUTERS.
  • If the router returns a database name (Ex: 'analytics'), that database is used.
  • If the router returns None, Django checks the next router in the list.
  • If all routers return None (or if no routers are defined), Django automatically uses the default database.

Manual Selection ​

You can manually select a database for a specific operation if you do not want to rely on routers. To target a specific database, use 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')
# Deleting an object
User(name="Alice").delete(using='analytics')

Migrations ​

The migrate command operates on one database at a time. By default, it runs on the "default" database. To migrate a secondary database, you must explicitly use the --database=alias flag in your command line.

Admin Interface ​

By default, the Django admin interface follows your Database Routers. If your router directs a model to a specific database, the Admin will automatically use that database for listing and saving data.

However, the Admin has no explicit UI for switching between databases. You will need to write a custom ModelAdmin class if you need to:

  • Bypass the router: Force the Admin to use a specific database (Ex: viewing a specific legacy DB that isn't the primary routing target).
  • Fix Relations: Ensure Foreign Key dropdowns query the correct database. Without this, the Admin may incorrectly fallback to querying the default database for related objects, resulting in empty dropdowns if the data actually lives in a secondary database.

This example creates a base ModelAdmin class that routes all read, save, and delete operations to the analytics database:

python
from django.contrib import admin

class AnalyticsDBModelAdmin(admin.ModelAdmin):
    # A handy constant for the name of the alternate database.
    using = 'analytics'

    def save_model(self, request, obj, form, change):
        # Tell Django to save objects to the 'analytics' database.
        obj.save(using=self.using)

    def delete_model(self, request, obj):
        # Tell Django to delete objects from the 'analytics' database.
        obj.delete(using=self.using)

    def get_queryset(self, request):
        # Tell Django to look for objects on the 'analytics' database.
        return super().get_queryset(request).using(self.using)

    def formfield_for_foreignkey(self, db_field, request, **kwargs):
        # Tell Django to populate ForeignKey widgets using a query
        # on the 'analytics' database.
        return super().formfield_for_foreignkey(
            db_field, request, using=self.using, **kwargs
        )

    def formfield_for_manytomany(self, db_field, request, **kwargs):
        # Tell Django to populate ManyToMany widgets using a query
        # on the 'analytics' database.
        return super().formfield_for_manytomany(
            db_field, request, using=self.using, **kwargs
        )

Inherit from this custom class when registering your models:

python
# admin.py
from .models import LogEntry

@admin.register(LogEntry)
class LogEntryAdmin(AnalyticsDBModelAdmin):
    list_display = ['timestamp', 'action']

Limitations ​

Django does not support cross-database relationships. You cannot create a Foreign Key or Many-to-Many field that links a model in one database to a model in another. This restriction ensures data integrity, as databases cannot validate constraints across different connections.

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 outlines how to configure your database correctly.

To keep this book concise, only PostgreSQL is covered here. For other databases, please refer the provided official links.

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 (Ex: 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

SQLite ​

Refer this official doc.

MySQL ​

Refer this official doc.

MariaDB ​

Refer this official doc.

Oracle ​

Refer this official doc.