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
`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.`,
        },
      ],
    };
  }
},

src/services/spanner/tools.ts:437-450 (schema)

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;
}

Google Cloud MCP Server