PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/data.ts:429-485 (handler)

  Full tool definition including the handler execute function for pg_execute_sql. This is the core implementation that handles tool calls, validates input, invokes the SQL execution helper, and formats the output.

  export const executeSqlTool: PostgresTool = {
    name: 'pg_execute_sql',
    description: 'Execute arbitrary SQL statements - sql="ANY_VALID_SQL" with optional parameters and transaction support. Examples: sql="CREATE INDEX ...", sql="WITH complex_cte AS (...) SELECT ...", transactional=true',
    inputSchema: ExecuteSqlInputSchema,
    execute: async (args: unknown, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => {
      const { 
        connectionString: connStringArg,
        sql,
        parameters,
        expectRows,
        timeout,
        transactional
      } = args as {
        connectionString?: string;
        sql: string;
        parameters?: unknown[];
        expectRows?: boolean;
        timeout?: number;
        transactional?: boolean;
      };
  
      const resolvedConnString = getConnectionStringVal(connStringArg);
  
      try {
        // Input validation
        if (!sql?.trim()) {
          return { 
            content: [{ type: 'text', text: 'Error: sql is required' }], 
            isError: true 
          };
        }
  
        const result = await executeSql({
          connectionString: resolvedConnString,
          sql,
          parameters: parameters ?? [],
          expectRows: expectRows ?? true,
          timeout,
          transactional: transactional ?? false
        }, getConnectionStringVal);
  
        let responseText = result.message;
        
        if (result.rows && result.rows.length > 0) {
          responseText += `\n\nResults:\n${JSON.stringify(result.rows, null, 2)}`;
        }
  
        return { content: [{ type: 'text', text: responseText }] };
  
      } catch (error) {
        return { 
          content: [{ type: 'text', text: `Error executing SQL: ${error instanceof Error ? error.message : String(error)}` }], 
          isError: true 
        };
      }
    }
  }; 

• src/tools/data.ts:364-371 (schema)

  Zod schema for input validation of pg_execute_sql tool parameters.

  const ExecuteSqlInputSchema = z.object({
    connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'),
    sql: z.string().describe('SQL statement to execute (can be any valid PostgreSQL SQL)'),
    parameters: z.array(z.unknown()).optional().default([]).describe('Parameter values for prepared statement placeholders ($1, $2, etc.)'),
    expectRows: z.boolean().optional().default(true).describe('Whether to expect rows back (false for statements like CREATE, DROP, etc.)'),
    timeout: z.number().optional().describe('Query timeout in milliseconds'),
    transactional: z.boolean().optional().default(false).describe('Whether to wrap in a transaction')
  });

• src/tools/data.ts:375-427 (helper)

  Helper function that performs the actual database connection, SQL execution (supporting transactions), result processing, and error handling for pg_execute_sql.

  async function executeSql(
    input: ExecuteSqlInput,
    getConnectionString: GetConnectionStringFn
  ): Promise<{ sql: string; rowsAffected?: number; rows?: unknown[]; message: string }> {
    const resolvedConnectionString = getConnectionString(input.connectionString);
    const db = DatabaseConnection.getInstance();
    const { sql, parameters, expectRows, timeout, transactional } = input;
  
    try {
      await db.connect(resolvedConnectionString);
      
      const queryOptions = timeout ? { timeout } : {};
  
      if (transactional) {
        return await db.transaction(async (client) => {
          const result = await client.query(sql, parameters || []);
          
          if (expectRows) {
            return {
              sql,
              rowsAffected: Array.isArray(result.rows) ? result.rows.length : 0,
              rows: result.rows,
              message: `SQL executed successfully in transaction. Retrieved ${Array.isArray(result.rows) ? result.rows.length : 0} rows.`
            };
          }
          return {
            sql,
            rowsAffected: result.rowCount || 0,
            message: `SQL executed successfully in transaction. Rows affected: ${result.rowCount || 0}`
          };
        });
      }
      const result = await db.query(sql, parameters || [], queryOptions);
      
      if (expectRows) {
        return {
          sql,
          rowsAffected: Array.isArray(result) ? result.length : 0,
          rows: result,
          message: `SQL executed successfully. Retrieved ${Array.isArray(result) ? result.length : 0} rows.`
        };
      }
      return {
        sql,
        rowsAffected: Array.isArray(result) ? result.length : 1,
        message: 'SQL executed successfully. Operation completed.'
      };
    } catch (error) {
      throw new McpError(ErrorCode.InternalError, `Failed to execute SQL: ${error instanceof Error ? error.message : String(error)}`);
    } finally {
      await db.disconnect();
    }
  }

• src/index.ts:30-30 (registration)

  Import of the pg_execute_sql tool (as executeSqlTool) from data.ts.

  import { executeQueryTool, executeMutationTool, executeSqlTool } from './tools/data.js';

• src/index.ts:245-247 (registration)

  Registration of pg_execute_sql tool (executeSqlTool) in the allTools array passed to the MCP server constructor.

  executeQueryTool,
  executeMutationTool,
  executeSqlTool,