""" Database configuration and session management """ from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker from sqlalchemy.orm import declarative_base from sqlalchemy.pool import NullPool from typing import AsyncGenerator import os from urllib.parse import urlparse import asyncpg from dotenv import load_dotenv load_dotenv() # Detect serverless environment IS_SERVERLESS = bool(os.getenv("VERCEL") or os.getenv("AWS_LAMBDA_FUNCTION_NAME")) # Get database URL from environment variable DATABASE_URL = os.getenv("DATABASE_URL") if not DATABASE_URL: raise ValueError( "DATABASE_URL environment variable is not set. " "Please add DATABASE_URL to your .env file." ) # Ensure the URL uses the asyncpg driver if DATABASE_URL.startswith("postgresql://"): DATABASE_URL = DATABASE_URL.replace("postgresql://", "postgresql+asyncpg://", 1) elif DATABASE_URL.startswith("postgres://"): DATABASE_URL = DATABASE_URL.replace("postgres://", "postgresql+asyncpg://", 1) # Strip query params that asyncpg doesn't support (sslmode, channel_binding, etc.) # SSL is handled separately via connect_args from urllib.parse import urlparse as _urlparse, parse_qs as _parse_qs, urlencode as _urlencode, urlunparse as _urlunparse _ASYNCPG_UNSUPPORTED_PARAMS = {"sslmode", "channel_binding"} _parsed_url = _urlparse(DATABASE_URL) _query_params = _parse_qs(_parsed_url.query) _needs_ssl = _query_params.get("sslmode", [None])[0] in ("require", "verify-ca", "verify-full") for _param in _ASYNCPG_UNSUPPORTED_PARAMS: _query_params.pop(_param, None) _clean_query = _urlencode({k: v[0] for k, v in _query_params.items()}, doseq=False) DATABASE_URL = _urlunparse(_parsed_url._replace(query=_clean_query)) async def create_database_if_not_exists(database_url: str) -> str: """ Create database if it doesn't exist (PostgreSQL only) For SQLite, this function does nothing as the file is created automatically """ # Only handle PostgreSQL databases if "+asyncpg" not in database_url and "postgresql" not in database_url: return database_url # Normalize URL format for parsing normalized_url = database_url.replace("postgresql+asyncpg://", "postgresql://") parsed = urlparse(normalized_url) # Extract components db_name = parsed.path.lstrip('/') if not db_name: return database_url # No database name specified, return as is # Extract connection parameters username = parsed.username or "postgres" password = parsed.password or "" host = parsed.hostname or "localhost" port = parsed.port or 5432 # Skip auto-creation for managed database services (Neon, Supabase, etc.) # These don't allow connecting to the default 'postgres' DB to create new ones if any(provider in host for provider in ['neon.tech', 'supabase', 'railway']): print(f"✓ Using managed database '{db_name}' at {host}") return database_url try: # Connect to default 'postgres' database to check/create target database # asyncpg.connect() accepts connection parameters as keyword arguments conn = await asyncpg.connect( host=host, port=port, user=username, password=password, database='postgres' # Connect to default postgres database ) # Check if database exists db_exists = await conn.fetchval( "SELECT 1 FROM pg_database WHERE datname = $1", db_name ) if not db_exists: # Create database (must be done outside transaction) await conn.execute(f'CREATE DATABASE "{db_name}"') print(f"✓ Database '{db_name}' created successfully") else: print(f"✓ Database '{db_name}' already exists") await conn.close() except asyncpg.exceptions.InvalidPasswordError: print(f"Warning: Invalid password for database user '{username}'") print("Make sure the database exists or create it manually") except asyncpg.exceptions.InvalidCatalogNameError: # This shouldn't happen when connecting to 'postgres', but handle it anyway print(f"Warning: Could not connect to 'postgres' database") print("Make sure PostgreSQL is running and the database exists or create it manually") except Exception as e: # If we can't connect to postgres or create database, log and continue # The actual connection will fail later with a more specific error print(f"Warning: Could not auto-create database: {e}") print("Make sure the database exists or create it manually") return database_url # Create async engine # For PostgreSQL with asyncpg: postgresql+asyncpg://user:password@host:port/dbname # For SQLite: sqlite+aiosqlite:///./database.db if IS_SERVERLESS: # Serverless: use NullPool (no persistent connections) _engine_kwargs = dict( echo=False, future=True, poolclass=NullPool, ) else: _engine_kwargs = dict( echo=False, future=True, pool_size=20, max_overflow=10, pool_timeout=30, pool_pre_ping=True, pool_recycle=3600, ) # Enable SSL for managed database services (Neon, Supabase, etc.) if _needs_ssl or any(provider in DATABASE_URL for provider in ['neon.tech', 'supabase', 'railway']): _engine_kwargs["connect_args"] = {"ssl": "require"} engine = create_async_engine(DATABASE_URL, **_engine_kwargs) # Create async session factory AsyncSessionLocal = async_sessionmaker( engine, class_=AsyncSession, expire_on_commit=False, autocommit=False, autoflush=False, ) # Create base class for models Base = declarative_base() async def get_db() -> AsyncGenerator[AsyncSession, None]: """ Dependency to get database session Use this in FastAPI route dependencies like: @router.get("/items") async def get_items(db: AsyncSession = Depends(get_db)): ... """ async with AsyncSessionLocal() as session: try: yield session await session.commit() except Exception: await session.rollback() raise finally: await session.close() async def init_db(): """ Initialize database - create database if it doesn't exist, then create all tables Call this on application startup """ # First, try to create the database if it doesn't exist (PostgreSQL only) await create_database_if_not_exists(DATABASE_URL) # Then create all tables async with engine.begin() as conn: await conn.run_sync(Base.metadata.create_all)