alembic migrations with sqlmodel attempts to alter primary key colum

Question

Given this model:

from typing import Optional

from sqlmodel import SQLModel, Field


class SongBase(SQLModel):
    name: str
    artist: str = Field(index=False)
    #label: Optional[str] = Field(None, index=False)
    year: Optional[int] = Field(None, index=False)


class Song(SongBase, table=True):
    id: int = Field(default=None, primary_key=True, index=False)


class SongCreate(SongBase):
    pass

I create an initial alembic revision using alembic revision --autogenerate -m "init"and then apply it using alembic upgrade head.

Now I uncomment the labelfield, and run alembic revision --autogenerate -m "label". My migration shows up like this:

revision = '083a8e84f047'
down_revision = 'c1b2ad7d0a39'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('song', sa.Column('label', sqlmodel.sql.sqltypes.AutoString(), nullable=True))
    op.alter_column('song', 'id',
               existing_type=sa.INTEGER(),
               nullable=True,
               autoincrement=True)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('song', 'id',
               existing_type=sa.INTEGER(),
               nullable=False,
               autoincrement=True)
    op.drop_column('song', 'label')
    # ### end Alembic commands ###

Why is alembic trying to make changes to the id field? We're trying to evaluate sqlmodel/alembic to see if it's feasable for a production workload and having to hand-wrangle migrations to get rid of these primary key manipulations seems a bit dangerous to me. Am I doing anything wrong to make alembic want to edit my primary key field in this way?

EDIT: For disclosure, the model comes from this article/example: https://github.com/testdrivenio/fastapi-sqlmodel-alembic

just for ref: I tested another project with regular sqlaclhemy models instead of SqlModels and I can still see alembic touching fields it really shouldn't. I don't know if there could be a discrepandy between the postgres version I'm running and alembic or something. This stuff sems quite dangerous. — Trondh, Nov 06 '21 at 18:01

score 4 · Answer 1 · answered Nov 06 '21 at 19:07

Did some more research here, and looked at a few github discussions in the alembic repo. What I think happens, is that the id column doesnt explicitly set nullable=False which alembic seems to require. Then during the initial "migration" it overlooks that fact (idk if that's a bug), which makes the model get out of sync from the get-go. So each migration tries to bring it back into sync.

In any case, the fix seems to be to always explicitly declare the nullable parameter for primary key fields:

class SongBase(SQLModel):
    name: str
    artist: str
    label: str = Field(index=False)
    year: Optional[int] = None


class Song(SongBase, table=True):
    id: int = Field(default=None, primary_key=True, nullable=False)


class SongCreate(SongBase):
   pass

alembic migrations with sqlmodel attempts to alter primary key colum

1 Answers1