backend/app/core/database.py

from sqlalchemy import create_engine, event
from sqlalchemy.engine import Engine
from sqlalchemy.orm import declarative_base, sessionmaker
from app.core.config import settings

# Determine if using SQLite or Postgres
is_sqlite = settings.DATABASE_URL.startswith("sqlite")

connect_args = {}
if is_sqlite:
    # SQLite requires disabling same thread checks for concurrent FastAPI endpoints
    connect_args = {"check_same_thread": False}

# Engine setup
engine = create_engine(
    settings.DATABASE_URL,
    connect_args=connect_args,
    # Standard pool config for Postgres (SQLite doesn't support pool pre-ping/size attributes)
    **({
        "pool_size": 20,
        "max_overflow": 10,
        "pool_recycle": 1800,
        "pool_pre_ping": True
    } if not is_sqlite else {})
)

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    if is_sqlite:
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON")
        cursor.close()

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()