⇦

2025-07-08

Wrapping raw SQL with Django models + TEMPORARY VIEW for composabilty

The Django ORM is terrible in many ways (versus eg. SQLAlchemy) - much of this results from the inability to dip down to the SQL level without resorting to SQL string munging and throwing away all your typing.

Following is a technique to combine complex raw SQL queries with dynamic, composable .filter(), .exclude(), etc. from the ORM.

from contextlib import contextmanager
from typing import Iterator, TypeVar
from django import db
from django.db import connection
from django.db.models import Model, QuerySet

TModel = TypeVar("TModel", bound=Model)

Say you have a complex query:

SELECT x, y
FROM (complex_subqueries)
GROUP BY (loads_of_things)

Let's define a throwaway model to use as a row type:

class Row(models.Model):
    x = models.CharField()
    y = models.IntegerField()

Now we write a function that creates a TEMPORARY VIEW from the query, wrapped in a bit that adds an arbitrary id column to placate Django:

@contextmanager
def from_query(cls: type[TModel], sql: str) -> Iterator[QuerySet[TModel]]:
    cls._meta.db_table = "__temporary_view"  # We mutate the class 🤷
    cls._meta.managed = False
    with connection.cursor() as cursor:
        sql = f"""
            CREATE OR REPLACE TEMPORARY VIEW __temporary_view AS
            SELECT ROW_NUMBER() OVER (ORDER BY NULL) AS id, *
            FROM ({sql}) AS __subquery
        """
        cursor.execute(sql)
        yield cls.objects  # type: ignore[attr-defined]

Usage:

with from_query(Row, sql) as query:
    query = query.filter(x="foo")
    ...

Typing and performance

This approach seemingly plays well with the Django mypy plugin.

Although the TEMPORARY VIEW does introduce some session state, __temporary_view  won't collide with any other sessions:

Existing permanent relations with the same name are not visible to the current session while the temporary view exists...

I did a budget benchmark of CREATE TEMPORARY VIEW vs plain ol' CTEs and they're quick enough™:

N, temporary_views, ctes = 100, 0.0, 0.0
for _ in range(N):
    with connection.cursor() as cur:
        before = time.time()
        cur.execute("CREATE OR REPLACE TEMPORARY VIEW t AS SELECT 1 AS x, 2 AS y")
        cur.execute("SELECT * FROM t")
        temporary_views += (time.time() - before)

    with connection.cursor() as cur:
        before = time.time()
        cur.execute("WITH t AS (SELECT 1 AS x, 2 AS y) SELECT * FROM t")
        ctes += (time.time() - before)

print(f"TEMPORARY VIEW: {temporary_views/N*1000:2f}ms\nCTE: {ctes/N*1000:2f}ms")

Results:

TEMPORARY VIEW: 0.6ms
CTE: 0.25ms

Edit

Turns out I maybe just reimplemented django-raw-sugar. Still, interesting to discover that TEMPORARY TABLEs are pretty cheap, and I know people (reasonably) have a preference for a small bit of copy pasta over some random library.