PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/data.ts:302-359 (handler)

  Handler function that processes tool arguments, performs basic validation, calls the executeMutation helper, and formats the response.

  execute: async (args: unknown, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => {
    const { 
      connectionString: connStringArg,
      operation,
      table,
      data,
      where,
      conflictColumns,
      returning,
      schema
    } = args as {
      connectionString?: string;
      operation: 'insert' | 'update' | 'delete' | 'upsert';
      table: string;
      data?: Record<string, unknown>;
      where?: string;
      conflictColumns?: string[];
      returning?: string;
      schema?: string;
    };
  
    const resolvedConnString = getConnectionStringVal(connStringArg);
  
    try {
      // Input validation
      if (!table?.trim()) {
        return { 
          content: [{ type: 'text', text: 'Error: table is required' }], 
          isError: true 
        };
      }
  
      const result = await executeMutation({
        connectionString: resolvedConnString,
        operation,
        table,
        data,
        where,
        conflictColumns,
        returning,
        schema: schema || 'public'
      } as ExecuteMutationInput, getConnectionStringVal);
  
      let responseText = `${operation.toUpperCase()} operation completed successfully. Rows affected: ${result.rowsAffected}`;
      
      if (result.returning && result.returning.length > 0) {
        responseText += `\n\nReturning data:\n${JSON.stringify(result.returning, null, 2)}`;
      }
  
      return { content: [{ type: 'text', text: responseText }] };
  
    } catch (error) {
      return { 
        content: [{ type: 'text', text: `Error executing ${operation} operation: ${error instanceof Error ? error.message : String(error)}` }], 
        isError: true 
      };
    }
  }

• src/tools/data.ts:168-296 (helper)

  Core helper function that constructs and executes the specific SQL mutation (INSERT, UPDATE, DELETE, UPSERT) using dynamic SQL building and database connection.

  async function executeMutation(
    input: ExecuteMutationInput,
    getConnectionString: GetConnectionStringFn
  ): Promise<{ operation: string; rowsAffected: number; returning?: unknown[] }> {
    const resolvedConnectionString = getConnectionString(input.connectionString);
    const db = DatabaseConnection.getInstance();
    const { operation, table, data, where, conflictColumns, returning, schema } = input;
  
    try {
      await db.connect(resolvedConnectionString);
      
      const schemaPrefix = (schema && schema !== 'public') ? `"${schema}".` : '';
      const tableName = `${schemaPrefix}"${table}"`;
  
      switch (operation) {
        case 'insert': {
          if (!data || Object.keys(data).length === 0) {
            throw new McpError(ErrorCode.InvalidParams, 'Data object is required for insert operation');
          }
  
          const columns = Object.keys(data);
          const values = Object.values(data);
          const placeholders = values.map((_, i) => `$${i + 1}`).join(', ');
          
          let insertSQL = `INSERT INTO ${tableName} (${columns.map(col => `"${col}"`).join(', ')}) VALUES (${placeholders})`;
          
          if (returning) {
            insertSQL += ` RETURNING ${returning}`;
          }
  
          const result = await db.query(insertSQL, values);
          return {
            operation: 'insert',
            rowsAffected: Array.isArray(result) ? result.length : 1,
            returning: returning ? result : undefined
          };
        }
  
        case 'update': {
          if (!data || Object.keys(data).length === 0) {
            throw new McpError(ErrorCode.InvalidParams, 'Data object is required for update operation');
          }
          if (!where) {
            throw new McpError(ErrorCode.InvalidParams, 'WHERE clause is required for update operation to prevent accidental full table updates');
          }
  
          const columns = Object.keys(data);
          const values = Object.values(data);
          const setClause = columns.map((col, i) => `"${col}" = $${i + 1}`).join(', ');
          
          let updateSQL = `UPDATE ${tableName} SET ${setClause} WHERE ${where}`;
          
          if (returning) {
            updateSQL += ` RETURNING ${returning}`;
          }
  
          const result = await db.query(updateSQL, values);
          return {
            operation: 'update',
            rowsAffected: Array.isArray(result) ? result.length : 1,
            returning: returning ? result : undefined
          };
        }
  
        case 'delete': {
          if (!where) {
            throw new McpError(ErrorCode.InvalidParams, 'WHERE clause is required for delete operation to prevent accidental full table deletion');
          }
  
          let deleteSQL = `DELETE FROM ${tableName} WHERE ${where}`;
          
          if (returning) {
            deleteSQL += ` RETURNING ${returning}`;
          }
  
          const result = await db.query(deleteSQL);
          return {
            operation: 'delete',
            rowsAffected: Array.isArray(result) ? result.length : 1,
            returning: returning ? result : undefined
          };
        }
  
        case 'upsert': {
          if (!data || Object.keys(data).length === 0) {
            throw new McpError(ErrorCode.InvalidParams, 'Data object is required for upsert operation');
          }
          if (!conflictColumns || conflictColumns.length === 0) {
            throw new McpError(ErrorCode.InvalidParams, 'Conflict columns are required for upsert operation');
          }
  
          const columns = Object.keys(data);
          const values = Object.values(data);
          const placeholders = values.map((_, i) => `$${i + 1}`).join(', ');
          const conflictCols = conflictColumns.map(col => `"${col}"`).join(', ');
          const updateClause = columns
            .filter(col => !conflictColumns.includes(col))
            .map(col => `"${col}" = EXCLUDED."${col}"`)
            .join(', ');
          
          let upsertSQL = `INSERT INTO ${tableName} (${columns.map(col => `"${col}"`).join(', ')}) VALUES (${placeholders}) ON CONFLICT (${conflictCols})`;
          
          if (updateClause) {
            upsertSQL += ` DO UPDATE SET ${updateClause}`;
          } else {
            upsertSQL += ' DO NOTHING';
          }
          
          if (returning) {
            upsertSQL += ` RETURNING ${returning}`;
          }
  
          const result = await db.query(upsertSQL, values);
          return {
            operation: 'upsert',
            rowsAffected: Array.isArray(result) ? result.length : 1,
            returning: returning ? result : undefined
          };
        }
  
        default:
          throw new McpError(ErrorCode.InvalidParams, `Unknown operation: ${operation}`);
      }
    } catch (error) {
      throw new McpError(ErrorCode.InternalError, `Failed to execute ${operation}: ${error instanceof Error ? error.message : String(error)}`);
    } finally {
      await db.disconnect();
    }
  }

• src/tools/data.ts:155-164 (schema)

  Zod schema defining the input parameters for the pg_execute_mutation tool.

  const ExecuteMutationInputSchema = z.object({
    connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'),
    operation: z.enum(['insert', 'update', 'delete', 'upsert']).describe('Mutation operation: insert (add rows), update (modify rows), delete (remove rows), upsert (insert or update)'),
    table: z.string().describe('Table name for the operation'),
    data: z.record(z.unknown()).optional().describe('Data object with column-value pairs (required for insert/update/upsert)'),
    where: z.string().optional().describe('WHERE clause for update/delete operations (without WHERE keyword)'),
    conflictColumns: z.array(z.string()).optional().describe('Columns for conflict resolution in upsert (ON CONFLICT)'),
    returning: z.string().optional().describe('RETURNING clause to get back inserted/updated data'),
    schema: z.string().optional().default('public').describe('Schema name (defaults to public)')
  });

• src/tools/data.ts:298-360 (registration)

  Definition and export of the PostgresTool object for pg_execute_mutation.

  export const executeMutationTool: PostgresTool = {
    name: 'pg_execute_mutation',
    description: 'Execute data modification operations (INSERT/UPDATE/DELETE/UPSERT) - operation="insert/update/delete/upsert" with table and data. Examples: operation="insert", table="users", data={"name":"John","email":"john@example.com"}',
    inputSchema: ExecuteMutationInputSchema,
    execute: async (args: unknown, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => {
      const { 
        connectionString: connStringArg,
        operation,
        table,
        data,
        where,
        conflictColumns,
        returning,
        schema
      } = args as {
        connectionString?: string;
        operation: 'insert' | 'update' | 'delete' | 'upsert';
        table: string;
        data?: Record<string, unknown>;
        where?: string;
        conflictColumns?: string[];
        returning?: string;
        schema?: string;
      };
  
      const resolvedConnString = getConnectionStringVal(connStringArg);
  
      try {
        // Input validation
        if (!table?.trim()) {
          return { 
            content: [{ type: 'text', text: 'Error: table is required' }], 
            isError: true 
          };
        }
  
        const result = await executeMutation({
          connectionString: resolvedConnString,
          operation,
          table,
          data,
          where,
          conflictColumns,
          returning,
          schema: schema || 'public'
        } as ExecuteMutationInput, getConnectionStringVal);
  
        let responseText = `${operation.toUpperCase()} operation completed successfully. Rows affected: ${result.rowsAffected}`;
        
        if (result.returning && result.returning.length > 0) {
          responseText += `\n\nReturning data:\n${JSON.stringify(result.returning, null, 2)}`;
        }
  
        return { content: [{ type: 'text', text: responseText }] };
  
      } catch (error) {
        return { 
          content: [{ type: 'text', text: `Error executing ${operation} operation: ${error instanceof Error ? error.message : String(error)}` }], 
          isError: true 
        };
      }
    }
  };

• src/index.ts:225-257 (registration)

  Inclusion of executeMutationTool in the allTools array passed to the MCP server constructor for registration.

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