How to create a time Field for a model like django DateTimeField

[regainOWO]

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

from datetime import datetime
from typing import Optional
from sqlmodel import Field, SQLModel, DateTime


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

    camera_ip: str
    camera_channel: int
    file_path: str
    file_name: str
    create_time: DateTime 
    update_time: DateTime

Description

when i create a Image_Save_Record instance and commit it to db, the create_time field value should be that system time current.

# django version
create_time = models.DateTimeField(auto_now_add=True)

when i change the Image_Save_Record instance which i created before and commit it to db, the update_time field value should be that system time current.

# django version
update_time = models.DateTimeField(auto_now=True)

Is there sqlmodel have option to do the same like this?

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.7.9

Additional Context

No response

[RobertRosca]

There are a few ways to do this, either via Pydantic or with SQLAlchemy.

SQLAlchemy is the way to go since implementing updated_at in it seems easier, but I'll leave the Pydantic approach here anyway since I wrote it first 😛

With Pydantic default factories or validators

With Pydantic you could use default factories:

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 would be validators:

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()

Having an automatic update time is a bit tougher... I think it should be possible with validators, but I can think of a few cases where the incorrect time can be set.

Doing this in SQLAlchemy is easy using server_default and on_update:

from sqlalchemy.sql import func

time_created = Column(DateTime(timezone=True), server_default=func.now())
time_updated = Column(DateTime(timezone=True), onupdate=func.now())

The above column definitions would mean that if time_created is None, then func.now() is the default, and if time_updated is changed, then its value will be overwritten with func.now().

Since you can directly use SQLAlchemy Columns in an SQLModel Field with the keyword argument sa_column, the above can just be copy/pasted into an SQLModel model:

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

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

   foo: Optional[str]

    created_at: Optional[datetime] = Field(
        sa_column=Column(DateTime(timezone=True), server_default=func.now())
    )

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

Gave it a quick test and it seems to work correctly, when an instance of Model is first added and committed created_at is set to the current time and updated_at is None, then if foo is changed and the change is saved, updated_at is set to the time the save was done and created_at remains unchanged.

[RobertRosca]

Here is my very rough example code:

import time
from datetime import datetime
from typing import Optional

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


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

    foo: Optional[str]

    created_at: Optional[datetime] = Field(
        sa_column=Column(DateTime(timezone=True), server_default=func.now())
    )

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


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

engine = create_engine(sqlite_url)


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


def create_entry():
    with Session(engine) as session:
        m = Model()

        print(f"created {m=}")

        session.add(m)
        session.commit()
        session.refresh(m)

        print(f"added and refreshed {m=}")


def update_entry():
    with Session(engine) as session:
        s = select(Model)
        m = session.exec(s).first()

        print(f"\nfound {m=}")

        m.foo = "bar"

        print(f"modified {m=}")

        session.add(m)
        session.commit()
        session.refresh(m)

        print(f"refreshed {m=}")


def main():
    create_db_and_tables()
    create_entry()
    time.sleep(5)
    update_entry()


if __name__ == "__main__":
    main()

Running it outputs:

created m=Model(id=None, foo=None, created_at=None, updated_at=None)
added and refreshed m=Model(foo=None, created_at=datetime.datetime(2022, 6, 29, 8, 14, 53), updated_at=None, id=1)

found m=Model(foo=None, created_at=datetime.datetime(2022, 6, 29, 8, 14, 53), updated_at=None, id=1)
modified m=Model(foo='bar', created_at=datetime.datetime(2022, 6, 29, 8, 14, 53), updated_at=None, id=1)
refreshed m=Model(foo='bar', created_at=datetime.datetime(2022, 6, 29, 8, 14, 53), updated_at=datetime.datetime(2022, 6, 29, 8, 14, 58), id=1)

So created_at is constant, and update_at is 5 seconds after created_at as expected given the 5 second sleep between creation and update.

[regainOWO]

@RobertRosca This example is very detailed! thanks a lot!

[RobertRosca]

Thanks! Happy it helped 😄

I wrote up an explanation and created PR #372 to add this to the documentation, since it does come up pretty often.

[RobertRosca]

I know #372 isn't merged yet, but if my example helped answer your question could you close this issue? Thanks 😄

Feel free to leave comments or suggestions on #372 as well if you have any!

[Proahmedjaved]

@RobertRosca This is really helpful but there is an issue with created_at field. datetime on created_at is fixed for every resource creation. server_default assigns default data directly to the table when using alembic.

[RobertRosca]

So when using the example code I showed with Alembic instead of SQLModel.metadata.create_all the created_at field always has the same value, as if the default value were set to a constant datetime?

I haven't tested it with Alembic, but their docs show that the server_default=func.now() should work to set the time to the current time for a timestamp column:

from alembic import op
from sqlalchemy import Column, TIMESTAMP, func

# specify "DEFAULT NOW" along with the column add
op.add_column('account',
    Column('timestamp', TIMESTAMP, server_default=func.now())
)

I guess there could be some issue with interactions between Alembic, SQLAlchemy, and SQLModel causing the default to be a constant instead of the now function, but I haven't had time to look.

Since this should be supported by Alembic then if this isn't working correctly it's probably an issue in SQLModel, so I'd say open an issue here with some example code. I'll try and have a look some time this week as well, but maybe somebody else would be able to help before I can 😄

[tiangolo]

Thanks a lot for the help here @RobertRosca ! 🍰

And thanks @regainOWO for coming back to close the issue. ☕

[FilipeMarch]

I don't understand... this definitely does not work for me.
I have a database containing some users, when I add the column created_at and execute alembic upgrade head I get this error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Cannot add a column with non-constant default
[SQL: ALTER TABLE users ADD COLUMN created_at DATETIME DEFAULT (CURRENT_TIMESTAMP)]

migration script:

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('users', sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=True))
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('users', 'created_at')
    # ### end Alembic commands ###

I just added this line to Users table:

    created_at: Optional[datetime.datetime] = Field(
        sa_column=Column(DateTime(timezone=True), server_default=func.now())
    )

[FilipeMarch]

I figured out this works:
I added these lines to User table:

    created_at: datetime.datetime = Field(
        default_factory=datetime.datetime.utcnow,
        nullable=False,
    )

In env.py I added the argument render_as_batch=True to context.configure.
I autogenerated the migration.
I changed the upgrade function to add the created_at column with nullable=True, I updated the values with sa.func.now and then I batch_alter_table to make it nullable=False.

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    print("Adding column created_at")
    op.add_column("users", sa.Column("created_at", sa.DateTime(), nullable=True))
    # Get the users.created_at column
    user = sa.sql.table(
        "users",
        sa.sql.column("created_at", sa.DateTime()),
    )
    # Update the users.created_at column
    op.execute(user.update().values(created_at=sa.func.now()))

    with op.batch_alter_table("users", schema=None) as batch_op:
        # Set the users.created_at column to not null
        batch_op.alter_column("created_at", nullable=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table("users", schema=None) as batch_op:
        batch_op.drop_column("created_at")

[FilipeMarch]

I also implemented updated_at field here with the following:
Add this to the your SQLModel:

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

Alembic migration:

def upgrade() -> None:
    op.add_column("users", sa.Column("updated_at", sa.DateTime(), nullable=True))

def downgrade() -> None:
    op.drop_column("users", "updated_at")

Or in case you are using batch operation:

def upgrade() -> None:
    with op.batch_alter_table('users', schema=None) as batch_op:
        batch_op.add_column(sa.Column('updated_at', sa.DateTime(), nullable=True))

def downgrade() -> None:
    with op.batch_alter_table('users', schema=None) as batch_op:
        batch_op.drop_column('updated_at')

[jwezel]

This doesn't work if I factor out a field with sa_column attribute to another class and inherit from it, like:

class WithId(SQLModel):
    """
    Database row with ID field
    """

    id: UUID = Field(default_factory=lambda: ULID().to_uuid(), description='Row ULID', primary_key=True)


class TimedRow(SQLModel):
    """
    Database row with status times
    """

    created: datetime = Field(
        default_factory=lambda: datetime.now(tz=config.timezone),
        sa_column=Column(DateTime(timezone=True), server_default=text('CURRENT_TIMESTAMP'), nullable=False),
        description='Time when row was created',
    )
    updated: datetime | None = Field(
        sa_column=Column(DateTime(timezone=True)),
        description='Time when row was last updated',
    )
    deleted: datetime | None = Field(
        sa_column=Column(DateTime(timezone=True)),
        description='Time when row was deleted',
    )

class Practice(TimedRow, WithId, table=True):

    name: str

If I have definitions like Practice in more than one module, I get:

sqlalchemy.exc.ArgumentError: Column object 'created' already assigned to Table '<tablename>'

on the second and following import of such a module.