What is ORM (Object-Relational Mapping)?

Definition

An ORM (Object-Relational Mapping) is a layer of abstraction between your application code and the database. It maps database tables to classes, rows to objects, and columns to attributes, letting you interact with data using familiar object-oriented patterns instead of SQL.

ORMs handle the translation between the relational model (tables, rows, foreign keys) and the object model (classes, instances, references), bridging the "impedance mismatch" between the two paradigms.

Python ORMs

• SQLAlchemy: The most powerful and flexible Python ORM. Supports both high-level ORM patterns and low-level SQL expression building. Works with any database.
• Django ORM: Built into Django, offering a simpler API with strong PostgreSQL support. Great for rapid development.
• Tortoise ORM: Async-first ORM inspired by Django, designed for use with FastAPI and other async frameworks.
• SQLModel: Created by the FastAPI author, combining SQLAlchemy and Pydantic for type-safe database models.
• Peewee: Lightweight ORM with a simple, expressive API. Good for smaller projects.

When to Use an ORM

Benefits:

• Faster development - write less code for common database operations
• Database portability - switch databases with minimal code changes
• Security - parameterized queries prevent SQL injection by default
• Maintainability - models serve as documentation of your schema

Drawbacks:

• Performance overhead for complex queries
• N+1 query problem if relationships aren't loaded properly
• Learning curve for advanced query optimization
• Can hide important SQL details from developers

The best approach is using an ORM for standard CRUD operations while dropping to raw SQL for complex analytical queries.

Code Example

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, DeclarativeBase

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    posts = relationship('Post', back_populates='author')

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    title = Column(String(200))
    author_id = Column(Integer, ForeignKey('users.id'))
    author = relationship('User', back_populates='posts')

# Query: Get user with all their posts
user = session.query(User).filter_by(name="Maciej").first()
for post in user.posts:
    print(post.title)

Frequently Asked Questions

Should I use an ORM or raw SQL?

Use both. ORMs are excellent for CRUD operations, migrations, and maintaining clean code. Use raw SQL (or the ORM's expression language) for complex queries, reporting, and performance-critical operations. SQLAlchemy uniquely supports both approaches in the same project.