MySQL Query

Instructions

Implementation Reference

• src/tools/mysql-query.ts:73-111 (handler)

  The main handler function that executes the mysql_query tool logic. It resolves the query context, executes the SQL query with limits, and returns the results with metadata including row count, columns, and execution time.

  async (args) => {
    try {
      const context = resolveQueryContext(registry, args);
      const startedAt = Date.now();
      const result = await pools.query(
        context.env,
        buildLimitedReadSql(
          context.statementType,
          context.normalizedSql,
          context.limitApplied,
        ),
        context.database,
        context.timeoutMs,
      );
      const limitedRows = result.rows.slice(0, context.limitApplied);
      const structuredContent: QueryStructuredContent = {
        ok: true,
        env: context.env,
        database: context.database,
        statementType: context.statementType,
        rowCount: limitedRows.length,
        rows: limitedRows,
        columns: result.fields.map((field) => ({
          name: field.name,
          mysqlType: String(field.columnType),
        })),
        truncated: result.rows.length > context.limitApplied,
        limitApplied: context.limitApplied,
        elapsedMs: Date.now() - startedAt,
      };
  
      return buildSuccessResult(
        `Returned ${structuredContent.rowCount} row(s) from ${context.env}${context.database ? `.${context.database}` : ""} in ${structuredContent.elapsedMs}ms.`,
        structuredContent,
      );
    } catch (error) {
      return buildErrorResult(error, args.env);
    }
  },

• src/tools/mysql-query.ts:29-49 (schema)

  Input schema definition for mysql_query tool using Zod. Validates environment alias, optional database, SQL statement, row limit, and timeout parameters.

  export const mysqlQueryInputSchema = z.object({
    env: z.string().min(1).describe("Environment alias such as dev, stg, or prod_ro."),
    database: z
      .string()
      .min(1)
      .optional()
      .describe("Optional database override allowed by server policy."),
    sql: z.string().min(1).describe("One read-only SQL statement."),
    limit: z
      .number()
      .int()
      .min(1)
      .optional()
      .describe("Requested row limit. The server may clamp it."),
    timeoutMs: z
      .number()
      .int()
      .min(100)
      .optional()
      .describe("Requested execution timeout in milliseconds."),
  });

• src/tools/mysql-query.ts:10-27 (schema)

  Output schema builder for mysql_query tool using Zod. Defines the structure for successful query results including rows, columns, metadata, and execution statistics.

  const buildMysqlQueryOutputSchema = (hardMaxRows: number) =>
    z.object({
      ok: z.literal(true),
      env: z.string(),
      database: z.string().optional(),
      statementType: z.enum(["select", "show", "describe", "explain"]),
      rowCount: z.number().int().nonnegative(),
      rows: z.array(z.record(z.string(), z.unknown())).max(hardMaxRows),
      columns: z.array(
        z.object({
          name: z.string(),
          mysqlType: z.string().optional(),
        }),
      ),
      truncated: z.boolean(),
      limitApplied: z.number().int().positive().max(hardMaxRows),
      elapsedMs: z.number().int().nonnegative(),
    });

• src/tools/mysql-query.ts:51-113 (registration)

  Registration function that registers the mysql_query tool with the MCP server. Configures tool metadata, annotations (readOnly, idempotent), input/output schemas, and attaches the handler function.

  export const registerMysqlQueryTool = (
    server: McpServer,
    registry: EnvironmentRegistry,
    pools: MysqlPoolRegistry,
  ) => {
    const serverConfig = registry.getServerConfig();
  
    server.registerTool(
      "mysql_query",
      {
        title: "MySQL Query",
        description: "Run one read-only SQL statement on a configured MySQL environment.",
        annotations: {
          title: "MySQL Query",
          readOnlyHint: true,
          destructiveHint: false,
          idempotentHint: true,
          openWorldHint: false,
        },
        inputSchema: mysqlQueryInputSchema,
        outputSchema: buildMysqlQueryOutputSchema(serverConfig.hardMaxRows),
      },
      async (args) => {
        try {
          const context = resolveQueryContext(registry, args);
          const startedAt = Date.now();
          const result = await pools.query(
            context.env,
            buildLimitedReadSql(
              context.statementType,
              context.normalizedSql,
              context.limitApplied,
            ),
            context.database,
            context.timeoutMs,
          );
          const limitedRows = result.rows.slice(0, context.limitApplied);
          const structuredContent: QueryStructuredContent = {
            ok: true,
            env: context.env,
            database: context.database,
            statementType: context.statementType,
            rowCount: limitedRows.length,
            rows: limitedRows,
            columns: result.fields.map((field) => ({
              name: field.name,
              mysqlType: String(field.columnType),
            })),
            truncated: result.rows.length > context.limitApplied,
            limitApplied: context.limitApplied,
            elapsedMs: Date.now() - startedAt,
          };
  
          return buildSuccessResult(
            `Returned ${structuredContent.rowCount} row(s) from ${context.env}${context.database ? `.${context.database}` : ""} in ${structuredContent.elapsedMs}ms.`,
            structuredContent,
          );
        } catch (error) {
          return buildErrorResult(error, args.env);
        }
      },
    );
  };

• src/core/policy.ts:69-128 (helper)

  Query context resolver that validates SQL statements for mysql_query. Ensures only read-only statement types (SELECT, SHOW, DESCRIBE, EXPLAIN) are allowed, validates database permissions, and applies row limits and timeouts.

  export const resolveQueryContext = (
    registry: EnvironmentRegistry,
    args: QueryArgs,
  ): ResolvedExecutionContext & {
    normalizedSql: string;
    statementType: "select" | "show" | "describe" | "explain";
    limitApplied: number;
  } => {
    const environment = registry.getEnvironment(args.env);
    const inspection = inspectSql(args.sql);
  
    if (!inspection.normalizedSql) {
      throw createError("SQL_EMPTY", "SQL cannot be empty.", false);
    }
    if (inspection.hasMultipleStatements) {
      throw createError(
        "SQL_MULTI_STATEMENT",
        "Only one SQL statement is allowed per call.",
        false,
      );
    }
    if (inspection.hasLeadingWithClause && inspection.statementType !== "select") {
      throw createError(
        "SQL_NOT_READ_ONLY",
        "Only top-level WITH ... SELECT statements are allowed in mysql_query.",
        false,
      );
    }
    if (!isReadOnlyType(inspection.statementType)) {
      throw createError(
        "SQL_NOT_READ_ONLY",
        `Statement type '${inspection.mainKeyword || inspection.firstKeyword || "unknown"}' is not allowed in mysql_query.`,
        false,
      );
    }
  
    const database = args.database ?? environment.defaultDatabase;
    assertAllowedDatabase(environment, database);
    assertReferencedDatabasesAllowed(environment, inspection.normalizedSql);
  
    const serverConfig = registry.getServerConfig();
    const envMaxRows = Math.min(environment.maxRows, serverConfig.hardMaxRows);
    const requestedLimit = args.limit ?? serverConfig.defaultQueryLimit;
    const limitApplied = Math.max(1, Math.min(requestedLimit, envMaxRows));
  
    return {
      env: args.env,
      database,
      environment,
      timeoutMs: resolveTimeout(
        args.timeoutMs,
        environment.defaultTimeoutMs,
        serverConfig.hardTimeoutMs,
        serverConfig.defaultTimeoutMs,
      ),
      normalizedSql: inspection.normalizedSql,
      statementType: inspection.statementType,
      limitApplied,
    };
  };