Storing extra information in alembic_version table

[darraghenright]

Hi there 👋

I am guessing this is a topic that might have come up before, if so I am mostly curious about what the general sentiment is, more than this being an actual suggestion!

The alembic_version table records each revision reference that was run. I have a requirement to record some supporting information including:

1. The inserted_at datetime to show when the migration was run in the database.
2. The message, if it was provided, when the migration was created with alembic revision -m hello.

The use case here is to allow the ability for someone to view the table and get an idea of what happened and when. This can be useful for people who don't have access to the migrations files.

I am new to Alembic and I was curious to know if it was possible to configure the library to achieve either of these requirements. I dug into the code and found where the table is created which confirms that it's not possible to do so.

Implementing the inserted_at datetime can be easily implemented outside of the scope of the application of course, by using a trigger.

The second requirement, not so much. I guess this would assume that the information is retrievable from the migration file itself, which is technically possible by adding message = ${repr(message)} or just using the multiline comment at the top of the file. Of course, there's no guarantee that these are usable because the template can be customised.

Anyway, just curious about this and figured it'd be worth discussing. Thanks! 😃

[zzzeek]

yes there's a long-standing issue for this at #309. however your suggestion to also include the message is a handy one.

as mentioned in that issue someone has implemented https://github.com/jpassaro/Audit-Alembic but I haven't evaluated this at all.

[darraghenright]

Hi @zzzeek

Thanks for the quick response! Ah, that's very interesting about Audit-Alembic.

I had a quick look at it this morning, unfortunately it appears somewhat out of date. It's looking for an Alembic callback named on_version_apply. The last commit to the library was 5 years ago so I guess this refers to outdated and non-existent Alembic functionality?

But the approach, as far as I can make it out, is interesting — I believe it creates a new record for every migration run, recording the current revision, previous revision, the direction of the migration and a datetime of the migration event. This is a bit different to what I was looking for originally, it would not be straightforward to store a message since an audit event could represent a series of migrations. But if the library worked, it would certainly meet the first requirement of storing when the migration event happened.

I also realised I made an incorrect assumption about how alembic_version itself works, I realised this morning that it only ever stores the current revision hash. So my suggestion about adding columns is a bit nonsensical, at least if wanting to build a historical record. That said, showing the datetime and associated message, if any, for that record would still has value for me at least.

But Audit-Alembic definitely has me thinking about the value of a related table — I guess it'd just be a question of hooking into each step and inserting a record, if that's something that Alembic makes possible? Being new to this library, I am not sure if Alembic exposes functionality to implement that. I read about hooks but these appear to be for when a migration is created only, not when it's run.

[darraghenright]

I originally posted a question about this on StackOverflow, and I just got an interesting answer that suggests a simple workaround:

1. Create a history table with columns for migration event datetime and revision hash.
2. When upgrading/downgrading, execute a query to insert a record into the table.

Obviously you could extend this by modifying the provided .mako template to do the following:

1. Add a message field to capture the message provided when creating the migration.
2. Add a message column to the history table.
3. Create helper methods like record_upgrade_history()/record_downgrade_history() or similar to insert a relevant history record.
4. Add calls to these functions to the end of the upgrade/downgrade functions.

Obviously a careless developer could delete these calls. There's probably a nicer way to do this, add a decorator or whatever.

In a pinch, this should work well enough.

[EtienneDG]

Hi (again...coming from SO :) ),

I kept looking into this and using the mako file to update an history has indeed a major flaw : "Obviously a careless developer could delete these calls".

Decorator sounds nice, but you can use the context.configure function by using on_version_apply keyword argument to define a function in which you will INSERT/DELETE history.

[zzzeek]

I wouldnt put these things into the mako file. In your actual application I would be editing the env.py file that's local to your environment once created. The linked audit-alembic project uses the on_version_apply hook that we added for this purpose.

I would recommend reading the source to the https://github.com/jpassaro/Audit-Alembic package to see the general technique, then you can customize the approach to what you want to store.

[darraghenright]

Ahhhh.... Okay!

I was completely waylaid by the Audit-Alembic docs, which does this:

if not audit_alembic.alembic_supports_callback():
    raise audit_alembic.exc.AuditSetupError(
        'This Alembic version does not have on_version_apply')
auditor = audit_alembic.Auditor.create(version)

So as noted in my first response I assumed that the on_version_apply callback was not available, surmising that it was removed in the five years since the last commit to Audit-Alembic.

I looked into the alembic_supports_callback() function a bit but I didn't think to double check the Alembic docs themselves. So, massive facepalm for me.

So yeah, on_version_apply is what I wanted to use in the first place, and it's actually available. Game changer!

Thanks folks and excellent stuff @EtienneDG — I owe you one.