Hi there everyone,
I am kind of looking for a "best practice" on how to implement
automatically setting and updating columns for created and modified
timestamps in SQLAlchemy, preferrably database-agnostic.
First of all, is DateTime the appropriate column type or should it be
timestamp instead? Both render to datetime on the Python side, so the
arguments of generic SQL discussions on that topic aren't so relevant here.
Now for the automatic updating, I have two variants:
1)
created = Column(DateTime, nullable=False, server_default=func.now())
modified = Column(DateTime, nullable=False, server_default=func.now(), server_onupdate=func.now())
Which only work if the database supports an ON UPDATE statement, which
e.g. sqlite doesn't seem to.
2)
created = Column(DateTime, nullable=False, server_default=func.now())
modified = Column(DateTime, nullable=False, server_default=func.now(), onupdate=func.now())
Which would account for that, or are there databases that don't even
support a DEFAULT value?
But the second solution isn't really aesthetic - since the modified
timestamp will now always be updated by SQLAlchemy.
Isn't there a way to make SQLAlchemy decide whether to omit data for
modified or not based on the actual database dialect used?
Hope my questions came out clear and maybe someone can help me!
Cheers,
Moritz