Sqlx: [cli] `sqlx migrate add` should support generating a matched set of up/down migrations

[mehcode]

$ sqlx migrate add -?

by default, we just generate <timestamp>_<name>.sql but with the option set we should generate <timestamp>_<name>.up.sql and <timestamp>_<name>.down.sql

no idea on a good option name

[JesperAxelsson]

I have a few thoughts and comments about this. I hope you don't mind me sharing them here.

Rather then having two different schemes for "up" migrations perhaps we could settle for either <timestamp>_<name>.sql or <timestamp>_<name>.up.sql and make <timestamp>_<name>.down.sql optional.

Something else I have been thinking about is how to store migration settings. I think it's quite rare that you only want to create down migrations for some of you up migrations rather you probably always want only up migration or always up and down migrations. By storing this in a setting file or similair it would be easier for a project to enforce. This could come later though.

Perhaps a strict mode would be nice to add later as well, that enforces that all up migrations also has down migrations.

[mehcode]

I have a few thoughts and comments about this. I hope you don't mind me sharing them here.

Please do.

Perhaps a strict mode would be nice to add later as well, that enforces that all up migrations also has down migrations.

This is why I suggest two different schemes. I see this as a clean separation. Migrations fall into two general buckets anyway. There are schema migrations that normally can be reversed (depending on the database) and there are data migrations that generally are destructive of the preceding data (or it would be non-trivial to revert).

A _.up.sql file must have an associated _.down.sql file. And we can easily error if someone does sqlx migrate revert (or whatever we call the command) and the most recent migration is not a _reversible_ migration.

• Migration - sqlx migrate add NAME
• Reversible Migration - sqlx migrate add -r NAME

With that we can have a

[migrate]
reversible = true

section in sqlx.toml that could enforce that flag

[vladinator1000]

I think it would be nice if we encouraged reversible migrations by default, it would be easy to error on a non-reversible migration but I can see how people will paint themselves into a corner.

How about
sqlx migrate add NAME (reversible)
sqlx migrate add -n NAME (non-reversible)

This would put some weight on making non-reversible migrations by letting the user make a conscious choice, kinda like Rust makes us .unwrap() to explicitly panic.

Also, hi y'all, I just found this project, it looks super cool! I'd love to contribute 🎉

[abonander]

It was a conscious decision to not require down-migrations, namely that they're not really useful in modern containerized architecture and it's annoying to be forced to write them.

If you want to enforce reversible migrations by default, sqlx migrate add could default to the value of migrate.reversible in sqlx.toml like @mehcode proposed.

[vladinator1000]

For reference, this is in progress in #649