How to add an Index on created_at from UUIDAuditBase Mixin ?

[sorasful]

Hi there,

Imagine you have a very basic model :

class Conversation(UUIDAuditBase):
    __tablename__ = "conversations"

   ...

You want the created_at field to be indexed. How do you do this ? Do you redefine the same field in your model but with index=True ? Is there another way ?

The current implementation of AuditColumns.

@declarative_mixin
class AuditColumns:
    """Created/Updated At Fields Mixin."""

    created_at: Mapped[datetime.datetime] = mapped_column(
        DateTimeUTC(timezone=True),
        default=lambda: datetime.datetime.now(datetime.timezone.utc),
    )
    """Date/time of instance creation."""
    updated_at: Mapped[datetime.datetime] = mapped_column(
        DateTimeUTC(timezone=True),
        default=lambda: datetime.datetime.now(datetime.timezone.utc),
        onupdate=lambda: datetime.datetime.now(datetime.timezone.utc),
    )
    """Date/time of instance last update."""

    @validates("created_at", "updated_at")
    def validate_tz_info(self, _: str, value: datetime.datetime) -> datetime.datetime:
        if value.tzinfo is None:
            value = value.replace(tzinfo=datetime.timezone.utc)
        return value

Thanks !

[Kumzy]

Hello,

sorry for the late answer but yes you have to redefine your model with specifying index=True. We decided to not add it in the default as it's adding overhead.

There is no other way currently (as I am aware of)

Regards

[provinzkraut]

There is no other way currently (as I am aware of)

Actually, there is!

You should be able to directly add an Index to a model's __table_args__ like this:

class Conversation(UUIDAuditBase):
    __tablename__ = "conversations"
    __table_args__ = (Index("idx_created_at", "created_at"),)

[provinzkraut]

We could also think about adding an audit_index kwarg to UUIDAuditBases metaclass, so you could do something like

class Conversation(UUIDAutidBase, audit_index=True):

[sorasful]

Thank you for your answers and your time! I'll have to choose between the two options, that's good!