Why is sqlalchemy not setting default value correctly?

Question

For some reason, in this example, the optional is_active attribute is not getting set to default value.

from pydantic import BaseModel, EmailStr
from datetime import datetime

# Pydantic schemas

# Shared properties
class UserBase(BaseModel):
    email: Optional[EmailStr] = None
    is_active: Optional[bool] = True
    is_superuser: bool = False
    username: Optional[str] = None
    

# Properties to receive via API on creation
class UserCreate(UserBase):
    email: EmailStr
    password: str


# sqlalchemy model

class User(Base):
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(25), index=True, unique=True, nullable=False)
    email = Column(String(50), unique=True, index=True, nullable=False)
    hashed_password = Column(String(256), nullable=False)
    is_active = Column(Boolean(), default=True, nullable=False)
    is_superuser = Column(Boolean(), default=False, nullable=False)

    __mapper_args__ = {"eager_defaults": True}

I was expecting the default value of is_active, being optional input, to be True. But I get None if not explicitly passed.

obj_in = UserCreate(email=email, password=password, username=username)
print(obj_in.is_active)
# True


db_obj = User(
            email=obj_in.email,
            hashed_password=get_password_hash(obj_in.password),
            username=obj_in.username,
            is_superuser=obj_in.is_superuser,
            # is_active=obj_in.is_active, 
        )
print(db_obj.is_active)
# None


# I have to add the is_active flag explicitly
db_obj = User(
            email=obj_in.email,
            hashed_password=get_password_hash(obj_in.password),
            username=obj_in.username,
            is_superuser=obj_in.is_superuser,
            is_active=obj_in.is_active, 
        )
print(db_obj.is_active)
# True

not sure if it's the issue; however, i don't think you should be *calling* `Boolean`. please try instead, e.g.: `is_active = Column(Boolean, default=True, nullable=False)` i.e. no parens/brackets at the end. — mechanical_meat, Dec 18 '21 at 17:09
Thanks, but that doesn't seem to matter. look at `String(25)` for example. — muon, Dec 18 '21 at 17:23
just tested on SQLite with the way you wrote it, and you are correct: doesn't seem to matter. what RDBMS is it? maybe there's something about that which isn't compatible? — mechanical_meat, Dec 18 '21 at 17:41
i am working with postgres, but shouldn't matter right? because this issue is even before inserting into the DB — muon, Dec 18 '21 at 18:03
well, yeah, that's most likely not a problem then because it's a major DB. i just read in the SQLAlchemy docs that something might be problematic if the DB doesn't have a boolean type. but, for example, SQLite stores a 1 which is fine. — mechanical_meat, Dec 18 '21 at 18:17

muon · Accepted Answer · 2021-12-31T19:16:50.787

In hindsight, super obvious. (isn't everything). There were two issues:

I was checking the value of db_obj before actually inserting it into the database. For whatever reason I though object returned by sqlalchemy Model db_obj = User(...) would have also the default value assigned. Turns out the default value is assigned only after inserting the object into the db.
The second issue that affected my use case was that I didn't flush the session and tried to access obj id before exiting the with session.begin() block, i.e. before committing changes to db.

So, fixing these two points, we get :


from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import sessionmaker

engine = create_async_engine(
        SQLALCHEMY_DATABASE_URI,
        echo=True,
    )
    
# expire_on_commit=False will prevent attributes from being expired
# after commit.
async_session = sessionmaker(
    engine, expire_on_commit=False, class_=AsyncSession
)


obj_in = UserCreate(email=email, password=password, username=username)

db_obj = User(
            email=obj_in.email,
            hashed_password=get_password_hash(obj_in.password),
            username=obj_in.username,
            is_superuser=obj_in.is_superuser,
            # is_active=obj_in.is_active, 
        )


# this was my use case for running tests.
# I start session.begin() in a pytest fixture, so I don't have
# control on exiting the with block to allow db commit. 
# So in this case, a force `flush` is required.
async with async_session() as session, session.begin():
    session.add(db_obj)

    # force commit 
    await session.flush()

    # this assertion fails if above flush is removed.
    assert db_obj.is_active == obj_in.is_active

Why is sqlalchemy not setting default value correctly?

1 Answers1