ORM vs Raw SQL 🪑

An eternal holy war, where some advocate for "convenience" and others for "control".

🪑 Chair #1: SQLAlchemy ORM

ORM is not about code speed, it's about feature delivery speed. You work with objects, not strings.

✅ Why it's great:
— Unit of Work: Alchemy tracks object changes and pushes them to the database in one transactional batch.
— Security: SQL injections? Forget it. If you don't use .text(), you're protected by default.
— Migrations: Syncing the database schema with your models is pure magic that saves hours of routine work.
— Domain Logic: Ideal for complex e-commerce systems and admin panels where table relationships are more tangled than the plot of "Interstellar".

❌ The catch:
— Overhead: Object mapping is expensive. On large datasets (100k+ rows), you'll feel the Python process eating memory and CPU.
— N+1 Problem: The main performance killer. One forgotten .joinedload() and your service goes down under a hail of small queries.

🪑 Chair #2: Raw SQL / asyncpg (Choice for Highload)

When you hit performance limits, abstractions start to get in the way.

✅ Why it's great:
— Extreme speed: A 2.5x difference is no joke. The binary protocol of asyncpg lets you squeeze the maximum out of the network channel.
— Full control: You write exactly the SQL that goes to the database planner. No extra ORM magic.
— Specific DB features: Try using JSONB or specific Postgres indexes efficiently through ORM — sometimes it becomes a Sisyphean task.

❌ The catch:
— Your responsibility: Forgot to escape parameters? Congratulations, the database is compromised.
— Boilerplate: You'll have to manually map tuples from the database to DTO/Pydantic models. It's tedious and error-prone.
— Maintenance: Reading 500 lines of raw SQL in code six months later is a dubious pleasure.


💡 Verdict: The golden standard is a hybrid approach

You don't have to choose just one. Modern architecture looks like this:
1. SQLAlchemy ORM — for 90% of tasks: CRUD, business logic, migrations, and admin panel.
2. SQLAlchemy Core — when ORM objects are too heavy, but you're not ready to write raw strings yet.
3. Raw SQL (asyncpg) — for bottlenecks: analytical reports, bulk inserts, and microservices with 10k+ RPS load.

Choose your chair based on the load size ☝️

What do you use?
👨‍💻 Only ORM, life is too short to write SQL by hand.
👨‍💻 Only Raw SQL, I don't trust that magic.

#два_стула