[proposal] [cli] support numeric migration version prefixes instead of datetime

[francoposa]

Coming from other migration tools, I am most comfortable with having numeric migration prefixes like 000001, 000002, etc.

I would like to be able to create this directly from the cli, something like: sqlx migrate add -r --prefix numeric create_authn_user ---> migrations/000001_create_authn_user.sql sqlx migrate add -r --prefix numeric create_authn_login ---> migrations/000002_create_authn_login.sql

Currently I am just creating wrapper commands to rename the files after creation.

The MigrationSource already has no problem parsing these filenames into the i64 version numbers, correctly handling the order.

This should be a relatively minor change.

• [Design] determine name for prefix type flag
• [Design] Determine how many leading zeros to put in filename
• Add a flag to migrate add command for prefix type
• in migrate add fn, , for numeric migration types, get latest numeric version by checking last element on MigrationSource iterator, increment for new filename(s)

[mehcode]

I'd be fine with this. As for designing the CLI argument, can you point me to the migration tool that does do things like this?

[francoposa]

When I first proposed, I was not thinking of an existing tool that allows this through the command line, but a way to allow the command line to mirror Django's migration versioning: https://docs.djangoproject.com/en/3.1/topics/migrations/.

I don't see any that support different version prefix types without manual renaming.

Of the ones I have used:

Django - Uses 0-padded integers, but still makes you specify in a migrations which version(s) precede it. So clearly not relying just on filename sorting Alembic - uses random strings which aren't necessarily in a sorted order, still makes you specify a migration's previous version Goose - uses datetimes like sqlx currently does

I would probably propose --version-prefix-type or --prefix-type as the CLI argument name, defaulting to the existing behavior which could be called datetime, vs. numeric for the new option.

Ultimately, it's just a quality of life improvement when renaming still works (as with the other tools), so I can see an argument not to add it. But it's simple and compatible with what's already there.