ORMs interview questions
ORMs questions a Backend developer can be asked, from first job to senior. Open one to see the strongest answer and why it works.
You add a field to schema.prisma. What has to happen before the running database knows about it?
The strongest answer: Generate a migration from the schema change and apply it: the tool writes the SQL file, you commit it, and the same file is applied on every environment at deploy
Why it works: Right — the migration file is the artefact: reviewed in the pull request and applied identically everywhere.
- Schema change, then a migration.
- The migration file is the artefact.
- Commit it; apply it on deploy.
- Generating the client is not a migration.
The interviewer asks: “What is an N+1 query, and how do you stop it?” Strongest answer?
The strongest answer: One query for the parent list, then one query per row for children. I fix it with a join, an INCLUDE/fetch-join, or a batched WHERE id IN (…).
Why it works: Exactly. You diagnose it with a query count or a trace, then load the graph in one (or two batched) round-trips.
- N+1 = 1 + one query per row.
- See it in traces or query counts.
- Join, fetch-join, or batch IN.
- Don't “fix” it with more connections.
The interviewer asks: “How do you change a production schema?” Strongest answer?
The strongest answer: Versioned, reviewed migrations that run in deploy. Additive first: add column/table, ship code that writes both, then drop the old. Never edit a migration that already ran.
Why it works: Exactly. Expand/contract is how you avoid locking users out. Migrations are the audit trail; rewrite history and environments diverge.
- Reviewed, versioned migrations.
- Expand, then contract — don't rewrite history.
- No auto-migrate on app boot in prod.
- Don't DROP as a shortcut.
You load an entity, change one property and call save. How does the ORM know which columns to update?
The strongest answer: The session keeps a snapshot of the entity as it was loaded and compares the current values against it when the unit of work flushes, so it writes only what changed on the entities it is tracking
Why it works: Exactly — a snapshot at load, compared at flush. It is also why an entity you detached, or never loaded, is not saved.
- Snapshot at load, compare at flush.
- Only tracked entities get written.
- Tracking costs memory on large reads.
- Read the SQL the ORM generates.
The interviewer asks: “ORM or raw SQL?” Strongest answer?
The strongest answer: ORM for ordinary CRUD and identity mapping. Raw SQL (or a query builder) when the plan, a window function, or a bulk write matters — still parameterised, still in source control.
Why it works: Exactly. It's a judgement call, not a religion. Profile the query; drop to SQL when the ORM fights the planner.
- ORM for CRUD; SQL when the plan matters.
- Read the SQL your ORM emits.
- Keep raw SQL parameterised and reviewed.
- Don't interpolate the request into SQL.
A @Transactional method loads an order and returns the entity. A scheduled job that calls it then reads order.getItems() and gets a LazyInitializationException.
What is happening, and how do you fix it properly?
The strongest answer: The items were never loaded and the persistence context is closed by then — load what the use case needs in the query with a fetch join or an entity graph, or map to a DTO inside the transaction
Why it works: Exactly — decide at query time what the use case needs. A DTO projection also keeps entities out of your API shape.
- Load what the use case needs.
- Fetch join or entity graph in the query.
- Or map to a DTO inside the transaction.
- Eager everywhere is not the fix.
A FastAPI service creates one SQLAlchemy Session at import time and every endpoint uses it. Under load, requests see each other’s uncommitted objects and a failed request poisons the next one.
What is the rule, and why?
The strongest answer: One session per unit of work: created for the request, closed at the end, usually through a dependency that yields it — a Session is a transaction-scoped identity map, not a pool, and not thread-safe
Why it works: Exactly — the session holds identity map and transaction state, which is per unit of work by definition. The engine is the shared, pooled thing.
- One session per unit of work.
- Open per request, close at the end.
- Share the engine, not the session.
- A session is a transaction, not a pool.
The interviewer asks: “You need to rename a column used by two app versions during a rolling deploy. How?” Strongest answer?
The strongest answer: Expand/contract: add the new column, ship code that reads old and writes both, backfill, ship code that reads new, then drop the old. Never rename in one step while mixed versions run.
Why it works: Exactly. Rolling deploys mean two binaries. The schema must be compatible with both. That's the 3–5 migration answer.
- Add, dual-write, backfill, switch reads, drop.
- Two versions run at once — plan for both.
- Don't rename in a single step.
- Don't use crashes as a migration protocol.
The interviewer asks: “Zero-downtime migration of a 200 GB table with a new NOT NULL column — outline it.” Strongest answer?
The strongest answer: Add nullable (or with a default the DB can backfill without a long lock), dual-write, backfill in batches with throttling, then tighten constraints and switch reads. I check lock behaviour per engine — I don't run one giant UPDATE in peak.
Why it works: Exactly. Large tables need batched backfill, lock-aware DDL (Postgres still surprises people), and a compatibility window. That's staff-level schema work.
- Additive DDL, batched backfill, then tighten.
- Know your engine's locks.
- No peak-hour full-table UPDATE.
- Don't leave the invariant only in the ORM.