Postgres MCP Server

Instructions

Implementation Reference

• src/index.ts:718-787 (handler)

  The primary handler function for the 'query' MCP tool. It parses the input arguments, validates them, and dispatches to appropriate QueryAPIClient methods based on the 'action' parameter (execute, transaction, explain, analyze, validate, active, cancel). Returns JSON-formatted results.
  private async handleQuery(args: any) { const { action, sql, parameters, queries, options, pid } = args; switch (action) { case 'execute': ParameterValidator.validateRequired(sql, 'sql'); return { content: [{ type: 'text', text: JSON.stringify(await this.queryClient.executeQuery(sql, parameters, options), null, 2) }] }; case 'transaction': ParameterValidator.validateRequired(queries, 'queries'); return { content: [{ type: 'text', text: JSON.stringify(await this.queryClient.executeTransaction(queries, options?.readOnly), null, 2) }] }; case 'explain': ParameterValidator.validateRequired(sql, 'sql'); return { content: [{ type: 'text', text: JSON.stringify(await this.queryClient.getExecutionPlan(sql, parameters), null, 2) }] }; case 'analyze': ParameterValidator.validateRequired(sql, 'sql'); return { content: [{ type: 'text', text: JSON.stringify(await this.queryClient.analyzeQuery(sql, parameters), null, 2) }] }; case 'validate': ParameterValidator.validateRequired(sql, 'sql'); return { content: [{ type: 'text', text: JSON.stringify(await this.queryClient.validateSyntax(sql), null, 2) }] }; case 'active': return { content: [{ type: 'text', text: JSON.stringify(await this.queryClient.getActiveQueries(), null, 2) }] }; case 'cancel': ParameterValidator.validateRequired(pid, 'pid'); return { content: [{ type: 'text', text: JSON.stringify({ cancelled: await this.queryClient.cancelQuery(pid) }, null, 2) }] }; default: throw new Error(`Unknown query action: ${action}`); } }
• src/index.ts:43-87 (schema)

  The input schema definition for the 'query' tool, defining parameters like action, sql, parameters, queries, options, pid with types, enums, descriptions, and required fields.
  inputSchema: { type: 'object', properties: { action: { type: 'string', enum: ['execute', 'transaction', 'explain', 'analyze', 'validate', 'cancel', 'active'], description: 'Action: execute (single query), transaction (multiple queries), explain (execution plan), analyze (performance), validate (syntax), cancel (query by PID), active (list active queries)' }, sql: { type: 'string', description: 'SQL query to execute (required for execute, explain, analyze, validate actions)' }, parameters: { type: 'array', items: { type: 'string' }, description: 'Query parameters for parameterized queries' }, queries: { type: 'array', items: { type: 'object', properties: { sql: { type: 'string' }, parameters: { type: 'array', items: { type: 'string' } } }, required: ['sql'] }, description: 'Array of queries for transaction action' }, options: { type: 'object', properties: { timeout: { type: 'integer', description: 'Query timeout in milliseconds' }, limit: { type: 'integer', description: 'Maximum number of rows to return' }, offset: { type: 'integer', description: 'Number of rows to skip' }, readOnly: { type: 'boolean', description: 'Execute as read-only transaction' } }, description: 'Query execution options' }, pid: { type: 'integer', description: 'Process ID of query to cancel (required for cancel action)' } }, required: ['action']
• src/index.ts:634-636 (registration)

  Registration of all tools including 'query' via the ListToolsRequestSchema handler, which returns the toolDefinitions array containing the 'query' tool definition.
  this.server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: toolDefinitions, }));
• src/index.ts:643-645 (registration)

  Dispatch registration in the CallToolRequestSchema handler switch statement, routing 'query' tool calls to the handleQuery method.
  case 'query': return await this.handleQuery(args);
• src/api/domains/query-api.ts:33-89 (helper)

  Core helper function executeQuery in QueryAPIClient, used by the 'query' tool handler for executing SQL queries with parameter validation, options handling (limit, offset, explain), logging, and error handling.
  async executeQuery( sql: string, parameters?: any[], options: QueryOptions = {} ): Promise<QueryExecutionResult> { const startTime = Date.now(); // Validate SQL const validatedSql = ParameterValidator.validateSql(sql); // Add LIMIT if specified and not already present let finalSql = validatedSql; if (options.limit && !finalSql.toUpperCase().includes('LIMIT')) { finalSql += ` LIMIT ${ParameterValidator.validateLimit(options.limit)}`; } // Add OFFSET if specified if (options.offset && !finalSql.toUpperCase().includes('OFFSET')) { finalSql += ` OFFSET ${ParameterValidator.validateOffset(options.offset)}`; } // Add EXPLAIN if requested if (options.explain) { const explainPrefix = options.analyze ? 'EXPLAIN (ANALYZE, BUFFERS)' : 'EXPLAIN'; finalSql = `${explainPrefix} ${finalSql}`; } try { const result = await this.dbManager.query(finalSql, parameters, { timeout: options.timeout, readOnly: this.isReadOnlyQuery(validatedSql) }); const duration = Date.now() - startTime; return { rows: result.rows, rowCount: result.rowCount || 0, fields: result.fields.map((field: any) => ({ name: field.name, dataTypeID: field.dataTypeID, dataTypeSize: field.dataTypeSize, dataTypeModifier: field.dataTypeModifier, format: field.format })), command: result.command, duration }; } catch (error) { logger.error('Query execution failed', { sql: sql.substring(0, 100), parameters, error: error instanceof Error ? error.message : error }); throw error; } }