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 aResponse
. (#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 fordatasette 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
toerror.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.
More recent articles
- OpenAI DevDay: Let’s build developer tools, not digital God - 2nd October 2024
- OpenAI DevDay 2024 live blog - 1st October 2024
- Weeknotes: Three podcasts, two trips and a new plugin system - 30th September 2024