A SQLAlchemy consumer calls session.merge() on a Product rebuilt from a queue message, then edits that object; why are the edits never written?
answer
- copy, not attach
- use the return value
- SELECT by primary key first
- the source stays outside the Session
- add() would try an INSERT
basics
~20 sSession.merge() copies the incoming object's state onto the Session's own persistent Product, loaded by primary key, and returns that copy. The object passed in never joins the Session, so later edits to it are not tracked; edit the returned object.
solid answer
~40 s`session.merge(incoming)` reads the primary key of `incoming`, finds the matching object in the identity map or, with the default `load=True`, SELECTs it, creates a new pending object if no row exists, copies every attribute that was set on `incoming` onto it, and **returns it**. `incoming` itself is left as it was: transient or detached, outside the Session. Code that calls `session.merge(incoming)` and keeps using `incoming` edits an untracked object, so those changes never reach an UPDATE. Write `product = session.merge(incoming)` and edit `product`. `session.add(incoming)` is no fix: the Session treats it as new and flushes an INSERT that the primary key constraint rejects with `IntegrityError`. `merge(load=False)` is not a shortcut either; it skips the SELECT and the change history, so it suits trusted clean objects such as a cache, not writes.
code
python · 31 linesfrom sqlalchemy import String, create_engine, inspect
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
class Base(DeclarativeBase):
pass
class Product(Base):
__tablename__ = "product"
id: Mapped[int] = mapped_column(primary_key=True)
sku: Mapped[str] = mapped_column(String(20), unique=True)
stock: Mapped[int]
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
session.add(Product(id=5, sku="A-5", stock=10))
with Session(engine) as session, session.begin():
incoming = Product(id=5, stock=7) # rebuilt from a queue message
product = session.merge(incoming) # SELECT by primary key, copy state
print(product is incoming) # False
print(inspect(incoming).transient, inspect(product).persistent) # True True
print(product.sku, product.stock) # A-5 7 (sku was never set on incoming)
incoming.stock = 1000 # lost: incoming is not in the Session
product.stock -= 1 # tracked: UPDATE at commit
with Session(engine) as session:
print(session.get(Product, 5).stock) # 6go deeper
Recall that merge() returns the object you must keep using, and that the object you passed in is not added to the Session.
Explain how merge() finds its target by primary key, SELECTs on a miss, copies set attributes as change history and returns the copy.
Spot a discarded merge() result in review, explain the IntegrityError that add() causes instead, and know when load=False or make_transient_to_detached() is safe.
Decide whether consumers should merge whole rebuilt objects or load and set fields, given that merge() copies stale data over newer rows unless the write path checks for that.
## What Session.merge() does, step by step `Session.merge(instance, *, load=True, options=None)` exists to bring the **state** of an object from outside the Session into it. With the default `load=True`, for an incoming `Product(id=5, stock=7)` rebuilt from a queue message: 1. The Session autoflushes, so the lookup that follows sees its pending changes. 2. It reads the primary key of the incoming object. If there is none, it creates a new instance, which will be INSERTed. 3. It looks for the identity key `(Product, (5,), None)` in the identity map. On a miss it SELECTs row 5 by primary key, applying any loader `options` passed in. If no row exists, it creates a new **pending** `Product`. 4. It copies each attribute that is present on the incoming object onto the target through the normal attribute machinery, so the changes are recorded as history and produce an UPDATE at the next flush. 5. It cascades the same operation along relationships whose `cascade` includes `merge`, which the default `"save-update, merge"` does. 6. It **returns the target**, the Session's persistent (or pending) object. The incoming object is never modified and never added to the Session; the same object can be merged into any number of Sessions. ## The bug in the consumer ```python session.merge(incoming) # return value thrown away incoming.stock -= 1 # edits an object no Session tracks session.commit() # UPDATE carries only what merge() copied ``` The decrement happened on `incoming`, which is transient: no Session watches it, so the flush knows nothing about it. The fix is one assignment, `product = session.merge(incoming)`, after which every edit goes to `product`. A useful review rule: a bare `session.merge(...)` statement whose result is discarded is almost always a defect. ## merge() versus add() | | `session.add(incoming)` | `product = session.merge(incoming)` | |---|---|---| | Object tracked afterwards | `incoming` itself | the Session's own object for that key | | Row 5 already exists, not loaded | INSERT at flush, rejected with `IntegrityError` on the primary key | SELECT, then UPDATE of the changed columns | | Row 5 already loaded in this Session | INSERT at flush, rejected the same way | no SELECT; the loaded object is updated and returned | | SQL at call time | none | autoflush, plus a SELECT on an identity-map miss | | Incoming object afterwards | pending in the Session | unchanged and outside the Session | If `incoming` is already persistent in this same Session, `merge()` simply returns it. ## Which attributes are copied - **Set on the incoming object**: copied, including an explicit `None`, which becomes `NULL` in the UPDATE. - **Never set**: not copied. The target keeps the value it already has, or loads it from the row. With a plain declarative class, `Product(id=5, stock=7)` leaves `sku` untouched. - **2.1 dataclass mappings**: a `MappedAsDataclass` field that has a `default` but was not passed to the constructor is no longer placed in `__dict__`, so `merge()` leaves that column alone too. - **Relationships**: followed only along `merge` cascades; each related object reached that way is merged by its own primary key, so related rows are looked up, copied and returned as the Session's own objects too. ## When merge() is the right tool - **Re-importing a structure keyed by primary key**, such as a file or a message that carries full rows: each run loads the stored row and updates it with whatever changed. - **Moving objects between Sessions**, for example from a parent thread's Session to a worker's, where each worker needs its own copy. - **Restoring objects from a cache**, usually with `load=False`, so the cached object stays detached and each Session gets a local copy. ## load=False and the alternatives - **`merge(load=False)`** skips the SELECT and emits no history: the values are stamped onto the target as if loaded, so **no UPDATE follows**. It requires a clean object that already has a database identity; a freshly constructed object is rejected with `InvalidRequestError` ("merge() with load=False option does not support objects transient ..."). It fits copying objects from a cache or another thread's Session. - **`make_transient_to_detached()`** turns a transient object with its primary key set into a detached one without SQL, which `add()` or `merge(load=False)` will then accept. - **Load and set** (`product = session.get(Product, 5)` then assign fields) is often clearer than `merge()` when the message carries only a few fields. Note that `merge()` is not a concurrency check: it copies whatever arrived over whatever is stored, so a stale message overwrites newer data. Guarding against that is optimistic or pessimistic locking, which the ORM concepts material on locking covers.
- Why does SQLAlchemy reject session.merge(Product(id=5, stock=7), load=False)?`load=False` stamps state onto the target without a SELECT or history, so it only accepts clean objects that already have a database identity. A freshly built object is transient and raises `InvalidRequestError`. `make_transient_to_detached()` can turn it into a detached object first, but even then no UPDATE follows, because `load=False` records no changes.
- In SQLAlchemy 2.1, why does merging a MappedAsDataclass Product built without sku leave the stored sku alone?In 2.1 a dataclass field with a `default` that is not passed to the constructor is delivered through a descriptor and not stored in `__dict__`. `merge()` copies only attributes present in the incoming object's `__dict__`, so `sku` is skipped. Under 2.0 the default sat in `__dict__` and was copied over the stored value.
- session.merge(obj) attaches obj itself to the Session, the way add() does.
- merge() never touches the database; it only copies attributes in memory.
- session.add() of an object whose primary key exists issues an UPDATE.
- merge() sets every attribute the incoming object did not supply to NULL.
- merge(load=False) is a faster drop-in for any merge() call.
Assumes SQLAlchemy 2.1; merge() itself works as in 2.0, but 2.1 stopped placing unset dataclass defaults in __dict__, which changes what merge() copies for MappedAsDataclass classes.