DEV Community: Paolo Melchiorre

Database generated columns (3): GeoDjango & PostGIS

Paolo Melchiorre — Mon, 11 Dec 2023 00:00:00 +0000

An introduction to database generated columns, using PostgGIS, GeoDjango 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 GeoDjango and PostgGIS as the database backend:

from django.contrib.gis.db import models
from django.contrib.gis.db.models.functions import (
    GeoHash,
)


class City(models.Model):
    name = models.TextField()
    point = models.PointField()
    geohash = models.GeneratedField(
        expression=GeoHash("point"),
        output_field=models.TextField(),
        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 PostGIS 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.

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

🕶️ Attributes

`db_persist`

In this article, we're going to use PostGIS 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):

$ python3 --version
Python 3.12.0

⚗️ Virtual environment

A Python virtual environment:

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

🦄 Django

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

$ python3 -m pip install django==5.0

🐘 Psycopg

We’ll use the Python PostgreSQL database adapter which requires installing psycopg greater than the 3.1.8 version.

$ python3 -m pip install psycopg[binary]~=3.1.8

⚠️ Warning

The psycopg[binary] package is meant for beginners to start playing with Python and PostgreSQL without the need to meet the build requirements. For production use, you are advised to use the "Local installation". ⬀

🌎 GDAL

GDAL (Geospatial Data Abstraction Library) is a mandatory GeoDjango requirement and we need to install its binaries:

on Debian-based GNU/Linux distributions (e.g. Debian 10-12, Ubuntu 20.04-23.04, …):

$ sudo apt install gdal-bin

⚠️ Warning

For other platform-specific instructions read the Installing Geospatial libraries ⬀ in the Django documentation.

🗃️ The `generatedfields` project

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

$ cd ~/projects

and then use the startproject Django command:

$ python3 -m django startproject generatedfields

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

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

🎛️ Activating PostGIS

To use Postgres as the database in our Django project, we modify the project database settings, adding the PostGIS engine and the connection parameters of our PostgreSQL database:

generatedfields/generatedfields/settings.py

DATABASES = {
    "default": {
        "ENGINE": "django.contrib.gis.db.backends.postgis",
        "HOST": "<my_database_host>",
        "NAME": "<my_database_name",
        "PASSWORD": "<my_database_password>",
        "PORT": "<my_database_port>",
        "USER": "<my_database_user>",
    }
}

⚠️ Warning

You need to create/activate a PostgreSQL database instance (e.g. with system packages, with Docker or as a remote service) and then replace your credentials in the DATABASE settings.

🧩 Activating GeoDjango

We can now activate GeoDjango by adding the django.contrib.gis module to the INSTALLED_APPS, in our project settings.

generatedfields/generatedfields/settings.py

INSTALLED_APPS = [
    "django.contrib.admin",
    "django.contrib.auth",
    "django.contrib.contenttypes",
    "django.contrib.sessions",
    "django.contrib.messages",
    "django.contrib.staticfiles",
    "django.contrib.gis",
]

🗂️ The `samples` app

After switching to the generatedfields directory:

$ cd generatedfields

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

$ python3 -m django startapp samples

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

$ 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

INSTALLED_APPS = [
    "django.contrib.admin",
    "django.contrib.auth",
    "django.contrib.contenttypes",
    "django.contrib.sessions",
    "django.contrib.messages",
    "django.contrib.staticfiles",
    "django.contrib.gis",
    "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:

$ python3 -m manage makemigrations --no-header

`sqlmigrate`

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

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

`migrate`

Apply migration to our database with:

$ python3 -m manage migrate

`shell`

Start an interactive Django shell:

$ python3 -m manage shell

📇 A calculated GeoHash field

The geographical field from which we start is the Point. Using a pair of latitude and longitude we can define the space position of an object. We created a City template with a text field to store the name and point field.

GeoDjango provides many functions that can be applied to a geographic field to extract information or apply transformations.

In our case, we created two calculated fields: the first generates the GeoHash starting from the Point field, and the second generates the GeoJSON of the Point field. GeoHash can be very convenient for representing coordinates, while GeoJson could be used to transmit geographic information in a standard way.

generatedfields/samples/models.py

from django.contrib.gis.db import models
from django.contrib.gis.db.models.functions import (
    AsGeoJSON,
    GeoHash,
)
from django.db.models.functions import (
    Cast,
)


class City(models.Model):
    name = models.TextField()
    point = models.PointField()
    geohash = models.GeneratedField(
        expression=GeoHash("point"),
        output_field=models.TextField(),
        db_persist=True,
    )
    geojson = models.GeneratedField(
        expression=Cast(
            AsGeoJSON("point"),
            models.JSONField(),
        ),
        output_field=models.JSONField(),
        db_persist=True,
    )

    def __str__(self):
        return (
            f"{self.name} "
            f"({self.geohash})"
        )

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

BEGIN;
--
-- Create model City
--
CREATE TABLE "samples_city" (
  "id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "name" text NOT NULL,
  "point" geometry(POINT, 4326) NOT NULL,
  "geohash" text GENERATED ALWAYS AS (
    ST_GeoHash("point")
  ) STORED,
  "geojson" jsonb GENERATED ALWAYS AS (
    (ST_AsGeoJSON("point", 8, 0))::jsonb
  ) STORED
);
CREATE INDEX "samples_city_point_id"
ON "samples_city" USING GIST ("point");
COMMIT;

The SQL code generated for PostGIS uses the ST_GeoHash function for the creation of the GeoHash and the ST_AsGeoJSON function for the generation of the GeoJSON to which a cast to JSONB format is applied.

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

>>> from samples.models import City
>>> name = "Pescara, IT"
>>> point = "POINT(14.21 42.47)"
>>> City.objects.create(name=name, point=point)
<City: Pescara, IT (sfw0y6venxzrb89yzr2q)>

I added the data of my hometown, Pescara, Italy, simply by providing its latitude and longitude directly using the well-known text (WKT) format.

🔔 Note

In the Django shell, we only show the automatically calculated GeoHash field, but in a very similar way, we could use the GeoJson field in a view to return all the other model instances without having to recalculate the GeoJSON.

📏 A calculated length field

In GeoDjango we can define fields more complex than a point, and in this case, we used a LineStringField to represent a Route, in addition to a text field to store its name.

We added three example calculated fields from the line: the line length field, the field containing the number of points that define the line, and a Point field that defines the center of the line.

The Length field is defined in meters and its use is immediate in the case of a LineStringField. The field that defines the number of points instead denotes the complexity of a line. Finally, the Centroid is a point that more or less defines the center of the line itself, it can be convenient for example to represent it on a map when it is displayed at such a high zoom level as to make the detail of the line useless.

generatedfields/samples/models.py

from django.contrib.gis.db import models
from django.contrib.gis.db.models.functions import (
    Centroid,
    Length,
    NumPoints,
)


class Route(models.Model):
    name = models.TextField()
    line = models.LineStringField()
    length = models.GeneratedField(
        db_persist=True,
        expression=Length("line"),
        output_field=models.FloatField(),
    )
    numpoints = models.GeneratedField(
        db_persist=True,
        expression=NumPoints("line"),
        output_field=models.IntegerField(),
    )
    centroid = models.GeneratedField(
        db_persist=True,
        expression=Centroid("line"),
        output_field=models.PointField(),
    )

    def __str__(self):
        return (
            f"{self.name} "
            f"(~{self.length/1000:.1f} km)"
        )

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

BEGIN;
--
-- Create model Route
--
CREATE TABLE "samples_route" (
    "id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    "name" text NOT NULL,
    "line" geometry(LINESTRING, 4326) NOT NULL,
    "length" double precision GENERATED ALWAYS AS (
        ST_LengthSpheroid(
            "line",
            'SPHEROID["WGS 84",6378137.0,298.257223563]'
        )
    ) STORED,
    "numpoints" integer GENERATED ALWAYS AS (
        ST_NPoints("line")
    ) STORED,
    "centroid" geometry(POINT, 4326) GENERATED ALWAYS AS (
        ST_Centroid("line")
    ) STORED
);
CREATE INDEX "samples_route_line_id"
ON "samples_route" USING GIST ("line");
COMMIT;

The SQL code generated for PostGIS uses the functions: ST_NPoints to extract the number of points on the line, ST_Centroid to calculate the center point of the line, and ST_LengthSpheroid to calculate the length of the line on a spheroid.

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

>>> from samples.models import Route
>>> name = "90 Mile Straight, AU"
>>> line = "LINESTRING(123.944 -32.455, 125.484 -32.27)"
>>> Route.objects.create(name=name, line=line)
<Route: 90 Mile Straight, AU (~146.4 km)>

In this example we have represented the longest straight route in the world as a straight line, it is a stretch of the Eyre Highway between Perth and Adelaide, 90 miles long, in Australia.

🔔 Note

In the Django shell we have only represented the length converted into kilometers, but the other two automatically calculated fields are also very useful, for example, to indicate the complexity of a route or to represent the route via the point that represents its center.

📐 A calculated area field

Moving up the scale of complexity of the geometries that can be represented in GeoDjango, we used a polygon field to approximate the surface of a State in addition to the text field to specify its name.

We have specified three example calculated fields that use the polygon as the base. The area field automatically calculates the surface of the polygon, the bounding box field represents the rectangle containing the polygon and the point field represents the approximation of the polygon center via a point on its surface.

generatedfields/samples/models.py

from django.contrib.gis.db import models
from django.contrib.gis.db.models.functions import (
    Area,
    Envelope,
    PointOnSurface,
)


class State(models.Model):
    name = models.TextField()
    polygon = models.PolygonField()
    area = models.GeneratedField(
        db_persist=True,
        expression=Area("polygon"),
        output_field=models.FloatField(),
    )
    bbox = models.GeneratedField(
        db_persist=True,
        expression=Envelope("polygon"),
        output_field=models.PolygonField(),
    )
    point = models.GeneratedField(
        db_persist=True,
        expression=PointOnSurface(
            "polygon"
        ),
        output_field=models.PointField(),
    )

    def __str__(self):
        return (
            f"{self.name} "
            f"(~{self.area*10000:.0f} km²)"
        )

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

BEGIN;
--
-- Create model State
--
CREATE TABLE "samples_state" (
    "id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    "name" text NOT NULL,
    "polygon" geometry(POLYGON, 4326) NOT NULL,
    "area" double precision GENERATED ALWAYS AS (
        ST_Area("polygon")
    ) STORED,
    "bbox" geometry(POLYGON, 4326) GENERATED ALWAYS AS (
        ST_Envelope("polygon")
    ) STORED,
    "point" geometry(POINT, 4326) GENERATED ALWAYS AS (
        ST_PointOnSurface("polygon")
    ) STORED
);
CREATE INDEX "samples_state_polygon_8801f79a_id"
ON "samples_state" USING GIST ("polygon");
COMMIT;

Three functions are used in the SQL code generated for PostGIS: ST_Area to calculate the area of the polygon, ST_Envelope to calculate the bounding box containing the polygon and ST_PointOnSurface to calculate the point on the surface.

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

>>> from samples.models import State
>>> name = "Colorado, US"
>>> polygon = "POLYGON((-109 37, -109 41, -102 41, -102 37, -109 37))"
>>> State.objects.create(name=name, polygon=polygon)
<State: Colorado, US (~280000 km²)>

In this example, I have inserted an approximation of the polygon representing the US state of Colorado. Although I believe it is the state with a perimeter as close as possible to a rectangle, in reality, it contains several irregularities and this is why the calculated area value is greater than its real size.

🔔 Note

This time too in the Django shell we have only represented one of the calculated fields: the area. The other fields are also very useful for representing a polygon on a map, for example, the bounding box is useful for defining the area in which to position the map so that the entire polygon is visible inside it, and the calculated point is similar to the Centroid defined in the previous example, but it ensures that it is always on the surface in the case of a very irregular polygon, and is very useful for representing the geometry as a point in case we are viewing the map at a very high zoom level.

🗺️ Maps

If you want to view the automatically generated geographic fields on a map, I recommend you follow the instructions I have already published to create a map with Django in my 2-part series "Maps with Django":

🙋 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.

🥠 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.

That's all!

— 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

Database generated columns (2): Django & PostgreSQL

Paolo Melchiorre — Thu, 23 Nov 2023 23:00:00 +0000

An introduction to database generated columns, using PostgreSQL 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 PostgreSQL as the database backend:

from django.db import models
from django.contrib.postgres.search import (
    SearchVector, SearchVectorField
)


class Quote(models.Model):
    author = models.TextField()
    text = models.TextField()
    search = models.GeneratedField(
        db_persist=True,
        expression=SearchVector(
            "text", config="english"
        ),
        output_field=SearchVectorField(),
    )

🚀 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.

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 PostgreSQL as a database backend.

🤖 Generated Field

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.

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

🕶️ Attributes

`db_persist`

In this article, we're going to use PostgreSQL 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`

↔️ 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):

$ python3 --version
Python 3.12.0

⚗️ Virtual environment

A Python virtual environment:

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

🦄 Django

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

$ python3 -m pip install django==5.0

🐘 Psycopg

We’ll use the Python PostgreSQL database adapter which requires installing psycopg greater than the 3.1.8 version.

$ python3 -m pip install psycopg[binary]~=3.1.8

⚠️ Warning

The psycopg[binary] package is meant for beginners to start playing with Python and PostgreSQL without the need to meet the build requirements. For production uses you are advised to use the "Local installation". ⬀

🗃️ The `generatedfields` project

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

$ cd ~/projects

and then use the startproject Django command:

$ python3 -m django startproject generatedfields

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

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

🎛️ Activating PostgreSQL

To use Postgres as the database in our Django project, we modify the project database settings, adding the PostgreSQL engine and the connection parameters of our PostgreSQL database:

generatedfields/generatedfields/settings.py

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "HOST": "<my_database_host>",
        "NAME": "<my_database_name",
        "PASSWORD": "<my_database_password>",
        "PORT": "<my_database_port>",
        "USER": "<my_database_user>",
    }
}

⚠️ Warning

You need to create/activate a PostgreSQL database instance (e.g. with system packages, with Docker or as a remote service) and then replace your credentials in the DATABASE settings.

🗂️ The `samples` app

After switching to the generatedfields directory:

$ cd generatedfields

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

$ python3 -m django startapp samples

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

$ 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

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.

🔧 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:

$ python3 -m manage makemigrations --no-header

`sqlmigrate`

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

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

`migrate`

Apply migration to our database with:

$ python3 -m manage migrate

`shell`

Start an interactive Django shell:

$ python3 -m manage shell

♻️ Common generated fields

In my previous article in this series (Database generated columns⁽¹⁾: Django & SQLite), I showed nine examples of generated fields using SQLite.

The first eight examples can be used as they were shown even with PostgreSQL as the database.

By viewing the generated SQL code with the sqlmigrate command you will notice some differences because different syntaxes and specific field types will be used, or PostgreSQL capabilities absent in SQLite will be exploited.

The difference in the SQL code generated by PostgreSQL is relevant when viewing the example "🔑 A calculated JSON key field" where the data type for JSON fields and the specific syntax for extracting keys is used.

The ninth example "🖇️ A calculated concatenated field", however, although simple from a functionality point of view, highlights a problem that should be kept in mind when using generated fields, namely that only IMMUTABLE functions can be used.

Unfortunately in the SQL code, the concatenation function that the ORM uses when PostgreSQL is the selected database is not IMMUTABLE, and I will show you a way I found to get around this.

I copied the two examples from my previous article below in full for completeness, showing the difference in the SQL or Python code.

🔑 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

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:

BEGIN;
--
-- Create model Package
--
CREATE TABLE "samples_package" (
  "id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "slug" varchar(50) NOT NULL,
  "data" jsonb NOT NULL,
  "version" text GENERATED ALWAYS AS (
    ("data"#>'{info,version}')
  ) STORED
);
CREATE INDEX "samples_package_slug_idx"
ON "samples_package" ("slug");
CREATE INDEX "samples_package_slug_idx_like"
ON "samples_package" ("slug" varchar_pattern_ops);
COMMIT;

The SQL code generated for PostgreSQL is decidedly simpler than that generated for other databases (for example SQLite) because it uses the JSON type with its specific functions.

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

>>> 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

from django.db import models
from django.db.models import Value


class ConcatOp(models.Func):
    arg_joiner = " || "
    function = None
    output_field = models.TextField()
    template = "%(expressions)s"


class User(models.Model):
    first_name = models.CharField(max_length=150)
    last_name = models.CharField(max_length=150)
    full_name = models.GeneratedField(
        expression=ConcatOp(
            "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:

BEGIN;
--
-- Create model User
--
CREATE TABLE "samples_user" (
  "id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "first_name" varchar(150) NOT NULL,
  "last_name" varchar(150) NOT NULL,
  "full_name" text GENERATED ALWAYS AS (
    "first_name" || ' ' || "last_name"
  ) STORED
);
COMMIT;

Using the custom ConcatOp function the SQL code generated for PostgreSQL uses the concatenation operator || to join column values together.

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

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

🔔 Note

I defined the ConcatOp function to make this example work also in PostgreSQL because with the Concat function defined by the Djanngo ORM it would have failed as it would have used the CONCAT SQL function which is not IMMUTABLE.

I don't recommend you use this function in production, but rather you can try to lend a hand to modify the current implementation in Django ORM which is in progress in this pull request: Refs #34955 -- Simplified implementation of Concat ⬀

🧩 Activating PostgreSQL module

As we have seen in all the examples of using the generated fields shown with SQLite, with some small differences they also work with PostgreSQL, it was enough to change the backend database in the settings of our project.

However, there are specific features that are contained in the specific module for PostgreSQL (present in Django since version 1.8) and which can be exploited after adding it to the list of INSTALLED_APPS of our project settings.

generatedfields/generatedfields/settings.py

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

🔍 A calculated search vector field

Once the PostgreSQL module is activated, we can add a calculated search vector field, in a document model with a content text field. The search vector field is updated with each change to the content of the document, this makes it possible to perform the full-text search immediately eliminating the time and effort of its update.

generatedfields/samples/models.py

from django.db import models
from django.contrib.postgres.search import (
    SearchVector, SearchVectorField
)


class Quote(models.Model):
    author = models.TextField()
    text = models.TextField()
    search = models.GeneratedField(
        db_persist=True,
        expression=SearchVector(
            "text", config="english"
        ),
        output_field=SearchVectorField(),
    )

    def __str__(self):
        return f"[{self.author}] {self.text}"

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

BEGIN;
--
-- Create model Quote
--
CREATE TABLE "samples_quote" (
  "id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "author" text NOT NULL,
  "text" text NOT NULL,
  "search" tsvector GENERATED ALWAYS AS (
    to_tsvector('english'::regconfig, COALESCE("text", ''))
  ) STORED
);
COMMIT;

The SQL code is specific to PostgreSQL and exploits the expressions and functions of full-text search, which however are used automatically without the need for triggers or store procedures to keep the search vector column updated.

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

>>> from samples.models import Quote
>>> author = "Plato"
>>> text = "Man is a being in search of meaning"
>>> Quote.objects.create(author=author, text=text)
<Quote: [Plato] Man is a being in search of meaning>
>>> Quote.objects.values_list("search")
<QuerySet [("'man':1 'mean':8 'search':6",)]>
>>> Quote.objects.filter(search="meanings").first()
<Quote: [Plato] Man is a being in search of meaning>

🔔 Note

What was done with generated fields could also be achieved using functional indexes, but having a generated field greatly simplifies the investigation of their content, which is very useful for the continuous improvement of search mechanisms.

We have already pointed out that the generated fields, like functional indexes, have the constraint of operating only on the columns of the same table.

But, if we wanted to include fields from other tables in the search mechanisms, we could not do it with functional indexes, but only using a search vector field, updated with more complex systems.

🧮 A calculated array length field

Another specific field that can be used after activating the PostgreSQL module is the ArrayField. This field opens up many interesting usage scenarios such as storing values of the same type (e.g. URL fields) in a single column. In this case, we used an array field of integers which we are going to count in the generated field.

generatedfields/samples/models.py

from django.db import models
from django.contrib.postgres.fields.array import (
    ArrayField,
    ArrayLenTransform,
)


class Landmark(models.Model):
    name = models.TextField()
    reviews = ArrayField(
        models.SmallIntegerField()
    )
    count = models.GeneratedField(
        db_persist=True,
        expression=ArrayLenTransform(
            "reviews"
        ),
        output_field=models.IntegerField(),
    )

    def __str__(self):
        return (
            f"{self.name} "
            f"({self.count} reviews)"
        )

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

BEGIN;
--
-- Create model Landmark
--
CREATE TABLE "samples_landmark" (
  "id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "name" text NOT NULL,
  "reviews" smallint [] NOT NULL,
  "count" integer GENERATED ALWAYS AS (
    CASE
      WHEN "reviews" IS NULL THEN NULL
      ELSE coalesce(
        array_length("reviews", 1),
        0
      )
    END
  ) STORED
);
COMMIT;

In the generated SQL code we see the specific syntax used for array-type columns and the function for calculating the length of the field which we use to count how many values are present in the column.

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

>>> from samples.models import Landmark
>>> name = "Colosseum"
>>> reviews = [4, 3, 5, 4, 4, 5, 5, 3, 4]
>>> Landmark.objects.create(
...     name=name, reviews=reviews
... )
<Landmark: Colosseum (9 reviews)>

🔔 Note

Unfortunately in the ORM, there are not many functions ready to work with Array fields, but there are many available in PostgreSQL (e.g. concatenation, value extraction, etc ...) that can be used in Django by defining a custom function or proposing addition in Django's PostgreSQL module.

⏳ A calculated date range field

The Range type is also an interesting data type available thanks to Django's PostgreSQL module. Range fields of various types can be created (e.g. integers) and very useful comparison operations can then be carried out (e.g. containment, overlaps, etc...).

In this case, we have created a generated date range type field that we create starting from your normal date fields.

Missing a ready-to-use function in the Django ORM, we defined our custom DateRange function to then be used in the generated field.

generatedfields/samples/models.py

from django.db import models
from django.contrib.postgres.fields import (
    DateRangeField,
)


class DateRangeFunc(models.Func):
    function = "DATERANGE"
    output_field = DateRangeField()


class Booking(models.Model):
    start = models.DateField()
    end = models.DateField()
    span = models.GeneratedField(
        expression=DateRangeFunc(
            "start", "end"
        ),
        output_field=DateRangeField(),
        db_persist=True,
    )

    def __str__(self):
        return (
            f"{self.span.bounds[0]}"
            f"{self.span.lower.isoformat()} -"
            f"> {self.span.upper.isoformat()}"
            f"{self.span.bounds[1]}"
        )

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

BEGIN;
--
-- Create model Booking
--
CREATE TABLE "samples_booking" (
  "id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  "start" date NOT NULL,
  "end" date NOT NULL,
  "span" daterange GENERATED ALWAYS AS (
    DATERANGE("start", "end")
  ) STORED
);
COMMIT;

The SQL code generated for PostgreSQL in this case uses only the specific data type for the date range and the function that generates it starting from two single dates.

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

>>> from datetime import date
>>> from samples.models import Booking
>>> Booking.objects.create(
...     start="2023-1-1",
...     end="2023-1-9"
... )
<Booking: [2023-01-01 -> 2023-01-09)>
>>> Booking.objects.filter(
...     span__contains=date(2023, 1, 5)
... )
<QuerySet [<Booking: [2023-01-01 -> 2023-01-09)>]>

🔔 Note

The range data type is very convenient for making complex range queries with little effort. Having ranges automatically calculated with the generated fields allows you not to change the experience of the user who has to provide this data, but at the same time makes the power of this data type available.

Some functions or features are not yet supported by the Django ORM (e.g. multi ranges) but it is not excluded that they will be added in the future.

🙋 Get involved

Thank you for getting this far with reading this article.

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.

🥠 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 modules (e.g. GeoDjango).

Stay tuned.

— Paolo

⚠️ Disclaimer

🔗 Resources

Database generated columns (1): Django & SQLite

Paolo Melchiorre — Mon, 06 Nov 2023 23:00:00 +0000

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:

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.

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

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.

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`

↔️ 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):

$ python3 --version
Python 3.12.0

⚗️ Virtual environment

A Python virtual environment:

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

🦄 Django

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

$ python3 -m pip install django==5.0

🗃️ The `generatedfields` project

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

$ cd ~/projects

and then use the startproject Django command:

$ python3 -m django startproject generatedfields

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

$ 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:

$ cd generatedfields

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

$ python3 -m django startapp samples

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

$ 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

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.

🔧 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:

$ python3 -m manage makemigrations --no-header

`sqlmigrate`

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

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

`migrate`

Apply migration to our database with:

$ python3 -m manage migrate

`shell`

Start an interactive Django shell:

$ 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

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:

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:

>>> 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

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:

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:

>>> 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

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:

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:

>>> 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

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:

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:

>>> 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

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:

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:

>>> 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

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:

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:

>>> 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

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:

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:

>>> 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

generatedfields/samples/models.py

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:

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:

>>> 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

🖇️ A calculated concatenated field

generatedfields/samples/models.py

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:

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:

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

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

🥠 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

🔗 Resources

About my proposal for the Django Core Sprints 🌅

Paolo Melchiorre — Tue, 25 Oct 2022 22:00:00 +0000

Originally published on: https://www.paulox.net/2022/10/26/about-my-proposal-for-the-django-core-sprints/

The story of how the proposal to organize sprints on the Django core was born and how I proposed it during the past DjangoCon US 2022 in San Diego to various components of the Django community.

Introduction 🧭

The first Python community I ever participated in was that of the Zope and Plone developers and I remember from that time on how they occasionally organized sprints for a few days to work together on internal issues. The idea has remained in my mind over the years, I have seen other communities organize similar events and I think perhaps I have mentioned the idea in an impromptu way to some members of the community.

Foreword 🗺️

I was lucky enough to attend the last DjangoCon US 2022 in San Diego. After years of conferences in Europe it was the first time for me to participate in a DjangoCon US and it was a fantastic experience for which I thank again the organizers, the speakers, the volunteers but above all the participants. I had the opportunity to meet many new members of the Django community, many I had known for some time I met them for the first time in person and others I had met in person in Europe I met them again with pleasure in San Diego.

Attending a conference in San Diego meant for me to travel for 36 hours (other participants even more), take various means of transport, a long-haul flight and find myself 9 hours away from home with jet lag to be disposed of. In my case, this has accumulated the stress of attending a conference in the US for the first time, communicating in a language that is not mine and having, with pleasure, to present a technical talk on Django.

On Wednesday evening, after the three-day conference ended, Mariusz Felisiak proposed to the people who remained to chat to meet the next morning at 6:30 AM to go for a swim in the Pacific Ocean, on the closest San Diego beach from the conference venue. Since jet lag led me to wake up very early every morning, the time did not scare me. I love the sea, since I live on the shores of the Adriatic Sea, and for this I accepted with pleasure.

Epiphany ☄️

So on Thursday morning we arrived at sunrise on Ocean Beach in San Diego with Mariusz, Wil Klopp and Simon Charette and shortly after we were joined by Carlton Gibson and Will Vincent by bike. The sea was full of morning surfers, the beach was deserted and we were the only ones to take a swim in the cold water of the Pacific Ocean. The swim was short but it was an excellent way to release the stress from the previous days.

After the swim we took a walk in the Ocean Beach area, to go for a coffee in a nearby café and while walking we exchanged impressions on the conference that just ended, we talked about the effort made to be present there and the relief for that moment of leisure after so much stress.

So I thought that that could be the formula for the idea I had in mind that is to organize sprints to work on the core of Django with other active developers of the community, with moments of conviviality to create greater harmony among the participants and moments of technical commitment to work on future key Django features.

The idea was to avoid the stress of an overly demanding trip, the commitment of attending a three-day conference or the responsibility of having to present a talk or a keynote in front of Django's top experts, often in a language other than one's own.

I remember that Mariusz and Simon seemed enthusiastic about the idea, I was sure I had already mentioned it to someone, perhaps even in the previous days, but at that moment I had it very clear in my mind and so I talked about it during the morning of sprint also with Will and Jeff Triplett, who confirmed the positive impression already received.

The proposal 💍

Will asked me to formulate the idea to be presented to the board of the Django Software Foundation and on the long trip back to the case I had the time to better organize the idea that I present to you here.

The Django Core Sprints should be an annual or semi-annual meeting lasting 2 or 3 days to be organized possibly in a period of the year compatible with the work or family life of the participants (e.g. avoiding the Christmas period or school holidays) and far enough away from important conferences (eg: DjangoCon, PyCon, EuroPython) and certainly not in continuity with one of them to relieve the participants from organizational, voluntary or speaking commitments.

Participation in person would be important, but without excluding any participants who were unable to participate for health, work or family reasons, providing for them a hybrid mode of participation at least in the technical part of the sprint.

The chosen location should require everyone to make a similar effort to reach it, should allow them to participate safely (e.g. avoiding nations that are not safe for some), allow some kind of outdoor leisure activity and preferably provide convivial accommodation for a small number of participants, which would allow not to exclude large cities or tourist centers, and choose centers well connected by the transport system that would allow participants to avoid trips with many stopovers, with different means of transport and would allow to drastically reduce the time and travel costs.

Ideally, the sprint participants should be the developers most recently active in contributing to the code of the Django organization, the members of the techboard (which may change its name in the future) and above all participants in the mentorship program (which will probably start in the future).

The technical activities to be carried out should be clearly chosen before the sprints and should preferably converge on a single goal (e.g. completing work on a key Django feature or proposing future Django features) and should preferably focus on a single repository of the Django group that is considered of key importance from time to time (eg: django, channels, djangoproject, ...) providing a final report of the sprint to be shared with the whole Django community.

Technical activities should be alternated with some non-compulsory recreational activities to be carried out in groups or subgroups freely chosen by the participants and also agreed before the event (eg: sports activities, dinners, sporting or cultural events, ...).

The sprints would be financed by the Django Software Foundation, by some employers of the participants or by third party sponsors (eg: PSF, companies, organizations, ...) interested in completing key Django functionalities or simply interested in financing specific activities of the DSF.

The goals 🎯

The main goal of such a sprint would be to create the ideal conditions for the most active developers to finalize functionalities considered key for Django or the joint formulation of new proposals to be submitted to the Dajngo community.

The other goal would be to foster harmony in the group of developers through personal knowledge and the creation of direct communication channels.

The ultimate goal would be to create regular Django community meetings free of the stress associated with a conference (e.g. organization, participation, volunteering, speaking, …).

Conclusions 💡

The community is the beating heart of any open source software and creating moments of meeting and sharing in person is one of the best ways to grow the community. Two years of canceled conferences have shown us how isolation leads to the weakening of the community and also of individual members (burnout cases have increased). At the same time it's stressful to get back to such big events and Django Core Sprints could be a possibility for the sake of Django development.

Other proposals ⚗️

There are other initiatives that I would like to propose for the future development of Django that I take this opportunity to mention here:

Remote collaborations funded with targeted goals with open source developers external to the community (e.g. collaboration with Daniele Varrazzo to complete and test the new PostgreSQL backend based on psycopg version 3 he created)
Remote workshops with core Python developers to train Django core developers, enhance synergy with the language, but also give feedback to improve language development
Remote summits with friendly communities to discuss or work on common projects (e.g. discuss ASGI with Flask, Starlette, FastAPI or DB-API with SQLAlchemy, SQLModel, Peewee)

Links 🔗

Tweets 🐦

@pauloxnet - Ocean Beach swim ↗️
@pauloxnet - DjangoCon US 2022 sprints ↗️
@wsvincent1 - Will Vincent proposals ↗️

FLOSS and linguistic diversity

Paolo Melchiorre — Wed, 12 May 2021 00:00:00 +0000

Thoughts on linguistic diversity in FLOSS, arising from the Italian translation of the Django documentation.

TL;DR Over the past week, the Italian Django community has translated an important part of Django's documentation into Italian, allowing for online publication and considerably increasing linguistic diversity in the community.

Linguistic diversity

“If you talk to a man in a language he understands, that goes to his head. If you talk to him in his language, that goes to his heart.”
— Nelson Mandela

I have been dealing with FLOSS (Free Libre and Open Source software) for more than 20 years and I must admit that the community has grown a lot and the issues dealt with within it have improved.

A very important issue that has become important lately is the diversity of people in the community at all levels. I am happy that these issues are particularly present in the Django community, the one in which I participate most actively. I also try to contribute to the increase of diversity, among other things by participating as a coach in the Django Girls workshops.

One type of diversity that is rarely talked about, in my opinion, is linguistic diversity. Unfortunately, in an area such as computer science where English has been the lingua franca from the very beginning, it is easy to forget that in reality most of the users and developers of FLOSS software are not native English speakers.

Personally, I had to combine my work commitment and participation in the community with the study of the English language in order to effectively contribute to the community, for example to write my blog or to give my presentations at conferences.

Many developers, mostly native English speakers, will tell you, in good faith, that the language problem is secondary, that you just need to contribute the code. The reality of the facts is unfortunately different. More than once I have been forced to edit a PR more to fix the English text inevitably contained in it (eg: docstring) than to improve the code, and sometimes just to "harmonize" the English style in that project.

My personal experience and also that of the local FLOSS communities I attend make me declare with certainty that the first barrier to entry into the FLOSS community is the language barrier, which is even more so for newcomers to the community.

Django documentation

"Everything you need to know about Django."
— Django documentation

A week ago, on Twitter, Curtis Maloney expressed his surprise at not seeing an Italian translation of the Django documentation. At that point I realized that that lack was a major barrier to entry to the Django community, which kept out a huge number of potential contributors and even more of Italian speaking Django users around the world.

After pointing this out, with the contribution of the fantastic Italian Django community a competition was started to translate the hundreds of untranslated strings in the introduction of the Django documentation. Thanks to the generous contribution of so many members of the community, to my surprise, the task was completed in just five days.

I hope that the availability of the Italian translation of the Django documentation will have the ripple effect of further increasing linguistic diversity in the community and helping to complete the translation completely.

Improvement proposal

"… higher scores indicate material that is easier to read; lower numbers mark passages that are more difficult to read."
— Flesch–Kincaid readability tests (Wikipedia)

The international Django community should increase the attention dedicated to this type of diversity with a very simple initiative:

increase the readability of the English used in its documentation, code, website and communications in general.

There are established algorithms for measuring the readability of an English text (e.g. Flesch–Kincaid) and also many excellent python libraries (e.g. textstat) that support these algorithms. It would be enough to start measuring the readability of the published texts (e.g. documentation) and rephrase the more complex parts using a more accessible English.

A more readable English would have two immediate effects:

it would help community members who are not native English speakers to read, therefore have limited vocabulary or are not familiar with local American or English slang
it would simplify the work of translating the documentation into other languages, allowing an increase and access to people who do not know English at all

I hope to continue to be an active member of this community and to make my contribution so that it grows also from the point of view of diversity, including linguistic diversity.

You can find the names of all Italian translators in the PR: github.com/django/django-docs-translations/pull/29

License

This article and related presentation is released with Creative Commons Attribution ShareAlike license (CC BY-SA)

Original

Originally posted on my blog:

https://www.paulox.net/2021/05/12/floss-and-linguistic-diversity/

Django 3.2 - News on compressed fixtures and fixtures compression

Paolo Melchiorre — Tue, 06 Apr 2021 00:00:00 +0000

In the Django 3.2 version just released I contributed with new features related to compressed fixtures and fixtures compression. In this article I have explored into the topic and produced some sample benchmarks.

Management Commands

As reported in the documentation, the changes are related to the scope of the management commands.

loaddata now supports fixtures stored in XZ archives (.xz) and LZMA archives (.lzma).
dumpdata now can compress data in the bz2, gz, lzma, or xz formats.

loaddata

The loaddata command searches for and loads the contents of the named fixture into the database.

Compressed fixtures

In the Django 3.2 version was addes support for xz archives (.xz) and lzma archives (.lzma).

Fixtures may be compressed in zip, gz, bz2, lzma, or xz format.

For example $ django-admin loaddata mydata.json would look for any of mydata.json, mydata.json.zip, mydata.json.gz, mydata.json.bz2, mydata.json.lzma, or mydata.json.xz.

The first file contained within a compressed archive is used.

dumpdata

The dumpdata outputs all data in the database associated with some or installed applications. The output of dumpdata can be used as input for loaddata.

Fixtures compression

In the Django 3.2 version was addes support to dump data directly to a compressed file.

The output file can be compressed with one of the bz2, gz, lzma, or xz formats by ending the filename with the corresponding extension.

For example, to output the data as a compressed JSON file $ django-admin dumpdata -o mydata.json.gz

Benchmarks

After the development of the new fixtures compression function I carried out benchmarks for all supported file formats starting from different databases, from small projects to larger ones.

The benchmark were performed on my pc and are only examples of the relationship between time, file size, memory and cpu occupation that is needed to export data directly into different type of compressed files.

System info

import os
import platform

print(f"Architecture:\t{platform.architecture()[0]}")
print(f"Machine type:\t{platform.machine()}")
print(f"System glibc:\t{platform.libc_ver()[1]}")
print(f"System memory:\t{os.sysconf('SC_PAGE_SIZE') * os.sysconf('SC_PHYS_PAGES')}")
print(f"System release:\t{platform.release()}")
print(f"System type:\t{platform.system()}")
print(f"Python impl.:\t{platform.python_implementation()}")
print(f"Python version:\t{platform.python_version()}")

Architecture:   64bit
Machine type:   x86_64
System glibc:   2.32
System memory:  33402449920
System release: 5.8.0-48-generic
System type:    Linux
Python impl.:   CPython
Python version: 3.8.6

Benchmark 01

type	time	memory	cpu	size
txt	0.75	70300	99	826
gz	0.66	70920	99	312
bz2	0.69	70372	99	351
xz	0.67	86832	99	336

Benchmark 02

type	time	memory	cpu	size
txt	0.67	70260	99	1202
gz	0.66	70868	99	501
bz2	0.66	70560	99	538
xz	0.68	86860	99	532

Benchmark 03

type	time	memory	cpu	size
txt	1.03	72856	98	872126
gz	1.08	72904	99	30446
bz2	1.21	79024	99	20664
xz	1.14	96608	99	23252

Benchmark 04

type	time	memory	cpu	size
txt	1.53	71304	98	2138437
gz	1.60	72004	98	257593
bz2	1.71	77732	98	198347
xz	2.42	107252	99	164072

Benchmark 05

type	time	memory	cpu	size
txt	2.10	74240	98	5252952
gz	2.25	74236	98	405580
bz2	2.69	80592	98	334556
xz	3.22	137004	99	238432

Benchmark 06

type	time	memory	cpu	size
txt	55.31	87012	73	12092981
gz	71.97	87200	71	845193
bz2	53.74	93372	74	688968
xz	73.19	180936	73	768812

Benchmark 07

type	time	memory	cpu	size
txt	118.74	86344	74	36035128
gz	183.11	86572	71	3936656
bz2	158.76	93272	73	2719186
xz	220.65	181636	73	2586748

Benchmark 08

type	time	memory	cpu	size
txt	532.92	89192	79	394846146
gz	711.72	89944	77	94789125
bz2	673.47	96284	78	73823620
xz	1217.50	184724	79	64908128

Conclusions

From the benchmarks carried out with various starting data in exporting data directly to compressed files, it is clear that:

the xz format almost always produces the smallest files in the face of greater memory and cpu occupation
the gz and bz2 formats almost always have execution times comparable to saving on simple and uncompressed text files in the face of a strong reduction in the space occupied
the space gain in the generated compressed files compared to the uncompressed text file ranges from 55% to 98%
export execution times for compressed files are in the worst case (xz) about double the export in an uncompressed file and in the best case (gz) a tenth faster

The export of fixtures directly to compressed files therefore allows a strong reduction of the space occupied in the face of a small increase in the time and resources required for creation.

In addition there is the possibility for the user to choose the best file type for their use case, opting for maximum compression (xz) or for greater portability (gz).

External links

PR #12871: Added tests for loaddata with gzip/bzip2 compressed fixtures.
Ticket #31552: Loading lzma compressed fixtures.
PR #12879: Fixed #31552 -- Added support for LZMA and XZ fixtures to loaddata.
Ticket #32291: Add support for fixtures compression in dumpdata.
PR #13797: Fixed #32291 -- Added fixtures compression support to dumpdata.

License

This article and related presentation is released with Creative Commons Attribution ShareAlike license (CC BY-SA)

Original

Originally posted on my blog:

https://www.paulox.net/2021/04/06/django-32-news-on-compressed-fixtures-and-fixtures-compression/

Maps with Django (part 1): GeoDjango, SpatiaLite and Leaflet

Paolo Melchiorre — Mon, 07 Dec 2020 23:00:00 +0000

A quickstart guide to create a web map with the Python based web framework Django using its module GeoDjango , the SQLite database with its spatial extension SpaliaLite and Leaflet , a JavaScript library for interactive maps.

Introduction

In this guide we will see how to create a minimal web map using Django (the Python-based web framework), starting from its default project, writing a few lines of code and with the minimum addition of other software:

GeoDjango, the Django geographic module
SpatiaLite, the SQLite spatial extension
Leaflet, a JavaScript library for interactive maps.

Requirements

The only python package required is Django.
We’ll assume you have Django installed already.
This guide is tested with Django 3.1 and Python 3.8.

Creating a project

You need to create the basic project in your workspace directory with this command:

$ django-admin startproject mymap

That’ll create a directory mymap, which is laid out like this:

mymap/
├── manage.py
└── mymap
    ├── __init__.py
    ├── asgi.py
    ├── settings.py
    ├── urls.py
    └── wsgi.py

Creating the Markers app

To create your app, make sure you’re in the same directory as manage.py and type this command:

$ python manage.py startapp markers

That’ll create a directory markers, which is laid out like this:

markers/
├── __init__.py
├── admin.py
├── apps.py
├── migrations
│   └── __init__.py
├── models.py
├── tests.py
└── views.py

Activating the Markers app

Modify the INSTALLED_APPS setting

Append markers to the INSTALLED_APPS in mymap/settings.py

INSTALLED_APPS = [
    "django.contrib.admin",
    "django.contrib.auth",
    "django.contrib.contenttypes",
    "django.contrib.sessions",
    "django.contrib.messages",
    "django.contrib.staticfiles",
    "markers",
]

Adding an empty web map

We're going to add an empty web to the app:

Adding a template view

We have to add a TemplateView in views.py:

"""Markers view."""

from django.views.generic.base import TemplateView


class MarkersMapView(TemplateView):
    """Markers map view."""

    template_name = "map.html"

Adding a map template

We have to add a templates/ directory in markers/:

$ mkdir templates

And a map.html template in markers/templates/:

{% load static %}
<!doctype html>
<html lang="en">
<head>
  <title>Markers Map</title>
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <link rel="stylesheet" type="text/css" href="{% static 'map.css' %}">
  <link rel="stylesheet" type="text/css" href="https://unpkg.com/leaflet@1.7.1/dist/leaflet.css">
  <script src="https://unpkg.com/leaflet@1.7.1/dist/leaflet.js"></script>
</head>
<body>
  <div id="map"></div>
  <script src="{% static 'map.js' %}"></script>
</body>
</html>

Adding javascript and css files

We have to add a static/ directory in markers/:

$ mkdir static

Add a map.css stylesheet in markers/static/:

html, body {
  height: 100%;
  margin: 0;
}
#map {
  width: 100%;
  height: 100%;
}

Add a map.js stylesheet in markers/static/:

const attribution = '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
const map = L.map('map')
L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', { attribution: attribution }).addTo(map);
map.fitWorld();

Adding a new URL

Add an urls.py files in markers/:

"""Markers urls."""

from django.urls import path

from .views import MarkersMapView

app_name = "markers"

urlpatterns = [
    path("map/", MarkersMapView.as_view()),
]

Modify urls.py in mymap/ :

"""mymap URL Configuration."""

from django.contrib import admin
from django.urls import include, path

urlpatterns = [
    path("admin/", admin.site.urls),
    path("markers/", include("markers.urls")),
]

Testing the web map

Now you can test the empty web map running this command:

$ python manage.py runserver

Now that the server’s running, visit http://127.0.0.1:8000/markers/map/ with your Web browser. You’ll see a “Markers map” page, with a full page map. It worked!

An empty web map page.

Adding geographic features

Installing SpatiaLite

We need to install the SQLite spatial extension SpatiaLite:

on Debian-based GNU/Linux distributions (es: Debian, Ubuntu, ...):

$ apt install libsqlite3-mod-spatialite

on macOS using Homebrew:

$ brew install spatialite-tools

Changing the database engine

Modify the DATABASES default engine in mymap/settings.py

DATABASES = {
    "default": {
        "ENGINE": "django.contrib.gis.db.backends.spatialite",
        "NAME": BASE_DIR / "db.sqlite3",
    }
}

Activating GeoDjango

Modify the INSTALLED_APPS setting

Append GeoDjango to the INSTALLED_APPS in mymap/settings.py

INSTALLED_APPS = [
    "django.contrib.admin",
    "django.contrib.auth",
    "django.contrib.contenttypes",
    "django.contrib.sessions",
    "django.contrib.messages",
    "django.contrib.staticfiles",
    "django.contrib.gis",
    "markers",
]

Adding some markers

Now we can add some markers in the map.

Adding the Marker model

We're going to add a Marker model in markes/models.py:

"""Markers models."""

from django.contrib.gis.db.models import PointField
from django.db import models


class Marker(models.Model):
    """A marker with name and location."""

    name = models.CharField(max_length=255)
    location = PointField()

Now we have to create migrations for the new model:

$ python manage.py makemigrations

And then we'll apply this migration to the SQLite database:

$ python manage.py migrate

Activating the Marker admin

To insert new marker we have to add a Marker admin in markes/admin.py

"""Markers admin."""

from django.contrib.gis import admin

from .models import Marker


@admin.register(Marker)
class MarkerAdmin(admin.OSMGeoAdmin):
    """Marker admin."""

    list_display = ("name", "location")

Testing the admin

We have to create an admin user to login and test it:

$ python manage.py createsuperuser

Now you can test the admin running this command:

$ python manage.py runserver

Now that the server’s running, visit http://127.0.0.1:8000/admin/markers/marker/add/ with your Web browser. You’ll see a “Markers” admin page, to add a new markers with a map widget. I added a marker to the latest peak I climbed: "Monte Amaro 2793m 🇮🇹"

Adding a marker in the admin page.

Showing all markers in the web map

Adding all markers in the view

We can add with a serializer all markers as a GeoJSON in the context of the MarkersMapView in markes/views.py:

"""Markers view."""

import json

from django.core.serializers import serialize
from django.views.generic.base import TemplateView

from .models import Marker


class MarkersMapView(TemplateView):
    """Markers map view."""

    template_name = "map.html"

    def get_context_data(self, **kwargs):
        """Return the view context data."""
        context = super().get_context_data(**kwargs)
        context["markers"] = json.loads(serialize("geojson", Marker.objects.all()))
        return context

The value of the markes key in the context dictionary we'll something like that:

{
    "type": "FeatureCollection",
    "crs": {
        "type": "name",
        "properties": {
            "name": "EPSG:4326"
        }
    },
    "features": [
        {
            "type": "Feature",
            "properties": {
                "name": "Monte Amaro 2793m \ud83c\uddee\ud83c\uddf9",
                "pk": "1"
            },
            "geometry": {
                "type": "Point",
                "coordinates": [
                    14.08591836494682,
                    42.08632592463349
                ]
            }
        }
    ]
}

Inserting the GeoJSON in the template

Using json_script built-in filter we can safely outputs the Python dict with all markers as GeoJSON in markers/templates/map.html:

{% load static %}
<!doctype html>
<html lang="en">
<head>
  <title>Markers Map</title>
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <link rel="stylesheet" type="text/css" href="{% static 'map.css' %}">
  <link rel="stylesheet" type="text/css" href="https://unpkg.com/leaflet@1.7.1/dist/leaflet.css">
  <script src="https://unpkg.com/leaflet@1.7.1/dist/leaflet.js"></script>
</head>
<body>
  {{ markers|json_script:"markers-data" }}
  <div id="map"></div>
  <script src="{% static 'map.js' %}"></script>
</body>
</html>

Rendering all markers in the map

We can render the GeoJSON with all markers in the web map using Leaflet in markers/static/map.js:

const attribution = '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
const map = L.map('map')
L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', { attribution: attribution }).addTo(map);
const markers = JSON.parse(document.getElementById('markers-data').textContent);
let feature = L.geoJSON(markers).bindPopup(function (layer) { return layer.feature.properties.name; }).addTo(map);
map.fitBounds(feature.getBounds(), { padding: [100, 100] });

Testing the populated map

I populated the map with other markers of the highest or lowest points I've visited in the world to show them on my map.

Now you can test the populated web map running this command:

$ python manage.py runserver

Now that the server’s running, visit http://127.0.0.1:8000/markers/map/ with your Web browser. You’ll see the “Markers map” page, with a full page map and all the markers. It worked!

A map with some markers of the highest or lowest points I've visited in the world with the opened popup of the latest peak I climbed.

Curiosity

If you want to know more about my latest hike to the Monte Amaro peak you can see it on my Wikiloc account: Round trip hike from Rifugio Pomilio to Monte Amaro 🔗.

Conclusion

We have shown an example of a fully functional map, trying to use the least amount of software, without using external services.

This map is enough to show a few points in a simple project using SQLite and Django templates.

In future articles we will see how to make this map even more advanced using Django Rest Framework, PostGIS, etc ... to render very large numbers of markers in an even more dynamic way.

Stay tuned.

-- Paolo

Resources

GeoDjango - Dajngo Documentation 🔗

Leaflet API reference 🔗

SpatiaLite - SQL functions reference list 🔗

Safely Including Data for JavaScript in a Django Template 🔗

License

This article and related presentation is released with Creative Commons Attribution ShareAlike license (CC BY-SA)

Original

Originally posted on my blog:

https://www.paulox.net/2020/12/08/maps-with-django-part-1-geodjango-spatialite-and-leaflet/

Speaking online at EuroPython 2020

Paolo Melchiorre — Thu, 23 Jul 2020 22:00:00 +0000

An article about my first participation as a speaker at an online conference: EuroPython 2020.

Yesterday I gave a talk at the EuroPython 2020 Online Edition with title "A pythonic full-text search" which is now available here on my blog.

In the last few years I gave a lot of talks in various tech conferences in Europe, but it was my first talk in an online conference, so I decided to share some thoughts about this experience.

The EuroPython team did a great job organizing everything. They made a list of tips and a complete guide for speakers that I suggest, to organizers and speakers of other conferences, to read.

ep2020.europython.eu/events/tips

Besides reading the guide and attending the rehearsal session, I've spent a lot of time trying to have a good setup. I copied from other conference speakers, and to have a working virtual background in zoom, I used my two external monitors as light panels.

Attending an online conference is a new experience , but giving a talk remotely is way more strange than in a regular conference. There's nobody around to speak with, smiles at or asking to take a picture, so I shoot by myself some pictures of me during the talk.

At the same time, I was able to capture some screenshots of myself giving the talk from the live Youtube channel, so I have a picture of me from the other side of the screen in the exact moment of the above picture.

In the speakers guide there was this suggestion: "Throw in some jokes. Even bad jokes. Even a really bad pun.". I tried to make a joke about synchronization problems in full-text search, a very dangerous thing to do, but here I'm laughing at my own joke during the talk.

Another suggestion from the organizers was to use "Pictures instead of bullet points in slides. Pictures are great. Everyone loves cats." so I used:

an elephant (the logo of PostgreSQL)

a famous guitaris (Django Reinhardt from whom the Django web framework takes its name)

some books

a picture of me at EuroPython 2017 during the sprint on Django

In an online conference people are more interactive and free to do other things watching your talk, so I published in advance a post on my blog with the slides and other information about the talk, and I shared a QR-code linked to it and also all my other contacts.

Giving online talks is a stressful thing, but answering questions is way more difficult, you are not able to see who is asking the question and its expression. In these pictures I was at the end of the talk after a few questions, waiting for an applause.

This was my first time giving a talk in an online conference, it was a very challenging experience but I enjoyed it. I learnt a lot of new stuff, also how to take a picture of myself after the talk, to share it online.

I'll gave other talks this year, I listed them on my talks page:

www.paulox.net/talks

See you at the next conference!

This article is based on a thread posted on my Twitter account @pauloxnet.

Original

Originally posted on my blog:

www.paulox.net/2020/07/24/speaking-online-at-europython-2020/

Upgrading PostgreSQL from version 11 to 12 on Ubuntu 20.04 (Focal Fossa)

Paolo Melchiorre — Fri, 24 Apr 2020 10:10:29 +0000

Howto guide for upgrading PostgreSQL from version 11 to 12 on Ubuntu, after its upgrade from version 19.10 to 20.04.

Goal

This article is aimed at those like me who use Ubuntu and PostgreSQL to develop locally on their computer and after the last update to Ubuntu 20.04 they have two versions of PostgreSQL installed.

TL;DR

After upgrade to Ubuntu 20.04:

$ sudo pg_dropcluster 12 main --stop
$ sudo pg_upgradecluster 11 main
$ sudo pg_dropcluster 11 main

Upgrade PostgreSQL

During Ubuntu updgrade to 20.04 you receive this message "Configuring postgresql-common":

Obsolete major version 11

The PostgreSQL version 11 is obsolete, but the server or client packages are still installed.
Please install the latest packages (postgresql-12 and postgresql-client-12) and upgrade the existing clusters with pg_upgradecluster (see manpage).

Please be aware that the installation of postgresql-12 will automatically create a default cluster 12/main.
If you want to upgrade the 11/main cluster, you need to remove the already existing 12 cluster (pg_dropcluster --stop 12 main, see manpage for details).

The old server and client packages are no longer supported.
After the existing clusters are upgraded, the postgresql-11 and postgresql-client-11 packages should be removed.

Please see /usr/share/doc/postgresql-common/README.Debian.gz for details.

Use dpkg -l | grep postgresql to check which versions of postgres are installed:

ii  postgresql                12+214                 all    object-relational SQL database (supported version)
ii  postgresql-11             11.7-0ubuntu0.19.10.1  amd64  object-relational SQL database, version 11 server
ii  postgresql-12             12.2-4                 amd64  object-relational SQL database, version 12 server
ii  postgresql-client         12+214                 all    front-end programs for PostgreSQL (supported version)
ii  postgresql-client-11      11.7-0ubuntu0.19.10.1  amd64  front-end programs for PostgreSQL 11
ii  postgresql-client-12      12.2-4                 amd64  front-end programs for PostgreSQL 12
ii  postgresql-client-common  214                    all    manager for multiple PostgreSQL client versions
ii  postgresql-common         214                    all    PostgreSQL database-cluster manager

Run pg_lsclusters, your 11 and 12 main clusters should be "online".

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12  main    5433 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

There already is a cluster "main" for 12 (since this is created by default on package installation). This is done so that a fresh installation works out of the box without the need to create a cluster first, but of course it clashes when you try to upgrade 11/main when 12/main also exists. The recommended procedure is to remove the 12 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 12 cluster and drop it.

$ sudo pg_dropcluster 12 main --stop

Upgrade the 11 cluster to the latest version.

$ sudo pg_upgradecluster 11 main

Your 11 cluster should now be "down" and you can verifity running pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5433 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12  main    5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

Check that the upgraded cluster works, then remove the 11 cluster.

$ sudo pg_dropcluster 11 main

After all your data check you can remove your old packages.

$ sudo apt-get purge postgresql-11 postgresql-client-11

Disclaimer of Warranty.

THERE IS NO WARRANTY FOR THE PROGRAM, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES PROVIDE THE PROGRAM "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.

Reference

Originially published on www.paulox.net

Upgrading PostgreSQL from version 10 to 11 on Ubuntu 19.04 (Disco Dingo)

Paolo Melchiorre — Mon, 27 May 2019 22:00:00 +0000

Howto guide for upgrading PostgreSQL from version 10 to 11 on Ubuntu, after its upgrade from version 18.10 to 19.04.

TL;DR

After upgrade to Ubuntu 19.04:

$ sudo pg_dropcluster 11 main --stop
$ sudo pg_upgradecluster 10 main
$ sudo pg_dropcluster 10 main

Upgrade PostgreSQL

During Ubuntu updgrade to 19.04 you receive this message "Configuring postgresql-common":

Obsolete major version 10

The PostgreSQL version 10 is obsolete, but the server or client packages are still installed.
Please install the latest packages (postgresql-11 and postgresql-client-11) and upgrade the existing clusters with pg_upgradecluster (see manpage).

Please be aware that the installation of postgresql-11 will automatically create a default cluster 11/main.
If you want to upgrade the 10/main cluster, you need to remove the already existing 11 cluster (pg_dropcluster --stop 11 main, see manpage for details).

The old server and client packages are no longer supported.
After the existing clusters are upgraded, the postgresql-10 and postgresql-client-10 packages should be removed.

Please see /usr/share/doc/postgresql-common/README.Debian.gz for details.

Use dpkg -l | grep postgresql to check which versions of postgres are installed:

ii  postgresql                                 11+199                               all          object-relational SQL database (supported version)
ii  postgresql-10                              10.8-0ubuntu0.18.10.1                amd64        object-relational SQL database, version 10 server
ii  postgresql-11                              11.3-0ubuntu0.19.04.1                amd64        object-relational SQL database, version 11 server
ii  postgresql-client                          11+199                               all          front-end programs for PostgreSQL (supported version)
ii  postgresql-client-10                       10.8-0ubuntu0.18.10.1                amd64        front-end programs for PostgreSQL 10
ii  postgresql-client-11                       11.3-0ubuntu0.19.04.1                amd64        front-end programs for PostgreSQL 11
ii  postgresql-client-common                   199                                  all          manager for multiple PostgreSQL client versions
ii  postgresql-common                          199                                  all          PostgreSQL database-cluster manager

Run pg_lsclusters, your 10 and 11 main clusters should be "online".

Ver Cluster Port Status Owner    Data directory               Log file
10  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

There already is a cluster "main" for 11 (since this is created by default on package installation). This is done so that a fresh installation works out of the box without the need to create a cluster first, but of course it clashes when you try to upgrade 10/main when 11/main also exists. The recommended procedure is to remove the 11 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 11 cluster and drop it.

$ sudo pg_dropcluster 11 main --stop

Upgrade the 10 cluster to the latest version.

$ sudo pg_upgradecluster 10 main

Your 10 cluster should now be "down" and you can verifity running pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5433 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

Check that the upgraded cluster works, then remove the 10 cluster.

$ sudo pg_dropcluster 10 main

After all your data check you can remove your old packages.

$ sudo apt purge \
postgresql-10 postgresql-client-10 postgresql-contrib-10

Disclaimer of Warranty.

Reference

Originially published on www.paulox.net

My involvement in the Python community

Paolo Melchiorre — Thu, 14 Mar 2019 21:10:10 +0000

An article about me and my involvement in the Python community (mainly Django) as a Free Software developer, GNU/Linux user and tech conference speaker.

About me

I graduated with a degree in Computer Science from the University of Bologna. My thesis was about Free Software and since then I've been a Free Software advocate.

I've been a GNU/Linux user since 2000 and now I'm a happy user of Ubuntu.

In 2007 I attended my first conference, the Plone Conference, and since then I've attended many other pythonic conferences in Europe.

In 2017 I presented a talk at PyCon Italy and at EuroPython and since then I have been a conference speaker for local and international events, both in Italian and in English.

I've lived and worked in Rome and London, and since 2015 I've been a remote worker located in my hometown of Pescara, Italy.

I love nature and spending my time swimming, snowboarding or hiking, but also traveling with my wife around the world.

I like improving my English skills by reading fiction books or listening audiobooks, watching TV series and movies, listening podcasts and attending local English speaking meetups.

I answer questions at stack overflow, tweet at @pauloxnet and occasionally post at paulox.net.

My journey through programming languages

I started programming with Pascal during high school and then I learned HTML and CSS on my own to develop my first website as my final project for high school.

At university I studied various languages such as C, C++, C#, Java, SQL and Javascript and I used some of them at work in the past.

Since 2006 I've predominantly used Python, and it's without a doubt the language I prefer, although sometimes I still use SQL, Javascript and of course HTML and CSS.

How I started using Python

I started using Python in my first job because we developed websites with Plone and Zope.

I realized how much better Python was for me than other languages I've studied and used before because it's easier to learn. It focuses on code simplicity and readability, it's extensible and fast to write and has a fantastic community.

When I stopped using Plone I continued using Python as my main programming language.

My favorite Python libraries

I work every day with Django and PostgreSQL, so apart from the Django framework itself I think my favorite python library is the Python-PostgreSQL database adapter psycopg2 because it's pretty solid and allows me to work with the database without the Django ORM when I need to do very low level operations or when I want to use all the great features of PostgreSQL.

The choice of Django as web framework

Originally I started working with Plone and the Zope application framework which stores all information in Zope's built-in transactional object database (ZODB).

Then I started using Django when I needed to store data in a relational database like PostgreSQL, and after some research, I realized it was the best choice.

I love its architecture, the ORM, the admin module, the PostgreSQL support, all its ready-to-use modules like GeoDjango, all the 3rd party packages, and particularly the community behind it.

How I got started contributing to Django

I started contributing to the core of Django during the sprint day at DjangoCon Europe 2017 with a pull request that integrated the PostgreSQL crypto extension in its contrib package and then was merged in Django 2.0.

I presented a talk about the Django Full-text search feature at the PyCon Italy 2017 conference and then wrote the article "Full-text search with Django and PostgreSQL" based on this. Later I realized that the Django Full-text search function was not used on the djangoproject.com site.

At EuroPython 2017 I organized a sprint about the search module of the djangoproject.com.

I completed a pull request that replaced Elasticsearch with the PostgreSQL Full-text search function on the official Django website and I continued updating this function with improvements in speed and multilingual support.

I presented a talk about this experience as an example of my contribution to the Django project.

The projects I'm working on

I contribute to the Django project, its website and some related packages.

I'm attending some Django Girls workshops as a coach and I've contributed to its tutorial.

In addition, I'm updating my Django Queries project with code I've used in my talks which lets people try it on their own.

I'm working on a Django project template we use at work to speed up the bootstrap of a project deployed on uWSGI.

I'm updating my Pelican-based technical blog where I post some articles, information about me, my projects and my talks.

I'm updating my YouTube channel with all my recorded talks and my Speaker Deck account with all my talk slides.

I'm also answering as many python-related questions as I can on Stack Overflow, particularly related to Django, Full-text search and PostgreSQL and I wrote the article "Updating a Django queryset with annotation and subquery" based on one of them.

Lessons learned as a Free Software developer

I think Free Software is the one of the best inventions in the last century, and being part of it is very rewarding.

In particular being a Free Software developer has taught me:

Sharing knowledge (in form of ideas, code, documentation, skills) is the best way to better yourself as a person and a developer
The best part about Free Software is its community of human beings
Some things not code-related are very important for improving Free Software and its community, such as choosing a good license, adding contributing guidelines and not forgetting about documentation

Being a tech conference speaker

Being a conference speaker at Free Software related conferences has given me the opportunity to meet a lot of people and become a better person.

I encourage everyone to join meetups, get out in the community and attend conferences and, of course, if we meet at some conference, please say hello.

A thought for all native English-speaking developers

I also want to say to all native English-speaking developers that there a lot of excellent developers who hesitate to contribute to Free Software because of their lack of English knowledge. Personally, I waited a long time before contributing to projects and actively participating in the community and then I forced myself to improve my English skills with a lot of costs in term of time, effort and money.

So I would just like to remind people to be patient and inclusive when it comes to non-native English speakers as we need a bit more time and effort to open an issue, send a pull request, ask questions online and at conferences or simply speak and write about ourselves and our ideas in an interview like this.

This article is based on the answers I sent to Mike Driscoll for an email interview for his blog, The Mouse Vs. The Python, ️published on 11th February 2019, in the article "PyDev of the Week: Paolo Melchiorre".

Original

Originally posted on my blog:

paulox.net/2018/10/01/updating-a-django-queryset-with-annotation-and-subquery/

Upgrading PostgreSQL from 9.6 to 10.0 on Ubuntu 18.04

Paolo Melchiorre — Tue, 12 Mar 2019 08:04:34 +0000

How-To guide to upgrade PostgreSQL from 9.6 to 10.0 on Ubuntu after upgrade it to 18.04 version.

TL;DR

After upgrade to Ubuntu 18.04:

$ sudo pg_dropcluster 10.0 main --stop
$ sudo pg_upgradecluster 9.6 main
$ sudo pg_dropcluster 9.6 main

Upgrade PostgreSQL

During Ubuntu updgrade to 18.04 you receive this message "Configuring postgresql-common":

Obsolete major version 9.6

The PostgreSQL version 9.6 is obsolete, but the server or client packages are still installed.
Please install the latest packages (postgresql-10 and postgresql-client-10) and upgrade the existing clusters with pg_upgradecluster (see manpage).

Please be aware that the installation of postgresql-10 will automatically create a default cluster 10.0/main.
If you want to upgrade the 9.6/main cluster, you need to remove the already existing 10 cluster (pg_dropcluster --stop 10 main, see manpage for details).

The old server and client packages are no longer supported.
After the existing clusters are upgraded, the postgresql-9.6 and postgresql-client-9.6 packages should be removed.

Please see /usr/share/doc/postgresql-common/README.Debian.gz for details.

Use dpkg -l | grep postgresql to check which versions of postgres are installed:

ii  postgresql                                 10+190                                      all          object-relational SQL database (supported version)
ii  postgresql-10                              10.3-1                                      amd64        object-relational SQL database, version 10 server
ii  postgresql-10-postgis-2.4                  2.4.3+dfsg-4                                amd64        Geographic objects support for PostgreSQL 10
ii  postgresql-10-postgis-2.4-scripts          2.4.3+dfsg-4                                all          Geographic objects support for PostgreSQL 10 -- SQL scripts
ii  postgresql-9.6                             9.6.8-0ubuntu0.17.10                        amd64        object-relational SQL database, version 9.6 server
ii  postgresql-9.6-postgis-2.3                 2.3.3+dfsg-1                                amd64        Geographic objects support for PostgreSQL 9.6
ii  postgresql-client                          10+190                                      all          front-end programs for PostgreSQL (supported version)
ii  postgresql-client-10                       10.3-1                                      amd64        front-end programs for PostgreSQL 10
ii  postgresql-client-9.6                      9.6.8-0ubuntu0.17.10                        amd64        front-end programs for PostgreSQL 9.6
ii  postgresql-client-common                   190                                         all          manager for multiple PostgreSQL client versions
ii  postgresql-common                          190                                         all          PostgreSQL database-cluster manager
ii  postgresql-contrib                         10+190                                      all          additional facilities for PostgreSQL (supported version)
ii  postgresql-contrib-9.6                     9.6.8-0ubuntu0.17.10                        amd64        additional facilities for PostgreSQL
ii  postgresql-plpython3-9.6                   9.6.8-0ubuntu0.17.10                        amd64        PL/Python 3 procedural language for PostgreSQL 9.6

Run pg_lsclusters, your 9.6 and 10 main clusters should be "online".

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 down   postgres /var/lib/postgresql/9.6/main pg_log/postgresql-%Y-%m-%d.log
10  main    5433 down   postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

There already is a cluster "main" for 10 (since this is created by default on package installation). This is done so that a fresh installation works out of the box without the need to create a cluster first, but of course it clashes when you try to upgrade 9.6/main when 10/main also exists. The recommended procedure is to remove the 10 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 10 cluster and drop it.

$ sudo pg_dropcluster 10 main --stop

Upgrade the 9.6 cluster to the latest version.

$ sudo pg_upgradecluster 9.6 main

Your 9.6 cluster should now be "down" and you can verifity running pg_lsclusters

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5433 down   postgres /var/lib/postgresql/9.6/main ...
10  main    5432 online postgres /var/lib/postgresql/10/main  ...

Check that the upgraded cluster works, then remove the 9.6 cluster.

$ sudo pg_dropcluster 9.6 main

After all your data check you can remove your old packages.

$ sudo apt purge postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6

Disclaimer of Warranty.

Reference

Originially published on www.paulox.net

DEV Community: Paolo Melchiorre

Database generated columns (3): GeoDjango & PostGIS

⚡ TL;DR

🚀 Introduction

🤖 Generated Field

🕶️ Attributes

db_persist

output_field

expression

↔️ Migrations

💻 Set Up

🐍 Python

⚗️ Virtual environment

🦄 Django

🐘 Psycopg

⚠️ Warning

🌎 GDAL

⚠️ Warning

🗃️ The generatedfields project

🎛️ Activating PostGIS

⚠️ Warning

🧩 Activating GeoDjango

🗂️ The samples app

✅ Activating the samples app

📚 Sample models

🔧 Useful commands

makemigrations

sqlmigrate

migrate

shell

📇 A calculated GeoHash field

🔔 Note

📏 A calculated length field

🔔 Note

📐 A calculated area field

🔔 Note

🗺️ Maps

🙋 Get involved

🥠 Conclusion

⚠️ Disclaimer

🔗 Resources

Database generated columns (2): Django & PostgreSQL

⚡ TL;DR

🚀 Introduction

🤖 Generated Field

🕶️ Attributes

db_persist

output_field

expression

↔️ Migrations

💻 Set Up

🐍 Python

⚗️ Virtual environment

🦄 Django

🐘 Psycopg

⚠️ Warning

🗃️ The generatedfields project

🎛️ Activating PostgreSQL

⚠️ Warning

🗂️ The samples app

✅ Activating the samples app

📚 Sample models

🔧 Useful commands

makemigrations

sqlmigrate

migrate

shell

♻️ Common generated fields

🔑 A calculated JSON key field

🔔 Note

🖇️ A calculated concatenated field

🔔 Note

🧩 Activating PostgreSQL module

🔍 A calculated search vector field

🔔 Note

🧮 A calculated array length field

🔔 Note

⏳ A calculated date range field

🔔 Note

🙋 Get involved

`db_persist`

`output_field`

`expression`

🗃️ The `generatedfields` project

🗂️ The `samples` app

✅ Activating the `samples` app

`makemigrations`

`sqlmigrate`

`migrate`

`shell`

`db_persist`

`output_field`

`expression`

🗃️ The `generatedfields` project

🗂️ The `samples` app

✅ Activating the `samples` app

`makemigrations`

`sqlmigrate`

`migrate`

`shell`

`db_persist`

`output_field`

`expression`

🗃️ The `generatedfields` project

🗂️ The `samples` app

✅ Activating the `samples` app

`makemigrations`

`sqlmigrate`

`migrate`

`shell`