TopolearnMost production ORM bugs (stale data, half-written changes, DetachedInstanceError) come from misunderstanding the session's transaction lifecycle.
A Session always works inside a transaction. Flush sends pending changes to the database as SQL, inside that transaction, so later queries in the same session see them; commit flushes and then makes everything permanent. Statements executed through the session autoflush first by default (in 2.1, any statement, including text()), which is why a just-added object shows up in a select() before you commit. session.flush() is also how you get a database-generated id without committing.
Two idiomatic shapes:
with Session(engine) as s:...s.commit()for explicit commits.with Session(engine) as s, s.begin():(orwith SessionLocal.begin() as s:) commits when the block ends and rolls back on an exception.
If a flush fails (an IntegrityError, say), the session's transaction is broken: you must call rollback() before using that session again. A rollback also expires all objects so they reload from the database.
Scope: one session per unit of work. In a web app that means one per request, created by the framework integration or a dependency, closed at the end. Don't share sessions across threads; a Session isn't thread-safe.
Objects have states: transient (new), pending (added), persistent (in session with a row), detached (session closed). Lazy-loading or refreshing a detached object raises DetachedInstanceError, which is why returning ORM objects out of a closed session needs care.
Example
from sqlalchemy import create_engine, select
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker
class Base(DeclarativeBase): pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
email: Mapped[str] = mapped_column(unique=True)
engine = create_engine("sqlite://"); Base.metadata.create_all(engine)
SessionLocal = sessionmaker(engine)
with SessionLocal.begin() as s: # commits at the end of the block
u = User(email="a@x.io"); s.add(u)
s.flush() # INSERT sent, still uncommitted
print(u.id) # 1
with SessionLocal() as s:
try:
s.add(User(email="a@x.io")); s.commit() # duplicate email
except IntegrityError:
s.rollback() # required before reusing the session
print("duplicate")
print(s.scalar(select(User.email))) # a@x.ioRemember
- flush sends SQL inside the transaction; commit makes it permanent
- Queries autoflush pending changes first
- After a failed flush, rollback() before reusing the session
- One session per unit of work (e.g. per request), never shared across threads
Watch out for
- Catching an IntegrityError and continuing to use the session without rollback().