数据库查询执行协议

mycli-dms-mcp

MCP (Model Context Protocol) server for mycli DMS (Database Management System) functionality.

This MCP server provides a clean interface for executing database queries through the mycli DMS command-line tool, supporting both direct database connections and production DMS access.

Features

• Environment Management: List and configure multiple database environments
• Query Execution: Execute SQL queries with various output formats
• Production Access: Manage DMS cookies for production database access
• Multiple Formats: Support for table, JSON, and vertical output formats

Prerequisites

1. mycli binary: The mycli tool must be installed and accessible
2. Configuration: DMS environments should be pre-configured in ~/.config/mycli/

Installation

1. Build the MCP server:

cd /Users/Chen/Coding/mcp/mycli-dms-mcp
go build -o mycli-dms-mcp ./cmd/mycli-dms-mcp

2. Configure your MCP client to use the built binary

Configuration

Environment Variables

• MYCLI_PATH: Path to the mycli binary (optional, will search in PATH by default)
• MYCLI_CONFIG_DIR: Path to mycli configuration directory (default: ~/.config/mycli)

DMS Configuration

The MCP server works with existing mycli DMS configurations:

• Production DMS: ~/.config/mycli/dms.env (cookie-based authentication)
• Environment configs: ~/.config/mycli/dms_<env>.env (direct database connections)

Available Tools

1. list_profiles

List all available database connection profiles with usage guidance.

Parameters: None

Returns:

• profiles: Array of available profile configurations with type and usage patterns
• available_profiles: Simple array of valid profile names for exec_sql
• auth_configured: Boolean indicating if production DMS authentication is configured
• usage_examples: Example commands for different connection types
• setup_help: Setup instructions and file locations

AI Usage Pattern: Always call this first to discover valid profiles before using exec_sql.

2. exec_sql

Execute any SQL statement using mycli dms command.

Parameters:

• profile (required): Connection profile name from list_profiles. Examples: 'gg' for production, 'local', 'dev', 'testing', 'staging' for direct connections
• sql (required): Any SQL statement to execute (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, DESCRIBE, etc.)
• format (optional): Output format - 'json' (default), 'table', or 'vertical'
• limit_num (optional): Limit results for DMS gg mode only
• instance (optional): DMS instance name for gg mode only
• database (optional): Database name for gg mode only

Returns:

• success: Boolean indicating if statement succeeded
• output: SQL result or error message
• command: The actual mycli command executed
• profile: The profile used
• format: The output format used
• error: Error details if failed
• suggestion: Helpful guidance for common errors

AI Usage Pattern:

1. First call list_profiles to get available profiles
2. Use one of the returned profile names in the profile parameter
3. The server will validate the profile and provide helpful error messages if invalid
4. Supports all SQL operations: SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, DESCRIBE, etc.

3. set_auth_cookie

Set authentication cookie for production DMS access.

Parameters:

• cookie_string (optional): Cookie string in format 'csrftoken=xxx; sessionid=yyy'
• auto (optional): Automatically read cookie from Edge browser (macOS only)

Returns:

• success: Boolean indicating if operation succeeded
• output: Operation result or error message
• error: Error details if failed

4. create_profile

Create or update a database connection profile.

Parameters:

• profile (required): Connection profile name
• host (optional): Database host
• port (optional): Database port
• user (optional): Database user
• password (optional): Database password
• database (optional): Database name
• docker_container (optional): Docker container name

Returns:

• success: Boolean indicating if operation succeeded
• output: Operation result or error message
• error: Error details if failed

Usage Examples

AI Agent Workflow

The MCP server is designed to help AI agents work intelligently with database profiles:

Step 1: Discover Available Profiles

{
  "tool": "list_profiles"
}

Response provides:

• available_profiles: ["niumall", "safe"] - valid profile values for exec_sql
• profiles: Detailed info including usage patterns
• setup_help: Setup instructions if no profiles exist

Step 2: Execute SQL with Valid Profile

{
  "tool": "exec_sql",
  "arguments": {
    "profile": "niumall",
    "sql": "SELECT COUNT(*) FROM users WHERE status = 'active'",
    "format": "json"
  }
}

Step 3: Handle Invalid Profile Gracefully

If AI tries an invalid profile, the server provides helpful guidance:

{
  "tool": "exec_sql",
  "arguments": {
    "profile": "invalid",
    "sql": "SELECT 1"
  }
}

Error response includes:

• available_profiles: ["niumall", "safe"]
• suggestion: "Available profiles: [niumall safe]"
• help: "Use 'list_profiles' to see all available connection profiles"

Direct Usage Examples

List available profiles

{
  "tool": "list_profiles"
}

Execute various SQL operations

{
  "tool": "exec_sql",
  "arguments": {
    "profile": "local",
    "sql": "SELECT * FROM users LIMIT 10",
    "format": "json"
  }
}

{
  "tool": "exec_sql",
  "arguments": {
    "profile": "local",
    "sql": "INSERT INTO users (name, email) VALUES ('John', 'john@example.com')"
  }
}

{
  "tool": "exec_sql",
  "arguments": {
    "profile": "local",
    "sql": "CREATE TABLE temp_data (id INT PRIMARY KEY, value VARCHAR(100))"
  }
}

{
  "tool": "exec_sql",
  "arguments": {
    "profile": "local",
    "sql": "DESCRIBE users"
  }
}

Execute a query on production DMS

{
  "tool": "exec_sql",
  "arguments": {
    "profile": "gg",
    "sql": "SELECT COUNT(*) FROM orders WHERE created_at >= '2024-01-01'",
    "database": "forebay_msr",
    "limit_num": 100
  }
}

Set DMS cookie automatically

{
  "tool": "set_auth_cookie",
  "arguments": {
    "auto": true
  }
}

Create a new profile

{
  "tool": "create_profile",
  "arguments": {
    "profile": "staging",
    "host": "staging-db.example.com",
    "port": "3306",
    "user": "readonly",
    "password": "secret123",
    "database": "app_staging"
  }
}

Security Notes

• Database credentials are stored in environment configuration files
• Production access requires valid DMS cookies
• The MCP server executes mycli commands with appropriate timeouts
• SQL injection protection should be handled at the application level

Error Handling

The MCP server returns structured error responses:

• success: Always indicates if the operation succeeded
• error: Error message from mycli or system
• output: Raw output from mycli command for debugging

Integration with AI Agents

This MCP server is designed to work seamlessly with AI agents, providing:

1. Structured interfaces: All tools have well-defined schemas
2. Error transparency: Clear error reporting for debugging
3. Flexible output: Multiple output formats for different use cases
4. Environment isolation: Separate configurations for different deployment stages

Development

To extend the MCP server:

1. Add new tools in internal/mcp/server.go
2. Update the tool registration in the registerTools method
3. Test with the mycli command-line tool directly
4. Update this documentation

License

This MCP server follows the same license as the mycli project.