I was against ORMs until I used EF Core in .NET which I really loved. A good ORM is amazing for productivity and when needed you can always write raw SQL.
I don't use .NET anymore but lately I've been happy with Drizzle for TS. It's very performant and expressive. After years it seems that they're finally going to release v1.0 soon.
Personally I would never go back to writing all my queries with SQL, manually mapping the results, etc.