Simon Willison's Weblog: Datasette Lite

Weeknotes: Datasette Lite, s3-credentials, shot-scraper, datasette-edit-templates and more

2022-09-16T02:55:03+00:00

Despite distractions from AI I managed to make progress on a bunch of different projects this week, including new releases of s3-credentials and shot-scraper, a new datasette-edit-templates plugin and a small but neat improvement to Datasette Lite.

Better GitHub support for Datasette Lite

Datasette Lite is Datasette running in WebAssembly. Originally intended as a cool tech demo it's quickly becoming a key component of the wider Datasette ecosystem - just this week I saw that mySociety are using it to help people explore their WhatDoTheyKnow Authorities Dataset.

One of the neat things about Datasette Lite is that you can feed it URLs to CSV files, SQLite database files and even SQL initialization scripts and it will fetch them into your browser and serve them up inside Datasette. I wrote more about this capability in Joining CSV files in your browser using Datasette Lite.

There's just one catch: because those URLs are fetched by JavaScript running in your browser, they need to be served from a host that sets the Access-Control-Allow-Origin: * header (see MDN). This is not an easy thing to explain to people!

The good news here is that GitHub makes every public file (and every Gist) hosted on GitHub available as static hosting with that magic header.

The bad news is that you have to know how to construct that URL! GitHub's "raw" links redirect to that URL, but JavaScript fetch() calls can't follow redirects if they don't have that header - and GitHub's redirects do not.

So you need to know that if you want to load the SQLite database file from this page on GitHub:

https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite

You first need to rewrite that URL to the following, which is served with the correct CORS header:

https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite

Asking human's to do that by hand isn't reasonable. So I added some code!

const githubUrl = /^https:\/\/github.com\/(.*)\/(.*)\/blob\/(.*)(\?raw=true)?$/;

function fixUrl(url) {
  const matches = githubUrl.exec(url);
  if (matches) {
    return `https://raw.githubusercontent.com/${matches[1]}/${matches[2]}/${matches[3]}`;
  }
  return url;
}

Fun aside: GitHub Copilot auto-completed that return statement for me, correctly guessing the URL string I needed based on the regular expression I had defined several lines earlier.

Now any time you feed Datasette Lite a URL, if it's a GitHub page it will automatically rewrite it to the CORS-enabled equivalent on the raw.githubusercontent.com domain.

Some examples:

https://lite.datasette.io/?url=https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite - that Chinook SQLite database example (from here)
https://lite.datasette.io/?csv=https://github.com/simonw/covid-19-datasette/blob/6294ade30843bfd76f2d82641a8df76d8885effa/us_census_state_populations_2019.csv - US censes populations by state, from my simonw/covid-19-datasette repo

datasette-edit-templates

I started working on this plugin a couple of years ago but didn't get it working. This week I finally closed the initial issue and shipped a first alpha release.

It's pretty fun. On first launch it creates a _templates_ table in your database. Then it allows the root user (run datasette data.db --root and click the link to sign in as root) to edit Datasette's default set of Jinja templates, writing their changes to that new table.

Datasette uses those templates straight away. It turns the whole of Datasette into an interface for editing itself.

Here's an animated demo showing the plugin in action:

The implementation is currently a bit gnarly, but I've filed an issue in Datasette core to help clear some of it up.

s3-credentials get-objects and put-objects

I built s3-credentials to solve my number one frustration with AWS S3: the surprising level of complexity involved in issuing IAM credentials that could only access a specific S3 bucket. I introduced it in s3-credentials: a tool for creating credentials for S3 buckets.

Once you've created credentials, you need to be able to do stuff with them. I find the default AWS CLI tools relatively unintuitive, so s3-credentials has continued to grow other commands as and when I feel the need for them.

The latest version, 0.14, adds two more: get-objects and put-objects.

These let you do things like this:

s3-credentials get-objects my-bucket -p "*.txt" -p "static/*.css"

This downloads every key in my-bucket with a name that matches either of those patterns.

s3-credentials put-objects my-bucket one.txt ../other-directory

This uploads one.txt and the whole other-directory folder with all of its contents.

As with most of my projects, the GitHub issues threads for each of these include a blow-by-blow account of how I finalized their design - #68 for put-objects and #78 for get-objects.

shot-scraper --log-requests

shot-scraper is my tool for automating screenshots, built on top of Playwright.

Its latest feature was inspired by Datasette Lite.

I have an ongoing ambition to get Datasette Lite to work entirely offline, using Service Workers.

The first step is to get it to work without loading external resources - it currently hits PyPI and a separate CDN multiple times to download wheels every time you load the application.

To do that, I need a reliable list of all of the assets that it's fetching.

Wouldn't it be handy If I could run a command and get a list of those resources?

The following command now does exactly that:

shot-scraper https://lite.datasette.io/ \
  --wait-for 'document.querySelector("h2")' \
  --log-requests requests.log

Here' the --wait-for is needed to ensure shot-scraper doesn't terminate until the application has fully loaded - detected by waiting for a <h2> element to be added to the page.

The --log-requests bit is a new feature in shot-scraper 0.15: it logs out a newline-delimited JSON file with details of all of the resources fetched during the run. That file starts like this:

{"method": "GET", "url": "https://lite.datasette.io/", "size": 10516, "timing": {...}}
{"method": "GET", "url": "https://plausible.io/js/script.manual.js", "size": 1005, "timing": {...}}
{"method": "GET", "url": "https://latest.datasette.io/-/static/app.css?cead5a", "size": 16230, "timing": {...}}
{"method": "GET", "url": "https://lite.datasette.io/webworker.js", "size": 4875, "timing": {...}}
{"method": "GET", "url": "https://cdn.jsdelivr.net/pyodide/v0.20.0/full/pyodide.js", "size": null, "timing": {...}}

This is already pretty useful... but wouldn't it be more useful if I could explore that data in Datasette?

That's what this recipe does:

shot-scraper https://lite.datasette.io/ \
  --wait-for 'document.querySelector("h2")' \
  --log-requests - | \
  sqlite-utils insert /tmp/datasette-lite.db log - --flatten --nl

It's piping the newline-delimited JSON to sqlite-utils insert which then inserts it, using the --flatten option to turn that nested timing object into a flat set of columns.

I decided to share it by turning it into a SQL dump and publishing that to this Gist. I did that using the sqlite-utils memory command to convert it to a SQL dump like so:

shot-scraper https://lite.datasette.io/ \
  --wait-for 'document.querySelector("h2")' \
  --log-requests - | \
  sqlite-utils memory stdin:nl --flatten --dump > dump.sql

stdin:nl means "read from standard input and treat that as newline-delimited JSON". Then I run a select * command and use --dump to output that to dump.sql, which I pasted into a new Gist.

So now I can open the result in Datasette Lite!

Datasette on Sandstorm

Sandstorm is "an open source platform for self-hosting web apps". You can think of it as an easy to use UI over a Docker-like container platform - once you've installed it on a server you can use it to manage and install applications that have been bundled for it.

Jacob Weisz has been doing exactly that for Datasette. The result is Datasette in the Sandstorm App Market.

You can see how it works in the ocdtrekkie/datasette-sandstorm repo. I helped out by building a small datasette-sandstorm-support plugin to show how permissions and authentication can work against Sandstorm's custom HTTP headers.

Releases this week

s3-credentials: 0.14 - (15 releases total) - 2022-09-15
A tool for creating credentials for accessing S3 buckets
shot-scraper: 0.16 - (21 releases total) - 2022-09-15
A command-line utility for taking automated screenshots of websites
datasette-edit-templates: 0.1a0 - 2022-09-14
Plugin allowing Datasette templates to be edited within Datasette
datasette-sandstorm-support: 0.1 - 2022-09-14
Authentication and permissions for Datasette on Sandstorm
datasette-upload-dbs: 0.1.2 - (3 releases total) - 2022-09-09
Upload SQLite database files to Datasette
datasette-upload-csvs: 0.8.2 - (13 releases total) - 2022-09-08
Datasette plugin for uploading CSV files and converting them to database tables

TIL this week

Tags: plugins, projects, datasette, weeknotes, s3-credentials, shot-scraper, datasette-lite, github-copilot

Analyzing ScotRail audio announcements with Datasette - from prototype to production

2022-08-21T02:04:59+00:00

Scottish train operator ScotRail released a two-hour long MP3 file containing all of the components of its automated station announcements. Messing around with them is proving to be a huge amount of fun.

I now have a Datasette instance running at scotrail.datasette.io with a table of 2,440 MP3 files of announcement snippets. You can search them, filter them and play them there - either individual clips or as a sequence of clips attached together.

A few examples of things you can do with it:

Here are all of 421 reasons that a train might be delayed or cancelled.
A search for bomb turns up four audio clips about a "wartime bomb near the railway". Searching for theft is fun too.
Here's every clip in the Special Train category.
Here are 100 random clips - hit "Play 100 MP3s on this page" to listen to them all in sequence.
This page generates a random apology by gluing together six different clips, mad libs style.

The rest of this post is a detailed account of how I built this Datasette instance, from early prototypes on Datasette Lite to a full, customized instance of Datasette running on Vercel and with build and deploy automation powered by GitHub Actions.

Origins of the data

Yesterday, Daily Record journalist Jon Brady tweeted this:

Bizarre FOI find of the day: Scotrail has openly published a two-hour long sound file containing every single element of its automated station announcements https://files.scotrail.co.uk/ScotRail_Station_Announcements_June2022.mp3

An hour and a half later, developer Matt Eason replied:

If anyone needs this split into 2,440 individual mp3s - because why wouldn't you - I've put them here: https://drive.google.com/drive/folders/172W6sXnvlr7UcNLipO8BTw417_KRz9c5?usp=sharing

And if anyone wants to help transcribe all the files, here's a shared sheet, which has a good chance of descending into chaos:

https://docs.google.com/spreadsheets/d/1jAtNLBXLYwTraaC_IGAAs53jJWWEQUtFrocS5jW31JM/edit#gid=2073317291

(Matt later shared notes about how he used WavePad to do this.)

It took two hours for a collective of anonymous volunteers to dive through all of the files and transcribe every single one of them.

I first heard about this all earlier today, when Matt shared his project Ambient Scotrail Beats - an absolutely superb project that overlays random snippets of ScotRail announcement on top of ambient "lofi hip hop radio" - or any YouTube clip you want to enhance in that way.

I love how Matt credits Voiceover artist Alison McKay, who originally recorded the announcements for ScotRail, in his work.

I couldn't resist throwing the data into Datasette to see what kind of things I could do with it.

Starting out with Datasette Lite

My first step to explore the data was to try it out in Datasette Lite, my build of Datasette that runs in WebAssembly entirely in the browser.

The great thing about Datasette Lite is that you don't need to deploy anything anywhere in order to use it for a new project.

I exported a CSV file from the Google Sheet, pasted it into this GitHub Gist, grabbed the URL to the raw CSV file and pasted that into Datasette Lite.

I ran it through GitHub Gists because Datasette Lite needs data to have open CORS headers in order to load it, and GitHub Gists (and files on GitHub itself) have these headers.

Update: This turns out to be unneccessary: Google Sheets serves these headers too, so given a URL to a Google Sheets CSV export Datasette Lite can open that directly.

Here's the result in Datasette Lite. Just being able to facet by Category was already pretty interesting:

I started a Twitter thread sharing my progress so far. This ended up running for the next eight hours as my experiments got increasingly ambitious.

Adding an audio player

Wouldn't it be neat if you could listen to those MP3 files right there in the Datasette interface?

A few days ago I added plugin support to Datasette Lite - you can now add ?install=name-of-plugin to the URL to install and use a new plugin.

This seemed like the perfect opportunity to put that new capability to good use!

Matt had uploaded all of the individual MP3 files to his scotrail-announcements-june-2022 GitHub repository. This meant that you could get the URL for a snippet called 0008.mp3 by constructing the following URL:

https://github.com/matteason/scotrail-announcements-june-2022/raw/main/announcements/0008.mp3

What I needed was a Datasette plugin that could spot URLs ending in .mp3 and turn them into an interactive audio player.

The easiest way to create a player in modern HTML is like this:

<audio controls src="https://github.com/matteason/scotrail-announcements-june-2022/raw/main/announcements/0008.mp3">
  Audio not supported
</audio>

Datasette has a render_cell() plugin hook which allows plugins to customize the way individual table cells are rendered.

Here's the full implementation of a plugin that turns .mp3 URLs into audio elements:

from datasette import hookimpl
from markupsafe import Markup, escape


@hookimpl
def render_cell(value):
    if not isinstance(value, str):
        return
    if value.endswith(".mp3") and (
        value.startswith("http://")
        or value.startswith("https://")
        or value.startswith("/")
    ):
        return Markup(
            '<audio controls src="{}">Audio not supported</audio>'.format(escape(value))
        )

I fired up a new plugin using my datasette-plugin cookiecutter/GitHub repository template called datasette-mp3-audio, dropped in that implementation, added a basic test and shipped it to PyPI.

Now, adding ?install=datasette-mp3-audio to the Datasette Lite URL would install that plugin!

I built a new copy of the CSV file with those full MP3 URLs in it, and loaded that up in Datasette Lite:

https://lite.datasette.io/?install=datasette-mp3-audio&csv=https://gist.githubusercontent.com/simonw/0a30d52feeb3ff60f7d8636b0bde296b/raw/c078a9e5a0151331e2e46c04c1ebe7edc9f45e8c/scotrail-announcements.csv#/data/scotrail-announcements

Being able to listen to the clips right there in the Datasette interface made the whole project instantly enormously more fun and interesting.

Upgrading to a full Datasette

Prototyping this on Datasette Lite had been fast and fun, but the long load times were beginning to grate - downloading and running a full copy of Python-compiled-to-WebAssembly every time someone loads the site didn't make for a great first impression.

I decided to ship a server-side Datasette instance. The database is tiny - just 504KB - so I decided to use Vercel for this. I tend to switch to Cloud Run when my databases grow above about 50MB.

I like to automate my projects as early as possible, so I started a simonw/scotrail-datasette GitHub repository so I could run the deploys using GitHub Actions.

Fetching the CSV with GitHub Actions

First, I wanted a copy of the data in my repository - so that any future changes to the Google Sheet wouldn't break my project.

I followed my Git scraping pattern and built a quick GitHub Actions workflow to download the Google Sheets data as CSV and stash it in the repository:

name: Fetch CSV from Google Sheets

on:
  workflow_dispatch:

permissions:
  contents: write

jobs:
  fetch-csv:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v3
    - name: Set up Python 3.10
      uses: actions/setup-python@v3
      with:
        python-version: "3.10"
        cache: "pip"
    - name: Install dependencies
      run: |
        pip install -r requirements.txt
    - name: Fetch
      run: |        
        curl -s -L 'https://docs.google.com/spreadsheets/d/1jAtNLBXLYwTraaC_IGAAs53jJWWEQUtFrocS5jW31JM/export?format=csv' | \
          sqlite-utils memory stdin:csv \
          'select [File], [NRE ID], Transcription, Category, Notes, Timestamp, [mp3 file] from stdin' \
          --no-detect-types \
          --csv > announcements.csv
    - name: Commit and push
      run: |-
        git config user.name "Automated"
        git config user.email "actions@users.noreply.github.com"
        git add -A
        timestamp=$(date -u)
        git commit -m "${timestamp}" || exit 0
        git pull --rebase
        git push

I was hoping I could just grab the CSV file and save it directly to the repo, but it turned out the raw data had some weirdness to it:

% curl -s -L 'https://docs.google.com/spreadsheets/d/1jAtNLBXLYwTraaC_IGAAs53jJWWEQUtFrocS5jW31JM/export?format=csv' | head -n 5 
File,NRE ID,Transcription,Category,Notes,Timestamp,Length,mp3 file,,,,,,,,,,,,,,,,,,,,,,,,
1869,BAI,Blairhill,Destination,,,00:00:00,1869.mp3,,,,,,,,,,,,,,,,,,,,,,,,
1703,BYK,Bentley,Destination,,,00:00:00,1703.mp3,,,,,,,,,,,,,,,,,,,,,,,,
0083,HEX,Hexham,Destination,,,00:00:00,0083.mp3,,,,,,,,,,,,,,,,,,,,,,,,
0004,,South West Trains service to,Train operating company,Bit of a detour,0:00:07,00:00:02,0004.mp3,Progress,#DIV/0!,Progress,#DIV/0!,Progress,#DIV/0!,,,,,,,,,,,,,,,,,,

What's with all of those ,,,,, blank lines?

It turned out enthusiastic data entry volunteers had scattered some emoji around in the spreadsheet grid to the right of the data, resulting in a whole bunch of extra blank columns in the CSV!

I decided to solve this using by using my sqlite-utils tool to clean up the data - in particular, the sqlite-utils memory mechanism. That's what this bit does:

curl -s -L 'https://docs.google.com/spreadsheets/d/1jAtNLBXLYwTraaC_IGAAs53jJWWEQUtFrocS5jW31JM/export?format=csv' | \
    sqlite-utils memory stdin:csv \
    'select [File], [NRE ID], Transcription, Category, Notes, Timestamp, [mp3 file] from stdin' \
    --no-detect-types \
    --csv > announcements.csv

This fetches the raw CSV data with curl and loads it into an in-memory database, using stdin:csv to hint to sqlite-utils that the data being piped to standard input is in CSV format rather than the default JSON.

Then it runs a SQL query directly against that in-memory database to pull out just the columns I want to keep.

The tool automatically detects types in CSV data. In this particular case filenames such as 0001 were being truncated to the integer 1, which I didn't want to happen - so I added the --no-detect-types option to turn that off.

Finally, I told it to output --csv and write that to the announcements.csv file.

The workflow ends by writing that new file back to the repository and pushing it, if it has changed.

The workflow is triggered on workflow_dispatch, which means I have to click a button in the GitHub Actions UI to trigger it. I clicked that button, waited a few seconds and GitHub Actions wrote this CSV file back to my repo.

Building the SQLite database

The next step was to write a script that would load that CSV file into a SQLite database. Again, I used sqlite-utils for this. Here's the build-db.sh script I wrote:

#!/bin/bash
sqlite-utils insert scotrail.db announcements announcements.csv --csv --pk File
sqlite-utils transform scotrail.db announcements \
  --rename 'NRE ID' NRE_ID \
  --rename 'mp3 file' 'mp3' \
  -o File \
  -o Transcription \
  -o Category \
  -o mp3 \
  -o Notes \
  -o Timestamp \
  -o NRE_ID
sqlite-utils scotrail.db "update announcements set mp3 = 'https://github.com/matteason/scotrail-announcements-june-2022/raw/main/announcements/' || mp3"

# Enable search
sqlite-utils enable-fts scotrail.db announcements Transcription --tokenize porter

It took a few iterations to get this exactly right.

The first line imports the CSV data into a table.

The second line does most of the work: it uses sqlite-utils transform to rename a couple of columns, then change the order of the columns in that table to present them in a more useful order in the Datasette UI.

Next the script updates the mp3 column to turn those filenames into full URLs.

Finally, it configures full-text search on the Transcription column, with porter stemming enabled.

Deploying to Vercel

Build script in place, the last step was to automate the process of building the database and then deploying it to Vercel.

I wrote a second workflow for that:

name: Build and deploy

on:
  workflow_dispatch:
  push:

jobs:
  build-and-deploy:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v3
    - name: Set up Python 3.10
      uses: actions/setup-python@v3
      with:
        python-version: "3.10"
        cache: "pip"
    - name: Install dependencies
      run: |
        pip install -r requirements.txt
    - name: Build SQLite database
      run: ./build-db.sh
    - name: Deploy to Vercel
      env:
        VERCEL_TOKEN: ${{ secrets.VERCEL_TOKEN }}
      run: |-
        datasette publish vercel scotrail.db \
          --token $VERCEL_TOKEN \
          --scope datasette \
          --project scotrail \
          --install datasette-mp3-audio \
          --metadata metadata.yml

This workflow runs on every push to the repo, but can also be triggered manually.

It installs Python 3.10 and the dependencies I need, then runs the build script to build the database.

The interesting bit is that last block, titled "Deploy to Vercel".

I created a Vercel API token in their dashboard and saved that as the VERCEL_TOKEN secret in the repository.

The final block uses the datasette-publish-vercel plugin to deploy the site:

datasette publish vercel scotrail.db \
  --token $VERCEL_TOKEN \
  --scope datasette \
  --project scotrail \
  --install datasette-mp3-audio \
  --metadata metadata.yml

Here we're deploying the scotrail.db database that was created by our earlier build script. I'm using --scope datasette to specify the Vercel organization that I want to deploy to, and --project scotrail to specify the project.

Vercel will create a new project the first time you use that name, then deploy to that existing project on subsequent runs.

I install the datasette-mp3-audio plugin, described earlier.

Finally, I specify --metadata metadata.yml to upload a metadata file with details to be displayed on the homepage of the site. You can see an early version of that file here.

This worked! I pushed the new workflow, it ran, and Vercel made my new application available at scotrail.vercel.app. I used their dashboard to assign a vanity URL of scotrail.datasette.io.

Adding a "play all" button

I quickly found myself frustrated at having to click the play button on so many different clips!

I decided to see if I could use JavaScript to hit play on everything in succession instead.

I opened up the Firefox DevTools console and started messing around in JavaScript. I eventually found that this recipe did what I wanted:

function playAllAudios() {
  let audios = Array.from(document.querySelectorAll('audio'));
  function playNext() {
    if (!audios.length) {
      return;
    }
    let next = audios.shift();
    next.addEventListener('ended', playNext);
    next.play();
  }
  playNext();
}
playAllAudios();

I opened an issue to record my prototype.

This was while I was still working with Datasette Lite, which still has some limitations in terms of executing JavaScript from plugins.

Once I'd upgraded to a full Datasette, I revisited the issue.

I figured out an extended version of my JavaScript prototype which would add a "Play 33 MP3s on this page" button to any page with two or more audio elements. Here's the code. I implemented that in datasette-mp3-audio, shipped a 0.2 release and added that to the site.

The new button is really fun! Here it is on a query that returns 100 random snippets:

Generating random apologies with a SQL query

To really exercise this new feature, I decided to try and build a mad lib.

I figured out a SQL query that would construct a random apology, stringing together random snippets from a number of different categories.

I used CTEs (common table expressions - the with x as (query) blocks) to pick each of the random components of the final sentence.

Here's the query I came up with, with some extra inline commentary:

with apology as (
    select Transcription, mp3 from announcements where
        Category = 'Apology'
    order by random() limit 1
),

The Apology category only has 8 rows, each along the lines of "I am sorry to announce that the" or "We are sorry to announce that the".

train_company as (
    select Transcription, mp3 from announcements where
        Category = 'Train operating company'
        and Transcription like '%to%'
    order by random() limit 1
),

There are 76 Train operating company snippets, in formats that include "Midland Main Line service from" and "Southeastern Trains service to". I filtered for just the ones with to in the text.

destination as (
    select Transcription, mp3 from announcements where
        Category = 'Destination'
    order by random() limit 1
),

There are 1,271 potential destinations - single word names of places, like "Bath Spa" or "Dunfermline Queen Margaret".

cancelled as (
    select Transcription, mp3 from announcements where
        Transcription = 'has been cancelled'
    limit 1
),
due_to as (
    select Transcription, mp3 from announcements where
        Transcription = 'due to'
    limit 1
),

There's probably a neater way to do this - I just wanted the literal text "has been cancelled due to" in every result.

reason as (
    select Transcription, mp3 from announcements where
        Category = 'Reason'
    order by random() limit 1
),

These are the really fun ones. There are 421 snippets marked "Reason", everything from "bad weather conditions" to "...a burst water main near the railway, yesterday" to "a wartime bomb near the railway earlier today"!

The final piece of the query combined these together into a list, with an artificially added sort column to guarantee the order (which I don't think is strictly necessary in SQLite, but better safe than sorry):

combined as (
  select 1 as sort, * from apology
  union select 2, * from train_company
  union select 3, * from destination
  union select 4, * from cancelled
  union select 5, * from due_to
  union select 6, * from reason
)
select * from combined order by sort

This totally works!

Since the bookmarked query is a pretty long URL I decided to use Datasette's canned query mechanism to turn it into a more easily discovered page:

scotrail.datasette.io/scotrail/random_apology

I also chose to hide the SQL query by default, to put the essential "Play 6 MP3s on this page" button above the fold.

I linked to it from the site homepage too, using description_html in the site metadata.yml:

Assembling full sentences from user input

I wanted the ability to stitch together my own custom phrases - specifying a sequence of clips to be played in order.

I decided to do this using an input that was a comma separated list of search terms:

i am sorry, scotrail, from, bath spa, is delayed, due to, bomb

My idea was to split this into search terms and return the first clip for each of those, in order. Then I could hit the "play all" button to listen to them in sequence.

Try this feature out - I built it as another canned query. Click the show link to see the full SQL query.

Here's that SQL, with additional comments:

with phrases as (
  select
    key, value
  from
    json_each('["' || replace(:terms, ',', '","') || '"]')
),

This is the trickiest bit. I needed to split that user input of a comma separated string into separate terms, so I could run a search against each one.

The trick I'm using here is to first convert that string into JSON. Here's a simplified version:

select '["' || replace(:terms, ',', '","') || '"]'

Try that here.

Note that users can break this by including double quotes in their input - I'm OK with that, it just means the query will exit with an error.

Once I have a JSON list of terms I can use the SQLite json_each table-valued function. This turns that JSON into a table you can join against other things.

Try select * from json_each('["one", "two", "three"]') to see what that table looks like:

key	value	type	atom	id	fullkey	path
0	one	text	one	1	$[0]	$
1	two	text	two	2	$[1]	$
2	three	text	three	3	$[2]	$

We only need the key column (to maintain the order of the search terms) and the value column (the search term itself).

Back to the next part of that big SQL query:

matches as (select
  phrases.key,
  phrases.value,
  (
    select File from announcements
    where announcements.Transcription like '%' || trim(phrases.value) || '%'
    order by length(announcements.Transcription)
    limit 1
  ) as File
from
  phrases
),

This is the part that runs a search for each of the phrases from that JSON list.

It's using a subselect in the select clause to run the actual search.

Try a variant of that subselect to see how it works - it looks for clips that match a like query of %search term%, then orders by length to find the shortest match. This is so that if you type the you'll get back the word "the" and not the destination "Atherton".

results as (
  select key, announcements.Transcription, announcements.mp3
  from announcements join matches on announcements.File = matches.File
  order by key
)

We only returned the File column from our subselect, because when you put a subselect in a SELECT you can only return a single column.

This next bit joins our matches back against the announcements table to get the other data we need: the transcription text and the mp3 file.

Run a select * from results query to see our results so far.

select
  'Combined sentence:' as mp3,
  group_concat(Transcription, ' ') as Transcription,
  -1 as key
from results
  union
select
  mp3, Transcription, key
from results
order by key

This last step is an extra flourish. I decided it would be useful to show the full combined sentence as text, in addition to presenting the individual clips.

I'm using a union for this. The first select query sets the mp3 column to the text "Combined sentence:" and then uses the group_concat() SQLite function to join all of the Transcription values together as a space-separated list. It adds on a -1 as the key so we can sort by that later and have this artificial row show up first.

Then it runs a union against the rows from that results CTE.

The final output looks like this:

mp3	Transcription	key
Combined sentence:	I am sorry to announce that the ScotRail service from Bath Spa is delayed due to A wartime bomb near the railway	-1
.../0031.mp3	I am sorry to announce that the	0
.../0032.mp3	ScotRail	1
.../1778.mp3	service from	2
.../0006.mp3	Bath Spa	3
.../1750.mp3	is delayed	4
.../1528.mp3	due to	5
.../0946.mp3	A wartime bomb near the railway	6

Make fun stuff with the API

As you can probably tell, I had a ridiculous amount of fun playing with this data.

If you want to build some things with this, you should know that every page on the Datasette instance has an accompanying API. Look for the JSON links on each page - Datasette serves them with CORS headers so you can fetch data from them from any domain on the Web.

I recommend adding ?_shape=objects to the array, since this shape of data is closer to the breaking change I have planned for Datasette 1.0. Here's an example returning one of those random apologies:

https://scotrail.datasette.io/scotrail/random_apology.json?_shape=objects

I also installed the datasette-graphql plugin, so if you want to use GraphQL instead you can do something like this:

{
  announcements(filter: {Category: {eq: "Special train"}}) {
    nodes {
      File
      Transcription
      Category
      mp3
      Notes
      Timestamp
      NRE_ID
    }
  }
}

That query returns the 12 rows in the Special train category.

Let me know on Twitter if you build something fun!

Tags: projects, prototyping, datasette, datasette-lite

Plugin support for Datasette Lite

2022-08-17T18:20:59+00:00

I've added a new feature to Datasette Lite, my distribution of Datasette that runs entirely in the browser using Python and SQLite compiled to WebAssembly. You can now install additional Datasette plugins by passing them in the URL.

Datasette Lite background

Datasette Lite runs Datasette in the browser. I initially built it as a fun technical proof of concept, but I'm increasingly finding it to be a genuinely useful tool for quick ad-hoc data analysis and publication. Not having any server-side components at all makes it effectively free to use without fear of racking up cloud computing costs for a throwaway project.

You can read more about Datasette Lite in these posts:

Datasette Lite: a server-side Python web application running in a browser
Joining CSV files in your browser using Datasette Lite
Scraping data into Datasette Lite shows an example project where I scraped PSF board resolutions, stored the results in a CSV file in a GitHub Gist and then constructed this URL to open the result in Datasette Lite and execute a SQL query.

Adding plugins to Datasette Lite

One of Datasette's key features is support for plugins. There are over 90 listed in the plugin directory now, with more emerging all the time. They're a fantastic way to explore new feature ideas and extend the software to handle non-default use cases.

Plugins are Python packages, published to PyPI. You can add them to Datasette Lite using the new ?install=name-of-plugin query string parameter.

Here's an example URL that loads the datasette-jellyfish plugin, which adds new SQL functions for calculating distances between strings, then executes a SQL query that demonstrates that plugin:

https://lite.datasette.io/?install=datasette-jellyfish#/fixtures?sql=SELECT%0A++++levenshtein_distance%28%3As1%2C+%3As2%29%2C%0A++++damerau_levenshtein_distance%28%3As1%2C+%3As2%29%2C%0A++++hamming_distance%28%3As1%2C+%3As2%29%2C%0A++++jaro_similarity%28%3As1%2C+%3As2%29%2C%0A++++jaro_winkler_similarity%28%3As1%2C+%3As2%29%2C%0A++++match_rating_comparison%28%3As1%2C+%3As2%29%3B&s1=barrack+obama&s2=barrack+h+obama

That URL uses ?install=datasette-jellyfish to install the plugin, then executes the following SQL query:

SELECT
    levenshtein_distance(:s1, :s2),
    damerau_levenshtein_distance(:s1, :s2),
    hamming_distance(:s1, :s2),
    jaro_similarity(:s1, :s2),
    jaro_winkler_similarity(:s1, :s2),
    match_rating_comparison(:s1, :s2);

It sets s1 to "barack obama" and s2 to "barrack h obama".

Plugin compatibility

Unfortunately, many existing Datasette plugins aren't yet compatible with Datasette Lite. Most importantly, visualization plugins such as datasette-cluster-map and datasette-vega don't work.

This is because I haven't yet solved the challenge of loading additional JavaScript and CSS into Datasette Lite - see issue #8.

Here's the full list of plugins that I've confirmed work with Datasette Lite so far:

datasette-packages - Show a list of currently installed Python packages - demo
datasette-dateutil - dateutil functions for Datasette - demo
datasette-schema-versions - Datasette plugin that shows the schema version of every attached database - demo
datasette-debug-asgi - Datasette plugin for dumping out the ASGI scope. - demo
datasette-query-links - Turn SELECT queries returned by a query into links to execute them - demo
datasette-json-html - Datasette plugin for rendering HTML based on JSON values - demo
datasette-haversine - Datasette plugin that adds a custom SQL function for haversine distances - demo
datasette-jellyfish - Datasette plugin that adds custom SQL functions for fuzzy string matching, built on top of the Jellyfish Python library - demo
datasette-pretty-json - Datasette plugin that pretty-prints any column values that are valid JSON objects or arrays. - demo
datasette-yaml - Export Datasette records as YAML - demo
datasette-copyable - Datasette plugin for outputting tables in formats suitable for copy and paste - demo

How it works

The implementation is pretty simple - it can be seen in this commit. The short version is that ?install= options are passed through to the Python web worker that powers Datasette Lite, which then runs the following:

for install_url in install_urls:
    await micropip.install(install_url)

micropip is a component of Pyodide which knows how to install pure Python wheels directly from PyPI into the browser's emulated Python environment. If you open up the browser devtools networking panel you can see that in action!

Since the ?install= parameter is being passed directly to micropip.install() you don't even need to provide names of packages hosted on PyPI - you could instead provide the URL to a wheel file that you're hosting elsewhere.

This means you can use ?install= as a code injection attack - you can install any Python code you want into the environent. I think that's fine - the only person who will be affected by this is the user who is viewing the page, and the lite.datasette.io domain deliberately doesn't have any cookies set that could cause problems if someone were to steal them in some way.

Tags: plugins, projects, pypi, datasette, webassembly, pyodide, datasette-lite, cors

Joining CSV files in your browser using Datasette Lite

2022-06-20T21:20:16+00:00

I added a new feature to Datasette Lite - my version of Datasette that runs entirely in your browser using WebAssembly (previously): you can now use it to load one or more CSV files by URL, and then run SQL queries against them - including joins across data from multiple files.

Your CSV file needs to be hosted somewhere with access-control-allow-origin: * CORS headers. Any CSV file hosted on GitHub provides these, if you use the link you get by clicking on the "Raw" version.

Loading CSV data from a URL

Here's the URL to a CSV file of college fight songs collected by FiveThirtyEight in their data repo as part of the reporting for this story a few years ago:

https://raw.githubusercontent.com/fivethirtyeight/data/master/fight-songs/fight-songs.csv

You can pass this to Datasette Lite in two ways:

You can load the web app, click the "Load data by URL to a CSV file" button and paste in the URL
Or you can pass it as a ?csv= parameter to the application, like this: https://lite.datasette.io/?csv=https://raw.githubusercontent.com/fivethirtyeight/data/master/fight-songs/fight-songs.csv

Once Datasette has loaded, a data database will be available with a single table called fight-songs.

As you navigate around in Datasette the URL bar will update to reflect current state - which means you can deep-link to table views with applied filters and facets:

https://lite.datasette.io/?csv=https://raw.githubusercontent.com/fivethirtyeight/data/master/fight-songs/fight-songs.csv#/data/fight-songs?_facet=conference&_facet=student_writer&_facet=official_song

Or even link to the result of a custom SQL query:

https://lite.datasette.io/?csv=https://raw.githubusercontent.com/fivethirtyeight/data/master/fight-songs/fight-songs.csv#/data?sql=select+school%2C+conference%2C+song_name%2C+writers%2C+year%2C+student_writer+spotify_id+from+%5Bfight-songs%5D+order+by+rowid+limit+101

Loading multiple files and joining data

You can pass the ?csv= parameter more than once to load data from multiple CSV files into the same virtual data database. Each CSV file will result in a separate table.

For this demo I'll use two CSV files.

The first is us-counties-recent.csv from the NY Times covid-19-data repository, which lists the most recent numbers for Covid cases for every US county.

The second is us_census_county_populations_2019.csv, a CSV file listing the population of each county according to the 2019 US Census which I extracted from this page on the US Census website.

Both of those tables include a column called fips, representing the FIPS county code for each county. These 4-5 digit codes are ideal for joining the two tables.

Here's a SQL query which joins the two tables, filters for the data for the most recent date represented (using where date = (select max(date) from [us-counties-recent])) and calculates cases_per_million using the cases and the population:

select
  [us-counties-recent].*,
  us_census_county_populations_2019.population,
  1.0 * [us-counties-recent].cases / us_census_county_populations_2019.population * 1000000 as cases_per_million
from
  [us-counties-recent]
  join us_census_county_populations_2019 on us_census_county_populations_2019.fips = [us-counties-recent].fips
where
  date = (select max(date) from [us-counties-recent])
order by
  cases_per_million desc

And since everything in Datasette Lite can be bookmarked, here's the super long URL (clickable version here) that executes that query against those two CSV files:

https://lite.datasette.io/?csv=https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-counties-recent.csv&csv=https://raw.githubusercontent.com/simonw/covid-19-datasette/main/us_census_county_populations_2019.csv#/data?sql=select%0A++%5Bus-counties-recent%5D.*%2C%0A++us_census_county_populations_2019.population%2C%0A++1.0+*+%5Bus-counties-recent%5D.cases+%2F+us_census_county_populations_2019.population+*+1000000+as+cases_per_million%0Afrom%0A++%5Bus-counties-recent%5D%0A++join+us_census_county_populations_2019+on+us_census_county_populations_2019.fips+%3D+%5Bus-counties-recent%5D.fips%0Awhere%0A++date+%3D+%28select+max%28date%29+from+%5Bus-counties-recent%5D%29%0Aorder+by%0A++cases_per_million+desc

Tags: csv, projects, sql, datasette, webassembly, datasette-lite, cors

Datasette Lite: a server-side Python web application running in a browser

2022-05-04T15:16:49+00:00

Datasette Lite is a new way to run Datasette: entirely in a browser, taking advantage of the incredible Pyodide project which provides Python compiled to WebAssembly plus a whole suite of useful extras.

You can try it out here:

https://lite.datasette.io/

Update 20th June 2022: Datasette Lite can now load CSV files directly by URL, see Joining CSV files in your browser using Datasette Lite for details.

Update 17th August 2022: It can now load Datasette plugins as well.

The initial example loads two databases - the classic fixtures.db used by the Datasette test suite, and the content.db database that powers the official datasette.io website (described in some detail in my post about Baked Data).

You can instead use the "Load database by URL to a SQLite DB" button to paste in a URL to your own database. That file will need to be served with CORS headers that allow it to be fetched by the website (see README).

Try this URL, for example:

https://congress-legislators.datasettes.com/legislators.db

You can follow this link to open that database in Datasette Lite.

Datasette Lite supports almost all of Datasette's regular functionality: you can view tables, apply facets, run your own custom SQL results and export the results as CSV or JSON.

It's basically the full Datasette experience, except it's running entirely in your browser with no server (other than the static file hosting provided here by GitHub Pages) required.

I’m pretty stunned that this is possible now.

I had to make some small changes to Datasette to get this to work, detailed below, but really nothing extravagant - the demo is running the exact same Python code as the regular server-side Datasette application, just inside a web worker process in a browser rather than on a server.

The implementation is pretty small - around 300 lines of JavaScript. You can see the code in the simonw/datasette-lite repository - in two files, index.html and webworker.js

Why build this?

I built this because I want as many people as possible to be able to use my software.

I've invested a ton of effort in reducing the friction to getting started with Datasette. I've documented the install process, I've packaged it for Homebrew, I've written guides to running it on Glitch, I've built tools to help deploy it to Heroku, Cloud Run, Vercel and Fly.io. I even taught myself Electron and built a macOS Datasette Desktop application, so people could install it without having to think about their Python environment.

Datasette Lite is my latest attempt at this. Anyone with a browser that can run WebAssembly can now run Datasette in it - if they can afford the 10MB load (which in many places with metered internet access is way too much).

I also built this because I'm fascinated by WebAssembly and I've been looking for an opportunity to really try it out.

And, I find this project deeply amusing. Running a Python server-side web application in a browser still feels like an absurd thing to do. I love that it works.

I'm deeply inspired by JupyterLite. Datasette Lite's name is a tribute to that project.

How it works: Python in a Web Worker

Datasette Lite does most of its work in a Web Worker - a separate process that can run expensive CPU operations (like an entire Python interpreter) without blocking the main browser's UI thread.

The worker starts running when you load the page. It loads a WebAssembly compiled Python interpreter from a CDN, then installs Datasette and its dependencies into that interpreter using micropip.

It also downloads the specified SQLite database files using the browser's HTTP fetching mechanism and writes them to a virtual in-memory filesystem managed by Pyodide.

Once everything is installed, it imports datasette and creates a Datasette() object called ds. This object stays resident in the web worker.

To render pages, the index.html page sends a message to the web worker specifying which Datasette path has been requested - / for the homepage, /fixtures for the database index page, /fixtures/facetable for a table page and so on.

The web worker then simulates an HTTP GET against that path within Datasette using the following code:

response = await ds.client.get(path, follow_redirects=True)

This takes advantage of a really useful internal Datasette API: datasette.client is an HTTPX client object that can be used to execute HTTP requests against Datasette internally, without doing a round-trip across the network.

I initially added datasette.client with the goal of making any JSON APIs that Datasette provides available for internal calls by plugins as well, and to make it easier to write automated tests. It turns out to have other interesting applications too!

The web worker sends a message back to index.html with the status code, content type and content retrieved from Datasette. JavaScript in index.html then injects that HTML into the page using .innerHTML.

To get internal links working, Datasette Lite uses a trick I originally learned from jQuery: it applies a capturing event listener to the area of the page displaying the content, such that any link clicks or form submissions will be intercepted by a JavaScript function. That JavaScript can then turn them into new messages to the web worker rather than navigating to another page.

Some annotated code

Here are annotated versions of the most important pieces of code. In index.html this code manages the worker and updates the page when it recieves messages from it:

// Load the worker script
const datasetteWorker = new Worker("webworker.js");

// Extract the ?url= from the current page's URL
const initialUrl = new URLSearchParams(location.search).get('url');

// Message that to the worker: {type: 'startup', initialUrl: url}
datasetteWorker.postMessage({type: 'startup', initialUrl});

// This function does most of the work - it responds to messages sent
// back from the worker to the index page:
datasetteWorker.onmessage = (event) => {
  // {type: log, line: ...} messages are appended to a log textarea:
  var ta = document.getElementById('loading-logs');
  if (event.data.type == 'log') {
    loadingLogs.push(event.data.line);
    ta.value = loadingLogs.join("\n");
    ta.scrollTop = ta.scrollHeight;
    return;
  }
  let html = '';
  // If it's an {error: ...} message show it in a <pre> in a <div>
  if (event.data.error) {
    html = `<div style="padding: 0.5em"><h3>Error</h3><pre>${escapeHtml(event.data.error)}</pre></div>`;
  // If contentType is text/html, show it as straight HTML
  } else if (/^text\/html/.exec(event.data.contentType)) {
    html = event.data.text;
  // For contentType of application/json parse and pretty-print it
  } else if (/^application\/json/.exec(event.data.contentType)) {
    html = `<pre style="padding: 0.5em">${escapeHtml(JSON.stringify(JSON.parse(event.data.text), null, 4))}</pre>`;
  // Anything else (likely CSV data) escape it and show in a <pre>
  } else {
    html = `<pre style="padding: 0.5em">${escapeHtml(event.data.text)}</pre>`;
  }
  // Add the result to <div id="output"> using innerHTML
  document.getElementById("output").innerHTML = html;
  // Update the document.title if a <title> element is present
  let title = document.getElementById("output").querySelector("title");
  if (title) {
    document.title = title.innerText;
  }
  // Scroll to the top of the page after each new page is loaded
  window.scrollTo({top: 0, left: 0});
  // If we're showing the initial loading indicator, hide it
  document.getElementById('loading-indicator').style.display = 'none';
};

The webworker.js script is where the real magic happens:

// Load Pyodide from the CDN
importScripts("https://cdn.jsdelivr.net/pyodide/dev/full/pyodide.js");

// Deliver log messages back to the index.html page
function log(line) {
  self.postMessage({type: 'log', line: line});
}

// This function initializes Pyodide and installs Datasette
async function startDatasette(initialUrl) {
  // Mechanism for downloading and saving specified DB files
  let toLoad = [];
  if (initialUrl) {
    let name = initialUrl.split('.db')[0].split('/').slice(-1)[0];
    toLoad.push([name, initialUrl]);
  } else {
    // If no ?url= provided, loads these two demo databases instead:
    toLoad.push(["fixtures.db", "https://latest.datasette.io/fixtures.db"]);
    toLoad.push(["content.db", "https://datasette.io/content.db"]);
  }
  // This does a LOT of work - it pulls down the WASM blob and starts it running
  self.pyodide = await loadPyodide({
    indexURL: "https://cdn.jsdelivr.net/pyodide/dev/full/"
  });
  // We need these packages for the next bit of code to work
  await pyodide.loadPackage('micropip', log);
  await pyodide.loadPackage('ssl', log);
  await pyodide.loadPackage('setuptools', log); // For pkg_resources
  try {
    // Now we switch to Python code
    await self.pyodide.runPythonAsync(`
    # Here's where we download and save those .db files - they are saved
    # to a virtual in-memory filesystem provided by Pyodide

    # pyfetch is a wrapper around the JS fetch() function - calls using
    # it are handled by the browser's regular HTTP fetching mechanism
    from pyodide.http import pyfetch
    names = []
    for name, url in ${JSON.stringify(toLoad)}:
        response = await pyfetch(url)
        with open(name, "wb") as fp:
            fp.write(await response.bytes())
        names.append(name)

    import micropip
    # Workaround for Requested 'h11<0.13,>=0.11', but h11==0.13.0 is already installed
    await micropip.install("h11==0.12.0")
    # Install Datasette itself!
    await micropip.install("datasette==0.62a0")
    # Now we can create a Datasette() object that can respond to fake requests
    from datasette.app import Datasette
    ds = Datasette(names, settings={
        "num_sql_threads": 0,
    }, metadata = {
        # This metadata is displayed in Datasette's footer
        "about": "Datasette Lite",
        "about_url": "https://github.com/simonw/datasette-lite"
    })
    `);
    datasetteLiteReady();
  } catch (error) {
    self.postMessage({error: error.message});
  }
}

// Outside promise pattern
// https://github.com/simonw/datasette-lite/issues/25#issuecomment-1116948381
let datasetteLiteReady;
let readyPromise = new Promise(function(resolve) {
  datasetteLiteReady = resolve;
});

// This function handles messages sent from index.html to webworker.js
self.onmessage = async (event) => {
  // The first message should be that startup message, carrying the URL
  if (event.data.type == 'startup') {
    await startDatasette(event.data.initialUrl);
    return;
  }
  // This promise trick ensures that we don't run the next block until we
  // are certain that startDatasette() has finished and the ds.client
  // Python object is ready to use
  await readyPromise;
  // Run the reuest in Python to get a status code, content type and text
  try {
    let [status, contentType, text] = await self.pyodide.runPythonAsync(
      `
      import json
      # ds.client.get(path) simulates running a request through Datasette
      response = await ds.client.get(
          # Using json here is a quick way to generate a quoted string
          ${JSON.stringify(event.data.path)},
          # If Datasette redirects to another page we want to follow that
          follow_redirects=True
      )
      [response.status_code, response.headers.get("content-type"), response.text]
      `
    );
    // Message the results back to index.html
    self.postMessage({status, contentType, text});
  } catch (error) {
    // If an error occurred, send that back as a {error: ...} message
    self.postMessage({error: error.message});
  }
};

One last bit of code: here's the JavaScript in index.html which intercepts clicks on links and turns them into messages to the worker:

let output = document.getElementById('output');
// This captures any click on any element within <div id="output">
output.addEventListener('click', (ev => {
  // .closest("a") traverses up the DOM to find if this is an a
  // or an element nested in an a. We ignore other clicks.
  var link = ev.srcElement.closest("a");
  if (link && link.href) {
    // It was a click on a <a href="..."> link! Cancel the event:
    ev.stopPropagation();
    ev.preventDefault();
    // I want #fragment links to still work, using scrollIntoView()
    if (isFragmentLink(link.href)) {
      // Jump them to that element, but don't update the URL bar
      // since we use # in the URL to mean something else
      let fragment = new URL(link.href).hash.replace("#", "");
      if (fragment) {
        let el = document.getElementById(fragment);
        el.scrollIntoView();
      }
      return;
    }
    let href = link.getAttribute("href");
    // Links to external sites should open in a new window
    if (isExternal(href)) {
      window.open(href);
      return;
    }
    // It's an internal link navigation - send it to the worker
    loadPath(href);
  }
}), true);

function loadPath(path) {
  // We don't want anything after #, and we only want the /path
  path = path.split("#")[0].replace("http://localhost", "");
  // Update the URL with the new # location
  history.pushState({path: path}, path, "#" + path);
  // Plausible analytics, see:
  // https://github.com/simonw/datasette-lite/issues/22
  useAnalytics && plausible('pageview', {u: location.href.replace('?url=', '').replace('#', '/')});
  // Send a {path: "/path"} message to the worker
  datasetteWorker.postMessage({path});
}

Getting Datasette to work in Pyodide

Pyodide is the secret sauce that makes this all possible. That project provides several key components:

A custom WebAssembly build of the core Python interpreter, bundling the standard library (including a compiled WASM version of SQLite)
micropip - a package that can install additional Python dependencies by downloading them from PyPI
A comprehensive JavaScript to Python bridge, including mechanisms for translating Python objects to JavaScript and vice-versa
A JavaScript API for launching and then managing a Python interpreter process

I found the documentation on Using Pyodide in a web worker particularly helpful.

I had to make a few changes to Datasette to get it working with Pyodide. My tracking issue for that has the full details, but the short version is:

Ensure each of Datasette's dependencies had a wheel package on PyPI (as opposed to just a .tar.gz) - micropip only works with wheels. I ended up removing python-baseconv as a dependency and replacing click-default-group with my own click-default-group-wheel forked package (repo here). I got sqlite-utils working in Pyodide with this change too, see the 3.26.1 release notes.
Work around an error caused by importing uvicorn. Since Datasette Lite doesn't actually run its own web server that dependency wasn't necessary, so I changed my code to catch the ImportError in the right place.
The biggest change: WebAssembly can't run threads, which means Python can't run threads, which means any attempts to start a thread in Python cause an error. Datasette only uses threads in one place: to execute SQL queries in a thread pool where they won't block the event loop. I added a new --setting num_sql_threads 0 feature for disabling threading entirely, see issue 1735.

Having made those changes I shipped them in a Datasette 0.62a0 release. It's this release that Datasette Lite installs from PyPI.

Fragment hashes for navigation

You may have noticed that as you navigate through Datasette Lite the URL bar updates with URLs that look like the following:

https://lite.datasette.io/#/content/pypi_packages?_facet=author

I'm using the # here to separate out the path within the virtual Datasette instance from the URL to the Datasette Lite application itself.

Maintaining the state in the URL like this means that the Back and Forward browser buttons work, and also means that users can bookmark pages within the application and share links to them.

I usually like to avoid # URLs - the HTML history API makes it possible to use "real" URLs these days, even for JavaScript applications. But in the case of Datasette Lite those URLs wouldn't actually work - if someone attempted to refresh the page or navigate to a link GitHub Pages wouldn't know what file to serve.

I could run this on my own domain with a catch-all page handler that serves the Datasette Lite HTML and JavaScript no matter what path is requested, but I wanted to keep this as pure and simple as possible.

This also means I can reserve Datasette Lite's own query string for things like specifying the database to load, and potentially other options in the future.

Web Workers or Service Workers?

My initial idea for this project was to build it with Service Workers.

Service Workers are some deep, deep browser magic: they let you install a process that can intercept browser traffic to a specific domain (or path within that domain) and run custom code to return a result. Effectively they let you run your own server-side code in the browser itself.

They're mainly designed for building offline applications, but my hope was that I could use them to offer a full simulation of a server-side application instead.

Here's my TIL on Intercepting fetch in a service worker that came out of my initial research.

I managed to get a server-side JavaScript "hello world" demo working, but when I tried to add Pyodide I ran into some unavoidable road blocks. It turns out Service Workers are very restricted in which APIs they provide - in particular, they don't allow XMLHttpRequest calls. Pyodide apparently depends on XMLHttpRequest, so it was unable to run in a Service Worker at all. I filed an issue about it with the Pyodide project.

Initially I thought this would block the whole project, but eventually I figured out a way to achieve the same goals using Web Workers instead.

Is this an SPA or an MPA?

SPAs are Single Page Applications. MPAs are Multi Page Applications. Datasette Lite is a weird hybrid of the two.

This amuses me greatly.

Datasette itself is very deliberately architected as a multi page application.

I think SPAs, as developed over the last decade, have mostly been a mistake. In my experience they take longer to build, have more bugs and provide worse performance than a server-side, multi-page alternative implementation.

Obviously if you are building Figma or VS Code then SPAs are the right way to go. But most web applications are not Figma, and don't need to be!

(I used to think Gmail was a shining example of an SPA, but it's so sludgy and slow loading these days that I now see it as more of an argument against the paradigm.)

Datasette Lite is an SPA wrapper around an MPA. It literally simulates the existing MPA by running it in a web worker.

It's very heavy - it loads 11MB of assets before it can show you anything. But it also inherits many of the benefits of the underlying MPA: it has obvious distinctions between pages, a deeply interlinked interface, working back and forward buttons, it's bookmarkable and it's easy to maintain and add new features.

I'm not sure what my conclusion here is. I'm skeptical of SPAs, and now I've built a particularly weird one. Is this even a good idea? I'm looking forward to finding that out for myself.

Coming soon: JavaScript!

Another amusing detail about Datasette Lite is that the one part of Datasette that doesn't work yet is Datasette's existing JavaScript features!

Datasette currently makes very sparing use of JavaScript in the UI: it's used to add some drop-down interactive menus (including the handy "cog" menu on column headings) and for a CodeMirror-enhanced SQL editing interface.

JavaScript is used much more extensively by several popular Datasette plugins, including datasette-cluster-map and datasette-vega.

Unfortunately none of this works in Datasette Lite at the moment - because I don't yet have a good way to turn <script src="..."> links into things that can load content from the Web Worker.

This is one of the reasons I was initially hopeful about Service Workers.

Thankfully, since Datasette is built on the principles of progressive enhancement this doesn't matter: the application remains usable even if none of the JavaScript enhancements are applied.

I have an open issue for this. I welcome suggestions as to how I can get all of Datasette's existing JavaScript working in the new environment with as little effort as possible.

Bonus: Testing it with shot-scraper

In building Datasette Lite, I've committed to making Pyodide a supported runtime environment for Datasette. How can I ensure that future changes I make to Datasette - accidentally introducing a new dependency that doesn't work there for example - don't break in Pyodide without me noticing?

This felt like a great opportunity to exercise my shot-scraper CLI tool, in particular its ability to run some JavaScript against a page and pass or fail a CI job depending on if that JavaScript throws an error.

Pyodide needs you to run it from a real web server, not just an HTML file saved to disk - so I put together a very scrappy shell script which builds a Datasette wheel package, starts a localhost file server (using python3 -m http.server), then uses shot-scraper javascript to execute a test against it that installs Datasette from the wheel using micropip and confirms that it can execute a simple SQL query via the JSON API.

Here's the script in full, with extra comments:

#!/bin/bash
set -e
# I always forget to do this in my bash scripts - without it, any
# commands that fail in the script won't result in the script itself
# returning a non-zero exit code. I need it for running tests in CI.

# Build the wheel - this generates a file with a name similar to
# dist/datasette-0.62a0-py3-none-any.whl
python3 -m build

# Find the name of that wheel file, strip off the dist/
wheel=$(basename $(ls dist/*.whl))
# $wheel is now datasette-0.62a0-py3-none-any.whl

# Create a blank index page that loads Pyodide
echo '
<script src="https://cdn.jsdelivr.net/pyodide/v0.20.0/full/pyodide.js"></script>
' > dist/index.html

# Run a localhost web server for that dist/ folder, in the background
# so we can do more stuff in this script
cd dist
python3 -m http.server 8529 &
cd ..

# Now we use shot-scraper to run a block of JavaScript against our
# temporary web server. This will execute in the context of that
# index.html page we created earlier, which has loaded Pyodide
shot-scraper javascript http://localhost:8529/ "
async () => {
  // Load Pyodide and all of its necessary assets
  let pyodide = await loadPyodide();
  // We also need these packages for Datasette to work
  await pyodide.loadPackage(['micropip', 'ssl', 'setuptools']);
  // We need to escape the backticks because of Bash escaping rules
  let output = await pyodide.runPythonAsync(\`
    import micropip
    // This is needed to avoid a dependency conflict error
    await micropip.install('h11==0.12.0')
    // Here we install the Datasette wheel package we created earlier
    await micropip.install('http://localhost:8529/$wheel')
    // These imports avoid Pyodide errors importing datasette itself
    import ssl
    import setuptools
    from datasette.app import Datasette
    // num_sql_threads=0 is essential or Datasette will crash, since
    // Pyodide and WebAssembly cannot start threads
    ds = Datasette(memory=True, settings={'num_sql_threads': 0})
    // Simulate a hit to execute 'select 55 as itworks' and return the text
    (await ds.client.get(
      '/_memory.json?sql=select+55+as+itworks&_shape=array'
    )).text
  \`);
  // The last expression in the runPythonAsync block is returned, here
  // that's the text returned by the simulated HTTP response to the JSON API
  if (JSON.parse(output)[0].itworks != 55) {
    // This throws if the JSON API did not return the expected result
    // shot-scraper turns that into a non-zero exit code for the script
    // which will cause the CI task to fail
    throw 'Got ' + output + ', expected itworks: 55';
  }
  // This gets displayed on the console, with a 0 exit code for a pass
  return 'Test passed!';
}
"

# Shut down the server we started earlier, by searching for and killing
# a process that's running on the port we selected
pkill -f 'http.server 8529'

Tags: javascript, projects, python, webworkers, datasette, webassembly, pyodide, datasette-lite, cors