RAGmonsters Custom PostgreSQL MCP Server

Instructions

Implementation Reference

• src/mcp-server/tools/monsters.js:34-123 (handler)

  The core handler function for the getMonsters tool. It constructs a dynamic SQL query based on input parameters (filters, sort, limit, offset), executes it using the database pool, and returns a formatted list of monsters.

  export async function getMonsters(params = {}) {
    try {
      if (!dbPool) {
        throw new Error('Database pool not initialized. Call initialize() first.');
      }
      
      logger.info(`getMonsters called with params: ${JSON.stringify(params)}`);
      const { filters = {}, sort = {}, limit = 10, offset = 0 } = params;
      
      // Start building the query
      let query = `
        SELECT 
          m.monster_id,
          m.name,
          m.category,
          m.habitat,
          m.rarity,
          m.primary_power,
          m.secondary_power,
          m.special_ability
        FROM 
          monsters m
        WHERE 1=1
      `;
      
      // Build parameter array for prepared statement
      const queryParams = [];
      
      // Add filters
      if (filters.category) {
        queryParams.push(filters.category);
        query += ` AND m.category = $${queryParams.length}`;
      }
      
      if (filters.habitat) {
        queryParams.push(filters.habitat);
        query += ` AND m.habitat = $${queryParams.length}`;
      }
      
      if (filters.rarity) {
        queryParams.push(filters.rarity);
        query += ` AND m.rarity = $${queryParams.length}`;
      }
      
      // Add sorting
      if (sort.field) {
        // Validate sort field to prevent SQL injection
        const validSortFields = ['name', 'category', 'habitat', 'rarity'];
        const sortField = validSortFields.includes(sort.field) ? sort.field : 'name';
        
        // Validate sort direction
        const sortDirection = sort.direction?.toLowerCase() === 'desc' ? 'DESC' : 'ASC';
        
        query += ` ORDER BY m.${sortField} ${sortDirection}`;
      } else {
        // Default sort by name
        query += ` ORDER BY m.name ASC`;
      }
      
      // Add pagination
      query += ` LIMIT $${queryParams.length + 1} OFFSET $${queryParams.length + 2}`;
      queryParams.push(limit, offset);
      
      const monsters = await executeQuery(dbPool, query, queryParams);
      
      logger.info(`getMonsters returning ${monsters.length} monsters`);
      logger.debug(`First monster in results: ${JSON.stringify(monsters[0] || {})}`);
      
      // Format the response
      return { content:monsters.map(monster => ({
        type: 'text',
        text: JSON.stringify({
          id: monster.monster_id,
          name: monster.name,
          category: monster.category,
          habitat: monster.habitat,
          rarity: monster.rarity,
          powers: {
            primary: monster.primary_power,
            secondary: monster.secondary_power,
            special: monster.special_ability
          }
        }),
      }))};
    } catch (error) {
      logger.error(`Error in getMonsters: ${error.message}`);
      logger.error(error.stack);
      throw error;
    }
  }

• src/mcp-server/tools/index.js:17-36 (registration)

  Registers the getMonsters tool with the MCP server instance, linking the handler function and defining the input schema.

  server.addTool({
    name: 'getMonsters',
    description: 'Get a list of monsters with optional filtering, sorting, and pagination',
    parameters: z.object({
      filters: z.object({
        category: z.string().optional().describe('Filter by monster category'),
        habitat: z.string().optional().describe('Filter by monster habitat'),
        rarity: z.string().optional().describe('Filter by monster rarity')
      }).optional().describe('Optional filters for the query'),
      
      sort: z.object({
        field: z.string().optional().describe('Field to sort by (name, category, habitat, rarity)'),
        direction: z.enum(['asc', 'desc']).optional().describe('Sort direction (asc or desc)')
      }).optional().describe('Optional sorting parameters'),
      
      limit: z.number().optional().describe('Maximum number of results to return (default: 10)'),
      offset: z.number().optional().describe('Number of results to skip for pagination (default: 0)')
    }),
    execute: getMonsters
  });

• src/mcp-server/tools/index.js:20-34 (schema)

  Zod schema defining the input parameters for the getMonsters tool: filters (category, habitat, rarity), sort (field, direction), limit, offset.

  parameters: z.object({
    filters: z.object({
      category: z.string().optional().describe('Filter by monster category'),
      habitat: z.string().optional().describe('Filter by monster habitat'),
      rarity: z.string().optional().describe('Filter by monster rarity')
    }).optional().describe('Optional filters for the query'),
    
    sort: z.object({
      field: z.string().optional().describe('Field to sort by (name, category, habitat, rarity)'),
      direction: z.enum(['asc', 'desc']).optional().describe('Sort direction (asc or desc)')
    }).optional().describe('Optional sorting parameters'),
    
    limit: z.number().optional().describe('Maximum number of results to return (default: 10)'),
    offset: z.number().optional().describe('Number of results to skip for pagination (default: 0)')
  }),

• src/mcp-server/tools/monsters.js:14-17 (helper)

  Initialization function for the monsters tools module, setting up the shared database connection pool required by the handlers.

  export function initializeTools(pool) {
    dbPool = pool;
    logger.info('Monsters module initialized with database pool');
  }