# MCP Database Server Example

**Status**: ✅ Complete

**Last Updated**: December 6, 2025

---

## Overview

This example demonstrates integrating a database MCP server with RiceCoder. The database server provides tools for querying, inserting, updating, and deleting data.

## Setup

### 1. Install Database MCP Server

```bash
# Install via pip
pip install database-mcp-server

# Or use uvx
uvx database-mcp-server@latest
```

### 2. Configure MCP Server

Create `.ricecoder/mcp-servers.yaml`:

```yaml
servers:
  - id: database-server
    name: Database Tools
    command: uvx
    args:
      - database-mcp-server@latest
    env:
      DB_URL: postgresql://localhost/mydb
      DB_USER: admin
      DB_PASSWORD: ${DB_PASSWORD}
      DB_POOL_SIZE: "10"
      LOG_LEVEL: INFO
    timeout_ms: 5000
    auto_reconnect: true
    max_retries: 3
```

### 3. Set Environment Variables

```bash
export DB_PASSWORD=your_password
```

### 4. Verify Connection

```bash
ricecoder tools list
```

Expected output:
```
Available Tools:

database-query (database-server)
  Description: Execute database queries
  Category: database
  Parameters: query (string, required)
  Returns: object

database-insert (database-server)
  Description: Insert data into database
  Category: database
  Parameters: table (string, required), data (object, required)
  Returns: object

database-update (database-server)
  Description: Update data in database
  Category: database
  Parameters: table (string, required), id (string, required), data (object, required)
  Returns: object

database-delete (database-server)
  Description: Delete data from database
  Category: database
  Parameters: table (string, required), id (string, required)
  Returns: object
```

## Configure Permissions

Create `.ricecoder/permissions.yaml`:

```yaml
permissions:
  # Allow all database reads
  - pattern: "database-query"
    level: allow
  
  # Ask for database writes
  - pattern: "database-insert"
    level: ask
  
  - pattern: "database-update"
    level: ask
  
  - pattern: "database-delete"
    level: ask
```

## Usage Examples

### Example 1: Query Database

```bash
ricecoder tools invoke database-query \
  --query "SELECT * FROM users WHERE age > 18"
```

Output:
```json
{
  "success": true,
  "rows": [
    {
      "id": 1,
      "name": "Alice",
      "age": 25,
      "email": "alice@example.com"
    },
    {
      "id": 2,
      "name": "Bob",
      "age": 30,
      "email": "bob@example.com"
    }
  ],
  "count": 2
}
```

### Example 2: Insert Data

```bash
ricecoder tools invoke database-insert \
  --table users \
  --data '{"name": "Charlie", "age": 28, "email": "charlie@example.com"}'
```

Output:
```json
{
  "success": true,
  "id": 3,
  "message": "Record inserted successfully"
}
```

### Example 3: Update Data

```bash
ricecoder tools invoke database-update \
  --table users \
  --id 3 \
  --data '{"age": 29}'
```

Output:
```json
{
  "success": true,
  "message": "Record updated successfully"
}
```

### Example 4: Delete Data

```bash
ricecoder tools invoke database-delete \
  --table users \
  --id 3
```

Output:
```json
{
  "success": true,
  "message": "Record deleted successfully"
}
```

## Integration with Agents

### Code Generator Agent

The code generator can use database tools to:
- Query existing database schema
- Insert generated code into database
- Update documentation in database

```rust
// In agent code
let schema = agent.invoke_tool("database-query", 
  json!({"query": "SELECT * FROM information_schema.tables"})
).await?;

// Generate code based on schema
let generated_code = generate_code(&schema);

// Store in database
agent.invoke_tool("database-insert",
  json!({
    "table": "generated_code",
    "data": {
      "code": generated_code,
      "timestamp": now()
    }
  })
).await?;
```

### Data Analysis Agent

The data analysis agent can use database tools to:
- Query data for analysis
- Store analysis results
- Update data based on analysis

```rust
// Query data
let data = agent.invoke_tool("database-query",
  json!({"query": "SELECT * FROM metrics WHERE date > NOW() - INTERVAL 7 DAY"})
).await?;

// Analyze data
let analysis = analyze_metrics(&data);

// Store results
agent.invoke_tool("database-insert",
  json!({
    "table": "analysis_results",
    "data": analysis
  })
).await?;
```

## Configuration Files

### .ricecoder/mcp-servers.yaml

```yaml
servers:
  - id: database-server
    name: Database Tools
    command: uvx
    args:
      - database-mcp-server@latest
    env:
      DB_URL: postgresql://localhost/mydb
      DB_USER: admin
      DB_PASSWORD: ${DB_PASSWORD}
      DB_POOL_SIZE: "10"
      LOG_LEVEL: INFO
    timeout_ms: 5000
    auto_reconnect: true
    max_retries: 3
```

### .ricecoder/permissions.yaml

```yaml
permissions:
  # Allow all database reads
  - pattern: "database-query"
    level: allow
  
  # Ask for database writes
  - pattern: "database-insert"
    level: ask
  
  - pattern: "database-update"
    level: ask
  
  - pattern: "database-delete"
    level: ask
  
  # Per-agent: Allow data processor to write
  - pattern: "database-insert"
    level: allow
    agent_id: data-processor
  
  - pattern: "database-update"
    level: allow
    agent_id: data-processor
```

## Troubleshooting

### Connection Failed

**Error**: `Failed to connect to database server`

**Solutions**:
1. Check database is running: `psql -U admin -d mydb`
2. Verify connection string: `DB_URL=postgresql://localhost/mydb`
3. Check credentials: `DB_USER=admin`, `DB_PASSWORD=...`
4. Check network connectivity

### Query Timeout

**Error**: `Tool 'database-query' timed out after 5000ms`

**Solutions**:
1. Increase timeout: `timeout_ms: 10000`
2. Optimize query
3. Check database performance
4. Add indexes to frequently queried columns

### Permission Denied

**Error**: `Permission denied for tool 'database-insert'`

**Solutions**:
1. Check permissions in `.ricecoder/permissions.yaml`
2. Verify permission level is not "deny"
3. Check per-agent permissions
4. Update permissions if needed

## See Also

- [MCP Integration](./MCP-Integration.md)
- [MCP Server Configuration](./MCP-Server-Configuration.md)
- [Permission Configuration](./MCP-Permissions.md)
- [MCP API Service Example](./MCP-API-Service-Example.md)

---

*Last updated: December 6, 2025*