Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 77 ยท Models and the ORM

Raw SQL, Database Functions and JSON Fields

Go beyond basic ORM queries - run safe raw SQL, call database functions with Func and Concat, query JSONField keys, and use GeneratedField and db_default.

The Django ORM covers most of what a web application asks of a database, but not everything. Sooner or later you meet a query that the ORM cannot express, a vendor feature you want to use, or a value that the database should compute rather than your Python code. Django has escape hatches for each of these, and knowing them keeps you from rewriting a project in raw SQL just because one report is awkward.

This lesson walks up a ladder, from the most Django-flavored tools to the least: database functions (Concat, Lower, your own Func), JSONField queries, generated columns and database defaults, and finally raw SQL with raw() and the cursor. Reach for the highest rung that works, because the ORM tools are portable between databases and protect you from SQL injection by design. You should know basic queries and aggregation first.

All examples use SQLite in your browser. Some features behave differently on other databases; those differences are noted in the text. The setup block at the top of each example is the same as in the other ORM lessons.

flowchart TD
    A["Need something the plain ORM cannot do?"] --> B["Database functions and expressions<br/>Func, Concat, Case, Cast"]
    B --> C["Field features<br/>JSONField, GeneratedField, db_default"]
    C --> D["Model.objects.raw()<br/>your SQL, model instances back"]
    D --> E["connection.cursor()<br/>any SQL, plain tuples back"]

Move down only when the step above cannot express what you need: each step trades portability and safety for freedom.

Database functions

Django ships a library of SQL functions in django.db.models.functions. They return expressions that you can use in annotate(), filter(), order_by() and update(), and the work happens inside the database. Some everyday ones:

Function SQL idea
Lower, Upper, Length change case, measure text
Concat(a, b, ...) join strings (use Value(" ") for a literal)
Substr(text, start, length) slice text (positions start at 1)
Replace(text, old, new) search and replace in text
Coalesce(a, b) the first value that is not NULL
Cast(expr, output_field) convert types
Round, Abs, Greatest, Least numbers
Now, TruncMonth, ExtractYear dates and times

Value() wraps a Python constant so it can be part of an expression, and F() refers to a column. When you combine fields of different types, give the expression an output_field so Django knows how to convert the result.

Writing your own function

Every function above is a subclass of Func. When your database has a function Django does not wrap, make a small subclass with the SQL function name and an output_field. You can also use Func(...) directly with a template. Custom SQL functions tie you to databases that have them: INSTR below exists in SQLite and MySQL, while PostgreSQL calls its version POSITION or STRPOS.

Python Database functions and a custom Func

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at the last two columns of each row. Instr returns 0 when the search text is missing, and the position 3 of the space in "Le Guin". All the work happened in SQL: Python only received the finished values. Because annotations are expressions, you can also filter with them (filter(name_length__gt=5) after annotating) or update with them, as in update(last_name=Upper("last_name")), which changes every row with a single statement and never loads objects into Python.

JSONField

models.JSONField stores any JSON-serializable Python value (dicts, lists, strings, numbers) in a column. It suits data whose shape varies per row, such as product attributes or webhook payloads, and it is a poor replacement for proper columns you will filter and join by every day. Django supports it on PostgreSQL, MySQL/MariaDB, SQLite and Oracle.

You query keys with the same double-underscore syntax as relations. attrs__color="green" looks inside the JSON object, attrs__size__width__gte=100 walks into nested objects (and still compares numbers), and attrs__tags__0 addresses array items by index. Special lookups test for keys: has_key, has_keys (all of them) and has_any_keys (at least one). KT("attrs__color") returns a key as text, which is handy for annotations.

Python Querying keys inside a JSONField

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Two caveats matter in practice:

  • Updating: the simple way, shown above, is to read the dict, change it and save the whole value. Concurrent edits can overwrite each other, so keep the JSON small or protect it with a transaction and locking.
  • Portability: contains and contained_by (does the JSON include this fragment?) work on PostgreSQL and MySQL but not on SQLite, which is why that last lookup raised NotSupportedError. On PostgreSQL you can also add a GIN index to make JSON queries fast. Use default=dict (the callable), never default={}, so rows do not share one dict.

GeneratedField and db_default

Two newer features move logic from Python into the table definition.

A GeneratedField (Django 5.0+) is a column whose value the database computes from an expression over other columns of the same row. With db_persist=True the value is stored on disk, so it can be indexed and queried like any other column. Typical uses are totals (unit_price * quantity), normalized search keys (Lower("product")) and full names. You never assign to it, and Django leaves it out of INSERT and UPDATE statements. After an insert or update, call refresh_from_db() to see the new value on the Python object.

A db_default (Django 5.0+) gives a field a default that is part of the table, not just of the Python model. db_default="new" or db_default=Now() fills in a missing value even when the row is written by bulk_create() or by another program with plain SQL, and expressions such as database functions are allowed. The older default= is applied in Python only, which is why it cannot cover inserts from other sources.

Python A generated total and database defaults

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Both features are schema changes, so Django records them in migrations. Support varies: generated columns need SQLite 3.31 or newer and a recent PostgreSQL or MySQL; if your database does not support a feature, the system check reports it.

Raw SQL

When nothing above fits, write SQL yourself. You have two tools.

Manager.raw()

Model.objects.raw(sql, params) runs your query and returns model instances. The query must select the primary key. Columns that match model fields fill them in, extra columns become attributes (such as double_pages below), and any model field you leave out is deferred and loaded with an extra query when you touch it.

connection.cursor()

For statements that do not map to model objects (bulk updates, reports, vendor commands), open a cursor from django.db.connection. cursor.execute(sql, params) runs it, cursor.fetchall() returns tuples, cursor.description lists the column names, and cursor.rowcount tells you how many rows a write changed. Use with connection.cursor() as cursor: so it is closed for you.

flowchart LR
    S["SQL text with %s placeholders"] --> D["database driver"]
    P["parameters: [2018]"] --> D
    D --> R["query plan runs with values bound as DATA"]

Placeholders keep the SQL and the values apart, so a value can never change the meaning of the query.

Warning: Never build SQL by pasting user input into a string with an f-string, % or +. That is how SQL injection happens. Always use %s placeholders and pass the values in the second argument. Placeholders protect values only: table and column names cannot be parameters, so build those from your own trusted constants (such as Book._meta.db_table), never from request data.

Python raw() and the cursor with safe parameters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last query shows why parameters matter: the hostile string x' OR '1'='1 was treated as a plain value to compare, so it matched nothing. Raw queries skip Django's model validation and caching, tie you to one SQL dialect, and will not follow future schema changes unless you update them, so keep them small and put them in one place, such as a custom manager method. Test them as carefully as the rest of your code.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which is the safe way to run a raw query that depends on a year from the request?

    Only the placeholder form sends the value separately from the SQL text. The other three paste user input into the query string, which allows SQL injection.
  2. 2. What must a raw() query always select?

    Django needs the primary key to build model instances. Other model fields you leave out are deferred and loaded lazily.
  3. 3. Why use db_default instead of default?

    default is applied by Django in Python. db_default becomes part of the table definition, so bulk inserts and plain SQL inserts get the value too.
  4. 4. Which JSONField lookup is not supported on SQLite?

    contains and contained_by need native JSON containment operators, available on PostgreSQL and MySQL but not on SQLite. Key lookups, key tests and nested comparisons work everywhere.

Summary

  • Prefer the ORM's expressions: Func, Concat, Lower, Coalesce, Cast and friends run in the database, work in annotate(), filter() and update(), and can be extended with your own Func subclass.
  • JSONField stores flexible data; query keys with field__key, test keys with has_key, and remember that contains is unavailable on SQLite.
  • GeneratedField makes the database compute a column from others; db_default makes the database supply missing values.
  • Manager.raw() returns model instances (select the primary key); connection.cursor() handles any SQL and returns tuples.
  • Always pass values as parameters with %s placeholders, never by string formatting, and never take identifiers from user input.
  • Keep raw SQL small, tested and in one place.

Next, learn how to share fields and behavior between models in Model Inheritance and Abstract Models.