skip to content

In Django's ORM, how do you add a raw SQL fragment to an otherwise normal QuerySet, and why prefer RawSQL over extra()?

level: middleimportance: should knowfreq 38%

answer

  1. an expression, not a whole statement
  2. params is a required argument
  3. output_field for type conversion
  4. extra() is the legacy hook

basics

~20 s

Wrap the fragment in RawSQL(sql, params, output_field) and use it like any expression in annotate(), filter(pk__in=...) or order_by(). extra() is the older hook that splices strings into clauses; the docs call it a last resort.

solid answer

~40 s

`django.db.models.expressions.RawSQL(sql, params, output_field=None)` is an expression: Django wraps it in parentheses and drops it into the SQL it builds, so you keep `filter()`, `select_related()`, pagination and everything else. Typical uses are `annotate(score=RawSQL("...", (x,), output_field=FloatField()))` and `filter(id__in=RawSQL("select id from ... where col = %s", (v,)))`. `params` is required so you cannot forget the injection question, and `output_field` tells Django how to convert the result. `QuerySet.extra(select=, where=, params=, tables=, order_by=, select_params=)` does a similar job by pasting strings into specific clauses; it predates expressions, composes badly with them, and the docs say it is an old API to use only as a last resort and that they aim to deprecate it. Prefer built-in expressions first, then `RawSQL`.

code

python · 21 lines
python
from django.db.models import FloatField
from django.db.models.expressions import RawSQL

from library.models import Book

# Legacy form, still runs but the docs call it a last resort
old = Book.objects.extra(
    select={"flagged": "SELECT 1 FROM legacy_flags f WHERE f.book_id = library_book.id"},
)

# Expression form: chainable, typed, params required
new = (
    Book.objects.filter(
        id__in=RawSQL("SELECT book_id FROM legacy_flags WHERE flag = %s", ("recall",))
    )
    .annotate(
        weight=RawSQL("library_book.pages * %s", (0.5,), output_field=FloatField())
    )
    .select_related("publisher")
    .order_by("-weight")
)

go deeper

for a junior

Recall that RawSQL embeds a fragment inside a normal QuerySet, and that its params argument is where values go.

for a middle

Explain where RawSQL can be used, why output_field and qualified column names matter, and what each extra() argument splices into.

for a senior

Show the migration path from extra() to annotate(RawSQL(...)) or a Func, and judge when a fragment should become a reusable expression.

for a principal

Weigh raw fragments against portability and maintainability, and set a team rule for which escape hatch is acceptable at which point.

## The problem both tools solve Sometimes a query is 95% ordinary ORM and 5% SQL the ORM cannot express — a vendor function, an odd predicate, a subselect against a table without a model. Dropping the whole query to `raw()` loses the chainable `QuerySet`. Django offers two ways to embed just the fragment. ## `RawSQL`: a fragment as an expression `RawSQL` lives in `django.db.models.expressions` and has the signature `RawSQL(sql, params, output_field=None)`. Because it is an **expression**, it goes anywhere expressions go: - **`annotate()`** — `Book.objects.annotate(rank=RawSQL("ts_rank(search, plainto_tsquery(%s))", (q,), output_field=FloatField()))`. - **`filter(... __in=...)`** — `Book.objects.filter(id__in=RawSQL("select book_id from legacy_flags where flag = %s", (flag,)))`. - **`order_by()`** — order by an annotation computed with `RawSQL`. How it behaves: 1. **It is wrapped in parentheses.** `as_sql()` returns `"(%s)" % self.sql` plus your params, so a subselect fits naturally. 2. **`params` is required.** It is a positional argument with no default. The docs explain this is deliberate: it forces you to acknowledge you are not interpolating user data into the SQL string. Placeholders stay unquoted. 3. **`output_field` matters.** Without it the result is typed as a generic `Field`, so no backend converters run. Set `DecimalField()`, `DateTimeField()` or `FloatField()` so values come back as the right Python type and further expressions combine correctly. 4. **Column names are yours to write.** Django does not rewrite the fragment for table aliases. Inside a query with joins, qualify names by table (`"myapp_book"."id"`) or you risk ambiguous columns. 5. **Grouping.** When the query groups because an aggregate sits alongside it, the fragment itself is added to `GROUP BY`. The docs still flag the costs: raw fragments are not portable across engines and repeat knowledge the models already have, so reach for built-in functions first. ## `extra()`: the legacy clause splicer `QuerySet.extra(select=None, where=None, params=None, tables=None, order_by=None, select_params=None)` predates query expressions. Each argument pastes strings into one clause: | Argument | Goes into | |---|---| | `select={"alias": "sql"}` | extra `SELECT` columns (values via `select_params`) | | `where=["sql"]` | extra `WHERE` conditions, ANDed together (values via `params`) | | `tables=["t"]` | extra entries in `FROM` — an implicit cross join unless `where` constrains it | | `order_by=["alias"]` | `ORDER BY` terms | Why it is discouraged: - The docs head the section "Use this method as a last resort": an **old API they aim to deprecate at some point**, asking users with a remaining use case to file a ticket. It is not yet formally deprecated in 6.1, so existing code still runs. - Its strings are **opaque to the expression system**: an `extra(select=...)` alias cannot be used inside `F()`, `Case()` or an aggregate the way an annotation can. - Two separate params lists (`params`, `select_params`) must line up with their clauses, and misalignment is easy. - `tables=` silently produces cross joins when a `where` is forgotten. ## Migrating an `extra()` call Most `extra()` usage in older code is one of two shapes, and each has a direct replacement: - **An extra column.** `qs.extra(select={"is_recent": "created > %s"}, select_params=(cutoff,))` becomes `qs.annotate(is_recent=RawSQL("created > %s", (cutoff,), output_field=BooleanField()))` — or, better, `qs.annotate(is_recent=Q(created__gt=cutoff))` wrapped in `ExpressionWrapper` with a `BooleanField` output, which needs no SQL at all. - **An extra condition.** `qs.extra(where=["id IN (SELECT book_id FROM legacy_flags)"])` becomes `qs.filter(id__in=RawSQL("SELECT book_id FROM legacy_flags", ()))`. Note the empty tuple: `params` is still required even when there are none. After the swap, the new annotation can be referenced by `F()`, used in `order_by()`, aggregated, and filtered on like any other annotation, which the `extra()` alias never allowed cleanly. ## Choosing, in order 1. A **built-in expression or database function** (`F`, `Case`, `Coalesce`, `Cast`, `Concat`, `Extract`, ...). 2. A **custom `Func` subclass** when a named SQL function is missing — it stays reusable and typed. 3. **`RawSQL`** for a one-off fragment or subselect you cannot model. 4. **`raw()` or a cursor** when the whole statement is hand-written. 5. **`extra()`** only when maintaining code that already uses it; migrating an `extra(select=...)` to `annotate(x=RawSQL(...))` is usually a direct swap.

  • What goes wrong if a RawSQL annotation omits output_field?
    Django types it as a plain `Field`, so no backend converter runs and the value arrives as whatever the driver returns. Combining it with other expressions can also fail to resolve a type. Setting `output_field` makes conversion and further arithmetic predictable.
  • Is extra() deprecated in Django 6.1?
    Not formally. The documentation calls it an old API to use as a last resort and says the project aims to deprecate it at some point, asking users to file tickets for use cases the QuerySet API cannot cover. Existing calls still work, but new code should use expressions or RawSQL.

RawSQL is a pre-cut part you bolt into a machine the ORM assembles; the machine still runs as designed. extra() is taping notes onto individual pages of the finished blueprint, which the rest of the machinery cannot read.

saying these in an interview costs you the question

  • Says RawSQL turns the whole QuerySet into a raw query
  • Believes params is optional for RawSQL
  • Claims extra() was removed from Django
  • Assumes Django rewrites column names inside a RawSQL fragment for join aliases
  • Thinks output_field on RawSQL is purely cosmetic