How can I use "select for update" with sqlmodel?

[oren0e]

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

Not relevant

Description

Hi,
I want to perform a "select for update" query with sqlmodel, I know that in SqlAlchemy this is done with .with_for_update(). However something like this does not work:

    with Session(engine) as session:
        session.query(BomModel).filter(BomModel.serial == 123).with_for_update().first()

BomModel is defined as:

class BomModel(SQLModel, BomSchema, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    serial: int = Field(index=True)
    order_id: int = Field(index=True)
    assembly_id: str = Field(index=True)
    bom: dict = Field(sa_column=Column(JSONB), default_factory=dict)

and BomSchema as

class BomSchema(BaseModel):
    serial: int = Field(frozen=True)
    order_id: int = Field(frozen=True)
    assembly_id: str = Field(frozen=True)
    bom: dict = Field(default={})

I already tried to change session.query() to session.exec() and session.execute() - but that doesn't solve it.
Is there something in the documentation that I am missing?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.22

Python Version

3.9

Additional Context

No response

[YuriiMotov]

Seems that this works:

    st = select(BomModel).filter(BomModel.serial == 123).with_for_update()
    print(st.compile(engine))

    # SELECT bommodel.id, bommodel.serial 
    # FROM bommodel 
    # WHERE bommodel.serial = %(serial_1)s FOR UPDATE

    obj = session.exec(st).first()