<commit_before>=========
Databases
=========

Django officially supports the following databases:

* :ref:`PostgreSQL <postgresql-notes>`
* :ref:`MariaDB <mariadb-notes>`
* :ref:`MySQL <mysql-notes>`
* :ref:`Oracle <oracle-notes>`
* :ref:`SQLite <sqlite-notes>`

There are also a number of :ref:`database backends provided by third parties
<third-party-notes>`.

Django attempts to support as many features as possible on all database
backends. However, not all database backends are alike, and we've had to make
design decisions on which features to support and which assumptions we can make
safely.

This file describes some of the features that might be relevant to Django
usage. It is not intended as a replacement for server-specific documentation or
reference manuals.

General notes
=============

.. _persistent-database-connections:

Persistent connections
----------------------

Persistent connections avoid the overhead of reestablishing a connection to
the database in each request. They're controlled by the
:setting:`CONN_MAX_AGE` parameter which defines the maximum lifetime of a
connection. It can be set independently for each database.

The default value is ``0``, preserving the historical behavior of closing the
database connection at the end of each request. To enable persistent
connections, set :setting:`CONN_MAX_AGE` to a positive integer of seconds. For
unlimited persistent connections, set it to ``None``.

Connection management
~~~~~~~~~~~~~~~~~~~~~

Django opens a connection to the database when it first makes a database
query. It keeps this connection open and reuses it in subsequent requests.
Django closes the connection once it exceeds the maximum age defined by
:setting:`CONN_MAX_AGE` or when it isn't usable any longer.

In detail, Django automatically opens a connection to the database whenever it
needs one and doesn't have one already — either because this is the first
connection, or because the previous connection was closed.

At the beginning of each request, Django closes the connection if it has
reached its maximum age. If your database terminates idle connections after
some time, you should set :setting:`CONN_MAX_AGE` to a lower value, so that
Django doesn't attempt to use a connection that has been terminated by the
database server. (This problem may only affect very low traffic sites.)

At the end of each request, Django closes the connection if it has reached its
maximum age or if it is in an unrecoverable error state. If any database
errors have occurred while processing the requests, Django checks whether the
connection still works, and closes it if it doesn't. Thus, database errors
affect at most one request per each application's worker thread; if the
connection becomes unusable, the next request gets a fresh connection.

Setting :setting:`CONN_HEALTH_CHECKS` to ``True`` can be used to improve the
robustness of connection reuse and prevent errors when a connection has been
closed by the database server which is now ready to accept and serve new
connections, e.g. after database server restart. The health check is performed
only once per request and only if the database is being accessed during the
handling of the request.

.. versionchanged:: 4.1

    The :setting:`CONN_HEALTH_CHECKS` setting was added.

Caveats
~~~~~~~

Since each thread maintains its own connection, your database must support at
least as many simultaneous connections as you have worker threads.

Sometimes a database won't be accessed by the majority of your views, for
example because it's the database of an external system, or thanks to caching.
In such cases, you should set :setting:`CONN_MAX_AGE` to a low value or even
``0``, because it doesn't make sense to maintain a connection that's unlikely
to be reused. This will help keep the number of simultaneous connections to
this database small.

The development server creates a new thread for each request it handles,
negating the effect of persistent connections. Don't enable them during
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 modify
parameters such as the connection's isolation level or time zone, you should
either restore Django's defaults at the end of each request, force an
appropriate value at the beginning of each request, or disable persistent
connections.

Encoding
--------

Django assumes that all databases use UTF-8 encoding. Using other encodings may
result in unexpected behavior such as "value too long" errors from your
database for data that is valid in Django. See the database specific notes
below for information on how to set up your database correctly.

.. _postgresql-notes:

PostgreSQL notes
================

Django supports PostgreSQL 11 and higher. `psycopg2`_ 2.8.4 or higher is
required, though the latest release is recommended.

.. _psycopg2: https://www.psycopg.org/

.. _postgresql-connection-settings:

PostgreSQL connection settings
-------------------------------

See :setting:`HOST` for details.

To connect using a service name from the `connection service file`_ and a
password from the `password file`_, you must specify them in the
:setting:`OPTIONS` part of your database configuration in :setting:`DATABASES`:

.. code-block:: python
    :caption: settings.py

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.postgresql',
            'OPTIONS': {
                'service': 'my_service',
                'passfile': '.my_pgpass',
            },
        }
    }

.. code-block:: text
    :caption: .pg_service.conf

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

.. code-block:: text
    :caption: .my_pgpass

    localhost:5432:NAME:USER:PASSWORD

.. _connection service file: https://www.postgresql.org/docs/current/libpq-pgservice.html
.. _password file: https://www.postgresql.org/docs/current/libpq-pgpass.html

.. versionchanged:: 4.0

    Support for connecting by a service name, and specifying a password file
    was added.

Optimizing PostgreSQL's configuration
-------------------------------------

Django needs the following parameters for its database connections:

- ``client_encoding``: ``'UTF8'``,
- ``default_transaction_isolation``: ``'read committed'`` by default,
  or the value set in the connection options (see below),
- ``timezone``:
    - when :setting:`USE_TZ` is ``True``, ``'UTC'`` by default, or the
      :setting:`TIME_ZONE <DATABASE-TIME_ZONE>` value set for the connection,
    - when :setting:`USE_TZ` is ``False``, the value of the global
      :setting:`TIME_ZONE` setting.
<commit_msg>Refs #33685 -- Doc'd that using PostgreSQL's service names for testing purposes is not supported.<commit_after>=========
Databases
=========

Django officially supports the following databases:

* :ref:`PostgreSQL <postgresql-notes>`
* :ref:`MariaDB <mariadb-notes>`
* :ref:`MySQL <mysql-notes>`
* :ref:`Oracle <oracle-notes>`
* :ref:`SQLite <sqlite-notes>`

There are also a number of :ref:`database backends provided by third parties
<third-party-notes>`.

Django attempts to support as many features as possible on all database
backends. However, not all database backends are alike, and we've had to make
design decisions on which features to support and which assumptions we can make
safely.

This file describes some of the features that might be relevant to Django
usage. It is not intended as a replacement for server-specific documentation or
reference manuals.

General notes
=============

.. _persistent-database-connections:

Persistent connections
----------------------

Persistent connections avoid the overhead of reestablishing a connection to
the database in each request. They're controlled by the
:setting:`CONN_MAX_AGE` parameter which defines the maximum lifetime of a
connection. It can be set independently for each database.

The default value is ``0``, preserving the historical behavior of closing the
database connection at the end of each request. To enable persistent
connections, set :setting:`CONN_MAX_AGE` to a positive integer of seconds. For
unlimited persistent connections, set it to ``None``.

Connection management
~~~~~~~~~~~~~~~~~~~~~

Django opens a connection to the database when it first makes a database
query. It keeps this connection open and reuses it in subsequent requests.
Django closes the connection once it exceeds the maximum age defined by
:setting:`CONN_MAX_AGE` or when it isn't usable any longer.

In detail, Django automatically opens a connection to the database whenever it
needs one and doesn't have one already — either because this is the first
connection, or because the previous connection was closed.

At the beginning of each request, Django closes the connection if it has
reached its maximum age. If your database terminates idle connections after
some time, you should set :setting:`CONN_MAX_AGE` to a lower value, so that
Django doesn't attempt to use a connection that has been terminated by the
database server. (This problem may only affect very low traffic sites.)

At the end of each request, Django closes the connection if it has reached its
maximum age or if it is in an unrecoverable error state. If any database
errors have occurred while processing the requests, Django checks whether the
connection still works, and closes it if it doesn't. Thus, database errors
affect at most one request per each application's worker thread; if the
connection becomes unusable, the next request gets a fresh connection.

Setting :setting:`CONN_HEALTH_CHECKS` to ``True`` can be used to improve the
robustness of connection reuse and prevent errors when a connection has been
closed by the database server which is now ready to accept and serve new
connections, e.g. after database server restart. The health check is performed
only once per request and only if the database is being accessed during the
handling of the request.

.. versionchanged:: 4.1

    The :setting:`CONN_HEALTH_CHECKS` setting was added.

Caveats
~~~~~~~

Since each thread maintains its own connection, your database must support at
least as many simultaneous connections as you have worker threads.

Sometimes a database won't be accessed by the majority of your views, for
example because it's the database of an external system, or thanks to caching.
In such cases, you should set :setting:`CONN_MAX_AGE` to a low value or even
``0``, because it doesn't make sense to maintain a connection that's unlikely
to be reused. This will help keep the number of simultaneous connections to
this database small.

The development server creates a new thread for each request it handles,
negating the effect of persistent connections. Don't enable them during
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 modify
parameters such as the connection's isolation level or time zone, you should
either restore Django's defaults at the end of each request, force an
appropriate value at the beginning of each request, or disable persistent
connections.

Encoding
--------

Django assumes that all databases use UTF-8 encoding. Using other encodings may
result in unexpected behavior such as "value too long" errors from your
database for data that is valid in Django. See the database specific notes
below for information on how to set up your database correctly.

.. _postgresql-notes:

PostgreSQL notes
================

Django supports PostgreSQL 11 and higher. `psycopg2`_ 2.8.4 or higher is
required, though the latest release is recommended.

.. _psycopg2: https://www.psycopg.org/

.. _postgresql-connection-settings:

PostgreSQL connection settings
-------------------------------

See :setting:`HOST` for details.

To connect using a service name from the `connection service file`_ and a
password from the `password file`_, you must specify them in the
:setting:`OPTIONS` part of your database configuration in :setting:`DATABASES`:

.. code-block:: python
    :caption: settings.py

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.postgresql',
            'OPTIONS': {
                'service': 'my_service',
                'passfile': <PASSWORD>',
            },
        }
    }

.. code-block:: text
    :caption: .pg_service.conf

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

.. code-block:: text
    :caption: .my_pgpass

    localhost:5432:NAME:USER:PASSWORD

.. _connection service file: https://www.postgresql.org/docs/current/libpq-pgservice.html
.. _password file: https://www.postgresql.org/docs/current/libpq-pgpass.html

.. versionchanged:: 4.0

    Support for connecting by a service name, and specifying a password file
    was added.

.. warning::

    Using a service name for testing purposes is not supported. This
    :ticket:`may be implemented later <33685>`.

Optimizing PostgreSQL's configuration
-------------------------------------

Django needs the following parameters for its database connections:

- ``client_encoding``: ``'UTF8'``,
- ``default_transaction_isolation``: ``'read committed'`` by default,
  or the value set in the connection options (see below),
- ``timezone``:
    - when :setting:`USE_TZ` is ``True``, ``'UTC'`` by default, or the
      :setting:`TIME_ZONE <DATABASE-TIME_ZONE>` value set for the connection,
    - when :setting:`USE_TZ` is ``False``, the value of the global
      :setting:`TIME_ZONE` setting.

{'fraction_non_alphanumeric': 0.08767962652272229, 'fraction_numerical': 0.004084907724852287, 'mean_word_length': 4.046006624953994, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 19, 'https://': 6, 'lorem ipsum': 0, 'www.': 6, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4474447', 'n_tokens_mistral': 3603, 'n_tokens_neox': 3405, 'n_words': 1768}