Google Cloud MCP Server

Implementation Reference

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

  The handler function that takes a natural language query, determines the appropriate SQL based on simple heuristics (list tables, schema, count, preview data), executes it on the Spanner database, and formats results as markdown table. For complex queries, provides schema and instructs to use direct SQL tool.
  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 for input parameters: 'query' (string, natural language query), 'instanceId' and 'databaseId' (optional strings).
  { 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:434-693 (registration)

  The server.tool call within registerSpannerTools that registers the tool, including its name, input schema, and handler function.
  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 to generate a detailed, human-readable schema string from Spanner database schema, used for query generation and 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; }