Database generated columns (1): Django & SQLite
Tags: django, database, python, sqlite
Published: 2023-11-24 22:31:24

---
title: Database generated columns (1): Django & SQLite
published: true
date: 2023-11-06 23:00:00 UTC
tags: Django,Database,Python,SQLite
canonical_url: https://www.paulox.net/2023/11/07/database-generated-columns-part-1-django-and-sqlite/
description: An introduction to database generated columns, using SQLite and the new GeneratedField added in Django 5.0.
series: Database generated columns
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zps9omsaykranzfy98t4.jpg
---

An introduction to database generated columns, using SQLite and the new GeneratedField added in Django 5.0.

## ⚡ TL;DR

`GeneratedField` is a new Django 5.0 feature and has these characteristics:

- its value is calculated entirely by the database
- it works in all supported backend databases

Here is a model example with a `GeneratedField` to use with SQLite as the database backend:

```python
from django.db import models
from django.db.models import F


class Rectangle(models.Model):
    base = models.FloatField()
    height = models.FloatField()
    area = models.GeneratedField(
        expression=F("base") * F("height"),
        output_field=models.FloatField(),
        db_persist=True,
    )
```

## 🚀 Introduction

I've been thinking for a long time that database generated columns would be a killer feature for Django and now they will finally be available in Django 5.0 with the new `GeneratedField`.

Given my great interest in this functionality, I proposed it to the Django developer mailing list, I tried to help in the revision phase of the Pull Request on the Django code, but above all I tried to test them as much as possible after the merge, trying to highlight any bugs before the release of Django 5.0 as a stable version.

In this article, I want to share with you the work I have done to allow you to experiment with `GeneratedField` using common model field types using SQLite as a database backend.

## 🤖 Generated Field

The `GeneratedField` are full-fledged fields that can be used in queries, displayed and even indexed but their values cannot be set or modified because they are automatically calculated by the database itself whenever the other fields of the same row are modified.

For this reason, they are very useful for having immediately available values calculated starting from the other fields of the same model as long as these are in the same database table.

The definition of the `GeneratedField` class requires specifying the `expression`, `output_field`, and `db_persist` attributes.

```python
class GeneratedField(
    expression, output_field, db_persist=None, **kwargs
):
    ...
```

#### 🕶️ Attributes

##### `db_persist`

In this article, we're going to use SQLite as a database backend with persisted columns, specified with `db_persist=True`, in that the database column will occupy storage as if it were a real column.

##### `output_field`

We have to specify an explicit model field instance to define the field’s data type and its attributes.

##### `expression`

The `expression` attributes is the one we're going to play more with, using various example of `Expression` that the database will use to automatically set the field value each time the model is changed.

#### ↔️ Migrations

The SQL code generated from the migrations produced in our examples will all have the `GENERATED ALWAYS SQL (...) STORED` syntax, varying only in the internal logic of the expression.

## 💻 Set Up

The requirements to experiment with Django `GeneratedField` are:

### 🐍 Python

A stable and supported version of Python 3 (tested with Python 3.11-3.12):

```console
$ python3 --version
Python 3.12.0
```

### ⚗️ Virtual environment

A Python virtual environment:

```console
$ python3 -m venv ~/.generatedfields
$ source ~/.generatedfields/bin/activate
```

### 🦄 Django

The latest version of Django (tested with Django 5.0):

```console
$ python3 -m pip install django==5.0
```

## 🗃️ The `generatedfields` project

To create the `generatedfields` project I'll switch to my `projects` directory:

```console
$ cd ~/projects
```

and then use the `startproject` Django command:

```console
$ python3 -m django startproject generatedfields
```

The basic files of our project will be created in the `generatedfields` directory:

```console
$ tree --noreport generatedfields/
generatedfields/
├── manage.py
└── generatedfields
    ├── asgi.py
    ├── __init__.py
    ├── settings.py
    ├── urls.py
    └── wsgi.py
```

## 🗂️ The `samples` app

After switching to the `generatedfields` directory:

```console
$ cd generatedfields
```

We can create our `samples` app with the Django `startapp` command:

```console
$ python3 -m django startapp samples
```

Again, all the necessary files will be created for us in the `samples` directory:

```console
$ tree --noreport samples/
samples/
├── admin.py
├── apps.py
├── __init__.py
├── migrations
│   └── __init__.py
├── models.py
├── tests.py
└── views.py
```

## ✅ Activating the `samples` app

Now, we have to activate our `samples` application by inserting its name in the list of the `INSTALLED_APPS` in the `generatedfields` settings file.

`generatedfields/generatedfields/settings.py`

```python
INSTALLED_APPS = [
    "django.contrib.admin",
    "django.contrib.auth",
    "django.contrib.contenttypes",
    "django.contrib.sessions",
    "django.contrib.messages",
    "django.contrib.staticfiles",
    "samples",
]
```

## 📚 Sample models

The example models I have experimented with have expressions with complexity that progressively increase from example to example.

We start by combining two fields of the model to create the value for the generated one and then using database functions, combining them, using them with conditions and even generating multiple fields from the same starting field.

These experiments have allowed us to identify some bugs and fix them during this Django 5.0 testing period, but I leave off some edge cases to take into account which we'll mention at the end of the article.

## 🔧 Useful commands

In the following examples, we will use some commands that we show here to avoid repetition.

### `makemigrations`

Generates a new database migration to apply it to our database:

```console
$ python3 -m manage makemigrations --no-header
```

### `sqlmigrate`

See the SQL code that our migration will apply to the database:

```console
$ python3 -m manage sqlmigrate samples <migration-id>
```

### `migrate`

Apply migration to our database with:

```console
$ python3 -m manage migrate
```

### `shell`

Start an interactive Django shell:

```console
$ python3 -m manage shell
```

## ✖️ A calculated product field

The first generated field experiment I thought of is the one in which to use the model fields by combining them, for example, the `Rectangle` model, in which we have two float fields, `base` and `height`, and a generated field, the `area`, which is automatically calculated by the database by multiplying the other two fields together.

`generatedfields/samples/models.py`

```python
from django.db import models
from django.db.models import F


class Rectangle(models.Model):
    base = models.FloatField()
    height = models.FloatField()
    area = models.GeneratedField(
        expression=F("base") * F("height"),
        output_field=models.FloatField(),
        db_persist=True,
    )

    def __str__(self):
        return (
            f"{self.base}×{self.height}="
            f"{self.area}"
        )
```

After running `makemigrations`, use `sqlmigrate` and see the SQL code that will be applied to the database:

```sql
BEGIN;
--
-- Create model Rectangle
--
CREATE TABLE "samples_rectangle" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "base" real NOT NULL,
  "height" real NOT NULL,
  "area" real GENERATED ALWAYS AS (
    ("base" * "height")
  ) STORED
);
COMMIT;
```

In the generated SQL code you see the notation for the generated columns `GENERATED ALWAYS AS`, the specific formula `"base" * "height"` and the notation specifying that the column is of type `STORED`.

After running `migrate`, open a `shell` and check the model behaves as intended:

```python
>>> from samples.models import Rectangle
>>> Rectangle.objects.create(base=6, height=7)
<Rectangle: 6×7=42.0>
```

## ✳️ A calculated exponentiation field

The second example model is the `Square`, from its single `side` field, the value of the `area` generated field is calculated, using the `Power` database function, made available by Django's ORM.

`generatedfields/samples/models.py`

```python
from django.db import models
from django.db.models.functions import (
    Power,
)


class Square(models.Model):
    side = models.FloatField()
    area = models.GeneratedField(
        expression=Power("side", 2),
        output_field=models.FloatField(),
        db_persist=True,
    )

    def __str__(self):
        return f"{self.side}²={self.area}"
```

After running `makemigrations`, use `sqlmigrate` and see the SQL code that will be applied to the database:

```sql
BEGIN;
--
-- Create model Square
--
CREATE TABLE "samples_square" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "side" real NOT NULL,
  "area" real GENERATED ALWAYS AS (
    POWER("side", 2)
  ) STORED
);
COMMIT;
```

The SQL code generated for the SQLite database uses the built-in `POWER` mathematical function to calculate the value of the calculated column.

After running `migrate`, open a `shell` and check the model behaves as intended:

```python
>>> from samples.models import Square
>>> Square.objects.create(side=3)
<Square: 3²=9.0>
```

## ⭕ A calculated geometric formula field

The `Circle` model is very similar to that of the `Square` but the expression for calculating the value of the `area` generated field uses the database function `Pi`, and the result of multiplication with the latter by the value of the `radius` field, is rounded, with the other database function `Power`.

`generatedfields/samples/models.py`

```python
from django.db import models
from django.db.models.functions import (
    Pi, Power, Round
)


class Circle(models.Model):
    radius = models.FloatField()
    area = models.GeneratedField(
        expression=Round(
            Power("radius", 2) * Pi(),
            precision=2,
        ),
        output_field=models.FloatField(),
        db_persist=True,
    )

    def __str__(self):
        return (
            f"{self.radius}²×π="
            f"{self.area}"
        )
```

After running `makemigrations`, use `sqlmigrate` and see the SQL code that will be applied to the database:

```sql
BEGIN;
--
-- Create model Circle
--
CREATE TABLE "samples_circle" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "radius" real NOT NULL,
  "area" real GENERATED ALWAYS AS (
    ROUND((POWER("radius", 2) * PI()), 2)
  ) STORED
);
COMMIT;
```

The SQL code of the calculated column is more complex than that of the previous example, in line with the increased complexity of the model expression but, the functions used have a one-to-one correspondence with those made available by the Django ORM.

After running `migrate`, open a `shell` and check the model behaves as intended:

```python
>>> from samples.models import Circle
>>> Circle.objects.create(radius=3.1415)
<Circle: 3.1415²×π=31.0>
```

## 📐 A calculated trigonometric functions field

The `RightTriangle` model is the last geometric example and in this one a fairly complex expression is used to calculate the generated field `area`, starting from the `hypotenuse` and `angle` fields, using the trigonometric functions `Sin` and `Cos` made available by the Django ORM in addition to those used in the previous examples.

`generatedfields/samples/models.py`

```python
from django.db import models
from django.db.models.functions import (
    Cos, Power, Radians, Round, Sin
)


class RightTriangle(models.Model):
    hypotenuse = models.FloatField()
    angle = models.FloatField()
    area = models.GeneratedField(
        expression=Round(
            (
                Power("hypotenuse", 2)
                * Sin(Radians("angle"))
                * Cos(Radians("angle"))
            )
            / 2,
            precision=2,
        ),
        output_field=models.FloatField(),
        db_persist=True,
    )

    def __str__(self):
        return (
            f"{self.hypotenuse}²×"
            f"sin({self.angle}°)×"
            f"cos({self.angle}°)÷"
            f"2={self.area}"
        )
```

After running `makemigrations`, use `sqlmigrate` and see the SQL code that will be applied to the database:

```sql
BEGIN;
--
-- Create model RightTriangle
--
CREATE TABLE "samples_righttriangle" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "hypotenuse" real NOT NULL,
  "angle" real NOT NULL,
  "area" real GENERATED ALWAYS AS (
    ROUND(
      (
        (
          (
            POWER("hypotenuse", 2)
            * SIN(RADIANS("angle"))
          ) * COS(RADIANS("angle"))
        ) / 2
      ),
      2
    )
  ) STORED
);
COMMIT;
```

Despite the increased complexity of the expression, the SQL code of the database generated column expression, for SQLite, is clear, demonstrating the power of SQL for these types of queries.

After running `migrate`, open a `shell` and check the model behaves as intended:

```python
>>> from samples.models import RightTriangle
>>> RightTriangle.objects.create(angle=45, hypotenuse=5)
<RightTriangle: 5²×sin(45°)×cos(45°)÷2=6.25>
```

## 🧾 A calculated total price field

In the `Item` model the expression of the generated field `total_price` is a multiplication of the two fields `price` and `quantity` to which the `Round` function is applied. However, the integer `quantity` field has a default value of `1`, but this default value is declared via the new `db_default` parameter, which sets a database-computed default value, also introduced in Django 5.0.

`generatedfields/samples/models.py`

```python
from django.db import models
from django.db.models import F, Value


class Item(models.Model):
    price = models.DecimalField(
        max_digits=6, decimal_places=2
    )
    quantity = models.PositiveSmallIntegerField(
        db_default=Value(1)
    )
    total_price = models.GeneratedField(
        expression=F("price") * F("quantity"),
        output_field=models.DecimalField(
            max_digits=11, decimal_places=2
        ),
        db_persist=True,
    )

    def __str__(self):
        return (
            f"{self.price}×"
            f"{self.quantity}="
            f"{self.total_price}"
        )
```

After running `makemigrations`, use `sqlmigrate` and see the SQL code that will be applied to the database:

```sql
BEGIN;
--
-- Create model Item
--
CREATE TABLE "samples_item" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "price" decimal NOT NULL,
  "quantity" smallint unsigned DEFAULT 1 NOT NULL CHECK (
    "quantity" >= 0
  ),
  "total_price" decimal GENERATED ALWAYS AS (
    CAST(("price" * "quantity") AS NUMERIC)
  ) STORED
);
COMMIT;
```

The SQL code generated for SQLite contains an expression similar to a previous example, but the presence of the `DEFAULT` in the `quantity` column means that the calculation logic of the generated column `total_price` is entirely contained in the database.

After running `migrate`, open a `shell` and check the model behaves as intended:

```python
>>> from samples.models import Item
>>> Item.objects.create(price=9.99)
<Item: 9.99×1=9.99>
>>> Item.objects.create(price=4.99, quantity=2)
<Item: 4.99×2=9.98>
```

## 🚦 A calculated status field

In the `Order` model we see an example of using a conditional expression in the generated `status` field. The presence or absence of the `creation` and `payment` values permits the selection of the correct `status` value directly in the SQLite database.

`generatedfields/samples/models.py`

```python
from django.db import models
from django.db.models import (
    Case, Value, When
)


class Order(models.Model):
    creation = models.DateTimeField()
    payment = models.DateTimeField(null=True)
    status = models.GeneratedField(
        expression=Case(
            When(
                payment__isnull=False,
                then=Value("paid"),
            ),
            default=Value("created"),
        ),
        output_field=models.TextField(),
        db_persist=True,
    )

    def __str__(self):
        return (
            f"[{self.status}] "
            f"{self.payment or self.creation}"
        )
```

After running `makemigrations`, use `sqlmigrate` and see the SQL code that will be applied to the database:

```sql
BEGIN;
--
-- Create model Order
--
CREATE TABLE "samples_order" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "creation" datetime NOT NULL,
  "payment" datetime NULL,
  "status" text GENERATED ALWAYS AS (
    CASE
      WHEN "payment" IS NOT NULL THEN 'paid'
      ELSE 'created'
    END
  ) STORED
);
COMMIT;
```

The SQL generated by SQLite uses the `CASE` expression to populate the value of the generated column `status` based on whether or not there is a `datetime` value in the `payment` column.

After running `migrate`, open a `shell` and check the model behaves as intended:

```python
>>> from samples.models import Order
>>> Order.objects.create(creation="2023-01-01 12:00Z")
<Order: [created] 2023-01-01 12:00Z>
>>> Order.objects.create(
...     creation="2023-01-02 00:00Z",
...     payment="2023-01-03 06:30Z"
... )
<Order: [paid] 2023-01-02 6:30Z>
```

## 🗓️ Calculated date and duration fields

In the `Event` model there are three fields generated compared to only two basic `DateTimeField`, the `start` field is required and the `end` field is optional. The generated fields `start_date` and `end_date` have an expression that truncates the date of the corresponding fields. In the third generated field `duration` the expression performs the subtraction between the value of the `end` and `start` field. If the basic fields are empty, the expression of the generated fields returns a null value.

`generatedfields/samples/models.py`

```python
from django.db import models
from django.db.models import F
from django.db.models.functions import (
    TruncDate,
)


class Event(models.Model):
    start = models.DateTimeField()
    start_date = models.GeneratedField(
        expression=TruncDate("start"),
        output_field=models.DateField(),
        db_persist=True,
    )
    end = models.DateTimeField(null=True)
    end_date = models.GeneratedField(
        expression=TruncDate("end"),
        output_field=models.DateField(),
        db_persist=True,
    )
    duration = models.GeneratedField(
        expression=F("end") - F("start"),
        output_field=models.DurationField(),
        db_persist=True,
    )

    def __str__(self):
        return (
            f"[{self.duration or '∞'}] "
            f"{self.start_date}…"
            f"{self.end_date or ''}"
        )
```

After running `makemigrations`, use `sqlmigrate` and see the SQL code that will be applied to the database:

```sql
BEGIN;
--
-- Create model Event
--
CREATE TABLE "samples_event" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "start" datetime NOT NULL,
  "start_date" date GENERATED ALWAYS AS (
    django_datetime_cast_date(
      "start", 'UTC', 'UTC'
    )
  ) STORED,
  "end" datetime NULL,
  "end_date" date GENERATED ALWAYS AS (
    django_datetime_cast_date(
      "end", 'UTC', 'UTC'
    )
  ) STORED,
  "duration" bigint GENERATED ALWAYS AS (
    django_timestamp_diff(
      "end", "start"
    )
  ) STORED
);
COMMIT;
```

In the generated SQL code the expressions do not have complex logic, but we see the presence of the deterministic functions `django_datetime_cast_date` and `django_timestamp_diff` defined in the Django database backend for SQLite.

After running `migrate`, open a `shell` and check the model behaves as intended:

```python
>>> from samples.models import Event
>>> Event.objects.create(start="2023-1-1 12:00Z")
<Event: [∞] 2023-01-01…>
>>> Event.objects.create(
...     start="2023-1-1 11:45Z",
...     end="2023-1-9 00:00Z"
... )
<Event: [7 days, 12:15:00] 2023-01-01…2023-01-09>
```

## 🔑 A calculated JSON key field

In the `Package` model there is the `slug` field and the `data` JSONfield in which we want to store the JSON payload of the Python package, corresponding to the `slug` value, as it is returned to us by `PyPi` via its endpoint ` https://pypi.org/pypi/`. The generated field `version` will contain the latest version of the package, extracted from the JSONfield `data`.

`generatedfields/samples/models.py`

```python
from django.db import models
from django.db.models import F


class Package(models.Model):
    slug = models.SlugField()
    data = models.JSONField()
    version = models.GeneratedField(
        expression=F("data__info__version"),
        output_field=models.TextField(),
        db_persist=True,
    )

    def __str__(self):
        return f"{self.slug} {self.version}"
```

After running `makemigrations`, use `sqlmigrate` and see the SQL code that will be applied to the database:

```sql
BEGIN;
--
-- Create model Package
--
CREATE TABLE "samples_package" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "slug" varchar(50) NOT NULL,
  "data" text NOT NULL CHECK (
    (
      JSON_VALID("data")
      OR "data" IS NULL
    )
  ),
  "version" text GENERATED ALWAYS AS (
    (
      CASE
        WHEN JSON_TYPE(
          "data", '$."info"."version"'
        ) IN (
          'false', 'true', 'null'
        ) THEN JSON_TYPE(
          "data", '$."info"."version"'
        )
        ELSE JSON_EXTRACT(
          "data", '$."info"."version"'
        )
      END
    )
  ) STORED
);
CREATE INDEX "samples_package_slug_idx"
ON "samples_package" ("slug");
COMMIT;
```

The SQL code generated for SQLite is a little more complex and verbose than what we might have using another database with advanced JSON type support (for example PostgreSQL).

After running `migrate`, open a `shell` and check the model behaves as intended:

```python
>>> import json
>>> from urllib.request import urlopen
>>> from samples.models import Package
>>> slug = "django"
>>> URL = f"https://pypi.org/pypi/{slug}/json"
>>> data = json.loads(urlopen(URL).read())
>>> Package.objects.create(slug=slug, data=data)
<Package: django 4.2.7>
```

### 🔔 Note

At first glance, the case presented in this example might seem not very useful because it is limited to a simple extraction of the value corresponding to a specific key from a JSON, which in many databases is rather quick to do at run time.

In reality, if we assume that we have a large number of instances of the `Package` model, the execution of a query in which the version value is already present in its field greatly lightens its execution by simplifying the logic, and also alleviating indexes.

Furthermore, if the version were the only interesting field to extract from the JSON in the `data` field, we could defer the `data` field from our queries, speeding up them and drastically reducing the data transferred from the database (e.g. in the change list pages, in the Django admin).

## 🖇️ A calculated concatenated field

The last `User` model partially follows the one already present in Django itself, but we only have the `first_name` and the `last_name` fields. The generated field `full_name` is calculated starting from the first two on which is performed a concatenation with the addition of a space, using the appropriate `Concat` function.

`generatedfields/samples/models.py`

```python
from django.db import models
from django.db.models import Value
from django.db.models.functions import (
    Concat,
)


class User(models.Model):
    first_name = models.CharField(max_length=150)
    last_name = models.CharField(max_length=150)
    full_name = models.GeneratedField(
        expression=Concat(
            "first_name", Value(" "), "last_name",
        ),
        output_field=models.TextField(),
        db_persist=True,
    )

    def __str__(self):
        return self.full_name
```

After running `makemigrations`, use `sqlmigrate` and see the SQL code that will be applied to the database:

```sql
BEGIN;
--
-- Create model User
--
CREATE TABLE "samples_user" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "first_name" varchar(150) NOT NULL,
  "last_name" varchar(150) NOT NULL,
  "full_name" text GENERATED ALWAYS AS (
    COALESCE("first_name", '') ||
    COALESCE(COALESCE(' ', '') ||
    COALESCE("last_name", ''), '')
  ) STORED
);
COMMIT;
```

The SQL code generated for SQLite uses the concatenation operator `||` to join column values together with space and the `COALESCE` function in case one of the values is empty.

After running `migrate`, open a `shell` and check the model behaves as intended:

```python
>>> from samples.models import User
>>> User.objects.create(first_name="Jane", last_name="Doe")
<User: Jane Doe>
```

### 🔔 Note

This concatenation example is one of the most used to present the potential of the generated fields. I wanted to present it only at the end because, contrary to what it may seem at first glance, it contains pitfalls when used with other databases but I will delve into this in the next article of this series of articles on database generated columns.

## 🙋 Get involved

Thank you for getting this far with reading this article.

I hope you found it interesting to read the examples I proposed, as it was for me to formulate them and report them here. The motivation behind this work and this article is to collaborate with all Django developers to churn out features that are as solid and stable as possible.

As I have repeatedly invited you to do in my articles and talks, I invite you to **get involved**.

Try Django 5.0 and experiment with its new features, but above all report any strange behavior or bugs so that they end up being better for everyone.

## 🐙 GitHub

I have published the code presented in this article in this GitHub repository of mine to allow you to carry out experiments.

- [github.com/pauloxnet/generatedfields](https://github.com/pauloxnet/generatedfields)

## 🥠 Conclusion

As we have seen, the generated fields of Django 5.0 are a very powerful tool, which lend themselves to solving a wide variety of problems.

I think they also have the merit of bringing a lot of logic back into the database, which in most projects is the lowest layer of the project stack.

Together with the database-computed default values they also make it possible to simplify the collaboration of different applications on the same database.

In future articles, I'll try to show other examples of using Django's generated fields with other Django-supported database backends (e.g. PostgreSQL) and with other Django modules (e.g. GeoDjango).

Stay tuned.

— Paolo

---

## ⚠️ Disclaimer

This code is for demonstration purposes only and should not be used in production as is. However, the code is released without any guarantee from the author and no liability can be attributed. Use at your own risk.

## 🔗 Resources

- [GeneratedField - Django documentation ⬀](https://docs.djangoproject.com/en/5.0/ref/models/fields/#django.db.models.GeneratedField)
- [Generated Columns - SQLite documentation ⬀](https://www.sqlite.org/gencol.html)