Typing a column as : Optional[Decimal] fails with "ValueError: Unknown constraint max_digits"

[pellejacobs]

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

from decimal import Decimal
from typing import Optional

from sqlmodel import Field, SQLModel, create_engine


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    money: Optional[Decimal] = Field(default=0, max_digits=5, decimal_places=3)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

def main():
    create_db_and_tables()


if __name__ == "__main__":
    main()

Description

With the example code from https://sqlmodel.tiangolo.com/advanced/decimal/#decimals-in-sqlmodel, replacing : Decimal with : Optional[Decimal], fails with the following error:

...
File "/lib/python3.10/site-packages/pydantic/_internal/_known_annotated_metadata.py", line 184, in apply_known_metadata
    raise ValueError(f'Unknown constraint {constraint}')
ValueError: Unknown constraint max_digits

Operating System

Linux

Operating System Details

Reproduced the issue within a python repl.it shell.

SQLModel Version

0.0.14

Python Version

3.10

Additional Context

No response

[riziles]

@pellejacobs , I added nullable=True to the Field parameters and it seems to work fine.

Also: you may want to set the default to None if you want it to be optional. I don't think you have to, but it might be easier.

[jspaezp]

A bit more experimentation

This works!

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    money: Decimal = Field(default=None, max_digits=5, decimal_places=3, nullable=True)

But this does not ...

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    money: Optional[Decimal] = Field(default=None, max_digits=5, decimal_places=3, nullable=True)

Which feels counter-intuitive ... if this is the intendeed behavior I could write a PR appending to the documentation of the decimal https://sqlmodel.tiangolo.com/advanced/decimal/?h=decimal

[jspaezp]

@riziles and @pellejacobs I would highly appreciate if you could pitch in clarifying the behavior you observed.

[jspaezp]

Furthermore ...

from sqlmodel import SQLModel, Field
from decimal import Decimal
from typing import Optional


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    money: Decimal = Field(default=None, max_digits=5, decimal_places=3, nullable=True)


Hero.model_validate(Hero(money=Decimal("12.345")).model_dump()) # This works
Hero.model_validate(Hero(money=None).model_dump()) # This Fails

so I believe this would classify as a bug and not undocumented behavior

[joachimhuet]

Did you create an issue for that bugfix ? How can we handle that and keep the Optional ?
I opened a stackoverflow question on that topic and got no answer so far.. (https://stackoverflow.com/questions/78297135/how-to-define-an-optionaldecimal-field-in-python-sqlmodel-library-that-yields)

The problem is even wider because with the Optional[Decimal] we also loose all constraints in the model json schema.

[jspaezp]

I was not sure it it was an user/pydantic/skill issue rather than a real bug. I have not open an issue to address this.

[metamoof]

On a similar problem, I'm dealing with a legacy SQL Server database that uses DECIMAL(18,0) fields as primary keys for its tables (don't ask, not my decision).

As such, it seems that I can't type a primary key as Optional[Decimal] giving the same error - and I cannot set nullable to True here

from decimal import Decimal
from typing import Optional

from sqlmodel import Field, SQLModel, create_engine


class Hero(SQLModel, table=True):
    id: Optional[Decimal] = Field(
        default=None, primary_key=True, max_digits=18, decimal_places=0
    )
    name: str


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def main():
    create_db_and_tables()


if __name__ == "__main__":
    main()

[metamoof]

For added context, this requires special handling within sqlalchemy, going for Integer().with_variant() and I'm not sure how to map this to SQLModel.

From https://docs.sqlalchemy.org/en/20/dialects/mssql.html#using-identity-with-non-integer-numeric-types :

from sqlalchemy import TypeDecorator

class NumericAsInteger(TypeDecorator):
    '''normalize floating point return values into ints'''

    impl = Numeric(10, 0, asdecimal=False)
    cache_ok = True

    def process_result_value(self, value, dialect):
        if value is not None:
            value = int(value)
        return value

class TestTable(Base):
    __tablename__ = "test"
    id = Column(
        Integer().with_variant(NumericAsInteger, "mssql"),
        primary_key=True,
        autoincrement=True,
    )
    name = Column(String)

[riziles]

@metamoof , have you tried just creating a fake column in the sqlmodel class def? I have a table that has no pk! I just lie and tell sqlmodel that there is another column that doesn't actually exist.

[metamoof]

I'm interested in the PK, as I want to create a relationship, and use it to create a number of line objects for a header object.

I ended up going to a sqlalchemy model in order to fix the issue temporarily (this is a one-off script) but I'd like to know how to do this in SQLModel:

class Base(DeclarativeBase):
    pass

table_metadata = MetaData(
    schema="Accounting",
)

class NumericAsInteger(TypeDecorator):
    """normalize floating point return values into ints"""

    impl = Numeric(18, 0, asdecimal=False)
    cache_ok = True

    def process_result_value(self, value, dialect):
        if value is not None:
            value = int(value)
        return value


class SeatHeader(Base):
    __tablename__: str = "SeatHeader"
    
    id: Mapped[int] = mapped_column(
        Integer().with_variant(NumericAsInteger, "mssql"),
        default=None,
        primary_key=True,
    )
    SeatType: Mapped[str]
    SeatDate: Mapped[date]
    Voucher: Mapped[str]
    Company: Mapped[str]
    Layer: Mapped[int]
    Status: Mapped[str]
    InsertUser: Mapped[str]
    InsertDate: Mapped[date]
    IdExpediente: Mapped[Optional[int]] = mapped_column(Integer().with_variant(NumericAsInteger, "mssql"), default=None)
    ReferenceType: Mapped[str]
    ReferenceId: Mapped[int] = mapped_column(Integer().with_variant(NumericAsInteger, "mssql"))
    Transmission: Mapped[Optional[int]] = mapped_column(default=None)
    BCTransactionNo: Mapped[Optional[str]] = mapped_column(default=None)
    BCVerifiedDateTime: Mapped[Optional[date]] = mapped_column(default=None)

    transactions: Mapped[List["SeatTransaction"]] = relationship(
        back_populates="header"
    )

    metadata = table_metadata


class SeatTransaction(Base):
    __tablename__: str = "SeatTransaction"
    __table_args__ = {"implicit_returning": False}

    id: Mapped[int] = mapped_column(
        Integer().with_variant(NumericAsInteger, "mssql"),
        default=None,
        primary_key=True,
    )
    idSeat: Mapped[int] = mapped_column(
        ForeignKey("SeatHeader.id"),
    )
    LineType: Mapped[str]
    AccountNumber: Mapped[str]
    Debit: Mapped[float]
    Credit: Mapped[float]
    Currency: Mapped[str]
    ExchangeRate: Mapped[float]
    Info: Mapped[str]
    Area: Mapped[str]
    BusinessUnit: Mapped[str]
    Office: Mapped[str]
    FileNumber: Mapped[str]
    AuxiliaryType: Mapped[str]
    Auxiliary: Mapped[Optional[str]]
    idPrefacturacion: Mapped[int] = mapped_column(Integer().with_variant(NumericAsInteger, "mssql"))
    idHistoricoPrefacturacion: Mapped[Optional[int]] = mapped_column(Integer().with_variant(NumericAsInteger, "mssql"), default=None)
    TaxRate: Mapped[float] = mapped_column(default=0)
    TaxCode: Mapped[Optional[str]] = mapped_column(default=None)
    Withholding: Mapped[float]
    Rebillable: Mapped[bool] = mapped_column(default=False)
    ReferenceId: Mapped[Optional[int]] = mapped_column(Integer().with_variant(NumericAsInteger, "mssql"), default=None)
    ReferenceType: Mapped[Optional[str]] = mapped_column(default=None)
    RECID: Mapped[Optional[int]] = mapped_column(default=None)

    header: Mapped[SeatHeader] = relationship(back_populates="transactions")

    metadata = table_metadata

[20-buck-spin]

This discussion should not be marked as answered as the bug is still there. The stackoverflow page (https://stackoverflow.com/questions/78297135/how-to-define-an-optionaldecimal-field-in-python-sqlmodel-library-that-yields) received a temporary solution that suggests using le and multiple of instead of max_digits and decimal_places.

@tiangolo
Maybe update the docs to suggest using le and multiple_of ? Or is this issue going to be fixed in a later version?

[joachimhuet]

Yes, in fact in the stack overflow thread I also give an answer in my question. It seems the whole problem disappear when you don't use table=True. This table=True removes the usefulness of all constraints from pydantic..

@metamoof What I did for it to work in SQL model was to create a class MyTableBase that is not a table and then make a MyTable(MyTableBase, table=True) table that is only used internally and I only expose the Base class. In my case I have also a Create and Update class. And the conversion to the table is done in my crud operations functions.