# 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:
--------------------------------------------------------------------------------
```
### Generated by gibo (https://github.com/simonwhitaker/gibo)
### https://raw.github.com/github/gitignore/4488915eec0b3a45b5c63ead28f286819c0917de/Go.gitignore
# If you prefer the allow list template instead of the deny list, see community template:
# https://github.com/github/gitignore/blob/main/community/Golang/Go.AllowList.gitignore
#
# Binaries for programs and plugins
*.exe
*.exe~
*.dll
*.so
*.dylib
# Test binary, built with `go test -c`
*.test
# Output of the go coverage tool, specifically when used with LiteIDE
*.out
# Dependency directories (remove the comment below to include it)
# vendor/
# Go workspace file
go.work
mysql-schema-explorer-mcp
/.cursor
/.claude/settings.local.json
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
# MySQL Schema MCP Server
This is a Model Context Protocol (MCP) server that provides compressed schema information for MySQL databases.
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.
https://github.com/user-attachments/assets/0cecef84-cd70-4f84-95cb-01c6ec7c9ac7
## Provided Tools
- List Tables (`list_tables`)
- Lists all table information in the specified database. Includes table name, comment, primary key, unique key, and foreign key information.
- Parameters
- `dbName`: The name of the database to retrieve information from (not required when DB_NAME environment variable is set)
- Describe Tables (`describe_tables`)
- Displays detailed information for specific tables in the specified database. Provides formatted information such as column definitions, key constraints, and indexes.
- Parameters
- `dbName`: The name of the database to retrieve information from (not required when DB_NAME environment variable is set)
- `tableNames`: An array of table names to retrieve detailed information for
## Quick Start
1. Configure mcp.json
```json
{
"mcpServers": {
"mysql-schema-explorer-mcp": {
"command": "docker",
"args": ["run", "-i", "--rm", "--pull", "always", "--network=host",
"-e", "DB_HOST=127.0.0.1",
"-e", "DB_PORT=3306",
"-e", "DB_USER=root",
"-e", "DB_PASSWORD=your_password",
"ghcr.io/shibayu36/mysql-schema-explorer-mcp:latest"
]
}
}
}
```
If using Claude Code:
```bash
claude mcp add mysql-schema-explorer-mcp -- docker run -i --rm --pull always --network=host \
-e DB_HOST=127.0.0.1 \
-e DB_USER=root \
-e DB_PASSWORD=your_password \
-e DB_PORT=3306 \
ghcr.io/shibayu36/mysql-schema-explorer-mcp:latest
```
2. Execute SQL generation using the agent
Example: Using the structure of the ecshop database, list the names of the 3 most recently ordered products by the user shibayu36.
## Usage
### Fixing to a Specific Database
When accessing only one database, you can set the `DB_NAME` environment variable to avoid specifying the database name each time.
```json
{
"mcpServers": {
"mysql-schema-explorer-mcp": {
"command": "docker",
"args": ["run", "-i", "--rm", "--network=host",
"-e", "DB_HOST=127.0.0.1",
"-e", "DB_PORT=3306",
"-e", "DB_USER=root",
"-e", "DB_PASSWORD=your_password",
"-e", "DB_NAME=ecshop",
"ghcr.io/shibayu36/mysql-schema-explorer-mcp:latest"
]
}
}
}
```
### Using Binary Instead of Docker
If you have a Go development environment, you can also install and use the binary directly.
1. Install the command
```bash
go install github.com/shibayu36/mysql-schema-explorer-mcp@latest
```
2. Configure mcp.json
```json
{
"mcpServers": {
"mysql-schema-explorer-mcp": {
"command": "/path/to/mysql-schema-explorer-mcp",
"env": {
"DB_HOST": "127.0.0.1",
"DB_PORT": "3306",
"DB_USER": "root",
"DB_PASSWORD": "your_password"
}
}
}
}
```
```
--------------------------------------------------------------------------------
/CLAUDE.md:
--------------------------------------------------------------------------------
```markdown
# CLAUDE.md
This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.
## Commands
### Running Tests
```bash
# Run all tests
go test ./...
# Run tests with verbose output
go test -v ./...
# Run a specific test
go test -run TestListTables
```
### Building
```bash
# Build the binary
go build -o mysql-schema-explorer-mcp
# Install globally
go install
```
### Local Development with Docker
```bash
# Start MySQL test instance
docker-compose up -d
# Set environment variables for local testing
export DB_HOST=127.0.0.1
export DB_PORT=3306
export DB_USER=root
export DB_PASSWORD=root
```
## Architecture
This is a Model Context Protocol (MCP) server that provides MySQL schema information to LLM applications. The codebase follows a clean architecture pattern:
- **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)
- **handler.go**: Contains the business logic for handling MCP tool requests. Implements ListTables and DescribeTables methods
- **db.go**: Database layer that handles MySQL connections and queries. Provides methods for fetching table metadata, columns, indexes, and constraints
- **view.go**: Formatting layer that converts raw database schema information into human-readable output
The server uses the mcp-go library (github.com/mark3labs/mcp-go) to implement the MCP protocol and communicates via stdio.
## Code Guidelines
### Language
- Use English for all comments and documentation.
- Use Japanese for all comments and documentation when the code is written in Japanese.
### Appropriate Use of Comments
**⚠️ Important**: Keep comments to the absolute minimum. Do not comment on things that are immediately obvious from reading the code.
**Limited cases where comments should be written**:
1. **Public API documentation**: For documenting public methods and interfaces
2. **Background context**: When there's important background information not evident from the code
3. **Complex logic explanation**: When complex code requires explanation to understand the intended behavior
4. **Large code section separation**: For 100+ line code blocks where sectioning with explanatory comments is beneficial
## MCP Tool Implementation
When implementing new tools:
1. Define the tool in main.go using mcp.NewTool() with proper descriptions and parameters
2. Add the handler method to handler.go that processes the request
3. Implement any required database queries in db.go
4. Format the output in view.go if needed
Tools must handle errors gracefully and return meaningful error messages through the MCP protocol.
```
--------------------------------------------------------------------------------
/docker-compose.yml:
--------------------------------------------------------------------------------
```yaml
services:
mysql:
image: mysql:8
environment:
MYSQL_ROOT_PASSWORD: rootpass
ports:
- "13306:3306"
volumes:
- mysql-data:/var/lib/mysql
volumes:
mysql-data:
```
--------------------------------------------------------------------------------
/RELEASE.md:
--------------------------------------------------------------------------------
```markdown
# Release Process
## Prerequisites
- Ensure all changes are committed
- Run tests locally: `go test ./...`
## Release Steps
```bash
./release.sh <version>
```
Example:
```bash
./release.sh 1.0.1
```
## After Release
1. Create a GitHub release from the tag
2. Build binaries for different platforms if needed
3. Update changelog if maintained
```
--------------------------------------------------------------------------------
/.github/workflows/ci.yml:
--------------------------------------------------------------------------------
```yaml
name: CI
on:
push:
pull_request:
jobs:
test:
runs-on: ubuntu-latest
services:
mysql:
image: mysql:8
env:
MYSQL_ROOT_PASSWORD: rootpass
ports:
- 3306:3306
steps:
- uses: actions/checkout@v4
- name: Setup Go
uses: actions/setup-go@v5
with:
go-version-file: 'go.mod'
- name: Wait for MySQL
run: |
n=0; until mysqladmin ping -h127.0.0.1 -uroot -prootpass > /dev/null 2>&1 || [ $n -ge 30 ]; do
n=$((n+1)); echo "Waiting for MySQL... ($n)"; sleep 1;
done
if [ $n -ge 30 ]; then echo "MySQL not ready"; exit 1; fi
- name: Check formatting
run: |
make fmt
if [ -n "$(git status --porcelain)" ]; then
echo "Code is not formatted. Please run 'make fmt'"
git diff
exit 1
fi
- name: Run tests
env:
DB_HOST: 127.0.0.1
DB_PORT: 3306
DB_USER: root
DB_PASSWORD: rootpass
run: go test -v ./... -timeout 5m
```
--------------------------------------------------------------------------------
/.github/workflows/publish-docker.yml:
--------------------------------------------------------------------------------
```yaml
name: Build and Push Docker Image
on:
push:
tags: ['v*']
# Allow manual trigger for testing
workflow_dispatch:
env:
REGISTRY: ghcr.io
IMAGE_NAME: ${{ github.repository }}
jobs:
docker:
runs-on: ubuntu-latest
permissions:
contents: read
packages: write
steps:
- name: Checkout repository
uses: actions/checkout@v4
- name: Set up Docker Buildx
uses: docker/setup-buildx-action@v3
- name: Log in to the Container registry
uses: docker/login-action@v3
with:
registry: ${{ env.REGISTRY }}
username: ${{ github.actor }}
password: ${{ secrets.GITHUB_TOKEN }}
- name: Extract metadata
id: meta
uses: docker/metadata-action@v5
with:
images: ${{ env.REGISTRY }}/${{ env.IMAGE_NAME }}
tags: |
type=semver,pattern={{version}}
type=raw,value=latest,enable={{is_default_branch}}
- name: Build and push Docker image
uses: docker/build-push-action@v6
with:
context: .
platforms: linux/amd64,linux/arm64
push: true
tags: ${{ steps.meta.outputs.tags }}
labels: ${{ steps.meta.outputs.labels }}
cache-from: type=gha
cache-to: type=gha,mode=max
```
--------------------------------------------------------------------------------
/release.sh:
--------------------------------------------------------------------------------
```bash
#!/bin/bash
set -e
# Check if version is provided
if [ -z "$1" ]; then
echo "Usage: ./release.sh <version>"
echo "Example: ./release.sh 1.0.1"
exit 1
fi
VERSION=$1
# Validate version format (basic semantic versioning)
if ! [[ "$VERSION" =~ ^[0-9]+\.[0-9]+\.[0-9]+$ ]]; then
echo "Error: Version must be in format X.Y.Z (e.g., 1.0.1)"
exit 1
fi
# Check for uncommitted changes
if ! git diff-index --quiet HEAD --; then
echo "Error: You have uncommitted changes. Please commit or stash them first."
exit 1
fi
# Update version constant in main.go
echo "Updating version to $VERSION..."
sed -i '' "s/const Version = \".*\"/const Version = \"$VERSION\"/" main.go
# Run tests to ensure everything works
echo "Running tests..."
go test ./...
# Build to ensure compilation
echo "Building..."
go build -o mysql-schema-explorer-mcp
# Commit version change
echo "Committing version change..."
git add main.go
git commit -m "Release v$VERSION"
# Create annotated tag
echo "Creating tag v$VERSION..."
git tag -a "v$VERSION" -m "Release v$VERSION"
# Push commits and tags
echo "Pushing to remote..."
git push
git push --tags
echo "✅ Release v$VERSION completed successfully!"
echo ""
echo "Next steps:"
echo "1. Create a GitHub release from the tag"
echo "2. Build binaries for different platforms if needed"
echo "3. Update any documentation or changelogs"
```
--------------------------------------------------------------------------------
/testdata/schema.sql:
--------------------------------------------------------------------------------
```sql
-- users table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'User system ID',
email VARCHAR(255) NOT NULL UNIQUE COMMENT 'Email address',
username VARCHAR(255) NOT NULL UNIQUE COMMENT 'Username',
tenant_id INT NOT NULL COMMENT 'Tenant ID',
employee_id INT NOT NULL COMMENT 'Employee ID',
UNIQUE KEY uk_tenant_employee (tenant_id, employee_id) -- Composite unique key
) COMMENT='User information';
-- orders table (for reference from order_items)
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Order ID',
user_id INT NOT NULL COMMENT 'User ID (FK)',
order_date DATETIME COMMENT 'Order date',
INDEX(id), -- Index needed for referencing in composite foreign key
FOREIGN KEY fk_user (user_id) REFERENCES users(id)
) COMMENT='Order header';
-- products table
CREATE TABLE products (
product_code VARCHAR(50) PRIMARY KEY COMMENT 'Product code (Primary Key)',
maker_code VARCHAR(50) NOT NULL COMMENT 'Maker code',
internal_code VARCHAR(50) NOT NULL COMMENT 'Internal product code',
product_name VARCHAR(255) COMMENT 'Product name',
UNIQUE KEY uk_maker_internal (maker_code, internal_code), -- Composite unique key
INDEX idx_product_name (product_name),
INDEX idx_maker_product_name (maker_code, product_name)
) COMMENT='Product master';
-- order_items table
CREATE TABLE order_items (
order_id INT NOT NULL COMMENT 'Order ID (FK)',
item_seq INT NOT NULL COMMENT 'Order item sequence number',
product_maker VARCHAR(50) NOT NULL COMMENT 'Product maker code (FK)',
product_internal_code VARCHAR(50) NOT NULL COMMENT 'Product internal code (FK)',
quantity INT NOT NULL COMMENT 'Quantity',
PRIMARY KEY (order_id, item_seq), -- Composite primary key
UNIQUE KEY uk_order_product (order_id, product_maker, product_internal_code), -- Composite unique key (prevent duplicate products in one order)
FOREIGN KEY fk_order (order_id) REFERENCES orders(id) ON DELETE CASCADE, -- Single foreign key (delete items if order is deleted)
FOREIGN KEY fk_product (product_maker, product_internal_code) REFERENCES products(maker_code, internal_code) -- Composite foreign key
) COMMENT='Order details';
```
--------------------------------------------------------------------------------
/testhelper_test.go:
--------------------------------------------------------------------------------
```go
package main
import (
"database/sql"
"fmt"
"os"
"strings"
"testing"
_ "github.com/go-sql-driver/mysql"
)
// createTestDBConfig creates DB settings for testing. Uses default values if environment variables are not set.
func createTestDBConfig(t *testing.T) DBConfig {
host := os.Getenv("DB_HOST")
if host == "" {
host = "localhost"
}
port := os.Getenv("DB_PORT")
if port == "" {
port = "13306"
}
user := os.Getenv("DB_USER")
if user == "" {
user = "root"
}
password := os.Getenv("DB_PASSWORD")
if password == "" {
password = "rootpass"
}
return DBConfig{
Host: host,
Port: port,
User: user,
Password: password,
}
}
const testDBName = "test_mysql_schema_explorer_mcp"
// setupTestDB creates a test DB and returns the connection.
// It deletes the DB by calling the cleanup function after the test finishes.
func setupTestDB(t *testing.T, schemaFile string) *sql.DB {
t.Helper()
config := createTestDBConfig(t)
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/",
config.User, config.Password, config.Host, config.Port)
db, err := sql.Open("mysql", dsn)
if err != nil {
t.Fatalf("Failed to connect to MySQL: %v", err)
}
// Create DB (delete if exists)
_, err = db.Exec(fmt.Sprintf("DROP DATABASE IF EXISTS `%s`", testDBName))
if err != nil {
db.Close()
t.Fatalf("Failed to drop database: %v", err)
}
_, err = db.Exec(fmt.Sprintf("CREATE DATABASE `%s`", testDBName))
if err != nil {
db.Close()
t.Fatalf("Failed to create database: %v", err)
}
// Apply schema
{
applyDB, err := sql.Open("mysql", dsn+testDBName)
if err != nil {
t.Fatalf("Failed to connect to test DB: %v", err)
}
defer applyDB.Close()
schemaBytes, err := os.ReadFile(schemaFile)
if err != nil {
t.Fatalf("Failed to read schema file: %v", err)
}
schema := string(schemaBytes)
// Split and execute SQL statements
statements := strings.Split(schema, ";")
for _, stmt := range statements {
stmt = strings.TrimSpace(stmt)
if stmt == "" {
continue
}
_, err := applyDB.Exec(stmt)
if err != nil {
t.Logf("Failed to execute SQL: %s", stmt)
t.Fatalf("Failed to apply schema: %v", err)
}
}
}
t.Cleanup(func() {
_, _ = db.Exec(fmt.Sprintf("DROP DATABASE IF EXISTS `%s`", testDBName))
db.Close()
})
return db
}
```
--------------------------------------------------------------------------------
/main.go:
--------------------------------------------------------------------------------
```go
package main
import (
"fmt"
"log"
"os"
_ "github.com/go-sql-driver/mysql"
"github.com/mark3labs/mcp-go/mcp"
"github.com/mark3labs/mcp-go/server"
)
const Version = "1.1.1"
func main() {
dbConfig, err := loadDBConfig()
if err != nil {
log.Fatalf("Failed to load configuration: %v", err)
}
sqlDB, err := connectDB(dbConfig)
if err != nil {
log.Fatalf("Failed to connect to database: %v", err)
}
defer sqlDB.Close()
if err := sqlDB.Ping(); err != nil {
log.Fatalf("Failed to ping database: %v", err)
}
// Initialize DB layer and handler
db := NewDB(sqlDB)
fixedDBName := os.Getenv("DB_NAME")
handler := NewHandler(db, fixedDBName)
s := server.NewMCPServer(
"mysql-schema-mcp",
Version,
)
// Build list_tables tool options
listTablesOpts := []mcp.ToolOption{
mcp.WithDescription("Returns a list of table information in the MySQL database."),
}
if fixedDBName == "" {
listTablesOpts = append(listTablesOpts, mcp.WithString("dbName",
mcp.Required(),
mcp.Description("The name of the database to retrieve information from."),
))
}
s.AddTool(
mcp.NewTool("list_tables", listTablesOpts...),
handler.ListTables,
)
// Build describe_tables tool options
describeTablesOpts := []mcp.ToolOption{
mcp.WithDescription("Returns detailed information for the specified tables."),
}
if fixedDBName == "" {
describeTablesOpts = append(describeTablesOpts, mcp.WithString("dbName",
mcp.Required(),
mcp.Description("The name of the database to retrieve information from."),
))
}
describeTablesOpts = append(describeTablesOpts, mcp.WithArray(
"tableNames",
mcp.Items(
map[string]interface{}{
"type": "string",
},
),
mcp.Required(),
mcp.Description("The names of the tables to retrieve detailed information for (multiple names can be specified)."),
))
s.AddTool(
mcp.NewTool("describe_tables", describeTablesOpts...),
handler.DescribeTables,
)
if err := server.ServeStdio(s); err != nil {
fmt.Printf("Server error: %v\n", err)
}
}
func loadDBConfig() (DBConfig, error) {
host := os.Getenv("DB_HOST")
if host == "" {
host = "localhost"
}
port := os.Getenv("DB_PORT")
if port == "" {
port = "3306"
}
user := os.Getenv("DB_USER")
if user == "" {
return DBConfig{}, fmt.Errorf("DB_USER environment variable is not set")
}
password := os.Getenv("DB_PASSWORD")
return DBConfig{
Host: host,
Port: port,
User: user,
Password: password,
}, nil
}
```
--------------------------------------------------------------------------------
/view.go:
--------------------------------------------------------------------------------
```go
package main
import (
"fmt"
"strings"
"text/template"
)
// ListTablesData is the data structure passed to the ListTables template
type ListTablesData struct {
DBName string
Tables []TableSummary
}
// listTablesTemplate is the output format for ListTables
const listTablesTemplate = `Tables in database "{{.DBName}}" (Total: {{len .Tables}})
Format: Table Name - Table Comment [PK: Primary Key] [UK: Unique Key 1; Unique Key 2...] [FK: Foreign Key -> Referenced Table.Column; ...]
* Composite keys (keys composed of multiple columns) are grouped in parentheses: (col1, col2)
* Multiple different key constraints are separated by semicolons: key1; key2
{{range .Tables -}}
- {{.Name}} - {{.Comment}}{{if len .PK}} [PK: {{formatPK .PK}}]{{end}}{{if len .UK}} [UK: {{formatUK .UK}}]{{end}}{{if len .FK}} [FK: {{formatFK .FK}}]{{end}}
{{end -}}
`
// TableDetail holds detailed information for individual tables (uses types from db.go)
type TableDetail struct {
Name string
Comment string
Columns []ColumnInfo
PrimaryKeys []string
UniqueKeys []UniqueKey
ForeignKeys []ForeignKey
Indexes []IndexInfo
}
// describeTableDetailTemplate is the output format for describe_tables
const describeTableDetailTemplate = `# Table: {{.Name}}{{if .Comment}} - {{.Comment}}{{end}}
## Columns{{range .Columns}}
{{formatColumn .}}{{end}}
## Key Information{{if .PrimaryKeys}}
[PK: {{formatPK .PrimaryKeys}}]{{end}}{{if .UniqueKeys}}
[UK: {{formatUK .UniqueKeys}}]{{end}}{{if .ForeignKeys}}
[FK: {{formatFK .ForeignKeys}}]{{end}}{{if .Indexes}}
[INDEX: {{formatIndex .Indexes}}]{{end}}
`
var funcMap = template.FuncMap{
"formatPK": formatPK,
"formatUK": formatUK,
"formatFK": formatFK,
"formatColumn": formatColumn,
"formatIndex": formatIndex,
}
// formatPK formats primary key information
func formatPK(pk []string) string {
if len(pk) == 0 {
return ""
}
pkStr := strings.Join(pk, ", ")
if len(pk) > 1 {
pkStr = fmt.Sprintf("(%s)", pkStr)
}
return pkStr
}
// formatUK formats unique key information
func formatUK(uk []UniqueKey) string {
if len(uk) == 0 {
return ""
}
var ukInfo []string
for _, k := range uk {
if len(k.Columns) > 1 {
ukInfo = append(ukInfo, fmt.Sprintf("(%s)", strings.Join(k.Columns, ", ")))
} else {
ukInfo = append(ukInfo, strings.Join(k.Columns, ", "))
}
}
return strings.Join(ukInfo, "; ")
}
// formatFK formats foreign key information
func formatFK(fk []ForeignKey) string {
if len(fk) == 0 {
return ""
}
var fkInfo []string
for _, k := range fk {
colStr := strings.Join(k.Columns, ", ")
refColStr := strings.Join(k.RefColumns, ", ")
if len(k.Columns) > 1 {
colStr = fmt.Sprintf("(%s)", colStr)
}
if len(k.RefColumns) > 1 {
refColStr = fmt.Sprintf("(%s)", refColStr)
}
fkInfo = append(fkInfo, fmt.Sprintf("%s -> %s.%s",
colStr,
k.RefTable,
refColStr))
}
return strings.Join(fkInfo, "; ")
}
// formatColumn formats column information
func formatColumn(col ColumnInfo) string {
nullable := "NOT NULL"
if col.IsNullable == "YES" {
nullable = "NULL"
}
defaultValue := ""
if col.Default.Valid {
defaultValue = fmt.Sprintf(" DEFAULT %s", col.Default.String)
}
comment := ""
if col.Comment != "" {
comment = fmt.Sprintf(" [%s]", col.Comment)
}
return fmt.Sprintf("- %s: %s %s%s%s",
col.Name, col.Type, nullable, defaultValue, comment)
}
func formatIndex(idx []IndexInfo) string {
if len(idx) == 0 {
return ""
}
var idxInfo []string
for _, i := range idx {
if len(i.Columns) > 1 {
idxInfo = append(idxInfo, fmt.Sprintf("(%s)", strings.Join(i.Columns, ", ")))
} else {
idxInfo = append(idxInfo, strings.Join(i.Columns, ", "))
}
}
return strings.Join(idxInfo, "; ")
}
```
--------------------------------------------------------------------------------
/docs/requirements.md:
--------------------------------------------------------------------------------
```markdown
# MySQL Schema MCP Server Requirements
## Overview
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.
## Environment Variables
- **DB_HOST**: Database host name
- **DB_PORT**: Database port number
- **DB_USER**: Database username
- **DB_PASSWORD**: Database password
## MCP Server Tools
1. **Tool: `list_tables`**
- Description: Returns a list of all table names in the specified database.
- Arguments: `dbName` (string) - The name of the database to get information from.
- Return Value: A list of table names, table comments, and key information (in text format).
- Output Format:
```text
Tables in database "DB_NAME" (Total: X)
Format: Table Name - Table Comment [PK: Primary Key] [UK: Unique Key 1; Unique Key 2...] [FK: Foreign Key -> Referenced Table.Column; ...]
* Composite keys (keys made of multiple columns) are grouped in parentheses: (col1, col2)
* Multiple different key constraints are separated by semicolons: key1; key2
- users - User information [PK: id] [UK: email; username] [FK: role_id -> roles.id; department_id -> departments.id]
- posts - Post information [PK: id] [UK: slug] [FK: user_id -> users.id; category_id -> categories.id]
- order_items - Order items [PK: (order_id, item_id)] [FK: (order_id, item_id) -> orders.(id, item_id); product_id -> products.id]
```
2. **Tool: `describe_tables`**
- Description: Returns detailed information for the specified tables, such as column info, indexes, and foreign key constraints.
- Arguments:
- `dbName` (string) - The name of the database to get information from.
- `tableNames` (array of strings) - The names of the tables to get detailed information for (you can specify multiple names).
- Return Value: Formatted text with detailed information for each table.
- Output Format:
```text
# Table: order_items - Order Items
## Columns
- order_id: int(11) NOT NULL [Order ID]
- item_id: int(11) NOT NULL [Item ID]
- product_id: int(11) NOT NULL [Product ID]
- quantity: int(11) NOT NULL [Quantity]
- price: decimal(10,2) NOT NULL [Price]
- user_id: int(11) NOT NULL [User ID]
## Key Information
[PK: (order_id, item_id)]
[UK: (user_id, product_id)]
[FK: (order_id, item_id) -> orders.(id, item_id); product_id -> products.id; user_id -> users.id]
[INDEX: price; quantity]
---
# Table: users - User Information
## Columns
- id: int(11) NOT NULL [User ID]
- username: varchar(50) NOT NULL [Username]
- email: varchar(100) NOT NULL [Email Address]
- password: varchar(255) NOT NULL [Password]
- created_at: timestamp NULL DEFAULT CURRENT_TIMESTAMP [Created At]
## Key Information
[PK: id]
[UK: email; username]
[INDEX: created_at]
```
If you specify multiple tables, a separator line (`---`) will be inserted between each table's information.
## Implementation Steps
1. **Project Setup**
- Install the MCP library.
- Install necessary dependencies (like MySQL client library).
2. **MCP Server Initialization**
- Create the server instance and set its name.
3. **Load Environment Variables**
- Read environment variables when the server starts to set up database connection info.
4. **Database Connection Helper**
- Implement a helper function to manage database connections.
- The database name will be received as an argument in tool calls.
5. **Implement Tools**
- Implement each tool function.
- Run appropriate database queries within the tools and format the results.
6. **Run the Server**
- Set up the server to communicate with the client using standard input/output (stdio).
## Progress
- [x] Project Setup
- [x] MCP Server Initialization
- [x] Load Environment Variables
- [x] Database Connection Helper Implementation
- [x] Implement `list_tables` tool
- [x] Implement `describe_tables` tool
- [x] Receive DB_NAME as a tool call argument, not an environment variable
- [ ] Adjust security settings
```
--------------------------------------------------------------------------------
/handler_test.go:
--------------------------------------------------------------------------------
```go
package main
import (
"testing"
"github.com/mark3labs/mcp-go/mcp"
"github.com/stretchr/testify/assert"
"github.com/stretchr/testify/require"
)
func TestListTables(t *testing.T) {
dbConn := setupTestDB(t, "testdata/schema.sql")
db := NewDB(dbConn)
handler := NewHandler(db, "")
ctx := t.Context()
req := mcp.CallToolRequest{
Params: struct {
Name string `json:"name"`
Arguments map[string]interface{} `json:"arguments,omitempty"`
Meta *struct {
ProgressToken mcp.ProgressToken `json:"progressToken,omitempty"`
} `json:"_meta,omitempty"`
}{
// Name: "ListTables", // Set tool name if necessary
Arguments: map[string]interface{}{
"dbName": testDBName,
},
},
}
// --- Act ---
result, err := handler.ListTables(ctx, req)
// --- Assert ---
require.NoError(t, err, "handler.ListTables should not return an error")
require.NotNil(t, result, "handler.ListTables should return a result")
expectedOutput := `Tables in database "` + testDBName + `" (Total: 4)
Format: Table Name - Table Comment [PK: Primary Key] [UK: Unique Key 1; Unique Key 2...] [FK: Foreign Key -> Referenced Table.Column; ...]
* Composite keys (keys composed of multiple columns) are grouped in parentheses: (col1, col2)
* Multiple different key constraints are separated by semicolons: key1; key2
- 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)]
- orders - Order header [PK: id] [FK: user_id -> users.id]
- products - Product master [PK: product_code] [UK: (maker_code, internal_code)]
- users - User information [PK: id] [UK: email; (tenant_id, employee_id); username]
`
textContent := result.Content[0].(mcp.TextContent).Text
assert.Equal(t, expectedOutput, textContent, "Output content should match the expected format")
}
func TestDescribeTables(t *testing.T) {
// --- Arrange ---
dbConn := setupTestDB(t, "testdata/schema.sql") // Prepare test DB and schema
db := NewDB(dbConn)
handler := NewHandler(db, "")
ctx := t.Context()
req := mcp.CallToolRequest{
Params: struct {
Name string `json:"name"`
Arguments map[string]interface{} `json:"arguments,omitempty"`
Meta *struct {
ProgressToken mcp.ProgressToken `json:"progressToken,omitempty"`
} `json:"_meta,omitempty"`
}{
// Name: "DescribeTables", // Set tool name if necessary
Arguments: map[string]interface{}{
"dbName": testDBName,
"tableNames": []interface{}{"users", "products", "order_items"}, // Specify multiple tables
},
},
}
expectedOutput := `# Table: users - User information
## Columns
- id: int NOT NULL [User system ID]
- email: varchar(255) NOT NULL [Email address]
- username: varchar(255) NOT NULL [Username]
- tenant_id: int NOT NULL [Tenant ID]
- employee_id: int NOT NULL [Employee ID]
## Key Information
[PK: id]
[UK: email; (tenant_id, employee_id); username]
---
# Table: products - Product master
## Columns
- product_code: varchar(50) NOT NULL [Product code (Primary Key)]
- maker_code: varchar(50) NOT NULL [Maker code]
- internal_code: varchar(50) NOT NULL [Internal product code]
- product_name: varchar(255) NULL [Product name]
## Key Information
[PK: product_code]
[UK: (maker_code, internal_code)]
[INDEX: (maker_code, product_name); product_name]
---
# Table: order_items - Order details
## Columns
- order_id: int NOT NULL [Order ID (FK)]
- item_seq: int NOT NULL [Order item sequence number]
- product_maker: varchar(50) NOT NULL [Product maker code (FK)]
- product_internal_code: varchar(50) NOT NULL [Product internal code (FK)]
- quantity: int NOT NULL [Quantity]
## Key Information
[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)]
[INDEX: (product_maker, product_internal_code)]
`
// --- Act ---
result, err := handler.DescribeTables(ctx, req)
// --- Assert ---
require.NoError(t, err, "handler.DescribeTables should not return an error")
require.NotNil(t, result, "handler.DescribeTables should return a result")
require.Len(t, result.Content, 1, "Result should contain one content item")
require.IsType(t, mcp.TextContent{}, result.Content[0], "Content item should be TextContent")
textContent := result.Content[0].(mcp.TextContent).Text
assert.Equal(t, expectedOutput, textContent, "Output content should match the expected format")
}
```
--------------------------------------------------------------------------------
/handler.go:
--------------------------------------------------------------------------------
```go
package main
import (
"bytes"
"context"
"fmt"
"text/template"
"github.com/mark3labs/mcp-go/mcp"
)
// Handler struct implements the MCP handler
type Handler struct {
db *DB
fixedDBName string
}
func NewHandler(db *DB, fixedDBName string) *Handler {
return &Handler{db: db, fixedDBName: fixedDBName}
}
// getDatabaseName extracts the database name from the request or returns the fixed DB name
func (h *Handler) getDatabaseName(request mcp.CallToolRequest) (string, error) {
// Use fixed DB name if set
if h.fixedDBName != "" {
return h.fixedDBName, nil
}
// Otherwise get from request
dbNameRaw, ok := request.Params.Arguments["dbName"]
if !ok {
return "", fmt.Errorf("database name is not specified")
}
dbName, ok := dbNameRaw.(string)
if !ok || dbName == "" {
return "", fmt.Errorf("database name is not specified correctly")
}
return dbName, nil
}
// ListTables returns summary information for all tables
func (h *Handler) ListTables(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
dbName, err := h.getDatabaseName(request)
if err != nil {
return mcp.NewToolResultError(err.Error()), nil
}
// Get table information
tables, err := h.db.FetchAllTableSummaries(ctx, dbName)
if err != nil {
return mcp.NewToolResultError(fmt.Sprintf("Failed to get table information: %v", err)), nil
}
// No tables found
if len(tables) == 0 {
return mcp.NewToolResultText("No tables exist in the database."), nil
}
// Create output
var output bytes.Buffer
{
tmpl, err := template.New("listTables").Funcs(funcMap).Parse(listTablesTemplate)
if err != nil {
return mcp.NewToolResultError(fmt.Sprintf("Failed to parse template: %v", err)), nil
}
if err := tmpl.Execute(&output, ListTablesData{
DBName: dbName,
Tables: tables,
}); err != nil {
return mcp.NewToolResultError(fmt.Sprintf("Failed to execute template: %v", err)), nil
}
}
return mcp.NewToolResultText(output.String()), nil
}
// DescribeTables is a handler method that returns detailed information for the specified tables
func (h *Handler) DescribeTables(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
dbName, err := h.getDatabaseName(request)
if err != nil {
return mcp.NewToolResultError(err.Error()), nil
}
// Create list of table names
tableNamesRaw, ok := request.Params.Arguments["tableNames"]
if !ok {
return mcp.NewToolResultError("Table names are not specified"), nil
}
tableNamesInterface, ok := tableNamesRaw.([]interface{})
if !ok || len(tableNamesInterface) == 0 {
return mcp.NewToolResultError("Array of table names is not specified correctly"), nil
}
var tableNames []string
for _, v := range tableNamesInterface {
if tableName, ok := v.(string); ok && tableName != "" {
tableNames = append(tableNames, tableName)
}
}
if len(tableNames) == 0 {
return mcp.NewToolResultError("No valid table names are specified"), nil
}
allTables, err := h.db.FetchAllTableSummaries(ctx, dbName)
if err != nil {
return mcp.NewToolResultError(fmt.Sprintf("Failed to get table information: %v", err)), nil
}
// Prepare output
var output bytes.Buffer
tmpl, err := template.New("describeTableDetail").Funcs(funcMap).Parse(describeTableDetailTemplate)
if err != nil {
return mcp.NewToolResultError(fmt.Sprintf("Failed to parse template: %v", err)), nil
}
// Get information for all tables
for i, tableName := range tableNames {
// Add a separator line before the second and subsequent tables
if i > 0 {
output.WriteString("\n---\n\n")
}
// Find the specified table
var tableInfo TableSummary
var tableFound bool
for _, t := range allTables {
if t.Name == tableName {
tableInfo = t
tableFound = true
break
}
}
if !tableFound {
output.WriteString(fmt.Sprintf("# Table: %s\nTable not found\n", tableName))
continue
}
// Get table detail information
primaryKeys, err := h.db.FetchPrimaryKeys(ctx, dbName, tableName)
if err != nil {
return mcp.NewToolResultError(fmt.Sprintf("Failed to get primary key information: %v", err)), nil
}
uniqueKeys, err := h.db.FetchUniqueKeys(ctx, dbName, tableName)
if err != nil {
return mcp.NewToolResultError(fmt.Sprintf("Failed to get unique key information: %v", err)), nil
}
foreignKeys, err := h.db.FetchForeignKeys(ctx, dbName, tableName)
if err != nil {
return mcp.NewToolResultError(fmt.Sprintf("Failed to get foreign key information: %v", err)), nil
}
columns, err := h.db.FetchTableColumns(ctx, dbName, tableName)
if err != nil {
return mcp.NewToolResultError(fmt.Sprintf("Failed to get column information: %v", err)), nil
}
indexes, err := h.db.FetchTableIndexes(ctx, dbName, tableName)
if err != nil {
return mcp.NewToolResultError(fmt.Sprintf("Failed to get index information: %v", err)), nil
}
// Create data to pass to the template
tableDetail := TableDetail{
Name: tableName,
Comment: tableInfo.Comment,
Columns: columns,
PrimaryKeys: primaryKeys,
UniqueKeys: uniqueKeys,
ForeignKeys: foreignKeys,
Indexes: indexes,
}
// Execute the template and write to the buffer
if err := tmpl.Execute(&output, tableDetail); err != nil {
return mcp.NewToolResultError(fmt.Sprintf("Failed to execute template: %v", err)), nil
}
}
return mcp.NewToolResultText(output.String()), nil
}
```
--------------------------------------------------------------------------------
/docs/mcp-server-implementing-guide.md:
--------------------------------------------------------------------------------
```markdown
# Model Context Protocol (MCP) Server Implementation Guide
## What is MCP?
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.
## Basic Architecture
MCP uses a client-server model:
- **MCP Server**: A lightweight program that provides access to data sources or tools.
- **MCP Host/Client**: An LLM application like Claude Desktop. It connects to MCP servers to use their features.
## Main Features of an MCP Server
MCP servers can offer three main types of features:
1. **Tools**: Functions that the LLM can call (with user approval).
2. **Resources**: Data in a file-like format that the client can read (like API responses or file content).
3. **Prompts**: Standard templates that help with specific tasks.
## Communication Protocols
MCP servers support these communication methods:
- **Standard Input/Output (stdio)**: A simple method suitable for local development.
- **Server-Sent Events (SSE)**: A more flexible method for distributed teams.
- **WebSockets**: For real-time, two-way communication.
## What You Need to Build
### 1. Basic Structure
To implement an MCP server, you need these elements:
- Server initialization and transport setup.
- Definitions for tools, resources, or prompts.
- Implementation of request handlers.
- The main function to run the server.
### 2. Server Configuration
The server configuration includes this information:
```json
{
"mcpServers": {
"myserver": {
"command": "command_to_execute",
"args": ["arg1", "arg2"],
"env": {
"ENV_VAR_NAME": "value"
}
}
}
}
```
## How to Implement in Different Languages
You can implement MCP servers in various programming languages:
### Python
```python
# Install necessary libraries
# pip install mcp[cli]
import asyncio
import mcp
from mcp.server import NotificationOptions, InitializationOptions
# Example tool definition
@mcp.server.tool("tool_name", "Description of the tool")
async def some_tool(param1: str, param2: int) -> str:
# Implement the tool's logic
return "Result"
# Initialize the server
server = mcp.server.Server()
# Main function
async def main():
# Run the server with stdin/stdout streams
async with mcp.server.stdio.stdio_server() as (read_stream, write_stream):
await server.run(
read_stream, write_stream,
InitializationOptions(
server_name="Server Name",
server_version="Version",
capabilities=server.get_capabilities(
notification_options=NotificationOptions(),
experimental_capabilities={},
),
),
)
if __name__ == "__main__":
asyncio.run(main())
```
### TypeScript/JavaScript
```typescript
// Install necessary libraries
// npm install @modelcontextprotocol/sdk
import { Server, StdioServerTransport } from "@modelcontextprotocol/sdk";
// Create a server instance
const server = new Server();
// Define a tool
const myTool = {
name: "tool_name",
description: "Description of the tool",
parameters: {
// Define parameters
},
execute: async (params) => {
// Implement the tool's logic
return { result: "Result" };
}
};
// Register the tool
server.tools.registerTool(myTool);
// Main function
async function main() {
// Set up transport
const transport = new StdioServerTransport();
await server.connect(transport);
console.error("Server running");
}
main().catch((error) => {
console.error("Error:", error);
process.exit(1);
});
```
## Security Points to Consider
When implementing an MCP server, consider these security aspects:
- **Access Control**: Limit access to the data and tools the server exposes.
- **Authentication**: Mechanisms to verify the client.
- **Data Protection**: Handle sensitive data properly.
- **Resource Limiting**: Limit resource usage to prevent Denial of Service (DoS) attacks.
## Best Practices
1. **Clear Documentation**: Provide detailed descriptions for each tool, resource, and prompt.
2. **Error Handling**: Return appropriate error messages and status codes.
3. **Versioning**: Manage API versions for compatibility.
4. **Testing**: Perform unit tests and integration tests.
5. **Logging**: Implement logging for debugging and auditing.
## Examples of Existing MCP Servers
- **File System**: A secure server for file operations.
- **PostgreSQL**: A server for database access.
- **GitHub**: Provides features for repository and issue management.
- **Brave Search**: Offers web search functionality.
## Connecting with Claude Desktop
To connect your MCP server with Claude Desktop:
1. Install Claude Desktop.
2. Edit `~/Library/Application Support/Claude/claude_desktop_config.json`.
3. Add your custom server to the `mcpServers` section.
```json
{
"mcpServers": {
"myserver": {
"command": "command_to_execute",
"args": ["arg1", "arg2"]
}
}
}
```
## Debugging and Troubleshooting
1. **Log Output**: Record detailed debug information.
2. **Step-by-Step Testing**: Test from basic features to complex ones.
3. **Error Codes**: Implement clear error codes and messages.
4. **MCP Inspector**: Use debugging tools to check behavior.
## Summary
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.
```
--------------------------------------------------------------------------------
/db.go:
--------------------------------------------------------------------------------
```go
package main
import (
"context"
"database/sql"
"fmt"
)
type DBConfig struct {
Host string
Port string
User string
Password string
}
type TableSummary struct {
Name string
Comment string
PK []string // Primary key columns
UK []UniqueKey // Unique key information
FK []ForeignKey // Foreign key information
}
type UniqueKey struct {
Name string
Columns []string
}
type ForeignKey struct {
Name string
Columns []string
RefTable string
RefColumns []string
}
type ColumnInfo struct {
Name string
Type string
IsNullable string
Default sql.NullString
Comment string
}
type IndexInfo struct {
Name string
Columns []string
Unique bool
}
type DB struct {
conn *sql.DB
}
func NewDB(conn *sql.DB) *DB {
return &DB{conn: conn}
}
func connectDB(config DBConfig) (*sql.DB, error) {
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/",
config.User, config.Password, config.Host, config.Port)
conn, err := sql.Open("mysql", dsn)
if err != nil {
return nil, err
}
return conn, nil
}
// FetchAllTableSummaries gets summary information for all tables in the database
func (db *DB) FetchAllTableSummaries(ctx context.Context, dbName string) ([]TableSummary, error) {
tables, err := db.FetchTableWithComments(ctx, dbName)
if err != nil {
return nil, err
}
// Get additional information for each table
for i := range tables {
tables[i].PK, err = db.FetchPrimaryKeys(ctx, dbName, tables[i].Name)
if err != nil {
return nil, err
}
tables[i].UK, err = db.FetchUniqueKeys(ctx, dbName, tables[i].Name)
if err != nil {
return nil, err
}
tables[i].FK, err = db.FetchForeignKeys(ctx, dbName, tables[i].Name)
if err != nil {
return nil, err
}
}
return tables, nil
}
// FetchTableWithComments gets table names and comments
func (db *DB) FetchTableWithComments(ctx context.Context, dbName string) ([]TableSummary, error) {
query := `
SELECT
TABLE_NAME,
IFNULL(TABLE_COMMENT, '') AS TABLE_COMMENT
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = ?
ORDER BY
TABLE_NAME
`
rows, err := db.conn.QueryContext(ctx, query, dbName)
if err != nil {
return nil, err
}
defer rows.Close()
var tables []TableSummary
for rows.Next() {
var table TableSummary
if err := rows.Scan(&table.Name, &table.Comment); err != nil {
return nil, err
}
tables = append(tables, table)
}
if err := rows.Err(); err != nil {
return nil, err
}
return tables, nil
}
// FetchPrimaryKeys gets the primary key columns of a table
func (db *DB) FetchPrimaryKeys(ctx context.Context, dbName string, tableName string) ([]string, error) {
query := `
SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
CONSTRAINT_SCHEMA = ?
AND TABLE_NAME = ?
AND CONSTRAINT_NAME = 'PRIMARY'
ORDER BY
ORDINAL_POSITION
`
rows, err := db.conn.QueryContext(ctx, query, dbName, tableName)
if err != nil {
return nil, err
}
defer rows.Close()
var primaryKeys []string
for rows.Next() {
var columnName string
if err := rows.Scan(&columnName); err != nil {
return nil, err
}
primaryKeys = append(primaryKeys, columnName)
}
if err := rows.Err(); err != nil {
return nil, err
}
return primaryKeys, nil
}
// FetchUniqueKeys gets the unique key constraints of a table
func (db *DB) FetchUniqueKeys(ctx context.Context, dbName string, tableName string) ([]UniqueKey, error) {
query := `
SELECT
kcu.CONSTRAINT_NAME,
kcu.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON
kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND kcu.TABLE_NAME = tc.TABLE_NAME
WHERE
kcu.TABLE_SCHEMA = ?
AND kcu.TABLE_NAME = ?
AND tc.CONSTRAINT_TYPE = 'UNIQUE'
ORDER BY
kcu.CONSTRAINT_NAME,
kcu.ORDINAL_POSITION
`
rows, err := db.conn.QueryContext(ctx, query, dbName, tableName)
if err != nil {
return nil, err
}
defer rows.Close()
// Build information while maintaining the order of SQL acquisition
var uniqueKeys []UniqueKey
var currentUniqueKey *UniqueKey
for rows.Next() {
var constraintName, columnName string
if err := rows.Scan(&constraintName, &columnName); err != nil {
return nil, err
}
if currentUniqueKey == nil || currentUniqueKey.Name != constraintName {
// When it's the first one, or when switching to another UK
newUK := UniqueKey{
Name: constraintName,
Columns: []string{},
}
uniqueKeys = append(uniqueKeys, newUK)
currentUniqueKey = &uniqueKeys[len(uniqueKeys)-1]
}
// If the constraint name is the same, add to the Columns of the current UniqueKey
currentUniqueKey.Columns = append(currentUniqueKey.Columns, columnName)
}
if err := rows.Err(); err != nil {
return nil, err
}
return uniqueKeys, nil
}
// FetchForeignKeys gets the foreign key constraints of a table
func (db *DB) FetchForeignKeys(ctx context.Context, dbName string, tableName string) ([]ForeignKey, error) {
query := `
SELECT
kcu.CONSTRAINT_NAME,
kcu.COLUMN_NAME,
kcu.REFERENCED_TABLE_NAME,
kcu.REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON
kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
WHERE
kcu.TABLE_SCHEMA = ?
AND kcu.TABLE_NAME = ?
AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY
kcu.CONSTRAINT_NAME,
kcu.ORDINAL_POSITION
`
rows, err := db.conn.QueryContext(ctx, query, dbName, tableName)
if err != nil {
return nil, err
}
defer rows.Close()
// Build information while maintaining the order of SQL acquisition
var foreignKeys []ForeignKey
var currentFK *ForeignKey
for rows.Next() {
var constraintName, columnName, refTableName, refColumnName string
if err := rows.Scan(&constraintName, &columnName, &refTableName, &refColumnName); err != nil {
return nil, err
}
if currentFK == nil || currentFK.Name != constraintName {
newFK := ForeignKey{
Name: constraintName,
RefTable: refTableName,
}
foreignKeys = append(foreignKeys, newFK)
currentFK = &foreignKeys[len(foreignKeys)-1]
}
currentFK.Columns = append(currentFK.Columns, columnName)
currentFK.RefColumns = append(currentFK.RefColumns, refColumnName)
}
if err := rows.Err(); err != nil {
return nil, err
}
return foreignKeys, nil
}
// FetchTableColumns gets the column information of a table
func (db *DB) FetchTableColumns(ctx context.Context, dbName string, tableName string) ([]ColumnInfo, error) {
query := `
SELECT
COLUMN_NAME,
COLUMN_TYPE,
IS_NULLABLE,
COLUMN_DEFAULT,
IFNULL(COLUMN_COMMENT, '') AS COLUMN_COMMENT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = ?
AND TABLE_NAME = ?
ORDER BY
ORDINAL_POSITION
`
rows, err := db.conn.QueryContext(ctx, query, dbName, tableName)
if err != nil {
return nil, err
}
defer rows.Close()
var columns []ColumnInfo
for rows.Next() {
var col ColumnInfo
if err := rows.Scan(&col.Name, &col.Type, &col.IsNullable, &col.Default, &col.Comment); err != nil {
return nil, err
}
columns = append(columns, col)
}
if err := rows.Err(); err != nil {
return nil, err
}
return columns, nil
}
// FetchTableIndexes gets the index information of a table
func (db *DB) FetchTableIndexes(ctx context.Context, dbName string, tableName string) ([]IndexInfo, error) {
query := `
SELECT
INDEX_NAME,
COLUMN_NAME,
NON_UNIQUE
FROM
INFORMATION_SCHEMA.STATISTICS
WHERE
TABLE_SCHEMA = ?
AND TABLE_NAME = ?
AND INDEX_NAME != 'PRIMARY'
AND INDEX_NAME NOT IN (
SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = ?
AND TABLE_NAME = ?
AND CONSTRAINT_TYPE IN ('UNIQUE', 'FOREIGN KEY')
)
ORDER BY
INDEX_NAME,
SEQ_IN_INDEX
`
rows, err := db.conn.QueryContext(ctx, query, dbName, tableName, dbName, tableName)
if err != nil {
return nil, err
}
defer rows.Close()
// Build information while maintaining the order of SQL acquisition
var indexes []IndexInfo
var currentIdx *IndexInfo
for rows.Next() {
var indexName, columnName string
var nonUnique bool
if err := rows.Scan(&indexName, &columnName, &nonUnique); err != nil {
return nil, err
}
if currentIdx == nil || currentIdx.Name != indexName {
newIdx := IndexInfo{
Name: indexName,
Unique: !nonUnique,
Columns: []string{},
}
indexes = append(indexes, newIdx)
currentIdx = &indexes[len(indexes)-1]
}
currentIdx.Columns = append(currentIdx.Columns, columnName)
}
if err := rows.Err(); err != nil {
return nil, err
}
return indexes, nil
}
```
--------------------------------------------------------------------------------
/e2e_test.go:
--------------------------------------------------------------------------------
```go
package main
import (
"bufio"
"encoding/json"
"fmt"
"io"
"os"
"os/exec"
"testing"
"time"
"github.com/stretchr/testify/assert"
"github.com/stretchr/testify/require"
)
type jsonRPCRequest struct {
ID interface{} `json:"id,omitempty"`
Method string `json:"method"`
Params map[string]interface{} `json:"params,omitempty"`
}
type jsonRPCResponse struct {
ID interface{} `json:"id"`
Result json.RawMessage `json:"result,omitempty"`
Error map[string]interface{} `json:"error,omitempty"`
}
type mcpServer struct {
cmd *exec.Cmd
stdin io.WriteCloser
stdout io.ReadCloser
reader *bufio.Reader
nextID int
}
func setupMCPServer(t *testing.T, env []string) *mcpServer {
cmd := exec.Command("go", "run", ".")
cmd.Env = append(os.Environ(), env...)
stdin, err := cmd.StdinPipe()
require.NoError(t, err)
stdout, err := cmd.StdoutPipe()
require.NoError(t, err)
err = cmd.Start()
require.NoError(t, err)
server := &mcpServer{
cmd: cmd,
stdin: stdin,
stdout: stdout,
reader: bufio.NewReader(stdout),
nextID: 1,
}
t.Cleanup(func() {
stdin.Close()
cmd.Process.Kill()
cmd.Wait()
})
// Wait for server to be ready
time.Sleep(100 * time.Millisecond)
return server
}
func initializeMCPServer(t *testing.T, server *mcpServer) {
// Send initialize request
initReq := jsonRPCRequest{
Method: "initialize",
}
server.sendRequest(t, initReq)
// Read initialize response
initResp := server.readResponse(t)
require.Empty(t, initResp.Error, "Initialize should succeed")
// Send initialized notification
initializedReq := jsonRPCRequest{
Method: "notifications/initialized",
}
server.sendRequest(t, initializedReq)
}
func (s *mcpServer) sendRequest(t *testing.T, req jsonRPCRequest) {
// Auto-increment ID for requests (except notifications)
if req.Method != "notifications/initialized" && req.ID == nil {
req.ID = s.nextID
s.nextID++
}
// Convert to the actual JSON-RPC format with jsonrpc field
fullReq := map[string]interface{}{
"jsonrpc": "2.0",
"method": req.Method,
}
if req.ID != nil {
fullReq["id"] = req.ID
}
if req.Params != nil {
fullReq["params"] = req.Params
}
data, err := json.Marshal(fullReq)
require.NoError(t, err)
_, err = fmt.Fprintf(s.stdin, "%s\n", data)
require.NoError(t, err)
}
func (s *mcpServer) readResponse(t *testing.T) jsonRPCResponse {
line, err := s.reader.ReadBytes('\n')
require.NoError(t, err)
var resp jsonRPCResponse
err = json.Unmarshal(line, &resp)
require.NoError(t, err)
return resp
}
// Common test setup helper
func setupE2ETest(t *testing.T) *mcpServer {
config := createTestDBConfig(t)
_ = setupTestDB(t, "testdata/schema.sql")
env := []string{
fmt.Sprintf("DB_HOST=%s", config.Host),
fmt.Sprintf("DB_PORT=%s", config.Port),
fmt.Sprintf("DB_USER=%s", config.User),
fmt.Sprintf("DB_PASSWORD=%s", config.Password),
}
server := setupMCPServer(t, env)
initializeMCPServer(t, server)
return server
}
// Helper to send tools/call request
func (s *mcpServer) sendToolCallRequest(t *testing.T, toolName string, arguments map[string]interface{}) {
req := jsonRPCRequest{
Method: "tools/call",
Params: map[string]interface{}{
"name": toolName,
"arguments": arguments,
},
}
s.sendRequest(t, req)
}
// Helper to verify response and extract text content
func verifyTextResponse(t *testing.T, resp jsonRPCResponse) string {
// Check no error
assert.Empty(t, resp.Error)
// Parse result
var result map[string]interface{}
err := json.Unmarshal(resp.Result, &result)
require.NoError(t, err)
// Verify content
content, ok := result["content"].([]interface{})
require.True(t, ok)
require.Len(t, content, 1)
textContent := content[0].(map[string]interface{})
assert.Equal(t, "text", textContent["type"])
text := textContent["text"].(string)
return text
}
func TestE2EListTables(t *testing.T) {
server := setupE2ETest(t)
// Send list_tables request
server.sendToolCallRequest(t, "list_tables", map[string]interface{}{
"dbName": testDBName,
})
// Read and verify response
resp := server.readResponse(t)
text := verifyTextResponse(t, resp)
expectedText := `Tables in database "test_mysql_schema_explorer_mcp" (Total: 4)
Format: Table Name - Table Comment [PK: Primary Key] [UK: Unique Key 1; Unique Key 2...] [FK: Foreign Key -> Referenced Table.Column; ...]
* Composite keys (keys composed of multiple columns) are grouped in parentheses: (col1, col2)
* Multiple different key constraints are separated by semicolons: key1; key2
- 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)]
- orders - Order header [PK: id] [FK: user_id -> users.id]
- products - Product master [PK: product_code] [UK: (maker_code, internal_code)]
- users - User information [PK: id] [UK: email; (tenant_id, employee_id); username]
`
assert.Equal(t, expectedText, text)
}
func TestE2EDescribeTables(t *testing.T) {
server := setupE2ETest(t)
// Send describe_tables request
server.sendToolCallRequest(t, "describe_tables", map[string]interface{}{
"dbName": testDBName,
"tableNames": []string{"users", "products", "order_items"},
})
// Read and verify response
resp := server.readResponse(t)
text := verifyTextResponse(t, resp)
expectedText := `# Table: users - User information
## Columns
- id: int NOT NULL [User system ID]
- email: varchar(255) NOT NULL [Email address]
- username: varchar(255) NOT NULL [Username]
- tenant_id: int NOT NULL [Tenant ID]
- employee_id: int NOT NULL [Employee ID]
## Key Information
[PK: id]
[UK: email; (tenant_id, employee_id); username]
---
# Table: products - Product master
## Columns
- product_code: varchar(50) NOT NULL [Product code (Primary Key)]
- maker_code: varchar(50) NOT NULL [Maker code]
- internal_code: varchar(50) NOT NULL [Internal product code]
- product_name: varchar(255) NULL [Product name]
## Key Information
[PK: product_code]
[UK: (maker_code, internal_code)]
[INDEX: (maker_code, product_name); product_name]
---
# Table: order_items - Order details
## Columns
- order_id: int NOT NULL [Order ID (FK)]
- item_seq: int NOT NULL [Order item sequence number]
- product_maker: varchar(50) NOT NULL [Product maker code (FK)]
- product_internal_code: varchar(50) NOT NULL [Product internal code (FK)]
- quantity: int NOT NULL [Quantity]
## Key Information
[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)]
[INDEX: (product_maker, product_internal_code)]
`
assert.Equal(t, expectedText, text)
}
func TestE2E_FixedDBMode(t *testing.T) {
config := createTestDBConfig(t)
_ = setupTestDB(t, "testdata/schema.sql")
// Setup with DB_NAME environment variable
env := []string{
fmt.Sprintf("DB_HOST=%s", config.Host),
fmt.Sprintf("DB_PORT=%s", config.Port),
fmt.Sprintf("DB_USER=%s", config.User),
fmt.Sprintf("DB_PASSWORD=%s", config.Password),
fmt.Sprintf("DB_NAME=%s", testDBName), // Fixed DB name
}
server := setupMCPServer(t, env)
initializeMCPServer(t, server)
t.Run("list_tables works without dbName in fixed mode", func(t *testing.T) {
// Send without dbName parameter
server.sendToolCallRequest(t, "list_tables", map[string]interface{}{})
resp := server.readResponse(t)
text := verifyTextResponse(t, resp)
// Should list tables from the fixed DB
assert.Contains(t, text, "Tables in database \"test_mysql_schema_explorer_mcp\" (Total: 4)")
assert.Contains(t, text, "users")
assert.Contains(t, text, "orders")
assert.Contains(t, text, "products")
assert.Contains(t, text, "order_items")
})
t.Run("describe_tables works without dbName in fixed mode", func(t *testing.T) {
// Send without dbName parameter
server.sendToolCallRequest(t, "describe_tables", map[string]interface{}{
"tableNames": []string{"users"},
})
resp := server.readResponse(t)
text := verifyTextResponse(t, resp)
// Should describe table from the fixed DB
assert.Contains(t, text, "# Table: users")
assert.Contains(t, text, "## Columns")
assert.Contains(t, text, "- id: int NOT NULL")
assert.Contains(t, text, "- email: varchar(255) NOT NULL")
assert.Contains(t, text, "- username: varchar(255) NOT NULL")
})
}
func TestE2E_ToolDefinitions(t *testing.T) {
findTool := func(tools []interface{}, name string) map[string]interface{} {
for _, tool := range tools {
toolMap := tool.(map[string]interface{})
if toolMap["name"] == name {
return toolMap
}
}
return nil
}
getToolProperties := func(tool map[string]interface{}) map[string]interface{} {
inputSchema, ok := tool["inputSchema"]
if !ok || inputSchema == nil {
return map[string]interface{}{}
}
properties, ok := inputSchema.(map[string]interface{})["properties"]
if !ok || properties == nil {
return map[string]interface{}{}
}
return properties.(map[string]interface{})
}
t.Run("normal mode has dbName parameter", func(t *testing.T) {
server := setupE2ETest(t) // Normal mode (no DB_NAME)
req := jsonRPCRequest{Method: "tools/list"}
server.sendRequest(t, req)
resp := server.readResponse(t)
var result map[string]interface{}
json.Unmarshal(resp.Result, &result)
tools := result["tools"].([]interface{})
assert.Len(t, tools, 2)
// Check list_tables has dbName parameter
listTables := findTool(tools, "list_tables")
properties := getToolProperties(listTables)
_, hasDBName := properties["dbName"]
assert.True(t, hasDBName, "list_tables should have dbName in normal mode")
// Check describe_tables has dbName parameter
describeTables := findTool(tools, "describe_tables")
properties = getToolProperties(describeTables)
_, hasDBName = properties["dbName"]
assert.True(t, hasDBName, "describe_tables should have dbName in normal mode")
})
t.Run("fixed mode has no dbName parameter", func(t *testing.T) {
config := createTestDBConfig(t)
_ = setupTestDB(t, "testdata/schema.sql")
env := []string{
fmt.Sprintf("DB_HOST=%s", config.Host),
fmt.Sprintf("DB_PORT=%s", config.Port),
fmt.Sprintf("DB_USER=%s", config.User),
fmt.Sprintf("DB_PASSWORD=%s", config.Password),
fmt.Sprintf("DB_NAME=%s", testDBName),
}
server := setupMCPServer(t, env)
initializeMCPServer(t, server)
req := jsonRPCRequest{Method: "tools/list"}
server.sendRequest(t, req)
resp := server.readResponse(t)
var result map[string]interface{}
json.Unmarshal(resp.Result, &result)
tools := result["tools"].([]interface{})
assert.Len(t, tools, 2)
// Check list_tables has no dbName parameter
listTables := findTool(tools, "list_tables")
properties := getToolProperties(listTables)
_, hasDBName := properties["dbName"]
assert.False(t, hasDBName, "list_tables should not have dbName in fixed mode")
// Check describe_tables has no dbName parameter
describeTables := findTool(tools, "describe_tables")
properties = getToolProperties(describeTables)
_, hasDBName = properties["dbName"]
assert.False(t, hasDBName, "describe_tables should not have dbName in fixed mode")
})
}
```
--------------------------------------------------------------------------------
/docs/mcp-go-cheatsheet.md:
--------------------------------------------------------------------------------
```markdown
## Quickstart
Let's create a simple MCP server that exposes a calculator tool and some data:
```go
package main
import (
"context"
"errors"
"fmt"
"github.com/mark3labs/mcp-go/mcp"
"github.com/mark3labs/mcp-go/server"
)
func main() {
// Create a new MCP server
s := server.NewMCPServer(
"Calculator Demo",
"1.0.0",
server.WithResourceCapabilities(true, true),
server.WithLogging(),
server.WithRecovery(),
)
// Add a calculator tool
calculatorTool := mcp.NewTool("calculate",
mcp.WithDescription("Perform basic arithmetic operations"),
mcp.WithString("operation",
mcp.Required(),
mcp.Description("The operation to perform (add, subtract, multiply, divide)"),
mcp.Enum("add", "subtract", "multiply", "divide"),
),
mcp.WithNumber("x",
mcp.Required(),
mcp.Description("First number"),
),
mcp.WithNumber("y",
mcp.Required(),
mcp.Description("Second number"),
),
)
// Add the calculator handler
s.AddTool(calculatorTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
op := request.Params.Arguments["operation"].(string)
x := request.Params.Arguments["x"].(float64)
y := request.Params.Arguments["y"].(float64)
var result float64
switch op {
case "add":
result = x + y
case "subtract":
result = x - y
case "multiply":
result = x * y
case "divide":
if y == 0 {
return mcp.NewToolResultError("cannot divide by zero"), nil
}
result = x / y
}
return mcp.NewToolResultText(fmt.Sprintf("%.2f", result)), nil
})
// Start the server
if err := server.ServeStdio(s); err != nil {
fmt.Printf("Server error: %v\n", err)
}
}
```
## Core Concepts
### Server
<details>
<summary>Show Server Examples</summary>
The server is your core interface to the MCP protocol. It handles connection management, protocol compliance, and message routing:
```go
// Create a basic server
s := server.NewMCPServer(
"My Server", // Server name
"1.0.0", // Version
)
// Start the server using stdio
if err := server.ServeStdio(s); err != nil {
log.Fatalf("Server error: %v", err)
}
```
</details>
### Resources
<details>
<summary>Show Resource Examples</summary>
Resources are how you expose data to LLMs. They can be anything - files, API responses, database queries, system information, etc. Resources can be:
- Static (fixed URI)
- Dynamic (using URI templates)
Here's a simple example of a static resource:
```go
// Static resource example - exposing a README file
resource := mcp.NewResource(
"docs://readme",
"Project README",
mcp.WithResourceDescription("The project's README file"),
mcp.WithMIMEType("text/markdown"),
)
// Add resource with its handler
s.AddResource(resource, func(ctx context.Context, request mcp.ReadResourceRequest) ([]mcp.ResourceContents, error) {
content, err := os.ReadFile("README.md")
if err != nil {
return nil, err
}
return []mcp.ResourceContents{
mcp.TextResourceContents{
URI: "docs://readme",
MIMEType: "text/markdown",
Text: string(content),
},
}, nil
})
```
And here's an example of a dynamic resource using a template:
```go
// Dynamic resource example - user profiles by ID
template := mcp.NewResourceTemplate(
"users://{id}/profile",
"User Profile",
mcp.WithTemplateDescription("Returns user profile information"),
mcp.WithTemplateMIMEType("application/json"),
)
// Add template with its handler
s.AddResourceTemplate(template, func(ctx context.Context, request mcp.ReadResourceRequest) ([]mcp.ResourceContents, error) {
// Extract ID from the URI using regex matching
// The server automatically matches URIs to templates
userID := extractIDFromURI(request.Params.URI)
profile, err := getUserProfile(userID) // Your DB/API call here
if err != nil {
return nil, err
}
return []mcp.ResourceContents{
mcp.TextResourceContents{
URI: request.Params.URI,
MIMEType: "application/json",
Text: profile,
},
}, nil
})
```
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.
</details>
### Tools
<details>
<summary>Show Tool Examples</summary>
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.
Simple calculation example:
```go
calculatorTool := mcp.NewTool("calculate",
mcp.WithDescription("Perform basic arithmetic calculations"),
mcp.WithString("operation",
mcp.Required(),
mcp.Description("The arithmetic operation to perform"),
mcp.Enum("add", "subtract", "multiply", "divide"),
),
mcp.WithNumber("x",
mcp.Required(),
mcp.Description("First number"),
),
mcp.WithNumber("y",
mcp.Required(),
mcp.Description("Second number"),
),
)
s.AddTool(calculatorTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
op := request.Params.Arguments["operation"].(string)
x := request.Params.Arguments["x"].(float64)
y := request.Params.Arguments["y"].(float64)
var result float64
switch op {
case "add":
result = x + y
case "subtract":
result = x - y
case "multiply":
result = x * y
case "divide":
if y == 0 {
return mcp.NewToolResultError("cannot divide by zero"), nil
}
result = x / y
}
return mcp.FormatNumberResult(result), nil
})
```
HTTP request example:
```go
httpTool := mcp.NewTool("http_request",
mcp.WithDescription("Make HTTP requests to external APIs"),
mcp.WithString("method",
mcp.Required(),
mcp.Description("HTTP method to use"),
mcp.Enum("GET", "POST", "PUT", "DELETE"),
),
mcp.WithString("url",
mcp.Required(),
mcp.Description("URL to send the request to"),
mcp.Pattern("^https?://.*"),
),
mcp.WithString("body",
mcp.Description("Request body (for POST/PUT)"),
),
)
s.AddTool(httpTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
method := request.Params.Arguments["method"].(string)
url := request.Params.Arguments["url"].(string)
body := ""
if b, ok := request.Params.Arguments["body"].(string); ok {
body = b
}
// Create and send request
var req *http.Request
var err error
if body != "" {
req, err = http.NewRequest(method, url, strings.NewReader(body))
} else {
req, err = http.NewRequest(method, url, nil)
}
if err != nil {
return mcp.NewToolResultErrorFromErr("unable to create request", err), nil
}
client := &http.Client{}
resp, err := client.Do(req)
if err != nil {
return mcp.NewToolResultErrorFromErr("unable to execute request", err), nil
}
defer resp.Body.Close()
// Return response
respBody, err := io.ReadAll(resp.Body)
if err != nil {
return mcp.NewToolResultErrorFromErr("unable to read request response", err), nil
}
return mcp.NewToolResultText(fmt.Sprintf("Status: %d\nBody: %s", resp.StatusCode, string(respBody))), nil
})
```
Tools can be used for any kind of computation or side effect:
- Database queries
- File operations
- External API calls
- Calculations
- System operations
Each tool should:
- Have a clear description
- Validate inputs
- Handle errors gracefully
- Return structured responses
- Use appropriate result types
</details>
### Prompts
<details>
<summary>Show Prompt Examples</summary>
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:
```go
// Simple greeting prompt
s.AddPrompt(mcp.NewPrompt("greeting",
mcp.WithPromptDescription("A friendly greeting prompt"),
mcp.WithArgument("name",
mcp.ArgumentDescription("Name of the person to greet"),
),
), func(ctx context.Context, request mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
name := request.Params.Arguments["name"]
if name == "" {
name = "friend"
}
return mcp.NewGetPromptResult(
"A friendly greeting",
[]mcp.PromptMessage{
mcp.NewPromptMessage(
mcp.RoleAssistant,
mcp.NewTextContent(fmt.Sprintf("Hello, %s! How can I help you today?", name)),
),
},
), nil
})
// Code review prompt with embedded resource
s.AddPrompt(mcp.NewPrompt("code_review",
mcp.WithPromptDescription("Code review assistance"),
mcp.WithArgument("pr_number",
mcp.ArgumentDescription("Pull request number to review"),
mcp.RequiredArgument(),
),
), func(ctx context.Context, request mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
prNumber := request.Params.Arguments["pr_number"]
if prNumber == "" {
return nil, fmt.Errorf("pr_number is required")
}
return mcp.NewGetPromptResult(
"Code review assistance",
[]mcp.PromptMessage{
mcp.NewPromptMessage(
mcp.RoleSystem,
mcp.NewTextContent("You are a helpful code reviewer. Review the changes and provide constructive feedback."),
),
mcp.NewPromptMessage(
mcp.RoleAssistant,
mcp.NewEmbeddedResource(mcp.ResourceContents{
URI: fmt.Sprintf("git://pulls/%s/diff", prNumber),
MIMEType: "text/x-diff",
}),
),
},
), nil
})
// Database query builder prompt
s.AddPrompt(mcp.NewPrompt("query_builder",
mcp.WithPromptDescription("SQL query builder assistance"),
mcp.WithArgument("table",
mcp.ArgumentDescription("Name of the table to query"),
mcp.RequiredArgument(),
),
), func(ctx context.Context, request mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
tableName := request.Params.Arguments["table"]
if tableName == "" {
return nil, fmt.Errorf("table name is required")
}
return mcp.NewGetPromptResult(
"SQL query builder assistance",
[]mcp.PromptMessage{
mcp.NewPromptMessage(
mcp.RoleSystem,
mcp.NewTextContent("You are a SQL expert. Help construct efficient and safe queries."),
),
mcp.NewPromptMessage(
mcp.RoleAssistant,
mcp.NewEmbeddedResource(mcp.ResourceContents{
URI: fmt.Sprintf("db://schema/%s", tableName),
MIMEType: "application/json",
}),
),
},
), nil
})
```
Prompts can include:
- System instructions
- Required arguments
- Embedded resources
- Multiple messages
- Different content types (text, images, etc.)
- Custom URI schemes
</details>
```