execute_query
Execute SQL queries on PostgreSQL databases with configurable read-only or write permissions. Submit queries directly for database management or retrieval.
Instructions
Execute SQL queries on PostgreSQL database
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | SQL query to execute | |
| readOnly | No | If true, only allows read-only queries (SELECT, EXPLAIN, etc.) |
Implementation Reference
- src/tools/query.ts:36-69 (handler)The primary handler function implementing the execute_query tool logic: connects to PostgreSQL using DatabaseConnection, enforces read-only restrictions, executes the query, and returns structured results.export async function executeQuery( connectionString: string, query: string, readOnly: boolean = false ): Promise<QueryResult> { const db = DatabaseConnection.getInstance(); const startTime = Date.now(); try { // Check if query is allowed in readOnly mode if (readOnly && !isReadOnlyQuery(query)) { throw new Error('Only read-only queries are allowed when readOnly is set to true'); } await db.connect(connectionString); // Execute the query const result = await db.query(query); const executionTime = Date.now() - startTime; return { success: true, rowCount: result.rowCount || 0, rows: result.rows || [], command: result.command || 'UNKNOWN', executionTime }; } catch (error) { const executionTime = Date.now() - startTime; throw new Error(`Query execution failed: ${error instanceof Error ? error.message : String(error)} (execution time: ${executionTime}ms)`); } finally { await db.disconnect(); } }
- src/index.ts:13-33 (schema)Input schema definition for the execute_query tool, specifying required 'query' parameter and optional 'readOnly' flag.const TOOL_DEFINITIONS = [ { name: 'execute_query', description: 'Execute SQL queries on PostgreSQL database', inputSchema: { type: 'object', properties: { query: { type: 'string', description: 'SQL query to execute' }, readOnly: { type: 'boolean', description: 'If true, only allows read-only queries (SELECT, EXPLAIN, etc.)', default: false } }, required: ['query'] } } ];
- src/index.ts:50-55 (registration)Registration of the execute_query tool in the MCP server's capabilities object.capabilities: { tools: { execute_query: TOOL_DEFINITIONS[0] }, }, }
- src/index.ts:78-96 (handler)MCP CallToolRequest handler case that extracts arguments, enforces server mode read-only, calls executeQuery, and formats the response.case 'execute_query': { const { query, readOnly = false } = request.params.arguments as { query: string; readOnly?: boolean; }; // Enforce readonly mode if server is in readonly mode const enforceReadOnly = this.serverMode === 'readonly' || readOnly; const result = await executeQuery(this.connectionString, query, enforceReadOnly); return { content: [ { type: 'text', text: JSON.stringify(result, null, 2) } ] }; }
- src/tools/query.ts:16-34 (helper)Helper function used by executeQuery to determine if a query is read-only safe.function isReadOnlyQuery(query: string): boolean { // Normalize query - trim whitespace and convert to uppercase for comparison const normalizedQuery = query.trim().toUpperCase(); // Check if query starts with SELECT, EXPLAIN, SHOW, etc. const readOnlyCommands = [ 'SELECT', 'EXPLAIN', 'SHOW', 'WITH', // CTE that ends with SELECT 'ANALYZE', 'DESCRIBE' ]; return readOnlyCommands.some(cmd => normalizedQuery.startsWith(cmd)) && !normalizedQuery.includes('INTO') && // Exclude SELECT INTO !normalizedQuery.includes('FOR UPDATE') && // Exclude locking queries !normalizedQuery.includes('FOR SHARE'); }