Query APIs

Query APIs are a more free-form and type-unsafe way of exposing data using virtual tables based on user inputs and stored procedures. Please make sure to take a look at record APIs first. Views and generated columns may be a better fit for transforming data if no explicit user input is required.

Note

Query APIs fill a gap that in other frameworks is often filled by custom handlers. TrailBase may go this direction as well either with custom Axum handlers or embedding another runtime. At least for the time being Query APIs based on stored procedures are simply a very constrained (e.g. read-only) and performant way to achieve similar goals.

Example

Using migrations and sqlean’s define we can define a table query with unbound inputs (see placeholder $1):

CREATE VIRTUAL TABLE
  _is_editor
USING
  define((SELECT EXISTS (SELECT * FROM editors WHERE user = $1) AS is_editor));

Subsequently, an API can be configured to query the newly created VIRTUAL TABLE, also binding URL query parameters as inputs to above placeholders.

query_apis: [
  {
    name: "is_editor"
    virtual_table_name: "_is_editor"
    params: [
      {
        name: "user"
        type: BLOB
      }
    ]
    acl: WORLD
  }
]

Finally, we can query the API, e.g. using curl:

curl -g 'localhost:4000/api/query/v1/is_editor?user=<b64_user_id>'