Dose there any better way to write timezone aware datetime field without using the SQLAlchemy ?

[azataiot]

First Check

• [X] I added a very descriptive title to this issue.
• [X] I used the GitHub search to find a similar issue and didn't find it.
• [X] I searched the SQLModel documentation, with the integrated search.
• [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
• [X] I already read and followed all the tutorial in the docs and didn't find an answer.
• [X] I already checked if it is not related to SQLModel but to Pydantic.
• [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• [X] I commit to help with one of those options 👆

Example Code

class UserBase(SQLModel):
    username: str = Field(index=True, unique=True)
    email: EmailStr = Field(unique=True, index=True)  # this field should be unique for table and this field is required
    fullname: str | None = None
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)

Description

I write my created_at and updated_at fields like this, however, this did not work, because of the time awareness,

SQLModel user: fullname='string' created_at=datetime.datetime(2023, 1, 26, 18, 19, 32, 961000, tzinfo=datetime.timezone.utc) updated_at=datetime.datetime(2023, 1, 26, 18, 19, 32, 961000, tzinfo=datetime.timezone.utc) id=None is_staff=False is_admin=False username='string' email='[email protected]' password='string'
(sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $4: datetime.datetime(2023, 1, 26, 18, 19, 3... (can't subtract offset-naive and offset-aware datetimes)

After checking Github, i found this solution:

class AuthUser(sqlmodel.SQLModel, table=True):
  __tablename__ = 'auth_user'
  id: Optional[int] = sqlmodel.Field(default=None, primary_key=True)
  password: str = sqlmodel.Field(max_length=128)
  last_login: datetime.datetime = Field(sa_column=sa.Column(sa.DateTime(timezone=True), nullable=False))

It is written with mixing SQLModel stuff and the SALAlchemy, I know SQLModel is SQLAlchemy under the hood but this feels strange, cause i want to face SQLModel ONLY.

Is there any better way of handling this?

Let's say when SQLModel create tables, it will check the payding field created_at, if it is timezone aware datetime then it will set it as sa_column=sa.Column(sa.DateTime(timezone=True) so that we do not need to mix them both,

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10.2

Additional Context

No response

[antont]

I know SQLModel is SQLAlchemy under the hood but this feels strange, cause i want to face SQLModel ONLY.

Generally you can't do this kind of things with SQLModel without using SA api directly, so I'd just drop that want.

[enchance]

Coming from Tortoise ORM moving to SQLModel it would be great to have if the timezone would be saved without having to use SQLAlchemy directly. The project is new so I hope updates could be made soon.

[antont]

New release has

Add support for passing a custom SQLAlchemy type to Field() with sa_type. PR #505 by @maru0123-2004.

so maybe this could be extended somehow to allow arguments for the sa Column type too.