SQLAlchemy Session and Database Restore: Identity Map Serving Stale Rows After Backup Reload

Direct answer

After a database snapshot is restored, calling Session.expunge_all() on the existing session is sufficient to make subsequent queries return the restored rows, provided there are no pending transactions or uncommitted changes in that session. If a transaction is active, it is safer to discard the session and create a new one.

Why this works (likely explanation)

SQLAlchemy's identity map stores ORM instances keyed by their primary key. Session.get() (both 1.4 and 2.0) first looks in this map; if an instance is found it is returned without issuing SQL. Therefore, any object that existed before the restore will be returned from the cache unless it has been removed from the map. expunge_all() removes all instances from the identity map, turning them into detached objects. The next query for a given primary key will miss the map, trigger a SELECT, and construct a fresh instance from the restored row.

Confirmed facts from SQLAlchemy behavior

• Session.get() always checks the identity map before issuing SQL (the lookup logic is unchanged between 1.4 and 2.0).
• Session.expunge_all() clears the map; after it returns, the session holds no strong references to the expunged instances.
• Creating a new Session after the restore also yields a clean identity map, giving the same result as expunging when no transaction is active.

Practical steps for this case

1. Ensure any ongoing transaction is committed or rolled back before the restore.
2. After the backup reload completes, call session.expunge_all().
3. Resume normal queries; they will hit the database and return rows reflecting the restored state.
4. If you cannot guarantee a clean transaction state, replace the session: session = SessionMaker() (or your scoped session factory).

How to verify in a test

# Arrange: populate DB, load object into session
obj_before = session.get(MyModel, pk)
assert obj_before.attr == 'old_value'

# Act: restore backup into the same database (outside SQLAlchemy)
restore_backup()   # your backup/restore procedure

# Verify stale cache returns old data if not expunged
obj_stale = session.get(MyModel, pk)
assert obj_stale.attr == 'old_value'   # still from identity map

# Expunge all
session.expunge_all()

# Act: query again
obj_fresh = session.get(MyModel, pk)
assert obj_fresh.attr == 'restored_value'   # reflects backup data

# Optional: check that identity changed
assert obj_fresh._sa_instance_state.identity_key != obj_before._sa_instance_state.identity_key

If you observe that obj_fresh.attr still equals the old value, the session likely still holds a reference (e.g., you kept obj_before elsewhere) or there is an uncommitted transaction interfering with the expunge. In that case, create a new session and repeat the query.

Missing diagnostic detail that could change the recommendation

Do you have any pending transactions or uncommitted changes in the session at the moment the backup is restored? Knowing this determines whether a simple expunge_all() is safe or whether you must replace the session entirely.