What is the problem of hundreds or even thousands of migrations?? Point of writing migration files is that you can start from empty database, execute every migration file and end up with the current production database structure. So If you have 1000 migration files, writing 1000 and first is easy for the current sprint, you have state X and just need to write a diff for the next state.

And I have written many migrations in systems with tens of different db`s/schemas in the same project which could be different based on the geographical region or some other requirement and never had any big problem

I understand what migrations are. I just disagree that they are trivial.

Especially since you need to write the rollback script as well. And then test it with data. And then ensure there are no weird interactions with other migrations. Because if there is a problem with any one of them then it can be a huge headache to unwind everything.

Most of the migrations do not involve fiddling with data (for example adding a column, table). But if they do, yes you will spend more time on them. But it will be on the order of magnitude better then doing the same thing in MongoDB where you just have implicit schema

I have worked on tens of projects in the past decade, and I have very rarely seen migrations be anything other than automatic, taking one command to be generated.

For basic schema changes maybe.

But often you are adding to reference data or running a transform SQL in order to produce some new denormalised table or the huge array of changes needed to support new use cases. These can't be automated and are hugely error prone.

If your Schema is properly setup after the initial changes to it, then it's not error prone at all, it's doing what it's supposed to do.

Unless you migration involves "disable all checks, add new columns/tables, move and delete old data, enable all checks"

If you are migrating to a denormalised table then it should obviously crash when your conversion tool is doing something wrong and the RDBMS just prevented it from succeeding in doing something wrong.

If your migration is actually error prone as in "mangling and corrupting" data I suggest you are doing something wrong and you probably disabled the safety checks in your RDBMS.

How do schemaless databases make this easier?