# Copyright (C) 2026 Mattia Zatelli # # This program is free software: you can redistribute it and/or modify # it under the terms of the GNU Affero General Public License as published by # the Free Software Foundation, either version 3 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU Affero General Public License for more details. # # You should have received a copy of the GNU Affero General Public License # along with this program. If not, see . """ Database configuration and session management This module configures SQLAlchemy for async connections to PostgreSQL with support for pgvector extension for semantic vector search. Manages connection pooling and provides dependency injection for FastAPI. """ import logging from typing import AsyncGenerator from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine from sqlalchemy.orm import DeclarativeBase from sqlalchemy.pool import NullPool from app.core.config import get_settings logger = logging.getLogger(__name__) settings = get_settings() # Global variables for engine and session maker # Initialized during application startup engine = None async_session_maker = None class Base(DeclarativeBase): """Base class for all database models All SQLAlchemy models must inherit from this class. Provides common configuration and naming conventions. """ pass async def init_db(): """Initialize the PostgreSQL database connection Configures the SQLAlchemy engine with: - Async connections via asyncpg - Connection pooling with pre-ping for connection health - Pool recycling every hour to avoid stale connections - Echo SQL queries in development for debugging """ global engine, async_session_maker logger.info(f"Database connection: {settings.POSTGRES_HOST}:{settings.POSTGRES_PORT}/{settings.POSTGRES_DB} (user: {settings.POSTGRES_USER})") # asyncpg accepts ssl='require'|'prefer'|'disable' or an SSLContext. # Pass it via connect_args because asyncpg does not parse the libpq # 'sslmode' query string. connect_args: dict = {} if settings.POSTGRES_SSL: connect_args["ssl"] = settings.POSTGRES_SSL # Create async engine with optimized configuration engine = create_async_engine( settings.DATABASE_URL, echo=settings.APP_ENV == "dev", # Log SQL only in development poolclass=NullPool if settings.APP_ENV == "test" else None, # No pooling for tests pool_pre_ping=True, # Check connection health before use pool_recycle=3600, # Recycle connections every hour connect_args=connect_args, ) # Configure async session maker async_session_maker = async_sessionmaker( engine, class_=AsyncSession, expire_on_commit=False # Keep objects usable after commit ) logger.info("✅ Database connection initialized") async def close_db(): """Close database connection""" global engine if engine: await engine.dispose() logger.info("✅ Database connection closed") async def get_async_session() -> AsyncGenerator[AsyncSession, None]: """Dependency to obtain an async database session Used as a FastAPI dependency for database session injection. Automatically handles: - Session opening - Rollback on exception - Clean session closure Yields: AsyncSession: Async database session Raises: RuntimeError: If database is not initialized """ if not async_session_maker: raise RuntimeError("Database not initialized") # Manage the session lifecycle explicitly. We avoid `async with ... # as session:` because it bundles close() into the generator # teardown path; when FastAPI cancels the dependency (GeneratorExit # at the yield), the close() can race a still-in-flight # _connection_for_bind() and raise IllegalStateChangeError. Wrapping # both rollback and close in their own try/except keeps the cleanup # quiet on cancellation while still surfacing real DB errors via # the normal exception path. session = async_session_maker() try: yield session except Exception: try: await session.rollback() except Exception as rollback_err: logger.debug(f"Session rollback during error handling failed: {rollback_err}") raise finally: try: await session.close() except Exception as close_err: # Common during request cancellation; nothing actionable. logger.debug(f"Session close during teardown failed: {close_err}") async def create_tables(): """Create all database tables""" if not engine: raise RuntimeError("Database engine not initialized") logger.info("Creating database tables...") async with engine.begin() as conn: await conn.run_sync(Base.metadata.create_all) logger.info("✅ Database tables created") async def drop_tables(): """Drop all database tables""" if not engine: raise RuntimeError("Database engine not initialized") logger.info("Dropping database tables...") async with engine.begin() as conn: await conn.run_sync(Base.metadata.drop_all) logger.info("✅ Database tables dropped")