Writing Custom Django Database Functions

Django's database functions represent functions that will run in the database. It provides a way for users to use functions provided by the underlying database as annotations, aggregations, or filters. Functions are also expressions, so they can be used and combined with other expressions like aggregate functions.

One of the rich Django's features includes customization of its various functionalities. Yes, you got it right! 🙌 We can customize Django database functions as per our needs.

📝 In this post, we will look into a couple of examples below to get the gist of writing custom functions based on our business needs.

👉 Let's first understand the Django Func() class which serves as the basis for us to move forward.

📜 Django Func(*expressions, **extra) class

• The class Func() is the most general part of Django Query Expressions
• It allows the implementation of almost any function or operator into Django ORM in some way
• Func() expression is the base type of all expressions that involve database functions like COALESCE and LOWER, or aggregates like SUM
• I recommend reading Avoiding SQL injection before using Func()

Following are some ways to write our custom database functions:

🔹 Custom database functions

We can create our custom database functions using Django's Func class. In one of my projects, I wanted to covert the UTC timestamp to IST in Django filter with a specific date format. Writing two simple Django database functions helped me reuse it at multiple instances as follows:

from django.db.models import Func

class TimestampToIST(Func):
    """ Converts the db (UTC) timestamp value to IST equivalent timestamp
    """
    function = 'timezone'
    template = "%(function)s('Asia/Calcutta', %(expressions)s)"


class TimestampToStr(Func):
    """ Converts the timestamp to string using the given format
    """
    function = 'to_char'
    template = "%(function)s(%(expressions)s, 'DD/MM/YYYY HH24:MI:SS')"  # 21/06/2021 16:08:34

# Usage
Author.objects.annotate(last_updated=TimestampToStr(TimestampToIST(F('updated_at'))))

🔹 Partial implementation of database functions

Another great customization example is to make a new version of the function with one or two arguments already filled in. For example, let's create a specialized SubStr that extracts the first character from a string:

from functools import partial
from django.db.models.functions import Substr

ExtractFirstChar = partial(Substr, pos=1, length=1)

# Usage
User.objects.annotate(name_initial=ExtractFirstChar('first_name'))

🔹 Executing a GROUP BY without an aggregation function

Imagine a situation where we want to use GROUP BY without using any aggregate function. Django ORM does not allow us to use GROUP BY without an aggregate function 🤨 Hence, to accomplish this, we can create a Django function that is treated as an aggregate function by Django but evaluates to NULL in a SQL query, as sourced from StackOverflow

from django.db.models import CharField, Func

class NullAgg(Func):
    """Annotation that causes GROUP BY without aggregating.

    A fake aggregate Func class that can be used in an annotation to cause
    a query to perform a GROUP BY without also performing an aggregate
    operation that would require the server to enumerate all rows in every
    group.

    Takes no constructor arguments and produces a value of NULL.

    Example:
        ContentType.objects.values('app_label').annotate(na=NullAgg())
    """
    template = 'NULL'
    contains_aggregate = True
    window_compatible = False
    arity = 0
    output_field = CharField()

---

📑 Resources

• Django database functions
• Django Func() class
• StackOverflow answer GROUP BY without aggregate

---

See ya! until my next post 😋

Comments

[StormyTalent]

Excellent explanations!
I highly recommend your blog!
Thanks for sharing.

[Idris Rampurawala]

Thanks @stormytalent 🙂
Glad that you liked it.