Optional field in model fails validation with error Field required

[sebastianzander]

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

# model/round.py

from datetime import date, datetime
from model.tournament import Tournament
from sqlalchemy import Column, DateTime, UniqueConstraint, func, text
from sqlmodel import Field, Relationship, SQLModel
from typing import Optional


class RoundBase(SQLModel):
    __table_args__ = (
        UniqueConstraint('tournament_id', 'number'),
    )

    tournament_id: Optional[int] = Field(default = None, foreign_key = 'tournament.id')
    number: int = Field()
    date_started: Optional[date] = Field(default = None)
    date_ended: Optional[date] = Field(default = None)
    status: int = Field(default = 0)
    flags: int = Field(default = 0)


class Round(RoundBase, table = True):
    id: Optional[int] = Field(default = None, primary_key = True)

    # The following column/field causes the issue; it is declared similar to `id` since it must not be specified in requests like POST or PATCH
    last_updated: Optional[datetime] = Field(sa_column = Column(DateTime(), nullable = True, default = func.now(),
                                                                server_default = text('current_timestamp'),
                                                                onupdate = func.now()))
    # Relationships
    tournament: Optional[Tournament] = Relationship(back_populates = 'rounds')


class RoundCreate(RoundBase):
    pass


class RoundRead(RoundBase):
    id: int
    last_updated: datetime


class RoundUpdate(SQLModel):
    tournament_id: Optional[int] = None
    number: Optional[int] = None
    date_started: Optional[date] = None
    date_ended: Optional[date] = None
    status: Optional[bool] = None
    flags: Optional[bool] = None


# api/round.py

from database import database_session
from fastapi import APIRouter, Depends, HTTPException, Query, status
from model.round import Round, RoundCreate, RoundRead, RoundUpdate
from pydantic_core import ValidationError
from sqlalchemy import select
from sqlalchemy.exc import NoResultFound, IntegrityError
from sqlalchemy.orm.exc import UnmappedInstanceError
from sqlmodel.ext.asyncio.session import AsyncSession
from typing import List


round_router = APIRouter()


@round_router.post('', response_model = RoundRead)
async def post_round(
        body: RoundCreate,
        session: AsyncSession = Depends(database_session)):
    """ Creates a new round. """
    try:
        round = Round.model_validate(body)
        session.add(round)
        await session.commit()
        await session.refresh(round)

    except ValidationError as e:
        raise HTTPException(status_code = status.HTTP_400_BAD_REQUEST,
                            detail = f'The given round is invalid: {e.errors()}')

    except IntegrityError as e:
        raise HTTPException(status_code = status.HTTP_400_BAD_REQUEST,
                            detail = f'The round could not be added: {e._message()}')
    return round

Description

I created my models after the tutorial Multiple Models with FastAPI, so I use different classes for different requests and responses. All worked fine until I added the column/field last_updated, which I want to be part of the database model and behave similar to id, in that I don't specify it in any POST or PATCH requests. That's why I gave it the type Optional[datetime].

Now if I post a new round (btw, as in golf round) I get the following validation error:

[{'type': 'missing', 'loc': ('last_updated',), 'msg': 'Field required', 'input': RoundCreate(tournament_id=8, number=5, date_started=datetime.date(2024, 3, 10), date_ended=datetime.date(2024, 3, 10), status=0, flags=0), 'url': 'https://errors.pydantic.dev/2.5/v/missing'}]

Since it is the same principle as with column id I believe the problem lies maybe in how the field is declared. I have to say I find no definitive explanations about datetime or timestamp fields, neither via web search nor on FastAPI or SQLModel pages. At least this now creates the correct database schema for me and it does indeed update last_updated upon PATCH requests for rows that I manually inserted into the database.

I omitted my tournament model although the reference is still in the example code above. Should anyone try the example, just remove the relationship.

I would be glad for any advice or improvement suggestion towards my model declaration. Thank you in advance.

Sebastian

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.14

Python Version

Python 3.11.2

Additional Context

No response

[sebastianzander]

I solved my problem. It seems that I need default = None for that field as well, even though there is already a default given in sa_column = Column(...). My previous impression was, that pydantic only cares about the type annotation, but it doesn't. So my complete working field declaration is:

class Round(RoundBase, table = True):
    # ...
    last_updated: Optional[datetime] = Field(default = None,
                                             sa_column = Column(DateTime(), nullable = True, default = func.now(),
                                                                server_default = text('current_timestamp'),
                                                                onupdate = func.now()))

Not sure, though, whether default = func.now() is still needed and whether it has any effect, now.

[tiangolo]

Thanks for reporting back with your solution! 🚀