I'm trying to find a way to get SqlModel and Alembic play nice together. My goal is to not have to hand-edit the auto-generated alembic migrations.
Here's my model class:
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)
After creating the initial migration etc, I add my enum. This is the closest I've come to something that works, after digging thru https://github.com/tiangolo/sqlmodel/issues?q=is%3Aissue+is%3Aopen+enum :
class SongType(enum.Enum):
Rock = "Rock"
Ballad = "Ballad"
class SongBase(SQLModel):
name: str
artist: str
label: str = Field(index=False)
song_type: SongType = Field(
sa_column=Column(
Enum(SongType),
default=None,
nullable=True,
index=False
)
)
year: Optional[int] = None
class Song(SongBase, table=True):
id: int = Field(default=None, primary_key=True, nullable=False)
The generated alembic migration from adding the song_type field looks like this:
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('song', sa.Column('song_type', sa.Enum('Rock', 'Ballad', name='songtype'), nullable=True))
# ### end Alembic commands ###
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('song', 'song_type')
# ### end Alembic commands ###
From what I can see this resembles the documentation I've found on how to make sqlalchemy and alembic work with migrations. However, when I attempt to apply this to my database, it fails:
2021-11-07 14:13:59.425 UTC [150] LOG: execute __asyncpg_stmt_9__: ALTER TABLE song ADD COLUMN song_type songtype
2021-11-07 14:13:59.425 UTC [150] ERROR: type "songtype" does not exist at character 39
Relevant parts of my package dependency tree:
╰─ pipenv graph
alembic==1.7.4
- Mako [required: Any, installed: 1.1.5]
- MarkupSafe [required: >=0.9.2, installed: 2.0.1]
- SQLAlchemy [required: >=1.3.0, installed: 1.4.26]
- greenlet [required: !=0.4.17, installed: 1.1.2]
asyncpg==0.24.0
fastapi==0.70.0
- pydantic [required: >=1.6.2,<2.0.0,!=1.8.1,!=1.8,!=1.7.3,!=1.7.2,!=1.7.1,!=1.7, installed: 1.8.2]
- typing-extensions [required: >=3.7.4.3, installed: 3.10.0.2]
- starlette [required: ==0.16.0, installed: 0.16.0]
- anyio [required: >=3.0.0,<4, installed: 3.3.4]
- idna [required: >=2.8, installed: 3.3]
- sniffio [required: >=1.1, installed: 1.2.0]
sqlmodel==0.0.4
- pydantic [required: >=1.8.2,<2.0.0, installed: 1.8.2]
- typing-extensions [required: >=3.7.4.3, installed: 3.10.0.2]
- SQLAlchemy [required: >=1.4.17,<1.5.0, installed: 1.4.26]
- greenlet [required: !=0.4.17, installed: 1.1.2]
- sqlalchemy2-stubs [required: Any, installed: 0.0.2a19]
- typing-extensions [required: >=3.7.4, installed: 3.10.0.2]
I know that I can hand-edit my migration to look something like this:
"""label
Revision ID: 12e8618deb97
Revises: c7350c7c7282
Create Date: 2021-11-07 15:21:06.927365
"""
from alembic import op
import sqlalchemy as sa
import sqlmodel
from sqlalchemy.dialects import postgresql
from app.models import SongType
# revision identifiers, used by Alembic.
revision = '12e8618deb97'
down_revision = 'c7350c7c7282'
branch_labels = None
depends_on = None
def upgrade():
song_type = postgresql.ENUM(SongType, name="song_type")
song_type.create(op.get_bind(), checkfirst=True)
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('song', sa.Column('song_type', song_type, nullable=True))
# ### end Alembic commands ###
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('song', 'song_type')
# ### end Alembic commands ###
...but my goal is to see if I can make alembic correctly autogenerate the migration - especially since the error doesn't occur until I attempt to apply the migration I hope to be able to make this safer.
I'd be very interested in any pointers on how to make this work