File size: 5,546 Bytes
e6be672
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4f5c4a8
 
 
 
 
 
 
 
 
 
 
 
e6be672
 
4f5c4a8
 
 
 
 
 
 
 
 
e6be672
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
# 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 <https://www.gnu.org/licenses/>.

"""
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")