src/database_connection.py

import psycopg2
from psycopg2 import pool
from dotenv import load_dotenv
import os

load_dotenv()

class DatabaseConnection:
    def __init__(self):
        self.pool = None

    def connect(self):
        """Establish connection pool"""
        try:
            if not self.pool:
                self.pool = psycopg2.pool.SimpleConnectionPool(
                    minconn=1,
                    maxconn=5,  # Adjust max connections
                    user=os.getenv("DB_USER"),
                    password=os.getenv("DB_PASSWORD"),
                    host=os.getenv("DB_HOST"),
                    port=os.getenv("DB_PORT"),
                    database=os.getenv("DB_NAME")
                )
                print("✅ Connection pool established.")
        except Exception as e:
            print(f"❌ Error establishing connection pool: {e}")

    def get_connection(self):
        """Get connection from the pool"""
        if self.pool:
            return self.pool.getconn()
        else:
            raise Exception("Connection pool not established")

    def release_connection(self, conn):
        """Release connection back to the pool"""
        if self.pool and conn:
            self.pool.putconn(conn)

    def close_pool(self):
        """Close the connection pool"""
        if self.pool:
            self.pool.closeall()