Skip to content

Query Parameters

Jump to bottom
brookie edited this page Oct 8, 2026 · 2 revisions

Query Parameters

A database route's query_params section turns URL query string values into SQL filters, sorting and paging, required-parameter errors and fixed responses. This page covers every option and the order they are applied in.

routes:
  - route: users
    sql: SELECT * FROM [[users]]
    query_params:
      - department:
          sql: department = {{department}}      # only when ?department= is given
      - limit:
          sql_append: LIMIT {{limit}}
          default: 50                           # always applied
GET /db/users?department=eng
# SELECT * FROM 's3://.../users.parquet' AS users WHERE department = ? LIMIT 50   -- ? = 'eng'

The # SQL comments on this page show bound values in place where that is easier to read. Values in sql, multivalue_sql and conditional sql are always sent as bound parameters, never pasted into the SQL; see How values reach the database.

Where query params are defined

query_params is a list of single-key mappings (- name: {options}). It can be set in three places:

Where Applies to
A route's query_params That route
Top-level query_params in a database/version file Every route in that file
query_params in the shared databases/config.yaml Every database/version, unless limited with include/exclude (see Shared Database Config)

When the same name is defined in more than one place, the most specific wins: route > version file > shared. The merged list is the route's params, then the version file's, then the shared ones, each in YAML order. That order matters for sql_append (see below).

Options

Option Purpose
sql WHERE fragment, added when the param is in the URL (or always, with default).
multivalue_sql WHERE fragment used instead of sql when the key is repeated (?id=1&id=2).
sql_append Clause added after the WHERE clause (ORDER BY, LIMIT, OFFSET, ...).
default Value used when the param isn't in the URL. Also makes the param's fragment always apply.
required true: return an error when the param is missing.
missing_response Custom body (and http_status) for a missing required param.
response Return this body, without running SQL, when the param is present.
conditional Choose a WHERE fragment or a response by the param's value.
action Not implemented; don't use (see below).

Each param needs at least one of these keys; API Dock checks this at startup.

sql: filters

Each sql fragment is added to the WHERE clause, joined with AND, only when its param is in the URL:

query_params:
  - age:
      sql: age = {{age}}
  - name:
      sql: name ILIKE '%' || {{name}} || '%'
  - height:
      sql: height < {{height}}
      default: 200
GET /db/users?age=25
# SQL: SELECT * FROM users WHERE age = 25 AND height < 200
GET /db/users
# SQL: SELECT * FROM users WHERE height < 200

  • Fragments are added in list order to the base query's top-level WHERE. Each fragment is parenthesized and joined with AND, and the base query's own condition is parenthesized, so WHERE a = 1 OR b = 2 plus ?c=3 becomes WHERE (a = 1 OR b = 2) AND (c = ?). A base query without a top-level WHERE gets one.

  • "Top-level" means outside strings, comments, CTEs and subqueries, so a WHERE inside a CTE is left alone and the outer query gets its own:

    WITH x AS (SELECT * FROM [[users]] WHERE a = 1) SELECT * FROM x
# with ?b=2: WITH x AS (... WHERE a = 1) SELECT * FROM x WHERE (b = ?)

  • The condition goes before a trailing GROUP BY, HAVING, ORDER BY, LIMIT or OFFSET in the base query. Still, put clauses that query params control (sorting, paging) in sql_append; those are added at the end.

  • Before api_dock 0.10.0, fragments were appended to the end of the base SQL, starting with AND whenever the word WHERE appeared anywhere in it (even in a CTE or a file path), and without parentheses.

  • A fragment can use any variable: its own value, other query values, path variables, defaults, and {{cookies.<name>}}.

  • Fragments can use [[table]] references, such as "[[detections]].confidence >= {{confidence}}" (quote the YAML value when it starts with [[).

  • Write variables without quotes, and build patterns with || (see How values reach the database).

multivalue_sql: repeated keys

When a key appears more than once (?recording_id=1&recording_id=4), multivalue_sql is used instead of sql, and {{param}} becomes a parenthesized list with one bound value per URL entry:

query_params:
  - recording_id:
      sql: "[[detections]].recording_id = {{recording_id}}"
      multivalue_sql: "[[detections]].recording_id IN {{recording_id}}"
GET /db/detections?recording_id=4
# SQL: ... WHERE detections.recording_id = '4'
GET /db/detections?recording_id=4&recording_id=1
# SQL: ... WHERE detections.recording_id IN ('4', '1')

With a single value, sql is used (a param with only multivalue_sql adds nothing for a single value). Without multivalue_sql, a repeated key uses its last value.

sql_append: sorting and paging

sql_append adds a clause after the WHERE clause. Clauses are added in the merged list order, so list them in valid SQL order: ORDER BY, then LIMIT, then OFFSET.

query_params:
  - department:
      sql: department = {{department}}
  - sort:
      sql_append: ORDER BY {{sort}} {{direction}}
      default: created_date
  - direction:
      default: DESC
  - limit:
      sql_append: LIMIT {{limit}}
      default: 50
  - offset:
      sql_append: OFFSET {{offset}}
GET /db/users?department=eng&sort=name&direction=ASC&limit=10
# SQL: SELECT * FROM users WHERE department = 'eng' ORDER BY name ASC LIMIT 10
GET /db/users
# SQL: SELECT * FROM users ORDER BY created_date DESC LIMIT 50
GET /db/users?limit=20&offset=40
# SQL: SELECT * FROM users ORDER BY created_date DESC LIMIT 20 OFFSET 40
  • A clause is added when its param is in the URL, or always when it has a default.
  • sql_append values can't be bound, so they are written into the SQL text after a check. A value must be a non-negative integer (10), or a comma-separated list of column names, optionally qualified and each optionally followed by ASC/DESC and NULLS FIRST/NULLS LAST (confidence, d.start_time DESC, a, b DESC). Anything else (parentheses, quotes, operators, comments, an empty value) gives a 500 SQL query error. (Before 0.10.0 parentheses and - were allowed.)
  • A param with sql_append doesn't also add its sql; use one or the other.
  • A Conditional SQL branch's own sql_append (such as a GROUP BY) comes before these clauses.

Value-only params

A param with only a default adds no SQL. It just provides a variable for other templates, like direction above: ?direction=ASC overrides it, and without it {{direction}} is DESC. Give value-only params a default; a variable with no value is left in the SQL as text and the query fails.

default

  • A param with default is always applied: its sql or sql_append is added even when the param isn't in the URL, using the default value.
  • Defaults are available to every template as variables.
  • A default doesn't make a param "present": it doesn't trigger response, doesn't satisfy required, and isn't seen by Conditional SQL rules.

required and missing_response 

query_params:
  - report_type:
      sql: report_type = {{report_type}}
      required: true
      missing_response:
        error: report_type is required
        valid_types: [summary, detailed]
        http_status: 400
GET /db/reports
# 400 {"error": "report_type is required", "valid_types": ["summary", "detailed"], "http_status": 400}
  • The missing_response mapping is returned as the body (including its http_status key), with http_status (default 400) as the status.
  • Without missing_response: 400 {"error": "Required parameter 'report_type' is missing"}.
  • required checks the query string only.

response: fixed responses

When the param is present (any value, even empty), return response with status 200 and run no SQL:

query_params:
  - debug:
      response:
        message: Debug mode
        query: "You searched for {{q}}"
  - sleeping:
      response: This endpoint is disabled during sleep mode.
GET /db/users?debug=1&q=owls
# 200 {"message": "Debug mode", "query": "You searched for owls"}
GET /db/users?sleeping=true
# 200 "This endpoint is disabled during sleep mode."

{{variables}} in a response are replaced as plain text with path and query string values; ones with no value are left unchanged.

conditional: branch on the value

conditional maps a value to a branch. A branch has an sql fragment (added to the WHERE clause) or a response (returned immediately). A default branch with a response handles any other value:

query_params:
  - status:
      conditional:
        'true':
          sql: enrolled = true
        'false':
          sql: enrolled = false
        pending:
          response:
            message: Pending users cannot be queried
        default:
          response: Unknown status. Use true, false or pending.
GET /db/users?status=true
# SQL: SELECT * FROM users WHERE enrolled = true
GET /db/users?status=pending
# 200 {"message": "Pending users cannot be queried"}
GET /db/users?status=xyz
# 200 "Unknown status. Use true, false or pending."
  • Values match exactly (case-sensitive) against the keys as strings. Quote keys that YAML would read as something else: unquoted true:, false:, yes:, no:, on:, off: become booleans and never match (YAML numbers such as 1: likewise become integers, so quote them too).
  • Only a default branch's response is used; an sql in default is ignored.
  • With no matching branch and no default, the param adds nothing.
  • A conditional applies only when the param is in the URL.

For choosing a different base query by value (for example a COUNT(*) ... GROUP BY histogram), use Conditional SQL instead.

action

action isn't implemented. Since api_dock 0.10.0 a config that uses it (on a param or in a conditional branch) stops startup with a message naming the param. Earlier versions accepted it, and a conditional branch with action echoed the request's parameters back.

Processing order

  1. Early returns. Params are checked in merged list order. For each param, in turn:
    • response, if the param is present: return it.
    • conditional, if the param is present: a matching branch with response is returned; a value with no matching branch returns the default branch's response, if any.
    • required, if the param is missing: return the error.
  2. Base SQL. The route's sql is chosen (Conditional SQL rule lists can return a 400 here).
  3. WHERE. sql, multivalue_sql and conditional sql fragments are added in list order.
  4. After WHERE. The chosen Conditional SQL branch's sql_append, then each param's sql_append, in list order.
  5. The query runs.

Because step 1 runs param by param, a param's position in the list decides which early return wins when several apply.

Complete example

name: my_database
tables:
  users: s3://my-bucket/users.parquet

routes:
  - route: users/search
    sql: SELECT * FROM [[users]]
    query_params:
      # filters
      - name:
          sql: name ILIKE '%' || {{name}} || '%'
      - age_min:
          sql: age >= {{age_min}}
      - age_max:
          sql: age <= {{age_max}}
      - department:
          sql: department = {{department}}
          multivalue_sql: department IN {{department}}
      # sorting and paging
      - sort:
          sql_append: ORDER BY {{sort}} {{sort_direction}}
          default: created_date
      - sort_direction:
          default: DESC
      - limit:
          sql_append: LIMIT {{limit}}
          default: 50
      - offset:
          sql_append: OFFSET {{offset}}
      # fixed response
      - sleeping:
          response: Search is disabled during sleep mode.
GET /my_database/users/search?name=john&age_min=21&age_max=65&sort=age&sort_direction=ASC&limit=20&offset=40
# SQL: SELECT * FROM users
#      WHERE name ILIKE '%' || 'john' || '%' AND age >= 21 AND age <= 65
#      ORDER BY age ASC LIMIT 20 OFFSET 40

GET /my_database/users/search?department=eng&department=ops
# SQL: SELECT * FROM users WHERE department IN ('eng', 'ops') ORDER BY created_date DESC LIMIT 50

GET /my_database/users/search?sleeping=true
# 200 "Search is disabled during sleep mode."

API Dock

Getting started

Remote APIs

Databases

Serving

Developing

Repository · PyPI

Clone this wiki locally