Revision autogenerate fails to import datetime

[erfanrajati]

When having a SQLAlchemy model than has a Datetime field, alembic fails to import datetime in the migration file, which is mandatory for "upgrade".

The only fix I found so far is to manually import datetime in freshly generated migration files.

Unfortunately, this breaks the flow of orchestration using docker and similar tools. Since it's needed to manually change a file in the middle of migration steps, you can't fully orchestrate the project.

[CaselIT]

Hi,

Can you provide an example? Datetime should be available from the sa namespace that's imported by default with import sqlalchemy as sa.

Unfortunately, this breaks the flow of orchestration using docker and similar tools. Since it's needed to manually change a file in the middle of migration steps, you can't fully orchestrate the project.

I would advise against such a workflow, since autogenerate is intended to be a starting point for a migration, developers are expected to verify and correct it as needed.
Failing to do so will leat to unwanted results. For example column renames are detected as column remove plus column add that will leat do data loss

[zzzeek]

I've never heard of adding new source code to a project as part of an automated orchestration. im not able to think of how that would work

[erfanrajati]

Thank you for your answers, here's the idea: let's say I have a exam model that has some date fields like below:

class Exam(Base):
    __tablename__ = 'exam'

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=timezone.utc),
        default=datetime.now(tz=timezone.utc)
    )
    date_begins: Mapped[datetime] = mapped_column(
        DateTime(timezone=timezone.utc),
        nullable=False
    )

in the example above datetime is python's datetime module and DateTime is from SQLAlchemy.

alembic autogenerate will write something like this for the migration:

    op.create_table('exam',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=datetime.timezone.utc), nullable=False),
    sa.Column('date_begins', sa.DateTime(timezone=datetime.timezone.utc), nullable=False),
    )

it does import everything correctly except Python's built in datetime module, which results in incomplete migration when you run upgrade head. That's actually very bad because it's hard to detect which models have migrated and which ones have not.

I'm not sure if I'm using everything correctly, but the only fix I found so far is to import datetime in the migration script generated by alembic revision --autogenerate, before applying the migration with upgrade head.

here's how this breaks the normal flow of dockerization, if it did import everything (including datetime), you could have an all in one Dockerfile that would look like this:

FROM python:3.12 AS base
WORKDIR /app

COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

COPY . .
# a normal and easy flow of db migration on docker build
RUN alembic revision --autogenerate
RUN alembic upgrade head

CMD ["sh", "-c", "fastapi dev --host 0.0.0.0"]

But you can't do that now, because after revision --autogenerate, you must import datetime in the script before running the migration.

Final Question

is there a more standard usage flow of alembic that prevents this?

[CaselIT]

DateTime(timezone=timezone.utc),

This is not something that sqlalchemy support. timezone is a boolean: https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.DateTime

here's how this breaks the normal flow of dockerization

you should not do that, augogenerate is not meant to be set and forget without anyone looking at the candidate migration. Unless you don't care about the data

[zzzeek]

aside from the issue that the "timezone" thing is not part of DateTime, the general way of having autogenerate include imports that aren't automatic is at two levels:

1. add them to script.py.mako - this is the general approach for imports that are going to be frequently accessed by lots of migrations. This approach could even be used if you were linting your migrations to not have unused imports, since you could apply an import fixer like isort to migration files as they are generated from your script.py.mako using a code formatter hook
2. use a type rendering function that adds the necessary imports to the AutogenContext.imports collection

overall approach 1 is preferred since it's a lot less trouble