Auto updating columns don't work with SQLModel docs on updates

[Ben-Epstein]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from sqlmodel import SQLModel, Field, Session, create_engine
import datetime

engine = create_engine(...)

class SimpleTable(SQLModel, table=True):
    version: int = Field(primary_key=True)
    updated_at: datetime.datetime = Field(default_factory=datetime.datetime.utcnow, nullable=False)


def update_version() -> None:
    """Update the version of the table, where updated_at should update automatically"""
    with Session(engine) as session:
	stmt = select(SimpleTable)
	tbl = session.exec(stmt).one()
	tbl.version += 1
	session.add(tbl)
	session.commit()

Description

With the code above, which

• updates data based on the docs recommendation
• creates the table with a default_factory as per the answer to this issue

The updated_at field should auto update, but it's not doing so. I think the way you'd need to do this is to create a new instance of SimpleTable, which will create a new updated_at value. But there are no docs on the right way to do that. So i suspect people are doing what I've done above and silently not getting the updated_at field to work.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.14

Python Version

Python 3.9.8

Additional Context

No response

[cfhowes]

I don't know the answer here at all, but I am wondering if

updated_at: datetime.datetime = Field(sa_column_kwargs={'server_default': func.now()}, nullable=False)

would work? I'm just starting to use SQLModel and also trying to figure out where/how to best specify these things.

[N-Demir]

I think the following works and is a pretty clean solution?

updated_at: datetime = Field(
        default_factory=lambda: datetime.now(timezone.utc),
        sa_column_kwargs={"onupdate": lambda: datetime.now(timezone.utc)},
    )

[YuriiMotov]

The following works:

    updated_at: datetime = Field(
        sa_column_kwargs={
            "server_default": func.now(),
            "onupdate": func.now(),
        },
        nullable=False,
    )

Runnable code example in the details:

import time
from datetime import datetime

from sqlmodel import Field, Session, SQLModel, create_engine, func


class SimpleTable(SQLModel, table=True):
    version: int = Field(primary_key=True)
    updated_at: datetime = Field(
        sa_column_kwargs={
            "server_default": func.now(),
            "onupdate": func.now(),
        },
        nullable=False,
    )


def main() -> None:
    engine = create_engine(
        "postgresql://user:mysecretpassword@localhost/some_db", echo=True
    )
    SQLModel.metadata.drop_all(engine)
    SQLModel.metadata.create_all(engine)

    with Session(engine) as session:
        session.add(SimpleTable(version=1))
        session.commit()

    with Session(engine) as session:
        o1 = session.get(SimpleTable, 1)
        updated_at = o1.updated_at

        time.sleep(0.1)

        o1.version = 2
        session.commit()
        session.refresh(o1)

        assert o1.updated_at > updated_at, "updated_at should be updated on commit"


if __name__ == "__main__":
    main()