When would you use SQLAlchemy ORM, SQLAlchemy Core, or raw SQL?

Chooses a SQLAlchemy abstraction from the work's shape instead of performance slogans.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

I use the ORM when the operation naturally works with mapped entities, relationships, identity, and a unit of work. I use SQLAlchemy Core when I want composable SQL expressions and explicit row shapes without ORM object state, often for reporting or bulk-oriented paths. Raw SQL is reasonable when a database-specific feature or carefully tuned query is clearer than forcing it through another abstraction. All three still need parameters, transactions, and reviewable SQL.

Full answer below

Practice focus

Chooses a SQLAlchemy abstraction from the work's shape instead of performance slogans.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Overview
Chooses a SQLAlchemy abstraction from the work's shape instead of performance slogans.
I use the ORM when the operation naturally works with mapped entities, relationships, identity, and a unit of work. I use SQLAlchemy Core when I want composable SQL expressions and explicit row shapes without ORM object state, often for reporting or bulk-oriented paths. Raw SQL is reasonable when a database-specific feature or carefully tuned query is clearer than forcing it through another abstraction. All three still need parameters, transactions, and reviewable SQL.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
The choice can vary by use case in one service. A command may update an aggregate through the ORM while a dashboard uses a Core projection or a PostgreSQL-specific query. I do not assume raw SQL is automatically faster; query shape, indexes, round trips, and materialization dominate many problems. I inspect the generated statement and keep result mapping explicit so changing abstractions does not silently change the API contract.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Calling raw SQL inherently faster
Forcing complex reporting through entity loading
Forgetting bulk SQL can bypass ORM state

When would you use SQLAlchemy ORM, SQLAlchemy Core, or raw SQL?

Practice focus

Answer path

Overview

Review signals

Follow-up prompts

Continue practice

Next interviewer turn

Tech stacks

Skill areas

Suggest an improvement

Found this answer useful?