Simon Willison’s Weblog

Datasette 0.45: The annotated release notes

1st July 2020

Datasette 0.45, out today, features magic parameters for canned queries, a log out feature, improved plugin documentation and four new plugin hooks.

As I did for Datasette 0.44, I’m going to provide an annotated version of the full release notes here on my blog.

Magic parameters for canned queries

Canned queries now support Magic parameters, which can be used to insert or select automatically generated values. For example:

insert into logs
  (user_id, timestamp)
values
  (:_actor_id, :_now_datetime_utc)

This inserts the currently authenticated actor ID and the current datetime. (#842)

This is a fun new feature that extends the capabilities of writable canned queries, introduced in Datasette 0.44.

The key idea here is to make it easy to insert contextual information such as the current timestamp, the authenticated actor or other automatically generated values as part of a writable query.

This means Datasette’s canned queries are now powerful enough to build things like simple comment systems or logging endpoints purely by defining a SQL query with the right magic parameters.

There’s even a :_random_chars_32 parameter that automatically generates a random text string—useful for things like generating authentication tokens for use with datasette-auth-tokens. More on this below.

Log out

The ds_actor cookie can be used by plugins (or by Datasette’s --root mechanism) to authenticate users. The new /-/logout page provides a way to clear that cookie.

A “Log out” button now shows in the global navigation provided the user is authenticated using the ds_actor cookie. (#840)

Out of the box, Datasette’s authentication system is quite primitive: the only way to get an authenticated session is to use the --root option to get a special link when the server first starts running. As described in the documentation, the goal is for plugins to fill in the rest.

Even with just that mechanism it still makes sense to let people log out again! The new /-/logout page can do that, and Datasette’s navigation now includes a log out button if the user is logged in using that ds_actor cookie.

You can see what this looks like in Datasette’s pattern portfolio.

New plugin hooks

register_magic_parameters(datasette) can be used to define new types of magic canned query parameters.

I’m increasingly trying to have Datasette internally use plugin hooks for default behaviour. This hook can define custom magic parameters—you can see the implementation of the default parameters using this hook in default_magic_parameters.py.

startup(datasette) can run custom code when Datasette first starts up. datasette-init is a new plugin that uses this hook to create database tables and views on startup if they have not yet been created. (#834)

Here’s an example datasette-init plugin configuration in metadata.yaml. This will create a dogs table when the server starts, but only if one has not yet been created:

plugins:
  datasette-init:
    my_database:
      tables:
        dogs:
          columns:
            id: integer
            name: text
            age: integer
            weight: float
          pk: id

canned_queries(datasette, database, actor) lets plugins provide additional canned queries beyond those defined in Datasette’s metadata. See datasette-saved-queries for an example of this hook in action. (#852)

This started out as a feature request from Amjith Ramanujam on Twitter.

Canned queries, like these ones, are usually defined in the increasingly poorly-named metadata.json/yaml.

Letting plugins define them opens up some neat possibilities. datasette-saved-queries is an interesting example: it lets users store new queries in their database, inserting them using a writable canned query that the plugin itself returns from that hook by default.

Here’s the code. It also uses the new startup() hook to create its own table.

forbidden(datasette, request, message) is a hook for customizing how Datasette responds to 403 forbidden errors. (#812)

I need this for the next version of datasette-auth-github—it’s a way to customize what happens when a user fails a permission check.

Even more plugins

Thanks to the datasette-plugin cookiecutter template I can turn out simple plugins in just a few minutes. Here are my new releases from the past week:

• datasette-init, described above.
• datasette-write provides a /-/write page that can be used to directly execute write SQL queries against a selected database.
• datasette-allow-permissions-debug which is absolutely tiny. All it does is listen for permissions-debug checks and return True for them. This means you can access the /-/permissions debug page on your Datasette instance without authenticating first, which is handy for debugging.
• datasette-glitch is designed for use with Glitch. It outputs a magic one-time use URL to the private Glitch console which you can use to authenticate with your Datasette instance there as the root user.
• I also released a new version of datasette-auth-tokens, which allows users to configure API tokens to be used to access a private Datasette instance. It now lets you store tokens in a database table based on a configured SQL query. The :_random_chars_32 magic parameter mentioned above can be used to help create new tokens.

What’s next?

I’ve already slipped one feature into the Datasette 0.46 milestone, but my focus from here on should really be on getting everything in place for Datasette 1.0.