Database Query MCP Server

Instructions

Implementation Reference

• db-query-tool.js:250-289 (handler)

  The core handler function for the 'query_postgresql' tool. Validates SQL is read-only, connects to PostgreSQL database, executes the query, processes results, and handles errors.

  async executePostgreSQL(config) {
    const { querySql } = config;
    
    // 检查是否为只读查询
    if (!this.isReadOnlyQuery(querySql)) {
      return {
        success: false,
        error: "不允许执行非只读操作。仅支持SELECT、SHOW、DESCRIBE等查询语句。",
        code: "READONLY_VIOLATION"
      };
    }
    
    let connection;
    
    try {
      // 建立数据库连接
      connection = await this.getPostgreSQLConnection(config);
      
      // 执行查询
      const result = await this.executePostgreSQLQuery(connection, querySql);
      
      // 返回结果
      return {
        success: true,
        data: result.data,
        columns: result.columns,
        rowCount: result.rowCount
      };
    } catch (error) {
      // 错误处理
      return {
        success: false,
        error: error.message,
        code: error.code || 'DATABASE_ERROR'
      };
    } finally {
      // 关闭数据库连接
      await this.closePostgreSQLConnection(connection);
    }
  }

• mcp.full.config.js:75-115 (schema)

  Input schema definition and annotations for the 'query_postgresql' tool, including parameters like host, port, user, pwd, db, querySql.

  query_postgresql: {
    name: "query_postgresql",
    description: "执行PostgreSQL数据库查询（只读模式）",
    inputSchema: {
      type: "object",
      properties: {
        host: { 
          type: "string", 
          description: "数据库主机地址" 
        },
        port: { 
          type: "integer", 
          description: "数据库端口" 
        },
        user: { 
          type: "string", 
          description: "数据库用户名" 
        },
        pwd: { 
          type: "string", 
          description: "数据库密码" 
        },
        db: { 
          type: "string", 
          description: "数据库名称" 
        },
        querySql: { 
          type: "string", 
          description: "要执行的SQL查询语句（仅支持SELECT等只读操作）" 
        }
      },
      required: ["host", "port", "user", "pwd", "db", "querySql"]
    },
    annotations: {
      title: "PostgreSQL数据库查询工具（只读）",
      readOnlyHint: true,
      destructiveHint: false,
      idempotentHint: false,
      openWorldHint: false
    }
  },

• mcp-server.js:31-74 (registration)

  Registration of the tool call handler. Dispatches 'query_postgresql' calls to dbTool.executePostgreSQL based on tool name.

  server.setRequestHandler(CallToolRequestSchema, async (request) => {
    try {
      let result;
      
      switch (request.params.name) {
        case config.tools.query_mysql.name:
          result = await dbTool.executeMySQL(request.params.arguments);
          break;
          
        case config.tools.query_postgresql.name:
          result = await dbTool.executePostgreSQL(request.params.arguments);
          break;
          
        case config.tools.query_mssql.name:
          result = await dbTool.executeMSSQL(request.params.arguments);
          break;
          
        case config.tools.query_oracle.name:
          result = await dbTool.executeOracle(request.params.arguments);
          break;
          
        default:
          throw new Error(`Unknown tool: ${request.params.name}`);
      }
      
      return {
        content: [{
          type: "text",
          text: JSON.stringify(result, null, 2),
        }],
      };
    } catch (error) {
      return {
        content: [{
          type: "text",
          text: JSON.stringify({
            success: false,
            error: error.message
          }, null, 2),
        }],
        isError: true
      };
    }
  });

• mcp-server.js:77-86 (registration)

  Registration of the tool list handler. Includes 'query_postgresql' in the list of available tools.

  server.setRequestHandler(ListToolsRequestSchema, async () => {
    return {
      tools: [
        config.tools.query_mysql,
        config.tools.query_postgresql,
        config.tools.query_mssql,
        config.tools.query_oracle
      ]
    };
  });

• db-query-tool.js:23-71 (helper)

  Helper function used by the handler to validate that the SQL query is read-only, blocking dangerous operations.

  isReadOnlyQuery(sql) {
    // 转换为小写以便比较
    const lowerSql = sql.trim().toLowerCase();
    
    // 允许的只读操作关键词
    const allowedPatterns = [
      /^select/,
      /^show/,
      /^describe/,
      /^desc/,
      /^explain/,
      /^use/
    ];
    
    // 禁止的写操作关键词
    const forbiddenPatterns = [
      /insert/,
      /update/,
      /delete/,
      /drop/,
      /truncate/,
      /alter/,
      /create/,
      /replace/,
      /grant/,
      /revoke/,
      /commit/,
      /rollback/,
      /savepoint/,
      /set/
    ];
    
    // 检查是否包含禁止的关键词
    for (const pattern of forbiddenPatterns) {
      if (pattern.test(lowerSql)) {
        return false;
      }
    }
    
    // 检查是否以允许的关键词开头
    for (const pattern of allowedPatterns) {
      if (pattern.test(lowerSql)) {
        return true;
      }
    }
    
    // 如果既不明确允许也不明确禁止，默认为不安全
    return false;
  }