Django ORM vs SQLAlchemy Guide

Python offers two mature ORM options: Django's built-in ORM and SQLAlchemy. Each has strengths depending on your project requirements. At ZIRA Software, we use both based on project context.

Key Differences

Django ORM                        SQLAlchemy
├── Tightly coupled to Django     ├── Framework agnostic
├── Active Record pattern         ├── Data Mapper pattern
├── Simpler, opinionated          ├── More flexible, complex
├── Auto migrations               ├── Alembic for migrations
└── Best with Django projects     └── Any Python project

Model Definition

# Django ORM
from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField(unique=True)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ['name']

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')
    published = models.DateField()
    price = models.DecimalField(max_digits=10, decimal_places=2)

    def __str__(self):
        return self.title

# SQLAlchemy
from sqlalchemy import Column, Integer, String, ForeignKey, Date, Numeric, DateTime
from sqlalchemy.orm import relationship, declarative_base
from sqlalchemy.sql import func

Base = declarative_base()

class Author(Base):
    __tablename__ = 'authors'

    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    email = Column(String(255), unique=True, nullable=False)
    created_at = Column(DateTime, server_default=func.now())

    books = relationship('Book', back_populates='author')

class Book(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)
    title = Column(String(200), nullable=False)
    author_id = Column(Integer, ForeignKey('authors.id'), nullable=False)
    published = Column(Date)
    price = Column(Numeric(10, 2))

    author = relationship('Author', back_populates='books')

Querying

# Django ORM - Intuitive, chainable
# Simple queries
books = Book.objects.filter(price__lt=20)
author = Author.objects.get(id=1)

# Complex queries
from django.db.models import Q, Avg, Count

books = Book.objects.filter(
    Q(title__icontains='python') | Q(author__name='John')
).select_related('author')

stats = Author.objects.annotate(
    book_count=Count('books'),
    avg_price=Avg('books__price')
).filter(book_count__gt=5)

# SQLAlchemy - More explicit, flexible
from sqlalchemy.orm import Session
from sqlalchemy import select, func, or_

with Session(engine) as session:
    # Simple queries
    books = session.query(Book).filter(Book.price < 20).all()
    author = session.get(Author, 1)

    # Complex queries
    stmt = (
        select(Book)
        .join(Author)
        .where(
            or_(
                Book.title.ilike('%python%'),
                Author.name == 'John'
            )
        )
    )
    books = session.execute(stmt).scalars().all()

    # Aggregations
    stmt = (
        select(
            Author,
            func.count(Book.id).label('book_count'),
            func.avg(Book.price).label('avg_price')
        )
        .join(Book)
        .group_by(Author.id)
        .having(func.count(Book.id) > 5)
    )

Relationships

# Django - Automatic reverse relations
author = Author.objects.get(id=1)
books = author.books.all()  # Automatic reverse relation

# Prefetch to avoid N+1
authors = Author.objects.prefetch_related('books').all()
for author in authors:
    print(author.books.all())  # No additional queries

# SQLAlchemy - Explicit relationships
author = session.get(Author, 1)
books = author.books  # Defined in relationship

# Eager loading options
from sqlalchemy.orm import joinedload, selectinload

# Join load (single query)
authors = session.query(Author).options(joinedload(Author.books)).all()

# Select in load (two queries)
authors = session.query(Author).options(selectinload(Author.books)).all()

When to Choose Django ORM

Choose Django ORM when:
├── Building a Django application
├── Rapid development priority
├── Simple to moderate queries
├── Team familiarity with Django
└── Built-in admin is valuable

When to Choose SQLAlchemy

Choose SQLAlchemy when:
├── Non-Django project (Flask, FastAPI)
├── Complex database schema
├── Need fine-grained SQL control
├── Working with legacy databases
├── Multiple database backends
└── Unit of Work pattern preferred

Performance Comparison

# Django - Query optimization
# Use only() for partial loading
books = Book.objects.only('title', 'price').all()

# Use defer() to exclude fields
books = Book.objects.defer('content').all()

# SQLAlchemy - More control
from sqlalchemy.orm import load_only

books = session.query(Book).options(
    load_only(Book.title, Book.price)
).all()

Conclusion

Django ORM excels in Django projects with its simplicity and integration. SQLAlchemy offers more flexibility and control for complex requirements or non-Django projects. Choose based on your framework and complexity needs.

Need ORM guidance? Contact ZIRA Software for Python database consulting.