MCP Probe Kit

Instructions

Implementation Reference

• src/tools/gensql.ts:2-309 (handler)

  The core handler function for the 'gensql' tool. It takes arguments including description and dialect, constructs a detailed multilingual prompt with SQL examples, best practices, optimization tips, and database-specific syntax, then returns it as a text content response for LLM SQL generation.
  export async function gensql(args: any) { try { const description = args?.description || ""; const dialect = args?.dialect || "postgres"; // postgres, mysql, sqlite const message = `请根据以下需求生成 SQL： 📝 **需求描述**： ${description || "请描述需要查询/操作的数据"} 🗄️ **数据库类型**：${dialect} --- ## SQL 生成指南 ### 第一步：理解需求 **需求类型**： - 查询（SELECT） - 插入（INSERT） - 更新（UPDATE） - 删除（DELETE） - 建表（CREATE TABLE） - 修改表结构（ALTER TABLE） - 创建索引（CREATE INDEX） ### 第二步：生成 SQL **查询示例：** **1️⃣ 简单查询** \`\`\`sql -- 查询所有用户 SELECT * FROM users; -- 按条件查询 SELECT id, name, email FROM users WHERE status = 'active' AND created_at > '2024-01-01'; \`\`\` **2️⃣ 复杂查询** \`\`\`sql -- JOIN 查询 SELECT u.id, u.name, u.email, COUNT(o.id) as order_count, SUM(o.total) as total_spent FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.status = 'active' GROUP BY u.id, u.name, u.email HAVING COUNT(o.id) > 0 ORDER BY total_spent DESC LIMIT 10; \`\`\` **3️⃣ 子查询** \`\`\`sql -- 查找购买金额超过平均值的用户 SELECT u.name, SUM(o.total) as total_spent FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name HAVING SUM(o.total) > ( SELECT AVG(total_per_user) FROM ( SELECT SUM(total) as total_per_user FROM orders GROUP BY user_id ) avg_calc ); \`\`\` **4️⃣ 窗口函数** \`\`\`sql -- 每个用户的订单排名 SELECT user_id, order_id, total, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) as rank, SUM(total) OVER (PARTITION BY user_id) as user_total FROM orders; \`\`\` --- ### 建表示例 **完整的表定义：** \`\`\`sql CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(100) NOT NULL, password_hash VARCHAR(255) NOT NULL, status VARCHAR(20) DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 约束 CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'), CONSTRAINT valid_status CHECK (status IN ('active', 'inactive', 'suspended')) ); -- 索引 CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_status ON users(status); CREATE INDEX idx_users_created_at ON users(created_at); -- 注释 COMMENT ON TABLE users IS '用户表'; COMMENT ON COLUMN users.email IS '用户邮箱（唯一）'; \`\`\` --- ### 索引优化 **索引建议：** \`\`\`sql -- 单列索引 CREATE INDEX idx_users_email ON users(email); -- 复合索引（顺序很重要！） CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at); -- 唯一索引 CREATE UNIQUE INDEX idx_users_email_unique ON users(email); -- 部分索引（条件索引） CREATE INDEX idx_active_users ON users(email) WHERE status = 'active'; -- 全文索引（PostgreSQL） CREATE INDEX idx_posts_title_fulltext ON posts USING GIN (to_tsvector('english', title)); \`\`\` --- ## 查询优化技巧 ### 1️⃣ 使用 EXPLAIN ANALYZE \`\`\`sql EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123; \`\`\` ### 2️⃣ 避免 SELECT * \`\`\`sql -- ❌ Bad SELECT * FROM users; -- ✅ Good SELECT id, name, email FROM users; \`\`\` ### 3️⃣ 使用 EXISTS 替代 IN（大数据量） \`\`\`sql -- ❌ Slow SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); -- ✅ Faster SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id ); \`\`\` ### 4️⃣ 避免在 WHERE 中使用函数 \`\`\`sql -- ❌ Bad (无法使用索引) SELECT * FROM users WHERE LOWER(email) = 'test@example.com'; -- ✅ Good SELECT * FROM users WHERE email = 'test@example.com'; \`\`\` ### 5️⃣ 分页优化 \`\`\`sql -- ❌ Slow (大 OFFSET) SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 10000; -- ✅ Faster (游标分页) SELECT * FROM posts WHERE created_at < '2024-01-01 00:00:00' ORDER BY created_at DESC LIMIT 10; \`\`\` --- ## 常用查询模板 ### 去重查询 \`\`\`sql SELECT DISTINCT email FROM users; -- 或使用 GROUP BY SELECT email FROM users GROUP BY email; \`\`\` ### 统计分析 \`\`\`sql SELECT DATE_TRUNC('day', created_at) as date, COUNT(*) as count, COUNT(DISTINCT user_id) as unique_users, SUM(total) as revenue, AVG(total) as avg_order_value FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '30 days' GROUP BY DATE_TRUNC('day', created_at) ORDER BY date DESC; \`\`\` ### 排名查询 \`\`\`sql -- Top N per group SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) as rank FROM products ) ranked WHERE rank <= 10; \`\`\` ### 递归查询（树形结构） \`\`\`sql WITH RECURSIVE category_tree AS ( -- 根节点 SELECT id, name, parent_id, 1 as level FROM categories WHERE parent_id IS NULL UNION ALL -- 递归部分 SELECT c.id, c.name, c.parent_id, ct.level + 1 FROM categories c JOIN category_tree ct ON c.parent_id = ct.id ) SELECT * FROM category_tree ORDER BY level, name; \`\`\` --- ## 数据库特定语法 ### PostgreSQL \`\`\`sql -- JSON 查询 SELECT data->>'name' as name FROM users WHERE data @> '{"active": true}'; -- 数组操作 SELECT * FROM posts WHERE tags && ARRAY['sql', 'database']; -- 全文搜索 SELECT * FROM articles WHERE to_tsvector(content) @@ to_tsquery('postgresql'); \`\`\` ### MySQL \`\`\`sql -- JSON 查询 SELECT JSON_EXTRACT(data, '$.name') as name FROM users; -- 全文搜索 SELECT * FROM articles WHERE MATCH(title, content) AGAINST('database' IN NATURAL LANGUAGE MODE); \`\`\` --- 现在请根据需求生成优化的 SQL 语句，并提供： 1. 完整的 SQL 代码 2. 执行计划分析（如需要） 3. 索引建议 4. 性能优化建议`; return { content: [ { type: "text", text: message, }, ], }; } catch (error) { const errorMessage = error instanceof Error ? error.message : String(error); return { content: [ { type: "text", text: `❌ 生成 SQL 失败: ${errorMessage}`, }, ], isError: true, }; } }
• src/index.ts:301-318 (schema)

  The input schema definition for the gensql tool registered in the MCP server's ListTools handler, defining parameters for description and dialect.
  { name: "gensql", description: "【SQL 生成器】根据描述生成 SQL 查询语句", inputSchema: { type: "object", properties: { description: { type: "string", description: "需求描述", }, dialect: { type: "string", description: "数据库类型：postgres, mysql, sqlite（默认 postgres）", }, }, required: [], }, },
• src/index.ts:504-505 (registration)

  Registration in the CallToolRequestSchema switch statement that dispatches calls to the gensql handler.
  case "gensql": return await gensql(args);
• src/tools/index.ts:16-16 (registration)

  Re-export of the gensql function from its implementation module.
  export { gensql } from "./gensql.js";
• src/index.ts:12-15 (registration)

  Import of gensql function from tools/index.ts into the main index.ts for use in MCP server.
  detectShell, initSetting, initProject, gencommit, debug, genapi, codeReview, gentest, genpr, checkDeps, gendoc, genchangelog, refactor, perf, fix, gensql, resolveConflict, genui, explain, convert, genreadme, split, analyzeProject } from "./tools/index.js";