gcp-spanner-execute-query

Execute SQL queries on Google Cloud Spanner databases to retrieve, update, or manage data through the Google Cloud MCP Server.

Input Schema

TableJSON Schema

Name	Required	Description
`sql`	Yes	The SQL query to execute
`instanceId`	No	Spanner instance ID (defaults to SPANNER_INSTANCE env var)
`databaseId`	No	Spanner database ID (defaults to SPANNER_DATABASE env var)
`params`	No	Query parameters

Implementation Reference

src/services/spanner/tools.ts:73-147 (handler)

Main execution logic for the gcp-spanner-execute-query tool. Retrieves Spanner client and config, executes the SQL query with optional parameters, formats results as a markdown table (limited to 100 rows), and handles errors by throwing.

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 input schema defining parameters for the tool: required SQL query, optional instanceId/databaseId (default from env vars), optional params object.

{
  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)

Direct registration of the gcp-spanner-execute-query tool on the MCP server within 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;
    }
  },
);

src/index.ts:170-170 (registration)
Top-level invocation of registerSpannerTools in main server setup, which registers the Spanner tools including gcp-spanner-execute-query.
```
registerSpannerTools(server);
```

src/services/spanner/types.ts:79-103 (helper)

Helper function used in the handler to determine Spanner instanceId and databaseId from parameters or environment variables.

export async function getSpannerConfig(
  instanceId?: string,
  databaseId?: string,
): Promise<{ instanceId: string; databaseId: string }> {
  const instance = instanceId || process.env.SPANNER_INSTANCE;
  const database = databaseId || process.env.SPANNER_DATABASE;

  if (!instance) {
    throw new GcpMcpError(
      "Spanner instance ID not provided. Set SPANNER_INSTANCE environment variable or provide instanceId parameter.",
      "INVALID_ARGUMENT",
      400,
    );
  }

  if (!database) {
    throw new GcpMcpError(
      "Spanner database ID not provided. Set SPANNER_DATABASE environment variable or provide databaseId parameter.",
      "INVALID_ARGUMENT",
      400,
    );
  }

  return { instanceId: instance, databaseId: database };
}

Google Cloud MCP Server