My biggest concerns with spray foam are that foam does compress a deteriorate over time. Additionally, in roof applications, it can limit airflow because it results in a solid cavity unless you install air foils to all air to circulate next to the roof. That being said, all products have potential to lose effectiveness over time.
I'm a huge fan of cellulose insulation based on cost, fire retardation, and R-factor. For the underside of roofs, look into radiant barriers if you have an insulated ceiling and a non-insulated rafter/attic space. It can basically reflect the heat off the top side of your insulation and help reduce the temp differentials. If you've ever worked under an uninsulated roof, you'll understand how much that can help.