Django: Raw SQL Queries

Raw SQL queries in Django allow developers to execute custom SQL directly against the database, bypassing the Object-Relational Mapping (ORM) when its abstractions are insufficient for complex or highly specific queries. While Django’s ORM is powerful, raw SQL offers flexibility for advanced use cases, performance optimization, or database-specific features. This tutorial explores raw SQL queries in Django, covering execution methods, parameterization, security considerations, and practical applications for web applications.

---

01. Why Use Raw SQL Queries?

Raw SQL queries are useful when Django’s ORM cannot express a query efficiently or when leveraging database-specific features (e.g., advanced joins, window functions, or full-text search). They are critical for optimizing performance in analytics-heavy applications, integrating legacy databases, or executing complex aggregations that exceed ORM capabilities. However, raw SQL reduces portability and requires careful handling to prevent security risks like SQL injection.

Example: Basic Raw SQL Query

# myapp/models.py
from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100)
    price = models.DecimalField(max_digits=8, decimal_places=2)
    stock = models.PositiveIntegerField()

    def __str__(self):
        return self.name

# Raw SQL query (e.g., in shell or view)
from myapp.models import Product

products = Product.objects.raw('SELECT * FROM myapp_product WHERE price > %s', [100])

# Print results
for product in products:
    print(product.name, product.price)

Output: (Assuming sample data)

Laptop 999.99
Phone 499.99

Explanation:

• raw() - Executes the SQL query and maps results to Product model instances.
• [100] - Parameterizes the query to prevent SQL injection.

---

02. Key Methods for Raw SQL Queries

Django provides several methods to execute raw SQL, each suited to different use cases, from model-based queries to direct database connections. The table below summarizes key approaches and their applications:

Method	Description	Use Case
Model.objects.raw()	Execute SQL and map to model instances	Retrieve model-compatible data
connection.cursor()	Direct database cursor for raw SQL	Non-model queries or updates
extra()	Add raw SQL to ORM queries (deprecated)	Legacy code or specific filters

2.1 Using Model.objects.raw()

Example: Raw Query with Joins

# myapp/models.py
from django.db import models

class Category(models.Model):
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name

class Product(models.Model):
    name = models.CharField(max_length=100)
    category = models.ForeignKey(Category, on_delete=models.CASCADE, related_name='products')
    price = models.DecimalField(max_digits=8, decimal_places=2)

    def __str__(self):
        return self.name

# Raw SQL with join
from myapp.models import Product

products = Product.objects.raw(
    '''
    SELECT p.id, p.name, p.price, c.name as category_name
    FROM myapp_product p
    JOIN myapp_category c ON p.category_id = c.id
    WHERE p.price > %s
    ''',
    [500]
)

# Print results
for product in products:
    print(product.name, product.price, product.category_name)

Output: (Assuming sample data)

Laptop 999.99 Electronics
Phone 599.99 Electronics

Explanation:

• raw() - Maps SQL results to Product instances, with category_name as an extra attribute.
• The query joins myapp_product and myapp_category tables.

2.2 Using Database Cursor

Example: Raw SQL with Cursor

from django.db import connection

# Execute raw SQL using a cursor
def get_high_value_products(min_price):
    with connection.cursor() as cursor:
        cursor.execute(
            '''
            SELECT name, price
            FROM myapp_product
            WHERE price > %s
            ORDER BY price DESC
            ''',
            [min_price]
        )
        return cursor.fetchall()

# Fetch results
results = get_high_value_products(100)

# Print results
for name, price in results:
    print(name, price)

Output: (Assuming sample data)

Laptop 999.99
Phone 599.99
Tablet 199.99

Explanation:

• connection.cursor() - Provides direct access to the database for non-model queries.
• fetchall() - Returns raw tuples, requiring manual processing.

2.3 Using extra() (Deprecated)

Example: Using extra() for Legacy Code

from myapp.models import Product

# Add raw SQL to ORM query (deprecated)
products = Product.objects.filter(price__gt=100).extra(
    select={'category_name': 'SELECT name FROM myapp_category WHERE myapp_category.id = myapp_product.category_id'}
)

# Print results
for product in products:
    print(product.name, product.price, product.category_name)

Output: (Assuming sample data)

Laptop 999.99 Electronics
Phone 599.99 Electronics

Explanation:

• extra() - Adds raw SQL to ORM queries but is deprecated in favor of raw() or annotations.
• Use with caution in legacy projects; prefer modern alternatives.

2.4 Incorrect Raw SQL Usage

Example: SQL Injection Risk

from myapp.models import Product

# Unsafe: Vulnerable to SQL injection
min_price = "100; DROP TABLE myapp_product;"  # Malicious input
query = f"SELECT * FROM myapp_product WHERE price > {min_price}"
products = Product.objects.raw(query)

# Print results (would fail or cause damage)
for product in products:
    print(product.name)

Output:

Potential database corruption or error due to SQL injection.

Explanation:

• Direct string interpolation (e.g., f"...") allows SQL injection.
• Solution: Always use parameterized queries with %s and a parameter list.

---

03. Effective Usage

3.1 Recommended Practices

• Use parameterized queries to ensure security.

Example: Secure Raw SQL with Complex Logic

# myapp/models.py
from django.db import models

class Customer(models.Model):
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name

class Order(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name='orders')
    product = models.ForeignKey('Product', on_delete=models.CASCADE, related_name='orders')
    quantity = models.PositiveIntegerField()

    def __str__(self):
        return f"Order {self.id}"

class Product(models.Model):
    name = models.CharField(max_length=100)
    category = models.ForeignKey('Category', on_delete=models.CASCADE, related_name='products')
    price = models.DecimalField(max_digits=8, decimal_places=2)

    def __str__(self):
        return self.name

class Category(models.Model):
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name

# Secure raw SQL query with multiple joins
from myapp.models import Order

orders = Order.objects.raw(
    '''
    SELECT o.id, o.quantity, p.name as product_name, c.name as customer_name, cat.name as category_name
    FROM myapp_order o
    JOIN myapp_product p ON o.product_id = p.id
    JOIN myapp_customer c ON o.customer_id = c.id
    JOIN myapp_category cat ON p.category_id = cat.id
    WHERE p.price > %s AND cat.name = %s
    ''',
    [500, 'Electronics']
)

# Print results
for order in orders:
    print(order.customer_name, order.product_name, order.quantity, order.category_name)

Output: (Assuming sample data)

Alice Laptop 2 Electronics
Bob Phone 1 Electronics

• [500, 'Electronics'] - Safely parameterizes the query.
• Aliases (e.g., product_name) map to model attributes.

3.2 Practices to Avoid

• Avoid raw SQL for queries expressible via the ORM.

Example: Unnecessary Raw SQL

from myapp.models import Product

# Unnecessary raw SQL
products = Product.objects.raw('SELECT * FROM myapp_product WHERE stock > %s', [0])

# Print results
for product in products:
    print(product.name, product.stock)

Output: (Assuming sample data)

Laptop 50
Phone 100

• Simple queries like this are better handled by Product.objects.filter(stock__gt=0).
• Raw SQL reduces portability and increases maintenance overhead.

---

04. Common Use Cases

4.1 E-Commerce Sales Analysis

Perform complex sales analysis with raw SQL for performance.

Example: Sales Analysis with Raw SQL

from myapp.models import Order

# Raw SQL for sales analysis
orders = Order.objects.raw(
    '''
    SELECT o.id, c.name as customer_name, p.name as product_name, 
           (o.quantity * p.price) as total_cost
    FROM myapp_order o
    JOIN myapp_customer c ON o.customer_id = c.id
    JOIN myapp_product p ON o.product_id = p.id
    JOIN myapp_category cat ON p.category_id = cat.id
    WHERE cat.name = %s AND p.price > %s
    ORDER BY total_cost DESC
    ''',
    ['Electronics', 100]
)

# Print results
for order in orders:
    print(order.customer_name, order.product_name, order.total_cost)

Output: (Assuming sample data)

Alice Laptop 1999.98
Bob Phone 599.99

Explanation:

• Calculates total_cost in SQL for efficiency.
• Joins multiple tables to filter by category and price.

4.2 Blog Post Metrics with Window Functions

Use raw SQL to leverage database-specific features like window functions.

Example: Ranking Posts by Category

# blog/models.py
from django.db import models

class Category(models.Model):
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name

class Post(models.Model):
    title = models.CharField(max_length=200)
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True, related_name='posts')
    views = models.PositiveIntegerField(default=0)

    def __str__(self):
        return self.title

# Raw SQL with window function
from blog.models import Post

posts = Post.objects.raw(
    '''
    SELECT p.id, p.title, p.views, c.name as category_name,
           RANK() OVER (PARTITION BY p.category_id ORDER BY p.views DESC) as rank
    FROM blog_post p
    JOIN blog_category c ON p.category_id = c.id
    WHERE p.views > %s
    ''',
    [100]
)

# Print results
for post in posts:
    print(post.title, post.category_name, post.views, post.rank)

Output: (Assuming sample data)

Tech Trends Tech 500 1
Gadgets 101 Tech 300 2
Style Tips Lifestyle 200 1

Explanation:

• RANK() OVER - Ranks posts within each category by views (PostgreSQL-specific).
• Raw SQL is necessary as Django’s ORM does not natively support window functions.

---

Conclusion

Raw SQL queries in Django provide a powerful escape hatch for complex or database-specific operations when the ORM falls short. Key takeaways:

• Use raw() for model-mapped queries and connection.cursor() for non-model results.
• Always parameterize queries to prevent SQL injection.
• Reserve raw SQL for cases where ORM performance or functionality is insufficient.
• Leverage raw SQL for advanced analytics, legacy integrations, or database-specific features.

With careful use, raw SQL queries enhance Django’s flexibility, enabling high-performance data operations for sophisticated web applications!