# backend/app/database.py - Connection with pooling
engine = create_engine(
    DATABASE_URL,
    pool_size=10,              # Base connections
    max_overflow=20,           # Burst capacity
    pool_timeout=30,           # Wait time for connection
    pool_recycle=1800,         # Recycle every 30 min
    pool_pre_ping=True,        # Validate before use
)

# FastAPI dependency injection pattern
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# Usage in routers
@router.get("/books/{id}")
def get_book(id: int, db: Session = Depends(get_db)):
    return db.query(Book).filter(Book.id == id).first()

from sqlalchemy.orm import joinedload

# Always eager-load relationships for list endpoints
requests = db.query(BookRequest).options(
    joinedload(BookRequest.book),
    joinedload(BookRequest.user)
).order_by(BookRequest.created_at.desc()).all()

try:
    db.add(book)
    db.flush()  # Catch constraint violations early
    db.commit()
except IntegrityError:
    db.rollback()
    existing = db.query(Book).filter(Book.hardcover_id == id).first()
    return existing