app/db/session.py

from __future__ import annotations

from collections.abc import AsyncIterator
from functools import lru_cache

from sqlalchemy.ext.asyncio import (
    AsyncEngine,
    AsyncSession,
    async_sessionmaker,
    create_async_engine,
)

from app.core.config import get_settings


@lru_cache(maxsize=1)
def get_engine() -> AsyncEngine:
    """Process-wide async SQLAlchemy engine.

    ``pool_pre_ping`` checks a pooled connection is still alive before handing it out and
    transparently reconnects if not — needed for serverless Postgres (Neon) that suspends
    and drops idle connections. Harmless for local SQLite.
    """
    return create_async_engine(
        get_settings().database_url, echo=False, future=True, pool_pre_ping=True
    )


@lru_cache(maxsize=1)
def get_session_factory() -> async_sessionmaker[AsyncSession]:
    return async_sessionmaker(get_engine(), expire_on_commit=False, class_=AsyncSession)


async def get_session() -> AsyncIterator[AsyncSession]:
    """Yield a single AsyncSession; rollback on error, commit responsibility is on caller."""
    factory = get_session_factory()
    async with factory() as session:
        try:
            yield session
        except Exception:
            await session.rollback()
            raise