How to properly do a bulk insert and refresh the results without using deprecated features of SQLAlchemy?

[fisher60]

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

from collections.abc import Sequence
from sqlmodel import SQLModel, Field, Session

class FishBase(SQLModel):
    common_name: str | None = Field(default=None)
    scientific_name: str = Field(index=True, unique=True)

class Fish(FishBase, table=True):
    id: int | None = Field(default=None, primary_key=True)


def bulk_create_fish(fish: list[FishBase], session: Session) -> Sequence[Fish]:
    db_fish = [Fish.model_validate(x) for x in fish]
    session.bulk_save_objects(db_fish)  # Deprecated, but no alternative seems to exist for this ORM syntax
    session.commit()

    # Unable to refresh because objects are "not persistent within this Session"
    for each_obj in db_fish:
        session.refresh(each_obj)

    return db_fish

Description

I would like to create a function to bulk insert rows for one of my models and return those rows as a response in FastAPI.

The problems I have found are:

1. The above syntax is explicitly deprecated in SQLAlchemy, therefore I would personally like to avoid it and find the "best practice" for writing a bulk insert with SQLModel.
2. I am unable to refresh the models and therefore cannot generate a response using this "orm" style of syntax. Attempting to refresh raises an exception.

The following solution works, but it feels like a bit of an "anti-pattern" when using SQLModel since I would want SQLModel to do a bit more of the heavy-lifting for the sql statements.

def bulk_create_fish(fish: list[FishBase], session: Session) -> ScalarResult[Fish]:
    db_fish = session.scalars(
        insert(Fish).returning(Fish),
        [Fish.model_validate(x).dict() for x in fish]
    )
    return db_fish

Operating System

Windows

Operating System Details

Windows 10

SQLModel Version

0.0.22

Python Version

Python 3.13.0

Additional Context

I am really enjoying how simple SQLModel makes the dev experience with FastAPI, would love to know if there is a better way to do this that I am not familiar with.

[YaoJusheng]

I encountered a similar problem, has this problem been solved? I am also looking for the best implementation of batch operations that conforms to sqlmodel.

Also, The document sqlalchemy 2.0 I viewed showed the difference between the old and new writing methods. However, I still like the concise call writing method like bulk_save_objects.

Does anyone know of any batch operation solutions for sqlmodel?