query

Instructions

Implementation Reference

• src/tools/query.ts:20-119 (handler)

  The core handler function for the 'query' tool. It validates the input parameters, checks if the SQL query is read-only, acquires a database connection from the appropriate pool, executes the query with a configurable timeout, processes the results, and returns a formatted JSON response.

  export async function runQueryTool(params: z.infer<typeof QueryToolSchema>): Promise<{ content: { type: string; text: string }[] }> {
    const { sql, environment: rawEnvironment, timeout = 30000 } = params;
    
    debug('Starting query execution with params:', { sql, environment: rawEnvironment, timeout });
    debug('Raw environment type:', typeof rawEnvironment);
    debug('Raw environment value:', rawEnvironment);
  
    // Validate query
    if (!isReadOnlyQuery(sql)) {
      debug('Query validation failed: not a read-only query');
      throw new Error("Only SELECT, SHOW, DESCRIBE, and DESC queries are allowed");
    }
    debug('Query validation passed: is read-only');
  
    // Validate environment
    debug('Validating environment:', rawEnvironment);
    debug('Environment enum:', Environment);
    debug('Environment enum values:', Object.values(Environment.enum));
    const environment = Environment.parse(rawEnvironment);
    debug('Environment validated successfully:', environment);
    debug('Validated environment type:', typeof environment);
    debug('Validated environment value:', environment);
  
    // Get connection pool
    debug('Getting connection pool for environment:', environment);
    debug('Available pools:', Array.from(pools.keys()));
    debug('Pool map type:', typeof pools);
    debug('Pool keys type:', Array.from(pools.keys()).map(k => typeof k));
    debug('Pool keys:', Array.from(pools.keys()));
    debug('Environment type:', typeof environment);
    debug('Environment value:', environment);
    debug('Pool has environment?', pools.has(environment));
    
    const pool = pools.get(environment);
    if (!pool) {
      debug('No pool found for environment:', environment);
      debug('Current pools state:', {
        size: pools.size,
        keys: Array.from(pools.keys()),
        envType: typeof environment,
        envValue: environment,
        poolsType: typeof pools,
        poolsEntries: Array.from(pools.entries()).map(([k]) => ({ key: k, type: typeof k }))
      });
      throw new Error(`No connection pool available for environment: ${environment}`);
    }
    debug('Found pool for environment:', environment);
  
    try {
      // Execute query with timeout
      const startTime = Date.now();
      debug('Getting connection from pool');
      const connection = await pool.getConnection();
      debug('Connection acquired successfully');
      
      try {
        debug('Executing query with timeout:', timeout);
        const result = await Promise.race([
          connection.query(sql),
          new Promise((_, reject) => 
            setTimeout(() => reject(new Error(`Query timeout after ${timeout}ms`)), timeout)
          ),
        ]) as [any[], any[]];
  
        const [rows, fields] = result;
        const executionTime = Date.now() - startTime;
        debug('Query executed successfully:', { 
          rowCount: rows.length, 
          executionTime,
          fieldCount: fields.length 
        });
  
        const queryResult: QueryResult = {
          rows: rows as unknown[],
          fields: fields.map(f => ({
            name: f.name,
            type: f.type,
            length: f.length,
          })),
          executionTime,
          rowCount: rows.length,
        };
  
        return {
          content: [{
            type: "text",
            text: JSON.stringify(queryResult, null, 2),
          }],
        };
      } finally {
        debug('Releasing connection back to pool');
        connection.release();
        debug('Connection released');
      }
    } catch (error) {
      const message = error instanceof Error ? error.message : "Unknown error occurred";
      debug('Error executing query:', message);
      throw new Error(`Query execution failed: ${message}`);
    }
  } 

• src/types/index.ts:20-25 (schema)

  Zod schema definition for the 'query' tool input parameters: SQL query string, target environment, and optional timeout. Re-exported as QueryToolSchema in query.ts and used for validation.

  export const QueryParams = z.object({
    sql: z.string().min(1),
    environment: Environment,
    timeout: z.number().optional().default(30000),
  });
  export type QueryParameters = z.infer<typeof QueryParams>;

• src/index.ts:222-228 (registration)

  Registration of the 'query' tool in the MCP server's CallTool request handler. Validates arguments using QueryToolSchema and invokes the runQueryTool handler.

  case queryToolName: {
    debug('Validating query tool arguments...');
    const validated = QueryToolSchema.parse(args);
    debug('Validated query tool args:', validated);
    debug('Executing query tool...');
    return await runQueryTool(validated);
  }

• src/index.ts:102-123 (registration)

  Declaration of the 'query' tool in the MCP server capabilities, specifying description and input schema for tool discovery.

  [queryToolName]: {
    description: queryToolDescription,
    inputSchema: {
      type: "object",
      properties: {
        sql: {
          type: "string",
          description: "SQL query to execute (SELECT and SHOW only)",
        },
        environment: {
          type: "string",
          enum: ["local", "development", "staging", "production"],
          description: "Target environment to run the query against",
        },
        timeout: {
          type: "number",
          description: "Query timeout in milliseconds (default: 30000)",
        },
      },
      required: ["sql", "environment"],
    },
  },

• src/tools/query.ts:14-18 (helper)

  Helper function to validate that the SQL query is read-only by checking if it starts with SELECT, SHOW, DESCRIBE, or DESC.

  export function isReadOnlyQuery(sql: string): boolean {
    const upperSql = sql.trim().toUpperCase();
    return upperSql.startsWith("SELECT") || upperSql.startsWith("SHOW") || 
           upperSql.startsWith("DESCRIBE") || upperSql.startsWith("DESC");
  }