backend/app/core/db_pool.py

"""Database connection pooling — Redis + Postgres with auto-reconnect."""

import logging
import os

logger = logging.getLogger(__name__)

REDIS_URL = os.getenv("REDIS_URL", "redis://localhost:6379/0")
PG_URL = os.getenv("DATABASE_URL", "postgresql://postgres:postgres@localhost:5432/postgres")

_redis_pool = None
_pg_pool = None


def get_redis():
    """Get or create Redis connection pool. Auto-reconnects."""
    global _redis_pool
    if _redis_pool is None:
        import redis

        try:
            _redis_pool = redis.ConnectionPool.from_url(
                REDIS_URL, max_connections=20, retry_on_timeout=True, health_check_interval=30
            )
            logger.info("Redis pool created (max 20)")
        except Exception as e:
            logger.error(f"Redis pool failed: {e}")
            return None
    import redis

    return redis.Redis(connection_pool=_redis_pool)


def get_postgres():
    """Get or create Postgres connection pool."""
    global _pg_pool
    if _pg_pool is None:
        try:
            from psycopg2 import pool

            _pg_pool = pool.ThreadedConnectionPool(5, 20, PG_URL)
            logger.info("Postgres pool created (5-20)")
        except Exception as e:
            logger.error(f"Postgres pool failed: {e}")
            return None
    return _pg_pool.getconn()


def return_postgres(conn):
    """Return connection to pool."""
    if _pg_pool and conn:
        _pg_pool.putconn(conn)


def pool_stats() -> dict:
    """Get connection pool statistics."""
    return {
        "redis": {"pool_size": 20, "active": "unknown"} if _redis_pool else {"error": "no pool"},
        "postgres": {"min": 5, "max": 20} if _pg_pool else {"error": "no pool"},
    }