Allow canned query params to specify default values

[wdccdw]

If I call a canned query that includes named parameters, without passing any parameters, datasette runs the query anyway, resulting in an HTTP status code 400, and a visible error in the browser, with only a link back to home. This means that one of the default links on https://site/database/ will lead to a broken page with no apparent way out.

Is there any way to skip performing the query when parameters aren't supplied, but otherwise render the usual canned query page? Alternatively, can I supply default values for my parameters, either when defining my canned queries or when linking to the canned query page from the default database template.

[simonw]

This is a good idea. I avoided this initially because it should be possible to run a canned query with a parameter set to the empty string, but that view could definitely be smart enough to differentiate between ?sql=...&param= and ?sql= with no param specified at all.

[wdccdw]

What about allowing default parameters when defining the query in metadata.yml? Something like:

databases:
  fec:
    queries:
      search_by_name:
        params:
            - q
        default-param-values:
             q: "text to search"
        sql: |-
          SELECT...

For now, I'm using a custom database-.html file that hardcodes a default param in the link, but I'd rather not customize the template just for that.

[simonw]

I like that idea.

[simonw]

Right now they look like this:

databases:
  fixtures:
    queries:
      neighborhood_search:
        params:
        - text

In addition to being able to specify defaults, I'd also like to add other things in the future - most significantly the ability to specify a different input widget (e.g. textarea v.s. single-line input)

So maybe this looks like:

params:
- name: text
  default: ""
- name: age
  widget: number

[brandonrobertz]

This is pretty annoying for FTS because sqlite throws an error instead of just doing something like returning all or no results. This makes users who are unfamiliar with SQL and Datasette think the canned query page is broken and is a frequent source of confusion.

To anyone dealing with this: My solution is to modify the canned query so that it returns no results which cues people to fill in the blank parameters.

So instead of emails_fts match escape_fts(:search))

My canned queries now look like this:

emails_fts match escape_fts(iif(:search=="", "*", :search))

There are no asterisks in my data so the result is always blank.

Ultimately it would be nice to be able to handle this in the metadata. Either making some named parameters required or setting some default values.

[dbaynard]

I've been using the sqlite functions coalesce and nullif to handle this case. They even work for things like limit.

 limit coalesce(cast(nullif(:limit, '') as int), 101)

If the :limit parameter is defined, this uses the limit (assuming it can be cast to an integer). If not, then the nullif means :limit, now equal to the empty string, becomes null, and so we end up with 101.

I can make a PR to document this, if desirable?