Run analytical SQL on CSV/Parquet/JSON files directly via mcp-server-duckdb (uvx)

{
  "request_1_init": {
    "jsonrpc": "2.0",
    "id": 1,
    "method": "initialize",
    "params": {
      "protocolVersion": "2024-11-05",
      "capabilities": {},
      "clientInfo": {
        "name": "pathfinder",
        "version": "1.0"
      }
    }
  },
  "response_1_init": {
    "jsonrpc": "2.0",
    "id": 1,
    "result": {
      "protocolVersion": "2024-11-05",
      "capabilities": {
        "experimental": {},
        "prompts": {
          "listChanged": false
        },
        "resources": {
          "subscribe": false,
          "listChanged": false
        },
        "tools": {
          "listChanged": false
        }
      },
      "serverInfo": {
        "name": "mcp-duckdb-server",
        "version": "1.27.2"
      }
    }
  },
  "request_2_tools_list": {
    "jsonrpc": "2.0",
    "id": 2,
    "method": "tools/list",
    "params": {}
  },
  "response_2_tools_list": {
    "jsonrpc": "2.0",
    "id": 2,
    "result": {
      "tools": [
        {
          "name": "query",
          "description": "Execute a query on the DuckDB database",
          "inputSchema": {
            "type": "object",
            "properties": {
              "query": {
                "type": "string",
                "description": "SQL query to execute"
              }
            },
            "required": ["query"]
          }
        }
      ]
    }
  },
  "request_3_aggregate": {
    "jsonrpc": "2.0",
    "id": 3,
    "method": "tools/call",
    "params": {
      "name": "query",
      "arguments": {
        "query": "SELECT product, SUM(revenue) as total_revenue, SUM(units) as total_units FROM read_csv_auto('/tmp/sales.csv') GROUP BY product ORDER BY total_revenue DESC"
      }
    }
  },
  "response_3_aggregate": {
    "jsonrpc": "2.0",
    "id": 3,
    "result": {
      "content": [
        {
          "type": "text",
          "text": "[('Gadget', 246000, 320), ('Widget', 176000, 570)]"
        }
      ],
      "isError": false
    }
  },
  "request_4_grouped": {
    "jsonrpc": "2.0",
    "id": 4,
    "method": "tools/call",
    "params": {
      "name": "query",
      "arguments": {
        "query": "SELECT region, quarter, AVG(revenue)::INT as avg_revenue, COUNT(*) as num_products FROM read_csv_auto('/tmp/sales.csv') GROUP BY region, quarter ORDER BY region, quarter"
      }
    }
  },
  "response_4_grouped": {
    "jsonrpc": "2.0",
    "id": 4,
    "result": {
      "content": [
        {
          "type": "text",
          "text": "[('North', 'Q1', 53500, 2), ('North', 'Q2', 61500, 2), ('South', 'Q1', 46500, 2), ('South', 'Q2', 49500, 2)]"
        }
      ],
      "isError": false
    }
  },
  "request_5_window": {
    "jsonrpc": "2.0",
    "id": 5,
    "method": "tools/call",
    "params": {
      "name": "query",
      "arguments": {
        "query": "SELECT product, quarter, revenue, SUM(revenue) OVER (PARTITION BY product ORDER BY quarter) as running_total FROM read_csv_auto('/tmp/sales.csv') WHERE region = 'North' ORDER BY product, quarter"
      }
    }
  },
  "response_5_window": {
    "jsonrpc": "2.0",
    "id": 5,
    "result": {
      "content": [
        {
          "type": "text",
          "text": "[('Gadget', 'Q1', 62000, 62000), ('Gadget', 'Q2', 71000, 133000), ('Widget', 'Q1', 45000, 45000), ('Widget', 'Q2', 52000, 97000)]"
        }
      ],
      "isError": false
    }
  }
}