How to Make Auto-Updating Timestamp Fields in SQLModel

It is very common in application and database design to keep fields on a table for when a record was created and last updated. These fields are common enough that many large frameworks like Laravel handle them automatically.

SQLModel is a relatively new Python library that combines the data validation of Pydantic with the ORM capabilities of SQLAlchemy. It is new enough, in fact, that there are no great resources for implementing auto-updating created_at and updated_at fields.

Created At Field

First, let’s start simple. Let’s make a created_at column that automatically sets its value to the current date and time when a record is inserted.

created_at: Optional[datetime] = Field(
        default_factory=datetime.utcnow
    )

This is not too difficult to understand. Simply pass in python’s datetime.utcnow function handle to the default_factory parameter, and the created_at field will automatically be set. Now, let’s turn our attention to updated_at.

Updated At Field

updated_at needs all the functionality of created_at, plus its value needs to update whenever the record is updated.

updated_at: Optional[datetime] = Field(
        default_factory=datetime.utcnow,
        sa_column_kwargs={"onupdate": datetime.utcnow},
    )

As you can see, updated_at is almost exactly identical to created_at, with one addition. We pass a dictionary called sa_column_args, with a value onupdate equal to the datetime.utcnow function handle.

This is a little odd looking, no? Well, in order to access the onupdate parameter, we actually need to dig underneath the abstraction provided by the SQLModel library to get at some of the SQLAlchemy features that SQLModel does not have an interface for.

Under the hood, SQLModel creates an instance of a SQLAlchemy Column. sa_column_kwargs stands for “SQLAlchemy Column Keyword Arguments”, and it passes that dictionary to the constructor of the Column, allowing us to utilize the onupdate feature to set the value to the time the update occurs.

Conclusion

So, now you see how to create auto-updating datetime columns to track when record creations and updates occurred using SQLModel. Take note, the solution of using sa_column_kwargs to access the underlying SQLAlchemy implementation can be used all over SQLModel. Click here for another example. Hopefully this article helped you out, since when I needed to do this, I could find no resources explaining how it worked.

Note: This article was written when SQLModel was in a very early state (v. 0.0.16 at the time of writing). I certainly hope that in the future, a new and improved interface for accomplishing this very common task will be introduced.