Spaces:
Sleeping
Sleeping
File size: 5,288 Bytes
b8907ef | 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 160 161 162 163 164 165 166 167 168 169 170 | """
============================================
Database Connection Manager
- Async SQLAlchemy Engine with Connection Pooling
- Handles Render's free tier quirks:
* Sleep mode reconnection
* Connection drop recovery
* Pool pre-ping for stale connections
============================================
"""
import logging
from sqlalchemy.ext.asyncio import (
create_async_engine,
AsyncSession,
async_sessionmaker,
AsyncEngine,
)
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.pool import AsyncAdaptedQueuePool
from sqlalchemy import text
from app.config import get_database_url, settings
logger = logging.getLogger(__name__)
# ============================================
# Base Model Class (all models inherit this)
# ============================================
class Base(DeclarativeBase):
"""Base class for all database models."""
pass
# ============================================
# Engine Configuration
# ============================================
_engine: AsyncEngine | None = None
_session_factory: async_sessionmaker[AsyncSession] | None = None
def get_engine() -> AsyncEngine:
"""
Create or return the async SQLAlchemy engine.
Key settings for Render Free Tier:
- pool_pre_ping=True: Tests connection before using it
(prevents "connection closed" errors after DB sleep)
- pool_size=5: Small pool (free tier has limited connections)
- max_overflow=3: Allow 3 extra connections under load
- pool_recycle=300: Recycle connections every 5 minutes
(prevents stale connections from Render's idle timeout)
- pool_timeout=30: Wait max 30s for a connection from pool
"""
global _engine
if _engine is None:
database_url = get_database_url()
logger.info("Creating database engine...")
logger.info(f"Database host: {database_url.split('@')[1].split('/')[0] if '@' in database_url else 'unknown'}")
_engine = create_async_engine(
database_url,
# --- Connection Pool Settings ---
poolclass=AsyncAdaptedQueuePool,
pool_pre_ping=True, # CRITICAL: Check connection health
pool_size=5, # Base connections in pool
max_overflow=3, # Extra connections allowed
pool_recycle=300, # Recycle every 5 min (300 sec)
pool_timeout=30, # Wait 30s for available connection
# --- Query Settings ---
echo=settings.DEBUG, # Log SQL queries in debug mode
# --- Connection Arguments ---
connect_args={
"timeout": 30, # Connection timeout
"command_timeout": 60, # Query timeout
"server_settings": {
"application_name": "novel_scraper",
"idle_in_transaction_session_timeout": "60000", # 60s
},
},
)
logger.info("Database engine created successfully.")
return _engine
def get_session_factory() -> async_sessionmaker[AsyncSession]:
"""
Create or return the async session factory.
"""
global _session_factory
if _session_factory is None:
engine = get_engine()
_session_factory = async_sessionmaker(
bind=engine,
class_=AsyncSession,
expire_on_commit=False, # Don't expire objects after commit
autocommit=False,
autoflush=False,
)
logger.info("Session factory created.")
return _session_factory
async def get_db_session() -> AsyncSession:
"""
Dependency for FastAPI routes.
Yields an async database session.
Usage in FastAPI:
@router.get("/something")
async def my_route(db: AsyncSession = Depends(get_db_session)):
...
"""
factory = get_session_factory()
async with factory() as session:
try:
yield session
await session.commit()
except Exception as e:
await session.rollback()
logger.error(f"Database session error: {e}")
raise
finally:
await session.close()
async def init_database():
"""
Initialize database: Create all tables.
Called on application startup.
"""
engine = get_engine()
try:
# Test connection first
async with engine.begin() as conn:
await conn.execute(text("SELECT 1"))
logger.info("✅ Database connection test successful!")
# Create all tables
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.create_all)
logger.info("✅ Database tables created/verified!")
except Exception as e:
logger.error(f"❌ Database initialization failed: {e}")
logger.error("Check your DATABASE_URL environment variable!")
raise
async def close_database():
"""
Close database connections.
Called on application shutdown.
"""
global _engine, _session_factory
if _engine is not None:
await _engine.dispose()
_engine = None
_session_factory = None
logger.info("Database connections closed.") |