Raw SQL in Django is an advanced topic that becomes important when you move beyond the most common ORM patterns and start thinking more directly about how your application interacts with the database engine itself. One of Django’s greatest strengths is its ORM, which allows developers to work with database tables using Python classes and QuerySets instead of writing SQL by hand for every operation. In most cases, the ORM is not only more readable but also safer, more maintainable, and portable across database systems. That is why Django developers are generally encouraged to use the ORM first. However, there are still situations where raw SQL becomes useful, necessary, or at least worth considering. These include highly specialized queries, database-specific features, performance-sensitive reporting, complex joins or aggregations that feel unnatural in the ORM, legacy SQL already used by an organization, or debugging situations where understanding the exact SQL matters. Learning raw SQL in Django does not mean abandoning the ORM. It means understanding how to work closer to the database when the situation calls for it.
The first thing to understand is that Django does not treat raw SQL as forbidden. It provides several official ways to execute SQL safely and integrate the results into your application. At the same time, Django strongly encourages caution because raw SQL comes with risks. It can reduce portability between databases, make code harder to maintain, bypass some ORM protections, and create serious security problems if user input is handled incorrectly. The biggest danger is SQL injection, which happens when untrusted input is inserted directly into a query string in an unsafe way. So the real goal of this tutorial is not only to show how to execute SQL, but to show how to do it correctly, safely, and only when it genuinely adds value.
To start with the big picture, Django offers two main approaches for raw SQL work. The first is using Model.objects.raw() when you want raw SQL results mapped back to model instances. The second is using a database cursor through django.db.connection when you want lower-level control and are not necessarily returning model instances. These two tools serve different purposes. The raw() method is helpful when you still want the results to behave like model objects. A cursor is helpful when you need full SQL control, such as inserts, updates, deletes, reporting queries, joins that do not map neatly to one model, stored procedures, or specialized database features.
Let us begin with a simple model setup:
from django.db import models
from django.contrib.auth.models import User
class Category(models.Model):
name = models.CharField(max_length=100)
def __str__(self):
return self.name
class Article(models.Model):
title = models.CharField(max_length=200)
content = models.TextField()
category = models.ForeignKey(Category, on_delete=models.CASCADE, related_name='articles')
author = models.ForeignKey(User, on_delete=models.CASCADE, related_name='articles')
published = models.BooleanField(default=False)
views = models.PositiveIntegerField(default=0)
created_at = models.DateTimeField(auto_now_add=True)
def __str__(self):
return self.titleWith these models, the normal ORM way to get published articles would be:
articles = Article.objects.filter(published=True).order_by('-created_at')This is usually the best option. It is readable, safe, and portable. But let us imagine that for learning purposes, you want to retrieve the same data with raw SQL. Django allows this through raw():
articles = Article.objects.raw(
"SELECT * FROM app_article WHERE published = %s ORDER BY created_at DESC",
[True]
)This is a RawQuerySet. You can iterate over it like this:
for article in articles:
print(article.title, article.views)This is the easiest entry point into raw SQL in Django because the rows are converted into Article model instances. It still feels somewhat like ORM usage, but the query itself is plain SQL.
An important detail must be understood immediately: when using raw(), your SQL query must include the primary key field. Django needs it to map rows back to model instances. If you forget the primary key, Django will raise an error. For example, this would be wrong:
Article.objects.raw("SELECT title, views FROM app_article")Because the query does not include the model’s primary key, Django cannot properly construct Article objects. A correct version would be:
Article.objects.raw("SELECT id, title, views, content, category_id, author_id, published, created_at FROM app_article")Or simply:
Article.objects.raw("SELECT * FROM app_article")as long as the table structure matches the model.
Now let us examine parameter binding, because this is one of the most important security lessons in the whole topic. Suppose the user searches for articles containing a keyword. A dangerous version would be:
keyword = request.GET.get("q")
query = f"SELECT * FROM app_article WHERE title LIKE '%{keyword}%'"
articles = Article.objects.raw(query)This is unsafe because the user input is inserted directly into the SQL string. A malicious user could manipulate the query. The safe version is:
keyword = request.GET.get("q")
articles = Article.objects.raw(
"SELECT * FROM app_article WHERE title LIKE %s",
[f"%{keyword}%"]
)Here, the parameter is passed separately, and Django’s database layer handles escaping and binding correctly. This is the correct habit to build: never manually concatenate untrusted input into SQL.
While raw() is useful, it has some limitations. It is mainly for SELECT queries and for returning model instances. It is not the best tool for updates, inserts, deletes, or complex operations that do not map cleanly to a model. That is where the database cursor becomes important.
To use a cursor in Django, you import the database connection:
from django.db import connectionThen you open a cursor with a context manager:
from django.db import connection
with connection.cursor() as cursor:
cursor.execute("SELECT id, title, views FROM app_article WHERE published = %s", [True])
rows = cursor.fetchall()
for row in rows:
print(row)This gives you much more direct control. fetchall() returns a list of tuples, not model instances. For example, each row might look like:
(1, 'Django ORM Guide', 250)You can also use fetchone() if you want a single row:
with connection.cursor() as cursor:
cursor.execute("SELECT id, title FROM app_article WHERE id = %s", [1])
row = cursor.fetchone()
print(row)Or fetchmany(size) if you want a limited number of rows at a time.
A common issue with fetchall() is that tuples are not very descriptive because you need to remember which column is in which position. One useful helper pattern is to convert rows into dictionaries using cursor.description. For example:
from django.db import connection
def dictfetchall(cursor):
columns = [col[0] for col in cursor.description]
return [
dict(zip(columns, row))
for row in cursor.fetchall()
]
with connection.cursor() as cursor:
cursor.execute("SELECT id, title, views FROM app_article WHERE published = %s", [True])
articles = dictfetchall(cursor)
for article in articles:
print(article['title'], article['views'])This is a very practical pattern because it makes raw SQL results easier to work with in Python.
Now let us see how to use raw SQL for update operations. Suppose you want to increase the views count of an article
from django.db import connection
with connection.cursor() as cursor:
cursor.execute(
"UPDATE app_article SET views = views + 1 WHERE id = %s",
[article_id]
)This works, but it is worth pausing here. In Django, the ORM often provides an equally good or better solution:
from django.db.models import F
Article.objects.filter(id=article_id).update(views=F('views') + 1)This is a very important architectural lesson: just because raw SQL can do something does not mean it should be your first choice. The ORM version is more portable and easier to read. Raw SQL is best reserved for cases where the ORM becomes too awkward, limited, or inefficient for the specific need.
The same principle applies to inserts:
with connection.cursor() as cursor:
cursor.execute(
"""
INSERT INTO app_category (name)
VALUES (%s)
""",
["Databases"]
)This works, but normally the ORM version is clearer:
Category.objects.create(name="Databases")So the value of raw SQL is not in replacing normal ORM inserts and updates. It is in handling special cases.
One very important use case for raw SQL is reporting and analytics. Suppose you want a query that shows the number of published articles in each category, ordered by the count descending. The ORM can do this using annotations, but SQL can sometimes feel more direct:
with connection.cursor() as cursor:
cursor.execute("""
SELECT c.name, COUNT(a.id) AS article_count
FROM app_category c
LEFT JOIN app_article a ON a.category_id = c.id
WHERE a.published = %s
GROUP BY c.name
ORDER BY article_count DESC
""", [True])
results = cursor.fetchall()
for category_name, article_count in results:
print(category_name, article_count)This is readable if you know SQL well, and it can be very useful in reporting contexts. However, the ORM can also do something similar with annotate() and Count(). So again, the right question is not “Can I write it in SQL?” but “Is raw SQL actually clearer or more appropriate here?”
Another case where raw SQL becomes useful is database-specific features. Sometimes a particular database engine provides powerful features that Django’s ORM does not expose directly or not fully. For example, PostgreSQL has advanced full-text search, Common Table Expressions, window functions, JSON operators, and other advanced tools. Django supports many PostgreSQL features through its ORM extensions, but there are still cases where raw SQL is the easiest or only practical route. This is one of the main reasons advanced developers still need SQL knowledge even when using Django heavily.
Let us discuss joins more deeply. Suppose you want article titles together with the author username and category name:
with connection.cursor() as cursor:
cursor.execute("""
SELECT a.title, u.username, c.name
FROM app_article a
JOIN auth_user u ON a.author_id = u.id
JOIN app_category c ON a.category_id = c.id
WHERE a.published = %s
ORDER BY a.created_at DESC
""", [True])
rows = cursor.fetchall()
for title, username, category_name in rows:
print(title, username, category_name)This kind of query is sometimes comfortable in SQL because it expresses exactly what columns you want from each table. The ORM can also do this using select_related() and then accessing the related fields. In many cases, the ORM remains preferable because it gives you model relationships and portability. But for exports, custom dashboards, and reports that only need certain columns, raw SQL can be perfectly reasonable.
Now let us move to transactions, because raw SQL often appears in more database-sensitive code. When executing multiple SQL statements that must succeed together, you should use Django transactions just as you would with ORM operations:
from django.db import connection, transaction
with transaction.atomic():
with connection.cursor() as cursor:
cursor.execute(
"UPDATE app_product SET stock = stock - %s WHERE id = %s",
[quantity, product_id]
)
cursor.execute(
"""
INSERT INTO app_inventorylog (product_id, quantity_changed, action)
VALUES (%s, %s, %s)
""",
[product_id, -quantity, "sale"]
)This is a strong pattern because it combines raw SQL power with Django’s transaction safety. If one statement fails, the whole block can roll back.
Another advanced topic is stored procedures. Some databases support procedures or database functions that encapsulate complex logic. Django can call them through a cursor. For example:
with connection.cursor() as cursor:
cursor.callproc('my_database_procedure', [param1, param2])
results = cursor.fetchall()This is less common in ordinary Django apps, but in enterprise systems or legacy environments, it can matter a lot. Django does not prevent this; it simply expects you to use lower-level database tools carefully.
Now let us look at debugging and inspecting SQL. Even when you do not plan to write raw SQL, understanding the SQL behind the ORM is extremely helpful. Django QuerySets expose their SQL string through the query attribute:
qs = Article.objects.filter(published=True).order_by('-created_at')
print(qs.query)This does not execute raw SQL directly, but it helps you see how Django translates your ORM code into SQL. This is valuable when optimizing performance, debugging joins, or learning SQL by comparing ORM and database output. In many cases, developers think they need raw SQL, but by inspecting the ORM-generated SQL, they realize the ORM is already doing exactly what they want.
A critical security section is needed here. The number one rule is: never put user input directly into SQL strings. Unsafe code like this must be avoided:
user_id = request.GET.get("id")
query = f"SELECT * FROM app_article WHERE author_id = {user_id}"Even if the input looks harmless, this pattern is dangerous. The safe pattern is always:
user_id = request.GET.get("id")
with connection.cursor() as cursor:
cursor.execute(
"SELECT * FROM app_article WHERE author_id = %s",
[user_id]
)This protects against injection because the database adapter treats the input as a value, not as part of the SQL syntax.
Another best practice is to keep raw SQL isolated. If raw SQL is needed, do not scatter it randomly across views and templates. Put it in clearly named service functions, repository-like helpers, or manager methods where it can be documented and tested. For example:
from django.db import connection
def get_top_categories():
with connection.cursor() as cursor:
cursor.execute("""
SELECT c.name, COUNT(a.id) AS article_count
FROM app_category c
LEFT JOIN app_article a ON a.category_id = c.id
WHERE a.published = %s
GROUP BY c.name
ORDER BY article_count DESC
""", [True])
columns = [col[0] for col in cursor.description]
return [
dict(zip(columns, row))
for row in cursor.fetchall()
]This is much better than burying large SQL strings directly inside a view. It improves readability and maintainability.
Testing raw SQL code is also essential. Because raw SQL bypasses some of the ORM abstraction, mistakes can be easier to make and harder to catch. Tests should verify that the returned rows have the expected values and that the SQL behaves correctly with real database state. For example:
from django.test import TestCase
from .models import Category, Article
from django.contrib.auth.models import User
class RawSQLTests(TestCase):
def test_top_categories_query(self):
user = User.objects.create(username='admin')
category = Category.objects.create(name='Python')
Article.objects.create(
title='A1',
content='Text',
category=category,
author=user,
published=True,
views=100
)
results = get_top_categories()
self.assertEqual(results[0]['name'], 'Python')
self.assertEqual(results[0]['article_count'], 1)This helps ensure that raw SQL functions remain correct as the schema evolves.
Let us also discuss portability. One of Django’s great advantages is that the ORM helps your code stay relatively independent of a specific database engine. Raw SQL weakens that advantage. A query written for PostgreSQL may not work the same way on SQLite or MySQL. Even parameter styles, SQL functions, date handling, string concatenation, limits, locking syntax, and advanced features can differ across databases. That does not mean raw SQL is bad. It means you should be intentional. If your application is tightly committed to PostgreSQL and benefits from its advanced features, raw SQL may be perfectly appropriate. But if you want maximum database portability, raw SQL should be used sparingly.
Another subtle issue is schema changes. ORM code is often easier to maintain when models evolve because field renames, related names, and migrations keep things aligned. Raw SQL strings can silently become outdated when table names or column names change. This is another reason to document raw SQL carefully and keep it centralized. A good practice is to comment why raw SQL was chosen and what ORM alternative was not sufficient.
A useful intermediate option between pure ORM and pure raw SQL is Django’s extra() method historically, but it is now discouraged and largely superseded by better ORM expressions and annotations. In modern Django, if you need advanced querying, first consider whether tools like annotate(), Subquery, OuterRef, Case, When, Func, database functions, or RawSQL expressions can solve the problem without fully dropping to cursor-level SQL. Full raw SQL should often be the later option, not the first one.
Django also provides a RawSQL expression that can be embedded into ORM annotations or filters in advanced cases. For example:
from django.db.models.expressions import RawSQL
articles = Article.objects.annotate(
custom_score=RawSQL("views * 0.1", [])
)This lets you keep most of the query in ORM form while inserting a custom SQL expression where needed. It is an advanced but useful middle ground. Still, it should be used carefully and only when the ORM does not already offer a clearer expression-based alternative.
One very important mindset to develop is that SQL knowledge improves your ORM usage too. Learning raw SQL is not only for writing cursor-based queries. It helps you understand joins, indexes, grouping, filtering, ordering, aggregation, subqueries, and query plans. That knowledge makes you better at writing efficient ORM code. Many performance problems in Django are not caused by the ORM itself, but by developers who do not realize what SQL their QuerySets generate. So even if you rarely write raw SQL directly, understanding it is a major professional advantage.
In practice, a healthy Django approach to raw SQL often follows this order of preference:
- Use the ORM if it expresses the query clearly and efficiently.
- Use advanced ORM tools if the query is more complex.
- Use
raw()if you need model instances from a custom SQLSELECT. - Use a cursor for special-purpose SQL, reporting, updates, database-specific features, or operations that do not fit the ORM well.
- Always parameterize inputs and isolate raw SQL in well-tested helper functions.
This hierarchy helps you keep the codebase maintainable without losing the power of SQL when truly needed.
In conclusion, raw SQL in Django is an advanced tool that gives you direct control over the database when the ORM is not the best fit. Django supports raw SQL through Model.objects.raw(), database cursors, and even raw expressions inside ORM queries. Used correctly, raw SQL can solve specialized reporting needs, integrate legacy queries, access database-specific features, and handle complex operations with precision. But it must be used carefully because it introduces security risks, reduces portability, and can become harder to maintain than ORM code. The key lesson is not to choose between ORM and SQL as rivals, but to understand both well enough to choose the right tool for the right situation. That is the mark of a mature Django developer.
What you learned in this tutorial
In this tutorial, you learned what raw SQL is in Django, when it may be appropriate to use it, how to use Model.objects.raw() for model-mapped queries, how to execute SQL with connection.cursor(), how to fetch rows safely, how to parameterize queries to prevent SQL injection, how to use raw SQL with transactions, how stored procedures and advanced reporting fit into this topic, and why portability, maintainability, and testing matter when working close to the database.
💬 Comments
No comments yet. Be the first to comment!
Login to comment.