Implement a PATCH endpoint for partial SQLAlchemy updates

What's really being asked

This question tests whether you understand the semantic difference between PUT and PATCH at the database layer, and whether you can prevent a partial JSON payload from overwriting existing columns with nulls. It also checks schema hygiene: do you design an independent update model where every field is truly optional, or do you reuse a creation model that requires fields the client does not intend to change. The interviewer is looking for awareness of how SQLAlchemy session identity maps work and why mutating an already loaded instance is safer than merging a detached replacement.

The full answer

First, define a separate update model. In SQLModel this means inheriting directly from SQLModel rather than the base model, and giving every field an optional type with a default of None, so the client need not resend unchanged data. Second, query the existing record by primary key using the SQLAlchemy session and raise an HTTPException with status 404 if the record is missing. Third, extract only the fields explicitly provided in the request by dumping the update model with exclude_unset=True, which strips out keys the client left out entirely. Fourth, iterate over those remaining key-value pairs and use setattr to apply each one to the loaded ORM instance, then commit the transaction and refresh the object before returning it in a public response schema.

The mistakes people make

Reusing the HeroCreate schema for updates, which forces the client to include every field or fail Pydantic validation. Calling model_dump without exclude_unset=True, which turns every missing field into None and clobbers existing database values. Constructing a brand new ORM instance from the payload and merging it into the session, because that approach also overwrites omitted columns with defaults. Forgetting to handle the missing-record case, which should raise an HTTPException with status 404 rather than returning a generic error or None.

What usually comes next

How would you distinguish a field that was omitted from one that was explicitly set to null. What do you do about read-only or computed columns that should never be updated by the client. How would you handle optimistic locking or prevent lost updates when two clients PATCH the same resource simultaneously. Would you extract the setattr loop into a generic utility, and what are the type-safety trade-offs.

A concrete example

A FastAPI path operation receives hero_id and a HeroUpdate payload. Inside a session, it selects the hero with session.get(Hero, hero_id), validates the record exists, then builds update_data = hero_update.model_dump(exclude_unset=True). It loops over each key and value in update_data and calls setattr on the loaded hero object. After the loop it calls session.commit and session.refresh, then returns the refreshed object mapped to a public response schema.