gcp-spanner-execute-query
Execute SQL queries on Google Cloud Spanner databases by specifying the instance, database, and query parameters. Integrates with the Google Cloud MCP Server for streamlined database interactions.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| databaseId | No | Spanner database ID (defaults to SPANNER_DATABASE env var) | |
| instanceId | No | Spanner instance ID (defaults to SPANNER_INSTANCE env var) | |
| params | No | Query parameters | |
| sql | Yes | The SQL query to execute |
Implementation Reference
- src/services/spanner/tools.ts:73-148 (handler)The handler function that executes SQL queries on GCP Spanner using the @google-cloud/spanner client, supports parameterized queries, and returns results formatted as a markdown table (truncated to 100 rows). Handles project, instance, and database configuration.async ({ sql, instanceId, databaseId, params }, _extra) => { try { const projectId = await getProjectId(); const config = await getSpannerConfig( Array.isArray(instanceId) ? instanceId[0] : instanceId, Array.isArray(databaseId) ? databaseId[0] : databaseId, ); const spanner = await getSpannerClient(); logger.debug( `Using Spanner client with project ID: ${spanner.projectId} for execute-spanner-query`, ); const instance = spanner.instance(config.instanceId); const database = instance.database(config.databaseId); // Execute the query const [result] = await database.run({ sql, params: params || {}, }); if (!result || result.length === 0) { return { content: [ { type: "text", text: `# Query Results\n\nProject: ${projectId}\nInstance: ${config.instanceId}\nDatabase: ${config.databaseId}\n\nQuery executed successfully. No results returned.`, }, ], }; } // Convert to markdown table const columns = Object.keys(result[0]); let markdown = `# Query Results\n\nProject: ${projectId}\nInstance: ${config.instanceId}\nDatabase: ${config.databaseId}\n\n`; markdown += `SQL: \`${sql}\`\n\n`; markdown += `Rows: ${result.length}\n\n`; // Table header markdown += "| " + columns.join(" | ") + " |\n"; markdown += "| " + columns.map(() => "---").join(" | ") + " |\n"; // Table rows (limit to 100 rows for display) const displayRows = result.slice(0, 100); for (const row of displayRows) { const rowValues = columns.map((col) => { const value = (row as any)[col]; if (value === null || value === undefined) return "NULL"; if (typeof value === "object") return JSON.stringify(value); return String(value); }); markdown += "| " + rowValues.join(" | ") + " |\n"; } if (result.length > 100) { markdown += "\n*Results truncated. Showing 100 of " + result.length + " rows.*"; } return { content: [ { type: "text", text: markdown, }, ], }; } catch (error: any) { logger.error( `Error executing Spanner query: ${error instanceof Error ? error.message : String(error)}`, ); throw error; } },
- src/services/spanner/tools.ts:58-72 (schema)Zod schema for tool inputs: sql (string, required), instanceId (string, optional), databaseId (string, optional), params (record<string, any>, optional).{ sql: z.string().describe("The SQL query to execute"), instanceId: z .string() .optional() .describe("Spanner instance ID (defaults to SPANNER_INSTANCE env var)"), databaseId: z .string() .optional() .describe("Spanner database ID (defaults to SPANNER_DATABASE env var)"), params: z .record(z.string(), z.any()) .optional() .describe("Query parameters"), },
- src/services/spanner/tools.ts:57-149 (registration)Registers the "gcp-spanner-execute-query" tool on the McpServer instance within the registerSpannerTools function."gcp-spanner-execute-query", { sql: z.string().describe("The SQL query to execute"), instanceId: z .string() .optional() .describe("Spanner instance ID (defaults to SPANNER_INSTANCE env var)"), databaseId: z .string() .optional() .describe("Spanner database ID (defaults to SPANNER_DATABASE env var)"), params: z .record(z.string(), z.any()) .optional() .describe("Query parameters"), }, async ({ sql, instanceId, databaseId, params }, _extra) => { try { const projectId = await getProjectId(); const config = await getSpannerConfig( Array.isArray(instanceId) ? instanceId[0] : instanceId, Array.isArray(databaseId) ? databaseId[0] : databaseId, ); const spanner = await getSpannerClient(); logger.debug( `Using Spanner client with project ID: ${spanner.projectId} for execute-spanner-query`, ); const instance = spanner.instance(config.instanceId); const database = instance.database(config.databaseId); // Execute the query const [result] = await database.run({ sql, params: params || {}, }); if (!result || result.length === 0) { return { content: [ { type: "text", text: `# Query Results\n\nProject: ${projectId}\nInstance: ${config.instanceId}\nDatabase: ${config.databaseId}\n\nQuery executed successfully. No results returned.`, }, ], }; } // Convert to markdown table const columns = Object.keys(result[0]); let markdown = `# Query Results\n\nProject: ${projectId}\nInstance: ${config.instanceId}\nDatabase: ${config.databaseId}\n\n`; markdown += `SQL: \`${sql}\`\n\n`; markdown += `Rows: ${result.length}\n\n`; // Table header markdown += "| " + columns.join(" | ") + " |\n"; markdown += "| " + columns.map(() => "---").join(" | ") + " |\n"; // Table rows (limit to 100 rows for display) const displayRows = result.slice(0, 100); for (const row of displayRows) { const rowValues = columns.map((col) => { const value = (row as any)[col]; if (value === null || value === undefined) return "NULL"; if (typeof value === "object") return JSON.stringify(value); return String(value); }); markdown += "| " + rowValues.join(" | ") + " |\n"; } if (result.length > 100) { markdown += "\n*Results truncated. Showing 100 of " + result.length + " rows.*"; } return { content: [ { type: "text", text: markdown, }, ], }; } catch (error: any) { logger.error( `Error executing Spanner query: ${error instanceof Error ? error.message : String(error)}`, ); throw error; } }, );