itsme00/full-stack-todo

1"""
2Database Configuration and Session Management
3
4Sprint 2 - Task: T071
5Owner: @database-expert
6
7Provides:
8- SQLModel engine configuration with Neon PostgreSQL
9- Database session factory with dependency injection
10- Connection pooling and SSL stability for serverless environments
11
12CRITICAL: All models are now in separate files under backend/models/
13"""
14
15from sqlmodel import SQLModel, create_engine, Session
16from typing import Generator
17import os
18from dotenv import load_dotenv
19
20# Load environment variables
21load_dotenv()
22
23# Database connection configuration
24DATABASE_URL = os.getenv("DATABASE_URL")
25
26if not DATABASE_URL:
27    raise ValueError(
28        "DATABASE_URL environment variable not set. "
29        "Please configure Neon PostgreSQL connection in backend/.env"
30    )
31
32# Create database engine with connection pooling and SSL stability
33# pool_pre_ping=True: Checks connection health before using from pool
34# pool_recycle=300: Recycles connections every 5 minutes to prevent stale SSL connections
35# echo=True: Log SQL queries (disable in production for performance)
36engine = create_engine(
37    DATABASE_URL,
38    echo=True,  # Set to False in production
39    pool_pre_ping=True,  # Test connections before use
40    pool_recycle=300,  # Recycle every 5 minutes
41    pool_size=5,  # Maximum pool size
42    max_overflow=10,  # Allow 10 additional connections beyond pool_size
43)
44
45
46def get_session() -> Generator[Session, None, None]:
47    """
48    Dependency injection for database sessions.
49
50    Yields a SQLModel Session that automatically commits on success
51    and rolls back on exceptions.
52
53    Usage in FastAPI endpoints:
54        @app.get("/tasks")
55        def get_tasks(session: Session = Depends(get_session)):
56            tasks = session.exec(select(Task)).all()
57            return tasks
58    """
59    with Session(engine) as session:
60        yield session
61
62
63def init_db():
64    """
65    Initialize database tables on application startup.
66
67    Creates tables if they don't exist (idempotent, safe for production).
68    Imports all models to ensure they're registered with SQLModel metadata.
69    """
70    # Import models to register them with SQLModel metadata
71    from backend.models import User, Task  # noqa: F401
72    from backend.models.conversation import Conversation  # noqa: F401
73    from backend.models.message import Message  # noqa: F401
74
75    print("Initializing database tables...")
76
77    # Create all tables if they don't exist (idempotent operation)
78    SQLModel.metadata.create_all(engine)
79    print("[OK] Database tables initialized successfully")
80    print("[OK] - User, Task, Conversation, Message tables ready")
81