Datasette 0.54, and querying a database by drawing on a map

Datasette Weekly(ish) volume 6

Since the last edition of this newsletter I’ve made 33 releases across 13 different Datasette ecosystem projects. Some of the highlights.

datasette-leaflet-freedraw

Datasette supports parameterized SQL queries: if you compose a SQL query with a placeholder such as “select * from counties where population > :number” Datasette will spot the :number parameter and turn it into a form field on a page:

I’ve been planning to add custom form field widgets for a while. datasette-leaflet-freedraw is a new plugin which starts exploring this concept, by enhancing any fields ending in “freedraw” with a map widget that lets users draw a shape, which will be converted to a GeoJSON MultiPolygon ready to be sent back to Datasette.

Here’s an interactive demo:

You can read more about this plugin in Drawing shapes on a map to query a SpatiaLite database.

Datasette 0.54

I released Datasette 0.54 yesterday. You can read the annotated release notes on my blog, but the two two big new features in this release are the _internal SQLite in-memory database storing details of all connected databases and tables, and support for JavaScript modules in plugins and additional scripts.

The _internal database

I want Database to be able to handle dozens of attached database files containing potentially thousands of tables. To support this, the Datasette homepage will need to get a lot smarter: it needs to provide pagination and search and filtering against the list of tables themselves.

Datasette is already pretty good at providing paginated search and filtering! The new _internal database is the first step to providing this feature: it's an in-memory database which is automatically updated with details of all of the attached databases, plus their columns, foreign keys and indexes.

As part of building this I added support for shared, named in-memory databases - so plugins can now create an in-memory database that will persist for the lifetime of the Datasette process and can be accessed from multiple connections.

You can browse a demo of the _internal database by signing into the demo as root and then accessing latest.datasette.io/_internal

JavaScript modules

JavaScript modules are supported by modern browsers and allow you to use the “import” and “export” keywords from ECMAScript 2015 without needing a build step.

They only work in <script> blocks with the type="module" attribute, so Datasette now has the ability to include scripts with that attribute, for both plugins and extra scripts loaded through configuration.

A big benefit of native modules is that they help avoid accidentally loading the same script twice. There are three Datasette plugins that use Leaflet maps now - datasette-cluster-map, datasette-leaflet-geojson and the new datasette-leaflet-freedraw - so I've started working on a new base plugin called datasette-leaflet which makes the latest Leaflet available for all of them to use via the modules system.

datasette-css-properties and datasette-export-notebook

Two more new plugins: these use Datasette's register_output_renderer hook to add extra options for getting data back out of Datasette.

I wrote about datasette-css-properties here - it's a very weird plugin! It was inspired by Custom Properties as State by Chris Coyier, which proposed server APIs that return CSS files defining custom properties that can be used to style pages.

I'm not sure if this is a good idea, but the joy of plugins is that you can try out ideas like this without risking damage to the integrity of the core project!

datasette-export-notebook is more obviously useful: it adds instructions for exporting data from Datasette into Jupyter and Observable notebooks. You can try that out here against the Covid-19 Datasette.

Datasette Office Hours

I've now had 17 conversations with people about Datasette in office hours sessions. It's been absolutely wonderful! Talking to people about what they're building, or what they might build, is incredibly valuable for helping me decide what to work on next, and it's also just really fun talking to people about the project.

I plan to continue running office hours sessions on Friday for the forseeable future. If you'd like to chat about the project you can grab a slot with me here.