query
Execute read-only SQL queries (SELECT and SHOW) on MySQL databases across environments, enabling data exploration and database structure investigation via AI-powered tools.
Instructions
Execute read-only SQL queries against MySQL databases
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| environment | Yes | Target environment to run the query against | |
| sql | Yes | SQL query to execute (SELECT and SHOW only) | |
| timeout | No | Query timeout in milliseconds (default: 30000) |
Implementation Reference
- src/tools/query.ts:20-119 (handler)Core handler function that validates the query (read-only check), parses environment, acquires connection from pool, executes SQL with timeout, formats results, and returns 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-24 (schema)Zod schema definition for query tool input parameters (QueryParams), used as QueryToolSchema for validation.export const QueryParams = z.object({ sql: z.string().min(1), environment: Environment, timeout: z.number().optional().default(30000), });
- src/index.ts:222-228 (registration)Registration in MCP CallTool handler: validates arguments with QueryToolSchema and invokes runQueryTool.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/tools/query.ts:9-12 (schema)Tool metadata: name, description, and schema reference (QueryToolSchema = QueryParams).export const queryToolName = "query"; export const queryToolDescription = "Execute read-only SQL queries against MySQL databases"; export const QueryToolSchema = QueryParams;
- src/tools/query.ts:14-18 (helper)Helper function to validate that the SQL query is read-only (SELECT, SHOW, DESCRIBE, DESC only).export function isReadOnlyQuery(sql: string): boolean { const upperSql = sql.trim().toUpperCase(); return upperSql.startsWith("SELECT") || upperSql.startsWith("SHOW") || upperSql.startsWith("DESCRIBE") || upperSql.startsWith("DESC"); }
- src/index.ts:102-123 (registration)MCP server capabilities declaration for the 'query' tool, including inline input schema.[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"], }, },