how to auto generate created_at, updated_at, deleted_at... field with SQLModel

[mr-m0nst3r]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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

> I need some sample codes, thank you

Description

I want to have , let's say, three extra columns for created_time, updated_time, deleted_time, their value are set at different operations, just like the column name suggested.

I'm new to ORM, and SQLAlchemy seems support this function.

How to achieve this using SQLModel?

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

Python 3.9.9

Additional Context

No response

[njdowdy]

This is not really unique to SQLModel, as far as I know.

You can implement this in your database models using something like this:

from datetime import datetime
from sqlmodel import SQLModel, Field

class User(SQLModel, table=True):
    id: int = Field(primary_key=True, nullable=False)
    name: str = Field(default=None)
    created_at: datetime = Field(default=datetime.utcnow(), nullable=False)
    last_edited: datetime = Field(default_factory=datetime.utcnow, nullable=False)

The "created_at" and "last_edited" in this case will be instantiated when they are inserted into the database. You could also pass them again to overwrite. For example, if you change the "name" field, you can pass along with that, updated_user.last_edited.

*edited 2022-02-27 based on @byrman's suggestion. Using 'default_factory' instead of 'default' in the Field is good for dynamic fields (like 'last_edited') and you don't need to assign the value datatime.utcnow() when writing new data to the db. (see: https://pydantic-docs.helpmanual.io/usage/models/#field-with-dynamic-default-value).

[byrman]

You probably want to use a callable here, e.g. Field(default_factory=datetime.utcnow, nullable=False).

[mr-m0nst3r]

problem solved, thank you guys

[facundopadilla]

it's work for me

from datetime import datetime
from typing import Optional

from sqlalchemy import Column, DateTime, func
from sqlmodel import Field, SQLModel


class BaseTable(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    created_at: Optional[datetime] = Field(
        sa_column=Column(
            DateTime(timezone=True), server_default=func.now(), nullable=True
        )
    )
    updated_at: Optional[datetime] = Field(
        sa_column=Column(
            DateTime(timezone=True), onupdate=func.now(), nullable=True
        )
    )

[rjpaul2]

@facundopadilla I found adding default=None fixes an issue which required created_at and updated_at when using SQLModel model_validate . The optimal code is

from datetime import datetime
from typing import Optional

from sqlalchemy import Column, DateTime, func
from sqlmodel import Field, SQLModel


class BaseTable(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    created_at: Optional[datetime] = Field(
        default=None,
        sa_column=Column(
            DateTime(timezone=True), server_default=func.now(), nullable=True
        )
    )
    updated_at: Optional[datetime] = Field(
        default=None,
        sa_column=Column(
            DateTime(timezone=True), onupdate=func.now(), nullable=True
        )
    )

[atentoguy]

Hello @rjpaul2,
I'm trying to use this class as a base for multiple tables in the database and I'm getting the following error.
Have you ever been through this?

raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Column object 'created_at' already assigned to Table 'table_test'

class BaseTable(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    created_at: Optional[datetime] = Field(
        default=None,
        sa_column=Column(
            DateTime(timezone=True), server_default=func.now(), nullable=True
        )
    )
    updated_at: Optional[datetime] = Field(
        default=None,
        sa_column=Column(
            DateTime(timezone=True), onupdate=func.now(), nullable=True
        )
    )

class TableTest(BaseTable, table=True):
    __tablename__ = "table_test"

class TableTest2(BaseTable, table=True):
    __tablename__ = "table_test2"

[DaanRademaker]

I have the same issue as atentoguy! Not sure why this is happening either. Would really like this feature

[atentoguy]

I have the same issue as atentoguy! Not sure why this is happening either. Would really like this feature

I found the solution to this problem in #743.

class BaseTable(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)

    created_at: datetime = Field(
        sa_type=TIMESTAMP(timezone=True),
        sa_column_kwargs={"server_default": text("CURRENT_TIMESTAMP")},
        nullable=False
    )

    updated_at: Optional[datetime] = Field(
        default=None,
        sa_type=TIMESTAMP(timezone=True),
        sa_column_kwargs={"onupdate": func.now(), "nullable": True}
    )

Now it is possible to reuse this class in other tables.

[DaanRademaker]

Thanks for the reply. For further reference, this works as well which is how I ended up implementing it:

from datetime import datetime

import sqlalchemy as sa
from sqlmodel import Field, SQLModel

class SomeBaseModel(SQLModel):
    created_on: datetime | None = Field(
        default=None,
        sa_type=sa.DateTime(timezone=True),
        sa_column_kwargs={"server_default": sa.func.now()},
        nullable=False,
    )
    modified_on: datetime | None = Field(
        default=None,
        sa_type=sa.DateTime(timezone=True),
        sa_column_kwargs={"onupdate": sa.func.now(), "server_default": sa.func.now()},
    )

[opyate]

onupdate doesn't work, so this is what I have:

from datetime import datetime

import sqlalchemy as sa
from pydantic import model_validator
from sqlalchemy.sql import func
from sqlmodel import Field, SQLModel


class TemporalBase(SQLModel):
    created_at: datetime | None = Field(
        default=None,
        sa_type=sa.DateTime(timezone=True),
        sa_column_kwargs={"server_default": sa.func.now()},
        nullable=False,
    )
    updated_at: datetime | None = Field(
        default=None,
        sa_type=sa.DateTime(timezone=True),
        sa_column_kwargs={
            "server_default": sa.func.now(),
            "onupdate": sa.func.now(),  # onupdate doesn't work: https://github.com/sqlalchemy/sqlalchemy/issues/4652
        },
    )

    @model_validator(mode="after")
    @classmethod
    def update_updated_at(cls, obj: "TemporalBase") -> "TemporalBase":
        """Update updated_at field."""
        # must disable validation to avoid infinite loop
        obj.model_config["validate_assignment"] = False

        # update updated_at field
        obj.updated_at = datetime.now()

        # enable validation again
        obj.model_config["validate_assignment"] = True
        return obj

[Johnb6262]

I tried what @opyate suggested in the code above, but it doesn't work, yet. I don't know what's missing or how the "update_updated_at" method is supposed to be triggered, but it doesn't hit a breakpoint when I run in debug mode when I'm adding new Models to the database. An explanation of how it's supposed to work to replace or override "onupdate" might help.

Specifically, my "meta_updated_at" field IS populated at first write (I assume, based on what is in "server_default" but never updated after that (supported by never hitting a breakpoint in "update_updated_at"

Here is my code:

sa.Column("meta_updated_at", sa.DateTime(timezone=True), server_default=UtcNow(), onupdate=sa.func.now(), nullable=False)

@model_validator(mode="after")
    @classmethod
    def update_updated_at(cls, obj: "MetaBase") -> "MetaBase":
        """Update meta_updated_at field."""
        # must disable validation to avoid infinite loop
        obj.model_config["validate_assignment"] = False

        # update meta_updated_at field
        obj.meta_updated_at = datetime.utcnow()

        # re-enable validation again
        obj.model_config["validate_assignment"] = True
        return obj

[d33pjs]

Thanks for the reply. For further reference, this works as well which is how I ended up implementing it:

from datetime import datetime

import sqlalchemy as sa
from sqlmodel import Field, SQLModel

class SomeBaseModel(SQLModel):
    created_on: datetime | None = Field(
        default=None,
        sa_type=sa.DateTime(timezone=True),
        sa_column_kwargs={"server_default": sa.func.now()},
        nullable=False,
    )
    modified_on: datetime | None = Field(
        default=None,
        sa_type=sa.DateTime(timezone=True),
        sa_column_kwargs={"onupdate": sa.func.now(), "server_default": sa.func.now()},
    )

This is just working fine in my environment. Thanks.

[chenlb]

mysql onupdate doesn't work.

https://docs.sqlalchemy.org/en/20/dialects/mysql.html#rendering-on-update-current-timestamp-for-mysql-mariadb-s-explicit-defaults-for-timestamp

code like this, it is work.

# ...
    created_at: Optional[datetime] = Field(
        default=None,
        sa_column=Column(
            DateTime(timezone=True),
            server_default=text('CURRENT_TIMESTAMP'),
            nullable=False
        )
    )
    updated_at: Optional[datetime] = Field(
        default=None,
        sa_column=Column(
            DateTime(timezone=True),
            server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
        )
    )
# ...