# Directory Structure
```
├── .github
│ └── workflows
│ ├── ci.yml
│ └── publish-docker.yml
├── .gitignore
├── CLAUDE.md
├── db.go
├── docker-compose.yml
├── Dockerfile
├── docs
│ ├── mcp-go-cheatsheet.md
│ ├── mcp-server-implementing-guide.md
│ ├── onetime
│ │ ├── 20250130-db-specific-server_ja.md
│ │ ├── 20250423-create-test_ja.md
│ │ ├── 20250427-use-template_ja.md
│ │ └── 20250810-docker-deployment_ja.md
│ └── requirements.md
├── e2e_test.go
├── go.mod
├── go.sum
├── handler_test.go
├── handler.go
├── LICENSE
├── main.go
├── Makefile
├── README_ja.md
├── README.md
├── RELEASE.md
├── release.sh
├── testdata
│ └── schema.sql
├── testhelper_test.go
└── view.go
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | ### Generated by gibo (https://github.com/simonwhitaker/gibo)
2 | ### https://raw.github.com/github/gitignore/4488915eec0b3a45b5c63ead28f286819c0917de/Go.gitignore
3 |
4 | # If you prefer the allow list template instead of the deny list, see community template:
5 | # https://github.com/github/gitignore/blob/main/community/Golang/Go.AllowList.gitignore
6 | #
7 | # Binaries for programs and plugins
8 | *.exe
9 | *.exe~
10 | *.dll
11 | *.so
12 | *.dylib
13 |
14 | # Test binary, built with `go test -c`
15 | *.test
16 |
17 | # Output of the go coverage tool, specifically when used with LiteIDE
18 | *.out
19 |
20 | # Dependency directories (remove the comment below to include it)
21 | # vendor/
22 |
23 | # Go workspace file
24 | go.work
25 |
26 | mysql-schema-explorer-mcp
27 |
28 | /.cursor
29 | /.claude/settings.local.json
30 |
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # MySQL Schema MCP Server
2 |
3 | This is a Model Context Protocol (MCP) server that provides compressed schema information for MySQL databases.
4 | This MCP server is useful when the schema dump file does not fit in the context size because you are using a large database schema.
5 |
6 | https://github.com/user-attachments/assets/0cecef84-cd70-4f84-95cb-01c6ec7c9ac7
7 |
8 | ## Provided Tools
9 |
10 | - List Tables (`list_tables`)
11 | - Lists all table information in the specified database. Includes table name, comment, primary key, unique key, and foreign key information.
12 | - Parameters
13 | - `dbName`: The name of the database to retrieve information from (not required when DB_NAME environment variable is set)
14 | - Describe Tables (`describe_tables`)
15 | - Displays detailed information for specific tables in the specified database. Provides formatted information such as column definitions, key constraints, and indexes.
16 | - Parameters
17 | - `dbName`: The name of the database to retrieve information from (not required when DB_NAME environment variable is set)
18 | - `tableNames`: An array of table names to retrieve detailed information for
19 |
20 | ## Quick Start
21 |
22 | 1. Configure mcp.json
23 |
24 | ```json
25 | {
26 | "mcpServers": {
27 | "mysql-schema-explorer-mcp": {
28 | "command": "docker",
29 | "args": ["run", "-i", "--rm", "--pull", "always", "--network=host",
30 | "-e", "DB_HOST=127.0.0.1",
31 | "-e", "DB_PORT=3306",
32 | "-e", "DB_USER=root",
33 | "-e", "DB_PASSWORD=your_password",
34 | "ghcr.io/shibayu36/mysql-schema-explorer-mcp:latest"
35 | ]
36 | }
37 | }
38 | }
39 | ```
40 |
41 | If using Claude Code:
42 |
43 | ```bash
44 | claude mcp add mysql-schema-explorer-mcp -- docker run -i --rm --pull always --network=host \
45 | -e DB_HOST=127.0.0.1 \
46 | -e DB_USER=root \
47 | -e DB_PASSWORD=your_password \
48 | -e DB_PORT=3306 \
49 | ghcr.io/shibayu36/mysql-schema-explorer-mcp:latest
50 | ```
51 |
52 | 2. Execute SQL generation using the agent
53 |
54 | Example: Using the structure of the ecshop database, list the names of the 3 most recently ordered products by the user shibayu36.
55 |
56 | ## Usage
57 |
58 | ### Fixing to a Specific Database
59 |
60 | When accessing only one database, you can set the `DB_NAME` environment variable to avoid specifying the database name each time.
61 |
62 | ```json
63 | {
64 | "mcpServers": {
65 | "mysql-schema-explorer-mcp": {
66 | "command": "docker",
67 | "args": ["run", "-i", "--rm", "--network=host",
68 | "-e", "DB_HOST=127.0.0.1",
69 | "-e", "DB_PORT=3306",
70 | "-e", "DB_USER=root",
71 | "-e", "DB_PASSWORD=your_password",
72 | "-e", "DB_NAME=ecshop",
73 | "ghcr.io/shibayu36/mysql-schema-explorer-mcp:latest"
74 | ]
75 | }
76 | }
77 | }
78 | ```
79 |
80 | ### Using Binary Instead of Docker
81 |
82 | If you have a Go development environment, you can also install and use the binary directly.
83 |
84 | 1. Install the command
85 |
86 | ```bash
87 | go install github.com/shibayu36/mysql-schema-explorer-mcp@latest
88 | ```
89 |
90 | 2. Configure mcp.json
91 |
92 | ```json
93 | {
94 | "mcpServers": {
95 | "mysql-schema-explorer-mcp": {
96 | "command": "/path/to/mysql-schema-explorer-mcp",
97 | "env": {
98 | "DB_HOST": "127.0.0.1",
99 | "DB_PORT": "3306",
100 | "DB_USER": "root",
101 | "DB_PASSWORD": "your_password"
102 | }
103 | }
104 | }
105 | }
106 | ```
107 |
```
--------------------------------------------------------------------------------
/CLAUDE.md:
--------------------------------------------------------------------------------
```markdown
1 | # CLAUDE.md
2 |
3 | This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.
4 |
5 | ## Commands
6 |
7 | ### Running Tests
8 | ```bash
9 | # Run all tests
10 | go test ./...
11 |
12 | # Run tests with verbose output
13 | go test -v ./...
14 |
15 | # Run a specific test
16 | go test -run TestListTables
17 | ```
18 |
19 | ### Building
20 | ```bash
21 | # Build the binary
22 | go build -o mysql-schema-explorer-mcp
23 |
24 | # Install globally
25 | go install
26 | ```
27 |
28 | ### Local Development with Docker
29 | ```bash
30 | # Start MySQL test instance
31 | docker-compose up -d
32 |
33 | # Set environment variables for local testing
34 | export DB_HOST=127.0.0.1
35 | export DB_PORT=3306
36 | export DB_USER=root
37 | export DB_PASSWORD=root
38 | ```
39 |
40 | ## Architecture
41 |
42 | This is a Model Context Protocol (MCP) server that provides MySQL schema information to LLM applications. The codebase follows a clean architecture pattern:
43 |
44 | - **main.go**: Entry point that sets up the MCP server, loads DB configuration from environment variables, and registers the two available tools (list_tables and describe_tables)
45 | - **handler.go**: Contains the business logic for handling MCP tool requests. Implements ListTables and DescribeTables methods
46 | - **db.go**: Database layer that handles MySQL connections and queries. Provides methods for fetching table metadata, columns, indexes, and constraints
47 | - **view.go**: Formatting layer that converts raw database schema information into human-readable output
48 |
49 | The server uses the mcp-go library (github.com/mark3labs/mcp-go) to implement the MCP protocol and communicates via stdio.
50 |
51 | ## Code Guidelines
52 |
53 | ### Language
54 | - Use English for all comments and documentation.
55 | - Use Japanese for all comments and documentation when the code is written in Japanese.
56 |
57 | ### Appropriate Use of Comments
58 | **⚠️ Important**: Keep comments to the absolute minimum. Do not comment on things that are immediately obvious from reading the code.
59 |
60 | **Limited cases where comments should be written**:
61 | 1. **Public API documentation**: For documenting public methods and interfaces
62 | 2. **Background context**: When there's important background information not evident from the code
63 | 3. **Complex logic explanation**: When complex code requires explanation to understand the intended behavior
64 | 4. **Large code section separation**: For 100+ line code blocks where sectioning with explanatory comments is beneficial
65 |
66 | ## MCP Tool Implementation
67 |
68 | When implementing new tools:
69 | 1. Define the tool in main.go using mcp.NewTool() with proper descriptions and parameters
70 | 2. Add the handler method to handler.go that processes the request
71 | 3. Implement any required database queries in db.go
72 | 4. Format the output in view.go if needed
73 |
74 | Tools must handle errors gracefully and return meaningful error messages through the MCP protocol.
75 |
```
--------------------------------------------------------------------------------
/docker-compose.yml:
--------------------------------------------------------------------------------
```yaml
1 | services:
2 | mysql:
3 | image: mysql:8
4 | environment:
5 | MYSQL_ROOT_PASSWORD: rootpass
6 | ports:
7 | - "13306:3306"
8 | volumes:
9 | - mysql-data:/var/lib/mysql
10 |
11 | volumes:
12 | mysql-data:
13 |
```
--------------------------------------------------------------------------------
/RELEASE.md:
--------------------------------------------------------------------------------
```markdown
1 | # Release Process
2 |
3 | ## Prerequisites
4 |
5 | - Ensure all changes are committed
6 | - Run tests locally: `go test ./...`
7 |
8 | ## Release Steps
9 |
10 | ```bash
11 | ./release.sh <version>
12 | ```
13 |
14 | Example:
15 | ```bash
16 | ./release.sh 1.0.1
17 | ```
18 |
19 | ## After Release
20 |
21 | 1. Create a GitHub release from the tag
22 | 2. Build binaries for different platforms if needed
23 | 3. Update changelog if maintained
```
--------------------------------------------------------------------------------
/.github/workflows/ci.yml:
--------------------------------------------------------------------------------
```yaml
1 | name: CI
2 | on:
3 | push:
4 | pull_request:
5 |
6 | jobs:
7 | test:
8 | runs-on: ubuntu-latest
9 | services:
10 | mysql:
11 | image: mysql:8
12 | env:
13 | MYSQL_ROOT_PASSWORD: rootpass
14 | ports:
15 | - 3306:3306
16 | steps:
17 | - uses: actions/checkout@v4
18 | - name: Setup Go
19 | uses: actions/setup-go@v5
20 | with:
21 | go-version-file: 'go.mod'
22 | - name: Wait for MySQL
23 | run: |
24 | n=0; until mysqladmin ping -h127.0.0.1 -uroot -prootpass > /dev/null 2>&1 || [ $n -ge 30 ]; do
25 | n=$((n+1)); echo "Waiting for MySQL... ($n)"; sleep 1;
26 | done
27 | if [ $n -ge 30 ]; then echo "MySQL not ready"; exit 1; fi
28 |
29 | - name: Check formatting
30 | run: |
31 | make fmt
32 | if [ -n "$(git status --porcelain)" ]; then
33 | echo "Code is not formatted. Please run 'make fmt'"
34 | git diff
35 | exit 1
36 | fi
37 |
38 | - name: Run tests
39 | env:
40 | DB_HOST: 127.0.0.1
41 | DB_PORT: 3306
42 | DB_USER: root
43 | DB_PASSWORD: rootpass
44 | run: go test -v ./... -timeout 5m
45 |
```
--------------------------------------------------------------------------------
/.github/workflows/publish-docker.yml:
--------------------------------------------------------------------------------
```yaml
1 | name: Build and Push Docker Image
2 |
3 | on:
4 | push:
5 | tags: ['v*']
6 | # Allow manual trigger for testing
7 | workflow_dispatch:
8 |
9 | env:
10 | REGISTRY: ghcr.io
11 | IMAGE_NAME: ${{ github.repository }}
12 |
13 | jobs:
14 | docker:
15 | runs-on: ubuntu-latest
16 | permissions:
17 | contents: read
18 | packages: write
19 |
20 | steps:
21 | - name: Checkout repository
22 | uses: actions/checkout@v4
23 |
24 | - name: Set up Docker Buildx
25 | uses: docker/setup-buildx-action@v3
26 |
27 | - name: Log in to the Container registry
28 | uses: docker/login-action@v3
29 | with:
30 | registry: ${{ env.REGISTRY }}
31 | username: ${{ github.actor }}
32 | password: ${{ secrets.GITHUB_TOKEN }}
33 |
34 | - name: Extract metadata
35 | id: meta
36 | uses: docker/metadata-action@v5
37 | with:
38 | images: ${{ env.REGISTRY }}/${{ env.IMAGE_NAME }}
39 | tags: |
40 | type=semver,pattern={{version}}
41 | type=raw,value=latest,enable={{is_default_branch}}
42 |
43 | - name: Build and push Docker image
44 | uses: docker/build-push-action@v6
45 | with:
46 | context: .
47 | platforms: linux/amd64,linux/arm64
48 | push: true
49 | tags: ${{ steps.meta.outputs.tags }}
50 | labels: ${{ steps.meta.outputs.labels }}
51 | cache-from: type=gha
52 | cache-to: type=gha,mode=max
53 |
```
--------------------------------------------------------------------------------
/release.sh:
--------------------------------------------------------------------------------
```bash
1 | #!/bin/bash
2 |
3 | set -e
4 |
5 | # Check if version is provided
6 | if [ -z "$1" ]; then
7 | echo "Usage: ./release.sh <version>"
8 | echo "Example: ./release.sh 1.0.1"
9 | exit 1
10 | fi
11 |
12 | VERSION=$1
13 |
14 | # Validate version format (basic semantic versioning)
15 | if ! [[ "$VERSION" =~ ^[0-9]+\.[0-9]+\.[0-9]+$ ]]; then
16 | echo "Error: Version must be in format X.Y.Z (e.g., 1.0.1)"
17 | exit 1
18 | fi
19 |
20 | # Check for uncommitted changes
21 | if ! git diff-index --quiet HEAD --; then
22 | echo "Error: You have uncommitted changes. Please commit or stash them first."
23 | exit 1
24 | fi
25 |
26 | # Update version constant in main.go
27 | echo "Updating version to $VERSION..."
28 | sed -i '' "s/const Version = \".*\"/const Version = \"$VERSION\"/" main.go
29 |
30 | # Run tests to ensure everything works
31 | echo "Running tests..."
32 | go test ./...
33 |
34 | # Build to ensure compilation
35 | echo "Building..."
36 | go build -o mysql-schema-explorer-mcp
37 |
38 | # Commit version change
39 | echo "Committing version change..."
40 | git add main.go
41 | git commit -m "Release v$VERSION"
42 |
43 | # Create annotated tag
44 | echo "Creating tag v$VERSION..."
45 | git tag -a "v$VERSION" -m "Release v$VERSION"
46 |
47 | # Push commits and tags
48 | echo "Pushing to remote..."
49 | git push
50 | git push --tags
51 |
52 | echo "✅ Release v$VERSION completed successfully!"
53 | echo ""
54 | echo "Next steps:"
55 | echo "1. Create a GitHub release from the tag"
56 | echo "2. Build binaries for different platforms if needed"
57 | echo "3. Update any documentation or changelogs"
```
--------------------------------------------------------------------------------
/testdata/schema.sql:
--------------------------------------------------------------------------------
```sql
1 | -- users table
2 | CREATE TABLE users (
3 | id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'User system ID',
4 | email VARCHAR(255) NOT NULL UNIQUE COMMENT 'Email address',
5 | username VARCHAR(255) NOT NULL UNIQUE COMMENT 'Username',
6 | tenant_id INT NOT NULL COMMENT 'Tenant ID',
7 | employee_id INT NOT NULL COMMENT 'Employee ID',
8 | UNIQUE KEY uk_tenant_employee (tenant_id, employee_id) -- Composite unique key
9 | ) COMMENT='User information';
10 |
11 | -- orders table (for reference from order_items)
12 | CREATE TABLE orders (
13 | id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Order ID',
14 | user_id INT NOT NULL COMMENT 'User ID (FK)',
15 | order_date DATETIME COMMENT 'Order date',
16 | INDEX(id), -- Index needed for referencing in composite foreign key
17 | FOREIGN KEY fk_user (user_id) REFERENCES users(id)
18 | ) COMMENT='Order header';
19 |
20 | -- products table
21 | CREATE TABLE products (
22 | product_code VARCHAR(50) PRIMARY KEY COMMENT 'Product code (Primary Key)',
23 | maker_code VARCHAR(50) NOT NULL COMMENT 'Maker code',
24 | internal_code VARCHAR(50) NOT NULL COMMENT 'Internal product code',
25 | product_name VARCHAR(255) COMMENT 'Product name',
26 | UNIQUE KEY uk_maker_internal (maker_code, internal_code), -- Composite unique key
27 | INDEX idx_product_name (product_name),
28 | INDEX idx_maker_product_name (maker_code, product_name)
29 | ) COMMENT='Product master';
30 |
31 | -- order_items table
32 | CREATE TABLE order_items (
33 | order_id INT NOT NULL COMMENT 'Order ID (FK)',
34 | item_seq INT NOT NULL COMMENT 'Order item sequence number',
35 | product_maker VARCHAR(50) NOT NULL COMMENT 'Product maker code (FK)',
36 | product_internal_code VARCHAR(50) NOT NULL COMMENT 'Product internal code (FK)',
37 | quantity INT NOT NULL COMMENT 'Quantity',
38 | PRIMARY KEY (order_id, item_seq), -- Composite primary key
39 | UNIQUE KEY uk_order_product (order_id, product_maker, product_internal_code), -- Composite unique key (prevent duplicate products in one order)
40 | FOREIGN KEY fk_order (order_id) REFERENCES orders(id) ON DELETE CASCADE, -- Single foreign key (delete items if order is deleted)
41 | FOREIGN KEY fk_product (product_maker, product_internal_code) REFERENCES products(maker_code, internal_code) -- Composite foreign key
42 | ) COMMENT='Order details';
43 |
```
--------------------------------------------------------------------------------
/testhelper_test.go:
--------------------------------------------------------------------------------
```go
1 | package main
2 |
3 | import (
4 | "database/sql"
5 | "fmt"
6 | "os"
7 | "strings"
8 | "testing"
9 |
10 | _ "github.com/go-sql-driver/mysql"
11 | )
12 |
13 | // createTestDBConfig creates DB settings for testing. Uses default values if environment variables are not set.
14 | func createTestDBConfig(t *testing.T) DBConfig {
15 | host := os.Getenv("DB_HOST")
16 | if host == "" {
17 | host = "localhost"
18 | }
19 |
20 | port := os.Getenv("DB_PORT")
21 | if port == "" {
22 | port = "13306"
23 | }
24 |
25 | user := os.Getenv("DB_USER")
26 | if user == "" {
27 | user = "root"
28 | }
29 |
30 | password := os.Getenv("DB_PASSWORD")
31 | if password == "" {
32 | password = "rootpass"
33 | }
34 |
35 | return DBConfig{
36 | Host: host,
37 | Port: port,
38 | User: user,
39 | Password: password,
40 | }
41 | }
42 |
43 | const testDBName = "test_mysql_schema_explorer_mcp"
44 |
45 | // setupTestDB creates a test DB and returns the connection.
46 | // It deletes the DB by calling the cleanup function after the test finishes.
47 | func setupTestDB(t *testing.T, schemaFile string) *sql.DB {
48 | t.Helper()
49 |
50 | config := createTestDBConfig(t)
51 |
52 | dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/",
53 | config.User, config.Password, config.Host, config.Port)
54 | db, err := sql.Open("mysql", dsn)
55 | if err != nil {
56 | t.Fatalf("Failed to connect to MySQL: %v", err)
57 | }
58 |
59 | // Create DB (delete if exists)
60 | _, err = db.Exec(fmt.Sprintf("DROP DATABASE IF EXISTS `%s`", testDBName))
61 | if err != nil {
62 | db.Close()
63 | t.Fatalf("Failed to drop database: %v", err)
64 | }
65 |
66 | _, err = db.Exec(fmt.Sprintf("CREATE DATABASE `%s`", testDBName))
67 | if err != nil {
68 | db.Close()
69 | t.Fatalf("Failed to create database: %v", err)
70 | }
71 |
72 | // Apply schema
73 | {
74 | applyDB, err := sql.Open("mysql", dsn+testDBName)
75 | if err != nil {
76 | t.Fatalf("Failed to connect to test DB: %v", err)
77 | }
78 | defer applyDB.Close()
79 |
80 | schemaBytes, err := os.ReadFile(schemaFile)
81 | if err != nil {
82 | t.Fatalf("Failed to read schema file: %v", err)
83 | }
84 | schema := string(schemaBytes)
85 |
86 | // Split and execute SQL statements
87 | statements := strings.Split(schema, ";")
88 | for _, stmt := range statements {
89 | stmt = strings.TrimSpace(stmt)
90 | if stmt == "" {
91 | continue
92 | }
93 |
94 | _, err := applyDB.Exec(stmt)
95 | if err != nil {
96 | t.Logf("Failed to execute SQL: %s", stmt)
97 | t.Fatalf("Failed to apply schema: %v", err)
98 | }
99 | }
100 | }
101 |
102 | t.Cleanup(func() {
103 | _, _ = db.Exec(fmt.Sprintf("DROP DATABASE IF EXISTS `%s`", testDBName))
104 | db.Close()
105 | })
106 |
107 | return db
108 | }
109 |
```
--------------------------------------------------------------------------------
/main.go:
--------------------------------------------------------------------------------
```go
1 | package main
2 |
3 | import (
4 | "fmt"
5 | "log"
6 | "os"
7 |
8 | _ "github.com/go-sql-driver/mysql"
9 | "github.com/mark3labs/mcp-go/mcp"
10 | "github.com/mark3labs/mcp-go/server"
11 | )
12 |
13 | const Version = "1.1.1"
14 |
15 | func main() {
16 | dbConfig, err := loadDBConfig()
17 | if err != nil {
18 | log.Fatalf("Failed to load configuration: %v", err)
19 | }
20 |
21 | sqlDB, err := connectDB(dbConfig)
22 | if err != nil {
23 | log.Fatalf("Failed to connect to database: %v", err)
24 | }
25 | defer sqlDB.Close()
26 |
27 | if err := sqlDB.Ping(); err != nil {
28 | log.Fatalf("Failed to ping database: %v", err)
29 | }
30 |
31 | // Initialize DB layer and handler
32 | db := NewDB(sqlDB)
33 | fixedDBName := os.Getenv("DB_NAME")
34 | handler := NewHandler(db, fixedDBName)
35 |
36 | s := server.NewMCPServer(
37 | "mysql-schema-mcp",
38 | Version,
39 | )
40 |
41 | // Build list_tables tool options
42 | listTablesOpts := []mcp.ToolOption{
43 | mcp.WithDescription("Returns a list of table information in the MySQL database."),
44 | }
45 | if fixedDBName == "" {
46 | listTablesOpts = append(listTablesOpts, mcp.WithString("dbName",
47 | mcp.Required(),
48 | mcp.Description("The name of the database to retrieve information from."),
49 | ))
50 | }
51 | s.AddTool(
52 | mcp.NewTool("list_tables", listTablesOpts...),
53 | handler.ListTables,
54 | )
55 |
56 | // Build describe_tables tool options
57 | describeTablesOpts := []mcp.ToolOption{
58 | mcp.WithDescription("Returns detailed information for the specified tables."),
59 | }
60 | if fixedDBName == "" {
61 | describeTablesOpts = append(describeTablesOpts, mcp.WithString("dbName",
62 | mcp.Required(),
63 | mcp.Description("The name of the database to retrieve information from."),
64 | ))
65 | }
66 | describeTablesOpts = append(describeTablesOpts, mcp.WithArray(
67 | "tableNames",
68 | mcp.Items(
69 | map[string]interface{}{
70 | "type": "string",
71 | },
72 | ),
73 | mcp.Required(),
74 | mcp.Description("The names of the tables to retrieve detailed information for (multiple names can be specified)."),
75 | ))
76 | s.AddTool(
77 | mcp.NewTool("describe_tables", describeTablesOpts...),
78 | handler.DescribeTables,
79 | )
80 |
81 | if err := server.ServeStdio(s); err != nil {
82 | fmt.Printf("Server error: %v\n", err)
83 | }
84 | }
85 |
86 | func loadDBConfig() (DBConfig, error) {
87 | host := os.Getenv("DB_HOST")
88 | if host == "" {
89 | host = "localhost"
90 | }
91 |
92 | port := os.Getenv("DB_PORT")
93 | if port == "" {
94 | port = "3306"
95 | }
96 |
97 | user := os.Getenv("DB_USER")
98 | if user == "" {
99 | return DBConfig{}, fmt.Errorf("DB_USER environment variable is not set")
100 | }
101 |
102 | password := os.Getenv("DB_PASSWORD")
103 |
104 | return DBConfig{
105 | Host: host,
106 | Port: port,
107 | User: user,
108 | Password: password,
109 | }, nil
110 | }
111 |
```
--------------------------------------------------------------------------------
/view.go:
--------------------------------------------------------------------------------
```go
1 | package main
2 |
3 | import (
4 | "fmt"
5 | "strings"
6 | "text/template"
7 | )
8 |
9 | // ListTablesData is the data structure passed to the ListTables template
10 | type ListTablesData struct {
11 | DBName string
12 | Tables []TableSummary
13 | }
14 |
15 | // listTablesTemplate is the output format for ListTables
16 | const listTablesTemplate = `Tables in database "{{.DBName}}" (Total: {{len .Tables}})
17 | Format: Table Name - Table Comment [PK: Primary Key] [UK: Unique Key 1; Unique Key 2...] [FK: Foreign Key -> Referenced Table.Column; ...]
18 | * Composite keys (keys composed of multiple columns) are grouped in parentheses: (col1, col2)
19 | * Multiple different key constraints are separated by semicolons: key1; key2
20 |
21 | {{range .Tables -}}
22 | - {{.Name}} - {{.Comment}}{{if len .PK}} [PK: {{formatPK .PK}}]{{end}}{{if len .UK}} [UK: {{formatUK .UK}}]{{end}}{{if len .FK}} [FK: {{formatFK .FK}}]{{end}}
23 | {{end -}}
24 | `
25 |
26 | // TableDetail holds detailed information for individual tables (uses types from db.go)
27 | type TableDetail struct {
28 | Name string
29 | Comment string
30 | Columns []ColumnInfo
31 | PrimaryKeys []string
32 | UniqueKeys []UniqueKey
33 | ForeignKeys []ForeignKey
34 | Indexes []IndexInfo
35 | }
36 |
37 | // describeTableDetailTemplate is the output format for describe_tables
38 | const describeTableDetailTemplate = `# Table: {{.Name}}{{if .Comment}} - {{.Comment}}{{end}}
39 |
40 | ## Columns{{range .Columns}}
41 | {{formatColumn .}}{{end}}
42 |
43 | ## Key Information{{if .PrimaryKeys}}
44 | [PK: {{formatPK .PrimaryKeys}}]{{end}}{{if .UniqueKeys}}
45 | [UK: {{formatUK .UniqueKeys}}]{{end}}{{if .ForeignKeys}}
46 | [FK: {{formatFK .ForeignKeys}}]{{end}}{{if .Indexes}}
47 | [INDEX: {{formatIndex .Indexes}}]{{end}}
48 | `
49 |
50 | var funcMap = template.FuncMap{
51 | "formatPK": formatPK,
52 | "formatUK": formatUK,
53 | "formatFK": formatFK,
54 | "formatColumn": formatColumn,
55 | "formatIndex": formatIndex,
56 | }
57 |
58 | // formatPK formats primary key information
59 | func formatPK(pk []string) string {
60 | if len(pk) == 0 {
61 | return ""
62 | }
63 | pkStr := strings.Join(pk, ", ")
64 | if len(pk) > 1 {
65 | pkStr = fmt.Sprintf("(%s)", pkStr)
66 | }
67 | return pkStr
68 | }
69 |
70 | // formatUK formats unique key information
71 | func formatUK(uk []UniqueKey) string {
72 | if len(uk) == 0 {
73 | return ""
74 | }
75 | var ukInfo []string
76 | for _, k := range uk {
77 | if len(k.Columns) > 1 {
78 | ukInfo = append(ukInfo, fmt.Sprintf("(%s)", strings.Join(k.Columns, ", ")))
79 | } else {
80 | ukInfo = append(ukInfo, strings.Join(k.Columns, ", "))
81 | }
82 | }
83 | return strings.Join(ukInfo, "; ")
84 | }
85 |
86 | // formatFK formats foreign key information
87 | func formatFK(fk []ForeignKey) string {
88 | if len(fk) == 0 {
89 | return ""
90 | }
91 | var fkInfo []string
92 | for _, k := range fk {
93 | colStr := strings.Join(k.Columns, ", ")
94 | refColStr := strings.Join(k.RefColumns, ", ")
95 |
96 | if len(k.Columns) > 1 {
97 | colStr = fmt.Sprintf("(%s)", colStr)
98 | }
99 |
100 | if len(k.RefColumns) > 1 {
101 | refColStr = fmt.Sprintf("(%s)", refColStr)
102 | }
103 |
104 | fkInfo = append(fkInfo, fmt.Sprintf("%s -> %s.%s",
105 | colStr,
106 | k.RefTable,
107 | refColStr))
108 | }
109 | return strings.Join(fkInfo, "; ")
110 | }
111 |
112 | // formatColumn formats column information
113 | func formatColumn(col ColumnInfo) string {
114 | nullable := "NOT NULL"
115 | if col.IsNullable == "YES" {
116 | nullable = "NULL"
117 | }
118 |
119 | defaultValue := ""
120 | if col.Default.Valid {
121 | defaultValue = fmt.Sprintf(" DEFAULT %s", col.Default.String)
122 | }
123 |
124 | comment := ""
125 | if col.Comment != "" {
126 | comment = fmt.Sprintf(" [%s]", col.Comment)
127 | }
128 |
129 | return fmt.Sprintf("- %s: %s %s%s%s",
130 | col.Name, col.Type, nullable, defaultValue, comment)
131 | }
132 |
133 | func formatIndex(idx []IndexInfo) string {
134 | if len(idx) == 0 {
135 | return ""
136 | }
137 | var idxInfo []string
138 | for _, i := range idx {
139 | if len(i.Columns) > 1 {
140 | idxInfo = append(idxInfo, fmt.Sprintf("(%s)", strings.Join(i.Columns, ", ")))
141 | } else {
142 | idxInfo = append(idxInfo, strings.Join(i.Columns, ", "))
143 | }
144 | }
145 | return strings.Join(idxInfo, "; ")
146 | }
147 |
```
--------------------------------------------------------------------------------
/docs/requirements.md:
--------------------------------------------------------------------------------
```markdown
1 | # MySQL Schema MCP Server Requirements
2 |
3 | ## Overview
4 | This project implements a server using the Model Context Protocol (MCP) to provide MySQL database schema information. LLM clients like Claude can connect to this server. The server offers tools to get information about the database schema.
5 |
6 | ## Environment Variables
7 | - **DB_HOST**: Database host name
8 | - **DB_PORT**: Database port number
9 | - **DB_USER**: Database username
10 | - **DB_PASSWORD**: Database password
11 |
12 | ## MCP Server Tools
13 |
14 | 1. **Tool: `list_tables`**
15 | - Description: Returns a list of all table names in the specified database.
16 | - Arguments: `dbName` (string) - The name of the database to get information from.
17 | - Return Value: A list of table names, table comments, and key information (in text format).
18 | - Output Format:
19 | ```text
20 | Tables in database "DB_NAME" (Total: X)
21 | Format: Table Name - Table Comment [PK: Primary Key] [UK: Unique Key 1; Unique Key 2...] [FK: Foreign Key -> Referenced Table.Column; ...]
22 | * Composite keys (keys made of multiple columns) are grouped in parentheses: (col1, col2)
23 | * Multiple different key constraints are separated by semicolons: key1; key2
24 |
25 | - users - User information [PK: id] [UK: email; username] [FK: role_id -> roles.id; department_id -> departments.id]
26 | - posts - Post information [PK: id] [UK: slug] [FK: user_id -> users.id; category_id -> categories.id]
27 | - order_items - Order items [PK: (order_id, item_id)] [FK: (order_id, item_id) -> orders.(id, item_id); product_id -> products.id]
28 | ```
29 |
30 | 2. **Tool: `describe_tables`**
31 | - Description: Returns detailed information for the specified tables, such as column info, indexes, and foreign key constraints.
32 | - Arguments:
33 | - `dbName` (string) - The name of the database to get information from.
34 | - `tableNames` (array of strings) - The names of the tables to get detailed information for (you can specify multiple names).
35 | - Return Value: Formatted text with detailed information for each table.
36 | - Output Format:
37 | ```text
38 | # Table: order_items - Order Items
39 |
40 | ## Columns
41 | - order_id: int(11) NOT NULL [Order ID]
42 | - item_id: int(11) NOT NULL [Item ID]
43 | - product_id: int(11) NOT NULL [Product ID]
44 | - quantity: int(11) NOT NULL [Quantity]
45 | - price: decimal(10,2) NOT NULL [Price]
46 | - user_id: int(11) NOT NULL [User ID]
47 |
48 | ## Key Information
49 | [PK: (order_id, item_id)]
50 | [UK: (user_id, product_id)]
51 | [FK: (order_id, item_id) -> orders.(id, item_id); product_id -> products.id; user_id -> users.id]
52 | [INDEX: price; quantity]
53 |
54 | ---
55 |
56 | # Table: users - User Information
57 |
58 | ## Columns
59 | - id: int(11) NOT NULL [User ID]
60 | - username: varchar(50) NOT NULL [Username]
61 | - email: varchar(100) NOT NULL [Email Address]
62 | - password: varchar(255) NOT NULL [Password]
63 | - created_at: timestamp NULL DEFAULT CURRENT_TIMESTAMP [Created At]
64 |
65 | ## Key Information
66 | [PK: id]
67 | [UK: email; username]
68 | [INDEX: created_at]
69 | ```
70 |
71 | If you specify multiple tables, a separator line (`---`) will be inserted between each table's information.
72 |
73 | ## Implementation Steps
74 |
75 | 1. **Project Setup**
76 | - Install the MCP library.
77 | - Install necessary dependencies (like MySQL client library).
78 |
79 | 2. **MCP Server Initialization**
80 | - Create the server instance and set its name.
81 |
82 | 3. **Load Environment Variables**
83 | - Read environment variables when the server starts to set up database connection info.
84 |
85 | 4. **Database Connection Helper**
86 | - Implement a helper function to manage database connections.
87 | - The database name will be received as an argument in tool calls.
88 |
89 | 5. **Implement Tools**
90 | - Implement each tool function.
91 | - Run appropriate database queries within the tools and format the results.
92 |
93 | 6. **Run the Server**
94 | - Set up the server to communicate with the client using standard input/output (stdio).
95 |
96 | ## Progress
97 |
98 | - [x] Project Setup
99 | - [x] MCP Server Initialization
100 | - [x] Load Environment Variables
101 | - [x] Database Connection Helper Implementation
102 | - [x] Implement `list_tables` tool
103 | - [x] Implement `describe_tables` tool
104 | - [x] Receive DB_NAME as a tool call argument, not an environment variable
105 | - [ ] Adjust security settings
106 |
```
--------------------------------------------------------------------------------
/handler_test.go:
--------------------------------------------------------------------------------
```go
1 | package main
2 |
3 | import (
4 | "testing"
5 |
6 | "github.com/mark3labs/mcp-go/mcp"
7 | "github.com/stretchr/testify/assert"
8 | "github.com/stretchr/testify/require"
9 | )
10 |
11 | func TestListTables(t *testing.T) {
12 | dbConn := setupTestDB(t, "testdata/schema.sql")
13 |
14 | db := NewDB(dbConn)
15 | handler := NewHandler(db, "")
16 |
17 | ctx := t.Context()
18 | req := mcp.CallToolRequest{
19 | Params: struct {
20 | Name string `json:"name"`
21 | Arguments map[string]interface{} `json:"arguments,omitempty"`
22 | Meta *struct {
23 | ProgressToken mcp.ProgressToken `json:"progressToken,omitempty"`
24 | } `json:"_meta,omitempty"`
25 | }{
26 | // Name: "ListTables", // Set tool name if necessary
27 | Arguments: map[string]interface{}{
28 | "dbName": testDBName,
29 | },
30 | },
31 | }
32 |
33 | // --- Act ---
34 | result, err := handler.ListTables(ctx, req)
35 |
36 | // --- Assert ---
37 | require.NoError(t, err, "handler.ListTables should not return an error")
38 | require.NotNil(t, result, "handler.ListTables should return a result")
39 |
40 | expectedOutput := `Tables in database "` + testDBName + `" (Total: 4)
41 | Format: Table Name - Table Comment [PK: Primary Key] [UK: Unique Key 1; Unique Key 2...] [FK: Foreign Key -> Referenced Table.Column; ...]
42 | * Composite keys (keys composed of multiple columns) are grouped in parentheses: (col1, col2)
43 | * Multiple different key constraints are separated by semicolons: key1; key2
44 |
45 | - order_items - Order details [PK: (order_id, item_seq)] [UK: (order_id, product_maker, product_internal_code)] [FK: order_id -> orders.id; (product_maker, product_internal_code) -> products.(maker_code, internal_code)]
46 | - orders - Order header [PK: id] [FK: user_id -> users.id]
47 | - products - Product master [PK: product_code] [UK: (maker_code, internal_code)]
48 | - users - User information [PK: id] [UK: email; (tenant_id, employee_id); username]
49 | `
50 | textContent := result.Content[0].(mcp.TextContent).Text
51 | assert.Equal(t, expectedOutput, textContent, "Output content should match the expected format")
52 | }
53 |
54 | func TestDescribeTables(t *testing.T) {
55 | // --- Arrange ---
56 | dbConn := setupTestDB(t, "testdata/schema.sql") // Prepare test DB and schema
57 |
58 | db := NewDB(dbConn)
59 | handler := NewHandler(db, "")
60 |
61 | ctx := t.Context()
62 | req := mcp.CallToolRequest{
63 | Params: struct {
64 | Name string `json:"name"`
65 | Arguments map[string]interface{} `json:"arguments,omitempty"`
66 | Meta *struct {
67 | ProgressToken mcp.ProgressToken `json:"progressToken,omitempty"`
68 | } `json:"_meta,omitempty"`
69 | }{
70 | // Name: "DescribeTables", // Set tool name if necessary
71 | Arguments: map[string]interface{}{
72 | "dbName": testDBName,
73 | "tableNames": []interface{}{"users", "products", "order_items"}, // Specify multiple tables
74 | },
75 | },
76 | }
77 |
78 | expectedOutput := `# Table: users - User information
79 |
80 | ## Columns
81 | - id: int NOT NULL [User system ID]
82 | - email: varchar(255) NOT NULL [Email address]
83 | - username: varchar(255) NOT NULL [Username]
84 | - tenant_id: int NOT NULL [Tenant ID]
85 | - employee_id: int NOT NULL [Employee ID]
86 |
87 | ## Key Information
88 | [PK: id]
89 | [UK: email; (tenant_id, employee_id); username]
90 |
91 | ---
92 |
93 | # Table: products - Product master
94 |
95 | ## Columns
96 | - product_code: varchar(50) NOT NULL [Product code (Primary Key)]
97 | - maker_code: varchar(50) NOT NULL [Maker code]
98 | - internal_code: varchar(50) NOT NULL [Internal product code]
99 | - product_name: varchar(255) NULL [Product name]
100 |
101 | ## Key Information
102 | [PK: product_code]
103 | [UK: (maker_code, internal_code)]
104 | [INDEX: (maker_code, product_name); product_name]
105 |
106 | ---
107 |
108 | # Table: order_items - Order details
109 |
110 | ## Columns
111 | - order_id: int NOT NULL [Order ID (FK)]
112 | - item_seq: int NOT NULL [Order item sequence number]
113 | - product_maker: varchar(50) NOT NULL [Product maker code (FK)]
114 | - product_internal_code: varchar(50) NOT NULL [Product internal code (FK)]
115 | - quantity: int NOT NULL [Quantity]
116 |
117 | ## Key Information
118 | [PK: (order_id, item_seq)]
119 | [UK: (order_id, product_maker, product_internal_code)]
120 | [FK: order_id -> orders.id; (product_maker, product_internal_code) -> products.(maker_code, internal_code)]
121 | [INDEX: (product_maker, product_internal_code)]
122 | `
123 |
124 | // --- Act ---
125 | result, err := handler.DescribeTables(ctx, req)
126 |
127 | // --- Assert ---
128 | require.NoError(t, err, "handler.DescribeTables should not return an error")
129 | require.NotNil(t, result, "handler.DescribeTables should return a result")
130 | require.Len(t, result.Content, 1, "Result should contain one content item")
131 | require.IsType(t, mcp.TextContent{}, result.Content[0], "Content item should be TextContent")
132 |
133 | textContent := result.Content[0].(mcp.TextContent).Text
134 |
135 | assert.Equal(t, expectedOutput, textContent, "Output content should match the expected format")
136 | }
137 |
```
--------------------------------------------------------------------------------
/handler.go:
--------------------------------------------------------------------------------
```go
1 | package main
2 |
3 | import (
4 | "bytes"
5 | "context"
6 | "fmt"
7 | "text/template"
8 |
9 | "github.com/mark3labs/mcp-go/mcp"
10 | )
11 |
12 | // Handler struct implements the MCP handler
13 | type Handler struct {
14 | db *DB
15 | fixedDBName string
16 | }
17 |
18 | func NewHandler(db *DB, fixedDBName string) *Handler {
19 | return &Handler{db: db, fixedDBName: fixedDBName}
20 | }
21 |
22 | // getDatabaseName extracts the database name from the request or returns the fixed DB name
23 | func (h *Handler) getDatabaseName(request mcp.CallToolRequest) (string, error) {
24 | // Use fixed DB name if set
25 | if h.fixedDBName != "" {
26 | return h.fixedDBName, nil
27 | }
28 |
29 | // Otherwise get from request
30 | dbNameRaw, ok := request.Params.Arguments["dbName"]
31 | if !ok {
32 | return "", fmt.Errorf("database name is not specified")
33 | }
34 |
35 | dbName, ok := dbNameRaw.(string)
36 | if !ok || dbName == "" {
37 | return "", fmt.Errorf("database name is not specified correctly")
38 | }
39 |
40 | return dbName, nil
41 | }
42 |
43 | // ListTables returns summary information for all tables
44 | func (h *Handler) ListTables(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
45 | dbName, err := h.getDatabaseName(request)
46 | if err != nil {
47 | return mcp.NewToolResultError(err.Error()), nil
48 | }
49 |
50 | // Get table information
51 | tables, err := h.db.FetchAllTableSummaries(ctx, dbName)
52 | if err != nil {
53 | return mcp.NewToolResultError(fmt.Sprintf("Failed to get table information: %v", err)), nil
54 | }
55 |
56 | // No tables found
57 | if len(tables) == 0 {
58 | return mcp.NewToolResultText("No tables exist in the database."), nil
59 | }
60 |
61 | // Create output
62 | var output bytes.Buffer
63 | {
64 | tmpl, err := template.New("listTables").Funcs(funcMap).Parse(listTablesTemplate)
65 | if err != nil {
66 | return mcp.NewToolResultError(fmt.Sprintf("Failed to parse template: %v", err)), nil
67 | }
68 |
69 | if err := tmpl.Execute(&output, ListTablesData{
70 | DBName: dbName,
71 | Tables: tables,
72 | }); err != nil {
73 | return mcp.NewToolResultError(fmt.Sprintf("Failed to execute template: %v", err)), nil
74 | }
75 | }
76 |
77 | return mcp.NewToolResultText(output.String()), nil
78 | }
79 |
80 | // DescribeTables is a handler method that returns detailed information for the specified tables
81 | func (h *Handler) DescribeTables(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
82 | dbName, err := h.getDatabaseName(request)
83 | if err != nil {
84 | return mcp.NewToolResultError(err.Error()), nil
85 | }
86 |
87 | // Create list of table names
88 | tableNamesRaw, ok := request.Params.Arguments["tableNames"]
89 | if !ok {
90 | return mcp.NewToolResultError("Table names are not specified"), nil
91 | }
92 | tableNamesInterface, ok := tableNamesRaw.([]interface{})
93 | if !ok || len(tableNamesInterface) == 0 {
94 | return mcp.NewToolResultError("Array of table names is not specified correctly"), nil
95 | }
96 | var tableNames []string
97 | for _, v := range tableNamesInterface {
98 | if tableName, ok := v.(string); ok && tableName != "" {
99 | tableNames = append(tableNames, tableName)
100 | }
101 | }
102 | if len(tableNames) == 0 {
103 | return mcp.NewToolResultError("No valid table names are specified"), nil
104 | }
105 |
106 | allTables, err := h.db.FetchAllTableSummaries(ctx, dbName)
107 | if err != nil {
108 | return mcp.NewToolResultError(fmt.Sprintf("Failed to get table information: %v", err)), nil
109 | }
110 |
111 | // Prepare output
112 | var output bytes.Buffer
113 | tmpl, err := template.New("describeTableDetail").Funcs(funcMap).Parse(describeTableDetailTemplate)
114 | if err != nil {
115 | return mcp.NewToolResultError(fmt.Sprintf("Failed to parse template: %v", err)), nil
116 | }
117 |
118 | // Get information for all tables
119 | for i, tableName := range tableNames {
120 | // Add a separator line before the second and subsequent tables
121 | if i > 0 {
122 | output.WriteString("\n---\n\n")
123 | }
124 |
125 | // Find the specified table
126 | var tableInfo TableSummary
127 | var tableFound bool
128 | for _, t := range allTables {
129 | if t.Name == tableName {
130 | tableInfo = t
131 | tableFound = true
132 | break
133 | }
134 | }
135 |
136 | if !tableFound {
137 | output.WriteString(fmt.Sprintf("# Table: %s\nTable not found\n", tableName))
138 | continue
139 | }
140 |
141 | // Get table detail information
142 | primaryKeys, err := h.db.FetchPrimaryKeys(ctx, dbName, tableName)
143 | if err != nil {
144 | return mcp.NewToolResultError(fmt.Sprintf("Failed to get primary key information: %v", err)), nil
145 | }
146 |
147 | uniqueKeys, err := h.db.FetchUniqueKeys(ctx, dbName, tableName)
148 | if err != nil {
149 | return mcp.NewToolResultError(fmt.Sprintf("Failed to get unique key information: %v", err)), nil
150 | }
151 |
152 | foreignKeys, err := h.db.FetchForeignKeys(ctx, dbName, tableName)
153 | if err != nil {
154 | return mcp.NewToolResultError(fmt.Sprintf("Failed to get foreign key information: %v", err)), nil
155 | }
156 |
157 | columns, err := h.db.FetchTableColumns(ctx, dbName, tableName)
158 | if err != nil {
159 | return mcp.NewToolResultError(fmt.Sprintf("Failed to get column information: %v", err)), nil
160 | }
161 |
162 | indexes, err := h.db.FetchTableIndexes(ctx, dbName, tableName)
163 | if err != nil {
164 | return mcp.NewToolResultError(fmt.Sprintf("Failed to get index information: %v", err)), nil
165 | }
166 |
167 | // Create data to pass to the template
168 | tableDetail := TableDetail{
169 | Name: tableName,
170 | Comment: tableInfo.Comment,
171 | Columns: columns,
172 | PrimaryKeys: primaryKeys,
173 | UniqueKeys: uniqueKeys,
174 | ForeignKeys: foreignKeys,
175 | Indexes: indexes,
176 | }
177 |
178 | // Execute the template and write to the buffer
179 | if err := tmpl.Execute(&output, tableDetail); err != nil {
180 | return mcp.NewToolResultError(fmt.Sprintf("Failed to execute template: %v", err)), nil
181 | }
182 | }
183 |
184 | return mcp.NewToolResultText(output.String()), nil
185 | }
186 |
```
--------------------------------------------------------------------------------
/docs/mcp-server-implementing-guide.md:
--------------------------------------------------------------------------------
```markdown
1 | # Model Context Protocol (MCP) Server Implementation Guide
2 |
3 | ## What is MCP?
4 |
5 | The Model Context Protocol (MCP) is an open protocol. It connects AI language models (LLMs) with external data sources and tools in a standard way. It acts like a plugin system between LLM applications and external tools, giving seamless access to data sources.
6 |
7 | ## Basic Architecture
8 |
9 | MCP uses a client-server model:
10 |
11 | - **MCP Server**: A lightweight program that provides access to data sources or tools.
12 | - **MCP Host/Client**: An LLM application like Claude Desktop. It connects to MCP servers to use their features.
13 |
14 | ## Main Features of an MCP Server
15 |
16 | MCP servers can offer three main types of features:
17 |
18 | 1. **Tools**: Functions that the LLM can call (with user approval).
19 | 2. **Resources**: Data in a file-like format that the client can read (like API responses or file content).
20 | 3. **Prompts**: Standard templates that help with specific tasks.
21 |
22 | ## Communication Protocols
23 |
24 | MCP servers support these communication methods:
25 |
26 | - **Standard Input/Output (stdio)**: A simple method suitable for local development.
27 | - **Server-Sent Events (SSE)**: A more flexible method for distributed teams.
28 | - **WebSockets**: For real-time, two-way communication.
29 |
30 | ## What You Need to Build
31 |
32 | ### 1. Basic Structure
33 |
34 | To implement an MCP server, you need these elements:
35 |
36 | - Server initialization and transport setup.
37 | - Definitions for tools, resources, or prompts.
38 | - Implementation of request handlers.
39 | - The main function to run the server.
40 |
41 | ### 2. Server Configuration
42 |
43 | The server configuration includes this information:
44 |
45 | ```json
46 | {
47 | "mcpServers": {
48 | "myserver": {
49 | "command": "command_to_execute",
50 | "args": ["arg1", "arg2"],
51 | "env": {
52 | "ENV_VAR_NAME": "value"
53 | }
54 | }
55 | }
56 | }
57 | ```
58 |
59 | ## How to Implement in Different Languages
60 |
61 | You can implement MCP servers in various programming languages:
62 |
63 | ### Python
64 |
65 | ```python
66 | # Install necessary libraries
67 | # pip install mcp[cli]
68 |
69 | import asyncio
70 | import mcp
71 | from mcp.server import NotificationOptions, InitializationOptions
72 |
73 | # Example tool definition
74 | @mcp.server.tool("tool_name", "Description of the tool")
75 | async def some_tool(param1: str, param2: int) -> str:
76 | # Implement the tool's logic
77 | return "Result"
78 |
79 | # Initialize the server
80 | server = mcp.server.Server()
81 |
82 | # Main function
83 | async def main():
84 | # Run the server with stdin/stdout streams
85 | async with mcp.server.stdio.stdio_server() as (read_stream, write_stream):
86 | await server.run(
87 | read_stream, write_stream,
88 | InitializationOptions(
89 | server_name="Server Name",
90 | server_version="Version",
91 | capabilities=server.get_capabilities(
92 | notification_options=NotificationOptions(),
93 | experimental_capabilities={},
94 | ),
95 | ),
96 | )
97 |
98 | if __name__ == "__main__":
99 | asyncio.run(main())
100 | ```
101 |
102 | ### TypeScript/JavaScript
103 |
104 | ```typescript
105 | // Install necessary libraries
106 | // npm install @modelcontextprotocol/sdk
107 |
108 | import { Server, StdioServerTransport } from "@modelcontextprotocol/sdk";
109 |
110 | // Create a server instance
111 | const server = new Server();
112 |
113 | // Define a tool
114 | const myTool = {
115 | name: "tool_name",
116 | description: "Description of the tool",
117 | parameters: {
118 | // Define parameters
119 | },
120 | execute: async (params) => {
121 | // Implement the tool's logic
122 | return { result: "Result" };
123 | }
124 | };
125 |
126 | // Register the tool
127 | server.tools.registerTool(myTool);
128 |
129 | // Main function
130 | async function main() {
131 | // Set up transport
132 | const transport = new StdioServerTransport();
133 | await server.connect(transport);
134 | console.error("Server running");
135 | }
136 |
137 | main().catch((error) => {
138 | console.error("Error:", error);
139 | process.exit(1);
140 | });
141 | ```
142 |
143 | ## Security Points to Consider
144 |
145 | When implementing an MCP server, consider these security aspects:
146 |
147 | - **Access Control**: Limit access to the data and tools the server exposes.
148 | - **Authentication**: Mechanisms to verify the client.
149 | - **Data Protection**: Handle sensitive data properly.
150 | - **Resource Limiting**: Limit resource usage to prevent Denial of Service (DoS) attacks.
151 |
152 | ## Best Practices
153 |
154 | 1. **Clear Documentation**: Provide detailed descriptions for each tool, resource, and prompt.
155 | 2. **Error Handling**: Return appropriate error messages and status codes.
156 | 3. **Versioning**: Manage API versions for compatibility.
157 | 4. **Testing**: Perform unit tests and integration tests.
158 | 5. **Logging**: Implement logging for debugging and auditing.
159 |
160 | ## Examples of Existing MCP Servers
161 |
162 | - **File System**: A secure server for file operations.
163 | - **PostgreSQL**: A server for database access.
164 | - **GitHub**: Provides features for repository and issue management.
165 | - **Brave Search**: Offers web search functionality.
166 |
167 | ## Connecting with Claude Desktop
168 |
169 | To connect your MCP server with Claude Desktop:
170 |
171 | 1. Install Claude Desktop.
172 | 2. Edit `~/Library/Application Support/Claude/claude_desktop_config.json`.
173 | 3. Add your custom server to the `mcpServers` section.
174 |
175 | ```json
176 | {
177 | "mcpServers": {
178 | "myserver": {
179 | "command": "command_to_execute",
180 | "args": ["arg1", "arg2"]
181 | }
182 | }
183 | }
184 | ```
185 |
186 | ## Debugging and Troubleshooting
187 |
188 | 1. **Log Output**: Record detailed debug information.
189 | 2. **Step-by-Step Testing**: Test from basic features to complex ones.
190 | 3. **Error Codes**: Implement clear error codes and messages.
191 | 4. **MCP Inspector**: Use debugging tools to check behavior.
192 |
193 | ## Summary
194 |
195 | Implementing an MCP server allows you to connect various data sources and tools to LLMs. This extends the capabilities of AI assistants and can provide a richer user experience.
196 |
```
--------------------------------------------------------------------------------
/db.go:
--------------------------------------------------------------------------------
```go
1 | package main
2 |
3 | import (
4 | "context"
5 | "database/sql"
6 | "fmt"
7 | )
8 |
9 | type DBConfig struct {
10 | Host string
11 | Port string
12 | User string
13 | Password string
14 | }
15 |
16 | type TableSummary struct {
17 | Name string
18 | Comment string
19 | PK []string // Primary key columns
20 | UK []UniqueKey // Unique key information
21 | FK []ForeignKey // Foreign key information
22 | }
23 |
24 | type UniqueKey struct {
25 | Name string
26 | Columns []string
27 | }
28 |
29 | type ForeignKey struct {
30 | Name string
31 | Columns []string
32 | RefTable string
33 | RefColumns []string
34 | }
35 |
36 | type ColumnInfo struct {
37 | Name string
38 | Type string
39 | IsNullable string
40 | Default sql.NullString
41 | Comment string
42 | }
43 |
44 | type IndexInfo struct {
45 | Name string
46 | Columns []string
47 | Unique bool
48 | }
49 |
50 | type DB struct {
51 | conn *sql.DB
52 | }
53 |
54 | func NewDB(conn *sql.DB) *DB {
55 | return &DB{conn: conn}
56 | }
57 |
58 | func connectDB(config DBConfig) (*sql.DB, error) {
59 | dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/",
60 | config.User, config.Password, config.Host, config.Port)
61 |
62 | conn, err := sql.Open("mysql", dsn)
63 | if err != nil {
64 | return nil, err
65 | }
66 |
67 | return conn, nil
68 | }
69 |
70 | // FetchAllTableSummaries gets summary information for all tables in the database
71 | func (db *DB) FetchAllTableSummaries(ctx context.Context, dbName string) ([]TableSummary, error) {
72 | tables, err := db.FetchTableWithComments(ctx, dbName)
73 | if err != nil {
74 | return nil, err
75 | }
76 |
77 | // Get additional information for each table
78 | for i := range tables {
79 | tables[i].PK, err = db.FetchPrimaryKeys(ctx, dbName, tables[i].Name)
80 | if err != nil {
81 | return nil, err
82 | }
83 |
84 | tables[i].UK, err = db.FetchUniqueKeys(ctx, dbName, tables[i].Name)
85 | if err != nil {
86 | return nil, err
87 | }
88 |
89 | tables[i].FK, err = db.FetchForeignKeys(ctx, dbName, tables[i].Name)
90 | if err != nil {
91 | return nil, err
92 | }
93 | }
94 |
95 | return tables, nil
96 | }
97 |
98 | // FetchTableWithComments gets table names and comments
99 | func (db *DB) FetchTableWithComments(ctx context.Context, dbName string) ([]TableSummary, error) {
100 | query := `
101 | SELECT
102 | TABLE_NAME,
103 | IFNULL(TABLE_COMMENT, '') AS TABLE_COMMENT
104 | FROM
105 | INFORMATION_SCHEMA.TABLES
106 | WHERE
107 | TABLE_SCHEMA = ?
108 | ORDER BY
109 | TABLE_NAME
110 | `
111 |
112 | rows, err := db.conn.QueryContext(ctx, query, dbName)
113 | if err != nil {
114 | return nil, err
115 | }
116 | defer rows.Close()
117 |
118 | var tables []TableSummary
119 | for rows.Next() {
120 | var table TableSummary
121 | if err := rows.Scan(&table.Name, &table.Comment); err != nil {
122 | return nil, err
123 | }
124 | tables = append(tables, table)
125 | }
126 |
127 | if err := rows.Err(); err != nil {
128 | return nil, err
129 | }
130 |
131 | return tables, nil
132 | }
133 |
134 | // FetchPrimaryKeys gets the primary key columns of a table
135 | func (db *DB) FetchPrimaryKeys(ctx context.Context, dbName string, tableName string) ([]string, error) {
136 | query := `
137 | SELECT
138 | COLUMN_NAME
139 | FROM
140 | INFORMATION_SCHEMA.KEY_COLUMN_USAGE
141 | WHERE
142 | CONSTRAINT_SCHEMA = ?
143 | AND TABLE_NAME = ?
144 | AND CONSTRAINT_NAME = 'PRIMARY'
145 | ORDER BY
146 | ORDINAL_POSITION
147 | `
148 |
149 | rows, err := db.conn.QueryContext(ctx, query, dbName, tableName)
150 | if err != nil {
151 | return nil, err
152 | }
153 | defer rows.Close()
154 |
155 | var primaryKeys []string
156 | for rows.Next() {
157 | var columnName string
158 | if err := rows.Scan(&columnName); err != nil {
159 | return nil, err
160 | }
161 | primaryKeys = append(primaryKeys, columnName)
162 | }
163 |
164 | if err := rows.Err(); err != nil {
165 | return nil, err
166 | }
167 |
168 | return primaryKeys, nil
169 | }
170 |
171 | // FetchUniqueKeys gets the unique key constraints of a table
172 | func (db *DB) FetchUniqueKeys(ctx context.Context, dbName string, tableName string) ([]UniqueKey, error) {
173 | query := `
174 | SELECT
175 | kcu.CONSTRAINT_NAME,
176 | kcu.COLUMN_NAME
177 | FROM
178 | INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
179 | JOIN
180 | INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
181 | ON
182 | kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
183 | AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
184 | AND kcu.TABLE_NAME = tc.TABLE_NAME
185 | WHERE
186 | kcu.TABLE_SCHEMA = ?
187 | AND kcu.TABLE_NAME = ?
188 | AND tc.CONSTRAINT_TYPE = 'UNIQUE'
189 | ORDER BY
190 | kcu.CONSTRAINT_NAME,
191 | kcu.ORDINAL_POSITION
192 | `
193 |
194 | rows, err := db.conn.QueryContext(ctx, query, dbName, tableName)
195 | if err != nil {
196 | return nil, err
197 | }
198 | defer rows.Close()
199 |
200 | // Build information while maintaining the order of SQL acquisition
201 | var uniqueKeys []UniqueKey
202 | var currentUniqueKey *UniqueKey
203 | for rows.Next() {
204 | var constraintName, columnName string
205 | if err := rows.Scan(&constraintName, &columnName); err != nil {
206 | return nil, err
207 | }
208 |
209 | if currentUniqueKey == nil || currentUniqueKey.Name != constraintName {
210 | // When it's the first one, or when switching to another UK
211 | newUK := UniqueKey{
212 | Name: constraintName,
213 | Columns: []string{},
214 | }
215 | uniqueKeys = append(uniqueKeys, newUK)
216 | currentUniqueKey = &uniqueKeys[len(uniqueKeys)-1]
217 | }
218 |
219 | // If the constraint name is the same, add to the Columns of the current UniqueKey
220 | currentUniqueKey.Columns = append(currentUniqueKey.Columns, columnName)
221 | }
222 |
223 | if err := rows.Err(); err != nil {
224 | return nil, err
225 | }
226 |
227 | return uniqueKeys, nil
228 | }
229 |
230 | // FetchForeignKeys gets the foreign key constraints of a table
231 | func (db *DB) FetchForeignKeys(ctx context.Context, dbName string, tableName string) ([]ForeignKey, error) {
232 | query := `
233 | SELECT
234 | kcu.CONSTRAINT_NAME,
235 | kcu.COLUMN_NAME,
236 | kcu.REFERENCED_TABLE_NAME,
237 | kcu.REFERENCED_COLUMN_NAME
238 | FROM
239 | INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
240 | JOIN
241 | INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
242 | ON
243 | kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
244 | AND kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
245 | WHERE
246 | kcu.TABLE_SCHEMA = ?
247 | AND kcu.TABLE_NAME = ?
248 | AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
249 | ORDER BY
250 | kcu.CONSTRAINT_NAME,
251 | kcu.ORDINAL_POSITION
252 | `
253 |
254 | rows, err := db.conn.QueryContext(ctx, query, dbName, tableName)
255 | if err != nil {
256 | return nil, err
257 | }
258 | defer rows.Close()
259 |
260 | // Build information while maintaining the order of SQL acquisition
261 | var foreignKeys []ForeignKey
262 | var currentFK *ForeignKey
263 | for rows.Next() {
264 | var constraintName, columnName, refTableName, refColumnName string
265 | if err := rows.Scan(&constraintName, &columnName, &refTableName, &refColumnName); err != nil {
266 | return nil, err
267 | }
268 |
269 | if currentFK == nil || currentFK.Name != constraintName {
270 | newFK := ForeignKey{
271 | Name: constraintName,
272 | RefTable: refTableName,
273 | }
274 | foreignKeys = append(foreignKeys, newFK)
275 | currentFK = &foreignKeys[len(foreignKeys)-1]
276 | }
277 |
278 | currentFK.Columns = append(currentFK.Columns, columnName)
279 | currentFK.RefColumns = append(currentFK.RefColumns, refColumnName)
280 | }
281 | if err := rows.Err(); err != nil {
282 | return nil, err
283 | }
284 |
285 | return foreignKeys, nil
286 | }
287 |
288 | // FetchTableColumns gets the column information of a table
289 | func (db *DB) FetchTableColumns(ctx context.Context, dbName string, tableName string) ([]ColumnInfo, error) {
290 | query := `
291 | SELECT
292 | COLUMN_NAME,
293 | COLUMN_TYPE,
294 | IS_NULLABLE,
295 | COLUMN_DEFAULT,
296 | IFNULL(COLUMN_COMMENT, '') AS COLUMN_COMMENT
297 | FROM
298 | INFORMATION_SCHEMA.COLUMNS
299 | WHERE
300 | TABLE_SCHEMA = ?
301 | AND TABLE_NAME = ?
302 | ORDER BY
303 | ORDINAL_POSITION
304 | `
305 |
306 | rows, err := db.conn.QueryContext(ctx, query, dbName, tableName)
307 | if err != nil {
308 | return nil, err
309 | }
310 | defer rows.Close()
311 |
312 | var columns []ColumnInfo
313 | for rows.Next() {
314 | var col ColumnInfo
315 | if err := rows.Scan(&col.Name, &col.Type, &col.IsNullable, &col.Default, &col.Comment); err != nil {
316 | return nil, err
317 | }
318 | columns = append(columns, col)
319 | }
320 |
321 | if err := rows.Err(); err != nil {
322 | return nil, err
323 | }
324 |
325 | return columns, nil
326 | }
327 |
328 | // FetchTableIndexes gets the index information of a table
329 | func (db *DB) FetchTableIndexes(ctx context.Context, dbName string, tableName string) ([]IndexInfo, error) {
330 | query := `
331 | SELECT
332 | INDEX_NAME,
333 | COLUMN_NAME,
334 | NON_UNIQUE
335 | FROM
336 | INFORMATION_SCHEMA.STATISTICS
337 | WHERE
338 | TABLE_SCHEMA = ?
339 | AND TABLE_NAME = ?
340 | AND INDEX_NAME != 'PRIMARY'
341 | AND INDEX_NAME NOT IN (
342 | SELECT CONSTRAINT_NAME
343 | FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
344 | WHERE TABLE_SCHEMA = ?
345 | AND TABLE_NAME = ?
346 | AND CONSTRAINT_TYPE IN ('UNIQUE', 'FOREIGN KEY')
347 | )
348 | ORDER BY
349 | INDEX_NAME,
350 | SEQ_IN_INDEX
351 | `
352 |
353 | rows, err := db.conn.QueryContext(ctx, query, dbName, tableName, dbName, tableName)
354 | if err != nil {
355 | return nil, err
356 | }
357 | defer rows.Close()
358 |
359 | // Build information while maintaining the order of SQL acquisition
360 | var indexes []IndexInfo
361 | var currentIdx *IndexInfo
362 | for rows.Next() {
363 | var indexName, columnName string
364 | var nonUnique bool
365 | if err := rows.Scan(&indexName, &columnName, &nonUnique); err != nil {
366 | return nil, err
367 | }
368 | if currentIdx == nil || currentIdx.Name != indexName {
369 | newIdx := IndexInfo{
370 | Name: indexName,
371 | Unique: !nonUnique,
372 | Columns: []string{},
373 | }
374 | indexes = append(indexes, newIdx)
375 | currentIdx = &indexes[len(indexes)-1]
376 | }
377 | currentIdx.Columns = append(currentIdx.Columns, columnName)
378 | }
379 | if err := rows.Err(); err != nil {
380 | return nil, err
381 | }
382 | return indexes, nil
383 | }
384 |
```
--------------------------------------------------------------------------------
/e2e_test.go:
--------------------------------------------------------------------------------
```go
1 | package main
2 |
3 | import (
4 | "bufio"
5 | "encoding/json"
6 | "fmt"
7 | "io"
8 | "os"
9 | "os/exec"
10 | "testing"
11 | "time"
12 |
13 | "github.com/stretchr/testify/assert"
14 | "github.com/stretchr/testify/require"
15 | )
16 |
17 | type jsonRPCRequest struct {
18 | ID interface{} `json:"id,omitempty"`
19 | Method string `json:"method"`
20 | Params map[string]interface{} `json:"params,omitempty"`
21 | }
22 |
23 | type jsonRPCResponse struct {
24 | ID interface{} `json:"id"`
25 | Result json.RawMessage `json:"result,omitempty"`
26 | Error map[string]interface{} `json:"error,omitempty"`
27 | }
28 |
29 | type mcpServer struct {
30 | cmd *exec.Cmd
31 | stdin io.WriteCloser
32 | stdout io.ReadCloser
33 | reader *bufio.Reader
34 | nextID int
35 | }
36 |
37 | func setupMCPServer(t *testing.T, env []string) *mcpServer {
38 | cmd := exec.Command("go", "run", ".")
39 | cmd.Env = append(os.Environ(), env...)
40 |
41 | stdin, err := cmd.StdinPipe()
42 | require.NoError(t, err)
43 |
44 | stdout, err := cmd.StdoutPipe()
45 | require.NoError(t, err)
46 |
47 | err = cmd.Start()
48 | require.NoError(t, err)
49 |
50 | server := &mcpServer{
51 | cmd: cmd,
52 | stdin: stdin,
53 | stdout: stdout,
54 | reader: bufio.NewReader(stdout),
55 | nextID: 1,
56 | }
57 |
58 | t.Cleanup(func() {
59 | stdin.Close()
60 | cmd.Process.Kill()
61 | cmd.Wait()
62 | })
63 |
64 | // Wait for server to be ready
65 | time.Sleep(100 * time.Millisecond)
66 |
67 | return server
68 | }
69 |
70 | func initializeMCPServer(t *testing.T, server *mcpServer) {
71 | // Send initialize request
72 | initReq := jsonRPCRequest{
73 | Method: "initialize",
74 | }
75 | server.sendRequest(t, initReq)
76 |
77 | // Read initialize response
78 | initResp := server.readResponse(t)
79 | require.Empty(t, initResp.Error, "Initialize should succeed")
80 |
81 | // Send initialized notification
82 | initializedReq := jsonRPCRequest{
83 | Method: "notifications/initialized",
84 | }
85 | server.sendRequest(t, initializedReq)
86 | }
87 |
88 | func (s *mcpServer) sendRequest(t *testing.T, req jsonRPCRequest) {
89 | // Auto-increment ID for requests (except notifications)
90 | if req.Method != "notifications/initialized" && req.ID == nil {
91 | req.ID = s.nextID
92 | s.nextID++
93 | }
94 |
95 | // Convert to the actual JSON-RPC format with jsonrpc field
96 | fullReq := map[string]interface{}{
97 | "jsonrpc": "2.0",
98 | "method": req.Method,
99 | }
100 | if req.ID != nil {
101 | fullReq["id"] = req.ID
102 | }
103 | if req.Params != nil {
104 | fullReq["params"] = req.Params
105 | }
106 |
107 | data, err := json.Marshal(fullReq)
108 | require.NoError(t, err)
109 |
110 | _, err = fmt.Fprintf(s.stdin, "%s\n", data)
111 | require.NoError(t, err)
112 | }
113 |
114 | func (s *mcpServer) readResponse(t *testing.T) jsonRPCResponse {
115 | line, err := s.reader.ReadBytes('\n')
116 | require.NoError(t, err)
117 |
118 | var resp jsonRPCResponse
119 | err = json.Unmarshal(line, &resp)
120 | require.NoError(t, err)
121 |
122 | return resp
123 | }
124 |
125 | // Common test setup helper
126 | func setupE2ETest(t *testing.T) *mcpServer {
127 | config := createTestDBConfig(t)
128 | _ = setupTestDB(t, "testdata/schema.sql")
129 |
130 | env := []string{
131 | fmt.Sprintf("DB_HOST=%s", config.Host),
132 | fmt.Sprintf("DB_PORT=%s", config.Port),
133 | fmt.Sprintf("DB_USER=%s", config.User),
134 | fmt.Sprintf("DB_PASSWORD=%s", config.Password),
135 | }
136 |
137 | server := setupMCPServer(t, env)
138 | initializeMCPServer(t, server)
139 | return server
140 | }
141 |
142 | // Helper to send tools/call request
143 | func (s *mcpServer) sendToolCallRequest(t *testing.T, toolName string, arguments map[string]interface{}) {
144 | req := jsonRPCRequest{
145 | Method: "tools/call",
146 | Params: map[string]interface{}{
147 | "name": toolName,
148 | "arguments": arguments,
149 | },
150 | }
151 | s.sendRequest(t, req)
152 | }
153 |
154 | // Helper to verify response and extract text content
155 | func verifyTextResponse(t *testing.T, resp jsonRPCResponse) string {
156 | // Check no error
157 | assert.Empty(t, resp.Error)
158 |
159 | // Parse result
160 | var result map[string]interface{}
161 | err := json.Unmarshal(resp.Result, &result)
162 | require.NoError(t, err)
163 |
164 | // Verify content
165 | content, ok := result["content"].([]interface{})
166 | require.True(t, ok)
167 | require.Len(t, content, 1)
168 |
169 | textContent := content[0].(map[string]interface{})
170 | assert.Equal(t, "text", textContent["type"])
171 |
172 | text := textContent["text"].(string)
173 | return text
174 | }
175 |
176 | func TestE2EListTables(t *testing.T) {
177 | server := setupE2ETest(t)
178 |
179 | // Send list_tables request
180 | server.sendToolCallRequest(t, "list_tables", map[string]interface{}{
181 | "dbName": testDBName,
182 | })
183 |
184 | // Read and verify response
185 | resp := server.readResponse(t)
186 | text := verifyTextResponse(t, resp)
187 |
188 | expectedText := `Tables in database "test_mysql_schema_explorer_mcp" (Total: 4)
189 | Format: Table Name - Table Comment [PK: Primary Key] [UK: Unique Key 1; Unique Key 2...] [FK: Foreign Key -> Referenced Table.Column; ...]
190 | * Composite keys (keys composed of multiple columns) are grouped in parentheses: (col1, col2)
191 | * Multiple different key constraints are separated by semicolons: key1; key2
192 |
193 | - order_items - Order details [PK: (order_id, item_seq)] [UK: (order_id, product_maker, product_internal_code)] [FK: order_id -> orders.id; (product_maker, product_internal_code) -> products.(maker_code, internal_code)]
194 | - orders - Order header [PK: id] [FK: user_id -> users.id]
195 | - products - Product master [PK: product_code] [UK: (maker_code, internal_code)]
196 | - users - User information [PK: id] [UK: email; (tenant_id, employee_id); username]
197 | `
198 |
199 | assert.Equal(t, expectedText, text)
200 | }
201 |
202 | func TestE2EDescribeTables(t *testing.T) {
203 | server := setupE2ETest(t)
204 |
205 | // Send describe_tables request
206 | server.sendToolCallRequest(t, "describe_tables", map[string]interface{}{
207 | "dbName": testDBName,
208 | "tableNames": []string{"users", "products", "order_items"},
209 | })
210 |
211 | // Read and verify response
212 | resp := server.readResponse(t)
213 | text := verifyTextResponse(t, resp)
214 |
215 | expectedText := `# Table: users - User information
216 |
217 | ## Columns
218 | - id: int NOT NULL [User system ID]
219 | - email: varchar(255) NOT NULL [Email address]
220 | - username: varchar(255) NOT NULL [Username]
221 | - tenant_id: int NOT NULL [Tenant ID]
222 | - employee_id: int NOT NULL [Employee ID]
223 |
224 | ## Key Information
225 | [PK: id]
226 | [UK: email; (tenant_id, employee_id); username]
227 |
228 | ---
229 |
230 | # Table: products - Product master
231 |
232 | ## Columns
233 | - product_code: varchar(50) NOT NULL [Product code (Primary Key)]
234 | - maker_code: varchar(50) NOT NULL [Maker code]
235 | - internal_code: varchar(50) NOT NULL [Internal product code]
236 | - product_name: varchar(255) NULL [Product name]
237 |
238 | ## Key Information
239 | [PK: product_code]
240 | [UK: (maker_code, internal_code)]
241 | [INDEX: (maker_code, product_name); product_name]
242 |
243 | ---
244 |
245 | # Table: order_items - Order details
246 |
247 | ## Columns
248 | - order_id: int NOT NULL [Order ID (FK)]
249 | - item_seq: int NOT NULL [Order item sequence number]
250 | - product_maker: varchar(50) NOT NULL [Product maker code (FK)]
251 | - product_internal_code: varchar(50) NOT NULL [Product internal code (FK)]
252 | - quantity: int NOT NULL [Quantity]
253 |
254 | ## Key Information
255 | [PK: (order_id, item_seq)]
256 | [UK: (order_id, product_maker, product_internal_code)]
257 | [FK: order_id -> orders.id; (product_maker, product_internal_code) -> products.(maker_code, internal_code)]
258 | [INDEX: (product_maker, product_internal_code)]
259 | `
260 |
261 | assert.Equal(t, expectedText, text)
262 | }
263 |
264 | func TestE2E_FixedDBMode(t *testing.T) {
265 | config := createTestDBConfig(t)
266 | _ = setupTestDB(t, "testdata/schema.sql")
267 |
268 | // Setup with DB_NAME environment variable
269 | env := []string{
270 | fmt.Sprintf("DB_HOST=%s", config.Host),
271 | fmt.Sprintf("DB_PORT=%s", config.Port),
272 | fmt.Sprintf("DB_USER=%s", config.User),
273 | fmt.Sprintf("DB_PASSWORD=%s", config.Password),
274 | fmt.Sprintf("DB_NAME=%s", testDBName), // Fixed DB name
275 | }
276 |
277 | server := setupMCPServer(t, env)
278 | initializeMCPServer(t, server)
279 |
280 | t.Run("list_tables works without dbName in fixed mode", func(t *testing.T) {
281 | // Send without dbName parameter
282 | server.sendToolCallRequest(t, "list_tables", map[string]interface{}{})
283 |
284 | resp := server.readResponse(t)
285 | text := verifyTextResponse(t, resp)
286 |
287 | // Should list tables from the fixed DB
288 | assert.Contains(t, text, "Tables in database \"test_mysql_schema_explorer_mcp\" (Total: 4)")
289 | assert.Contains(t, text, "users")
290 | assert.Contains(t, text, "orders")
291 | assert.Contains(t, text, "products")
292 | assert.Contains(t, text, "order_items")
293 | })
294 |
295 | t.Run("describe_tables works without dbName in fixed mode", func(t *testing.T) {
296 | // Send without dbName parameter
297 | server.sendToolCallRequest(t, "describe_tables", map[string]interface{}{
298 | "tableNames": []string{"users"},
299 | })
300 |
301 | resp := server.readResponse(t)
302 | text := verifyTextResponse(t, resp)
303 |
304 | // Should describe table from the fixed DB
305 | assert.Contains(t, text, "# Table: users")
306 | assert.Contains(t, text, "## Columns")
307 | assert.Contains(t, text, "- id: int NOT NULL")
308 | assert.Contains(t, text, "- email: varchar(255) NOT NULL")
309 | assert.Contains(t, text, "- username: varchar(255) NOT NULL")
310 | })
311 |
312 | }
313 |
314 | func TestE2E_ToolDefinitions(t *testing.T) {
315 | findTool := func(tools []interface{}, name string) map[string]interface{} {
316 | for _, tool := range tools {
317 | toolMap := tool.(map[string]interface{})
318 | if toolMap["name"] == name {
319 | return toolMap
320 | }
321 | }
322 | return nil
323 | }
324 |
325 | getToolProperties := func(tool map[string]interface{}) map[string]interface{} {
326 | inputSchema, ok := tool["inputSchema"]
327 | if !ok || inputSchema == nil {
328 | return map[string]interface{}{}
329 | }
330 |
331 | properties, ok := inputSchema.(map[string]interface{})["properties"]
332 | if !ok || properties == nil {
333 | return map[string]interface{}{}
334 | }
335 |
336 | return properties.(map[string]interface{})
337 | }
338 |
339 | t.Run("normal mode has dbName parameter", func(t *testing.T) {
340 | server := setupE2ETest(t) // Normal mode (no DB_NAME)
341 |
342 | req := jsonRPCRequest{Method: "tools/list"}
343 | server.sendRequest(t, req)
344 | resp := server.readResponse(t)
345 |
346 | var result map[string]interface{}
347 | json.Unmarshal(resp.Result, &result)
348 | tools := result["tools"].([]interface{})
349 |
350 | assert.Len(t, tools, 2)
351 |
352 | // Check list_tables has dbName parameter
353 | listTables := findTool(tools, "list_tables")
354 | properties := getToolProperties(listTables)
355 | _, hasDBName := properties["dbName"]
356 | assert.True(t, hasDBName, "list_tables should have dbName in normal mode")
357 |
358 | // Check describe_tables has dbName parameter
359 | describeTables := findTool(tools, "describe_tables")
360 | properties = getToolProperties(describeTables)
361 | _, hasDBName = properties["dbName"]
362 | assert.True(t, hasDBName, "describe_tables should have dbName in normal mode")
363 | })
364 |
365 | t.Run("fixed mode has no dbName parameter", func(t *testing.T) {
366 | config := createTestDBConfig(t)
367 | _ = setupTestDB(t, "testdata/schema.sql")
368 |
369 | env := []string{
370 | fmt.Sprintf("DB_HOST=%s", config.Host),
371 | fmt.Sprintf("DB_PORT=%s", config.Port),
372 | fmt.Sprintf("DB_USER=%s", config.User),
373 | fmt.Sprintf("DB_PASSWORD=%s", config.Password),
374 | fmt.Sprintf("DB_NAME=%s", testDBName),
375 | }
376 |
377 | server := setupMCPServer(t, env)
378 | initializeMCPServer(t, server)
379 |
380 | req := jsonRPCRequest{Method: "tools/list"}
381 | server.sendRequest(t, req)
382 | resp := server.readResponse(t)
383 |
384 | var result map[string]interface{}
385 | json.Unmarshal(resp.Result, &result)
386 | tools := result["tools"].([]interface{})
387 |
388 | assert.Len(t, tools, 2)
389 |
390 | // Check list_tables has no dbName parameter
391 | listTables := findTool(tools, "list_tables")
392 | properties := getToolProperties(listTables)
393 | _, hasDBName := properties["dbName"]
394 | assert.False(t, hasDBName, "list_tables should not have dbName in fixed mode")
395 |
396 | // Check describe_tables has no dbName parameter
397 | describeTables := findTool(tools, "describe_tables")
398 | properties = getToolProperties(describeTables)
399 | _, hasDBName = properties["dbName"]
400 | assert.False(t, hasDBName, "describe_tables should not have dbName in fixed mode")
401 | })
402 | }
403 |
```
--------------------------------------------------------------------------------
/docs/mcp-go-cheatsheet.md:
--------------------------------------------------------------------------------
```markdown
1 | ## Quickstart
2 |
3 | Let's create a simple MCP server that exposes a calculator tool and some data:
4 |
5 | ```go
6 | package main
7 |
8 | import (
9 | "context"
10 | "errors"
11 | "fmt"
12 |
13 | "github.com/mark3labs/mcp-go/mcp"
14 | "github.com/mark3labs/mcp-go/server"
15 | )
16 |
17 | func main() {
18 | // Create a new MCP server
19 | s := server.NewMCPServer(
20 | "Calculator Demo",
21 | "1.0.0",
22 | server.WithResourceCapabilities(true, true),
23 | server.WithLogging(),
24 | server.WithRecovery(),
25 | )
26 |
27 | // Add a calculator tool
28 | calculatorTool := mcp.NewTool("calculate",
29 | mcp.WithDescription("Perform basic arithmetic operations"),
30 | mcp.WithString("operation",
31 | mcp.Required(),
32 | mcp.Description("The operation to perform (add, subtract, multiply, divide)"),
33 | mcp.Enum("add", "subtract", "multiply", "divide"),
34 | ),
35 | mcp.WithNumber("x",
36 | mcp.Required(),
37 | mcp.Description("First number"),
38 | ),
39 | mcp.WithNumber("y",
40 | mcp.Required(),
41 | mcp.Description("Second number"),
42 | ),
43 | )
44 |
45 | // Add the calculator handler
46 | s.AddTool(calculatorTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
47 | op := request.Params.Arguments["operation"].(string)
48 | x := request.Params.Arguments["x"].(float64)
49 | y := request.Params.Arguments["y"].(float64)
50 |
51 | var result float64
52 | switch op {
53 | case "add":
54 | result = x + y
55 | case "subtract":
56 | result = x - y
57 | case "multiply":
58 | result = x * y
59 | case "divide":
60 | if y == 0 {
61 | return mcp.NewToolResultError("cannot divide by zero"), nil
62 | }
63 | result = x / y
64 | }
65 |
66 | return mcp.NewToolResultText(fmt.Sprintf("%.2f", result)), nil
67 | })
68 |
69 | // Start the server
70 | if err := server.ServeStdio(s); err != nil {
71 | fmt.Printf("Server error: %v\n", err)
72 | }
73 | }
74 | ```
75 |
76 | ## Core Concepts
77 |
78 |
79 | ### Server
80 |
81 | <details>
82 | <summary>Show Server Examples</summary>
83 |
84 | The server is your core interface to the MCP protocol. It handles connection management, protocol compliance, and message routing:
85 |
86 | ```go
87 | // Create a basic server
88 | s := server.NewMCPServer(
89 | "My Server", // Server name
90 | "1.0.0", // Version
91 | )
92 |
93 | // Start the server using stdio
94 | if err := server.ServeStdio(s); err != nil {
95 | log.Fatalf("Server error: %v", err)
96 | }
97 | ```
98 |
99 | </details>
100 |
101 | ### Resources
102 |
103 | <details>
104 | <summary>Show Resource Examples</summary>
105 | Resources are how you expose data to LLMs. They can be anything - files, API responses, database queries, system information, etc. Resources can be:
106 |
107 | - Static (fixed URI)
108 | - Dynamic (using URI templates)
109 |
110 | Here's a simple example of a static resource:
111 |
112 | ```go
113 | // Static resource example - exposing a README file
114 | resource := mcp.NewResource(
115 | "docs://readme",
116 | "Project README",
117 | mcp.WithResourceDescription("The project's README file"),
118 | mcp.WithMIMEType("text/markdown"),
119 | )
120 |
121 | // Add resource with its handler
122 | s.AddResource(resource, func(ctx context.Context, request mcp.ReadResourceRequest) ([]mcp.ResourceContents, error) {
123 | content, err := os.ReadFile("README.md")
124 | if err != nil {
125 | return nil, err
126 | }
127 |
128 | return []mcp.ResourceContents{
129 | mcp.TextResourceContents{
130 | URI: "docs://readme",
131 | MIMEType: "text/markdown",
132 | Text: string(content),
133 | },
134 | }, nil
135 | })
136 | ```
137 |
138 | And here's an example of a dynamic resource using a template:
139 |
140 | ```go
141 | // Dynamic resource example - user profiles by ID
142 | template := mcp.NewResourceTemplate(
143 | "users://{id}/profile",
144 | "User Profile",
145 | mcp.WithTemplateDescription("Returns user profile information"),
146 | mcp.WithTemplateMIMEType("application/json"),
147 | )
148 |
149 | // Add template with its handler
150 | s.AddResourceTemplate(template, func(ctx context.Context, request mcp.ReadResourceRequest) ([]mcp.ResourceContents, error) {
151 | // Extract ID from the URI using regex matching
152 | // The server automatically matches URIs to templates
153 | userID := extractIDFromURI(request.Params.URI)
154 |
155 | profile, err := getUserProfile(userID) // Your DB/API call here
156 | if err != nil {
157 | return nil, err
158 | }
159 |
160 | return []mcp.ResourceContents{
161 | mcp.TextResourceContents{
162 | URI: request.Params.URI,
163 | MIMEType: "application/json",
164 | Text: profile,
165 | },
166 | }, nil
167 | })
168 | ```
169 |
170 | The examples are simple but demonstrate the core concepts. Resources can be much more sophisticated - serving multiple contents, integrating with databases or external APIs, etc.
171 | </details>
172 |
173 | ### Tools
174 |
175 | <details>
176 | <summary>Show Tool Examples</summary>
177 |
178 | Tools let LLMs take actions through your server. Unlike resources, tools are expected to perform computation and have side effects. They're similar to POST endpoints in a REST API.
179 |
180 | Simple calculation example:
181 | ```go
182 | calculatorTool := mcp.NewTool("calculate",
183 | mcp.WithDescription("Perform basic arithmetic calculations"),
184 | mcp.WithString("operation",
185 | mcp.Required(),
186 | mcp.Description("The arithmetic operation to perform"),
187 | mcp.Enum("add", "subtract", "multiply", "divide"),
188 | ),
189 | mcp.WithNumber("x",
190 | mcp.Required(),
191 | mcp.Description("First number"),
192 | ),
193 | mcp.WithNumber("y",
194 | mcp.Required(),
195 | mcp.Description("Second number"),
196 | ),
197 | )
198 |
199 | s.AddTool(calculatorTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
200 | op := request.Params.Arguments["operation"].(string)
201 | x := request.Params.Arguments["x"].(float64)
202 | y := request.Params.Arguments["y"].(float64)
203 |
204 | var result float64
205 | switch op {
206 | case "add":
207 | result = x + y
208 | case "subtract":
209 | result = x - y
210 | case "multiply":
211 | result = x * y
212 | case "divide":
213 | if y == 0 {
214 | return mcp.NewToolResultError("cannot divide by zero"), nil
215 | }
216 | result = x / y
217 | }
218 |
219 | return mcp.FormatNumberResult(result), nil
220 | })
221 | ```
222 |
223 | HTTP request example:
224 | ```go
225 | httpTool := mcp.NewTool("http_request",
226 | mcp.WithDescription("Make HTTP requests to external APIs"),
227 | mcp.WithString("method",
228 | mcp.Required(),
229 | mcp.Description("HTTP method to use"),
230 | mcp.Enum("GET", "POST", "PUT", "DELETE"),
231 | ),
232 | mcp.WithString("url",
233 | mcp.Required(),
234 | mcp.Description("URL to send the request to"),
235 | mcp.Pattern("^https?://.*"),
236 | ),
237 | mcp.WithString("body",
238 | mcp.Description("Request body (for POST/PUT)"),
239 | ),
240 | )
241 |
242 | s.AddTool(httpTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
243 | method := request.Params.Arguments["method"].(string)
244 | url := request.Params.Arguments["url"].(string)
245 | body := ""
246 | if b, ok := request.Params.Arguments["body"].(string); ok {
247 | body = b
248 | }
249 |
250 | // Create and send request
251 | var req *http.Request
252 | var err error
253 | if body != "" {
254 | req, err = http.NewRequest(method, url, strings.NewReader(body))
255 | } else {
256 | req, err = http.NewRequest(method, url, nil)
257 | }
258 | if err != nil {
259 | return mcp.NewToolResultErrorFromErr("unable to create request", err), nil
260 | }
261 |
262 | client := &http.Client{}
263 | resp, err := client.Do(req)
264 | if err != nil {
265 | return mcp.NewToolResultErrorFromErr("unable to execute request", err), nil
266 | }
267 | defer resp.Body.Close()
268 |
269 | // Return response
270 | respBody, err := io.ReadAll(resp.Body)
271 | if err != nil {
272 | return mcp.NewToolResultErrorFromErr("unable to read request response", err), nil
273 | }
274 |
275 | return mcp.NewToolResultText(fmt.Sprintf("Status: %d\nBody: %s", resp.StatusCode, string(respBody))), nil
276 | })
277 | ```
278 |
279 | Tools can be used for any kind of computation or side effect:
280 | - Database queries
281 | - File operations
282 | - External API calls
283 | - Calculations
284 | - System operations
285 |
286 | Each tool should:
287 | - Have a clear description
288 | - Validate inputs
289 | - Handle errors gracefully
290 | - Return structured responses
291 | - Use appropriate result types
292 |
293 | </details>
294 |
295 | ### Prompts
296 |
297 | <details>
298 | <summary>Show Prompt Examples</summary>
299 |
300 | Prompts are reusable templates that help LLMs interact with your server effectively. They're like "best practices" encoded into your server. Here are some examples:
301 |
302 | ```go
303 | // Simple greeting prompt
304 | s.AddPrompt(mcp.NewPrompt("greeting",
305 | mcp.WithPromptDescription("A friendly greeting prompt"),
306 | mcp.WithArgument("name",
307 | mcp.ArgumentDescription("Name of the person to greet"),
308 | ),
309 | ), func(ctx context.Context, request mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
310 | name := request.Params.Arguments["name"]
311 | if name == "" {
312 | name = "friend"
313 | }
314 |
315 | return mcp.NewGetPromptResult(
316 | "A friendly greeting",
317 | []mcp.PromptMessage{
318 | mcp.NewPromptMessage(
319 | mcp.RoleAssistant,
320 | mcp.NewTextContent(fmt.Sprintf("Hello, %s! How can I help you today?", name)),
321 | ),
322 | },
323 | ), nil
324 | })
325 |
326 | // Code review prompt with embedded resource
327 | s.AddPrompt(mcp.NewPrompt("code_review",
328 | mcp.WithPromptDescription("Code review assistance"),
329 | mcp.WithArgument("pr_number",
330 | mcp.ArgumentDescription("Pull request number to review"),
331 | mcp.RequiredArgument(),
332 | ),
333 | ), func(ctx context.Context, request mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
334 | prNumber := request.Params.Arguments["pr_number"]
335 | if prNumber == "" {
336 | return nil, fmt.Errorf("pr_number is required")
337 | }
338 |
339 | return mcp.NewGetPromptResult(
340 | "Code review assistance",
341 | []mcp.PromptMessage{
342 | mcp.NewPromptMessage(
343 | mcp.RoleSystem,
344 | mcp.NewTextContent("You are a helpful code reviewer. Review the changes and provide constructive feedback."),
345 | ),
346 | mcp.NewPromptMessage(
347 | mcp.RoleAssistant,
348 | mcp.NewEmbeddedResource(mcp.ResourceContents{
349 | URI: fmt.Sprintf("git://pulls/%s/diff", prNumber),
350 | MIMEType: "text/x-diff",
351 | }),
352 | ),
353 | },
354 | ), nil
355 | })
356 |
357 | // Database query builder prompt
358 | s.AddPrompt(mcp.NewPrompt("query_builder",
359 | mcp.WithPromptDescription("SQL query builder assistance"),
360 | mcp.WithArgument("table",
361 | mcp.ArgumentDescription("Name of the table to query"),
362 | mcp.RequiredArgument(),
363 | ),
364 | ), func(ctx context.Context, request mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
365 | tableName := request.Params.Arguments["table"]
366 | if tableName == "" {
367 | return nil, fmt.Errorf("table name is required")
368 | }
369 |
370 | return mcp.NewGetPromptResult(
371 | "SQL query builder assistance",
372 | []mcp.PromptMessage{
373 | mcp.NewPromptMessage(
374 | mcp.RoleSystem,
375 | mcp.NewTextContent("You are a SQL expert. Help construct efficient and safe queries."),
376 | ),
377 | mcp.NewPromptMessage(
378 | mcp.RoleAssistant,
379 | mcp.NewEmbeddedResource(mcp.ResourceContents{
380 | URI: fmt.Sprintf("db://schema/%s", tableName),
381 | MIMEType: "application/json",
382 | }),
383 | ),
384 | },
385 | ), nil
386 | })
387 | ```
388 |
389 | Prompts can include:
390 | - System instructions
391 | - Required arguments
392 | - Embedded resources
393 | - Multiple messages
394 | - Different content types (text, images, etc.)
395 | - Custom URI schemes
396 |
397 | </details>
398 |
```