execute_query
Run read-only SQL queries (SELECT, SHOW, DESCRIBE, EXPLAIN, WITH) on PostgreSQL databases, with optional row limits, to retrieve or analyze data securely.
Instructions
Execute a read-only SQL query (SELECT, SHOW, DESCRIBE, EXPLAIN, WITH statements only)
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | Maximum number of rows to return (default: 100) | |
| query | Yes | SQL query to execute (read-only operations only) |
Implementation Reference
- src/index.ts:301-362 (handler)The handler function for the 'execute_query' tool. It processes the input query and limit, adds a LIMIT if necessary for SELECT queries, executes the query using the helper executeQuery, formats the results as a markdown table, and returns them. Includes error handling.async ({ query, limit }) => { try { const maxLimit = limit || 100; // Add LIMIT clause if not present and it's a SELECT query let finalQuery = query.trim(); const normalizedQuery = finalQuery.toLowerCase(); if (normalizedQuery.startsWith('select') && !normalizedQuery.includes('limit')) { finalQuery += ` LIMIT ${maxLimit}`; } const rows = await executeQuery(finalQuery); if (rows.length === 0) { return { content: [ { type: "text", text: "Query executed successfully. No rows returned.", }, ], }; } // Format results as a table const headers = Object.keys(rows[0]); let result = `Query Results (${rows.length} rows):\n\n`; // Add headers result += headers.join(' | ') + '\n'; result += headers.map(() => '---').join(' | ') + '\n'; // Add rows rows.forEach(row => { const values = headers.map(header => { const value = row[header]; return value === null ? 'NULL' : String(value); }); result += values.join(' | ') + '\n'; }); return { content: [ { type: "text", text: result, }, ], }; } catch (error) { const errorMessage = error instanceof Error ? error.message : "Unknown error"; return { content: [ { type: "text", text: `Error executing query: ${errorMessage}`, }, ], }; } }
- src/index.ts:297-300 (schema)Zod schema defining the input parameters for the 'execute_query' tool: required 'query' string and optional 'limit' number.{ query: z.string().describe("SQL query to execute (read-only operations only)"), limit: z.number().describe("Maximum number of rows to return (default: 100)").optional(), },
- src/index.ts:294-363 (registration)Registration of the 'execute_query' tool using server.tool(), including name, description, input schema, and handler function.server.tool( "execute_query", "Execute a read-only SQL query (SELECT, SHOW, DESCRIBE, EXPLAIN, WITH statements only)", { query: z.string().describe("SQL query to execute (read-only operations only)"), limit: z.number().describe("Maximum number of rows to return (default: 100)").optional(), }, async ({ query, limit }) => { try { const maxLimit = limit || 100; // Add LIMIT clause if not present and it's a SELECT query let finalQuery = query.trim(); const normalizedQuery = finalQuery.toLowerCase(); if (normalizedQuery.startsWith('select') && !normalizedQuery.includes('limit')) { finalQuery += ` LIMIT ${maxLimit}`; } const rows = await executeQuery(finalQuery); if (rows.length === 0) { return { content: [ { type: "text", text: "Query executed successfully. No rows returned.", }, ], }; } // Format results as a table const headers = Object.keys(rows[0]); let result = `Query Results (${rows.length} rows):\n\n`; // Add headers result += headers.join(' | ') + '\n'; result += headers.map(() => '---').join(' | ') + '\n'; // Add rows rows.forEach(row => { const values = headers.map(header => { const value = row[header]; return value === null ? 'NULL' : String(value); }); result += values.join(' | ') + '\n'; }); return { content: [ { type: "text", text: result, }, ], }; } catch (error) { const errorMessage = error instanceof Error ? error.message : "Unknown error"; return { content: [ { type: "text", text: `Error executing query: ${errorMessage}`, }, ], }; } } );
- src/index.ts:43-62 (helper)Helper function 'executeQuery' that performs the actual database query execution with read-only safety checks. Used by the 'execute_query' handler and other tools.async function executeQuery(query: string, params: any[] = []): Promise<any[]> { const client = await getDbConnection(); // Basic safety checks for read-only operations const normalizedQuery = query.trim().toLowerCase(); const readOnlyPrefixes = ['select', 'show', 'describe', 'explain', 'with']; const isReadOnly = readOnlyPrefixes.some(prefix => normalizedQuery.startsWith(prefix)); if (!isReadOnly) { throw new Error("Only read-only queries (SELECT, SHOW, DESCRIBE, EXPLAIN, WITH) are allowed for security."); } try { const result = await client.query(query, params); return result.rows; } catch (error) { const errorMessage = error instanceof Error ? error.message : "Unknown error occurred"; throw new Error(`Query execution failed: ${errorMessage}`); } }