Revision IDs as sequential numbers or dates

[henadzit]

Hi!

Thank you for the awesome project!

I'm wondering why alembic uses hex numbers as revision IDs. For instance, Django uses sequential numbers and Ruby on Rails uses date and time as part of a migration file name which makes it easy to understand the sequence of migrations without running alembic history just by looking at the folder. Thanks!

I wasn't able to find any previous discussion on this topic, please feel free to refer me to it.

[zzzeek]

because you then can't easily amend the order of operations or create branches. alembic's predecessor sqlalchemy-migrate used sequential numbers, and then in openstack migrations had to be numbered 1, 50, 100, 150, etc. because they had to add new migrations in between so often.

alembic assigns no meaning to its revision identifiers (just like a source control system) and they can be linked in any way using "down revision", "depends on", just like a source control system.

[awoimbee]

Hi, I think it would be nice if alembic's revisions worked a bit like UUIDv7: <date>-<id>.
In most cases, migrations would be ordered by date but nothing prevents 20241204-5c9b from depending on 20250101-86ac.

I think it would make happy both all the people asking for a sequential ID and people asking to add migrations in between.

Currently it's possible to edit the file_template, but I'm not a aware of an official way to customize rev.

---

I would propose the format <year><month><day>-<5 base64 chars>:

• It's only 2 chars more than the current rev format (14 vs 12 chars)
• 30 bits of randomness (5*6) -> with 48 migrations per day the probability of a collision is 0.00010505%
• edit: base64 is case-sensitive, the python stdlib also has methods for base32, which is not. 25bits of randomness is still good enough

---

EDIT: here you said how to manually set the rev-id, is there a way to have a custom rev-id generator instead ?

[CaselIT]

a cookbook entry could be created for customizing the revision id. It's possible using process_revision_directives

[zzzeek]

Hi, I think it would be nice if alembic's revisions worked a bit like UUIDv7: <date>-<id>. In most cases, migrations would be ordered by date but nothing prevents 20241204-5c9b from depending on 20250101-86ac.

I think it would make happy both all the people asking for a sequential ID and people asking to add migrations in between.

the discussion here was about making it easier to see from a file perspective what the order of migrations is, that's why file_template is there and is customizable with a datestamp. Also, the "revid" is always allowed to be passed on the command line explicitly using --revid to alembic revision as well as alembic merge for people who do want their own revision id scheme.

What exactly is the "add migrations in between" case? Does this mean someone wants to order their migration files without explicitly setting the "down revision" inside of each migration file? That's not possible since alembic always orders the files using the structure created by down revisions. changing how "revid" comes out would not change this nor make it any "easier" to "add new migrations in between". So while it's not a big deal to make it easier to customize the revid generator, I dont see what use case that solves.

if you are referring to your discussion at #1608 where a private company has written about a non-public solution they've created for changing alembic to work in such a way, for that approach to work they had to replace alembic's entire revision scheme to no longer apply significance to the "down migration". if they are already patching out a huge part of alembic like that, then they might as well patch out the revision scheme as well. that is, I maintain as I did over there, they've produced a major fork of alembic, which is great! they should publish it.

[awoimbee]

that's why file_template is there

Honestly IMO file_template is a mistake and customizing the rev-id itself makes more sense, because file-name == rev-id is more maintainable (less grep involved).

What exactly is the "add migrations in between" case?

You said it first "openstack migrations had to be numbered 1, 50, 100, 150, etc. because they had to add new migrations in between so often."

if you are referring to your discussion at #1608

Not really, I've been ordering migrations using file_template for a while (so 2y old stuff is not mixed with yesterdays migration), just file-name != rev-id is a small pain

[zzzeek]

What exactly is the "add migrations in between" case?

You said it first "openstack migrations had to be numbered 1, 50, 100, 150, etc. because they had to add new migrations in between so often."

that's not the question. Alembic does not use natural ordering to order migrations. it uses a revision / downrevision structure. changing how revision id is generated does not change that in any way, so I see no extra utility from using a different revision number scheme except for human readability.

if you are referring to your discussion at #1608

Not really, I've been ordering migrations using file_template for a while (so 2y old stuff is not mixed with yesterdays migration), just file-name != rev-id is a small pain

oh. OK I completely thought you were still on that issue here since when you refer to "placing numbers in between" that's exactly what you were talking about in #1608. alembic specifically rejects "placing numbers in between" as a means of merging branches in the same way that git doesn't do anything like that either.