ORM vs RAW SQL in Python frameworks

When it comes to working with databases in Python, developers often find themselves at a crossroads: should they use an Object-Relational Mapping (ORM) tool or stick with good old RAW SQL queries?

This question is especially relevant when working with popular Python frameworks like Django and FastAPI.

In this article, we’ll explore into both approaches, their pros and cons, and see how they stack up in different scenarios.

What is an ORM?

Before we jump into the article, let’s break down what an ORM actually is. Think of an ORM as a magical translator between your Python code and your database. It lets you work with database tables as if they were Python objects. Instead of writing SQL queries, you can use Python methods and attributes to interact with your data. It’s like having a friendly robot that speaks both Python and SQL, doing all the translation work for you.

The ORM approach

Advantages of using ORM

1. Speaks your language: With an ORM, you can stick to Python syntax. No need to switch gears and think in SQL.
2. Keeps things tidy: ORMs encourage a clean, organized way of structuring your database interactions.
3. Database independence: Want to switch from MySQL to PostgreSQL? With an ORM, it’s often as simple as changing a configuration file.
4. Security boost: ORMs usually handle things like SQL injection protection out of the box.
5. Time-saver: For simple to moderately complex queries, ORMs can significantly speed up development time.

Disadvantages of ORM

1. Performance overhead: ORMs add an extra layer between your code and the database, which can slow things down a bit.
2. Learning curve: Each ORM has its own way of doing things, which means more stuff to learn.
3. Complex query challenges: When you need to write really complex queries, ORMs can sometimes feel like they’re getting in the way.

RAW SQL

Advantages of RAW SQL

1. Full control: You get to tell the database exactly what you want, how you want it.
2. Performance: For complex queries, hand-crafted SQL often performs better than ORM-generated queries.
3. Database-specific features: Some cool database features might only be accessible through RAW SQL.
4. Clarity: Sometimes, seeing the actual SQL can make it clearer what’s happening under the hood.

Disadvantages of RAW SQL

1. More code to manage: You’ll be writing and maintaining a lot more SQL strings in your Python code.
2. Security risks: If you’re not careful, you might leave yourself open to SQL injection attacks.
3. Database dependence: Switching databases becomes trickier when you’re using RAW SQL.

Django

Django comes with a powerful ORM that’s deeply integrated into the framework. Let’s look at how it handles both approaches:

Django ORM in action

# Creating a new user
new_user = User.objects.create(username='coolcoder', email='[email protected]')

# Querying users
active_users = User.objects.filter(is_active=True)

# Updating a user
user = User.objects.get(username='coolcoder')
user.email = '[email protected]'
user.save()

Django RAW SQL

from django.db import connection

def get_active_users():
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM auth_user WHERE is_active = %s", [True])
        return cursor.fetchall()

Django’s ORM is feature-rich and handles a wide range of database operations smoothly.

However, it also provides ways to use RAW SQL when needed, giving you the best of both worlds.

FastAPI: flexible and fast

FastAPI doesn’t come with its own ORM, but it plays nice with various database tools. Many FastAPI developers use SQLAlchemy as their ORM of choice.

SQLAlchemy (ORM) with FastAPI

from sqlalchemy.orm import Session
from . import models

def get_user(db: Session, user_id: int):
    return db.query(models.User).filter(models.User.id == user_id).first()

def create_user(db: Session, user: schemas.UserCreate):
    db_user = models.User(email=user.email, hashed_password=user.password)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

RAW SQL with FastAPI

from databases import Database

database = Database("postgresql://user:password@localhost/dbname")

async def get_active_users():
    query = "SELECT * FROM users WHERE is_active = :is_active"
    return await database.fetch_all(query=query, values={"is_active": True})

FastAPI’s flexibility allows developers to choose their preferred database approach, whether it’s using an ORM like SQLAlchemy or going with RAW SQL queries.

When to use what?

Choose ORM when:

1. You’re working on a project where development speed is crucial.
2. Your database operations are relatively straightforward.
3. You want your code to be database-agnostic.
4. You’re working in a team where not everyone is a SQL expert.

Go for RAW SQL when:

1. You need to squeeze out every bit of performance from your database.
2. You’re dealing with extremely complex queries that ORMs struggle with.
3. You want to use database-specific features that your ORM doesn’t support.
4. You’re more comfortable writing SQL than learning an ORM’s syntax.

The hybrid approach

In reality, many projects end up using a mix of ORM and RAW SQL. They use the ORM for most operations but switch to RAW SQL for performance-critical or complex parts of the application. Both Django and FastAPI support this hybrid approach, allowing developers to leverage the strengths of both methods.

Conclusion

The choice between ORM and RAW SQL isn’t about picking a winner – it’s about choosing the right tool for the job. ORMs offer convenience and speed of development, while RAW SQL provides power and control. Whether you’re building a blog with Django or a high-performance API with FastAPI, understanding the strengths and weaknesses of both approaches will make you a more effective Python developer.