PostgreSQL MCP Server

Instructions

Implementation Reference

• src/index.js:261-308 (handler)

  The main handler function that executes the tool logic: connects to Postgres, queries information_schema for foreign key constraints referencing the given table, formats and returns the relationships.

  async getTableRelationships(tableName, schema = 'public') {
    const client = await this.connectToDatabase();
    
    try {
      const query = `
        SELECT 
          kcu.column_name,
          ccu.table_schema AS foreign_table_schema,
          ccu.table_name AS foreign_table_name,
          ccu.column_name AS foreign_column_name,
          rc.constraint_name,
          rc.update_rule,
          rc.delete_rule
        FROM information_schema.table_constraints AS tc 
        JOIN information_schema.key_column_usage AS kcu
          ON tc.constraint_name = kcu.constraint_name
          AND tc.table_schema = kcu.table_schema
        JOIN information_schema.constraint_column_usage AS ccu
          ON ccu.constraint_name = tc.constraint_name
          AND ccu.table_schema = tc.table_schema
        JOIN information_schema.referential_constraints AS rc
          ON tc.constraint_name = rc.constraint_name
        WHERE tc.constraint_type = 'FOREIGN KEY' 
          AND tc.table_schema = $1 
          AND tc.table_name = $2;
      `;
      
      const result = await client.query(query, [schema, tableName]);
      
      return {
        content: [
          {
            type: 'text',
            text: result.rows.length > 0 ? 
              `Foreign key relationships for "${schema}.${tableName}":\n\n` + 
              result.rows.map(row => 
                `${row.column_name} → ${row.foreign_table_schema}.${row.foreign_table_name}.${row.foreign_column_name}\n` +
                `  Constraint: ${row.constraint_name}\n` +
                `  On Update: ${row.update_rule}, On Delete: ${row.delete_rule}`
              ).join('\n\n') :
              `No foreign key relationships found for table "${schema}.${tableName}"`
          },
        ],
      };
    } finally {
      await client.end();
    }
  }

• src/index.js:90-108 (schema)

  The tool's metadata and input schema definition returned by ListToolsRequestHandler, specifying name, description, inputSchema with table_name (required) and schema (optional).

  {
    name: 'get_table_relationships',
    description: 'Get foreign key relationships for a 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'],
    },
  },

• src/index.js:151-152 (registration)

  Tool registration in the switch statement of CallToolRequestSchema handler, dispatching calls to the getTableRelationships method.

  case 'get_table_relationships':
    return await this.getTableRelationships(args.table_name, args?.schema || 'public');