I’ve used cantis, V brakes, dual pivots, drum brakes, cable discs and hydro discs.
Most importantly, in almost all situations, braking force is limited by tyre to surface friction, not by the brakes (but see tandem comments). Hydro discs are not in general more powerful!
Personal opinion:
Sunday best road bike: dual pivot rim brakes. Light, simple, cheap, excellent.
Commuter/winter bike: Discs, either hydro or cable, swings & roundabouts. Give mudguard clearance and avoids the issue of rim wear which is really important commuting through winter.
MTB: hydro discs. Avoids the issues with crud into the cables. But Vee brakes are actually very good and more than adequate.
Tourer: either cable discs or Vee brakes. Wouldn’t want to have to fix a hydro system in the wild – unlikely but fatal failure and hydro not needed at all for this duty.
Tandem: extra drum brake if possible. Discs alone better than rim alone simply for braking force. Would go hydro unless planning to tour.
Anyone with reduced motor function in hands – hydro discs for better mechanical advantage
Cantis are absolutely awful to set up, I can’t see why anyone would prefer them to vee brakes.
Hydro discs are great in use, but if anything goes wrong you’ll be down the LBS, and they also add significant expense. The need for hoses has led to internal cabling which is also awful for gears and drives Di2 as a result. People do love the “feel” of hydro discs.
Monitoring wear on discs is much harder than with rims, and changing pads also more of a PITA.
The kicker though, is what’s actually available. You’ll struggle to get a nice road bike without hydro discs (and probably Di2 as well…). You definitely won’t get a decent rim braked MTB at all unless 2nd hand. All this adds cost; the ubiquity of hydro discs has IME put a significant cost barrier to getting into cycling.