Spaces:
Sleeping
Sleeping
File size: 6,703 Bytes
4db2610 2075bb4 4db2610 2075bb4 4db2610 2075bb4 4db2610 178a40a bdcdf93 4db2610 2075bb4 4db2610 2075bb4 4db2610 2075bb4 4db2610 2075bb4 4db2610 2075bb4 589403a 2075bb4 589403a 4db2610 2075bb4 4db2610 589403a 4db2610 589403a 4db2610 589403a 4db2610 2075bb4 4db2610 2075bb4 4db2610 2075bb4 df0e6cf 589403a 2075bb4 bdcdf93 2075bb4 589403a 4db2610 2075bb4 4db2610 589403a 4db2610 2075bb4 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 | """
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)
|