Show captured SQL beside query and mutation examples (needs #700) #725

Parent

#698

What to build

Writing-for-humans rule 13 asks for the SQL beside each query. Ferro has no public way to get the SQL a query or mutation runs; #700 designs one. Once it ships, the Queries, Aggregations, Relationships and Mutations guides show the SQL captured from that API beside each example, produced by the same test run that executes the example, never typed by hand.

The API shipped (#700, PR #771, on main and merged into feat/docs-rewrite): every Query terminal has a .sql() that returns a RenderedSql (sql, params, dialect; str() is the SQL alone) without sending anything, routed like the call, with secrets shown as REDACTED. Mutations on an instance (save, delete, bulk_create) have no .sql(); their statements are read from the statement log (ferro.sql records; the statement_records fixture in tests/conftest.py).

How the SQL reaches the page, and stays true. Follow the migrations pages' precedent (tests/test_docs_migrations.py, STEP_FILE_SOURCES): a page never carries SQL a person typed. Each sql fence on the four pages opens with a marker line naming its source, -- <example module>:<function or snippet> (<dialect>), for example -- predicates.py:first_query (sqlite), and a new test, tests/test_docs_sql.py, regenerates every marked block from the named example (calling the query's terminal .sql(), or reading the statement_records for an instance mutation) on that dialect and compares byte for byte, the way the migration step files are compared. A block for Postgres runs under the postgres_only marker. Where the SQL is the same on both dialects bar placeholders, show one block and say so in its lead-in; where it differs, show both. The convention in docs/solutions/patterns/docs-page-conventions.md ("Generated SQL beside each query") becomes the recipe, with the marker form written down.

Pages: guide/queries.md, guide/aggregations.md, guide/relationships.md, guide/mutations.md

Acceptance criteria

• Every fact on the page matches the code at the time of the PR; each High finding in the audit rows below is fixed, and each Medium/Low finding is fixed or answered in the PR body with the reason it was left.
• Each page is one kind (tutorial, how-to, reference or explanation) and opens with one sentence saying what it covers, then an example on the first screen (writing-for-humans rules 1–2).
• Each page uses one running example, declared once near the top and reused; each section shows only what changed (rule 3).
• Every code block has a lead-in sentence ending in a colon and a result sentence after it, and shows its result: printed output, or the asserted value inside the embedded snippet range (rules 7–8). Snippet end: markers sit below the assert or print that holds the result.
• Every copyable block either shows the async with engines.session(): wrapper or sits under a sentence that says it runs inside one and links to Connections › Sessions; the page's first block shows the wrapper.
• Every inline Python block that does real work runs under the harness ({test=run prelude=<name>}) or moves into an example script; a block left compile-only says why in its fence marker.
• Every lambda passed to a query method names its parameter after the model in lowercase singular (AGENTS.md I-8); python -m tests._docs_checks reports no violation for the owned files.
• Every error check in an owned example fails when the expected error is not raised (try/except/else: raise AssertionError, or the harness helper).
• Field-declaring examples show both declaration styles as tabs, each backed by a runnable script (AGENTS.md I-7), and the twins use the one Annotated idiom recorded in the docs conventions.
• Headings are short sentence-case noun phrases naming the feature (rule 5); how-to titles name the goal (rule 24); no minimising words (rule 17); admonitions follow rule 20.
• No ADR numbers, issue numbers, codenames, dated or release-bound notes, or contributor-only references in page text; version markers are self-contained blocks at the end of a section.
• Reference material that has a home in Reference is linked, not repeated; each topic ends with a pointer to its reference entry (rule 4).
• Every query example on the four pages shows the SQL Ferro sends, as a marked sql fence regenerated by tests/test_docs_sql.py from the example's own .sql() (or the statement log for an instance mutation), for each dialect it differs on; editing one character of a marked block makes that test fail naming the page and line.
• A marked block's lead-in says which call it is the SQL of and, where one block stands for both dialects, that only the placeholders differ.

Files owned

• Create: tests/test_docs_sql.py
• Modify: docs/solutions/patterns/docs-page-conventions.md (the "Generated SQL beside each query" section becomes the marker recipe), docs/examples/_preludes/queries.py (if a prelude needs a Postgres twin for the Postgres blocks), docs/pages/guide/queries.md, docs/pages/guide/aggregations.md, docs/pages/guide/relationships.md, docs/pages/guide/mutations.md, docs/examples/mutations.py, docs/examples/relationships.py, docs/examples/relationships_annotated.py, docs/examples/predicates.py, docs/examples/existence_tests.py, docs/examples/partial_selects.py, docs/examples/populated_relations.py, docs/examples/traversal.py, docs/examples/aggregations.py, docs/examples/predicates_annotated.py, docs/examples/existence_tests_annotated.py, docs/examples/partial_selects_annotated.py, docs/examples/populated_relations_annotated.py, docs/examples/traversal_annotated.py, docs/examples/aggregations_annotated.py

Interfaces

• Consumes: Query.all.sql(), first.sql(), count.sql(), exists.sql(), update.sql(...), delete.sql() → ferro.RenderedSql (sql: str, params: tuple, dialect: Literal["postgres", "sqlite"]; str(rendered) is the SQL), from SQL inspection and logging: print or log the SQL Ferro renders and runs for a query or mutation #700 (shipped in PR feat: SQL inspection and logging — the ferro.sql statement log and .sql() on every Query terminal (#700) #771); ferro.REDACTED; the statement_records fixture (tests/conftest.py) and the ferro.sql record attributes (sql, params, dialect, operation) for instance mutations; the postgres_only / backend_matrix markers (tests/conftest.py, tests/db_backends.py); the regeneration pattern of tests/test_docs_migrations.py (STEP_FILE_SOURCES, byte-for-byte compare naming the page and line).
• Consumes: the docs harness (tests/_docs_harness.py, tests/test_docs_examples.py), the preludes in docs/examples/_preludes/, and the page conventions, from Docs harness: run inline blocks with a prelude, run docstring examples, and check lambda names and error checks #704/Mutations, transactions and connections guides, and the multiple-databases how-to #711.
• Produces: the sql fence marker form -- <example>:<name> (<dialect>) and tests/test_docs_sql.py that regenerates it, used by Docs: the Inspecting SQL guide page #767 (the Inspecting SQL guide page shows its captured SQL the same way) and by every later docs page that shows a query's SQL.

Test scenarios

• Happy path: the strict site build succeeds and every snippet range on the owned pages renders without leading indentation.
• Happy path: every owned example script runs to completion under tests/test_docs_examples.py, and every inline block marked {test=run ...} runs.
• Error path: deleting the raise an owned example's error check expects makes that example's test fail.
• Edge case: python -m tests._docs_checks over the owned files exits 0; renaming one lambda parameter to t makes it exit 1 naming the file and line.
• Happy path: tests/test_docs_sql.py regenerates every marked sql block on the four pages on SQLite, and the Postgres-marked ones on Postgres, and they compare equal.
• Error path: changing one character inside a marked sql block makes tests/test_docs_sql.py fail naming the page, the line and the example it was regenerated from; a marker naming an example or name that does not exist fails the same way.
• Edge case: a page sql fence with no marker line is refused by the test (every SQL block on the four pages is regenerated or it does not exist).

Verify

uv run --no-sync zensical build --clean --strict
uv run --no-sync pytest tests/test_docs_examples.py tests/test_docs_sql.py tests/test_docs_site.py tests/test_docs_lint.py -q --no-cov -p no:cacheprovider -n 8
uv run --no-sync pytest tests/test_docs_sql.py -q --no-cov -p no:cacheprovider --db-backends=sqlite,postgres
uv run --no-sync python -m tests._docs_checks docs/pages/guide/queries.md docs/pages/guide/aggregations.md docs/pages/guide/relationships.md docs/pages/guide/mutations.md docs/examples

Blocked by

• Navigation: six tabs, sentence-case labels that match page titles, moved pages redirected #723, Enforce the docs guards: rendered-site checks, inline blocks run by default, lambda and error-check lint in the suite #724 (closed). SQL inspection and logging: print or log the SQL Ferro renders and runs for a query or mutation #700's API is on main and in feat/docs-rewrite (merge d070e31); the edge to SQL inspection and logging: print or log the SQL Ferro renders and runs for a query or mutation #700 was removed because SQL inspection and logging: print or log the SQL Ferro renders and runs for a query or mutation #700 itself waits on Docs: the Inspecting SQL guide page #767, which waits on this epic.

Audit findings