SQLAlchemy date field not updating with server_onupdate

Question

Using SQLAlchemy with flask_sqlalchemy and alembic for PostgreSQL. I have the following definition for a field:

date_updated = db.Column(db.DateTime, server_default=db.func.now(), server_onupdate=db.func.now())

However the field never updates when the record is modified. It is set on create and never updates. This is what is generated by alembic to create the table:

sa.Column('date_updated', sa.DateTime(), server_default=sa.text('now()'), nullable=True),

So it's no wonder that it's not being updated, since the server_onupdate param doesn't seem to be getting past alembic.

I'm not sure of the right way to do this. The SQLAlchemy documentation is frustratingly complex and unclear where this is concerned.

Edit: From looking at how to do this in PostgreSQL directly, it looks like it requires the use of triggers. I would prefer to do it at the DB level rather than at the application level if possible, but I don't know if I can add a trigger through SQLAlchemy. I can add it directly at the DB but that could break when migrations are applied.

score -1 · Answer 1 · answered Mar 26 '21 at 16:29

The way you say "I'm not sure of the right way to do this", I'm not sure if you mean specifically updating the date on the server side, or just updating it in general.

If you just want to update it and it doesn't matter how, the cleanest way in my opinion is to use event listeners.

Here's an example using the normal sqlalchemy, it will probably be the same (or at least very similar) in flask-sqlalchemy:

from datetime import datetime
from sqlalchemy import event

@event.listens_for(YourModel, 'before_insert')
@event.listens_for(YourModel, 'before_update')
def date_insert(mapper, connection, target):
    target.date_updated = datetime.utcnow()

SQLAlchemy date field not updating with server_onupdate

1 Answers1