Bug: `delete_where` and other methods fail with `InvalidRequestError` when `auto_expunge=True` on deleted objects

[rmonvfer]

Description

When using delete_where(), delete() or delete_many() with auto_expunge=True, the methods raise sqlalchemy.exc.InvalidRequestError: Instance <Model> is not present in this Session

This likely happens because all methods try to expunge objects that have already been deleted and committed, which SQLAlchemy doesn't allow.

These methods work in a similar way, specifically

1. Fetch matching instances
2. Delete them from the database
3. Commit the transaction
4. Attempt to expunge the deleted instances
5. Fail with InvalidRequestError because deleted+committed objects can't be expunged

Ideally, we should handle deleted objects gracefully, either by (a) not attempting to expunge deleted objects, OR (b) checking the object state before expunging.

If we decide to not expunge deleted objects (option a), this would require not calling _expunge() at all in delete operations since deleted objects are effectively detached anyway. The alternative (option b) is to check if the object is deleted before attempting expunge (in _expunge())

def _expunge(self, instance: ModelT, auto_expunge: Optional[bool]) -> None:
    if auto_expunge is None:
        auto_expunge = self.auto_expunge

    if auto_expunge:
        # Check if object is deleted before trying to expunge
        state = sqlalchemy.inspect(instance)
        if not state.deleted:
            self.session.expunge(instance)

URL to code causing the issue

No response

MCVE

import asyncio

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import Mapped, mapped_column, sessionmaker

from advanced_alchemy.base import UUIDBase
from advanced_alchemy.repository import SQLAlchemyAsyncRepository
from advanced_alchemy.service import SQLAlchemyAsyncRepositoryService


class User(UUIDBase):
    name: Mapped[str] = mapped_column()
    active: Mapped[bool] = mapped_column(default=True)


class UserRepository(SQLAlchemyAsyncRepository[User]):
    model_type = User


class UserService(SQLAlchemyAsyncRepositoryService[User, UserRepository]):
    repository_type = UserRepository


async def bug():
    engine = create_async_engine("sqlite+aiosqlite:///:memory:")
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    async with engine.begin() as conn:
        await conn.run_sync(UUIDBase.metadata.create_all)

    async with async_session() as session:
        service = UserService(session=session)
        await service.create_many([
            {"name": "Alice", "active": True},
            {"name": "Bob", "active": False},
            {"name": "Charlie", "active": False}
        ])
        await session.commit()

        # This will raise InvalidRequestError
        try:
            deleted = await service.delete_where(
                User.active == False,  # noqa
                auto_commit=True,      # Commits the deletion
                auto_expunge=True      # Tries to expunge after commit = ERROR
            )
            print(f"Deleted {len(deleted)} users")

        except Exception as e:
            print(f"ERROR: {type(e).__name__}: {e}")
            raise

if __name__ == "__main__":
    asyncio.run(bug())

As expected, this produces the following trace

$ uv run bug.py
ERROR: InvalidRequestError: An invalid request was made.
Traceback (most recent call last):
  File ".venv/lib/python3.13/site-packages/advanced_alchemy/exceptions.py", line 286, in wrap_sqlalchemy_exception
    yield
  File ".venv/lib/python3.13/site-packages/advanced_alchemy/repository/_async.py", line 893, in delete_where
    self._expunge(instance, auto_expunge=auto_expunge)
    ~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.13/site-packages/advanced_alchemy/repository/_async.py", line 1568, in _expunge
    return self.session.expunge(instance) if auto_expunge else None
           ~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^
  File ".venv/lib/python3.13/site-packages/sqlalchemy/ext/asyncio/session.py", line 1285, in expunge
    return self._proxied.expunge(instance)
           ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^
  File ".venv/lib/python3.13/site-packages/sqlalchemy/orm/session.py", line 3309, in expunge
    raise sa_exc.InvalidRequestError(
        "Instance %s is not present in this Session" % state_str(state)
    )
sqlalchemy.exc.InvalidRequestError: Instance <User at 0x10aa46520> is not present in this Session

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "bug.py", line 57, in <module>
    asyncio.run(bug())
    ~~~~~~~~~~~^^^^^^^
  File ".local/share/uv/python/cpython-3.13.0-macos-aarch64-none/lib/python3.13/asyncio/runners.py", line 194, in run
    return runner.run(main)
           ~~~~~~~~~~^^^^^^
  File ".local/share/uv/python/cpython-3.13.0-macos-aarch64-none/lib/python3.13/asyncio/runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^
  File ".local/share/uv/python/cpython-3.13.0-macos-aarch64-none/lib/python3.13/asyncio/base_events.py", line 721, in run_until_complete
    return future.result()
           ~~~~~~~~~~~~~^^
  File "bug.py", line 45, in bug
    deleted = await service.delete_where(
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ...<3 lines>...
    )
    ^
  File ".venv/lib/python3.13/site-packages/advanced_alchemy/service/_async.py", line 1146, in delete_where
    await self.repository.delete_where(
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ...<9 lines>...
    ),
    ^
  File ".venv/lib/python3.13/site-packages/advanced_alchemy/repository/_async.py", line 857, in delete_where
    with wrap_sqlalchemy_exception(
         ~~~~~~~~~~~~~~~~~~~~~~~~~^
        error_messages=error_messages, dialect_name=self._dialect.name, wrap_exceptions=self.wrap_exceptions
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ):
    ^
  File ".local/share/uv/python/cpython-3.13.0-macos-aarch64-none/lib/python3.13/contextlib.py", line 162, in __exit__
    self.gen.throw(value)
    ~~~~~~~~~~~~~~^^^^^^^
  File ".venv/lib/python3.13/site-packages/advanced_alchemy/exceptions.py", line 328, in wrap_sqlalchemy_exception
    raise InvalidRequestError(detail="An invalid request was made.") from exc

Steps to reproduce

Use any repository with auto_expunge=True as default or passed explicitly


await repository.delete_where(
  field=value,
  auto_expunge=True  # Fails here
)

Package Version

Advanced Alchemy version: 1.4.5
SQLAlchemy version: 2.0.40
Python version: 3.13.0
OS: macOS (Darwin 25.0.0)

Platform

• Linux
• Mac
• Windows
• Other (Please specify in the description above)

[rmonvfer]

I forgot to add that setting auto_expunge=False in the MCVE makes the bug go away (as expected)

$ uv run bug.py
Deleted 2 users