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';