One of the weird cultural things in the US is the equation of "urban" with "downtown" and "downtown" with "Manhattan-like." Basically, the city is where the skyscrapers are. NIMBYism related to density in urban areas that experience it (San Francisco especially these days) always raises the specter of 2-3 story residential neighborhoods being turned into "Manhattan" (And by Manhattan they mean Midtown or Downtown. You know, where the skyscrapers are).
Not everybody wants to live in Manhattan! Fair enough. But not even Manhattan is the "Manhattan" of the imagination. More importantly, the choice isn't between 2-3 floor detached single family homes and Manhattan. There's a big range inbetween. Aside from taking the pressure off of rents (no, realistic supply increases aren't going to crater SF rents, but they will at least temper increases a bit), a bit more density can maximize the value of those expensive transit links, provide more local demand for local businesses, and decrease per capita car use. The latter is important because those cars take up a lot of space!
City-as-skyscraperville was a pretty modern American development. Really only recently have tall buildings gone up in central London, and many European cities have them only on the outskirts if at all. But those cities are cities.