I'm wondering if this is how fields like max_length should work

[TGoddessana]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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 Role(str, Enum):
    ADMIN = "admin"
    EDITOR = "editor"


class User(SQLModel, table=True):
    __tablename__ = "user"

    id: int | None = Field(default=None, primary_key=True)
    username: str = Field(max_length=25)
    email: EmailStr = Field(unique=True)
    password: str = Field(nullable=False)
    role: Role = Field(default=Role.EDITOR)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    is_active: bool = Field(default=False)

Description

I'm wondering if this is how fields like max_length should work.

Hi, if you look at the SQLModel class below, you can see that the maximum field length for username is set to 25.

So I expected the SQL equivalent of this to be something like VARCHAR(6), but the actual SQL doesn't add any constraints on the length to the VARCHAR.

Should I use sa_column for this? Or is there another way and I'm missing something?

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.14

Python Version

3.11

Additional Context

No response

[fcaspani01]

You can use sa_type argument

Try this:

from sqlalchemy import String
class Role(str, Enum):
    ADMIN = "admin"
    EDITOR = "editor"


class User(SQLModel, table=True):
    __tablename__ = "user"

    id: int | None = Field(default=None, primary_key=True)
    username: str = Field(sa_type=String(25))
    email: EmailStr = Field(unique=True)
    password: str = Field(nullable=False)
    role: Role = Field(default=Role.EDITOR)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    is_active: bool = Field(default=False)

[TGoddessana]

Thank you. I have a question: Is this the intended behavior, or is this a bug?

[fcaspani01]

I don't know but I think that it is a bug.

[TGoddessana]

@fcaspani01 Thank you. I actually thought it was a bug myself.

@tiangolo Hi, I'd be grateful if you could tell me if this is a bug or not. If it is a bug, I'd like to work on fixing it, thank you.

[DarylDohner20]

this is impacting me as well, the sa_type workaround is fine but the max_length not being respected for varchar fields is definitely confusing
looks like this problem has existed for a while: #126

[HeiziND]

this is impacting me a lot as I am working with an existing postgres db with 100s of varchar(n) columns.
Using sa_type doesn't work well as a workaround:

• it doesn't give you pydantic validation, so have to also supply max_length which makes. it ugly:
  username: str = Field(sa_type=String(25), max_length=25)
• my IDE (Pycharm with the pydantic plugin install), complains:

"Expected type 'type | PydanticUndefinedType', got 'String' instead"

so there seem to be some type-hinting issues. note that it doesn't complain if I just use
username: str = Field(sa_type=String, max_length=25)
but that obviously defeats the point

[chris-beedie]

I've just stumbled on this issue too. This was working as expected when targeting Pydantic V1, so think it is a bug. The sa_type workaround works fine.

I dug a little deeper and the issue appears to be in a function in _compat.py

    def get_field_metadata(field: Any) -> Any:
        for meta in field.metadata:
            if isinstance(meta, PydanticMetadata):
                return meta
        return FakeMetadata()

The configured max length is in field.metadata, but as an annotated type, not PydanticMetadata, so fails the check.

[proever]

I opened a small PR (#857) that seems to fix the issue by checking for the case mentioned above.

[niderhoff]

I am using a combination of max_length and sa_type

username: str = Field(sa_type=String(25), max_length=25)

it seems to kinda work, but mypy is really mad about it:

src/document_ingest_service/app/core/models_v2.py:170: error: No overload variant of "Field" matches argument types "int", "String"  [call-overload]
src/document_ingest_service/app/core/models_v2.py:170: note: Possible overload variants:
src/document_ingest_service/app/core/models_v2.py:170: note:     def Field(default: Any = ..., *, default_factory: Callable[[], Any] | None = ..., alias: str | None = ..., title: str | None = ..., description: str | None = ..., exclude: AbstractSet[int | str] | Mapping[int | str, Any] | Any = ..., include: AbstractSet[int | str] | Mapping[int | str, Any] | Any = ..., const: bool | None = ..., gt: float | None = ..., ge: float | None = ..., lt: float | None = ..., le: float | None = ..., multiple_of: float | None = ..., max_digits: int | None = ..., decimal_places: int | None = ..., min_items: int | None = ..., max_items: int | None = ..., unique_items: bool | None = ..., min_length: int | None = ..., max_length: int | None = ..., allow_mutation: bool = ..., regex: str | None = ..., discriminator: str | None = ..., repr: bool = ..., primary_key: bool | PydanticUndefinedType = ..., foreign_key: Any = ..., unique: bool | PydanticUndefinedType = ..., nullable: bool | PydanticUndefinedType = ..., index: bool | PydanticUndefinedType = ..., sa_type: type[Any] | PydanticUndefinedType = ..., sa_column_args: Sequence[Any] | PydanticUndefinedType = ..., sa_column_kwargs: Mapping[str, Any] | PydanticUndefinedType = ..., schema_extra: dict[str, Any] | None = ...) -> Any
src/document_ingest_service/app/core/models_v2.py:170: note:     def Field(default: Any = ..., *, default_factory: Callable[[], Any] | None = ..., alias: str | None = ..., title: str | None = ..., description: str | None = ..., exclude: AbstractSet[int | str] | Mapping[int | str, Any] | Any = ..., include: AbstractSet[int | str] | Mapping[int | str, Any] | Any = ..., const: bool | None = ..., gt: float | None = ..., ge: float | None = ..., lt: float | None = ..., le: float | None = ..., multiple_of: float | None = ..., max_digits: int | None = ..., decimal_places: int | None = ..., min_items: int | None = ..., max_items: int | None = ..., unique_items: bool | None = ..., min_length: int | None = ..., max_length: int | None = ..., allow_mutation: bool = ..., regex: str | None = ..., discriminator: str | None = ..., repr: bool = ..., sa_column: Column[Any] | PydanticUndefinedType = ..., schema_extra: dict[str, Any] | None = ...) -> Any

in SQLModel the type for sa_type is

sa_type: Union[Type[Any], UndefinedType] = Undefined,

[YuriiMotov]

In current version (0.0.24) it already works this way:

from enum import Enum
from sqlmodel import SQLModel, Field, create_engine

class Role(str, Enum):
    ADMIN = "admin"
    EDITOR = "editor"

class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    username: str = Field(max_length=25)
    role: Role

engine = create_engine("sqlite:///", echo=True)
SQLModel.metadata.create_all(engine)

Output:

CREATE TABLE user (
        id INTEGER NOT NULL, 
        username VARCHAR(25) NOT NULL, 
        role VARCHAR(6) NOT NULL, 
        PRIMARY KEY (id)
)