Writing Custom Django Database Functions
Tags: python, django, webdev, tutorial
Published: 2022-05-19 11:40:12

---
title: Writing Custom Django Database Functions
published: true
description: Understanding different ways to customize Django's rich set of features based on our needs is beneficial. This post focuses on ways to customize Django database functions that provide more control in filtering queries with helpful examples.
tags: python, django, webdev, tutorial
cover_image: https://res.cloudinary.com/idr/image/upload/w_1000,h_420,c_pad,b_white,q_auto,f_auto,fl_progressive/dev.to/undraw_Developer_activity_re_39tg_wlflhm.png
---

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](https://docs.djangoproject.com/en/4.0/ref/models/expressions/), so they can be used and combined with other expressions like [aggregate functions](https://docs.djangoproject.com/en/4.0/ref/models/querysets/#aggregation-functions).

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

:memo: 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.

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

## :scroll: Django `Func(*expressions, **extra)` class
- The class [Func()](https://docs.djangoproject.com/en/4.0/ref/models/expressions/#django.db.models.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](https://docs.djangoproject.com/en/4.0/ref/models/expressions/#django.db.models.Func) 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](https://docs.djangoproject.com/en/4.0/ref/models/expressions/#avoiding-sql-injection) before using `Func()`


Following are some ways to write our custom database functions:

## :small_blue_diamond: Custom database functions
We can create our custom database functions using Django's [Func class](https://docs.djangoproject.com/en/4.0/ref/models/expressions/#django.db.models.Func). 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:
```py
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
```
```sh
# Usage
Author.objects.annotate(last_updated=TimestampToStr(TimestampToIST(F('updated_at'))))
```
 

## :small_blue_diamond: 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:
```py
from functools import partial
from django.db.models.functions import Substr

ExtractFirstChar = partial(Substr, pos=1, length=1)
```
```sh
# Usage
User.objects.annotate(name_initial=ExtractFirstChar('first_name'))
```


## :small_blue_diamond: 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 :raised_eyebrow: 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](https://stackoverflow.com/a/65066965/9334209)

```py
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()
```
---
## :bookmark_tabs: Resources
- [Django database functions](https://docs.djangoproject.com/en/4.0/ref/models/database-functions/)
- [Django Func() class](https://docs.djangoproject.com/en/4.0/ref/models/expressions/#django.db.models.Func)
- [StackOverflow answer GROUP BY without aggregate](https://stackoverflow.com/a/65066965/9334209)

---

<center>See ya! until my next post :yum: </center>