Aurora / db.py
Kannan Priyadharshan
Upload folder using huggingface_hub
3cd0b3c verified
Raw History Blame Contribute Delete
6.91 kB
"""
Database configuration with MySQL SSL support for Aiven deployment.
"""
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.pool import QueuePool
# Ensure PyMySQL is used as MySQLdb
try:
import pymysql
pymysql.install_as_MySQLdb()
except ImportError:
pass
# Read environment variables - AURORA_DB_URL is required
AURORA_DB_URL = os.getenv("AURORA_DB_URL")
if not AURORA_DB_URL:
raise ValueError(
"AURORA_DB_URL environment variable is required. "
"Please set it to your MySQL connection string (e.g., mysql+pymysql://user:pass@host:port/db)"
)
MYSQL_SSL_CA_PATH = os.getenv("MYSQL_SSL_CA_PATH", "/app/ca.pem")
def create_aurora_engine():
"""
Create SQLAlchemy engine with SSL support and conservative pool settings for free tiers.
"""
print(f"Creating database engine with URL: {AURORA_DB_URL[:50]}...")
print(f"SSL CA path: {MYSQL_SSL_CA_PATH}")
print(f"SSL CA exists: {os.path.exists(MYSQL_SSL_CA_PATH)}")
# Verify MySQL dialect
if not AURORA_DB_URL.startswith("mysql"):
from urllib.parse import urlparse
parsed = urlparse(AURORA_DB_URL)
host_port = f"{parsed.hostname}:{parsed.port}" if parsed.port else parsed.hostname
raise ValueError(
f"Database dialect must be MySQL, but got: {parsed.scheme} "
f"(host: {host_port}). Please set AURORA_DB_URL to a MySQL connection string "
f"in your Hugging Face Space environment variables."
)
# Ensure we're using PyMySQL dialect and clean the URL
db_url = AURORA_DB_URL
if db_url.startswith("mysql://") and not db_url.startswith("mysql+pymysql://"):
db_url = db_url.replace("mysql://", "mysql+pymysql://", 1)
print(f"Converted URL to PyMySQL format: {db_url[:50]}...")
# Remove any existing SSL parameters from URL to avoid conflicts
from urllib.parse import urlparse, urlunparse, parse_qs
parsed = urlparse(db_url)
query_params = parse_qs(parsed.query)
# Remove SSL-related parameters from URL
ssl_params_to_remove = ['ssl-mode', 'ssl_ca', 'ssl_verify_cert', 'ssl_verify_identity', 'ssl_disabled']
for param in ssl_params_to_remove:
query_params.pop(param, None)
# Rebuild URL without SSL parameters
clean_query = '&'.join([f"{k}={v[0]}" for k, v in query_params.items()])
clean_url = urlunparse((parsed.scheme, parsed.netloc, parsed.path, parsed.params, clean_query, parsed.fragment))
if clean_url != db_url:
print(f"Cleaned URL: {clean_url[:50]}...")
db_url = clean_url
# SSL configuration for PyMySQL
ssl_args = {}
if "mysql+pymysql" in db_url and os.path.exists(MYSQL_SSL_CA_PATH):
ssl_args = {
"ssl_ca": MYSQL_SSL_CA_PATH,
"ssl_verify_cert": True,
"ssl_verify_identity": False
}
print(f"SSL configuration: {ssl_args}")
else:
print("No SSL configuration applied")
# Create engine with conservative pool settings for free tiers
try:
engine = create_engine(
db_url,
# Conservative pool settings for free tiers
poolclass=QueuePool,
pool_size=2, # Small pool size for free tier
max_overflow=3, # Limited overflow
pool_pre_ping=True, # Verify connections before use
pool_recycle=1800, # Recycle connections every 30 minutes
pool_timeout=30, # Timeout for getting connection from pool
# SSL configuration
connect_args=ssl_args,
# Additional settings
future=True,
echo=False # Set to True for debugging
)
print("Database engine created successfully")
# Log dialect information
dialect_name = engine.dialect.name
print(f"βœ… Database dialect: {dialect_name}")
return engine
except Exception as e:
print(f"Error creating database engine with SSL: {e}")
# Fallback: try without SSL
print("Attempting fallback without SSL...")
try:
fallback_engine = create_engine(
db_url,
poolclass=QueuePool,
pool_size=2,
max_overflow=3,
pool_pre_ping=True,
pool_recycle=1800,
pool_timeout=30,
future=True,
echo=False
)
print("Fallback engine created successfully")
# Log dialect information
dialect_name = fallback_engine.dialect.name
print(f"βœ… Database dialect: {dialect_name}")
return fallback_engine
except Exception as fallback_error:
print(f"Fallback also failed: {fallback_error}")
# Last resort: try with minimal configuration
print("Attempting minimal configuration...")
minimal_engine = create_engine(
db_url,
pool_pre_ping=True,
future=True,
echo=False
)
print("Minimal engine created")
# Log dialect information
dialect_name = minimal_engine.dialect.name
print(f"βœ… Database dialect: {dialect_name}")
return minimal_engine
# Lazy-loaded engine and session factory
_engine = None
Base = declarative_base()
def get_engine():
"""Get or create the database engine with fallback handling."""
global _engine
if _engine is None:
_engine = create_aurora_engine()
return _engine
def get_session_local():
"""Get or create the session factory."""
return sessionmaker(bind=get_engine(), autoflush=False, autocommit=False)
# For backward compatibility
SessionLocal = get_session_local()
engine = get_engine()
def get_db_session():
"""
Dependency function to get database session.
Use this in FastAPI dependencies.
"""
session = SessionLocal()
try:
yield session
finally:
session.close()
def init_database():
"""
Initialize database tables. Gracefully handles read-only databases.
"""
try:
Base.metadata.create_all(bind=engine)
print("βœ… Database tables created successfully")
except Exception as e:
error_msg = str(e).lower()
if "readonly" in error_msg or "read-only" in error_msg or "permission denied" in error_msg:
print("⚠️ Database is read-only - skipping table creation")
print(" This is normal for cloud database services with restricted access")
else:
print(f"❌ Database initialization failed: {e}")
raise
# For backward compatibility - export the engine and SessionLocal
__all__ = ["engine", "SessionLocal", "Base", "get_db_session", "init_database"]