The magic between Django and SQLite

A deep dive using the ``Exercise`` model from Liftlog.

Everything below was captured from this project: Django 5.2.17, SQLite 3.40.1, and Liftlog’s real db.sqlite3. The SQL statements are the exact ones Django sent. They were recorded with django.test.utils.CaptureQueriesContext on a copy of the database.

Each numbered section is self-contained, so each one can be its own video.

0. The inputs

The model (workouts/models.py)

class Exercise(models.Model):
    name = models.CharField(max_length=100, unique=True)
    muscle = models.ForeignKey(Muscle, on_delete=models.PROTECT, related_name="exercises")
    secondary_muscles = models.ManyToManyField(
        Muscle, blank=True, related_name="secondary_exercises",
        help_text="Other muscles it works. Each counts as half a set in weekly sets.",
    )
    equipment = models.CharField(max_length=60, blank=True, help_text="Optional, your own words, e.g. \"Hammer Strength\"")
    is_custom = models.BooleanField(default=False, help_text="Added by you, not part of the built-in library")
    unit = models.CharField(max_length=2, choices=[("lb", "lb"), ("kg", "kg")], default="lb",
                            help_text="All weights for this lift are stored and shown in this unit")

    class Meta:
        ordering = ["name"]

    def __str__(self):
        return self.name

    def set_unit(self, unit):
        """Switch lb <-> kg, converting every weight already saved for this lift."""
        if unit == self.unit or unit not in ("lb", "kg"):
            return
        factor = 1 / LB_PER_KG if unit == "kg" else LB_PER_KG
        rows = [
            *PlannedExercise.objects.filter(exercise=self, weight__isnull=False),
            *WorkoutExercise.objects.filter(exercise=self, weight__isnull=False),
            *WorkoutSet.objects.filter(workout_exercise__exercise=self, weight__isnull=False),
        ]
        for row in rows:
            row.weight = round(row.weight * factor, 1)
            row.save(update_fields=["weight"])
        self.unit = unit
        self.save(update_fields=["unit"])

The schema (sqlite3 db.sqlite3 ".schema workouts_exercise")

CREATE TABLE IF NOT EXISTS "workouts_exercise" (
  "id"        integer      NOT NULL PRIMARY KEY AUTOINCREMENT,
  "name"      varchar(100) NOT NULL UNIQUE,
  "equipment" varchar(60)  NOT NULL,
  "is_custom" bool         NOT NULL,
  "unit"      varchar(2)   NOT NULL,
  "muscle_id" bigint       NOT NULL REFERENCES "workouts_muscle" ("id") DEFERRABLE INITIALLY DEFERRED
);
CREATE INDEX "workouts_exercise_muscle_id_641d6f26" ON "workouts_exercise" ("muscle_id");

…plus a second table the model created without you ever writing it:

CREATE TABLE IF NOT EXISTS "workouts_exercise_secondary_muscles" (
  "id"          integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "exercise_id" bigint  NOT NULL REFERENCES "workouts_exercise" ("id") DEFERRABLE INITIALLY DEFERRED,
  "muscle_id"   bigint  NOT NULL REFERENCES "workouts_muscle"   ("id") DEFERRABLE INITIALLY DEFERRED
);
CREATE UNIQUE INDEX "workouts_exercise_secondary_muscles_exercise_id_muscle_id_cb7e9f56_uniq"
  ON "workouts_exercise_secondary_muscles" ("exercise_id", "muscle_id");
CREATE INDEX "workouts_exercise_secondary_muscles_exercise_id_fe2660e7" ON "workouts_exercise_secondary_muscles" ("exercise_id");
CREATE INDEX "workouts_exercise_secondary_muscles_muscle_id_c6c446c6"   ON "workouts_exercise_secondary_muscles" ("muscle_id");

The database holds 264 rows in workouts_exercise. A few of them:

id|name                |equipment|is_custom|unit|muscle_id
46|Bench Press         |         |0        |lb  |1
47|Paused Bench Press  |         |0        |lb  |1
48|Incline Bench Press |         |0        |lb  |2

1. The big picture: seven layers

When you write Exercise.objects.get(name="Bench Press"), the request passes down through these layers and the answer comes back up:

Your code          Exercise.objects.get(name="Bench Press")
   │
① Model class      Exercise  (built by the ModelBase metaclass; knows its table, columns, relations: Exercise._meta)
   │
② Manager          Exercise.objects  → hands out QuerySets
   │
③ QuerySet         lazy description of "which rows"; holds a Query object
   │
④ Query → SQL      Query tree compiled by SQLCompiler into  ("SELECT ... WHERE name = %s", ["Bench Press"])
   │
⑤ Backend          django.db.backends.sqlite3.DatabaseWrapper: converts %s → ?, manages connection & transactions
   │
⑥ DB-API driver    Python's built-in sqlite3 module (PEP 249) → calls the C library
   │
⑦ SQLite engine    libsqlite3: parses SQL, plans it, walks B-trees in the file  db.sqlite3

The “magic” is mostly layers ① to ⑤. They are plain Python, and you can read every line in .venv/lib/python3.11/site-packages/django/db/. SQLite itself is an ordinary C library linked into your Python process. There is no database server. Your Django process is the database engine, reading and writing one file directly.

2. Magic #1: a class becomes a table description

Writing class Exercise(models.Model): runs Django’s metaclass ModelBase.__new__ (django/db/models/base.py). Python calls it while it creates the class. Then, for every attribute that is a Field, Django calls field.contribute_to_class(cls, name). This step:

  • Registers the field in Exercise._meta (an Options object). _meta is the model’s complete self-description. Try Exercise._meta.db_table, Exercise._meta.get_fields(), and Exercise._meta.ordering.

  • Gives each field its database column name (field.column). For a ForeignKey named muscle, that’s muscle_id.

  • Replaces relation attributes with descriptors. A descriptor is a Python object that intercepts ex.muscle. See section 6.

  • Attaches a default Manager as Exercise.objects.

  • Adds an id primary key, because you didn’t declare one. Its type comes from DEFAULT_AUTO_FIELD = 'django.db.models.BigAutoField' in liftlog/settings.py.

The table name is <app_label>_<model name lowercased>, which gives workouts + _ + exercise.

Field-by-field: Python → column

Python field

Column in SQLite

What’s really going on

implicit id = BigAutoField(primary_key=True)

"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT

Django’s SQLite backend maps BigAutoField to integer, not bigint. That’s deliberate: only a column declared exactly INTEGER PRIMARY KEY becomes an alias for SQLite’s internal rowid, the key of the table’s B-tree. AUTOINCREMENT (from data_types_suffix) tells SQLite never to reuse ids of deleted rows. SQLite tracks the highest id in a hidden table, sqlite_sequence.

name = CharField(max_length=100, unique=True)

varchar(100) NOT NULL UNIQUE

UNIQUE makes SQLite build an automatic index (sqlite_autoindex_workouts_exercise_1). The 100 is ignored by SQLite: it gives the column TEXT affinity and never checks the length (see section 7).

muscle = ForeignKey(Muscle, on_delete=PROTECT)

muscle_id bigint NOT NULL REFERENCES "workouts_muscle"("id") DEFERRABLE INITIALLY DEFERRED + an index

The FK column points at Muscle’s id, so it uses the related type bigint. DEFERRABLE INITIALLY DEFERRED means SQLite checks the reference at COMMIT rather than after each statement. That lets Django insert rows in any order inside a transaction. Django adds an index because FKs are joined on constantly. on_delete=PROTECT is nowhere in the SQL. Django implements PROTECT/CASCADE/SET_NULL itself in Python (the deletion Collector) before it sends any DELETE.

secondary_muscles = ManyToManyField(Muscle)

no column at all

A many-to-many can’t fit in one row, so Django auto-creates a hidden through model and table workouts_exercise_secondary_muscles with two FKs, plus a unique index on the pair, so one muscle can’t be linked to one exercise twice.

equipment = CharField(blank=True)

varchar(60) NOT NULL

blank=True is a form/validation rule, not a database rule. Django convention: text columns store '' for “empty”, never NULL, so there’s only one kind of “nothing”.

is_custom = BooleanField(default=False)

bool NOT NULL

SQLite has no boolean type. bool gets NUMERIC affinity and the value is stored as integer 0/1. On the way back out, Django’s converters turn 0/1 into False/True.

unit = CharField(max_length=2, choices=[...], default="lb")

varchar(2) NOT NULL

choices and default are not in the schema. Django fills in the default in Python before INSERT, and choices is only checked by full_clean() and forms (see section 7).

help_text=..., Meta.ordering, __str__, set_unit

nothing

Pure Python. They exist for forms, the admin, and your code. ordering turns into an ORDER BY on queries, not into anything in the table.

related_name="exercises"

nothing

Adds a reverse descriptor on Muscle, so some_muscle.exercises.all() works.

Key idea for a video: the model is the source of truth for Python, and the schema is the source of truth for SQLite. They overlap but they are not the same. Some rules live only in Python (choices, blank, max_length, on_delete, default). Others live only in the database (UNIQUE, NOT NULL, foreign keys, CHECK constraints).

3. Magic #2: migrations turn class changes into SQL

You never typed CREATE TABLE. Two commands did:

  1. makemigrations loads every migration file and replays them in memory to rebuild the historical state of each model. It compares that with your current classes and writes the difference as Python operations: AddField, AlterField, CreateModel, and so on.

  2. migrate executes those operations through a SchemaEditor, which turns each one into SQL for your database backend. It then records each applied migration in the django_migrations table:

workouts|0006_exercise_unit|2026-10-07 13:31:30
workouts|0011_normalize_swap_fields|2026-10-07 13:59:47
...

You can see the SQL a migration runs, without running it, using python manage.py sqlmigrate workouts 0006. Migration 0006 added unit to Exercise:

BEGIN;
CREATE TABLE "new__workouts_exercise" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "unit" varchar(2) NOT NULL,
  "name" varchar(100) NOT NULL UNIQUE, "muscle" varchar(20) NOT NULL, "equipment" varchar(60) NOT NULL, "is_custom" bool NOT NULL);
INSERT INTO "new__workouts_exercise" ("id", "name", "muscle", "equipment", "is_custom", "unit")
  SELECT "id", "name", "muscle", "equipment", "is_custom", 'lb' FROM "workouts_exercise";
DROP TABLE "workouts_exercise";
ALTER TABLE "new__workouts_exercise" RENAME TO "workouts_exercise";
COMMIT;

This is the table remake, SQLite’s most famous quirk. SQLite’s ALTER TABLE is limited (no changing a column’s type or constraints, for example). So Django’s SQLite schema editor (_remake_table) builds a new table with the right shape, copies every row over, drops the old table, and renames the new one into place. Notice:

  • default="lb" shows up here, as the literal 'lb' used to backfill existing rows. That is the only place the default ever touches SQL.

  • In this migration muscle was still a varchar(20) text column. Migration 0011 (the normalization) remade the table again: it added muscle_id as a real foreign key and dropped the JSON secondary_muscles column in favor of the link table. The schema has a history, and django_migrations is its logbook.

  • During a remake, the schema editor runs PRAGMA foreign_keys = OFF (so dropping a table other tables point at doesn’t fail). Afterwards it runs a constraint check (PRAGMA foreign_key_check) before turning them back on (django/db/backends/sqlite3/schema.py, __enter__/__exit__).

Why .schema says CREATE TABLE IF NOT EXISTS: that text isn’t stored in the file. Run sqlite3 db.sqlite3 "SELECT sql FROM sqlite_schema WHERE name='workouts_exercise'" and you’ll see the stored statement is plain CREATE TABLE "workouts_exercise" .... The sqlite3 command-line shell adds IF NOT EXISTS when it prints the schema, so the output can be safely re-run as a script.

4. Magic #3: the connection

liftlog/settings.py says:

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

ENGINE is a Python import path. Django imports django.db.backends.sqlite3.base and uses its DatabaseWrapper class. Every backend (PostgreSQL, MySQL, Oracle) is a sibling package with the same interface. That’s why your models don’t change if you switch databases.

The connection is lazy and per-thread. Nothing opens until the first query. Then DatabaseWrapper.get_new_connection() runs, and it does four things worth knowing:

  1. Calls Python’s sqlite3.connect(...) with detect_types=PARSE_DECLTYPES | PARSE_COLNAMES. That lets declared column types such as date and datetime drive conversion back into Python date/datetime objects. It also sets check_same_thread=False, because Django manages thread safety itself.

  2. Registers Python functions inside SQLite (register_functions(conn) in sqlite3/_functions.py). SQLite lacks many SQL functions that PostgreSQL has, such as date truncation, some math functions and regex. Django defines them in Python and hands them to SQLite with create_function, so SQL like django_date_extract('week_day', "date") calls back into your Python process. When you write .filter(date__week_day=2), a C library calls a Python function for every row.

  3. Runs PRAGMA foreign_keys = ON. SQLite has foreign-key enforcement off by default, per connection, for backward compatibility. You can prove it:

    $ sqlite3 db.sqlite3 "PRAGMA foreign_keys;"
0                       ← the CLI's connection: FKs NOT enforced

    >>> connection.cursor().execute("PRAGMA foreign_keys").fetchone()
(1,)                    ← Django's connection: enforced

    So the same file has different rules depending on who opens it. If you edit db.sqlite3 by hand in the CLI, nothing stops you from pointing muscle_id at a muscle that doesn’t exist.

  4. Sets autocommit mode. Each statement commits on its own unless you’re inside transaction.atomic() (more in section 8).

Placeholders: %s becomes ?

Internally, every Django backend writes SQL with %s placeholders. The Python sqlite3 module expects ? (“qmark” style). SQLiteCursorWrapper.convert_query() rewrites one into the other with a regex (FORMAT_QMARK_REGEX = r"(?<!%)%s") just before execution. Values are never pasted into the SQL string. They travel separately as parameters, and SQLite binds them. That’s what makes the ORM immune to SQL injection.

5. Magic #4: QuerySets are lazy, then compiled

qs = Exercise.objects.filter(muscle__group__name="Chest").order_by("name")

This line sends no SQL. A QuerySet is a description of a query: a Query object holding a tree of WHERE conditions, joins, ordering, limits and so on. Each .filter() clones it and adds to the tree. You can peek at the SQL it would run:

>>> print(qs.query)
SELECT "workouts_exercise"."id", "workouts_exercise"."name", "workouts_exercise"."muscle_id",
       "workouts_exercise"."equipment", "workouts_exercise"."is_custom", "workouts_exercise"."unit"
FROM "workouts_exercise"
INNER JOIN "workouts_muscle"      ON ("workouts_exercise"."muscle_id" = "workouts_muscle"."id")
INNER JOIN "workouts_musclegroup" ON ("workouts_muscle"."group_id"   = "workouts_musclegroup"."id")
WHERE "workouts_musclegroup"."name" = Chest
ORDER BY "workouts_exercise"."name" ASC

Notice = Chest has no quotes. print(qs.query) is only an approximation for humans. The real execution sends ... = ? plus the parameter ('Chest',).

How muscle__group__name becomes two JOINs: the __ separator is the ORM’s path language. Query.setup_joins() walks it one piece at a time using _meta:

  • muscle is a ForeignKey on Exercise, so it joins workouts_muscle on muscle_id = id.

  • group is a ForeignKey on Muscle, so it joins workouts_musclegroup on group_id = id.

  • name is an ordinary field, so the final column is workouts_musclegroup.name.

The lookup has no suffix, which means __exact, which compiles to =. Other lookups compile to other SQL: __icontains becomes LIKE ... ESCAPE, and __isnull=False becomes IS NOT NULL.

When does SQL actually run? When the QuerySet is evaluated: iterating, list(), len(), bool(), indexing such as qs[0], or calling .get(), .count(), .exists() or .first(). After that, the results are cached inside that QuerySet object.

Captured: .get()

Exercise.objects.get(name="Bench Press")

SELECT "workouts_exercise"."id", "workouts_exercise"."name", "workouts_exercise"."muscle_id",
       "workouts_exercise"."equipment", "workouts_exercise"."is_custom", "workouts_exercise"."unit"
FROM "workouts_exercise" WHERE "workouts_exercise"."name" = 'Bench Press' LIMIT 21

Why LIMIT 21? .get() must raise MultipleObjectsReturned if more than one row matches. Rather than fetch an unbounded number of rows just to complain, Django fetches at most MAX_GET_RESULTS = 21 (django/db/models/query.py). That’s enough to say “returned more than 20!” in the error.

What SQLite does with it (EXPLAIN QUERY PLAN):

SEARCH workouts_exercise USING INDEX sqlite_autoindex_workouts_exercise_1 (name=?)

The unique=True on name built that index, so this is a B-tree lookup, not a scan. Compare a column with no index:

EXPLAIN QUERY PLAN SELECT * FROM workouts_exercise WHERE unit='kg';
SCAN workouts_exercise          ← reads every row

Rows become objects

The cursor returns plain tuples: (46, 'Bench Press', 1, '', 0, 'lb'). The compiler knows which column is which field, applies converters (for example 0 → False), and calls Exercise.from_db(), which builds the instance without calling your __init__ logic per field. It also sets instance._state.adding = False and instance._state.db = "default", so later code knows this row already exists in that database.

6. Magic #5: relationships are descriptors (and the N+1 problem)

ex.muscle looks like plain attribute access, but Exercise.muscle is a ForwardManyToOneDescriptor (django/db/models/fields/related_descriptors.py). Its __get__ runs whenever you read the attribute.

ex = Exercise.objects.get(name="Bench Press")   # 1 query (above)
ex.muscle                                       # descriptor fires:

SELECT "workouts_muscle"."id", "workouts_muscle"."code", "workouts_muscle"."name", "workouts_muscle"."order", "workouts_muscle"."group_id"
FROM "workouts_muscle" WHERE "workouts_muscle"."id" = 1 LIMIT 21

ex.muscle   # again → NO SQL. The result was stored in ex._state.fields_cache["muscle"]

The row already held muscle_id = 1, so ex.muscle_id never needs a query. Only following the relation to the object does.

ex.secondary_muscles is a ManyToManyDescriptor. It returns a manager class that Django builds on the fly (create_forward_many_to_many_manager), already filtered to this exercise through the hidden link table:

-- list(ex.secondary_muscles.all())   →  [Front delts, Triceps lateral head]
SELECT "workouts_muscle"."id", "workouts_muscle"."code", "workouts_muscle"."name", "workouts_muscle"."order", "workouts_muscle"."group_id"
FROM "workouts_muscle"
INNER JOIN "workouts_exercise_secondary_muscles" ON ("workouts_muscle"."id" = "workouts_exercise_secondary_muscles"."muscle_id")
WHERE "workouts_exercise_secondary_muscles"."exercise_id" = 46
ORDER BY "workouts_muscle"."order" ASC, "workouts_muscle"."name" ASC

The ORDER BY came from Muscle’s Meta.ordering = ["order", "name"], not from anything you wrote in the query.

The N+1 problem and its two cures

Looping over 264 exercises and touching e.muscle on each one runs 1 + 264 queries. Django gives two fixes, and they work in different ways:

select_related("muscle") uses one query with a JOIN. It works for FK and one-to-one:

SELECT "workouts_exercise"."id", ..., "workouts_exercise"."unit",
       "workouts_muscle"."id", "workouts_muscle"."code", "workouts_muscle"."name", "workouts_muscle"."order", "workouts_muscle"."group_id"
FROM "workouts_exercise" INNER JOIN "workouts_muscle" ON ("workouts_exercise"."muscle_id" = "workouts_muscle"."id")
ORDER BY "workouts_exercise"."name" ASC LIMIT 2

Each result row is split in two: the first 6 columns build the Exercise, the next 5 build the Muscle, and the Muscle is put straight into the Exercise’s field cache.

prefetch_related("secondary_muscles") uses two queries plus stitching in Python. It works for many-to-many and reverse FKs, where a JOIN would duplicate rows:

SELECT ... FROM "workouts_exercise" WHERE "workouts_exercise"."id" IN (121, 95) ORDER BY "workouts_exercise"."name" ASC;

SELECT ("workouts_exercise_secondary_muscles"."exercise_id") AS "_prefetch_related_val_exercise_id",
       "workouts_muscle"."id", "workouts_muscle"."code", "workouts_muscle"."name", "workouts_muscle"."order", "workouts_muscle"."group_id"
FROM "workouts_muscle"
INNER JOIN "workouts_exercise_secondary_muscles" ON ("workouts_muscle"."id" = "workouts_exercise_secondary_muscles"."muscle_id")
WHERE "workouts_exercise_secondary_muscles"."exercise_id" IN (121, 95)
ORDER BY "workouts_muscle"."order" ASC, "workouts_muscle"."name" ASC

Notice the extra column _prefetch_related_val_exercise_id. Django adds it so it can sort each muscle back to the exercise it belongs to, in Python, after both queries return. Liftlog’s History page uses exactly this pattern (Prefetch("exercises__logged_sets", ...)) so it can show every workout with a fixed handful of queries.

7. Magic #6: writes

INSERT with RETURNING

Exercise.objects.create(name="Zercher Squat Test", muscle=Muscle.objects.get(code="quads"), is_custom=True)

SELECT ... FROM "workouts_muscle" WHERE "workouts_muscle"."code" = 'quads' LIMIT 21;
INSERT INTO "workouts_exercise" ("name", "muscle_id", "equipment", "is_custom", "unit")
VALUES ('Zercher Squat Test', 11, '', 1, 'lb') RETURNING "workouts_exercise"."id";

  • equipment = '' and unit = 'lb' were filled in by Python from the field defaults ('' is CharField’s implicit default). The database has no DEFAULT clauses.

  • is_custom=True became 1.

  • muscle=<Muscle> became muscle_id = 11. The FK field stores only the id.

  • id is left out, so SQLite picks the next rowid. RETURNING (supported since SQLite 3.35) hands the new id straight back, so Django sets ex.id = 310 without a second query.

How save() picks INSERT vs UPDATE: Model._save_table() checks self._state.adding and whether pk is set. A new object is INSERTed. An object loaded from the database (adding=False) is UPDATEd. save(update_fields=["unit"]) narrows the UPDATE to just that column, instead of rewriting every column with possibly stale values.

The database says no: UNIQUE

with transaction.atomic():
    Exercise.objects.create(name="Bench Press", muscle_id=1)

BEGIN;
INSERT INTO "workouts_exercise" (...) VALUES ('Bench Press', 1, '', 0, 'lb') RETURNING "workouts_exercise"."id";
ROLLBACK;

django.db.utils.IntegrityError: UNIQUE constraint failed: workouts_exercise.name

SQLite rejected the row, because the rule lives in the schema. The Python sqlite3 module raised sqlite3.IntegrityError, and Django re-raised it as django.db.utils.IntegrityError. That wrapping (DatabaseErrorWrapper) is why your code catches the same exception class on every database.

The database says yes, when it shouldn’t

Exercise(name="Bad Unit", muscle_id=1, unit="stone").save()

INSERT INTO "workouts_exercise" ("name", "muscle_id", "equipment", "is_custom", "unit")
VALUES ('Bad Unit', 1, '', 0, 'stone') RETURNING "workouts_exercise"."id";

It succeeded. Two “rules” were broken and nobody noticed:

  1. 'stone' isn’t in choices. Choices live only in Python.

  2. 'stone' is 5 characters in a varchar(2) column. SQLite uses type affinity, not strict types: varchar(2) just means “prefer TEXT”, and the (2) is decoration. (PostgreSQL would have refused this.)

save() does not validate. Validation (full_clean(), which checks choices, max_length, blank and so on) runs automatically only in ModelForms and the admin. Liftlog’s set_unit guards against this itself (unit not in ("lb", "kg")). To make the database enforce it, add a constraint. Django turns it into a SQL CHECK clause in the schema:

class Meta:
    constraints = [models.CheckConstraint(condition=Q(unit__in=["lb", "kg"]), name="exercise_unit_lb_or_kg")]

8. Magic #7: tracing the set_unit method

Calling hack_squat.set_unit("kg") produced exactly this SQL:

-- rows = [...]  three QuerySets, each evaluated by the * unpacking:
SELECT ... FROM "workouts_plannedexercise"
  INNER JOIN "workouts_splitday" ON ("workouts_plannedexercise"."day_id" = "workouts_splitday"."id")
  WHERE ("workouts_plannedexercise"."exercise_id" = 217 AND "workouts_plannedexercise"."weight" IS NOT NULL)
  ORDER BY "workouts_splitday"."split_id" ASC, "workouts_splitday"."order" ASC, "workouts_plannedexercise"."order" ASC;

SELECT ... FROM "workouts_workoutexercise"
  INNER JOIN "workouts_workout" ON ("workouts_workoutexercise"."workout_id" = "workouts_workout"."id")
  WHERE ("workouts_workoutexercise"."exercise_id" = 217 AND "workouts_workoutexercise"."weight" IS NOT NULL)
  ORDER BY "workouts_workout"."date" DESC, "workouts_workout"."id" DESC, "workouts_workoutexercise"."order" ASC;

SELECT ... FROM "workouts_workoutset"
  INNER JOIN "workouts_workoutexercise" ON ("workouts_workoutset"."workout_exercise_id" = "workouts_workoutexercise"."id")
  INNER JOIN "workouts_workout" ON ("workouts_workoutexercise"."workout_id" = "workouts_workout"."id")
  WHERE ("workouts_workoutset"."weight" IS NOT NULL AND "workouts_workoutexercise"."exercise_id" = 217)
  ORDER BY "workouts_workout"."date" DESC, "workouts_workout"."id" DESC, "workouts_workoutexercise"."order" ASC, "workouts_workoutset"."order" ASC;

-- for row in rows: row.save(update_fields=["weight"])
UPDATE "workouts_plannedexercise" SET "weight" = 122.5 WHERE "workouts_plannedexercise"."id" = 39;
UPDATE "workouts_plannedexercise" SET "weight" = 122.5 WHERE "workouts_plannedexercise"."id" = 62;
UPDATE "workouts_workoutexercise" SET "weight" = 122.5 WHERE "workouts_workoutexercise"."id" = 9;
UPDATE "workouts_workoutset"      SET "weight" = 122.5 WHERE "workouts_workoutset"."id" = 7;
UPDATE "workouts_workoutset"      SET "weight" = 122.5 WHERE "workouts_workoutset"."id" = 8;
UPDATE "workouts_workoutset"      SET "weight" = 122.5 WHERE "workouts_workoutset"."id" = 9;

-- self.save(update_fields=["unit"])
UPDATE "workouts_exercise" SET "unit" = 'kg' WHERE "workouts_exercise"."id" = 217;

Things to point out on video:

  1. Hidden JOINs. The code only filtered by exercise=self, but the SQL joins splitday, workout and workoutexercise. Those joins come purely from each model’s Meta.ordering, which orders by fields on related tables (["day", "order"], ["-date", "-id"] and so on). Default ordering isn’t free.

  2. exercise=self compiled to exercise_id = 217. Passing a model instance to a filter uses its pk.

  3. workout_exercise__exercise=self spans a relation. That’s one JOIN, and the WHERE runs on the joined table’s column.

  4. The math happens in Python. Each weight was read into Python, multiplied by 1 / 2.20462, rounded, and written back with one UPDATE per row. With 300 logged sets, that’s 300 UPDATEs.

  5. It is not atomic. Django runs in autocommit mode, so each UPDATE commits on its own. If the process died halfway, some sets would be in kg and some in lb, and unit would still say lb.

A version that fixes 4 and 5 pushes the math into SQL with an F expression, which refers to a column’s value inside the database, and wraps everything in a transaction:

from django.db import transaction
from django.db.models import F
from django.db.models.functions import Round

with transaction.atomic():                          # BEGIN ... COMMIT (or ROLLBACK on error)
    new = Round(F("weight") * factor, 1)
    PlannedExercise.objects.filter(exercise=self, weight__isnull=False).update(weight=new)
    WorkoutExercise.objects.filter(exercise=self, weight__isnull=False).update(weight=new)
    WorkoutSet.objects.filter(workout_exercise__exercise=self, weight__isnull=False).update(weight=new)
    self.unit = unit
    self.save(update_fields=["unit"])

That’s 4 UPDATE statements total, no matter how many sets you have. They look like UPDATE "workouts_workoutset" SET "weight" = ROUND(("workouts_workoutset"."weight" * 0.4535...), 1) WHERE ..., and they commit together or not at all. QuerySet.update() skips save(), so no per-object Python runs. That’s the trade-off for the speed.

9. Magic #8: inside the file

db.sqlite3 is a single file, and its format is documented and stable. The first 16 bytes are always the same:

$ od -A d -c -N 32 db.sqlite3
0000000   S   Q   L   i   t   e       f   o   r   m   a   t       3  \0
0000016 020  \0 001 001  \0   @          \0  \0  \0 352  \0  \0  \0   L

  • Bytes 0–15: the magic string SQLite format 3\0.

  • Bytes 16–17: 020 \0 = 0x1000 = 4096, the page size.

  • Bytes 28–31: \0 \0 \0 L = 0x4C = 76, the page count.

  • 76 pages × 4096 bytes = 311,296 bytes. That’s exactly the size ls -l shows for Liftlog’s database.

$ sqlite3 db.sqlite3 "PRAGMA page_size; PRAGMA page_count; PRAGMA journal_mode;"
4096
76
delete

The whole file is an array of 4 KB pages. Every table and every index is a separate B-tree, and its root page number is stored in the master table sqlite_schema:

$ sqlite3 db.sqlite3 "SELECT name, rootpage FROM sqlite_schema WHERE tbl_name='workouts_exercise';"
workouts_exercise|53                                   ← the table: B-tree keyed by rowid (= id)
sqlite_autoindex_workouts_exercise_1|54                ← from unique=True on name: B-tree keyed by name → rowid
workouts_exercise_muscle_id_641d6f26|52                ← the FK index: keyed by muscle_id → rowid

So Exercise.objects.get(name="Bench Press") physically means:

  1. Read page 1 (sqlite_schema) to learn where things live. This is cached after the first time.

  2. Walk the name index B-tree from page 54 to find 'Bench Press' → rowid 46.

  3. Walk the table B-tree from page 53 to rowid 46 and decode the row’s bytes into 6 values.

  4. Hand them up through the C API to Python’s sqlite3 module, then to Django’s converters, then to Exercise.from_db(), and finally to your variable ex.

Journal mode delete: during a write transaction, SQLite first copies the original pages it’s about to change into db.sqlite3-journal. Then it modifies the main file. On COMMIT it deletes the journal. If the process crashes mid-write, the next connection finds the journal and rolls the file back. That is how a single file gets atomic transactions. It’s also why SQLite allows one writer at a time: a write takes a lock on the whole file.

10. Cheat sheet

You write (Python)

SQLite sees

class Exercise(models.Model)

table workouts_exercise (via migration)

implicit id

integer PRIMARY KEY AUTOINCREMENT = the rowid

unique=True

UNIQUE + automatic index

ForeignKey(Muscle)

muscle_id ... REFERENCES ... DEFERRABLE INITIALLY DEFERRED + index

on_delete=PROTECT

(nothing; enforced in Python)

ManyToManyField

separate link table with a unique (pair) index

default=, choices=, blank=, help_text=

(nothing; Python only)

Meta.ordering

ORDER BY added to queries (sometimes extra JOINs)

Exercise.objects.filter(...)

(nothing yet; lazy)

iterate / list() / .get()

SELECT ... (.get() adds LIMIT 21)

a__b__c=

one INNER JOIN per __ hop

ex.muscle

SELECT ... WHERE id = ? the first time, then cached

select_related

one query with JOIN

prefetch_related

second query with IN (...), stitched in Python

.create() / new .save()

INSERT ... RETURNING id

.save(update_fields=[...])

UPDATE ... SET <those columns> WHERE id = ?

.update(x=F(...))

one UPDATE computed inside SQLite

transaction.atomic()

BEGIN … COMMIT / ROLLBACK (SAVEPOINT when nested)

%s params

? placeholders, bound separately (no SQL injection)

11. Try it yourself (good for screen recordings)

# Look at the schema and the real stored SQL
sqlite3 db.sqlite3 ".schema workouts_exercise"
sqlite3 db.sqlite3 "SELECT sql FROM sqlite_schema WHERE name='workouts_exercise';"

# See how SQLite will run a query
sqlite3 db.sqlite3 "EXPLAIN QUERY PLAN SELECT * FROM workouts_exercise WHERE name='Bench Press';"

# Foreign keys: off in the CLI, on in Django
sqlite3 db.sqlite3 "PRAGMA foreign_keys;"

# See the SQL a migration would run
python manage.py sqlmigrate workouts 0006

# Open a SQLite shell with Django's settings
python manage.py dbshell

# python manage.py shell
from django.db import connection
from django.test.utils import CaptureQueriesContext
from workouts.models import Exercise

qs = Exercise.objects.filter(muscle__group__name="Chest")
print(qs.query)                       # SQL, without running it

with CaptureQueriesContext(connection) as ctx:
    ex = Exercise.objects.get(name="Bench Press")
    ex.muscle
    ex.muscle                         # cached: no second query
    list(ex.secondary_muscles.all())
for q in ctx.captured_queries:
    print(q["sql"])
print(len(ctx.captured_queries), "queries")

Exercise._meta.db_table               # 'workouts_exercise'
[f.column for f in Exercise._meta.concrete_fields]

Warning: run write experiments on a copy of db.sqlite3, or inside a transaction you roll back. set_unit really converts your data.

12. Quiz questions (for the game)

  1. Where does default="lb" appear in SQL? (Only in the migration that added the column, as a backfill literal, and in INSERTs, where Python supplies it. Never as a column DEFAULT.)

  2. Why is id declared integer when the model uses BigAutoField? (Only ``INTEGER PRIMARY KEY`` aliases SQLite’s rowid.)

  3. Saving unit="stone" succeeds. Name two reasons. (``choices`` isn’t in the schema, and SQLite ignores ``varchar(2)`` length.)

  4. Why does .get() add LIMIT 21? (To detect ``MultipleObjectsReturned`` without fetching every row.)

  5. How many queries does ex.muscle; ex.muscle run? (One. The second read comes from the field cache.)

  6. select_related or prefetch_related for secondary_muscles? (prefetch: it’s many-to-many.)

  7. What runs PRAGMA foreign_keys = ON, and why must it? (Django’s ``get_new_connection``. SQLite enforcement is off by default, per connection.)

  8. Why did set_unit JOIN workouts_workout when it only filtered by exercise? (``Meta.ordering`` on WorkoutExercise orders by the workout’s date.)

  9. What happens to set_unit’s data if the process crashes halfway? (Half-converted. Each UPDATE autocommits, because there’s no ``atomic()``.)

  10. What does on_delete=PROTECT compile to in SQLite? (Nothing. Django’s Collector checks it in Python before deleting.)