Automatic created and modified timestamp columns (best practice?!)

[Moritz Schlarb]

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

[Michael Bayer]

On Mar 27, 2013, at 1:02 PM, Moritz Schlarb <mosc...@metalabs.de> wrote:

> 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.

DateTime should be the best choice here, as TIMESTAMP in some cases implies automatic behavior like on MySQL.

>
> 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?

MySQL might not even support server_default for datetimes here (I'd have to check). On SQLite, you'd be getting the server's datetime format that probably doesn't match the one we use for the sqlite.DateTime type (SQLite only stores dates as strings or integers).

The most foolproof system would be to use "default" and "onupdate" across the board, the "now()" function, since it is a SQL function, is rendered inline into the INSERT/UPDATE statement in any case so there's no performance hit.

> 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?

there's ways this could be achieved, namely that you can use events, or use callable functions for "default" and "onupdate", but IMHO this is unnecessarily awkward. There's no downside to "default=func.now()" except for worrying about people emitting INSERTs directly to the database, which isn't the typical case for a packaged application that's targeting arbitrary database backends.

[Jonathan Vanasco]

FWIW, I've found this sort of stuff to be better done with
CURRENT_TIMESTAMP than NOW() , or doing a "NOT NULL" constraint with
no-default and passing in a time from the application.


On databases that support it, CURRENT_TIMESTAMP is locked to the
transaction and/or statement while NOW() is evaluated in real-time.
When you're doing some sort of create&modified date, there's an
element of record keeping involved. By using NOW() instead of
CURRENT_TIMESTMAP , you lose the ability to correlate any of the
database updates in the same transaction/back together -- or to a
specific web/application request.

You could keep your pg logs for a long time and be willing to sort
through them, but using CURRENT_TIMESTAMP gives fewer unique times.
So so so much easier for auditing.

[Moritz Schlarb]

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 Schlarb

[Julien Cigar]

If you use the ORM part of SQLAlchemy then I would use a 'before_update'
event for that. It has the advantage is that the event can be propagated
(thanks to propagate=True), which can be really usefull if you use
inheritance. For example:

def update_updated_listener(mapper, connection, target):
target.updated = datetime.now()

event.listen(YourClass, 'before_update', update_updated_listener,
propagate=True)

I usually create a 'last_update' column on the mapped class, something like:

'last_update' : orm.column_property(
sql.func.coalesce(table['content'].c.updated,
table['content'].c.added)
)


--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.