How to use nullable field with non-null default

[BassMastaCod]

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 documentation, with the integrated search.
• I already searched in Google "How to X" and didn't find any information.
• I already read and followed all the tutorials in the docs and didn't find an answer.

Example Code

from sqlmodel import SQLModel, Field, Session, create_engine

class DefaultModel(SQLModel, table=True):
    id: int = Field(primary_key=True)
    field: int | None = Field(default=1, nullable=True)

engine = create_engine("sqlite:///:memory:")
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    model = DefaultModel(field=None)
    print(model.field)  # None (as expected)

    session.add(model)
    session.commit()
    session.refresh(model)
    print(model.field)  # 1 (inserts the default value instead of None)

    model.field = None
    session.commit()
    session.refresh(model)
    print(model.field)  # None (workaround: this time null is set in the db)

Description

1. I have a model field that is nullable and has a default value of 1
2. I want to insert a row, but don't want that default value so I set the field to None
3. However, upon commit, the default value of 1 is used instead of None/null.

Additionally, setting the field to None afterwards and re-committing gets me what I want. This seems like an inconsistency: in both cases, the field is explicitly set to None but on insert, it doesn't use that null value.

Is applying the default to any None value intended behavior? Is there a special way to inform SQLModel that I want the value to be null without executing multiple queries?

Operating System

Windows

Operating System Details

No response

Project Version

2.13.4

Python Version

3.13

Additional Context

I have not tested this with other DBs outside of sqlite.

[sueun-dev]

That default 1 isn't coming from Pydantic, it's a SQLAlchemy column default. Field(default=1) attaches a ScalarElementColumnDefault(1) to the column, and a scalar column default gets applied on INSERT whenever the value is None, so your explicit None gets replaced with 1. There's no column default on UPDATE (that's onupdate), which is why setting it to None and committing again keeps the null.

To make unset give 1 but explicit None give NULL, move the default off the column so only the Pydantic side remains:

from sqlalchemy import Column, Integer
field: int | None = Field(default=1, sa_column=Column(Integer, nullable=True))

Keep default=1 on the Field, that's now what fills 1 when the attribute is unset. The column itself has no SA default, so Model() inserts 1 and Model(field=None) inserts NULL, each a single insert. Heads up that server_default=text("1") has the same trap (it also turns an explicit None into 1). Checked on sqlmodel 0.0.38 / sqlalchemy 2.0.51 / pydantic 2.13.4.

[BassMastaCod]

Thank you! This was the support I needed to get unblocked. My solution turned out a little different since my library dynamically creates the Field and doesn't actually know that sa_type.

So what I did was intercept the field after it's created and clear that default value. The Pydantic default is still present, but the SQLAlchemy default is changed to None. This was simple to do inside a Metaclass which I was already using for other purposes.

Here is the commit in case it helps anyone else: BassMastaCod/DAOModel@6a5202c

But the answer from sueun-dev should work perfectly in normal circumstances.

Thanks again!