Alembic settings primary key to nullable

[adlmtl]

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

class Foo(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    bar: str

Description

• Create model, with id field as described in docs
• Run alembic migrations
• Upgrade head
• Run alembic migrations again

Alembic reads the primary key as nullable.
When running second time it tries to alter the field
You will then get an error when trying to upgrade.
I using psycopg2-binary

*Adding nullable=False to id declaration fixes this. Should this be added to the tutorial?

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.8

Additional Context

"""foo

Revision ID: 6d4a123a9ff6
Revises: 2910ce9e323e
Create Date: 2021-10-01 12:53:59.432921

"""
from alembic import op
import sqlalchemy as sa
import sqlmodel

revision identifiers, used by Alembic.

revision = '6d4a123a9ff6'
down_revision = '2910ce9e323e'
branch_labels = None
depends_on = None

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('foo',
sa.Column('id', sa.Integer(), nullable=True),
sa.Column('bar', sqlmodel.sql.sqltypes.AutoString(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('ix_foo_bar'), 'foo', ['bar'], unique=False)
op.create_index(op.f('ix_foo_id'), 'foo', ['id'], unique=False)
# ### end Alembic commands ###

def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_index(op.f('ix_foo_id'), table_name='foo')
op.drop_index(op.f('ix_foo_bar'), table_name='foo')
op.drop_table('foo')
# ### end Alembic commands ###

[rafsaf]

For anybody facing issues with alembic and sqlmodel.

People wrote issues about migrations in very first days of this project I believe, you are not alone ;)

My own investigation has shown that (alembic migrations) they are completely broken (even typo errors - indexes, see PR #11, simple but not yet merged) at the moment and although we see 0.0.4, it should be still alpha, beta or pre-release w/e, it's just not yet ready for something more than "hello world" playground and hopefully tiangolo will find time to continue looking into this project, it looks very promising.

So the answer to the main question: Probably a bug

[jdgaravito]

yes, i got the same problem, alembic was trying to alter column ID even when is a primary key. the way i ommited the error was to remove any reference of alter column where id was modified in the migration file.

[heyaco]

Gotta get alembic working. Having the same issue.

[wangxin688]

facing the same issue.

[heyaco]

Not so much a real issue if you can read and tweak alembic's autogen code... but if u wanna keep things idiot proof, which is usually the case, gotta get alembic workin like magic

[Naisisor]

facing the same issue.

[thomasborgen]

Same issue

[murata100]

I did it like this.

If primary_key=True, be sure to specify nullable=False

Before

class Foo(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True)
    bar: str

After

class Foo(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, nullable=False)
    bar: str

This works fine and probably works as desired before and after the alembic bug has been fixed.
Please let me know if you have any problems with this

[alexberezin]

I did it like this.

If primary_key=True, be sure to specify nullable=False

Before

class Foo(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True)
    bar: str

After

class Foo(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, nullable=False)
    bar: str

This works fine and probably works as desired before and after the alembic bug has been fixed. Please let me know if you have any problems with this

Worked for me

[tiangolo]

Thanks for the report and for the discussion everyone! This was probably fixed in #79

It will be available in SQLModel 0.0.7, released in the next hours. 🚀