Database Access
ORM Basics
Object-Relational Mapping concepts — mapping Python classes to database tables, advantages, trade-offs, and when to use ORM vs raw SQL.
Interview: Database abstraction — understanding ORM trade-offs is important for architecture decisions.
ORM (Object-Relational Mapping) maps Python classes to database tables, class attributes to columns, and instances to rows. Instead of writing SQL, you work with Python objects. ORMs handle SQL generation, parameterization, and result mapping automatically.
ORM Advantages
- No raw SQL: Work with Python objects instead of SQL strings
- Database portability: Switch from SQLite to PostgreSQL with config change
- SQL injection protection: ORMs parameterize queries automatically
- Schema migrations: Tools like Alembic manage database schema changes
- Relationships: Define and query related objects easily
ORM Trade-offs
- Performance overhead: Generated SQL may not be optimal for complex queries
- N+1 query problem: Loading related objects one by one in a loop
- Learning curve: ORM has its own API on top of SQL
- Impedance mismatch: Object-oriented vs relational model differences
- Leaky abstraction: Complex queries still require understanding SQL
Popular Python ORMs
- SQLAlchemy: Most powerful — full SQL toolkit + ORM
- Django ORM: Built into Django framework
- Peewee: Lightweight, simple ORM for small projects
- Tortoise ORM: Async ORM for async frameworks (FastAPI)
- SQLModel: SQLAlchemy + Pydantic (for FastAPI)
Interview Insight
Know the trade-offs: ORMs save development time and prevent SQL injection, but can generate inefficient SQL for complex queries. The N+1 problem is the most common ORM performance issue. Use ORM for CRUD operations, raw SQL for complex reporting queries.
Use Cases
Web applications — Django ORM, SQLAlchemy for standard CRUD
Rapid prototyping — ORM for quick data model changes
Multi-database support — same models work with different databases
Schema migrations — Alembic for managing database changes
Complex applications — hybrid ORM + raw SQL approach
Common Mistakes
N+1 query problem — always use eager loading for related objects
Using ORM for everything — complex queries are often better with raw SQL
Not understanding generated SQL — use echo=True to see what ORM generates
Ignoring the impedance mismatch — not all object patterns map cleanly to tables
Not using transactions — group related operations in a single transaction