Question
Identity-map reads vs. explicit expire/refresh when verifying externally restored rows in SQLAlchemy ORM
Sky River
0 reputation · 25 Sept 2023, 20:24 UTC
125.4K views1
Loading…
0 reputation · 25 Sept 2023, 20:24 UTC
After restoring a database externally, an application needs to confirm that a specific row recovered from backup matches the restored database. SQLAlchemy ORM Sessions cache loaded instances in an identity map, so session.query(Model).get(pk) can return a pre-restore object without issuing a SELECT. Two documented approaches exist: rely on the identity-map read for speed, or force a fresh read using session.expire(instance) or session.refresh(instance).
The trade-off is not obvious. A cached read may silently return stale data, while refresh() immediately issues a SELECT and overwrites in-memory attributes, discarding uncommitted local changes. Calling session.expire_all() forces fresh reads on next access but can trigger a burst of SELECT statements for large object graphs. Core Connection.execute(select(...)) results bypass ORM identity-map caching and always reflect the current database state, but they do not update the ORM instance. Exact session API behavior varies between SQLAlchemy 1.4 and 2.0, so the installed major version matters.
For a post-restore verification step that must not disturb unsaved working objects, is it safer to compare a Core result row against the ORM instance, or to expire/refresh the instance and accept the query cost? Does the answer change between SQLAlchemy 1.4 and 2.0 session APIs?
For a post‑restore verification that must not disturb unsaved objects, the safest approach is to refresh the ORM instance (session.refresh) or, if you can tolerate a manual check, compare a Core SELECT result with the instance’s attributes.
The ORM identity map caches the object loaded before the restore. Calling session.query(Model).get(pk) may return that cached instance, which still holds the pre‑restore state, so a comparison can be stale even though no SELECT was issued.
sqlalchemyrow = session.execute(select(Model).where(Model.id == pk)).first()session.refresh(instance) before the comparison. This issues a single SELECT, updates the instance, and discards any pending local modifications.In SQLAlchemy 1.4, session.refresh(instance) performs a SELECT and updates the instance, while session.expire(instance) only marks it as expired, causing lazy loads on subsequent access. In SQLAlchemy 2.0 the API is unchanged, but the default behavior of session.flush() now auto‑expires instances, so a manual refresh is still required to guarantee freshness without affecting uncommitted work. Using session.expire_all() forces a SELECT on every attribute access and can generate a burst of queries, which is undesirable for a single verification step.
If you can afford one extra SELECT, session.refresh(instance) is the cleanest way to guarantee the ORM instance matches the restored row while preserving any unsaved changes until you flush. If you must avoid any query, compare the Core result row to the instance’s attributes, but be aware that the instance may still contain stale data if it was loaded before the restore.
Is the instance part of an uncommitted transaction that you intend to keep?
Use comments to ask for clarification. Post a solution as an answer.
35,775 reputation · 26 Sept 2023, 05:54 UTC
One option that avoids disturbing dirty state entirely is to run the verification in a separate short-lived Session—it always issues a fresh SELECT and never touches your working session's identity map. If you prefer to stay in the same session, SQLAlchemy 2.0's session.execute(select(Model), execution_options={"populate_existing": True}) forces re-population of that specific row, though like refresh() it overwrites unflushed changes. To verify behavior in your installed version, set engine.echo = True and confirm whether Session.get() (2.0) or Query.get() (1.4 legacy) actually emits SQL when the instance is already loaded.