Repository navigation
Add support for deferred column loading #97
Description
Activity
- changed the title
[-]Add support for diferred column loading[/-][+]Add support for deferred column loading[/+]on Sep 14, 2021 Having same issue, did you find any alternative with base SQLAlchemy or something?
Did you find a solution?
I think that we can use the new
sa_typenow, in order to achieve this behaviour.any news how to use sa_type for deferred loading?
@tiangolo can you provide some information if / how it possible to leverage SQLalchemy's deferred loading mechanism in SQLModel ?
I also could not find a way to make deferred loading on column level (not on query level) work. sa_column setting is not recognized by SQLModel and still loads the deferred column
blob: bytes | None = Field(default=None, sa_column=deferred(Column(LargeBinary)))
Reacted by chentt, Vinicius Abade, Kalel L. Martinho, matan-prime, Fatem From DailyDoses, Ridwan Mulyadi and Lars Alexander BlumbergAny updates? =(
Why anyone would even consider sqlmodel these days is beyond me.
Been fine managing Pydantic and SQLA models independently for the past 3 years.It's a lib that get's hardly any updates, and only 1 person is allowed to merge PR's. 🤦♂️
Hi! Is there an update on how to make this work? :) Thanks.
It seems that for now there is no way to declare model with the deferred field, and we can only defer column loading by specifying the option for query:
session.get(User, 1, options=[defer(User.data)])
or
u = session.exec( select(User).where(col(User.id) == 1).options(defer(User.data)) ).one()
Reacted by Ilia BurakovCame looking for support, found this issue, determined a workaround...
from typing import List import sqlalchemy import sqlalchemy.orm import sqlmodel def defer_columns(*columns: List[str]): def defer_columns_for_sqlmodel_class(cls): mapper = sqlalchemy.inspect(cls) for column in columns: prop = mapper.get_property(column) prop.deferred = True new_strategy = [] # we expect strategy_key to be a tuple of (name, value) pairs but we're # going to be extra careful here... for strategy in prop.strategy_key: if ( isinstance(strategy, tuple) and len(strategy) == 2 and isinstance(strategy[0], str) and strategy[0] == "deferred" ): new_strategy.append(("deferred", True)) else: new_strategy.append(strategy) prop.strategy_key = tuple(new_strategy) # normally invoked automatically, but you might need to do this explicitly if # you're up to really unusual things... # sqlalchemy.orm.configure_mappers() return cls return defer_columns_for_sqlmodel_class @defer_columns("deferred_by_default") class Foo(sqlmodel.SQLModel, table=True): id: int = sqlmodel.Field(primary_key=True) always_loaded: str deferred_by_default: str def get_attrs(model): "Return underling attributes." return model._sa_instance_state.dict # test_engine is a pytest fixture with a working test DB connection. YMMV def test_deferred(test_engine): session = sqlmodel.Session(test_engine) session.add(Foo(id=1, always_loaded="Happy", deferred_by_default="Sad")) session.commit() session.expire_all() select = sqlmodel.select(Foo).where(Foo.id == 1) explicitly_deferred = session.exec( select.options(sqlalchemy.orm.defer(Foo.deferred_by_default)) ).one() assert "always_loaded" in get_attrs(explicitly_deferred) assert "deferred_by_default" not in get_attrs(explicitly_deferred) session.expire_all() explicitly_loaded = session.exec( select.options(sqlalchemy.orm.undefer(Foo.deferred_by_default)) ).one() assert "deferred_by_default" in get_attrs(explicitly_loaded) session.expire_all() foo = session.exec(select).one() assert "deferred_by_default" not in get_attrs(foo)
Reacted by Alessia Marcolini, Hank, Will Blaschko and UncleDrew92any updates on this issue?
- locked and limited conversation to collaborators
on May 18, 2026
First Check
Commit to Help
Example Code
Description
The ability to be able to included deferred sql columns
Wanted Solution
I would like to be able to work with sqlalchemy deferred columns, so that certain columns are not loaded by default as part of a model
Wanted Code
Alternatives
No response
Operating System
macOS
Operating System Details
No response
SQLModel Version
0.0.4
Python Version
3.9.6
Additional Context
No response