# Database Connection Best Practices

## Quick Reference for Developers

### ✅ DO: Use Async Database Pool

```python
from database_async import fetch_one, fetch_dict_all, execute_query

async def my_function(user_id: int):
    # Single row query
    user = await fetch_dict_one(
        "SELECT * FROM users WHERE id = %s",
        (user_id,)
    )

    # Multiple rows query
    items = await fetch_dict_all(
        "SELECT * FROM items WHERE user_id = %s",
        (user_id,)
    )

    # Insert/Update/Delete
    await execute_query(
        "UPDATE users SET coins = coins + %s WHERE id = %s",
        (amount, user_id)
    )
```

### ❌ DON'T: Create New Connections

```python
# BAD - Creates new connection every call
from database import get_database_connection

def my_function():
    conn = get_database_connection()  # ❌ Connection leak risk
    cursor = conn.cursor()
    cursor.execute("SELECT ...")
    # Connection might not get closed on errors
```

### 🔧 IF YOU MUST Use Sync Connections

Only use for legacy code or special cases. ALWAYS clean up:

```python
from database import get_database_connection

def legacy_function():
    mydb = None
    cursor = None
    try:
        mydb = get_database_connection()
        cursor = mydb.cursor()

        cursor.execute("SELECT * FROM table WHERE id = %s", (id,))
        result = cursor.fetchone()
        mydb.commit()

        return result

    except Exception as e:
        logger.error(f"Error: {e}")
        if mydb:
            mydb.rollback()
        raise

    finally:
        # CRITICAL: Always close
        if cursor:
            cursor.close()
        if mydb:
            mydb.close()
```

## Common Patterns

### Pattern 1: Simple Query

```python
async def get_user(user_id: int):
    from database_async import fetch_dict_one

    return await fetch_dict_one(
        "SELECT * FROM users WHERE id = %s",
        (user_id,)
    )
```

### Pattern 2: Insert and Get ID

```python
async def create_user(name: str, email: str):
    from database_async import get_connection

    async with get_connection() as conn:
        async with conn.cursor() as cursor:
            await cursor.execute(
                "INSERT INTO users (name, email) VALUES (%s, %s)",
                (name, email)
            )
            user_id = cursor.lastrowid

    return user_id
```

### Pattern 3: Transaction (Multiple Queries)

```python
async def transfer_coins(from_user: int, to_user: int, amount: int):
    from database_async import Transaction

    async with Transaction() as conn:
        async with conn.cursor() as cursor:
            # Deduct from sender
            await cursor.execute(
                "UPDATE users SET coins = coins - %s WHERE id = %s",
                (amount, from_user)
            )

            # Add to receiver
            await cursor.execute(
                "UPDATE users SET coins = coins + %s WHERE id = %s",
                (amount, to_user)
            )

            # Both queries commit together or rollback together
```

### Pattern 4: Batch Insert

```python
async def create_multiple_items(items: List[Tuple]):
    from database_async import execute_many

    await execute_many(
        "INSERT INTO items (name, price) VALUES (%s, %s)",
        items  # [(name1, price1), (name2, price2), ...]
    )
```

## Connection Pool Status

Current configuration:
- **Pool Size:** 20 connections (configurable via `MAX_CONNECTIONS` env var)
- **Min Size:** 10 connections (kept warm)
- **Connection Timeout:** 10 seconds
- **Pool Timeout:** 30 seconds (waiting for available connection)
- **Recycle:** Connections recycled after 1 hour

## Monitoring

### Check Connection Usage

```sql
-- Current connections
SHOW STATUS LIKE 'Threads_connected';

-- Max allowed
SHOW VARIABLES LIKE 'max_connections';

-- Active queries
SHOW PROCESSLIST;
```

### Expected Values

- **Development:** 5-15 connections
- **Production:** 10-20 connections
- **Peak Load:** Should not exceed 30 connections

### Warning Signs

❌ If you see:
- `pymysql.err.OperationalError: (1040, 'Too many connections')`
- `aiomysql.errors.PoolError: Pool timeout`
- Connection count steadily increasing

✅ Then:
1. Check for missing `finally` blocks
2. Verify async functions use pool
3. Review recent code changes
4. Check logs for errors during DB operations

## Migration Checklist

When converting sync to async:

- [ ] Change `def` to `async def`
- [ ] Add `await` before database calls
- [ ] Import from `database_async` not `database`
- [ ] Replace `get_database_connection()` with pool methods
- [ ] Remove manual connection/cursor cleanup (pool handles it)
- [ ] Update callers to `await` your function
- [ ] Test error cases still work

## Common Mistakes

### Mistake 1: Forgetting await

```python
# ❌ WRONG
async def my_function():
    result = fetch_one("SELECT ...")  # Missing await!

# ✅ CORRECT
async def my_function():
    result = await fetch_one("SELECT ...")
```

### Mistake 2: Mixing Sync and Async

```python
# ❌ WRONG
async def my_function():
    conn = get_database_connection()  # Sync connection in async function!

# ✅ CORRECT
async def my_function():
    from database_async import fetch_one
    result = await fetch_one(...)
```

### Mistake 3: Not Closing Sync Connections

```python
# ❌ WRONG
def my_function():
    conn = get_database_connection()
    cursor = conn.cursor()
    cursor.execute(...)
    return result  # Leaks connection!

# ✅ CORRECT
def my_function():
    conn = None
    cursor = None
    try:
        conn = get_database_connection()
        cursor = conn.cursor()
        cursor.execute(...)
        return result
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
```

## Performance Tips

1. **Use connection pool** - Reusing connections is 10x faster than creating new ones
2. **Batch operations** - Use `execute_many()` for multiple inserts
3. **Use transactions** - Group related queries to reduce round trips
4. **Close cursors** - Even in async, cursors hold memory
5. **Avoid SELECT *** - Only fetch columns you need

## Questions?

See these files for examples:
- `/Users/craigvandergalien/Documents/GitHub/lichun/ws/database_async.py` - Pool implementation
- `/Users/craigvandergalien/Documents/GitHub/lichun/ws/retention/daily_quests.py` - Async examples
- `/Users/craigvandergalien/Documents/GitHub/lichun/DATABASE_CONNECTION_FIX.md` - Full fix documentation