.. include:: /global.rst .. _DjangoSQLite: *************************************** The Magic Behind Django and SQLite *************************************** *How a few lines of Python in* ``models.py`` *turn into tables, rows, bytes on a disk, and back into Python objects again.* When you write this in a Django project: .. code-block:: python season = Season.objects.filter(saved=True).first() print(season.cast[0]["name"]) you never open a file, never write SQL, and never convert a single value by hand. Yet the data comes out of a file called ``db.sqlite3`` and lands in your code as a real Python ``list`` of ``dict`` objects. That looks like magic. It isn't. It is a stack of very ordinary programs, each doing one job and handing the result to the next one. This page takes the "magic" apart one layer at a time, using the real files from the simulation game in the ``models`` folder: * ``models/models.py`` holds the two Django models, ``Season`` and ``Screen``. * ``models/db.sqlite3`` is the real SQLite database the game wrote (716,800 bytes, 2 seasons, 211 screens). * ``models/schema.txt`` is the output of ``.schema ranked_season`` from the ``sqlite3`` command-line shell. Every SQL statement on this page was **captured from Django 5.2.18 running against a copy of that database** with SQLite 3.40.1. Nothing is made up or simplified. When you make your videos, you can re-run the same commands and get the same output. .. contents:: On this page :local: :depth: 1 The Big Picture: Seven Layers ================================= Here is every layer a query passes through, from your code down to the disk, and back. Each section of this page zooms in on one or more of these layers. .. code-block:: text ┌───────────────────────────────────────────────────────────────────────┐ │ 1. YOUR CODE Season.objects.filter(saved=True).first() │ ├───────────────────────────────────────────────────────────────────────┤ │ 2. MODEL LAYER Model, ModelBase metaclass, _meta (Options), │ │ Fields, Manager, descriptors │ ├───────────────────────────────────────────────────────────────────────┤ │ 3. QUERY LAYER QuerySet -> Query (a tree) -> SQLCompiler │ │ produces: ("SELECT ... WHERE saved", params) │ ├───────────────────────────────────────────────────────────────────────┤ │ 4. BACKEND django.db.backends.sqlite3.DatabaseWrapper │ │ data_types, operators, quote_name, %s -> ? │ ├───────────────────────────────────────────────────────────────────────┤ │ 5. DB-API DRIVER Python's built-in sqlite3 module (PEP 249) │ │ a thin C wrapper around the SQLite library │ ├───────────────────────────────────────────────────────────────────────┤ │ 6. SQLITE ENGINE libsqlite3: SQL parser -> bytecode (VDBE) │ │ -> B-tree -> pager -> OS interface (VFS) │ ├───────────────────────────────────────────────────────────────────────┤ │ 7. OPERATING SYSTEM open(), read(), write(), fcntl() locks, fsync() │ │ on the file db.sqlite3 (4096-byte pages) │ └───────────────────────────────────────────────────────────────────────┘ Two things are worth noticing right away. **There is no database server.** MySQL and PostgreSQL run as separate *processes* (daemons) that your program talks to over a socket. SQLite is an *embedded database*: the whole database engine is a C library that is loaded into **your Python process**. When Django "talks to the database", it is really calling C functions in its own address space. Those functions then make *system calls* to read and write one ordinary file. **Django never touches the file directly.** Django only ever produces SQL text plus a list of parameters and gives them to the ``sqlite3`` driver. Everything below layer 4 is SQLite's job. Everything above layer 5 is Django's job. Magic #1: A Class Becomes a Description of a Table ================================================== Look at the start of ``models.py``: .. code-block:: python class Season(models.Model): user = models.ForeignKey(settings.AUTH_USER_MODEL, null=True, blank=True, on_delete=models.SET_NULL, related_name="seasons") name = models.CharField(max_length=100, blank=True) ... In normal Python, ``name = models.CharField(...)`` would just make a *class attribute* holding a ``CharField`` object. Then ``Season().name`` would return that ``CharField``, not a string. So why does ``season.name`` give you ``"Demo Season"``? The answer is a **metaclass**. ``models.Model`` is built by a metaclass called ``ModelBase`` (in ``django/db/models/base.py``). A metaclass is "the class of a class". When Python finishes reading the ``class Season`` block, it does not just build the class the normal way. It calls ``ModelBase.__new__()`` and hands it the class name, the base classes and a dictionary of everything defined in the body. ``ModelBase`` then rebuilds the class: 1. **It creates an** ``Options`` **object** and stores it as ``Season._meta``. This is the "description of the table" that every other part of Django reads. It holds the field list, the table name, the ordering, the constraints, the app label, and more. 2. **It reads the inner** ``class Meta`` (``ordering = ["-created"]``) and copies those settings into ``_meta``. The ``Meta`` class itself is then thrown away. 3. **It calls** ``contribute_to_class()`` **on every field.** Each field registers itself with ``_meta``, learns its own name (``"name"``), and works out its column name. For a ``ForeignKey`` the *attribute* is ``user`` but the *column* (``attname``) is ``user_id``. 4. **It adds a primary key** if you didn't declare one. ``Season`` has no ``id`` in ``models.py``, but ``ModelBase`` adds ``id = BigAutoField(primary_key=True)`` because the project's ``DEFAULT_AUTO_FIELD`` setting says so. 5. **It works out the table name**: ``app_label + "_" + model_name.lower()``. The app is called ``ranked``, so the table is ``ranked_season`` (and ``ranked_screen``). 6. **It adds a default manager** called ``objects`` (if you didn't define one). 7. **It creates two exception classes**, ``Season.DoesNotExist`` and ``Season.MultipleObjectsReturned``, which ``.get()`` raises. 8. **It registers the model** in the global app registry (``django.apps.apps``) so that ``makemigrations``, the admin and string references such as ``"ranked.Season"`` can find it. 9. **It wires up relationships.** The ``ForeignKey`` on ``Screen`` puts a *forward descriptor* on ``Screen.season`` and a *reverse descriptor* on ``Season.screens`` (the name comes from ``related_name="screens"``). In the same way, ``Season.user`` adds ``user.seasons`` to Django's built-in ``User`` model, even though you never edited that class. You can see the result in ``python manage.py shell``. This is real output: .. code-block:: pycon >>> type(Season).__name__ 'ModelBase' >>> Season._meta.db_table 'ranked_season' >>> Season._meta.pk >>> [f.attname for f in Season._meta.concrete_fields] ['id', 'user_id', 'name', 'player_count', 'colors', 'cast', 'seed', 'revealed', 'saved', 'result', 'created'] >>> type(Season.objects) >>> type(Season.screens) >>> type(Screen.season) Notice that ``user_id`` comes **second** in the column list even though ``user`` is the *first* field in ``models.py``. Django puts the auto-added ``id`` first. The rest of the order is the order the fields were *created*: every ``Field`` takes a number from a global ``creation_counter`` when it is built. That is why the SQL ``SELECT`` statements below always list ``"id", "user_id", "name", ...`` in that order. So the first "magic trick" is simple: **your class is turned into a data structure that describes a table.** Everything else on this page is code that reads ``_meta`` and acts on it. Magic #2: Each Field Becomes a Column ===================================== Here is the real schema of ``ranked_season`` from ``schema.txt``, split onto separate lines so it is easier to read: .. code-block:: sql CREATE TABLE IF NOT EXISTS "ranked_season" ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(100) NOT NULL, "player_count" integer unsigned NOT NULL CHECK ("player_count" >= 0), "colors" text NOT NULL CHECK ((JSON_VALID("colors") OR "colors" IS NULL)), "cast" text NOT NULL CHECK ((JSON_VALID("cast") OR "cast" IS NULL)), "seed" bigint NOT NULL, "revealed" integer unsigned NOT NULL CHECK ("revealed" >= 0), "saved" bool NOT NULL, "result" text NULL CHECK ((JSON_VALID("result") OR "result" IS NULL)), "created" datetime NOT NULL, "user_id" integer NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED ); CREATE INDEX "ranked_season_user_id_d59ea6db" ON "ranked_season" ("user_id"); .. note:: The words ``IF NOT EXISTS`` were **not** written by Django. Django ran a plain ``CREATE TABLE "ranked_season" (...)``. You can confirm this by running ``SELECT sql FROM sqlite_master WHERE name='ranked_season'``. The ``sqlite3`` shell adds ``IF NOT EXISTS`` when it prints ``.schema`` so that the output can be pasted back in safely. Where the column types come from --------------------------------- Every database backend has a dictionary called ``data_types`` that maps a Django field class to an SQL type. It lives in ``django/db/backends/sqlite3/base.py``. A second dictionary, ``data_type_check_constraints``, adds ``CHECK`` rules for some fields. The schema editor fills in the blanks (``%(max_length)s``, ``%(column)s``) for each field. .. list-table:: Field to column mapping for ``Season`` :header-rows: 1 :widths: 14 22 30 12 22 * - Field - Django class - SQL that Django writes - SQLite affinity - Python value you get back * - ``id`` - ``BigAutoField`` (added) - ``integer NOT NULL PRIMARY KEY AUTOINCREMENT`` - INTEGER - ``int`` * - ``name`` - ``CharField(max_length=100, blank=True)`` - ``varchar(100) NOT NULL`` - TEXT - ``str`` * - ``player_count`` - ``PositiveIntegerField`` - ``integer unsigned NOT NULL CHECK ("player_count" >= 0)`` - INTEGER - ``int`` * - ``colors``, ``cast`` - ``JSONField(default=list)`` - ``text NOT NULL CHECK ((JSON_VALID(...) OR ... IS NULL))`` - TEXT - ``list`` / ``dict`` (parsed JSON) * - ``seed`` - ``BigIntegerField`` - ``bigint NOT NULL`` - INTEGER - ``int`` * - ``saved`` - ``BooleanField(default=False)`` - ``bool NOT NULL`` - NUMERIC - ``bool`` * - ``result`` - ``JSONField(null=True, blank=True)`` - ``text NULL CHECK (...)`` - TEXT - ``dict`` or ``None`` * - ``created`` - ``DateTimeField(auto_now_add=True)`` - ``datetime NOT NULL`` - NUMERIC - ``datetime`` (timezone-aware, UTC) * - ``user`` - ``ForeignKey(User, null=True, on_delete=SET_NULL)`` - ``integer NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED`` - INTEGER - ``int`` in ``user_id``; a ``User`` object in ``user`` SQLite's secret: type affinity --------------------------------- Most databases are strict about types. If a column is ``VARCHAR(100)``, a 101-character string is refused. **SQLite is different.** It uses *dynamic typing*: the type belongs to each **value**, not to the column. The type you write in ``CREATE TABLE`` only gives the column a *type affinity*, which is a preference for how to store values. SQLite picks the affinity by looking for keywords inside the type name, in this order: 1. Contains ``INT`` → **INTEGER** affinity. (``integer``, ``integer unsigned``, ``bigint``) 2. Contains ``CHAR``, ``CLOB`` or ``TEXT`` → **TEXT** affinity. (``varchar(100)``, ``text``) 3. Contains ``BLOB`` or no type at all → **BLOB** affinity. 4. Contains ``REAL``, ``FLOA`` or ``DOUB`` → **REAL** affinity. 5. Anything else → **NUMERIC** affinity. (``bool``, ``datetime``) That has some surprising results, and you can show every one of them in a video: * ``varchar(100)`` **does not limit length.** The ``(100)`` is ignored. We stored a 500-character name in ``ranked_season`` and read back all 500 characters. Django only checks ``max_length`` when you call ``full_clean()`` or use a ``ModelForm``. A plain ``.save()`` will not stop you. * ``unsigned`` **means nothing to SQLite.** It is just an extra word in the type name. That is exactly why Django *also* adds ``CHECK ("player_count" >= 0)``. The CHECK is what actually blocks negative numbers: .. code-block:: pycon >>> Season.objects.create(name="neg", player_count=-1, seed=1) django.db.utils.IntegrityError: CHECK constraint failed: player_count * ``bool`` **is stored as an integer.** SQLite has no Boolean type. ``True`` is stored as ``1`` and ``False`` as ``0``. * ``datetime`` **is stored as text.** SQLite has no date type. Django writes an ISO-8601 string such as ``'2026-10-14 12:09:40.427655'``, always in UTC when ``USE_TZ = True``. The text sorts correctly as a string, so ``ORDER BY "created"`` still works. You can ask SQLite what type each stored value *really* has with the ``typeof()`` function: .. code-block:: sql sqlite> SELECT typeof(saved), typeof(created), typeof(colors), typeof(seed) ...> FROM ranked_season LIMIT 1; integer|text|text|integer ``id``: the rowid and AUTOINCREMENT --------------------------------------- In SQLite every ordinary table already has a hidden 64-bit integer key called the **rowid**. The table's data is stored in a B-tree sorted by that rowid. A column declared exactly as ``INTEGER PRIMARY KEY`` becomes an **alias** for the rowid, so ``id`` costs no extra space and looking up a row by ``id`` is the fastest search SQLite can do. ``AUTOINCREMENT`` adds one more promise: an ``id`` is **never reused**, even after the row with the largest ``id`` is deleted. To keep that promise, SQLite records the highest ``id`` ever handed out in a special table called ``sqlite_sequence``: .. code-block:: sql sqlite> SELECT * FROM sqlite_sequence WHERE name LIKE 'ranked%'; ranked_season|2 ranked_screen|211 ``BigAutoField`` and ``AutoField`` both become ``integer`` on SQLite, because every SQLite integer is already 64 bits. There is one place where you can still see the difference. A *foreign key* that points to a ``BigAutoField`` is written as ``bigint``. A foreign key that points to an ``AutoField`` is written as ``integer``. In this database: * ``ranked_screen.season_id`` is ``bigint``, because ``Season.id`` is a ``BigAutoField`` (from ``DEFAULT_AUTO_FIELD``). * ``ranked_season.user_id`` is ``integer``, because Django's own ``auth`` app still uses the older ``AutoField`` for ``User.id``. The schema is a fingerprint of the settings that created it. Why every name is in double quotes --------------------------------------- ``Season`` has a field called ``cast``. ``Screen`` has fields called ``index`` and ``order``. ``CAST``, ``INDEX`` and ``ORDER`` are all **reserved words** in SQL. Without quotes, ``SELECT order FROM ranked_screen`` would be a syntax error. Django avoids the problem by wrapping *every* table and column name in double quotes through ``connection.ops.quote_name()``: .. code-block:: python # django/db/backends/sqlite3/operations.py def quote_name(self, name): if name.startswith('"') and name.endswith('"'): return name # Quoting once is enough. return '"%s"' % name That is why you can name a field ``order`` in Django. If you write raw SQL yourself, you have to remember the quotes. JSONField: text plus a CHECK --------------------------------- SQLite has no JSON column type, so a ``JSONField`` is stored as plain ``text``. To make sure only valid JSON gets in, Django adds ``CHECK ((JSON_VALID("colors") OR "colors" IS NULL))``. ``JSON_VALID`` comes from SQLite's built-in JSON functions (they used to be called the JSON1 extension). If someone goes around Django and inserts bad data with raw SQL, the database itself refuses it: .. code-block:: text IntegrityError: CHECK constraint failed: (JSON_VALID("colors") OR "colors" IS NULL) The ``OR "colors" IS NULL`` part is there even though the column is ``NOT NULL``. Django uses the *same* CHECK template for every ``JSONField``. The ``NOT NULL`` rule blocks ``NULL`` anyway, so the extra test does no harm. The foreign key and its index --------------------------------- .. code-block:: sql "user_id" integer NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED * ``REFERENCES`` makes SQLite refuse a ``user_id`` that doesn't exist in ``auth_user``. SQLite only enforces this when ``PRAGMA foreign_keys = ON`` is set, and Django sets it every time it opens a connection (see Magic #4). * ``DEFERRABLE INITIALLY DEFERRED`` tells SQLite to check the rule at ``COMMIT`` instead of after every single statement. That lets Django insert rows in any order inside a transaction, which matters when loading fixtures or running migrations. * There is **no** ``ON DELETE SET NULL`` clause, even though the model says ``on_delete=models.SET_NULL``. Django carries out ``on_delete`` itself, in Python (see Magic #8). Django also adds an index on every foreign key column, so "all seasons for this user" is fast. The odd name ``ranked_season_user_id_d59ea6db`` is made of the table name, the column name and the first 8 hex digits of an MD5 hash of both. The hash keeps names unique and short enough for databases with name length limits: .. code-block:: pycon >>> from django.db.backends.utils import names_digest >>> names_digest("ranked_season", "user_id", length=8) 'd59ea6db' The UniqueConstraint on Screen --------------------------------- ``Screen.Meta`` has ``UniqueConstraint(fields=["season", "index"], name="unique_screen_index")``. It turns into this clause at the end of ``CREATE TABLE "ranked_screen"``: .. code-block:: sql CONSTRAINT "unique_screen_index" UNIQUE ("season_id", "index") SQLite enforces ``UNIQUE`` by quietly building an index, here called ``sqlite_autoindex_ranked_screen_1``. Because that index *starts* with ``season_id``, SQLite also uses it for "all screens of season 1" queries: .. code-block:: text sqlite> EXPLAIN QUERY PLAN ...> SELECT * FROM ranked_screen WHERE season_id = 1 ORDER BY "index"; `--SEARCH ranked_screen USING INDEX sqlite_autoindex_ranked_screen_1 (season_id=?) The index also covers the ``ORDER BY "index"`` (which comes from ``Meta.ordering``), so no separate sort step is needed. ``SEARCH`` means SQLite jumped straight to the right rows through a B-tree. Compare that with a column that has no index: .. code-block:: text sqlite> EXPLAIN QUERY PLAN SELECT * FROM ranked_season WHERE name = 'x'; `--SCAN ranked_season ``SCAN`` means SQLite reads every row. That is fine for 2 seasons. It would be slow for 2 million. What is **not** in the database --------------------------------- This is one of the most important ideas on the page. A lot of what you wrote in ``models.py`` **never reaches SQLite at all**. It lives only in Python, inside ``_meta``: .. list-table:: :header-rows: 1 :widths: 35 65 * - In ``models.py`` - What really happens * - ``default=list``, ``default=0``, ``default=False`` - There is **no** ``DEFAULT`` clause in the table. Django calls ``list()`` and fills in the value in Python before every ``INSERT``. A raw SQL ``INSERT`` without those columns would fail with ``NOT NULL constraint failed``. * - ``auto_now_add=True`` - Django sets ``created = timezone.now()`` in Python, inside ``pre_save()``. * - ``blank=True`` - Only affects validation (forms, admin, ``full_clean()``). ``name`` is still ``NOT NULL``. An empty name is stored as ``''``, not ``NULL``. * - ``max_length=100`` - Written into the type as ``varchar(100)``, but SQLite ignores the number. * - ``Meta.ordering`` - Added as ``ORDER BY`` to queries at run time. * - ``related_name`` - Only the Python attribute name (``season.screens``, ``user.seasons``). * - ``on_delete`` - Carried out by Django's ``Collector`` in Python. * - ``__str__``, ``display_name`` - Plain Python methods. They have nothing to do with the database. Magic #3: Migrations Write the SQL for You ========================================== Nobody typed the ``CREATE TABLE`` statement above. It was written by Django's **migrations** system in two steps. Step 1: ``makemigrations`` writes a plan ------------------------------------------ ``python manage.py makemigrations`` does not touch the database. It: 1. Loads every existing migration file and *replays* them in memory to build a ``ProjectState``. This is what Django *thinks* the database looks like. 2. Builds a second state from your current ``models.py``. 3. Runs the **autodetector** to compare the two states and lists the differences as operations (``CreateModel``, ``AddField``, ``AlterField``, ...). 4. Writes those operations to a new Python file. For the ``ranked`` app the first file is ``ranked/migrations/0001_initial.py``. Here is a shortened version of it: .. code-block:: python class Migration(migrations.Migration): initial = True dependencies = [migrations.swappable_dependency(settings.AUTH_USER_MODEL)] operations = [ migrations.CreateModel( name='Season', fields=[ ('id', models.BigAutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')), ('name', models.CharField(blank=True, max_length=100)), ('player_count', models.PositiveIntegerField()), ('colors', models.JSONField(default=list)), # ... cast, seed, revealed, saved, result, created ... ('user', models.ForeignKey(blank=True, null=True, on_delete=django.db.models.deletion.SET_NULL, related_name='seasons', to=settings.AUTH_USER_MODEL)), ], options={'ordering': ['-created']}, ), migrations.CreateModel(name='Screen', ...), ] Notice the ``dependencies`` line. The ``Season`` table points at ``auth_user``, so the ``auth`` app's migrations **must** run first. Django builds a dependency graph of all migrations and runs them in an order that respects it. Step 2: ``migrate`` runs the plan --------------------------------- ``python manage.py migrate``: 1. Reads the ``django_migrations`` table to find out what has already been applied. 2. Works out which migrations are still missing. 3. Opens a **schema editor**, ``django.db.backends.sqlite3.schema.DatabaseSchemaEditor``. It turns each operation into SQL using ``data_types``, ``quote_name`` and so on. 4. Runs the SQL inside a transaction. ``CREATE INDEX`` statements are saved as *deferred SQL* and run after all the tables exist. 5. Inserts a row into ``django_migrations`` to record that the migration ran. You can see the exact SQL for any migration without running it, using ``python manage.py sqlmigrate ranked 0001``. Its output matches ``schema.txt`` exactly, hash and all: .. code-block:: sql BEGIN; -- -- Create model Season -- CREATE TABLE "ranked_season" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, ...); -- -- Create model Screen -- CREATE TABLE "ranked_screen" (..., CONSTRAINT "unique_screen_index" UNIQUE ("season_id", "index")); CREATE INDEX "ranked_season_user_id_d59ea6db" ON "ranked_season" ("user_id"); CREATE INDEX "ranked_screen_season_id_c88713d6" ON "ranked_screen" ("season_id"); COMMIT; The real ``db.sqlite3`` tells the story of the day it was created. ``django_migrations`` has 19 rows. The ``ranked`` app's migration ran 0.016 seconds after the last ``auth`` migration: .. code-block:: text sqlite> SELECT app, name, applied FROM django_migrations ORDER BY id; contenttypes|0001_initial|2026-10-14 12:09:39.898769 auth|0001_initial|2026-10-14 12:09:39.915445 ... auth|0012_alter_user_first_name_max_length|2026-10-14 12:09:40.057761 ranked|0001_initial|2026-10-14 12:09:40.076070 sessions|0001_initial|2026-10-14 12:09:40.083807 The database also holds tables you never wrote models for: ``auth_user``, ``auth_permission``, ``django_content_type``, ``django_session`` and others. They come from the apps in ``INSTALLED_APPS``. Those apps are made of models and migrations too, just like yours. SQLite's weak ALTER TABLE and the "table remake" ------------------------------------------------ SQLite's ``ALTER TABLE`` can only do a few things: rename a table, rename a column, add a column, and drop a column (with limits). It **cannot** change a column's type, its ``NOT NULL`` rule or its constraints. So how does Django change ``max_length=100`` to ``max_length=120``? It **rebuilds the whole table**. This is what ``sqlmigrate`` shows for that change: .. code-block:: sql CREATE TABLE "new__ranked_season" ("id" integer ..., "name" varchar(120) NOT NULL, ...); INSERT INTO "new__ranked_season" ("id", "player_count", ..., "name") SELECT "id", "player_count", ..., "name" FROM "ranked_season"; DROP TABLE "ranked_season"; ALTER TABLE "new__ranked_season" RENAME TO "ranked_season"; CREATE INDEX "ranked_season_user_id_d59ea6db" ON "ranked_season" ("user_id"); Django creates a new table with the correct definition, copies every row into it, drops the old table, renames the new one, and rebuilds the indexes. While this happens the schema editor turns foreign key checking off (``PRAGMA foreign_keys = OFF``), because for a moment ``ranked_screen`` points at a table that doesn't exist. Afterwards it runs ``PRAGMA foreign_key_check`` to make sure nothing broke. On a big table a "small" change can take a long time, because every row is copied. Magic #4: Opening the Connection ================================ Your ``settings.py`` has something like this: .. code-block:: python DATABASES = { "default": { "ENGINE": "django.db.backends.sqlite3", "NAME": BASE_DIR / "db.sqlite3", } } ``ENGINE`` is just a Python import path. The first time any code touches ``django.db.connection``, Django imports ``django.db.backends.sqlite3.base`` and builds its ``DatabaseWrapper`` class. That is the **backend**, and it is the only part of Django that knows anything about SQLite. Swap the ``ENGINE`` string for PostgreSQL and every layer above it stays exactly the same. Connections are **lazy** (nothing opens until the first query) and there is **one per thread**. When a connection is needed, ``get_new_connection()`` runs: 1. ``get_connection_params()`` builds the arguments for ``sqlite3.connect()``. These are the real values Django used: .. code-block:: python {'database': 'db.sqlite3', 'detect_types': 3, # PARSE_DECLTYPES | PARSE_COLNAMES 'check_same_thread': False, 'uri': True} 2. ``sqlite3.connect(**params)`` opens the file. Under the hood, the C library calls the operating system's ``open()`` system call and gets back a file descriptor. 3. Django **registers its own SQL functions** in SQLite: ``django_date_extract``, ``django_date_trunc``, ``REGEXP``, math functions and others. SQLite lets a program add functions written in the host language, so these are Python functions that SQLite calls back during a query. That is how ``created__year`` or ``__regex`` work even though SQLite has no built-in support for them. 4. It runs ``PRAGMA foreign_keys = ON``. SQLite turns foreign keys **off** by default for historical reasons, so without this line the ``REFERENCES`` clauses would do nothing. 5. It runs ``PRAGMA legacy_alter_table = OFF``, which makes table renames update references correctly. 6. It runs any ``init_command`` from ``OPTIONS`` (new in Django 5.1). 7. It sets ``isolation_level = None`` on the Python connection. This puts the driver in **autocommit** mode. Django then manages transactions itself (see Magic #7). Converters and adapters: the type translators ---------------------------------------------- At import time, the SQLite backend registers **converters** and **adapters** with the ``sqlite3`` driver: .. code-block:: python # django/db/backends/sqlite3/base.py Database.register_converter("bool", b"1".__eq__) Database.register_converter("datetime", decoder(parse_datetime)) Database.register_converter("date", decoder(parse_date)) Database.register_converter("time", decoder(parse_time)) Database.register_adapter(decimal.Decimal, str) Database.register_adapter(datetime.datetime, adapt_datetime) Because ``detect_types`` includes ``PARSE_DECLTYPES``, the driver looks at the **declared type** of each column in ``CREATE TABLE``. When a value comes out of a column declared ``bool``, it runs the ``"bool"`` converter (``b"1" == value``) and you get ``True`` instead of ``1``. That is the real reason Django writes ``bool`` and ``datetime`` as type names, even though SQLite gives them no special meaning: **the names are labels for the converters.** ``check_same_thread=False`` ------------------------------- By default Python's ``sqlite3`` raises an error if a connection created in one thread is used in another. Django turns that check off and enforces its own rule: one connection per thread, kept in a thread-local ``connections`` object. Magic #5: QuerySets Are Lazy Recipes ==================================== ``Season.objects`` is a ``Manager``. Calling ``.filter()`` on it gives back a ``QuerySet``. A ``QuerySet`` **does not run anything**. It is a recipe that holds a ``Query`` object, and the ``Query`` holds a *tree* that describes the SQL: which table, which ``WHERE`` nodes, which joins, ordering and limits. .. code-block:: pycon >>> qs = Season.objects.filter(saved=True) # no SQL runs here >>> type(qs), type(qs.query) (, ) >>> qs = qs.filter(player_count__gte=10) # still no SQL; a new QuerySet is >>> # cloned with one more WHERE node Every ``.filter()``, ``.exclude()``, ``.order_by()`` or ``.select_related()`` **clones** the ``QuerySet`` and changes the copy. That is why you can chain them and keep partial queries in variables. When does the SQL actually run? ------------------------------- A ``QuerySet`` is **evaluated** (the SQL runs) only when you ask for its data: * iterating it: ``for s in qs:`` * ``list(qs)``, ``len(qs)``, ``bool(qs)``, ``if qs:`` * slicing with a step, or indexing: ``qs[0]`` * ``repr(qs)``, which is what the interactive shell does when it prints a result * methods that need an answer right away: ``.get()``, ``.first()``, ``.count()``, ``.exists()``, ``.aggregate()``, ``.update()``, ``.delete()`` While we were collecting the SQL for this page, one example "disappeared". The code was: .. code-block:: python with CaptureQueriesContext(connection) as c: r = Screen.objects.values("kind").annotate(n=Count("id")) print(r) # <- the SQL runs HERE, after capturing has stopped Building the ``QuerySet`` ran no SQL, so nothing was captured. The query only ran later, when ``print()`` called ``repr()``. That is laziness in action, and it would make a good gotcha for a video. Once evaluated, the rows are kept in ``qs._result_cache``. Looping over the same ``QuerySet`` a second time does **not** query again. From Query tree to SQL string: the compiler ------------------------------------------- When it is time to run, Django asks the ``Query`` for a **compiler** (``django/db/models/sql/compiler.py``). ``SQLCompiler.as_sql()`` walks the tree and returns **two things**: a SQL string with ``%s`` placeholders and a separate tuple of parameters. .. code-block:: pycon >>> Season.objects.filter(saved=True).query.get_compiler("default").as_sql() ('SELECT "ranked_season"."id", "ranked_season"."user_id", "ranked_season"."name", "ranked_season"."player_count", "ranked_season"."colors", "ranked_season"."cast", "ranked_season"."seed", "ranked_season"."revealed", "ranked_season"."saved", "ranked_season"."result", "ranked_season"."created" FROM "ranked_season" WHERE "ranked_season"."saved" ORDER BY "ranked_season"."created" DESC', ()) Look at what the compiler did on its own: * It listed **every column by name** instead of using ``SELECT *``, so the order of the values always matches ``_meta``. * It wrote ``WHERE "ranked_season"."saved"`` instead of ``= 1``. The SQLite backend knows that a Boolean column can be used directly as a condition. * It added ``ORDER BY ... DESC`` because ``Meta.ordering = ["-created"]``. The ``-`` means descending. How a lookup like ``name__icontains`` becomes SQL --------------------------------------------------- The double underscore splits a keyword into a **field** and a **lookup**: ``name__icontains="demo"`` means "field ``name``, lookup ``icontains``". Each backend has an ``operators`` dictionary that turns lookups into SQL. In ``django/db/backends/sqlite3/base.py``: .. code-block:: python operators = { "exact": "= %s", "iexact": "LIKE %s ESCAPE '\\'", "contains": "LIKE %s ESCAPE '\\'", "icontains": "LIKE %s ESCAPE '\\'", "regex": "REGEXP %s", ... } Real captured SQL for ``Season.objects.filter(name__icontains="demo").exists()``: .. code-block:: sql SELECT 1 AS "a" FROM "ranked_season" WHERE "ranked_season"."name" LIKE '%demo%' ESCAPE '\' LIMIT 1 ``.exists()`` doesn't need any data, so it selects the constant ``1`` and stops after one row. ``icontains`` turned into ``LIKE`` because SQLite's ``LIKE`` already ignores case for ASCII letters. Some lookups are more involved. Here is ``Season.objects.filter(created__year=2026)``: .. code-block:: sql SELECT COUNT(*) AS "__count" FROM "ranked_season" WHERE "ranked_season"."created" BETWEEN '2026-01-01 06:00:00' AND '2027-01-01 05:59:59.999999' Django did **not** use a "year" function. It turned "the year 2026" into a range of timestamps, because a range can use an index and a function call can't. The range starts at ``06:00`` and not ``00:00`` because the demo used Django's default ``TIME_ZONE = "America/Chicago"``. Midnight on 1 January in Chicago is 06:00 UTC, and the column stores UTC. Parameters: %s, ?, and SQL injection ------------------------------------- The SQL that ``print(qs.query)`` shows has the values pasted in. **That is not what is sent to SQLite.** It is only a readable preview. What really happens: 1. The compiler gives back SQL containing ``%s`` placeholders and a separate list of values. 2. Django's ``SQLiteCursorWrapper.convert_query()`` changes every ``%s`` into ``?``, because that is the placeholder style (``qmark``) the ``sqlite3`` driver expects. 3. The driver calls ``sqlite3_prepare_v2()``, which **compiles the SQL into SQLite bytecode** before any user value is involved. 4. Then each value is attached to its ``?`` slot with ``sqlite3_bind_*()``. Because the SQL is compiled *before* the values are added, a value can never change the meaning of the SQL. A player named ``'; DROP TABLE ranked_season; --`` is stored as an odd but harmless name. This is called a **parameterized query**, and it is the reason Django code is protected from SQL injection by default. ``.raw()`` and ``.extra()`` let you write SQL yourself, and with them it is your job to use parameters. From rows back to objects --------------------------- SQLite returns plain tuples such as ``(1, None, 'Demo Season', 20, '["#CD4D66", ...]', ...)``. To build a ``Season`` from one: 1. The ``sqlite3`` driver has already run its converters: ``bool`` columns are now ``True``/``False`` and ``datetime`` columns are ``datetime`` objects. 2. Django runs its own **field converters**. The backend's ``get_db_converters()`` adds a step that makes datetimes *timezone-aware* (UTC). ``JSONField.from_db_value()`` calls ``json.loads()`` on the text. 3. ``Season.from_db()`` builds the instance by passing the values to the constructor in column order (no validation runs, and field defaults are not used). It then sets ``_state.adding = False`` (this row already exists in the database) and ``_state.db = "default"``. Real result: .. code-block:: pycon >>> s = Season.objects.first() >>> type(s.colors), type(s.saved), type(s.created) (, , ) >>> s.created datetime.datetime(2026, 10, 14, 12, 18, 18, 723768, tzinfo=datetime.timezone.utc) .. note:: ``.get()`` adds ``LIMIT 21`` to its SQL (``MAX_GET_RESULTS = 21`` in ``django/db/models/query.py``). It wants exactly one row. If it gets more than one, it raises ``MultipleObjectsReturned`` and can tell you "more than 20" without loading a million rows. Magic #6: Querying Inside JSON ============================== The game stores most of its state as JSON. Each ``Screen.data`` is a whole snapshot. In this database the 211 screens hold 446,640 bytes of JSON text. You can still filter on values *inside* the JSON with the same double-underscore syntax: .. code-block:: python Screen.objects.filter(data__budget=4).count() # 5 Season.objects.filter(result__winner=15).first() # Demo Season Each extra ``__key`` becomes a **KeyTransform**, which turns into SQLite's JSON functions. Real SQL for ``data__budget=4``: .. code-block:: sql SELECT COUNT(*) AS "__count" FROM "ranked_screen" WHERE (CASE WHEN JSON_TYPE("ranked_screen"."data", '$."budget"') IN ('true','false','null') THEN JSON_TYPE("ranked_screen"."data", '$."budget"') ELSE JSON_EXTRACT("ranked_screen"."data", '$."budget"') END) = JSON_EXTRACT('4', '$') * ``'$."budget"'`` is a **JSON path**. ``$`` is the root of the document, and ``."budget"`` is the key. ``data__players__0__rank`` would become ``'$."players"[0]."rank"'``. * The ``CASE`` exists because ``JSON_EXTRACT`` turns JSON ``true``/``false`` into SQL ``1``/``0``. Django wants ``true`` and ``1`` to stay different, so for those types it compares the type names instead. * The right side is also passed through ``JSON_EXTRACT('4', '$')`` so that both sides are compared as JSON values, not as text. **The trade-off:** SQLite has to parse the JSON text of every row to answer this query. No ordinary index can help, so it is always a full ``SCAN``. Storing game state as JSON is flexible and easy, but anything you search on often should be its own column, like ``round`` and ``kind`` are. Magic #7: Relationships Are Descriptors ======================================= ``screen.season`` looks like a normal attribute, but ``Screen.season`` is a **descriptor**: an object with a ``__get__`` method. Python calls that method every time you read the attribute. That is how Django can run a query the moment you touch the attribute. The forward side: ``screen.season`` ----------------------------------- .. code-block:: python Screen.objects.get(pk=5).season.name Two queries run (real output): .. code-block:: sql SELECT "ranked_screen"."id", "ranked_screen"."season_id", ... FROM "ranked_screen" WHERE "ranked_screen"."id" = 5 LIMIT 21 SELECT "ranked_season"."id", ... FROM "ranked_season" WHERE "ranked_season"."id" = 1 LIMIT 21 The first query loaded only the number ``season_id = 1``. When ``.season`` was read, ``ForwardManyToOneDescriptor.__get__`` saw that no ``Season`` was cached yet. It ran the second query and saved the result in ``screen._state.fields_cache``, so reading ``.season`` again costs nothing. The reverse side: ``season.screens`` ------------------------------------- ``season.screens`` gives back a ``RelatedManager``. It is a manager that already has ``WHERE season_id = `` built in: .. code-block:: sql -- season.screens.count() SELECT COUNT(*) AS "__count" FROM "ranked_screen" WHERE "ranked_screen"."season_id" = 2 -- season.screens.filter(kind="alliances")[:2] SELECT ... FROM "ranked_screen" WHERE ("ranked_screen"."season_id" = 2 AND "ranked_screen"."kind" = 'alliances') ORDER BY "ranked_screen"."index" ASC LIMIT 2 The slice ``[:2]`` became ``LIMIT 2``. Slicing a ``QuerySet`` doesn't load everything and then cut it. The slice is turned into SQL. The N+1 problem, and two ways to fix it ---------------------------------------- Descriptors are handy but risky. This loop: .. code-block:: python for screen in Screen.objects.all(): # 1 query print(screen.season.name) # +1 query for EVERY screen runs **212 queries** for 211 screens. This is the famous **N+1 query problem**. Django has two cures. ``select_related`` uses a SQL **JOIN**, so there is still only one query: .. code-block:: sql -- Screen.objects.select_related("season") SELECT "ranked_screen"."id", ..., "ranked_season"."id", "ranked_season"."user_id", ... FROM "ranked_screen" INNER JOIN "ranked_season" ON ("ranked_screen"."season_id" = "ranked_season"."id") ORDER BY "ranked_screen"."index" ASC ``prefetch_related`` runs a **second query** with ``IN (...)`` and matches the rows up in Python. It is used for the "many" side, where a JOIN would repeat the parent row many times: .. code-block:: sql -- Season.objects.prefetch_related("screens") SELECT ... FROM "ranked_season" ORDER BY "ranked_season"."created" DESC SELECT ... FROM "ranked_screen" WHERE "ranked_screen"."season_id" IN (2, 1) ORDER BY "ranked_screen"."index" ASC That is 2 queries no matter how many seasons there are, instead of 1 + N. Aggregation follows relationships too. ``Season.objects.values("player_count") .annotate(n=Count("screens"))`` becomes: .. code-block:: sql SELECT "ranked_season"."player_count" AS "player_count", COUNT("ranked_screen"."id") AS "n" FROM "ranked_season" LEFT OUTER JOIN "ranked_screen" ON ("ranked_season"."id" = "ranked_screen"."season_id") GROUP BY 1 Django picked ``LEFT OUTER JOIN`` on purpose, so a season with zero screens still shows up with ``n = 0``. Magic #8: Saving, Updating, Deleting, and Transactions ======================================================= What ``save()`` really does --------------------------- Here is the real SQL from this code: .. code-block:: python with transaction.atomic(): n = Season.objects.create(name="Video Demo", player_count=3, seed=42, colors=["#fff"], cast=[]) Screen.objects.create(season=n, index=0, round=1, label="R1", kind="x", title="t") with transaction.atomic(): n.revealed = 1 n.save(update_fields=["revealed"]) n.save() .. code-block:: sql BEGIN INSERT INTO "ranked_season" ("user_id", "name", "player_count", "colors", "cast", "seed", "revealed", "saved", "result", "created") VALUES (NULL, 'Video Demo', 3, '["#fff"]', '[]', 42, 0, 0, NULL, '2026-10-15 17:22:50.186908') RETURNING "ranked_season"."id" INSERT INTO "ranked_screen" ("season_id", "index", "round", "label", "kind", "title", "order", "ranked", "data") VALUES (3, 0, 1, 'R1', 'x', 't', '[]', 0, '{}') RETURNING "ranked_screen"."id" SAVEPOINT "s124117736415936_x1" UPDATE "ranked_season" SET "revealed" = 1 WHERE "ranked_season"."id" = 3 RELEASE SAVEPOINT "s124117736415936_x1" UPDATE "ranked_season" SET "user_id" = NULL, "name" = 'Video Demo', "player_count" = 3, "colors" = '["#fff"]', "cast" = '[]', "seed" = 42, "revealed" = 1, "saved" = 0, "result" = NULL, "created" = '2026-10-15 17:22:50.186908' WHERE "ranked_season"."id" = 3 COMMIT Step by step, here is what ``save()`` did for the first ``INSERT``: 1. It sent the ``pre_save`` **signal**, so any listeners could react. 2. It called each field's ``pre_save()``. ``DateTimeField(auto_now_add=True)`` used this step to set ``created`` to the current time. 3. The **defaults** were already filled in when the object was built: ``revealed = 0``, ``saved = False``, ``result = None``. ``colors`` and ``cast`` were given directly here. 4. It called each field's ``get_db_prep_save()``. This is the opposite of ``from_db_value()``: ``JSONField`` runs ``json.dumps(["#fff"])`` and gets ``'["#fff"]'``. The driver's adapters then turn ``False`` into ``0`` and the ``datetime`` into text. 5. ``_state.adding`` was ``True``, so it ran an ``INSERT`` and not an ``UPDATE``. 6. ``RETURNING "ranked_season"."id"`` (supported since SQLite 3.35) gives back the new ``id`` in the same round trip. Django puts it into ``n.id``. 7. It sent the ``post_save`` signal. The rest of the output shows three more ideas: * ``Screen.objects.create(season=n, ...)`` wrote ``season_id = 3``. Django read the primary key off the ``Season`` object. * ``save(update_fields=["revealed"])`` updated **one** column. A plain ``save()`` (the last ``UPDATE``) rewrote **every** column. If two players saved the same season at the same time, the second plain ``save()`` would silently undo the first one's changes. * The inner ``atomic()`` became a ``SAVEPOINT``. If code inside it raised an error, only that part would be rolled back (``ROLLBACK TO SAVEPOINT``), not the whole transaction. Updating in the database with ``update()`` and ``F()`` ------------------------------------------------------ .. code-block:: python Season.objects.filter(pk=3).update(revealed=F("revealed") + 1) .. code-block:: sql UPDATE "ranked_season" SET "revealed" = ("ranked_season"."revealed" + 1) WHERE "ranked_season"."id" = 3 No ``SELECT`` runs and no object is created. An **F expression** refers to the column's *current value inside the database*. The math happens in SQLite in one atomic statement, so two requests that run at the same moment can't both read 5 and both write 6. Note that ``QuerySet.update()`` does **not** call ``save()`` and does **not** send signals. Deleting: ``on_delete`` happens in Python ------------------------------------------ The table has no ``ON DELETE CASCADE``, but deleting a season still removes its screens. Here is the real SQL for ``season.delete()``: .. code-block:: sql BEGIN DELETE FROM "ranked_screen" WHERE "ranked_screen"."season_id" IN (3) DELETE FROM "ranked_season" WHERE "ranked_season"."id" IN (3) COMMIT Django's ``Collector`` (``django/db/models/deletion.py``) walks every relationship that points at ``Season``, looks up each ``on_delete`` rule, and builds a delete plan. Children are removed first, then the parent, all in one transaction. The method returns a summary: .. code-block:: pycon (2, {'ranked.Screen': 1, 'ranked.Season': 1}) ``SET_NULL`` works the same way. When a ``User`` is deleted, Django runs an ``UPDATE`` **before** it deletes the user: .. code-block:: sql BEGIN DELETE FROM "auth_user_groups" WHERE "auth_user_groups"."user_id" IN (1) DELETE FROM "auth_user_user_permissions" WHERE "auth_user_user_permissions"."user_id" IN (1) UPDATE "ranked_season" SET "user_id" = NULL WHERE "ranked_season"."user_id" IN (1) DELETE FROM "auth_user" WHERE "auth_user"."id" IN (1) COMMIT Django handles this in Python for two reasons. ``on_delete`` has options that SQL doesn't have (``PROTECT``, ``RESTRICT``, ``SET()``, ``DO_NOTHING``), and Django wants to send ``pre_delete``/``post_delete`` signals for every object it removes. The SQL foreign key is still there as a **safety net**: if anything is left pointing at a missing row when ``COMMIT`` runs, SQLite refuses to commit. Transactions and autocommit --------------------------- Django runs in **autocommit** mode. Outside ``atomic()``, every statement is its own transaction and is saved right away. ``transaction.atomic()`` changes that: * The outermost ``atomic()`` sends ``BEGIN``. Leaving it normally sends ``COMMIT``. Leaving it with an exception sends ``ROLLBACK``. * A nested ``atomic()`` sends ``SAVEPOINT name`` and then ``RELEASE SAVEPOINT`` or ``ROLLBACK TO SAVEPOINT``. ``delete()`` uses its own ``atomic()`` internally. That is where the ``BEGIN``/``COMMIT`` around the deletes came from. Magic #9: Down to the Bytes on Disk =================================== Below the ``sqlite3`` driver, SQLite itself takes over. Inside the C library, a query goes through these stages: #. The **tokenizer and parser** read the SQL text. #. The **code generator** turns it into a program for SQLite's virtual machine, the **VDBE** (Virtual DataBase Engine). You can see this program with ``EXPLAIN SELECT ...``. #. The **B-tree** module finds rows and index entries. #. The **pager** reads and writes fixed-size **pages**, keeps a cache of them, and handles the journal and locking. #. The **VFS** (Virtual File System) layer makes the actual operating system calls. The file header --------------- The first 100 bytes of ``db.sqlite3`` are the header. Here they are from the real file (``od -A d -t x1 -N 100 db.sqlite3``): .. code-block:: text 0000000 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3. 0000016 10 00 01 01 00 40 20 20 00 00 00 42 00 00 00 af 0000032 00 00 00 00 00 00 00 00 00 00 00 3e 00 00 00 04 0000048 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 ... 0000096 00 2e 63 01 .. list-table:: Decoding the header :header-rows: 1 :widths: 18 18 64 * - Offset - Bytes - Meaning * - 0 - ``SQLite format 3\0`` - The "magic string" that marks every SQLite 3 file. * - 16 - ``10 00`` - Page size = ``0x1000`` = **4096 bytes**, the same as a typical OS memory page and disk block. * - 18, 19 - ``01 01`` - Read and write version 1 = **rollback journal** mode. (It would be 2 for WAL.) * - 24 - ``00 00 00 42`` - File change counter = 66. It goes up on every committed write. * - 28 - ``00 00 00 af`` - Database size = **175 pages**. 175 × 4096 = **716,800 bytes**, which is exactly the size of ``db.sqlite3``. * - 40 - ``00 00 00 3e`` - Schema cookie = 62. It goes up whenever the schema changes, so other connections know to re-read it. * - 44 - ``00 00 00 04`` - Schema format 4, the current format. * - 56 - ``00 00 00 01`` - Text encoding 1 = **UTF-8**. * - 96 - ``00 2e 63 01`` - ``0x002e6301`` = 3040001 = written by **SQLite 3.40.1**. Every table and index is a B-tree --------------------------------- Every table and every index is its own **B-tree**, made of pages inside the one file. The special table ``sqlite_master`` (also called ``sqlite_schema``) is always rooted at page 1. It stores the original ``CREATE`` statement and the **root page** of every other B-tree: .. code-block:: text sqlite> SELECT name, rootpage FROM sqlite_master WHERE tbl_name LIKE 'ranked%'; ranked_season|10 ranked_screen|11 sqlite_autoindex_ranked_screen_1|32 ranked_season_user_id_d59ea6db|33 ranked_screen_season_id_c88713d6|34 * A **table B-tree** (``ranked_season``, root page 10) is sorted by rowid, which is the ``id`` column. The leaf pages hold the actual row data, stored as a "record": a header of type codes followed by the values. * An **index B-tree** (for example page 33) stores ``(user_id, rowid)`` pairs sorted by ``user_id``. To use the index, SQLite searches it, gets the rowid, and then looks that rowid up in the table B-tree. * If a row is too big for one page, the rest goes into a chain of **overflow pages**. The ``cast`` JSON of the Demo Season is 4,223 bytes, which is more than one 4,096-byte page. So reading that one season means reading several pages. When Django runs ``SELECT ... WHERE "ranked_season"."id" = 1``, SQLite starts at page 10, follows the B-tree down to the leaf page holding rowid 1, follows any overflow pages, and decodes the record. The pager asks the operating system for each 4096-byte page using ``read()`` (or ``pread()``) at offset ``(page_number - 1) × 4096``. Locks, journals and the operating system ---------------------------------------- Because there is no server to coordinate access, SQLite uses **file locks** from the operating system. On Linux these are POSIX advisory locks taken with ``fcntl()`` on special byte ranges of the file. Any number of connections can read at the same time, but **only one can write**. A write transaction in this file's mode (rollback journal, ``journal_mode = delete``) works like this: #. Take a **SHARED** lock to read, then a **RESERVED** lock to say "I plan to write". #. Before changing a page, copy its *original* contents into ``db.sqlite3-journal``. #. Change the pages in memory. #. At ``COMMIT``: ``fsync()`` the journal, take an **EXCLUSIVE** lock, write the changed pages into ``db.sqlite3``, ``fsync()`` the database file, then **delete the journal**. Deleting the journal is the moment the commit becomes permanent. #. If the power fails halfway through, the next connection finds the journal still there ("hot journal") and copies the original pages back. The database is never left half-written. This is the **A** (atomicity) and **D** (durability) in ACID. The other mode is **WAL** (Write-Ahead Log). Changes are added to the end of a ``db.sqlite3-wal`` file instead, and readers keep reading the old pages while a writer works. That is better for a web app with many readers. You can turn it on with one line: .. code-block:: python DATABASES = { "default": { "ENGINE": "django.db.backends.sqlite3", "NAME": BASE_DIR / "db.sqlite3", "OPTIONS": { "init_command": "PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;", "transaction_mode": "IMMEDIATE", "timeout": 5, }, } } ``"database is locked"`` ------------------------ If two requests try to write at the same moment, the second one waits up to ``timeout`` seconds (5 by default) for the lock. If it still can't get the lock, it raises ``OperationalError: database is locked``. There is a sneaky version of this problem. A transaction that starts with ``BEGIN`` (a *deferred* transaction) only takes the write lock when it first writes. If it read some data first, SQLite can't always upgrade the lock safely, and to avoid a deadlock it can fail right away instead of waiting. ``"transaction_mode": "IMMEDIATE"`` (new in Django 5.1) makes Django send ``BEGIN IMMEDIATE``, which takes the write lock at the start and avoids this. For a game where several players save at once, WAL plus ``IMMEDIATE`` is the usual fix. Putting It All Together: One Line, Start to Finish =================================================== Here is the full journey of ``Season.objects.filter(saved=True).first()``. This list would make a good storyboard for a video: #. ``Season.objects`` → the ``Manager`` that ``ModelBase`` added when the class was created. #. ``.filter(saved=True)`` → clones a ``QuerySet``. Its ``Query`` gets a ``WHERE`` node, ``Exact(Col(ranked_season.saved), True)``. **No SQL yet.** #. ``.first()`` → there is an ordering (``Meta.ordering``), so Django slices ``[:1]``, which sets ``LIMIT 1``. This forces evaluation. #. ``connections["default"]`` → the ``DatabaseWrapper`` for this thread. On first use it calls ``get_new_connection()``, which calls ``sqlite3.connect()``, which calls the ``open()`` system call, then sets ``PRAGMA foreign_keys = ON``, and so on. #. ``SQLCompiler.as_sql()`` → walks the ``Query`` tree, quotes every name, applies ``ORDER BY "created" DESC`` and ``LIMIT 1``, and gives back SQL with ``%s`` plus a parameter list. #. ``SQLiteCursorWrapper.execute()`` → turns ``%s`` into ``?``. #. Python's ``sqlite3`` → ``sqlite3_prepare_v2()`` compiles the SQL into VDBE bytecode, ``sqlite3_bind_*()`` attaches the parameters, and ``sqlite3_step()`` runs it. #. SQLite → takes a SHARED lock (``fcntl``), walks the ``ranked_season`` B-tree from root page 10, and ``read()`` pulls in 4096-byte pages, using the page cache when it can. Because there is no index on ``created``, it scans the table, keeps the best row (``ORDER BY ... LIMIT 1``), and returns it. #. The driver → builds a Python tuple and runs the ``bool`` and ``datetime`` converters based on the declared column types. #. Django → runs the field converters (timezone-aware datetime, ``json.loads`` for the JSON fields) and calls ``Season.from_db()`` to build the instance. #. The read transaction ends. SQLite releases the SHARED lock. #. Your code gets ````. So the "magic" is really just **metadata** (``_meta``), a **compiler** (Query → SQL), **translators** (converters and adapters, in both directions), a **C library**, and the **operating system's file and locking calls**. Try It Yourself =============== These commands are good starting points for exploring, or for recording a video. .. code-block:: bash # Look at the raw schema and data without Django sqlite3 db.sqlite3 ".tables" sqlite3 db.sqlite3 ".schema ranked_screen" sqlite3 db.sqlite3 "SELECT typeof(saved), saved, created FROM ranked_season;" sqlite3 db.sqlite3 "EXPLAIN QUERY PLAN SELECT * FROM ranked_screen WHERE season_id=1;" sqlite3 db.sqlite3 "PRAGMA page_size; PRAGMA page_count; PRAGMA journal_mode;" # Ask Django what SQL it would run python manage.py sqlmigrate ranked 0001 python manage.py showmigrations python manage.py dbshell # opens sqlite3 on the configured database python manage.py inspectdb # the reverse trick: tables -> model code # Watch the file-level system calls SQLite makes (Linux) strace -f -e trace=openat,pread64,pwrite64,fcntl,fsync,unlink \ python manage.py shell -c "from ranked.models import Season; Season.objects.first()" Inside ``python manage.py shell``: .. code-block:: python from django.db import connection from django.test.utils import CaptureQueriesContext from ranked.models import Season, Screen qs = Season.objects.filter(saved=True) print(qs.query) # readable preview of the SQL print(qs.query.get_compiler("default").as_sql()) # the real (sql, params) print(qs.explain()) # SQLite's query plan with CaptureQueriesContext(connection) as ctx: list(Screen.objects.select_related("season")[:5]) for q in ctx.captured_queries: print(q["time"], q["sql"]) With ``DEBUG = True`` in your settings, every query is also saved in ``connection.queries``. That is how tools like Django Debug Toolbar count your queries. Where to Read the Source ======================== All of this is open source. These are the files behind each "magic trick": * ``django/db/models/base.py``: ``ModelBase``, ``Model.save()``, ``Model.from_db()`` * ``django/db/models/options.py``: ``Options`` (``_meta``) * ``django/db/models/fields/__init__.py``, ``fields/json.py``, ``fields/related.py``, ``fields/related_descriptors.py`` * ``django/db/models/query.py``: ``QuerySet``, ``MAX_GET_RESULTS`` * ``django/db/models/sql/query.py`` and ``sql/compiler.py``: the ``Query`` tree and the compiler * ``django/db/models/deletion.py``: ``Collector`` and ``on_delete`` * ``django/db/transaction.py``: ``atomic`` * ``django/db/backends/sqlite3/base.py``: ``data_types``, ``operators``, converters, ``get_new_connection()``, ``SQLiteCursorWrapper`` * ``django/db/backends/sqlite3/operations.py``, ``schema.py``, ``features.py``, ``_functions.py`` * ``django/db/migrations/autodetector.py``, ``executor.py``, ``recorder.py`` SQLite documentation: *Datatypes In SQLite* (type affinity), *Database File Format*, *Atomic Commit In SQLite*, *File Locking And Concurrency*, *Write-Ahead Logging*, *ALTER TABLE*, *The AUTOINCREMENT Keyword*, and *JSON Functions*, all at https://www.sqlite.org/docs.html. Django documentation: *Models*, *Making queries*, *Migrations*, and *Database transactions* at https://docs.djangoproject.com/en/5.2/. See the :ref:`Glossary` for short definitions of the database and Django terms used on this page.