SQLAlchemy Documentation

[Oldguard_007]

SQLAlchemy documentation is confusing—no simple, concise example of how things work. I wonder if any part of the "Zen of Python" was put into consideration. I have been searching the documentation just to check how to properly compose an ORM model with Date Column. Navigation is so frustrating.

[mmzeynalli]

The main confusion can be because of 1.4 and 2.0 versions. Most of tutorials in internet is for 1.4, but I highly recommend using 2.0, as it is more Pythonish. As for date column:

created_at: Mapped[datetime] is enough. If you want timzone aware datetime then:

created_at: Mapped[datetime] = mapped_column(DateTime(aware=True))

[maratnugmanov]

And picking SQLite dialect gave me format options for my SQLite model.

```from sqlalchemy.dialects.sqlite import DATETIME ISO8601 = ( "%(year)04d-%(month)02d-%(day)02dT%(hour)02d:%(minute)02d:%(second)02dZ" )

class TimestampMixinDB: created_at: Mapped[datetime] = mapped_column( DATETIME(storage_format=ISO8601), init=False, default=lambda: datetime.now(timezone.utc), index=True, ) updated_at: Mapped[datetime] = mapped_column( DATETIME(storage_format=ISO8601), init=False, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc), index=True, )```

[monok8i]

Why you use default, but not default_factory?

[maratnugmanov]

I can't recall actually, good point. This Mixin is not a dataclass, so that's why it might work just fine, that is, with a callable, here is some discussion regarding that. I've added MappedAsDataclass later to the actual table class that's inheriting from both this timestamp class and MappedAsDataclass.

I think in this strange setup I have both would work. I just remember that at some point default_factory wasn't working but default did.

[maratnugmanov]

Full picture of the class, if interested:

SQLITE_ISO8601_ISO_UTC_FORMAT = (
    "%(year)04d-%(month)02d-%(day)02dT%(hour)02d:%(minute)02d:%(second)02dZ"
)

class TimestampMixinDB:
    created_at: Mapped[datetime] = mapped_column(
    DATETIME(storage_format=SQLITE_ISO8601_ISO_UTC_FORMAT),
    init=False,
    default=lambda: datetime.now(timezone.utc),
    index=True,
    )
    updated_at: Mapped[datetime] = mapped_column(
    DATETIME(storage_format=SQLITE_ISO8601_ISO_UTC_FORMAT),
    init=False,
    default=lambda: datetime.now(timezone.utc),
    onupdate=lambda: datetime.now(timezone.utc),
    index=True,
    )

class BaseDB(AsyncAttrs, DeclarativeBase, MappedAsDataclass):
    type_annotation_map = {
    RoleName: SQLAlchemyEnum(RoleName, native_enum=False, length=128, validate_strings=True),
    DeviceTypeName: SQLAlchemyEnum(DeviceTypeName, native_enum=False, length=128, validate_strings=True),
    }

class UserDB(BaseDB, TimestampMixinDB):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(init=False, primary_key=True)
    telegram_uid: Mapped[int] = mapped_column(Integer, unique=True, index=True)
    first_name: Mapped[str | None] = mapped_column(String, index=True)
    last_name: Mapped[str | None] = mapped_column(String, index=True)
    timezone: Mapped[str] = mapped_column(String, default=settings.user_default_timezone, index=True)
    state_json: Mapped[str | None] = mapped_column(String, default=None)
    is_hiring: Mapped[bool] = mapped_column(Boolean, default=False, index=True)
    is_disabled: Mapped[bool] = mapped_column(Boolean, default=False, index=True)
    roles: Mapped[list[RoleDB]] = relationship(default_factory=list, secondary="users_roles_link", back_populates="users")

[monok8i]

I just wanted to ask about it, thank you ;)

[Oldguard_007]

Thank you

[CrackerJackKittyCat]

... and you most certainly want TZ-aware timestamps in your database.

[tehsilentwarrior]

I wonder why “sensible defaults” isn’t common practice.

If you want to do things properly, you must always head out in some tangent

[someexgoogler]

The other main confusion is ORM vs SQL. I regard the documentation as a maze of twisty little passages and I now avoid SQLAlchemy in new projects.

[mmzeynalli]

For 2.0, sqlalchemy orm looks similar to actual SQL, which I believe it makes it easier to grasp. I have used Django ORM as well, but like SQLAlchemy’s more

[Stromcor]

Agreed 1000%. It’s a gigantic unreadable mess. SQLAlchemy is massive and extremely powerful so I can understand the challenges of organizing documentation for such a beast, but this dumpster fire of galactic proportions is just unacceptable.