Datasette binds authenticated context into saved SQL

A form that records a message can ask the user for its text, but asking the same form for the author's identity confuses two different sources of information. Datasette 0.45 lets a saved query bind values from authenticated request context and from the application's clock. The July 1 announcement introduces magic parameters with an insert that records an actor ID and the current datetime.

The mechanism extends canned queries, which give SQL a configured name and a corresponding application page. It allows a query definition to distinguish values supplied by the person submitting the form from values supplied by Datasette. That distinction makes a small writable SQLite application easier to express without adding editable identity and time fields to every form.

I like this use of SQLite because the operation remains visible as SQL while the publishing application supplies the request context it owns. The resulting design can be small, but the meanings of its inputs still need to be explicit. A parameter's source affects how much authority an application should give its value.

Underscore parameters have a different source

The query documentation in the 0.45 tag reserves named parameters beginning with an underscore for automatically supplied values. These do not come from the incoming form fields or query string. Ordinary named parameters still identify inputs that a form can expose to the user.

Actor parameters read fields from the currently authenticated actor. The documented examples include _actor_id and _actor_name. Time parameters include seconds since the Unix epoch through _now_epoch, a UTC date through _now_date_utc, and an ISO 8601 UTC datetime through _now_datetime_utc. Each name describes a particular value source and representation.

An illustrative canned query can accept a message while supplying its recorded author and timestamp. This configuration assumes a mutable database named mydatabase with a suitable messages table and an authentication mechanism that supplies actor IDs.

databases:
  mydatabase:
    queries:
      add_message:
        allow:
          id: "*"
        sql: |-
          INSERT INTO messages (
            user_id, message, datetime
          ) VALUES (
            :_actor_id, :message, :_now_datetime_utc
          )
        write: true

The pattern follows the release's documented example but omits its IP-address field to keep the inputs limited to the described built-in parameters. The form needs a message input. Datasette supplies the actor ID and current UTC datetime through the magic-parameter mechanism. A caller does not choose those values by filling equivalent ordinary fields in that form.

The write: true property is also necessary. According to the documentation, canned queries are read-only by default, and that property marks one as capable of writing. Permission to invoke the query belongs in the allow configuration. Supplying an actor value and granting permission to execute a write remain separate responsibilities in the query definition.

Writable queries already support messages and redirects for successful and failed execution. Those settings describe the response after the database operation, while the new parameters describe inputs to that operation. A success message can tell the user that a message was accepted without asking the user to provide its recorded author. The layers address different parts of the same interaction.

The documentation also allows ordinary fields to be prepopulated through the query string, but still requires form submission to execute the write. This is another reason to distinguish ordinary parameters from contextual ones. Prepopulating a message is a convenience for the caller, whereas selecting the authenticated actor is a responsibility of the authentication context. The magic parameter names keep those sources visible in the SQL.

Request-derived does not always mean trustworthy

The available parameters also include request headers and cookies. Header names use lowercase with hyphens converted to underscores, as in _header_user_agent. Cookie parameters use the _cookie_ prefix followed by the cookie name. Datasette reads these values from the incoming request instead of presenting them as ordinary query inputs.

That difference in transport does not give every value the same authority as an authenticated identity. A user-agent header is a statement made by the requesting client. Recording it can be useful, but a query should not confuse it with proof of who submitted the operation. The documented value source is enough to explain why these parameters need different interpretations.

Similarly, the current server datetime records the time Datasette supplies for the operation. It does not establish when an event described in the message actually happened. An application that needs both timestamps can represent both meanings explicitly. Automatically generating one field removes an input burden without settling the semantics of every time value in the model.

The parameter collection also includes _random_chars_ followed by a requested length. The documentation describes this as a generated random character string. That provides a convenient automatically supplied value, but the feature's presence does not establish every uniqueness or security property an application might require. Those properties need their own design where they matter.

Plugins can define additional parameter families

The plugin-hook documentation in the same release introduces register_magic_parameters(datasette). A plugin registers a prefix with a function. When a query requests a matching parameter, the remainder of its name becomes a key passed to that function alongside the current request.

This divides a parameter name into a family and a family-specific argument. A plugin can therefore provide several related values through one registered function. The documented example uses a request family to expose the HTTP version and another family to generate a UUID. Unsupported keys raise KeyError in those examples, keeping the accepted parameter meanings explicit.

The extension point is useful because the core cannot know every application's request context. A plugin can supply a value while the canned query continues to describe the database operation in SQL. That arrangement also makes plugin behavior part of the query's dependencies. A saved query using a custom family requires the corresponding implementation to interpret it.

I prefer this explicit dependency to hiding the entire insert behind a bespoke form handler. The SQL records which columns receive contextual values, and the parameter family identifies where those values originate. For the message example, that leaves the writable operation understandable in one configured query while authentication remains responsible for establishing the actor whose ID the query records.