app/database.py

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.pool import AsyncAdaptedQueuePool
from app.config import settings


class Base(DeclarativeBase):
    pass


# Use connection pooling for faster responses
# Direct connection with AsyncAdaptedQueuePool keeps connections open and reuses them
engine = create_async_engine(
    settings.DATABASE_URL.replace("+asyncpg://", "+psycopg://"),
    echo=False,
    future=True,
    poolclass=AsyncAdaptedQueuePool,
    pool_size=5,           # Keep 5 connections ready
    max_overflow=10,       # Allow up to 15 total (5 + 10)
    pool_timeout=30,       # Wait up to 30s for a connection
    pool_recycle=1800,     # Recycle connections every 30 minutes
    pool_pre_ping=True,    # Verify connections are alive before using
)

async_session_maker = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
)


async def get_db():
    async with async_session_maker() as session:
        yield session


async def create_tables():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)