Sample MCP Server - Go (database-query-server)

[crivetimihai]

Overview

Create a sample MCP Server in Go that provides database query capabilities with connection pooling, query optimization, and security features.

Server Specifications

Server Details

• Name: database-query-server
• Language: Go
• Location: mcp-servers/go/database-query-server/
• Purpose: Demonstrate secure database operations via MCP

Core Features

• Multiple database support (PostgreSQL, MySQL, SQLite)
• Connection pooling and management
• Prepared statement execution
• Query result formatting (JSON, CSV, table)
• Transaction support
• Query performance metrics

Tools Provided

1. execute_query

Execute SQL SELECT queries with safety constraints

type QueryRequest struct {
    Database    string            `json:"database"`
    Query       string            `json:"query"`
    Parameters  map[string]any    `json:"parameters,omitempty"`
    Format      string            `json:"format,omitempty"` // json, csv, table
    Limit       int               `json:"limit,omitempty"`
    Timeout     int               `json:"timeout,omitempty"`
}

2. execute_prepared

Execute prepared statements safely

type PreparedRequest struct {
    Database      string         `json:"database"`
    StatementName string         `json:"statement_name"`
    Parameters    []any          `json:"parameters"`
    Format        string         `json:"format,omitempty"`
}

3. get_schema

Retrieve database schema information

type SchemaRequest struct {
    Database string   `json:"database"`
    Tables   []string `json:"tables,omitempty"`
    Detailed bool     `json:"detailed,omitempty"`
}

4. get_connection_status

Check database connection health and pool statistics

type ConnectionStatus struct {
    Database     string `json:"database"`
    Connected    bool   `json:"connected"`
    PoolStats    PoolInfo `json:"pool_stats"`
    LastPing     string `json:"last_ping"`
}

Security Features

• SQL injection prevention
• Query allowlist/blocklist
• Read-only mode enforcement
• Connection string encryption
• Query audit logging
• Rate limiting per connection

Implementation Requirements

Directory Structure

mcp-servers/go/database-query-server/
├── cmd/
│   └── server/
│       └── main.go
├── internal/
│   ├── handlers/
│   │   ├── query.go
│   │   ├── prepared.go
│   │   └── schema.go
│   ├── database/
│   │   ├── pool.go
│   │   ├── security.go
│   │   └── metrics.go
│   └── config/
│       └── config.go
├── pkg/
│   └── types/
│       └── requests.go
├── go.mod
├── go.sum
├── README.md
├── docker-compose.yml
└── Makefile

Dependencies

// go.mod
module github.com/IBM/mcp-context-forge/mcp-servers/go/database-query-server

go 1.21

require (
    github.com/lib/pq v1.10.9              // PostgreSQL
    github.com/go-sql-driver/mysql v1.7.1  // MySQL  
    modernc.org/sqlite v1.28.0             // SQLite
    github.com/IBM/mcp-context-forge/mcp-servers/go/mcp v0.1.0
)

Configuration

# config.yaml
databases:
  primary:
    driver: "postgres"
    connection_string: "postgres://user:pass@localhost/db"
    pool_config:
      max_open: 25
      max_idle: 5
      max_lifetime: "1h"
    security:
      read_only: true
      allowed_operations: ["SELECT", "SHOW", "DESCRIBE"]
      query_timeout: "30s"
      
  analytics:
    driver: "mysql"
    connection_string: "user:pass@tcp(localhost:3306)/analytics"
    
  local:
    driver: "sqlite3"
    connection_string: "./local.db"
    
server:
  timeout: "30s"
  rate_limit: 100  # queries per minute
  audit_log: true

Usage Examples

Basic Query

# Execute a simple SELECT query
echo '{
  "method": "tools/call",
  "params": {
    "name": "execute_query",
    "arguments": {
      "database": "primary",
      "query": "SELECT id, name, email FROM users WHERE active = $1",
      "parameters": {"1": true},
      "format": "json",
      "limit": 100
    }
  }
}' | mcp-database-query-server

Schema Information

# Get table schema
echo '{
  "method": "tools/call", 
  "params": {
    "name": "get_schema",
    "arguments": {
      "database": "primary",
      "tables": ["users", "orders"],
      "detailed": true
    }
  }
}' | mcp-database-query-server

Advanced Features

• Query Caching: Cache frequently used query results
• Connection Failover: Automatic failover to backup databases
• Metrics Export: Prometheus metrics for monitoring
• Query Explain: EXPLAIN query execution plans
• Batch Operations: Execute multiple queries in sequence

Testing Requirements

• Unit tests for all database operations
• Integration tests with real databases
• Security tests for SQL injection prevention
• Performance tests with connection pooling
• Docker Compose setup for testing environment

Documentation

• Comprehensive README with setup instructions
• API documentation for all tools
• Security best practices guide
• Performance tuning recommendations

Acceptance Criteria

• Go MCP server with 4+ database tools
• Support for PostgreSQL, MySQL, and SQLite
• Secure query execution with prepared statements
• Connection pooling and health monitoring
• Comprehensive test suite (>90% coverage)
• Docker setup for easy development
• Security features (injection prevention, audit logging)
• Performance monitoring and metrics
• Complete documentation and examples

Priority

Medium - Demonstrates enterprise database integration patterns

Use Cases

• Database exploration and analytics
• Secure data access for AI applications
• Multi-database query aggregation
• Database health monitoring
• Development and testing workflows

[himanshubohra13]

@crivetimihai Can i work on this?

[crivetimihai]

Sure thing, go for it!

[himanshubohra13]

Sure thing, go for it!
Thanks.
Will raise PR by 10