GCP Billing and Monitoring MCP Server

Implementation Reference

• src/services/spanner/tools.ts:451-692 (handler)

  Core handler logic for the 'gcp-spanner-query-natural-language' tool. Parses natural language input, generates corresponding SQL queries using pattern matching on keywords like 'list tables', 'schema', 'count', etc., retrieves database schema, executes the SQL on Spanner, and returns results in a formatted markdown table. For complex queries, provides schema and directs to use 'gcp-spanner-execute-query'.
  async ({ query, instanceId, databaseId }, _extra) => { try { // First try to get the project ID from the state manager let projectId = stateManager.getCurrentProjectId(); if (!projectId) { // If not in state manager, try to get it from our function const authProjectId = await getProjectId(); if (authProjectId) { projectId = authProjectId; logger.debug(`Got project ID from getProjectId: ${projectId}`); } } else { logger.debug(`Got project ID from state manager: ${projectId}`); } if (!projectId) { throw new Error( "Project ID could not be determined. Please set a project ID using the set-project-id tool.", ); } const config = await getSpannerConfig( Array.isArray(instanceId) ? instanceId[0] : instanceId, Array.isArray(databaseId) ? databaseId[0] : databaseId, ); // Get the schema for the database const schemaInfo = await getDetailedSchemaForQueryGeneration( config.instanceId, config.databaseId, ); // Create Spanner client with explicit project ID const spanner = new (await import("@google-cloud/spanner")).Spanner({ projectId: projectId, }); logger.debug( `Using Spanner client with project ID: ${projectId} for execute-spanner-query`, ); const instance = spanner.instance(config.instanceId); const database = instance.database(config.databaseId); const [tablesResult] = await database.run({ sql: `SELECT table_name FROM information_schema.tables WHERE table_catalog = '' AND table_schema = '' ORDER BY table_name`, }); const tableNames = tablesResult.map( (row: any) => row.table_name as string, ); if (!tableNames || tableNames.length === 0) { return { content: [ { type: "text", text: `# Query Error\n\nNo tables found in database ${config.databaseId}.`, }, ], }; } // Generate a SQL query based on the natural language query and schema let generatedSql = ""; // For simple queries about table structure, generate SQL directly if ( query.toLowerCase().includes("list tables") || query.toLowerCase().includes("show tables") || query.toLowerCase().includes("what tables") ) { generatedSql = `SELECT table_name FROM information_schema.tables WHERE table_catalog = '' AND table_schema = '' ORDER BY table_name`; } else if ( query.toLowerCase().includes("schema") || query.toLowerCase().includes("structure") || query.toLowerCase().includes("columns") ) { // Extract table name if specified let tableName = ""; for (const name of tableNames) { if (query.toLowerCase().includes(name.toLowerCase())) { tableName = name; break; } } if (tableName) { generatedSql = `SELECT column_name, spanner_type, is_nullable FROM information_schema.columns WHERE table_catalog = '' AND table_schema = '' AND table_name = '${tableName}' ORDER BY ordinal_position`; } else { generatedSql = `SELECT table_name, column_name, spanner_type FROM information_schema.columns WHERE table_catalog = '' AND table_schema = '' ORDER BY table_name, ordinal_position`; } } // For simple 'show all data' queries else if ( query.toLowerCase().includes("all data") || query.toLowerCase().includes("all rows") ) { // Extract table name if specified let tableName = ""; for (const name of tableNames) { if (query.toLowerCase().includes(name.toLowerCase())) { tableName = name; break; } } if (tableName) { generatedSql = `SELECT * FROM ${tableName} LIMIT 100`; } else { // If no specific table mentioned, return an error return { content: [ { type: "text", text: `# Query Error\n\nPlease specify which table you want to see data from. Available tables: ${tableNames.join(", ")}`, }, ], }; } } // For count queries else if ( query.toLowerCase().includes("count") || query.toLowerCase().includes("how many") ) { // Extract table name if specified let tableName = ""; for (const name of tableNames) { if (query.toLowerCase().includes(name.toLowerCase())) { tableName = name; break; } } if (tableName) { generatedSql = `SELECT COUNT(*) as count FROM ${tableName}`; } else { // If no specific table mentioned, count rows in all tables const countQueries = tableNames.map( (name) => `SELECT '${name}' as table_name, COUNT(*) as row_count FROM ${name}`, ); generatedSql = countQueries.join(" UNION ALL "); } } // For more complex queries, provide schema information and ask the user to use execute-spanner-query else { return { content: [ { type: "text", text: `# Complex Query Detected\n\nYour query requires a custom SQL statement. Here's the database schema to help you formulate your query:\n\n\`\`\`\n${schemaInfo}\n\`\`\`\n\nPlease use the \`execute-spanner-query\` tool with a specific SQL statement to query this data.\n\nExample:\n\`\`\`sql\nSELECT * FROM [table_name] WHERE [condition] LIMIT 100\n\`\`\``, }, ], }; } // Execute the generated SQL query // Execute the generated SQL query const [result] = await database.run({ sql: generatedSql, }); if (!result || result.length === 0) { return { content: [ { type: "text", text: `# Query Results\n\nProject: ${projectId}\nInstance: ${config.instanceId}\nDatabase: ${config.databaseId}\n\nNatural Language Query: ${query}\n\nGenerated SQL: \`${generatedSql}\`\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 += `Natural Language Query: ${query}\n\n`; markdown += `Generated SQL: \`${generatedSql}\`\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.*"; } // Add a note about using execute-spanner-query for more complex queries markdown += "\n\n## Need a more complex query?\n\n"; markdown += "For more complex queries, use the `execute-spanner-query` tool with a specific SQL statement."; return { content: [ { type: "text", text: markdown, }, ], }; } catch (error: any) { logger.error( `Error executing natural language Spanner query: ${error instanceof Error ? error.message : String(error)}`, ); return { content: [ { type: "text", text: `# Query Error\n\nFailed to execute query: ${error.message}\n\nIf this is a complex query, please use the \`execute-spanner-query\` tool with a specific SQL statement.`, }, ], }; } },
• src/services/spanner/tools.ts:436-450 (schema)

  Zod schema defining the input parameters for the tool: natural language 'query' (required), optional 'instanceId' and 'databaseId'.
  { query: z .string() .describe( "Natural language description of the query you want 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)"), },
• src/services/spanner/tools.ts:433-694 (registration)

  Registration of the tool using server.tool() within the registerSpannerTools function. This is called from src/index.ts.
  // Tool to execute natural language queries against Spanner server.tool( "gcp-spanner-query-natural-language", { query: z .string() .describe( "Natural language description of the query you want 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)"), }, async ({ query, instanceId, databaseId }, _extra) => { try { // First try to get the project ID from the state manager let projectId = stateManager.getCurrentProjectId(); if (!projectId) { // If not in state manager, try to get it from our function const authProjectId = await getProjectId(); if (authProjectId) { projectId = authProjectId; logger.debug(`Got project ID from getProjectId: ${projectId}`); } } else { logger.debug(`Got project ID from state manager: ${projectId}`); } if (!projectId) { throw new Error( "Project ID could not be determined. Please set a project ID using the set-project-id tool.", ); } const config = await getSpannerConfig( Array.isArray(instanceId) ? instanceId[0] : instanceId, Array.isArray(databaseId) ? databaseId[0] : databaseId, ); // Get the schema for the database const schemaInfo = await getDetailedSchemaForQueryGeneration( config.instanceId, config.databaseId, ); // Create Spanner client with explicit project ID const spanner = new (await import("@google-cloud/spanner")).Spanner({ projectId: projectId, }); logger.debug( `Using Spanner client with project ID: ${projectId} for execute-spanner-query`, ); const instance = spanner.instance(config.instanceId); const database = instance.database(config.databaseId); const [tablesResult] = await database.run({ sql: `SELECT table_name FROM information_schema.tables WHERE table_catalog = '' AND table_schema = '' ORDER BY table_name`, }); const tableNames = tablesResult.map( (row: any) => row.table_name as string, ); if (!tableNames || tableNames.length === 0) { return { content: [ { type: "text", text: `# Query Error\n\nNo tables found in database ${config.databaseId}.`, }, ], }; } // Generate a SQL query based on the natural language query and schema let generatedSql = ""; // For simple queries about table structure, generate SQL directly if ( query.toLowerCase().includes("list tables") || query.toLowerCase().includes("show tables") || query.toLowerCase().includes("what tables") ) { generatedSql = `SELECT table_name FROM information_schema.tables WHERE table_catalog = '' AND table_schema = '' ORDER BY table_name`; } else if ( query.toLowerCase().includes("schema") || query.toLowerCase().includes("structure") || query.toLowerCase().includes("columns") ) { // Extract table name if specified let tableName = ""; for (const name of tableNames) { if (query.toLowerCase().includes(name.toLowerCase())) { tableName = name; break; } } if (tableName) { generatedSql = `SELECT column_name, spanner_type, is_nullable FROM information_schema.columns WHERE table_catalog = '' AND table_schema = '' AND table_name = '${tableName}' ORDER BY ordinal_position`; } else { generatedSql = `SELECT table_name, column_name, spanner_type FROM information_schema.columns WHERE table_catalog = '' AND table_schema = '' ORDER BY table_name, ordinal_position`; } } // For simple 'show all data' queries else if ( query.toLowerCase().includes("all data") || query.toLowerCase().includes("all rows") ) { // Extract table name if specified let tableName = ""; for (const name of tableNames) { if (query.toLowerCase().includes(name.toLowerCase())) { tableName = name; break; } } if (tableName) { generatedSql = `SELECT * FROM ${tableName} LIMIT 100`; } else { // If no specific table mentioned, return an error return { content: [ { type: "text", text: `# Query Error\n\nPlease specify which table you want to see data from. Available tables: ${tableNames.join(", ")}`, }, ], }; } } // For count queries else if ( query.toLowerCase().includes("count") || query.toLowerCase().includes("how many") ) { // Extract table name if specified let tableName = ""; for (const name of tableNames) { if (query.toLowerCase().includes(name.toLowerCase())) { tableName = name; break; } } if (tableName) { generatedSql = `SELECT COUNT(*) as count FROM ${tableName}`; } else { // If no specific table mentioned, count rows in all tables const countQueries = tableNames.map( (name) => `SELECT '${name}' as table_name, COUNT(*) as row_count FROM ${name}`, ); generatedSql = countQueries.join(" UNION ALL "); } } // For more complex queries, provide schema information and ask the user to use execute-spanner-query else { return { content: [ { type: "text", text: `# Complex Query Detected\n\nYour query requires a custom SQL statement. Here's the database schema to help you formulate your query:\n\n\`\`\`\n${schemaInfo}\n\`\`\`\n\nPlease use the \`execute-spanner-query\` tool with a specific SQL statement to query this data.\n\nExample:\n\`\`\`sql\nSELECT * FROM [table_name] WHERE [condition] LIMIT 100\n\`\`\``, }, ], }; } // Execute the generated SQL query // Execute the generated SQL query const [result] = await database.run({ sql: generatedSql, }); if (!result || result.length === 0) { return { content: [ { type: "text", text: `# Query Results\n\nProject: ${projectId}\nInstance: ${config.instanceId}\nDatabase: ${config.databaseId}\n\nNatural Language Query: ${query}\n\nGenerated SQL: \`${generatedSql}\`\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 += `Natural Language Query: ${query}\n\n`; markdown += `Generated SQL: \`${generatedSql}\`\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.*"; } // Add a note about using execute-spanner-query for more complex queries markdown += "\n\n## Need a more complex query?\n\n"; markdown += "For more complex queries, use the `execute-spanner-query` tool with a specific SQL statement."; return { content: [ { type: "text", text: markdown, }, ], }; } catch (error: any) { logger.error( `Error executing natural language Spanner query: ${error instanceof Error ? error.message : String(error)}`, ); return { content: [ { type: "text", text: `# Query Error\n\nFailed to execute query: ${error.message}\n\nIf this is a complex query, please use the \`execute-spanner-query\` tool with a specific SQL statement.`, }, ], }; } }, ); }
• src/services/spanner/tools.ts:19-52 (helper)

  Helper function that generates a detailed, human-readable schema string used in the handler for complex query guidance.
  async function getDetailedSchemaForQueryGeneration( instanceId: string, databaseId: string, ): Promise<string> { const schema = await getSpannerSchema(instanceId, databaseId); // Format the schema in a way that's useful for SQL generation let schemaText = `Database: ${databaseId}\n\nTables:\n`; for (const table of schema.tables) { schemaText += `\nTable: ${table.name}\n`; schemaText += `Columns:\n`; for (const column of table.columns) { schemaText += ` - ${column.name}: ${column.type}${column.nullable ? " (nullable)" : ""}\n`; } if (table.indexes && table.indexes.length > 0) { schemaText += `Indexes:\n`; for (const index of table.indexes) { schemaText += ` - ${index.name}: ${index.columns.join(", ")}${index.unique ? " (unique)" : ""}\n`; } } if (table.foreignKeys && table.foreignKeys.length > 0) { schemaText += `Foreign Keys:\n`; for (const fk of table.foreignKeys) { schemaText += ` - ${fk.name}: ${fk.columns.join(", ")} → ${fk.referencedTable}(${fk.referencedColumns.join(", ")})\n`; } } } return schemaText; }
• src/index.ts:170-170 (registration)

  Top-level call to register all Spanner tools, including 'gcp-spanner-query-natural-language', on the MCP server.
  registerSpannerTools(server);