Is there any way to generate sequential Revision IDs in Alembic?

I made a script to autoincrement the revision number based off of how many migrations already exist maching the ####_ pattern. Here's a TLDR version. I you save this as migrations.sh and change the path in line 2

#!/usr/bin/env bash
NEXT_ID=`ls kennel/db/versions/* | grep -P '/\d{4}_.*\.py$' | wc -l`
alembic revision -m $@ --rev-id=`printf "%04d" ${NEXT_ID}`

Then you can use it like:

./migrations.sh migration_name
# or 
./migrations.sh migration_name --autogenerate

The full script has documentation and uses defaults to --autogenerate which can be disabled using an --empty flag. https://gist.github.com/chriscauley/cf0b038d055076a2a30de43526d4150e

I found how to do it in my case without additional bash scripts, just some mutation magic in env.py. Maybe it will help somebody.

Alembic has a powerful feature with customizing generated revisions so we can write override at this level:

# env.py
def process_revision_directives(context, revision, directives):
    # extract Migration
    migration_script = directives[0]
    # extract current head revision
    head_revision = ScriptDirectory.from_config(context.config).get_current_head()
    
    if head_revision is None:
        # edge case with first migration
        new_rev_id = 1
    else:
        # default branch with incrementation
        last_rev_id = int(head_revision.lstrip('0'))
        new_rev_id = last_rev_id + 1
    # fill zeros up to 4 digits: 1 -> 0001
    migration_script.rev_id = '{0:04}'.format(new_rev_id)

...
# then use it context.configure
context.configure(
  ...
  process_revision_directives=process_revision_directives,
)

If you also want to use it for revisions created without --autogenerate you should set revision_environment to true in alembic.ini

By the sounds of it, you are more interested in sequentially listed revision files rather than sequentially ordered revision ids. The former can be achieved without any change to how the revision ids are generated.

The alembic.ini file that is generated when you run alembic init alembic has a section that configures the naming of the revision files:

# template used to generate migration files
# file_template = %%(rev)s_%%(slug)s

And here is the explanation from the docs:

file_template - this is the naming scheme used to generate new migration files. The value present is the default, so is commented out. Tokens available include:

• %%(rev)s - revision id
• %%(slug)s - a truncated string derived from the revision message
• %%(year)d, %%(month).2d, %%(day).2d, %%(hour).2d, %%(minute).2d, %%(second).2d - components of the create date, by default datetime.datetime.now() unless the timezone configuration option is also used.

So adding file_template = %%(year)d-%%(month).2d-%%(day).2d_%%(rev)s_%%(slug)s to alembic.ini would name your revision like 2018-11-15_xxxxxxxxxxxx_adding_a_column.py.

I found this issue: https://bitbucket.org/zzzeek/alembic/issues/371/add-unixtime-stamp-to-start-of-versions which pointed me in the right direction.

A comment from from that issue:

timestamps don't necessarily tell you which file is the most "recent", since branching is allowed. "alembic history" is meant to be the best source of truth on this.

So, the file naming solution will not guarantee that migrations are ordered logically in the directory (but will help IMO). The same argument could be made against having sequential ids.

If you do want to specify your own revision identifier, use the --rev-id flag on the command line.

E.g.:

alembic revision -m 'a message' --rev-id=1

Generated a file called 1_a_message.py:

"""a message

Revision ID: 1
Revises:
Create Date: 2018-11-15 13:40:31.228888

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '1'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    pass


def downgrade():
    pass

So you can definitely manage the revision identifiers yourself. It would be trivial to write a bash script to trigger your revision generation, automatically passing a datetime based rev_id, e.g. --rev-id=<current datetime> to govern order listed in the directory.

If the revision id isn't specified, the function rev_id() found at alembic.util.langhelpers is called:

def rev_id():
    return uuid.uuid4().hex[-12:]

Function calls to rev_id() are hard-coded in the alembic source, so short of monkey-patching the function, it will be difficult to override the behavior. You could create a fork of the library and redefine that function or make the function that it calls for id generation configurable.

while i don't need migration branching i use this

@writer.rewrites(ops.MigrationScript)
def revid_increment(ctx: migration.MigrationContext, revisions: tuple, op: ops.MigrationScript):
    op.rev_id = '{0:04}'.format(len(tuple(ctx.script.walk_revisions())) + 1)
    return op

it makes it easy to replace the current rev_id naming scheme, add timestamp, date, whatever ...