Named SQL queries

Named SQL queries let a template or Markdown page describe the data it needs alongside the markup that displays it. A query declaration stores the SQL under a name without executing it or producing visible output. When the template calls fetch(), fetch_one(), or fetch_value(), StaticFiles passes the query and request parameters to a query runner, which is your application's connection to the database.

To execute the examples, configure a query runner for a weather database containing a stations table. Without a runner, fetch() returns an empty list.

Declaring a query

The {% sql %} tag declares a query in a Jinja template or Markdown file. This example names the query stations and selects rows from the weather database using a :country parameter:

{% sql stations from weather %}
SELECT * FROM stations WHERE country = :country
{% endsql %}

Markdown files also support query declarations in fenced code blocks. The sql info string below names a second query, totals, which counts all stations in the same database:

```sql totals from weather
SELECT count(*) FROM stations
```

Both forms accept sql <name> or sql <name> from <database>. StaticFiles collects the declared query into a QuerySet that the template reaches through queries.

Running a query

The fetch helpers execute a declared query and return its results to the template. You can look up a query through queries.stations or queries.get("stations"), or pass its name directly with fetch("stations"). With both declarations above in the page, this example lists the matching stations and displays the total count:

{% for s in fetch(queries.stations) %}
  <li>{{ s.name }}</li>
{% endfor %}

<p>There are {{ fetch_value(queries.totals) }} stations.</p>

A request to ?country=US supplies US for :country, so the list contains matching stations while totals counts all stations. Choose a helper according to the result the template needs:

Helper	Returns
`fetch(query)`	All rows, as a list of dicts.
`fetch_one(query, default=None)`	The first row, or `default` when there are none.
`fetch_value(query, default=None)`	The first column of the first row, such as a `COUNT`, or `default`.

The runner receives parameter values separately from the SQL and must bind them through the database driver rather than interpolate them into the query text.

Providing a query runner

A query runner adapts your database client to the QueryRunner protocol. Its async run() method receives the SQL declaration and request parameters and returns rows as dictionaries. The following skeleton shows where your database implementation belongs; replace the ellipsis with code that binds the parameters and executes the query before using it:

from jinja2 import FileSystemLoader

from starlette_templates.staticfiles import Query, Row, StaticFiles

class MyRunner:
    async def run(self, query: Query, params: dict) -> list[Row]:
        # Run query.sql against query.database, bind params, and return the rows.
        ...

static = StaticFiles(loader=FileSystemLoader("site"), query_runner=MyRunner())

The template awaits run() inline as it renders, inside the request's cancellation scope. When the client disconnects, a CancelledError reaches the runner. The runner owns any caching. To report a failure, raise QueryExecutionError. It keeps an engine-specific exception from reaching the render.

For a complete page example, see SQL in Markdown.