SQLAlchemy Columns¶

In some cases you may need more control over the columns generated by SQLModel, this can be done by using the sa_column, sa_column_args, and sa_column_kwargs arguments when creating the Field object.

There are many use cases for this, but ones where this is particularity useful is when you want more advanced defaults for values than what is easy to implement with Pydantic, such created_at or update_at timestamps for rows.

Columns for Timestamps¶

Two ways of implementing created_at timestamps with Pydantic are default factories and validators, however there's no straightforward way to have an update_at timestamp.

The SQLAlchemy docs describe how created_at timestamps can be automatically set with either default or server-default functions, by using sa_column=Column(...) as described in the SQLAlchemy documentation we can achieve the same behaviour:

Python 3.8+

# Code above omitted 👆

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

    registered_at: datetime = Field(
        sa_column=Column(DateTime(timezone=False), server_default=func.now())
    )

    updated_at: Optional[datetime] = Field(
        sa_column=Column(DateTime(timezone=False), onupdate=func.now())
    )

# Code below omitted 👇

👀 Full file preview

Python 3.8+

from datetime import datetime
from time import sleep
from typing import Optional

from sqlalchemy import Column, DateTime, func
from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

    registered_at: datetime = Field(
        sa_column=Column(DateTime(timezone=False), server_default=func.now())
    )

    updated_at: Optional[datetime] = Field(
        sa_column=Column(DateTime(timezone=False), onupdate=func.now())
    )


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    session = Session(engine)

    session.add(hero_1)
    session.add(hero_2)
    session.add(hero_3)

    session.commit()

    session.close()


def update_hero_age(new_secret_name):
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Spider-Boy")
        results = session.exec(statement)
        hero = results.one()
        print("Hero:", hero)

        hero.secret_name = new_secret_name
        session.add(hero)
        session.commit()
        session.refresh(hero)
        print("Updated hero:", hero)


def main():
    create_db_and_tables()
    create_heroes()
    sleep(1)
    update_hero_age("Arachnid-Lad")
    sleep(1)
    update_hero_age("The Wallclimber")


if __name__ == "__main__":
    main()

Above we are saying that the registered_at column should have a server_default value of func.now() (see full code for imports), which means that if there is no provided value then the current time will be the recorded value for that row.

As there is a value there now, then it will not be changed automatically in the future.

The updated_at column has an onupdate value of func.now(), this means that each time an UPDATE is performed, the function will be executed, meaning that the timestamp changes whenever a change is made to the row.

Warning

The difference between client-side python functions, server-side ddl expressions, and server-side implicit defaults is important in some situations but too in-depth to go into here. Check the SQL and SQLAlchemy docs for more information.

Pydantic Implementation¶

Implementing these timestamps on the DB side with SQLAlchemy works very well as the database itself is what will create and update the fields whenever a relevant database interaction occurs.

It's possible to achieve similar behaviour with Pydantic, for the created_at timestamp by using a Pydantic Field with a default_factory:

from datetime import datetime

from pydantic import BaseModel, Field


class Model(BaseModel):
    created_at: datetime = Field(default_factory=datetime.utcnow)


m1 = Model()
m2 = Model()
print(f'{m1.created_at} != {m2.created_at}')
#> 2022-05-19 10:49:22.053624 != 2022-05-19 10:49:22.053641

Another approach is to use a Pydantic validator:

from datetime import datetime

from pydantic import BaseModel, validator

class Model(BaseModel):
    created_at: datetime = None

    @validator('ts', pre=True, always=True)
    def set_created_at_now(cls, v):
        return v or datetime.now()

Both of these approaches come with the major caveat that default fields are set during the Pydantic model instantiation, as opposed to during interactions with the database, instead of the SQLModel approach which sets it with server_default which means that the timestamp will be exactly when the row is created in the database.

The real issue starts when looking at the updated_at timestamp - SQLAlchemy has the onupdate default which runs a function when the row is updated in the database, but there is no easy way to do this in Pydantic as it has no concept of 'about to be saved'.

So the pure Pydantic approach would require some additional logic to always change the updated_at timestamp before doing a write to the database, which adds some more complexity to the code and does not have benefits over the SQLAlchemy approach.