Simon Willison’s Weblog

Datasette 0.49: The annotated release notes

15th September 2020

Datasette 0.49 is out. Some notes on what’s new.

API for writable canned queries

Writable canned queries now expose a JSON API, see JSON API for writable canned queries. (#880)

I wrote about writable canned queries when they were introduced in Datasette 0.44 back in June. They provide a mechanism for defining a canned SQL query which can make writes (inserts, updates or deletes) to the underlying SQLite database. They can be protected by Datasette authentication or you can leave them open—for example if you want unauthenticated users to be able to post comments or leave feedback messages.

The missing feature was API support. Datasette 0.49 adds that, so now you can define a canned query that writes to the database and then call it as a JSON API—either without authentication or protected by a mechanism such as that provided by the datasette-auth-tokens plugin.

This feature works with magic parameters, so you can define an API that accepts API traffic and automatically logs things like the incoming IP address. Here’s a canned query defined in a metadata.yml file that logs user agent and IP addresses:

databases:
  logs:
    queries:
      log:
        sql: |-
          INSERT INTO logs (
            user_agent, datetime
          ) VALUES (
            :_header_user_agent, :_now_datetime_utc
          )
        write: true

Create a SQLite database file called logs.db with the correct table like this:

$ sqlite-utils create-table logs.db logs id integer user_agent text datetime text --pk=id

Confirm the created schema with:

$ sqlite3 logs.db .schema
CREATE TABLE [logs] (
   [id] INTEGER PRIMARY KEY,
   [user_agent] TEXT,
   [datetime] TEXT
);

Now start Datasette like so:

$ datasette logs.db -m metadata.yml

And visit http://127.0.0.1:8001/logs/log. You can click the “Run SQL” button there to insert a new log row, which you can then view at http://127.0.0.1:8001/logs/logs.

Next, the API. You can request a JSON response by adding ?_json=1 to the URL, so try this with curl:

$ curl -XPOST 'http://127.0.0.1:8001/logs/log?_json=1'
{"ok": true, "message": "Query executed, 1 row affected", "redirect": null}

You can also set the Accept: application/json header on your request, like so:

$ curl -XPOST 'http://127.0.0.1:8001/logs/log' -H 'Accept: application/json'
{"ok": true, "message": "Query executed, 1 row affected", "redirect": null}                                                             ~ % 

Or by passing _json=1 as part of a POST submission. Let’s try that using requests:

$ ipython
In [1]: import requests
In [2]: requests.post("http://127.0.0.1:8001/logs/log", data={"_json": "1"}).json()
Out[2]: {'ok': True, 'message': 'Query executed, 1 row affected', 'redirect': None}

Path parameters for custom page templates

New mechanism for defining page templates with custom path parameters—a template file called pages/about/{slug}.html will be used to render any requests to /about/something. See Path parameters for pages. (#944)

I added custom page support in Datasette 0.41 back in May, based on the needs of my Niche Museums site. I wanted an easy way to create things like an /about page that returned content from a custom template.

Custom page templates work as a fallback for Datasette 404s. If /about fails to resolve and Datasette was provided a --template-dir on startup, Datasette will check to see if a template exists called templates/pages/about.html.

Datasette 0.49 adds support for path parameters, partially inspired by cookiecutter (which showed me that it’s OK to create files with curly braces in their name). You can now create templates with {slug} style wildcards as part of their filenames, and Datasette will route matching requests to that template.

I shipped a new release of Niche Museums today that takes advantage of that feature. I wanted neater URLs for museum pages—to shift from https://www.niche-museums.com/browse/museums/101 to https://www.niche-museums.com/101.

Here’s how it works. I have a template file called templates/pages/{id}.html. That template takes advantage of the datasette-template-sql plugin, which adds a sql() function that can be called within the template. It starts like this:

<!DOCTYPE html>
<html>
{% set rows = sql("select * from museums where id = :id", {"id": id}) %}
{% if not rows %}
    {{ raise_404("Museum not found") }}
{% endif %}
<head>
    <meta charset="utf-8">
    {% set museum = rows[0] %}
    ... rest of the template here ...

Datasette made the variable id available to the context having captured it from the {id}.html template matching the incoming URL.

I then use the sql() function to execute a query, passing in id as a query parameter.

If there are no matches, I call the brand new raise_404() template function which cancels the rendering of this template and falls back to Datasette’s default 404 handling.

Otherwise, I set the museum variable to rows[0] and continue rendering the page.

I’ve basically reinvented PHP and ColdFusion in my custom 404 handler. Hooray!

A few other notable changes

register_output_renderer() render functions can now return a Response. (#953)

The register_output_renderer() plugin hook was designed before Datasette had a documented Response class. It asked your plugin to return a custom {"content_type": "...", "body": "...", "status_code": 200, "headers": {}} dictionary.

You can now return a Response instead, and I plan to remove the dictionary version before Datasette 1.0. I released new versions of datasette-ics, datasette-atom, datasette-copyable and the new datasette-yaml that use the new return format.

New --upgrade option for datasette install. (#945)

I added the datasette install datasette-cluster-map command as a thin wrapper around pip install.

This means you can install new plugins without first figuring out which virtual environment your Datasette is running out—particularly useful if you install Datasette using Homebrew.

I then realized that this could be used to upgrade Datasette itself—but only if you could run pip install -U. So now datasette install -U datasette will upgrade Datasette in place.

New datasette --pdb option. (#962)

This is useful if you are working on Datasette itself, or a Datasette plugin. Pass the --pdb option and Datasette will start an interactive Python debugger session any time it hits an exception.

datasette --get exit code now reflects the internal HTTP status code. (#947)

I’m excited about the pattern of using datasette --get for running simple soundness checks, e.g. as part of a CI suite. Now that the exit code reflects the status code for the page you can write test scripts that look like this:

# Fail if homepage returns 404 or 500
datasette . --get /

New raise_404() template function for returning 404 errors. (#964)

Demonstrated in the Niche Museums example above.

And the rest:

• datasette publish heroku now deploys using Python 3.8.5
• Upgraded CodeMirror to 5.57.0. (#948)
• Upgraded code style to Black 20.8b1. (#958)
• Fixed bug where selected facets were not correctly persisted in hidden form fields on the table page. (#963)
• Renamed the default error template from 500.html to error.html.
• Custom error pages are now documented, see Custom error pages. (#965)

In writing up these annotated release notes I spotted a bug with writable canned queries, which I have now fixed in Datasette 0.49.1.