testnexus-backend / database /database.py
Divyaraj1617's picture
Updated db files to conenct
bdcdf93
Raw History Blame Contribute Delete
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)