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)