Spaces:
Sleeping
Sleeping
Download database/database.py from Divyaraj1617/testnexus-backend: direct link, hf CLI and curl.
- Browser
- Download file 6.7 kB
-
https://huggingface.co/spaces/Divyaraj1617/testnexus-backend/resolve/main/database/database.py
- Command line
-
hf download hf://spaces/Divyaraj1617/testnexus-backend/database/database.py
-
curl -L -o database.py https://huggingface.co/spaces/Divyaraj1617/testnexus-backend/resolve/main/database/database.py
6.7 kB
| """ | |
| 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) | |