Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to add current date time by default on a table declaration? #594

Closed
1 task done
fraddygil opened this issue May 15, 2023 · 13 comments
Closed
1 task done

How to add current date time by default on a table declaration? #594

fraddygil opened this issue May 15, 2023 · 13 comments

Comments

@fraddygil
Copy link

Privileged issue

  • I'm @tiangolo or he asked me directly to create an issue here.

Issue Content

how to add a field in the table with the current date by default?

@antont
Copy link

antont commented May 15, 2023

We've been doing it like this

from datetime import datetime
from sqlmodel import SQLModel, Field

(...)

created_at: datetime = Field(default_factory=lambda: datetime.now())

@FilipeMarch
Copy link

I do the same as @antont but without the lambda

from sqlmodel import SQLModel, Field
import datetime


class Article(SQLModel, table=True):
    created_at: datetime.datetime = Field(
        default_factory=datetime.datetime.utcnow,
    )

@antont
Copy link

antont commented May 31, 2023

lol I don't know why we had the useless lambda, i guess some mindless copy-paste, thanks, will get to clean those up.

@firecube-oss
Copy link

This is the cleanest way I have found to do it

core_models.py

class SimpleIDModel(BaseModel):
    id: int


class UUIDIDModel(BaseModel):
    id: UUID = Field(default_factory=uuid4)


class TimestampModel(BaseModel):
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: Optional[datetime]


class EventTimestamp(BaseModel):
    occured_at: Optional[datetime]

consuming.py

class Service(SimpleIDModel, TimestampModel, table=True):
    name: str = Field(nullable=False)
    description: str = Field(nullable=False)

I think having opinionated timestamping might not work.

Some people might regard update_at as when a new version of the record was created rather than when the record is updated later on

@FilipeMarch
Copy link

This is how I use updated_at:

from sqlmodel import SQLModel, Column, Field
from sqlalchemy import DateTime, func
from typing import Optional
import datetime


class Article(SQLModel, table=True):
    created_at: datetime.datetime = Field(
        default_factory=datetime.datetime.utcnow,
    )
    updated_at: Optional[datetime.datetime] = Field(
        sa_column=Column(DateTime(), onupdate=func.now())
    )

Some people might regard update_at as when a new version of the record was created rather than when the record is updated later on

Personally I believe this does not make much sense.

  • When the record is created at 5PM, it was created_at 5PM.
  • When the record is updated at 7PM, it was updated_at 7PM.

@JamesHutchison
Copy link

Just going to throw this out there - created at / updated at / soft deleted at are really common patterns and there might be a bit of value to just make them first class features

@antont
Copy link

antont commented Jul 4, 2023

Just going to throw this out there - created at / updated at / soft deleted at are really common patterns and there might be a bit of value to just make them first class features

How would you do it? Maybe a mixin in some library module, that one can use to add features to base SQLModel?

@JamesHutchison
Copy link

Yes, I think a mixin might work. I do think it's worth having a discussion how created at / updated at / deleted at would fit with created by / updated by / deleted by (so user IDs, which may be an integer or string type) as well as versioned entities (does created / updated just get replaced with "updated", and "created" is implicit with version 1?)

@speetpeet
Copy link

It's common to delegate generating timestamps to the database rather than generating them in Python. Much like @FilipeMarch is showing in the update_at column.

In that case you would set the server_default attribute on the column. In the case of sqlalchemy it would look like this:
time = db.Column(db.Time, server_default=sqlalchemy.func.now())

I agree it's worth having a firstclass feature for this.

@rafalkrupinski
Copy link

Is the solution in #594 (comment) the answer? Can the issue be closed?

@PookieBuns
Copy link
Contributor

PookieBuns commented Aug 9, 2023

It's common to delegate generating timestamps to the database rather than generating them in Python. Much like @FilipeMarch is showing in the update_at column.

In that case you would set the server_default attribute on the column. In the case of sqlalchemy it would look like this: time = db.Column(db.Time, server_default=sqlalchemy.func.now())

I agree it's worth having a firstclass feature for this.

I agree generating timestamp should be dealt with by the database. In this case, I currently use this

class Game(GameBase, table=True):
    created_at: datetime = Field(
        sa_column_kwargs={
            "server_default": text("CURRENT_TIMESTAMP"),
        }
    )

The advantage of this is I don’t need to refer to an actual sa_column, and using CURRENT_TIMESTAMP adheres to ansi sql standards

@mj2068
Copy link

mj2068 commented Aug 16, 2023

The advantage of this is I don’t need to refer to an actual sa_column, and using CURRENT_TIMESTAMP adheres to ansi sql standards

wow, amazing, this is works pretty good with alembic's --autogenerate

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

@danielqba
Copy link

This can be closed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

10 participants