app/db/database.py

# app/db/database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import logging

# Import settings to get the database URL
from app.core.config import settings

logger = logging.getLogger(__name__)

# Ensure the DATABASE_URL (which is PostgresDsn type) is converted to string for create_engine
SQLALCHEMY_DATABASE_URL = str(settings.DATABASE_URL)

logger.info(f"Database URL Prefix: {SQLALCHEMY_DATABASE_URL[:SQLALCHEMY_DATABASE_URL.find('@') if '@' in SQLALCHEMY_DATABASE_URL else 20]}...") # Log prefix only, hiding credentials

try:
    # Create the SQLAlchemy engine
    # pool_pre_ping=True helps handle potential connection drops
    engine = create_engine(
        SQLALCHEMY_DATABASE_URL,
        pool_pre_ping=True
        # Add connect_args if needed, e.g., for SQLite:
        # connect_args={"check_same_thread": False}
    )

    # Create a configured "Session" class
    SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

    # Create a Base class for declarative class definitions
    Base = declarative_base()

    logger.info("SQLAlchemy engine and SessionLocal created successfully.")

except Exception as e:
    logger.exception(f"Failed to create SQLAlchemy engine or SessionLocal: {e}")
    # Depending on requirements, might want to raise error to prevent startup
    raise RuntimeError("Database configuration failed.") from e


# Dependency function to get a DB session for API endpoints
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()