All these things fall short the moment you need real "production" features, such as reliable migrations (writing them by hand? no thanks. Outsourcing to another library like knex? no thanks), transactions, community support, relationship/nested/join queries without a ton of boilerplate and being battle tested.

So far, the best thing I've found in the node ecosystem is Prisma [1], and it's better than the alternatives by a very long shot in my opinion.

[1] https://www.prisma.io/docs/understand-prisma/why-prisma

I actually much prefer writing migrations by hand. It's actually pretty simple, and it gives you complete control over the schema.

Agree, it's pretty simple writing them. The problem is deciding which ones to apply for a given version of your application, when, running them automatically on each of your developer's laptops, your testing environment, and automate and keep track of all the changes.

Or are we talking of just writing a "CREATE TABLE..." and hand it over the wall to the ops? I agree that's simpler. Not something I like to do these days.

2. have a unique, incremental migration ID in each migration filename

3. apply them one by one in order depending on the current schema_version from the target environment, and update the schema_version accordingly

4. rollback the transaction in case of error, otherwise commit and enjoy your updated schema

I used golang-migrate/migrate in production for the past 3 years to do exactly this but you can easily implement it yourself too. golang-migrate/migrate can be used either as a Go library or as a CLI tool.

https://github.com/golang-migrate/migrate

Or did I misunderstand the issues you're talking about?