Migrations should default for programatic application and allow for a callback API for up/down calls.

[seivan]

So migrations are generated or written in SQL which is inherently a good thing, but that poses a few issues.

1. It doesn't allow for shuffling data around with specific migrations.
2. You don't get to piggy-back on the transactional nature of the migration.
3. You have to synchronize the with up/down migrations manually which is most likely going to be error-prone.
4. Can't piggy-back on SHARE UPDATE EXCLUSIVE lock or whatever drizzle is currently using.

This is an issue if you want to launch multiple node and have the migrations run on boot and at the same time move data around in S3 or Redis.

My suggestions are that migrations should have a programmatic API:

• to run up/down to N where N defaults to latest for up.
• Allow for registering callbacks for up/down calls with specific identifiers so you can manage typescript code running inside the transaction alongside the specific migration in question.
• The lock should be on a database level as default (e.g SHARE UPDATE EXCLUSIVE) but of course allow op-tin for advisory locks.

For every SQL migration, it will run the associated up or down function registered for said callback. If the callback fails, the migration fails, and if the migration fails, the callback cancels or even runs down

Same for down calls.

Slightly related to #2832