9. Layered Architecture and Data Access

The previous examples kept their data in memory. A real application stores it in a database and is organized into layers (see the diagram in the introduction):

• the web layer (views, forms, templates): read the request, call the business layer, select the template;
• the business layer: business rules;
• the DAO layer (Data Access Object): database queries.

Each layer is aware only of the layer below it, and as few details about it as possible: the web layer is unaware of the queries, and the business layer is unaware of the HTML. This separation makes each layer easier to understand, test, and evolve. Example 28 applies this to managing a list of people: it is, on a smaller scale, the management of doctors and clients from the case study.

In [Python], database access is organized across several layers:

• the DB-API 2.0 standard (PEP 249) of [Python] defines a common interface for all SGBD: open a connection, obtain a cursor, execute a SQL command, and read rows. Each SGBD has its own driver: for [MySQL] (and [MariaDB]), [Django] recommends [mysqlclient], a library written in C, installed by the [requirements.txt] example file;
• ORM (Object-Relational Mapper) from [Django] maps classes (models) to database tables and converts the read rows into objects. It manages connections and transactions, generates the SQL commands itself—tailored to each SGBD—and translates errors from each driver into common exceptions (a uniqueness constraint violation becomes a [IntegrityError], regardless of the SGBD). It follows the so-called Active Record pattern: each object knows how to save itself ([person.save()]) and delete itself ([person.delete()]), and each class has a handler ([Person.objects]) from which requests are sent. This is the model for [Eloquent], the ORM of [Laravel]; [Doctrine], the ORM derived from [Symfony], on the other hand, followed the Data Mapper pattern, where a separate object reads and saves the entities.

ORM, derived from [Django], is part of [Django] and is one of its key components: the administration interface, the authentication application, database sessions, and generic views all rely on it. It is the equivalent of [JPA] and [Hibernate] in [Java], [Entity Framework Core] to [C#], or [SQLAlchemy] (used with [Flask]) to [Python]. Note a consequence of how [Django] works: by default, each request opens its own connection to the database (upon its first SQL request) and closes it upon termination (upon the [request_finished] signal, example 24). The [CONN_MAX_AGE] setting allows connections to remain open from one request to the next (persistent connections), and [Django] 5.1 added a true connection pool for [PostgreSQL]. Opening a connection to [MySQL] on the same machine takes only a few milliseconds.

This example requires a running [MySQL] 8.4 or later server (or [MariaDB] 10.11 or later; the minimum versions are [Django] 6.1): The appendix explains how to install one using [Laragon].

9.1. Example 28: A Three-Tiered Personnel Management System

28-layers/
├── config/
│   ├── middleware.py
│   ├── rendering.py
│   ├── settings.py
│   ├── urls.py
│   └── ...
├── database/
│   └── dbpersons.sql
├── persons/
│   ├── __init__.py
│   ├── business.py
│   ├── dao.py
│   ├── forms.py
│   ├── models.py
│   ├── urls.py
│   └── views.py
├── static/
│   ├── css/
│   │   └── styles.css
│   └── js/
│       └── app.js
├── templates/
│   ├── forms/
│   │   └── field.html
│   ├── fragments/
│   │   └── confirmation.html
│   ├── persons/
│   │   ├── form.html
│   │   └── list.html
│   └── layout.html
└── manage.py

9.1.1. The database

The [dbpersons] database is created by the SQL script. Using [Laragon], you can run it, for example, in the [HeidiSQL] tool (File menu / Run a SQL file), or from the command line, from the example folder:

mysql -u root < database/dbpersons.sql

The [database/dbpersons.sql] file:

-- database of example 28: a single table, [persons]
-- the script is written in UTF-8
SET NAMES utf8mb4;
DROP DATABASE IF EXISTS dbpersons;
CREATE DATABASE dbpersons CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE dbpersons;

CREATE TABLE persons (
  id INT NOT NULL AUTO_INCREMENT,
  -- the version of the row, increased at each modification (see DAO layer)
  version INT NOT NULL DEFAULT 0,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY (id),
  -- two persons cannot have the same first and last names
  CONSTRAINT unq_persons_first_name_last_name UNIQUE (first_name, last_name)
) ENGINE = InnoDB;

INSERT INTO persons (first_name, last_name, age) VALUES
  ('Marie', 'Pélissier', 42),
  ('Jacques', 'Bromard', 35),
  ('Philippe', 'Jandot', 58);

• Line 3: The script is written in [UTF-8]; this is specified to the server so that the accents in the data ([Pélissier]) remain intact;
• line 5: the character set [utf8mb4] accepts all characters (accents, emojis, etc.). The [utf8mb4_unicode_ci] collation compares strings without distinguishing between uppercase and lowercase letters or accents: for [MySQL], [pelissier], and [Pélissier] are equal;
• line 11: a version number, incremented each time the line is modified (see “optimistic lock,” below);
• line 17: a named uniqueness constraint: the database does not allow two people with the same first and last names.

[Django] can also create the tables itself, based on the schemas, using migrations: [Python] files that describe, step by step, the creation and modifications of each table. The [python manage.py makemigrations] command writes these files by comparing the schemas to the state described by the previous migrations; [python manage.py migrate] applies them to the database. This is the standard way of working with [Django]. We maintain a script, SQL, which is common to all versions of the case study and shows exactly what the database contains. Conversely, the command [python manage.py inspectdb] writes the schemas for an existing database: a good starting point, worth reviewing.

9.1.2. The configuration: config/settings.py

# config/settings.py: the configuration of example 28

from pathlib import Path

BASE_DIR = Path(__file__).resolve().parent.parent
DEBUG = True
SECRET_KEY = "django-insecure--7xp)7k(l6nl(owyoe0r99a@hufm3z2t@a(@w+va%5uw^%fbbm"
ALLOWED_HOSTS = []
LANGUAGE_CODE = "en"
TIME_ZONE = "Europe/Paris"
ROOT_URLCONF = "config.urls"
WSGI_APPLICATION = "config.wsgi.application"

INSTALLED_APPS = [
    "django.contrib.staticfiles",
    "django.contrib.messages",
    "django.forms",
    "persons",
]

MIDDLEWARE = [
    "django.middleware.csrf.CsrfViewMiddleware",
    "django.contrib.messages.middleware.MessageMiddleware",
    # PUT and DELETE through the hidden _method field (see example 21)
    "config.middleware.MethodOverride",
]

MESSAGE_STORAGE = "django.contrib.messages.storage.cookie.CookieStorage"

# the DATABASE: the MySQL DBMS, on this machine (127.0.0.1), port 3306, the
# dbpersons database, the root user without password (Laragon's settings)
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.mysql",
        "NAME": "dbpersons",
        "USER": "root",
        "PASSWORD": "",
        "HOST": "127.0.0.1",
        "PORT": "3306",
        "OPTIONS": {"charset": "utf8mb4"},
    },
}

TEMPLATES = [
    {
        "BACKEND": "django.template.backends.django.DjangoTemplates",
        "DIRS": [BASE_DIR / "templates"],
        "APP_DIRS": True,
        "OPTIONS": {
            "context_processors": [
                "django.contrib.messages.context_processors.messages",
            ],
        },
    },
]

FORM_RENDERER = "config.rendering.BootstrapRenderer"

STATIC_URL = "static/"
STATICFILES_DIRS = [
    BASE_DIR / "static",
    BASE_DIR.parent / "common" / "static",
]

# the LOG (logging): the SQL statements sent to the database are written in the console
# (the django.db.backends logger emits them only in debug mode)
LOGGING = {
    "version": 1,
    "disable_existing_loggers": False,
    "handlers": {"console": {"class": "logging.StreamHandler"}},
    "loggers": {
        "django.db.backends": {"handlers": ["console"], "level": "DEBUG"},
    },
}

• lines 21–26: the middleware from Example 21: anti-CSRF protection, flash messages, and the simulation of the methods PUT and DELETE (the file [config/middleware.py] is the one from Example 21);
• lines 32–42: the [DATABASES] configuration describes the application’s databases: in this case, a single one, the [default] database. [ENGINE] refers to the engine of [Django] for [MySQL] (which uses the driver [mysqlclient]); followed by the database name, the username, and the password (left blank with the [Laragon] settings: if your server has a password, enter it here), the hostname, and the port. [OPTIONS] passes the character set for communication with the server—the database’s character set—to the driver;
• lines 44–55: the templates, with [APP_DIRS] for the form templates (example 18) and the message context processor (example 20);
• lines 67–74: the setting [LOGGING] configures the log for [Python] (the [logging] module), which [Django] uses for its messages. It follows the format of the [logging.config] module: destinations ([handlers]: in this case, the console, via a [StreamHandler], which writes to the error output), and logs ([loggers]), identified by their names. The log [django.db.backends] receives, in debug mode only, every SQL command sent to the database, along with its duration: we send this to the console at level [DEBUG] (the most detailed). [disable_existing_loggers: False] stores the logs that [Django] configures itself.

There is nothing else to configure: the modules in the three layers import each other (the web layer imports the business layer, which imports the DAO layer), and the ORM layer establishes the connection to the database itself when it needs to.

9.1.3. The Person model: persons/models.py

# persons/models.py: a row of the [persons] table, a Django MODEL

from django.db import models


# a Django model: a class that inherits from models.Model; each class attribute
# that is a FIELD (models.XxxField) corresponds to a column of the table
class Person(models.Model):
    # the primary key, generated by the database (AUTO_INCREMENT)
    id = models.AutoField(primary_key=True)
    # the VERSION of the row, increased at each modification (see the DAO layer)
    version = models.IntegerField(default=0)
    # the VARCHAR(30) and INT columns
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)
    age = models.IntegerField()

    # Meta: the information about the model itself
    class Meta:
        # the name of the table (by default: persons_person, application_model)
        db_table = "persons"
        # the table already exists (script database/dbpersons.sql): Django does not create it
        managed = False
        # the order of the rows when the query does not specify one
        ordering = ["last_name", "first_name"]

    # the text that represents the object (in the console, the messages...)
    def __str__(self):
        return f"{self.first_name} {self.last_name}"

The file is named [models.py]: this is where [Django] looks for the models of an application declared in [INSTALLED_APPS].

• Line 8: A template is a class that inherits from [models.Model];
• Line 10: The primary key, generated by the database ([AutoField]: the [AUTO_INCREMENT] of [MySQL]). [Django] would add it automatically if it were not declared (a field [id], of the type chosen by the setting [DEFAULT_AUTO_FIELD]); we include it here for clarity. It is equal to [None] until the object has been inserted;
• line 12: the row version. The ORM from [Django] does not have a built-in optimistic lock: it is written by the DAO layer (see below);
• lines 14–16: the columns. Each class attribute that is a field in the model ([models.XxxField]) corresponds to the column of the same name; the column type is inferred from the field type ([CharField]: [VARCHAR], of length [max_length]; [IntegerField]: [INT]). As in a form (example 08), the fields are declared as attributes of the class, but they are not the same fields: those in [django.db.models] describe columns, while those in [django.forms] describe form fields;
• lines 19–25: the inner class [Meta] provides information about the template itself. [db_table]: the table name (otherwise, [Django] would take the application name followed by the model name, [persons_person]). [managed = False]: The table already exists, and [Django] does not manage it: [makemigrations] will not generate any creation or modification migrations for it. [ordering]: the order of rows when a query does not specify one;
• lines 28–29: the method [__str__] (Example 12) returns the text representing the object: [str(p)], or [{p}] in an f-string, returns [Marie Pélissier].

A model is an ordinary class to which methods and properties can be added. The ORM, on the other hand, populates the attributes based on the lines read and generates the commands SQL from the attributes.

9.1.4. The DAO layer: persons/dao.py

# persons/dao.py: the DAO layer, queries, no business rule

from django.db.models import F

from .models import Person


# SELECT ... FROM persons ORDER BY last_name, first_name. objects: the MANAGER of the model,
# where all the queries start from; all() returns a QuerySet, a query that is only
# executed when its results are read (here, by list)
def list_all() -> list[Person]:
    return list(Person.objects.all())


# SELECT ... FROM persons WHERE id = ?; None if the person does not exist
def read(id: int) -> Person | None:
    return Person.objects.filter(id=id).first()


# a person with the same first and last names, other than [except_id]. filter: the WHERE
# conditions; exclude: the WHERE NOT conditions
def namesake(first_name: str, last_name: str, except_id: int = 0) -> Person | None:
    return Person.objects.filter(first_name=first_name, last_name=last_name).exclude(id=except_id).first()


# INSERT INTO persons ...: create builds the object and saves it
def insert(first_name: str, last_name: str, age: int) -> Person:
    return Person.objects.create(first_name=first_name, last_name=last_name, age=age)


# UPDATE persons SET ..., version = version + 1 WHERE id = ? AND version = ?: the
# row is modified only if its version has not changed since it was read (OPTIMISTIC
# LOCK). F("version") designates the column itself, in the SQL statement. update
# returns the number of modified rows: 0 if the version has changed
def update(id: int, version: int, first_name: str, last_name: str, age: int) -> bool:
    rows = Person.objects.filter(id=id, version=version).update(
        first_name=first_name, last_name=last_name, age=age, version=F("version") + 1)
    return rows == 1


# DELETE FROM persons WHERE id = ?
def delete(person: Person) -> None:
    person.delete()

The DAO layer contains only queries, with no business rules. Each function begins with [Person.objects], the model manager, from which all queries originate.

• Lines 11–12: [all()] returns a [QuerySet]: an object that describes a query without executing it. The query is sent to the database only when its results are needed (here, when [list] iterates through them): a [QuerySet] is said to be lazy. It can thus be populated before execution ([filter], [exclude], [order_by]...), with each method returning a new [QuerySet]. The order is that of [Meta.ordering];
• Lines 16–17: [filter(id=id)] adds the condition [WHERE id = ...]; [first()] executes the query, limited to one row ([LIMIT 1]), and returns the first object, or [None]. ([Person.objects.get(id=id)] would return the object, but would throw the exception [Person.DoesNotExist] if it did not exist, and the shortcut [get_object_or_404], example 03, would return a 404 error);
• Lines 22–23: Several conditions in [filter] are combined by [AND]; [exclude] adds a condition [NOT]. The named parameters refer to the fields ([first_name=...]); suffixes specify the comparison: [age__gte=18] ([>=]), [last_name__startswith="P"], [last_name__iexact=...] (case-insensitive)... The values are transmitted to the database separately and are never copied into the text of the order SQL, which makes injection impossible SQL;
• lines 27–28: [create] constructs the object and saves it ([INSERT]); after insertion, ORM stores the ID generated by the database in the object. This is equivalent to [p = Person(first_name=..., ...)] followed by [p.save()];
• lines 35–38: the update, with its optimistic lock. [update] updates all rows specified by [QuerySet] in a single operation using a single [UPDATE] command, and returns the number of updated rows. The filter has two conditions: the ID and the read version. If another query has modified the row in the meantime—causing its version to change—[UPDATE] does not modify any rows, and the function returns [False]. This is the principle of optimistic locking: nothing is locked, but concurrent modifications are detected. The expression [F("version") + 1] refers to the column itself, in the order SQL ([version = version + 1]): the calculation is performed by the database, not in [Python] based on a value read earlier;
• Lines 42–43: the deletion: [delete()] sends the [DELETE] from the object’s line.

Unlike [Doctrine], ORM from [Django] does not have a handler that compares each read object to its original state in order to save only the modified columns: [save()] rewrites all columns of the object (or those of its parameter [update_fields]). This is simpler and more predictable; [update] allows you to write exactly in the desired order.

9.1.5. The business layer: persons/business.py

# persons/business.py: the BUSINESS layer, the business rules, and only them.
# Each operation that writes is a TRANSACTION: committed (COMMIT) if the function
# ends normally, cancelled (ROLLBACK) if an exception leaves it

from dataclasses import dataclass

from django.db import IntegrityError, transaction

from . import dao
from .models import Person

NAMESAKE = "A person already has these first and last names."
MODIFIED = "This record has been modified meanwhile: reload it."
UNKNOWN = "This person does not exist (anymore)."


# a business rule refuses the operation; the message is intended for the user
class BusinessError(Exception):
    pass


# the data of a person, as the web layer passes them
@dataclass(frozen=True)
class PersonData:
    first_name: str
    last_name: str
    age: int


def list_all() -> list[Person]:
    return dao.list_all()


def read(id: int) -> Person:
    person = dao.read(id)
    if person is None:
        raise BusinessError(UNKNOWN)
    return person


# with transaction.atomic(): the block is executed in a transaction. The uniqueness
# constraint of the database (two simultaneous additions) raises IntegrityError, which becomes a
# business error
def add(data: PersonData) -> Person:
    try:
        with transaction.atomic():
            # rule 1: no two persons with the same first and last names
            if dao.namesake(data.first_name, data.last_name):
                raise BusinessError(NAMESAKE)
            return dao.insert(data.first_name, data.last_name, data.age)
    except IntegrityError as e:
        raise BusinessError(NAMESAKE) from e


def update(id: int, version: int, data: PersonData) -> Person:
    try:
        with transaction.atomic():
            person = read(id)
            # rule 2: a record modified meanwhile by someone else is not modified
            if person.version != version:
                raise BusinessError(MODIFIED)
            if dao.namesake(data.first_name, data.last_name, id):
                raise BusinessError(NAMESAKE)
            # the UPDATE ... WHERE version = ... modifies nothing if another request has
            # modified the row since our reading
            if not dao.update(id, version, data.first_name, data.last_name, data.age):
                raise BusinessError(MODIFIED)
            return read(id)
    except IntegrityError as e:
        raise BusinessError(NAMESAKE) from e


def delete(id: int) -> Person:
    with transaction.atomic():
        person = read(id)
        dao.delete(person)
        return person

• lines 12–14: messages from business rules;
• lines 18–19: an exception class specific to the business layer: a business rule rejects the operation, and the exception message is intended for the user. It inherits from [Exception] without adding anything to it; only its name matters;
• lines 23–27: a person’s data, as transmitted by the web layer to the business layer: a simple object, unrelated to [Django];
• lines 34–38: a nonexistent person is a business error;
• lines 44–52: Rule No. 1: No two people may have the same first and last names. The rule is verified before writing to the database, and a [BusinessError] exception is thrown if it is not followed. But this verification is not sufficient: two simultaneous requests could both verify that there is no namesake, and then both proceed to insert the record. The database’s uniqueness constraint then rejects the second [INSERT], and the ORM raises a [IntegrityError], which is transformed into the same [BusinessError]. The database is the last line of defense. [raise ... from e] links the original exception to the new one: the log will show both;
• lines 55–70: Rule No. 2: Do not modify a record that has been modified in the meantime by someone else. The update form carries the version of the record that was read (in a hidden field); if it differs from the current version, the record has changed since it was displayed. There remains the case where the concurrent modification occurs between reading line 58 and saving line 66: this is detected by the optimistic lock in layer DAO. The function then re-reads the person record to return the new version;
• lines 73–77: the deletion sets the person to “deleted” for the confirmation message.

Every operation that writes data is executed within a transaction: [transaction.atomic()], used by [with] (a context manager, example 20), opens a transaction ([BEGIN]) at the beginning of the block; if the block ends normally (even with a [return]), the transaction is committed ([COMMIT]); if an exception occurs within the block, the transaction is rolled back ([ROLLBACK]): none of the changes made within the block are retained, and the exception continues on its path. [transaction.atomic] can also be used as a decorator for an entire function ([@transaction.atomic]). The business layer thus determines the scope of transactions: one business operation equals one transaction. Outside a [atomic] block, ORM operates in “autocommit” mode: each SQL command is committed immediately. A [IntegrityError] is intercepted outside the [with] block: inside the block, the transaction would no longer be usable after the error. Other exceptions (such as a database failure or a programming error) are not business errors: they continue to be processed.

9.1.6. The Web Layer

The data entry is described by a form (Example 19):

# persons/forms.py: the form of a person (see examples 18 and 19)

from django import forms

from .business import PersonData


class PersonForm(forms.Form):
    first_name = forms.CharField(label="First name", max_length=30, error_messages={
        "required": "The first name is required.", "max_length": "30 characters at most."})
    last_name = forms.CharField(label="Last name", max_length=30, error_messages={
        "required": "The last name is required.", "max_length": "30 characters at most."})
    age = forms.IntegerField(label="Age", min_value=0, max_value=150, error_messages={
        "required": "The age is required.",
        "invalid": "The age must be an integer.",
        "min_value": "The age must be between 0 and 150.",
        "max_value": "The age must be between 0 and 150."})

    # edit=True: the edit form, which has one more field
    def __init__(self, *args, edit=False, **kwargs):
        super().__init__(*args, **kwargs)
        for field in self.fields.values():
            field.widget.attrs["class"] = "form-control"
        # the version: a hidden field, in the edit form only
        if edit:
            self.fields["version"] = forms.IntegerField(widget=forms.HiddenInput)

    # the validated data, for the business layer; CharField has already removed the
    # spaces at the start and end of the input (strip option, true by default)
    def data_for_business(self) -> PersonData:
        d = self.cleaned_data
        return PersonData(d["first_name"], d["last_name"], d["age"])

• lines 9–17: the fields and their constraints, with messages in English;
• lines 20–26: an additional parameter for the constructor, [edit], specified by name ([PersonForm(request.POST, edit=True)]): a parameter written after [*args] can only be passed by name. The update form receives one additional field, “version,” in a hidden field: [self.fields] is a dictionary to which a field can be added;
• Lines 30–32: the validated data, in the format expected by the business layer.

[Django] can also generate a form from a template (the [forms.ModelForm] class): its fields and their constraints are derived from the columns, and its [save()] method saves the object. This is very common. However, the form would then modify the model before the business layer has verified its rules. A separate form keeps the layers distinct: the web layer transmits only validated data to the business layer.

# persons/views.py: the WEB layer; it only knows the business layer

from django.contrib import messages
from django.shortcuts import redirect, render
from django.urls import reverse
from django.views.decorators.http import require_GET, require_http_methods

from . import business
from .business import BusinessError
from .forms import PersonForm


@require_GET
def home(request):
    return redirect("persons_list")


@require_GET
def list_persons(request):
    return render(request, "persons/list.html", {"persons": business.list_all()})


# GET: the empty form; POST: the sent form
@require_http_methods(["GET", "POST"])
def add(request):
    title = "Add a person"
    action = reverse("persons_add")
    if request.method == "GET":
        return form_page(request, title, PersonForm(), action)
    form = PersonForm(request.POST)
    if not form.is_valid():
        return form_page(request, title, form, action, status=422)
    try:
        p = business.add(form.data_for_business())
    except BusinessError as e:
        # a business rule refuses: the form is displayed again, with the input, and
        # the message, at the bottom of the page
        return form_page(request, title, form, action, error=str(e))
    messages.success(request, f"{p} has been added.")
    return redirect("persons_list")


# GET: the edit form, pre-filled
@require_GET
def edit(request, id):
    try:
        p = business.read(id)
    except BusinessError as e:
        messages.error(request, str(e))
        return redirect("persons_list")
    form = PersonForm(edit=True, initial={
        "first_name": p.first_name, "last_name": p.last_name, "age": p.age, "version": p.version})
    return form_page(request, "Edit a person", form,
                     reverse("persons_person", args=[id]), method="PUT")


# PUT: the sent edit form; DELETE: the deletion
@require_http_methods(["PUT", "DELETE"])
def person(request, id):
    if request.method == "DELETE":
        try:
            p = business.delete(id)
            messages.success(request, f"{p} has been deleted.")
        except BusinessError as e:
            # no form to display again: the error also travels as a flash message
            messages.error(request, str(e))
        return redirect("persons_list")
    title = "Edit a person"
    action = reverse("persons_person", args=[id])
    form = PersonForm(request.POST, edit=True)
    if not form.is_valid():
        return form_page(request, title, form, action, method="PUT", status=422)
    try:
        p = business.update(id, form.cleaned_data["version"], form.data_for_business())
    except BusinessError as e:
        return form_page(request, title, form, action, method="PUT", error=str(e))
    messages.success(request, f"{p} has been edited.")
    return redirect("persons_list")


# the page of the form; a business error: the 422 code, as for an invalid form
# (see example 19)
def form_page(request, title, form, action, method=None, error=None, status=200):
    return render(request, "persons/form.html", {
        "title": title, "form": form, "action": action, "method": method,
        "error": error}, status=422 if error else status)

• line 8: the business layer, the only layer known to the web layer;
• lines 25–40: the addition, in three steps: form validation (input errors, example 19); the call to the business layer (business rules); then the PRG schema, with a success flash message (example 20). A [BusinessError] re-displays the form, along with the entered data and the exception message ([str(e)]), which the template will display at the bottom of the page. Input errors and business logic errors are thus handled in the same way;
• lines 44–54: the edit form, pre-filled with the retrieved person (parameter [initial], example 18). If the person does not exist, the form redirects to the list with a flash error message ([messages.error]). The form is submitted using method PUT (the hidden field [_method], Example 21), to URL [/persons/<id>] ([reverse], with the route parameters in [args]);
• lines 58–78: the view from [/persons/<id>], for the methods PUT (update, following the same pattern as the add operation) and DELETE (delete, protected by the anti-CSRF token, Example 21). For deletion, there is no form to redisplay: any error is also conveyed in a flash message;
• lines 83–86: the form page. A business error is returned with code 422, as for an invalid form.

The web layer contains neither SQL nor any business rules: it translates the request into a call to the business layer and the result into a page. The routes ([persons/urls.py]): [/persons] (the list), [/persons/add], [/persons/<id>/edit], and [/persons/<id>] (PUT and DELETE).

The templates:

<!doctype html>
{% load static %}
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Persons</title>
  <link rel="stylesheet" href="{% static 'bootstrap/css/bootstrap.min.css' %}">
  <link rel="stylesheet" href="{% static 'css/styles.css' %}">
</head>
<body>
  <div class="container py-3" style="max-width: 44rem">
    {# the flash messages; tags: their level (success, error) #}
    {% for message in messages %}
      <div class="alert {% if message.tags == 'error' %}alert-danger{% else %}alert-success{% endif %}" role="status">{{ message }}</div>
    {% endfor %}
    <main>
      {% block content %}{% endblock %}
    </main>
    {% block page_bottom %}{% endblock %}
  </div>
  {% include "fragments/confirmation.html" %}
  <script src="{% static 'js/app.js' %}"></script>
</body>
</html>

• lines 14–16: the flash messages at the top of the page (example 20). Their label ([message.tags]: [success], [error]) determines their color;
• line 20: the footer block, where pages display their errors (example 19);
• lines 22–23: the confirmation window and its script (example 21).

{% extends "layout.html" %}

{% block content %}
  <div class="d-flex justify-content-between align-items-center mb-2">
    <h1 class="h3 mb-0">Persons</h1>
    <a class="btn btn-primary btn-sm" href="{% url 'persons_add' %}">Add</a>
  </div>
  <table class="table align-middle">
    <thead><tr><th>First name</th><th>Last name</th><th>Age</th><th></th></tr></thead>
    <tbody>
      {% for p in persons %}
        <tr>
          <td>{{ p.first_name }}</td>
          <td>{{ p.last_name }}</td>
          <td>{{ p.age }}</td>
          <td class="text-end">
            <a class="btn btn-sm btn-outline-primary" href="{% url 'persons_edit' p.id %}">Edit</a>
            <form method="post" class="d-inline" action="{% url 'persons_person' p.id %}"
                  data-confirmation="Delete {{ p.first_name }} {{ p.last_name }}?">
              {% csrf_token %}
              <input type="hidden" name="_method" value="DELETE">
              <button class="btn btn-sm btn-outline-danger" type="submit">Delete</button>
            </form>
          </td>
        </tr>
      {% empty %}
        <tr><td colspan="4">No persons.</td></tr>
      {% endfor %}
    </tbody>
  </table>
{% endblock %}

The list includes the links and buttons from Example 21, along with its confirmation window (the files [fragments/confirmation.html] and [static/js/app.js] are those from Example 21). The attributes [p.first_name], [p.last_name]... are those of the [Person] objects read by ORM. The block [{% empty %}] (Example 14) processes the empty list.

{% extends "layout.html" %}

{% block content %}
  <h1 class="h3">{{ title }}</h1>
  <form method="post" action="{{ action }}" novalidate style="max-width: 24rem">
    {% csrf_token %}
    {# the modification is sent with the PUT method (see example 21) #}
    {% if method %}<input type="hidden" name="_method" value="{{ method }}">{% endif %}
    {{ form.first_name.as_field_group }}
    {{ form.last_name.as_field_group }}
    {{ form.age.as_field_group }}
    {# the version: a hidden field, in the edit form only #}
    {% if form.version %}{{ form.version }}{% endif %}
    <div class="d-flex gap-2">
      <button class="btn btn-primary" type="submit">Save</button>
      <a class="btn btn-link" href="{% url 'persons_list' %}">Cancel</a>
    </div>
  </form>
{% endblock %}

{% block page_bottom %}
  {% if error %}
    <div class="error-area" role="alert">{{ error }}</div>
  {% endif %}
{% endblock %}

A template common to both adding and modifying:

• line 8: the hidden field [_method], for modification only;
• line 13: the hidden version field, if it exists in the form;
• lines 21–25: the business error, at the bottom of the page.

9.1.7. Execution

The list of people:

An incorrectly filled-out add form: input errors are detected by the web layer.

A homonym: the entry is correct, but the business layer rejects the addition. Since the comparison is performed by the database, it ignores both uppercase letters and accents: [marie pelissier] is a homonym of [Marie Pélissier].

A successful addition:

To test Rule #2, let’s open the same record in two tabs. In the first tab, let’s change the age and save. In the second tab, which still displays the old version, let’s also modify the record:

The deletion, with its confirmation:

9.1.8. The SQL commands

Let’s track the SQL commands for a modification in the console (the server was restarted, and the database was recreated). The display of the modification form for person #1:

(0.000)
                SELECT VERSION(),
                       @@sql_mode,
                       @@default_storage_engine,
                       @@sql_auto_is_null,
                       @@lower_case_table_names,
                       CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
            ; args=None; alias=default
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None; alias=default
(0.000) SELECT `persons`.`id`, `persons`.`version`, `persons`.`first_name`, `persons`.`last_name`, `persons`.`age` FROM `persons` WHERE `persons`.`id` = 1 ORDER BY `persons`.`last_name` ASC, `persons`.`first_name` ASC LIMIT 1; args=(1,); alias=default
[27/Sep/2026 19:45:19] "GET /persons/1/edit HTTP/1.1" 200 2460

Each line shows the command’s duration (in seconds), the command SQL, its parameters ([args]), and the database name ([alias]). The first two commands are used to establish the connection: [Django] queries the server (its version, its settings), then sets the transaction isolation level ([READ COMMITTED]: a transaction sees the changes committed by others). Next comes the read by primary key, with its [ORDER BY] (corresponding to [Meta.ordering]) and its [LIMIT 1] (corresponding to [first()]). In the log, the parameters are copied in order so that they can be read; originally, they are sent separately ([args]). There is no transaction: an isolated read does not require one. The master record is saved with a new age:

(0.000) BEGIN; args=None; alias=default
(0.001) SELECT `persons`.`id`, `persons`.`version`, `persons`.`first_name`, `persons`.`last_name`, `persons`.`age` FROM `persons` WHERE `persons`.`id` = 1 ORDER BY `persons`.`last_name` ASC, `persons`.`first_name` ASC LIMIT 1; args=(1,); alias=default
(0.001) SELECT `persons`.`id`, `persons`.`version`, `persons`.`first_name`, `persons`.`last_name`, `persons`.`age` FROM `persons` WHERE (`persons`.`first_name` = 'Marie' AND `persons`.`last_name` = 'Pélissier' AND NOT (`persons`.`id` = 1)) ORDER BY `persons`.`last_name` ASC, `persons`.`first_name` ASC LIMIT 1; args=('Marie', 'Pélissier', 1); alias=default
(0.000) UPDATE `persons` SET `first_name` = 'Marie', `last_name` = 'Pélissier', `age` = 43, `version` = (`persons`.`version` + 1) WHERE (`persons`.`id` = 1 AND `persons`.`version` = 0); args=('Marie', 'Pélissier', 43, 1, 1, 0); alias=default
(0.000) SELECT `persons`.`id`, `persons`.`version`, `persons`.`first_name`, `persons`.`last_name`, `persons`.`age` FROM `persons` WHERE `persons`.`id` = 1 ORDER BY `persons`.`last_name` ASC, `persons`.`first_name` ASC LIMIT 1; args=(1,); alias=default
(0.001) COMMIT; args=None; alias=default
[27/Sep/2026 19:45:19] "POST /persons/1 HTTP/1.1" 302 0

(the connection was opened, as in the previous request). Within a single transaction ([BEGIN] ... [COMMIT]), we can see: the read of the record, the search for a namesake, the [UPDATE], and then the re-read of the record. The [UPDATE] increments the version ([version + 1]) and sets the optimistic lock condition ([AND version = 0]). The development server logs the request as a POST: this is how it arrived, before the middleware converted it to a PUT. Now let’s submit the same form a second time, still using the old version:

(0.000) BEGIN; args=None; alias=default
(0.000) SELECT `persons`.`id`, `persons`.`version`, `persons`.`first_name`, `persons`.`last_name`, `persons`.`age` FROM `persons` WHERE `persons`.`id` = 1 ORDER BY `persons`.`last_name` ASC, `persons`.`first_name` ASC LIMIT 1; args=(1,); alias=default
(0.000) ROLLBACK; args=None; alias=default
Unprocessable Content: /persons/1
[27/Sep/2026 19:45:19] "POST /persons/1 HTTP/1.1" 422 2562

The version being read is not the one from the form: the business layer raised a [BusinessError], which rolled back the transaction ([ROLLBACK]), and the page displays “This record has been modified meanwhile: reload it.” Similarly, adding a namesake stops after the namesake search, without a [INSERT].