Skip to content

Instantly share code, notes, and snippets.

@phartenfeller
Last active July 17, 2025 10:23
Show Gist options
  • Save phartenfeller/5430bac84f2e9c708dd14a34bd966c90 to your computer and use it in GitHub Desktop.
Save phartenfeller/5430bac84f2e9c708dd14a34bd966c90 to your computer and use it in GitHub Desktop.
#!/usr/bin/env bun
import { spawn } from "bun";
class SQLclMCPExplorer {
private sqlPath: string;
private process?: any;
private initialized = false;
constructor(sqlPath: string) {
this.sqlPath = sqlPath;
}
async startServer(): Promise<boolean> {
console.log("๐Ÿš€ Starting SQLcl MCP Server...");
this.process = spawn([this.sqlPath, "-mcp"], {
stdin: "pipe",
stdout: "pipe",
stderr: "pipe",
});
await new Promise((resolve) => setTimeout(resolve, 2000));
console.log("โœ… Server appears to be running");
return true;
}
async sendRequest(request: object, expectResponse = true): Promise<any> {
if (!this.process) {
throw new Error("Server not started");
}
console.log(`๐Ÿ“ค Sending: ${JSON.stringify(request, null, 2)}`);
await this.process.stdin.write(JSON.stringify(request) + "\n");
if (!expectResponse) {
console.log("๐Ÿ“ฅ No response expected\n");
return null;
}
// Wait longer and try to read response
return new Promise((resolve) => {
let responseBuffer = "";
let timeoutId: Timer;
const cleanup = () => {
if (timeoutId) clearTimeout(timeoutId);
};
const processOutput = async () => {
try {
const reader = this.process.stdout.getReader();
while (true) {
const { value, done } = await reader.read();
if (done) break;
const chunk = new TextDecoder().decode(value);
responseBuffer += chunk;
// Look for complete JSON responses
const lines = responseBuffer.split("\n");
for (const line of lines) {
const trimmed = line.trim();
if (trimmed && trimmed.startsWith("{")) {
try {
const response = JSON.parse(trimmed);
console.log(
`๐Ÿ“ฅ Response: ${JSON.stringify(response, null, 2)}\n`
);
cleanup();
reader.releaseLock();
resolve(response);
return;
} catch (e) {
// Not complete JSON yet
}
}
}
}
reader.releaseLock();
} catch (error) {
console.log(`๐Ÿ“ฅ Error reading response: ${error}\n`);
cleanup();
resolve(null);
}
};
// Start reading
processOutput();
// Timeout after 5 seconds
timeoutId = setTimeout(() => {
console.log("๐Ÿ“ฅ Timeout - no response received\n");
resolve(null);
}, 5000);
});
}
async initialize(): Promise<boolean> {
const request = {
jsonrpc: "2.0",
id: 1,
method: "initialize",
params: {
protocolVersion: "2024-11-05",
capabilities: {
roots: { listChanged: true },
sampling: {},
},
clientInfo: {
name: "SQLcl-Explorer",
version: "1.0.0",
},
},
};
const response = await this.sendRequest(request);
if (response && response.result) {
this.initialized = true;
console.log("โœ… Server initialized successfully!");
// Send initialized notification (required by MCP protocol)
const notification = {
jsonrpc: "2.0",
method: "notifications/initialized",
params: {},
};
await this.sendRequest(notification, false);
return true;
}
return false;
}
async exploreCapabilities() {
console.log("\n๐Ÿ” Exploring SQLcl MCP Server Capabilities");
console.log("=".repeat(60));
// First initialize
console.log("๐Ÿ”ง Initializing:");
console.log("-".repeat(40));
const initSuccess = await this.initialize();
if (!initSuccess) {
console.log("โŒ Failed to initialize server");
return;
}
// Now try other methods
const requests = [
{
name: "List Tools",
request: {
jsonrpc: "2.0",
id: 2,
method: "tools/list",
params: {},
},
},
{
name: "List Resources",
request: {
jsonrpc: "2.0",
id: 3,
method: "resources/list",
params: {},
},
},
{
name: "List Prompts",
request: {
jsonrpc: "2.0",
id: 4,
method: "prompts/list",
params: {},
},
},
{
name: "Server Info",
request: {
jsonrpc: "2.0",
id: 5,
method: "server/info",
params: {},
},
},
{
name: "Run tool: list-connections",
request: {
jsonrpc: "2.0",
id: 1,
method: "tools/call",
params: {
name: "list-connections",
arguments: {
mcp_client: "SQLcl-Explorer",
model: "HartenfellerHuman",
},
},
},
},
{
name: "Run tool: connect",
request: {
jsonrpc: "2.0",
id: 1,
method: "tools/call",
params: {
name: "connect",
arguments: {
mcp_client: "SQLcl-Explorer",
model: "HartenfellerHuman",
connection_name: "local-23ai-movies",
},
},
},
},
{
name: "List Tools (after connect)",
request: {
jsonrpc: "2.0",
id: 2,
method: "tools/list",
params: {},
},
},
{
name: "List Resources (after connect)",
request: {
jsonrpc: "2.0",
id: 3,
method: "resources/list",
params: {},
},
},
{
name: "List Prompts (after connect)",
request: {
jsonrpc: "2.0",
id: 4,
method: "prompts/list",
params: {},
},
},
{
name: "Run tool: run-sql",
request: {
jsonrpc: "2.0",
id: 1,
method: "tools/call",
params: {
name: "run-sql",
arguments: {
mcp_client: "SQLcl-Explorer",
model: "HartenfellerHuman",
sql: "select title, year, IMDB_RATING from movies where lower(title) like '%godfather%';",
},
},
},
},
{
name: "Run tool: disconnect",
request: {
jsonrpc: "2.0",
id: 1,
method: "tools/call",
params: {
name: "disconnect",
arguments: {
mcp_client: "SQLcl-Explorer",
model: "HartenfellerHuman",
connection_name: "local-23ai-movies",
},
},
},
},
];
for (const { name, request } of requests) {
console.log(`๐Ÿ”ง ${name}:`);
console.log("-".repeat(40));
try {
await this.sendRequest(request);
await new Promise((resolve) => setTimeout(resolve, 500)); // Small delay between requests
} catch (error) {
console.log(`โŒ Error: ${error}\n`);
}
}
}
async interactiveMode() {
console.log("\n๐ŸŽฎ Interactive Mode");
console.log("Available methods based on server capabilities:");
console.log("- tools/list, tools/call");
console.log("- resources/list, resources/read");
console.log("- prompts/list, prompts/get");
console.log("- Or enter full JSON-RPC request");
console.log("- 'exit' to quit\n");
let id = 100;
while (true) {
const input = prompt("SQLcl MCP> ");
if (!input || input.toLowerCase() === "exit") break;
let request;
if (input.startsWith("{")) {
try {
request = JSON.parse(input);
} catch (e) {
console.log("โŒ Invalid JSON");
continue;
}
} else {
request = {
jsonrpc: "2.0",
id: ++id,
method: input,
params: {},
};
}
try {
await this.sendRequest(request);
} catch (error) {
console.log(`โŒ Error: ${error}\n`);
}
}
}
cleanup() {
if (this.process) {
console.log("\n๐Ÿ›‘ Stopping SQLcl MCP Server...");
this.process.kill("SIGTERM");
}
}
}
process.on("SIGINT", () => {
console.log("\n๐Ÿ‘‹ Shutting down...");
process.exit(0);
});
async function main() {
const sqlPath = process.argv[2];
if (!sqlPath) {
console.log("Usage: bun script.ts <path-to-sql-binary>");
process.exit(1);
}
const explorer = new SQLclMCPExplorer(sqlPath);
try {
const started = await explorer.startServer();
if (!started) {
console.log("โŒ Failed to start server");
process.exit(1);
}
await explorer.exploreCapabilities();
const shouldTest = prompt("Enter interactive mode? (y/N): ");
if (shouldTest?.toLowerCase() === "y") {
await explorer.interactiveMode();
}
} catch (error) {
console.error("Error:", error);
} finally {
explorer.cleanup();
}
console.log("\nโœ… Done!");
}
main().catch(console.error);
// run with bun sqlcl_mcp_demo.ts sql
// check "const requests" for list of commands
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment