Repository navigation
Alembic Migration when going from SQLAlchemy declarative_base to SQLModel adding indexes #9
Description
First Check
- I added a very descriptive title to this issue.
- I used the GitHub search to find a similar issue and didn't find it.
- I searched the SQLModel documentation, with the integrated search.
- I already searched in Google "How to X in SQLModel" and didn't find any information.
- I already read and followed all the tutorial in the docs and didn't find an answer.
- I already checked if it is not related to SQLModel but to Pydantic.
- I already checked if it is not related to SQLModel but to SQLAlchemy.
Commit to Help
- I commit to help with one of those options 👆
Example Code
# Original SQLAlchamy Schema
import uuid
from sqlalchemy import Column, DateTime
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = "user"
id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, unique=True, default=uuid.uuid4)
created_at = Column(DateTime, nullable=False)
updated_at = Column(DateTime, nullable=False)
# Updated to sqlmodel Schema
import uuid
from datetime import datetime
from sqlmodel import Field, SQLModel
class User(SQLModel, table=True):
id: uuid.UUID = Field(primary_key=True, default=uuid.uuid4)
created_at: datetime
updated_at: datetime
# Resulting Auto Migration
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('user', 'id',
existing_type=postgresql.UUID(),
nullable=True)
op.create_index(op.f('ix_user_created_at'), 'user', ['created_at'], unique=False)
op.create_index(op.f('ix_user_id'), 'user', ['id'], unique=False)
op.create_index(op.f('ix_user_updated_at'), 'user', ['updated_at'], unique=False)
# ### end Alembic commands ###Description
Using Postgres 13, alembic 1.6.5, and SQLAlchemy 1.4.22. All deps locked with poetry and run inside a docker container.
- Create a simple auto migration with Alembic based on SQLAlchemy declarative_base models alembic revision --autogenerate
- Upgrade DB with migration
- Change schema to use sqlmodel and run automigration again.
The resulting migration includes a bunch of unnecessary indexes.
Operating System
Linux
Operating System Details
Ubuntu in Docker on Linux host
FROM python@sha256:8f642902ba368481c9aca0a100f08daf93793c6fa14d3002253ea3cd210383a7
SQLModel Version
0.0.3
Python Version
3.9.6
Additional Context
No response
Activity
CaselIT commented on Aug 25, 2021
It seems to add an index by default
https://github.com/tiangolo/sqlmodel/blob/af03df88acda8c5fb3e0e755b0ab0517a2c8aabb/sqlmodel/main.py#L419-L420
I think it's just a typo there
falkben commented on Nov 20, 2021
Until this gets fixed, if you don't want to set index=False on every single attribute, you can create a decorator on Field function and have it override the index param setting the default to False. This works as long as you make sure to call Field() for each of your model attributes.
To be used like:
Creates a table with name column with an index but description does not.
Without calling Field() for description, you'd still end up with an index.
tiangolo commented on Dec 28, 2021
Thank you @peterHoburg for bringing it up, thanks @CaselIT for the PR, and thanks @falkben for the workaround! 🚀 🍰
This should be solved in #205, the new docs explaining indexes are here: https://sqlmodel.tiangolo.com/tutorial/indexes/
Indexes are now opt-in and not created by default. You can read more about it all in that PR.
It is now released as part of SQLModel 0.0.6. 🎉
github-actions commented on Jan 8, 2022
Assuming the original need was handled, this will be automatically closed now. But feel free to add more comments or create new issues or PRs.
Merge pull request fastapi#9 from NEXT-JP/patch-1