Use an ORM because tools like this: https://marshmallow.readthedocs.io/en/latest/ work really well with it.
And tools for migrations: https://sqlalchemy-migrate.readthedocs.io/en/latest/ start to make sense in your code.
You don't need an ORM to do either of those things, but it makes things a hell of a lot more organized. Makes the database easy to understand from your code. Makes things like "testing from a blank database" really nice.
You don't ever _need_ an ORM, but for 90% of projects you'll use Flask and a Database for, it makes your life a lot nicer.
No, it's a matter of philosophy. The newbie who has learned to code without ever being taught SQL, and the employee of an "agile" app sweatshop who doesn't care about the quality of his code or data, may prefer ORM for its' quick-and-dirtiness, or may use it by default because he knows no better way. I'm not saying that's wrong, but, there are other philosophies. There is the whole "craftsmanship" idea out there, that we should actually care about making things well. Jobs and Wozniak for example supposedly were adamant that the insides of Apple computers were designed well. If you care about your database and want to use special features like triggers, procedures, recursive queries in Postgres, etc., then ORM is about as helpful as clamshell packaging.
> And what happens when those tools fail?
I haven't used any of these tools that don't let me fall back on raw SQL or writing my own migration in SQL and including it in my code (outside of the DB). So, I haven't had this problem, honestly! Most DB problems I've had are related to _design_. There is an argument there to be had: "Do ORMs encourage bad database design?" but it's a different topic, in my opinion.
> There is the whole "craftsmanship" idea out there, that we should actually care about making things well.
Making a website is a completely different process than making a chair, or remodeling a kitchen, or designing hardware for a computer.
I absolutely make technology that is great, and I do take pride in my work, but if I didn't use an ORM it'd take me much longer to make: and the application would be much less maintainable.
> If you care about your database and want to use special features like triggers, procedures, recursive queries in Postgres, etc., then ORM is about as helpful as clamshell packaging.
I use all these things all the time. I've created migrations in Django to create my Postgres FTS trigger function. Now all these raw-SQL migrations are stored very nicely in their own space, and in version control. And it's just like I typed them into the SQL prompt: but I can just look in my codebase and see what I did :)
You can'd even do sub-selects nicely in Django ORM, forget recursive queries. But you can use raw SQL when you need it!
In fact I'm coming short on a value equation where it'd be more secure / efficient / readable without at least a query builder.
> but ORM means giving up a lot of control
What control is being lost, specifically?
> (and transparency, and understanding)
Highly recommend Django in Depth by James Bennett at PyCon 2015. https://www.youtube.com/watch?v=tkwZ1jG3XgA
Also, try out printing the .query of the QuerySet:
print(MyUser.objects.all().query)
SELECT "core_myuser"."id", "core_myuser"."password", "core_myuser"."last_login", "core_myuser"."is_superuser", "core_myuser"."username", "core_myuser"."email", "core_myuser"."is_active", "core_myuser"."is_admin" FROM "core_myuser"
And Q(), https://docs.djangoproject.com/en/1.11/topics/db/queries/#co..., .extra(), https://docs.djangoproject.com/en/1.11/ref/models/querysets/..., and finally raw SQL queries https://docs.djangoproject.com/en/1.11/topics/db/sql/.
> of your database in exchange for a few shortcuts regarding things like M:M links.
Maybe a few other benefits: helps with readability, consistent with OOP in python. Works across SQL dialects. Prevents mistakes, since those relationships are common. Getting clear and consistent access to common data retrieval and updating.