app/db/base.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, DeclarativeBase

from app.core.config import settings

# Check for Turso remote connection
is_turso = "turso.io" in settings.DATABASE_URL and settings.TURSO_AUTH_TOKEN

if is_turso:
    # Extract hostname
    hostname = settings.DATABASE_URL
    if "://" in hostname:
        hostname = hostname.split("://")[1]
    if "?" in hostname:
        hostname = hostname.split("?")[0]
    
    from sqlalchemy.pool import NullPool

    # Proxy to satisfy SQLAlchemy's requirement for create_function on SQLite connections
    class LibsqlConnectionProxy:
        def __init__(self, conn):
            # Use __dict__ to avoid infinite recursion with __setattr__
            self.__dict__["_conn"] = conn
        
        def __getattr__(self, name):
            return getattr(self._conn, name)
            
        def __setattr__(self, name, value):
            if name == "_conn":
                self.__dict__["_conn"] = value
            else:
                setattr(self._conn, name, value)
                
        def create_function(self, *args, **kwargs):
            # libsql doesn't support create_function over HTTP, but SQLAlchemy expects it
            return None

    # Force HTTPS for stability on HF Spaces (avoids 505/WebSocket handshake errors)
    def create_libsql_connection():
        import libsql
        url = f"https://{hostname}"
        print(f">>> [DB] Establishing SECURE HTTP connection to Turso: {hostname}")
        conn = libsql.connect(url, auth_token=settings.TURSO_AUTH_TOKEN)
        return LibsqlConnectionProxy(conn)

    # Use NullPool for Turso HTTPS to avoid "detached connection fairy" errors in multi-thread env
    engine = create_engine(
        "sqlite+libsql://",
        creator=create_libsql_connection,
        poolclass=NullPool,
        echo=False,
    )
else:
    # Standard local SQLite
    connect_args = {}
    if "sqlite" in settings.DATABASE_URL or "libsql" in settings.DATABASE_URL:
        connect_args["check_same_thread"] = False
        
    engine = create_engine(
        settings.DATABASE_URL,
        pool_size=20,
        max_overflow=max(10, 20), # Allow some overflow during peak
        pool_timeout=30,
        pool_recycle=1800,
        connect_args=connect_args,
        echo=False,
    )

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


class Base(DeclarativeBase):
    pass


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