Yeah, this is one of those issues that I feel separates the seniors from the, uh, less experienced seniors. (Let's be real, as a junior, you know jackshit about this.)

Knowing when to use an ORM, when to use SQL vs. NoSQL, all of that is stuff you basically only learn through experience. And experience means building multiple larger applications with different database technologies, bringing them into production and seeing them evolve over time.

It takes multiple years to do that for one application, so you need a decade or more experience to be able to have somewhat of an opinion.
And of course, it is all too easy to never explore outside of your pond, to always have similar problems to solve, where an SQL database does the job well enough, so a decade of experience is not a guarantee of anything either...