DEV Community

Cover image for Writing Custom Django Database Functions
Idris Rampurawala
Idris Rampurawala

Posted on • Updated on

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
Enter fullscreen mode Exit fullscreen mode
# Usage
Author.objects.annotate(last_updated=TimestampToStr(TimestampToIST(F('updated_at'))))
Enter fullscreen mode Exit fullscreen mode

๐Ÿ”น 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)
Enter fullscreen mode Exit fullscreen mode
# Usage
User.objects.annotate(name_initial=ExtractFirstChar('first_name'))
Enter fullscreen mode Exit fullscreen mode

๐Ÿ”น 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()
Enter fullscreen mode Exit fullscreen mode

๐Ÿ“‘ Resources


See ya! until my next post ๐Ÿ˜‹

Discussion (2)

Collapse
stormytalent profile image
StormyTalent

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

Collapse
idrisrampurawala profile image
Idris Rampurawala Author

Thanks @stormytalent ๐Ÿ™‚
Glad that you liked it.