PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/data.ts:88-151 (handler)

  The complete definition of the `executeQueryTool` object for `pg_execute_query`, including the `execute` handler function that processes tool arguments, validates input, calls the internal query executor, and formats the response.
  export const executeQueryTool: PostgresTool = { name: 'pg_execute_query', description: 'Execute SELECT queries and data retrieval operations - operation="select/count/exists" with query and optional parameters. Examples: operation="select", query="SELECT * FROM users WHERE created_at > $1", parameters=["2024-01-01"]', inputSchema: ExecuteQueryInputSchema, execute: async (args: unknown, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => { const { connectionString: connStringArg, operation, query, parameters, limit, timeout } = args as { connectionString?: string; operation: 'select' | 'count' | 'exists'; query: string; parameters?: unknown[]; limit?: number; timeout?: number; }; const resolvedConnString = getConnectionStringVal(connStringArg); try { // Input validation if (!query?.trim()) { return { content: [{ type: 'text', text: 'Error: query is required' }], isError: true }; } const result = await executeQuery({ connectionString: resolvedConnString, operation, query, parameters: parameters ?? [], limit, timeout }, getConnectionStringVal); let responseText = ''; switch (operation) { case 'select': responseText = `Query executed successfully. Retrieved ${result.rowCount} rows.\n\nResults:\n${JSON.stringify(result.rows, null, 2)}`; break; case 'count': responseText = `Count query executed successfully. Total rows: ${result.result}`; break; case 'exists': responseText = `Exists query executed successfully. Result: ${result.result ? 'EXISTS' : 'NOT EXISTS'}`; break; } return { content: [{ type: 'text', text: responseText }] }; } catch (error) { return { content: [{ type: 'text', text: `Error executing ${operation} query: ${error instanceof Error ? error.message : String(error)}` }], isError: true }; } } };
• src/tools/data.ts:8-15 (schema)

  Zod schema definition (`ExecuteQueryInputSchema`) for input validation of the `pg_execute_query` tool, defining parameters like connectionString, operation, query, parameters, limit, and timeout.
  const ExecuteQueryInputSchema = z.object({ connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'), operation: z.enum(['select', 'count', 'exists']).describe('Query operation: select (fetch rows), count (count rows), exists (check existence)'), query: z.string().describe('SQL SELECT query to execute'), parameters: z.array(z.unknown()).optional().default([]).describe('Parameter values for prepared statement placeholders ($1, $2, etc.)'), limit: z.number().optional().describe('Maximum number of rows to return (safety limit)'), timeout: z.number().optional().describe('Query timeout in milliseconds') });
• src/index.ts:225-257 (registration)

  The `allTools` array where `executeQueryTool` (pg_execute_query) is registered alongside other tools. This array is passed to the `PostgreSQLServer` constructor, making the tool available to the MCP server.
  const allTools: PostgresTool[] = [ // Core Analysis & Debugging analyzeDatabaseTool, debugDatabaseTool, // Schema & Structure Management (Meta-Tools) manageSchemaTools, manageFunctionsTool, manageTriggersTools, manageIndexesTool, manageConstraintsTool, manageRLSTool, // User & Security Management manageUsersTool, // Query & Performance Management manageQueryTool, // Data Operations (Enhancement Tools) executeQueryTool, executeMutationTool, executeSqlTool, // Documentation & Metadata manageCommentsTool, // Data Migration & Monitoring exportTableDataTool, importTableDataTool, copyBetweenDatabasesTool, monitorDatabaseTool ];
• src/tools/data.ts:19-86 (helper)

  Internal `executeQuery` helper function that handles the core logic: connects to the database, validates SELECT queries, executes based on operation (select/count/exists), applies limits/timeouts, and returns results.
  async function executeQuery( input: ExecuteQueryInput, getConnectionString: GetConnectionStringFn ): Promise<{ operation: string; rowCount: number; rows?: unknown[]; result?: unknown }> { const resolvedConnectionString = getConnectionString(input.connectionString); const db = DatabaseConnection.getInstance(); const { operation, query, parameters, limit, timeout } = input; try { await db.connect(resolvedConnectionString); // Validate query is a SELECT-like operation const trimmedQuery = query.trim().toLowerCase(); if (!trimmedQuery.startsWith('select') && !trimmedQuery.startsWith('with')) { throw new McpError(ErrorCode.InvalidParams, 'Query must be a SELECT statement or CTE (WITH clause)'); } let finalQuery = query; const queryParams = parameters || []; // Apply limit if specified and not already in query if (limit && !trimmedQuery.includes('limit')) { finalQuery += ` LIMIT ${limit}`; } const queryOptions = timeout ? { timeout } : {}; switch (operation) { case 'select': { const rows = await db.query(finalQuery, queryParams, queryOptions); return { operation: 'select', rowCount: rows.length, rows: rows }; } case 'count': { // Wrap the query in a COUNT to get total rows const countQuery = `SELECT COUNT(*) as total FROM (${query}) as subquery`; const result = await db.queryOne<{ total: number }>(countQuery, queryParams, queryOptions); return { operation: 'count', rowCount: 1, result: result?.total || 0 }; } case 'exists': { // Wrap the query in an EXISTS check const existsQuery = `SELECT EXISTS (${query}) as exists`; const result = await db.queryOne<{ exists: boolean }>(existsQuery, queryParams, queryOptions); return { operation: 'exists', rowCount: 1, result: result?.exists || false }; } default: throw new McpError(ErrorCode.InvalidParams, `Unknown operation: ${operation}`); } } catch (error) { throw new McpError(ErrorCode.InternalError, `Failed to execute query: ${error instanceof Error ? error.message : String(error)}`); } finally { await db.disconnect(); } }
• src/index.ts:30-30 (registration)

  Import statement that brings `executeQueryTool` into the main index file for registration in the MCP server.
  import { executeQueryTool, executeMutationTool, executeSqlTool } from './tools/data.js';