PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/comments.ts:497-569 (handler)

  Main handler function that validates input, dispatches to specific execute* helper functions based on operation (get, set, remove, bulk_get), and formats ToolOutput.
  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 schema defining the input 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 (as manageCommentsTool) in the allTools array passed to PostgreSQLServer constructor, making it 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/comments.ts:46-191 (helper)

  Helper function to retrieve (get) comment for a specific database object using PostgreSQL system catalogs.
  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/add/update comment on a database object.
  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(); } }