How should we manage datetime fields in SQLModel in python?

Question

Let's say I want to create an API with a Hero SQLModel, below are minimum viable codes illustrating this:

from typing import Optional
from sqlmodel import Field, Relationship, SQLModel
from datetime import datetime
from sqlalchemy import Column, TIMESTAMP, text

class HeroBase(SQLModel): # essential fields
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)
    created_datetime: datetime = Field(sa_column=Column(TIMESTAMP(timezone=True),
                        nullable=False, server_default=text("now()")))
    updated_datetime: datetime = Field(sa_column=Column(TIMESTAMP(timezone=True),
                        nullable=False, server_onupdate=text("now()")))

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")


class Hero(HeroBase, table=True): # essential fields + uniq identifier + relationships
    id: Optional[int] = Field(default=None, primary_key=True)

    team: Optional["Team"] = Relationship(back_populates="heroes")


class HeroRead(HeroBase): # uniq identifier
    id: int


class HeroCreate(HeroBase): # same and Base
    pass


class HeroUpdate(SQLModel): # all essential fields without datetimes
    name: Optional[str] = None
    secret_name: Optional[str] = None
    age: Optional[int] = None
    team_id: Optional[int] = None


class HeroReadWithTeam(HeroRead):
    team: Optional["TeamRead"] = None

My question is, how should the SQLModel for HeroUpdate be like?

1. Does it include the create_datetime and update_datetime fields?
2. How do I delegate the responsibility of creating these fields to the database instead of using the app to do so?

Answer 1

1. Does [the HeroUpdate model] include the create_datetime and update_datetime fields?

Well, you tell me! Should the API endpoint for updating an entry in the hero table be able to change the value in the create_datetime and update_datetime columns? I would say, obviously not.

Fields like that serve as metadata about entries in the DB and are typically only ever written to by the DB. It is strange enough that you include them in the model for creating new entries in the table. Why would you let the API set the value of when an entry in the DB was created/updated?

One could even argue that those fields should not be visible to "the outside" at all. But I suppose you could include them in HeroRead for example, if you wanted to present that metadata to the consumers of the API.

---

2. How do I delegate the responsibility of creating [the create_datetime and update_datetime] fields to the database instead of using the app to do so?

You already have delegated it. You (correctly) defined a server_default and server_onupdate values for the Column instances that represent those fields. That means the DBMS will set their values accordingly, unless they are passed explicitly in a SQL statement.

---

What I would suggest is the following re-arrangement of your models:

from datetime import datetime
from typing import Optional

from sqlmodel import Column, Field, SQLModel, TIMESTAMP, text


class HeroBase(SQLModel):
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)


class Hero(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    created_datetime: Optional[datetime] = Field(sa_column=Column(
        TIMESTAMP(timezone=True),
        nullable=False,
        server_default=text("CURRENT_TIMESTAMP"),
    ))
    updated_datetime: Optional[datetime] = Field(sa_column=Column(
        TIMESTAMP(timezone=True),
        nullable=False,
        server_default=text("CURRENT_TIMESTAMP"),
        server_onupdate=text("CURRENT_TIMESTAMP"),
    ))


class HeroRead(HeroBase):
    id: int


class HeroCreate(HeroBase):
    pass


class HeroUpdate(SQLModel):
    name: Optional[str] = None
    secret_name: Optional[str] = None
    age: Optional[int] = None

(I use CURRENT_TIMESTAMP to test with SQLite.)

Demo:

from sqlmodel import Session, create_engine, select

# Initialize database & session:
engine = create_engine("sqlite:///", echo=True)
SQLModel.metadata.create_all(engine)
session = Session(engine)

# Create:
hero_create = HeroCreate(name="foo", secret_name="bar")
session.add(Hero.from_orm(hero_create))
session.commit()

# Query (SELECT):
statement = select(Hero).filter(Hero.name == "foo")
hero = session.execute(statement).scalar()

# Read (Response):
hero_read = HeroRead.from_orm(hero)
print(hero_read.json(indent=4))

# Update (comprehensive as in the docs, although we change only one field):
hero_update = HeroUpdate(secret_name="baz")
hero_update_data = hero_update.dict(exclude_unset=True)
for key, value in hero_update_data.items():
    setattr(hero, key, value)
session.add(hero)
session.commit()

# Read again:
hero_read = HeroRead.from_orm(hero)
print(hero_read.json(indent=4))

Here is what the CREATE statement looks like:

CREATE TABLE hero (
    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
    updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
    name VARCHAR NOT NULL, 
    secret_name VARCHAR NOT NULL, 
    age INTEGER, 
    id INTEGER NOT NULL, 
    PRIMARY KEY (id)
)

Here is the output of the the two HeroRead instances:

{
    "name": "foo",
    "secret_name": "bar",
    "age": null,
    "id": 1
}

{
    "name": "foo",
    "secret_name": "baz",
    "age": null,
    "id": 1
}

I did not include the timestamp columns in the read model, but SQLite does not honor ON UPDATE anyway.