Migrate SQLAlchemy models having dates without timezone to ones with timezone

Question

I have some SQLAlchemy models defined like this:

class TimeData(object):
    __table_args__ = {"extend_existing": True}

    created_on = Column(DateTime(timezone=True), server_default=db.func.now())
    updated_on = Column(
        DateTime(timezone=True),
        server_default=db.func.now(),
        server_onupdate=db.func.now(),
        onupdate=db.func.now(),
    )


class Article(db.Model, TimeData):
    id = Column(db.Integar, primary_key=True)
    title = Column(db.Text)
    contents = Column(db.Text)
    last_reviewed_on = Column(db.DateTime)


class Review(db.Model, TimeData):
    id = Column(db.Integer, primary_key=True)
    review = Column(db.Text)
    rating = Column(db.Integer)
    read_on = Column(db.DateTime)

As one can see, the DateTime objects which are inherited from the TimeData class are marked with timezone=True while the dates defined in the models themselves are not. This results two different types of date strings when serializing.

The created_on gets serialized into something like 2019-11-25T01:53:05.576Z with the Z in the end, while values like read_on get serialized into 2019-11-25T01:53:05.576 without the Z. Since we store everything in UTC, the date information in the DB is fine. But the serialized output is causing problems in the client side when the browser evaluated the dates. The ones with the Z are read as UTC, while the ones without Z are read as local time.

As a solution, migrating the models doesn't seem to work. When timezone=True is added to the models, Flask-Migrate (Alembic) doesn't detect any changes in the models.

How to migrate the dates to ones with TimeZone?

Answer 1

I had a similar issue, I ended up solving it with adding timezone=True to my datetime fields.

You are right that by default that change is not picked up by Flask-Migrate but after adding compare_type=true to your configuration in env.py it does.

    context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=True
            )

see for details: Can Alembic Autogenerate column alterations?

After generating the migration and upgrading my db my datetime fields all returning UTC (with z at the end) in the output.