Django DB Connection Pool

Common errors

• OperationalError: (2006, 'MySQL server has gone away')

  cause The database connection was closed by the server due to inactivity (idle timeout) or a network issue before `django-db-connection-pool` could recycle it, and `RECYCLE` or `TIMEOUT` options are not adequately configured.
  fix Increase the `RECYCLE` value (e.g., `RECYCLE: 3600` for 1 hour, which should be less than the database's idle timeout) in `POOL_OPTIONS` to ensure connections are refreshed proactively. Also, ensure `TIMEOUT` is set appropriately to prevent long waits for unavailable connections.

• django.db.utils.OperationalError: FATAL: sorry, too many clients already

  cause The total number of active database connections (across all worker processes and their respective pools) has exceeded the database server's configured maximum connection limit.
  fix Review and adjust `POOL_SIZE` and `MAX_OVERFLOW` in `POOL_OPTIONS` to better match your database server's `max_connections` and the number of application worker processes. Alternatively, consider an external pooler like PgBouncer for more centralized connection management, especially in highly concurrent environments.

• ImproperlyConfigured: Pooling doesn't support persistent connections

  cause This error occurs when Django's native connection pooling (introduced in Django 5.1 for PostgreSQL) is enabled with `CONN_MAX_AGE > 0`, or it's a general indicator of conflicting persistent connection settings when a pooling backend is used. Although not directly from `django-db-connection-pool`, it highlights the incompatibility.
  fix Set `CONN_MAX_AGE = 0` (or `None`) in your `DATABASES` settings when using `django-db-connection-pool` or any connection pooling solution. The pool itself manages connection persistence.

Warnings

• breaking Django 5.1+ includes native connection pooling for PostgreSQL using `psycopg`. Using `django-db-connection-pool` alongside or instead of the native solution for PostgreSQL on Django 5.1+ might lead to unexpected behavior, performance issues, or redundancy. Assess whether the native pooling meets your needs before opting for this third-party library for PostgreSQL with Django 5.1 and above.
  Fix: For PostgreSQL on Django 5.1+, consider using Django's native pooling by setting `DATABASES['default']['OPTIONS']['pool'] = True` or a dictionary of `psycopg_pool.ConnectionPool` options. If using `django-db-connection-pool`, ensure `CONN_MAX_AGE` is 0 and test thoroughly for conflicts.
• gotcha In multiprocessing environments (e.g., uWSGI, Gunicorn with multiple workers), each process will instantiate its own independent connection pool. This means the total number of connections to your database can be `number_of_workers * (pool_size + max_overflow)`, which might exceed your database's connection limits if not properly configured.
  Fix: Carefully calculate and configure `POOL_SIZE` and `MAX_OVERFLOW` in `POOL_OPTIONS` considering the number of worker processes your application uses to avoid exceeding database connection limits.
• gotcha Setting Django's `CONN_MAX_AGE` to a positive value will conflict with `django-db-connection-pool`'s connection management, as `CONN_MAX_AGE` implements Django's own form of persistent connections. This can lead to connections being closed or recycled prematurely by Django, or the pool failing to manage connections effectively.
  Fix: Always set `CONN_MAX_AGE` to `0` or `None` in your `DATABASES` settings when using `django-db-connection-pool`. Let the pooling library handle the full lifecycle and recycling of connections.
• gotcha Improper transaction management (e.g., not calling `commit()` or `rollback()` after a transaction) can lead to 'stale' connections being returned to the pool with an open transaction. While SQLAlchemy's pool aims to reset connections, applications should still ensure proper transaction hygiene.
  Fix: Always ensure that database transactions are explicitly committed or rolled back within your application logic. Use `with transaction.atomic():` blocks or equivalent patterns to ensure transaction integrity.
• gotcha For JDBC backend support, you must have a Java Runtime Environment (JRE) installed and correctly configure `JAVA_HOME` and `CLASSPATH` environment variables to include your JDBC driver JAR files. Without this, the JDBC backend will fail to connect.
  Fix: Install a JRE and set `export JAVA_HOME=/path/to/jre; export CLASSPATH=/path/to/jdbc_driver.jar` in your environment before running Django.

Install

• pip install django-db-connection-pool Basic Installation
• pip install django-db-connection-pool[all] Install with all database engines
• pip install django-db-connection-pool[mysql,postgresql] Install with specific engines (e.g., MySQL, PostgreSQL)

Imports

• MySQL Connection Pool
  wrong:

  DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql' } }

  correct:

  DATABASES = { 'default': { 'ENGINE': 'dj_db_conn_pool.backends.mysql' } }

  The 'ENGINE' path must be changed to the pooling backend provided by dj_db_conn_pool.
• PostgreSQL Connection Pool
  wrong:

  DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql' } }

  correct:

  DATABASES = { 'default': { 'ENGINE': 'dj_db_conn_pool.backends.postgresql' } }

  Ensure to replace the default Django PostgreSQL engine with the pooling variant.
• Oracle Connection Pool
  wrong:

  DATABASES = { 'default': { 'ENGINE': 'django.db.backends.oracle' } }

  correct:

  DATABASES = { 'default': { 'ENGINE': 'dj_db_conn_pool.backends.oracle' } }

  Similar to other backends, the Oracle engine path needs to be updated.
• JDBC Oracle Connection Pool

  DATABASES = { 'default': { 'ENGINE': 'dj_db_conn_pool.backends.jdbc.oracle' } }

  Specific backend for JDBC Oracle connections.
• dj_db_conn_pool.setup

  import dj_db_conn_pool
  dj_db_conn_pool.setup(pool_size=100, max_overflow=50)

  Used to change default pool arguments globally before any pool is created.

Quickstart

To quickly integrate `django-db-connection-pool`, update your `DATABASES` setting in `settings.py` by changing the `ENGINE` to the appropriate `dj_db_conn_pool.backends` path. It is crucial to set `CONN_MAX_AGE` to `0` or `None` to prevent Django's built-in persistent connection logic from conflicting with the connection pool. You can further customize pooling behavior using the `POOL_OPTIONS` dictionary, which accepts parameters like `POOL_SIZE`, `MAX_OVERFLOW`, `RECYCLE`, and `TIMEOUT`.

import os

# settings.py

DATABASES = {
    'default': {
        'ENGINE': 'dj_db_conn_pool.backends.postgresql',
        'NAME': os.environ.get('DB_NAME', 'mydatabase'),
        'USER': os.environ.get('DB_USER', 'myuser'),
        'PASSWORD': os.environ.get('DB_PASSWORD', 'mypassword'),
        'HOST': os.environ.get('DB_HOST', 'localhost'),
        'PORT': os.environ.get('DB_PORT', '5432'),
        # Important: Set CONN_MAX_AGE to 0 or None when using an external pooler to avoid conflicts
        'CONN_MAX_AGE': 0,
        'POOL_OPTIONS': {
            'POOL_SIZE': 10,
            'MAX_OVERFLOW': 5,
            'RECYCLE': 3600, # Recycle connections after 1 hour
            'TIMEOUT': 30 # Connection checkout timeout
        }
    }
}

# Example of using a connection (standard Django ORM operations apply)
# from django.db import connections
# from myapp.models import MyModel
#
# try:
#    obj = MyModel.objects.first()
#    print(obj)
# except Exception as e:
#    print(f"Database error: {e}")