=================================== 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``) ---------------------------------- .. code-block:: python 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"``) --------------------------------------------------------------- .. code-block:: sql 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: .. code-block:: sql 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: .. code-block:: text 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: .. code-block:: text 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 ``_``, which gives ``workouts`` + ``_`` + ``exercise``. Field-by-field: Python → column ------------------------------- .. list-table:: :header-rows: 1 :widths: auto * - 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: .. code-block:: text 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: .. code-block:: sql 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: .. code-block:: python 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: .. code-block:: console $ sqlite3 db.sqlite3 "PRAGMA foreign_keys;" 0 ← the CLI's connection: FKs NOT enforced .. code-block:: python >>> 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"(?>> 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()`` -------------------- .. code-block:: python Exercise.objects.get(name="Bench Press") .. code-block:: sql 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``): .. code-block:: text 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: .. code-block:: text 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. .. code-block:: python ex = Exercise.objects.get(name="Bench Press") # 1 query (above) ex.muscle # descriptor fires: .. code-block:: sql 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 .. code-block:: python 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*: .. code-block:: sql -- 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: .. code-block:: sql 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: .. code-block:: sql 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 --------------------- .. code-block:: python Exercise.objects.create(name="Zercher Squat Test", muscle=Muscle.objects.get(code="quads"), is_custom=True) .. code-block:: sql 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=`` 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 ---------------------------- .. code-block:: python with transaction.atomic(): Exercise.objects.create(name="Bench Press", muscle_id=1) .. code-block:: sql BEGIN; INSERT INTO "workouts_exercise" (...) VALUES ('Bench Press', 1, '', 0, 'lb') RETURNING "workouts_exercise"."id"; ROLLBACK; .. code-block:: text 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 ---------------------------------------- .. code-block:: python Exercise(name="Bad Unit", muscle_id=1, unit="stone").save() .. code-block:: sql 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: .. code-block:: python 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**: .. code-block:: 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: .. code-block:: python 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: .. code-block:: console $ 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. .. code-block:: console $ 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``: .. code-block:: console $ 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 =============== .. list-table:: :header-rows: 1 :widths: auto * - 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 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) ================================================ .. code-block:: bash # 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 .. code-block:: python # 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.)*