Alembic — store extra information in `alembic_version` table

Question

Is it possible to configure or customise Alembic to store a datetime in the database to record when each migration was run?

The alembic_version table has a single version_num column, which stores the unique revision identifier of each migration that was run; e.g:

version_num
------------
525d62b9a5fe
23c511df834d
03931f038a4d

I would like to also record when each migration ran in another column; e.g:

version_num   inserted_at
----------------------------------
525d62b9a5fe  2022-08-05 10:22:30
23c511df834d  2022-08-02 15:01:09
03931f038a4d  2022-07-30 09:02:59

This functionality could be implemented with a trigger, but I am curious to know if Alembic can be configured, extended or customised to the same effect.

I'd also like to be able to record the message string that can be included when generating a migration with alembic revision -m "create user table". This is an ideal however, not a deal breaker.

Answer 1

You can do this using the env.py and/or the script.py.mako files (see the UPDATE below).

Note : I always had only one row in my alembic_version table. The following solution will be based on this asumption and will create an history table instead of updating the alembic_version table. The mako file can be easily adapted in other cases.

1. Create the history table

CREATE TABLE alembic_version_history (
    version_num VARCHAR(32),
    inserted_at TIMESTAMP,
    message TEXT
)

2. Update the alembic/env.py to prevent auto generated migrations to delete the newly created table

# add this function
def include_name(name, type_, parent_names):
    if type_ == "table":
        if name == "alembic_version_history":
            return False
    return True

[...]

# Use it in BOTH run_migrations_offline and run_migration_online functions as follows
context.configure([...], include_name=include_name)

3. Update the code in the script.py.mako file, specifically the upgrade function :

[...]
def upgrade():
    ${upgrades if upgrades else "pass"}
    op.execute("INSERT INTO alembic_version_history (version_num, inserted_at, message) VALUES ('${up_revision}' ,NOW(), '${message}')")

Generate a migration alembic revision [--autogenerate] -m "test message". Then alembic upgrade head to update alembic_version table and add a new row to alembic_version_history.

UPDATE :

Adding operations to the mako file means that anyone can edit/update/delete those. To avoid this issue, we can again use the env.py:

def update_history(ctx, step, heads, run_args):
    revision_id = step.up_revision_id
    if step.is_upgrade:
        message = step.up_revision.doc
        ctx.connection.execute(
            f"INSERT INTO alembic_version_history (version_num, inserted_at, message) VALUES ('{revision_id}' ,NOW(), '{message}')"
        )
        print("INSERT NEW ROW IN HISTORY")
    else:
        ctx.connection.execute(f"DELETE FROM alembic_version_history where version_num = '{revision_id}'")
        print("DELETE LAST ROW IN HISTORY")

configure.context([...], include_name=include_name, on_version_apply=update_history)