gcp-spanner-query-natural-language
Query Google Cloud Spanner databases using natural language descriptions instead of SQL syntax.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | Natural language description of the query you want to execute | |
| instanceId | No | Spanner instance ID (defaults to SPANNER_INSTANCE env var) | |
| databaseId | No | Spanner database ID (defaults to SPANNER_DATABASE env var) |
Implementation Reference
- src/services/spanner/tools.ts:451-692 (handler)Handler function implementing gcp-spanner-query-natural-language: converts simple natural language queries (list tables, schema, counts, previews) to SQL, executes on Spanner, returns formatted markdown results. For complex queries, provides schema and suggests using execute-spanner-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.`, }, ], }; } },
- Zod input schema: required 'query' (natural language), 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:435-693 (registration)Registers the tool with McpServer using server.tool() including name, schema, and handler."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)getDetailedSchemaForQueryGeneration: formats Spanner schema (tables, columns, indexes, FKs) as text for use in NLQ tool.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; }