Add support for deferred column loading

[hitman-gdg]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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

from sqlmodel import deferred

class User(sqlmodel, table=True):
    id: Optional[int] = Field(primary_key=True, default=None)
    name: str
    data: Optional[bytes] = Field(deferred=True)

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

from sqlmodel import deferred

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

[eddcorts]

Having same issue, did you find any alternative with base SQLAlchemy or something?

[FilipeMarch]

Did you find a solution?

[lucas-labs]

I think that we can use the new sa_type now, in order to achieve this behaviour.

[ngi]

any news how to use sa_type for deferred loading?

[MarkusDressel]

@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)))

[Fatem-Mq]

Any updates? =(

[shawnmartin-py]

Why anyone would even consider sqlmodel these days is beyond me.
Been fine managing Pydantic and SQLA models independently for the past 3 years.

[shawnmartin-py]

It's a lib that get's hardly any updates, and only 1 person is allowed to merge PR's. 🤦‍♂️

[alessiamarcolini]

Hi! Is there an update on how to make this work? :) Thanks.

[YuriiMotov]

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()

[justanotherbody]

Came 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)

[natikos]

any updates on this issue?