ORMs & Migrations
Mapping objects to rows without losing sight of the SQL, and changing a schema under a running system.
A Useful Abstraction You Must See Through
The productive stance is to treat it as a query generator you supervise. Use it for the ordinary 90% — inserts, lookups, simple joins — read the SQL it emits, and drop to hand-written SQL for reporting queries and anything performance-critical. An ORM you cannot see through is the one that produces the mystery 4-second endpoint.
| Good at | Bad at |
|---|---|
| CRUD and relationships | Complex analytical queries |
| Type-safe field access | Window functions, CTEs, hints |
| Migrations and schema history | Making query count obvious |
| Portability across engines | Engine-specific features you want |
The N+1 Query Problem
This is the single most common backend performance defect, and ORMs make it effortless to write by accident.
# one query for the orders …
orders = session.query(Order).limit(50).all()
for order in orders:
print(order.customer.name) # … and one more per order. 51 round trips.
# eager load instead: one query, or two
orders = (session.query(Order)
.options(selectinload(Order.customer))
.limit(50).all())| Loading | Behaviour | Use when |
|---|---|---|
| Lazy | Fetches the relation on first access | You rarely touch it |
| Eager (join) | One query with a join | To-one relations |
| Eager (select-in) | A second query with IN (…) | To-many relations — avoids row multiplication |
Connections & Transactions
Opening a database connection is expensive, and databases cap how many they will accept. A pool keeps a fixed set open and lends them out; sizing it is a systems decision, not a default to leave alone.
| Symptom | Cause |
|---|---|
| Requests queue while the database is idle | Pool too small |
| Database refuses connections | Pool × instances exceeds its limit |
| Pool exhausted under load | A connection held across an external API call |
| Intermittent stale-connection errors | No recycle or health check on idle connections |
Keep transactions short and explicit. A transaction open for the duration of a request holds locks far longer than the work needs, and the resulting contention appears as unrelated endpoints getting slower.
Migrations Without Downtime
Schema changes run against a live database while the old code is still serving traffic. That constraint — not the ORM — is what makes migrations hard.
| Rule | Why |
|---|---|
| Migrations are code, in version control | Reviewed, ordered and reproducible |
| Additive first, destructive later | Old and new code must both work mid-deploy |
| Never rename in one step | Add, backfill, dual-write, switch, then drop |
| New columns nullable or defaulted | A NOT NULL without a default fails on existing rows |
| Index concurrently on large tables | A plain CREATE INDEX takes a write lock |
| Backfill in batches | One enormous UPDATE locks the table and bloats the log |
Interview Questions
What is the N+1 query problem?
Fetching a list with one query, then issuing another query per item to load a relation. Eager loading — a join or a select-in — collapses it back to one or two queries.
When should you bypass the ORM?
For reporting and analytical queries, engine-specific features, and any hot path where the generated SQL is not what you want. Use it for ordinary CRUD and read what it emits.
How do you size a connection pool?
By what the database can accept divided across instances, and by how long connections are held. Too small queues requests; pool × instances beyond the server limit causes refusals.
Why not hold a connection across an external API call?
The pool is shared. One slow third-party call with a connection checked out starves every other endpoint on the service.
How do you rename a column with no downtime?
Expand and contract: add the new column, backfill it, dual-write, switch reads, deploy, then drop the old one — because old and new code run simultaneously during a deploy.
Why add new columns as nullable or with a default?
A NOT NULL column with no default fails against existing rows, and on a large table the rewrite takes a lock long enough to be an outage.