PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/comments.ts:493-570 (handler)

  Main tool handler for 'pg_manage_comments'. Defines the tool object with name, description, schema reference, and execute function that validates input and dispatches to specific operations: get, set, remove, bulk_get.

  export const manageCommentsTool: PostgresTool = {
    name: 'pg_manage_comments',
    description: 'Manage PostgreSQL object comments - get, set, remove comments on tables, columns, functions, and other database objects. Examples: operation="get" with objectType="table", objectName="users", operation="set" with comment text, operation="bulk_get" for discovery',
    inputSchema: ManageCommentsInputSchema,
    execute: async (args: unknown, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => {
      const validationResult = ManageCommentsInputSchema.safeParse(args);
      if (!validationResult.success) {
        return { 
          content: [{ type: 'text', text: `Invalid input: ${validationResult.error.format()}` }], 
          isError: true 
        };
      }
  
      const input = validationResult.data;
  
      try {
        switch (input.operation) {
          case 'get': {
            const result = await executeGetComment(input, getConnectionStringVal);
            if (!result) {
              return { 
                content: [{ type: 'text', text: `No comment found for ${input.objectType} ${input.objectName}` }] 
              };
            }
            return { 
              content: [
                { type: 'text', text: `Comment for ${input.objectType} ${input.objectName}${input.columnName ? `.${input.columnName}` : ''}` },
                { type: 'text', text: JSON.stringify(result, null, 2) }
              ] 
            };
          }
  
          case 'set': {
            const result = await executeSetComment(input, getConnectionStringVal);
            return { 
              content: [
                { type: 'text', text: `Comment set successfully on ${result.objectType} ${result.objectName}${result.columnName ? `.${result.columnName}` : ''}` },
                { type: 'text', text: JSON.stringify(result, null, 2) }
              ] 
            };
          }
  
          case 'remove': {
            const result = await executeRemoveComment(input, getConnectionStringVal);
            return { 
              content: [
                { type: 'text', text: `Comment removed from ${result.objectType} ${result.objectName}${result.columnName ? `.${result.columnName}` : ''}` },
                { type: 'text', text: JSON.stringify(result, null, 2) }
              ] 
            };
          }
  
          case 'bulk_get': {
            const result = await executeBulkGetComments(input, getConnectionStringVal);
            return { 
              content: [
                { type: 'text', text: `Found ${result.length} comments in schema ${input.schema || 'public'}` },
                { type: 'text', text: JSON.stringify(result, null, 2) }
              ] 
            };
          }
  
          default:
            return { 
              content: [{ type: 'text', text: `Error: Unknown operation "${input.operation}". Supported operations: get, set, remove, bulk_get` }], 
              isError: true 
            };
        }
  
      } catch (error) {
        const errorMessage = error instanceof McpError ? error.message : (error instanceof Error ? error.message : String(error));
        return { 
          content: [{ type: 'text', text: `Error executing ${input.operation} operation: ${errorMessage}` }], 
          isError: true 
        };
      }
    }
  }; 

• src/tools/comments.ts:21-39 (schema)

  Zod input schema defining parameters for the pg_manage_comments tool, including operation type and object identifiers.

  const ManageCommentsInputSchema = z.object({
    connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'),
    operation: z.enum(['get', 'set', 'remove', 'bulk_get']).describe('Operation: get (retrieve comments), set (add/update comment), remove (delete comment), bulk_get (discovery mode)'),
    
    // Target object identification
    objectType: z.enum(['table', 'column', 'index', 'constraint', 'function', 'trigger', 'view', 'sequence', 'schema', 'database']).optional().describe('Type of database object (required for get/set/remove)'),
    objectName: z.string().optional().describe('Name of the object (required for get/set/remove)'),
    schema: z.string().optional().describe('Schema name (defaults to public, required for most object types)'),
    
    // Column-specific parameters
    columnName: z.string().optional().describe('Column name (required when objectType is "column")'),
    
    // Comment content
    comment: z.string().optional().describe('Comment text (required for set operation)'),
    
    // Bulk get parameters
    includeSystemObjects: z.boolean().optional().describe('Include system objects in bulk_get (defaults to false)'),
    filterObjectType: z.enum(['table', 'column', 'index', 'constraint', 'function', 'trigger', 'view', 'sequence', 'schema', 'database']).optional().describe('Filter by object type in bulk_get operation')
  });

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

  Registration of pg_manage_comments tool (imported as manageCommentsTool) in the central allTools array, which is passed to PostgreSQLServer constructor to make tools available.

  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/comments.ts:46-191 (helper)

  Helper function to retrieve (get) comment for a specific database object using dynamic SQL queries based on objectType.

  async function executeGetComment(
    input: ManageCommentsInput,
    getConnectionString: GetConnectionStringFn
  ): Promise<CommentInfo | null> {
    const resolvedConnectionString = getConnectionString(input.connectionString);
    const db = DatabaseConnection.getInstance();
    const { objectType, objectName, schema = 'public', columnName } = input;
  
    if (!objectType || !objectName) {
      throw new McpError(ErrorCode.InvalidParams, 'objectType and objectName are required for get operation');
    }
  
    try {
      await db.connect(resolvedConnectionString);
      
      let query: string;
      let params: (string | undefined)[];
  
      switch (objectType) {
        case 'table':
          query = `
            SELECT obj_description(c.oid, 'pg_class') AS comment
            FROM pg_class c
            JOIN pg_namespace n ON c.relnamespace = n.oid
            WHERE c.relname = $1 AND n.nspname = $2 AND c.relkind = 'r'
          `;
          params = [objectName, schema];
          break;
  
        case 'column':
          if (!columnName) {
            throw new McpError(ErrorCode.InvalidParams, 'columnName is required when objectType is "column"');
          }
          query = `
            SELECT col_description(c.oid, a.attnum) AS comment
            FROM pg_class c
            JOIN pg_namespace n ON c.relnamespace = n.oid
            JOIN pg_attribute a ON a.attrelid = c.oid
            WHERE c.relname = $1 AND n.nspname = $2 AND a.attname = $3 AND NOT a.attisdropped
          `;
          params = [objectName, schema, columnName];
          break;
  
        case 'index':
          query = `
            SELECT obj_description(c.oid, 'pg_class') AS comment
            FROM pg_class c
            JOIN pg_namespace n ON c.relnamespace = n.oid
            WHERE c.relname = $1 AND n.nspname = $2 AND c.relkind = 'i'
          `;
          params = [objectName, schema];
          break;
  
        case 'function':
          query = `
            SELECT obj_description(p.oid, 'pg_proc') AS comment
            FROM pg_proc p
            JOIN pg_namespace n ON p.pronamespace = n.oid
            WHERE p.proname = $1 AND n.nspname = $2
          `;
          params = [objectName, schema];
          break;
  
        case 'view':
          query = `
            SELECT obj_description(c.oid, 'pg_class') AS comment
            FROM pg_class c
            JOIN pg_namespace n ON c.relnamespace = n.oid
            WHERE c.relname = $1 AND n.nspname = $2 AND c.relkind = 'v'
          `;
          params = [objectName, schema];
          break;
  
        case 'sequence':
          query = `
            SELECT obj_description(c.oid, 'pg_class') AS comment
            FROM pg_class c
            JOIN pg_namespace n ON c.relnamespace = n.oid
            WHERE c.relname = $1 AND n.nspname = $2 AND c.relkind = 'S'
          `;
          params = [objectName, schema];
          break;
  
        case 'schema':
          query = `
            SELECT obj_description(n.oid, 'pg_namespace') AS comment
            FROM pg_namespace n
            WHERE n.nspname = $1
          `;
          params = [objectName];
          break;
  
        case 'database':
          query = `
            SELECT shobj_description(d.oid, 'pg_database') AS comment
            FROM pg_database d
            WHERE d.datname = $1
          `;
          params = [objectName];
          break;
  
        case 'constraint':
          query = `
            SELECT obj_description(con.oid, 'pg_constraint') AS comment
            FROM pg_constraint con
            JOIN pg_namespace n ON con.connamespace = n.oid
            WHERE con.conname = $1 AND n.nspname = $2
          `;
          params = [objectName, schema];
          break;
  
        case 'trigger':
          query = `
            SELECT obj_description(t.oid, 'pg_trigger') AS comment
            FROM pg_trigger t
            JOIN pg_class c ON t.tgrelid = c.oid
            JOIN pg_namespace n ON c.relnamespace = n.oid
            WHERE t.tgname = $1 AND n.nspname = $2
          `;
          params = [objectName, schema];
          break;
  
        default:
          throw new McpError(ErrorCode.InvalidParams, `Unsupported object type: ${objectType}`);
      }
  
      const result = await db.query(query, params);
      
      if (result.length === 0) {
        return null;
      }
  
      return {
        objectType,
        objectName,
        objectSchema: objectType !== 'database' && objectType !== 'schema' ? schema : undefined,
        columnName,
        comment: result[0].comment as string | null
      };
  
    } catch (error) {
      throw new McpError(ErrorCode.InternalError, `Failed to get comment: ${error instanceof Error ? error.message : String(error)}`);
    } finally {
      await db.disconnect();
    }
  }

• src/tools/comments.ts:196-278 (helper)

  Helper function to set/update comment on database objects using generated COMMENT ON SQL.

  async function executeSetComment(
    input: ManageCommentsInput,
    getConnectionString: GetConnectionStringFn
  ): Promise<{ objectType: string; objectName: string; schema?: string; columnName?: string; comment: string }> {
    const resolvedConnectionString = getConnectionString(input.connectionString);
    const db = DatabaseConnection.getInstance();
    const { objectType, objectName, schema = 'public', columnName, comment } = input;
  
    if (!objectType || !objectName || comment === undefined) {
      throw new McpError(ErrorCode.InvalidParams, 'objectType, objectName, and comment are required for set operation');
    }
  
    try {
      await db.connect(resolvedConnectionString);
      
      let sql: string;
      const escapedComment = comment.replace(/'/g, "''"); // Escape single quotes
  
      switch (objectType) {
        case 'table':
          sql = `COMMENT ON TABLE "${schema}"."${objectName}" IS '${escapedComment}'`;
          break;
  
        case 'column':
          if (!columnName) {
            throw new McpError(ErrorCode.InvalidParams, 'columnName is required when objectType is "column"');
          }
          sql = `COMMENT ON COLUMN "${schema}"."${objectName}"."${columnName}" IS '${escapedComment}'`;
          break;
  
        case 'index':
          sql = `COMMENT ON INDEX "${schema}"."${objectName}" IS '${escapedComment}'`;
          break;
  
        case 'function':
          // Note: This is simplified - in practice, you'd need to handle function overloads
          sql = `COMMENT ON FUNCTION "${schema}"."${objectName}" IS '${escapedComment}'`;
          break;
  
        case 'view':
          sql = `COMMENT ON VIEW "${schema}"."${objectName}" IS '${escapedComment}'`;
          break;
  
        case 'sequence':
          sql = `COMMENT ON SEQUENCE "${schema}"."${objectName}" IS '${escapedComment}'`;
          break;
  
        case 'schema':
          sql = `COMMENT ON SCHEMA "${objectName}" IS '${escapedComment}'`;
          break;
  
        case 'database':
          sql = `COMMENT ON DATABASE "${objectName}" IS '${escapedComment}'`;
          break;
  
        case 'constraint':
          sql = `COMMENT ON CONSTRAINT "${objectName}" ON "${schema}"."${objectName}" IS '${escapedComment}'`;
          break;
  
        case 'trigger':
          // Note: PostgreSQL doesn't support COMMENT ON TRIGGER directly
          throw new McpError(ErrorCode.InvalidParams, 'PostgreSQL does not support comments on triggers');
  
        default:
          throw new McpError(ErrorCode.InvalidParams, `Unsupported object type: ${objectType}`);
      }
  
      await db.query(sql);
  
      return {
        objectType,
        objectName,
        schema: objectType !== 'database' && objectType !== 'schema' ? schema : undefined,
        columnName,
        comment
      };
  
    } catch (error) {
      throw new McpError(ErrorCode.InternalError, `Failed to set comment: ${error instanceof Error ? error.message : String(error)}`);
    } finally {
      await db.disconnect();
    }
  }