PostgreSQL MCP Server

Instructions

Implementation Reference

• src/index.js:337-371 (handler)

  The main handler function for the 'get_indexes' tool. It connects to the PostgreSQL database, executes a query to retrieve indexes for the specified table and schema from pg_indexes, formats the results into a text response including index names, types, and definitions, and handles the case where no indexes are found.

  async getIndexes(tableName, schema = 'public') {
    const client = await this.connectToDatabase();
    
    try {
      const query = `
        SELECT 
          i.indexname,
          i.indexdef,
          am.amname AS index_type
        FROM pg_indexes i
        JOIN pg_class c ON c.relname = i.indexname
        JOIN pg_am am ON am.oid = c.relam
        WHERE i.schemaname = $1 AND i.tablename = $2
        ORDER BY i.indexname;
      `;
      
      const result = await client.query(query, [schema, tableName]);
      
      return {
        content: [
          {
            type: 'text',
            text: result.rows.length > 0 ? 
              `Indexes for table "${schema}.${tableName}":\n\n` + 
              result.rows.map(row => 
                `${row.indexname} (${row.index_type}):\n  ${row.indexdef}`
              ).join('\n\n') :
              `No indexes found for table "${schema}.${tableName}"`
          },
        ],
      };
    } finally {
      await client.end();
    }
  }

• src/index.js:120-134 (schema)

  Input schema validation for the 'get_indexes' tool, defining properties for table_name (required string) and schema (optional string with default 'public').

  inputSchema: {
    type: 'object',
    properties: {
      table_name: {
        type: 'string',
        description: 'Name of the table',
      },
      schema: {
        type: 'string',
        description: 'Schema name (default: public)',
        default: 'public'
      }
    },
    required: ['table_name'],
  },

• src/index.js:117-135 (registration)

  Tool registration in the ListTools response, defining the name 'get_indexes', description, and input schema.

  {
    name: 'get_indexes',
    description: 'Get indexes for a specific table',
    inputSchema: {
      type: 'object',
      properties: {
        table_name: {
          type: 'string',
          description: 'Name of the table',
        },
        schema: {
          type: 'string',
          description: 'Schema name (default: public)',
          default: 'public'
        }
      },
      required: ['table_name'],
    },
  },