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

[azataiot]

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

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='user@example.com' 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.

[Jufik]

That's how I sort this out now:

from typing import Annotated
from datetime import datetime, timezone
import pytest
from pydantic import types as pydantic_types, ValidationError
from sqlmodel import Field, SQLModel, create_engine, Session, DateTime, Column

class DummySchema(SQLModel):
    timestamp: pydantic_types.AwareDatetime = Field(sa_type=DateTime(timezone=True))


class DummyModel(DummySchema, table=True):
    id: int | None = Field(default=None, primary_key=True)

Indeed @antont , I believe adding sa_type_kwargs would be really helpful.
For such a widespread issue (eg: TZ Management): handle pydantic_types casting seems reasonnable.

That will throw an error:

class DummySchema(SQLModel):
    timestamp: pydantic_types.AwareDatetime = Field()

while that works:

class DummySchema(SQLModel):
    timestamp:  Annotated[datetime, pydantic_types.AwareDatetime] = Field()

Just considering all the whatever_at kind of fields you'd get that seems like a reasonable thing to have.
In the end, most of Pydantic Data Types are around date/datetime, and we could have:

PastDate = Annotated[date, pydantic_types.PastDate]
FutureDate = Annotated[date, pydantic_types.FutureDate]
PastDatetime = Annotated[datetime, pydantic_types.PastDatetime]
FutureDatetime = Annotated[datetime, pydantic_types.FutureDatetime]
AwareDatetime = Annotated[datetime, pydantic_types.AwareDatetime]
NaiveDatetime = Annotated[datetime, pydantic_types.NaiveDatetime]

as well as an updated get_sqlalchemy_type, JSON/JSONValue would be left to tackle but YAGNI I guess.

Taking a step back, I believe the this kind of challenges quite common, and making type-mapping used in get_sqlalchemy_type part of the public API would help a LOT.

In the end one could: get_sqlalchemy_type.register(annotation_type: Any,sa_type:Union[Type[TypeEngine],TypeEngine]) and let SQLModel map that transparently.
Obviously the Mapping should be Immutable, there are couple of guard rails to be put here and there but nothing impossible.
That can make SQLModel feel a bit like "magic" if mapping are added randomly on a code base yet that'd be really helpful.

I can dig into contributing to this, yet I'd like to know if there any kind of interest for such a contrib'?

[ekalosak]

@Jufik yes there's interest - your solution works, if you can make the ergonomics more natural, this would be an improvement. Congruity with Pydantic directly supports the first three project objectives:

Intuitive to write: Great editor support. Completion everywhere. Less time debugging. Designed to be easy to use and learn. Less time reading docs.
Easy to use: It has sensible defaults and does a lot of work underneath to simplify the code you write.
Compatible: It is designed to be compatible with FastAPI, Pydantic, and SQLAlchemy.

^ excerpted from: https://sqlmodel.tiangolo.com/

My 2¢ on the issue: using pydantic.AwareDatetime should "just work":

class MyThing(SqlModel):
    created_at: AwareDatetime = Field(default_factory=lambda: datetime.now(timezone.utc))
    other_stuff: ...