I wrote loads of SQLs at the beginning of my career with focus on performance tuning and worked with ORMs much later - so it was the other way round for me. For some use cases, ORMs can indeed save a bunch of effort - if I have to do a straightforward join with a simple filter condition, it is orders of magnitude simpler to use the ORM. The caveat is to verify the SQL being executed (most frameworks provide features to log the SQL) to ensure that it is what is expected. However, if you are doing complex connection, direct SQL would be the best choice - it is deterministic and you can tune it perfectly for what you need.
In my experience ORMs are great for simple CRUD but terrible once you do anything analytical in nature. Even stuff like account summaries often go sideways and it's better to handcraft.
For the CRUD side it's worth it I think, but yeah definitely a balancing act between the two.
One approach we use is to put complicated stuff in views, then use the orm to perform simple queries against those. That's especially useful for complex relationships between tables.
We do this too. Also allows for making changes purely at SQL level without having to do code changes - as long as the select clause signature is the same, the application doesn't know any better. The downside is that you have to be careful not to a build an impenetrable nesting of its-views-all-the-way-down.
We have an in-house migration management system that allows us to update views pretty safely and reliably, even with nested dependencies. Basically, on every release, all of the views are dropped and recreated, so we don't have to worry as much about that. (But still be very careful with nesting your views)
As with most things, it is a balance.