Simon Willison's Weblog: pyodide

Merge State Visualizer

2026-03-22T18:57:32+00:00

Bram Cohen wrote about his coherent vision for the future of version control using CRDTs, illustrated by 470 lines of Python.

I fed that Python (minus comments) into Claude and asked for an explanation, then had it use Pyodide to build me an interactive UI for seeing how the algorithms work.

Tags: vcs, pyodide, bram-cohen, crdt

cysqlite - a new sqlite driver

2026-02-11T17:34:40+00:00

cysqlite - a new sqlite driver

Charles Leifer has been maintaining pysqlite3 - a fork of the Python standard library's sqlite3 module that makes it much easier to run upgraded SQLite versions - since 2018.

He's been working on a ground-up Cython rewrite called cysqlite for almost as long, but it's finally at a stage where it's ready for people to try out.

The biggest change from the sqlite3 module involves transactions. Charles explains his discomfort with the sqlite3 implementation at length - that library provides two different variants neither of which exactly match the autocommit mechanism in SQLite itself.

I'm particularly excited about the support for custom virtual tables, a feature I'd love to see in sqlite3 itself.

cysqlite provides a Python extension compiled from C, which means it normally wouldn't be available in Pyodide. I set Claude Code on it (here's the prompt) and it built me cysqlite-0.1.4-cp311-cp311-emscripten_3_1_46_wasm32.whl, a 688KB wheel file with a WASM build of the library that can be loaded into Pyodide like this:

import micropip
await micropip.install(
    "https://simonw.github.io/research/cysqlite-wasm-wheel/cysqlite-0.1.4-cp311-cp311-emscripten_3_1_46_wasm32.whl"
)
import cysqlite
print(cysqlite.connect(":memory:").execute(
    "select sqlite_version()"
).fetchone())

(I also learned that wheels like this have to be built for the emscripten version used by that edition of Pyodide - my experimental wheel loads in Pyodide 0.25.1 but fails in 0.27.5 with a Wheel was built with Emscripten v3.1.46 but Pyodide was built with Emscripten v3.1.58 error.)

You can try my wheel in this new Pyodide REPL i had Claude build as a mobile-friendly alternative to Pyodide's own hosted console.

I also had Claude build this demo page that executes the original test suite in the browser and displays the results:

Via lobste.rs

Tags: python, sqlite, charles-leifer, webassembly, pyodide, ai-assisted-programming, claude-code

Running Pydantic's Monty Rust sandboxed Python subset in WebAssembly

2026-02-06T22:31:31+00:00

There's a jargon-filled headline for you! Everyone's building sandboxes for running untrusted code right now, and Pydantic's latest attempt, Monty, provides a custom Python-like language (a subset of Python) in Rust and makes it available as both a Rust library and a Python package. I got it working in WebAssembly, providing a sandbox-in-a-sandbox.

Here's how they describe Monty:

Monty avoids the cost, latency, complexity and general faff of using full container based sandbox for running LLM generated code.

Instead, it let's you safely run Python code written by an LLM embedded in your agent, with startup times measured in single digit microseconds not hundreds of milliseconds.

What Monty can do:

Run a reasonable subset of Python code - enough for your agent to express what it wants to do

Completely block access to the host environment: filesystem, env variables and network access are all implemented via external function calls the developer can control

Call functions on the host - only functions you give it access to [...]

A quick way to try it out is via uv:

uv run --with pydantic-monty python -m asyncio

Then paste this into the Python interactive prompt - the -m asyncio enables top-level await:

import pydantic_monty
code = pydantic_monty.Monty('print("hello " + str(4 * 5))')
await pydantic_monty.run_monty_async(code)

Monty supports a very small subset of Python - it doesn't even support class declarations yet!

But, given its target use-case, that's not actually a problem.

The neat thing about providing tools like this for LLMs is that they're really good at iterating against error messages. A coding agent can run some Python code, get an error message telling it that classes aren't supported and then try again with a different approach.

I wanted to try this in a browser, so I fired up a code research task in Claude Code for web and kicked it off with the following:

Clone https://github.com/pydantic/monty to /tmp and figure out how to compile it into a python WebAssembly wheel that can then be loaded in Pyodide. The wheel file itself should be checked into the repo along with build scripts and passing pytest playwright test scripts that load Pyodide from a CDN and the wheel from a “python -m http.server” localhost and demonstrate it working

Then a little later:

I want an additional WASM file that works independently of Pyodide, which is also usable in a web browser - build that too along with playwright tests that show it working. Also build two HTML files - one called demo.html and one called pyodide-demo.html - these should work similar to https://tools.simonwillison.net/micropython (download that code with curl to inspect it) - one should load the WASM build, the other should load Pyodide and have it use the WASM wheel. These will be served by GitHub Pages so they can load the WASM and wheel from a relative path since the .html files will be served from the same folder as the wheel and WASM file

Here's the transcript, and the final research report it produced.

I now have the Monty Rust code compiled to WebAssembly in two different shapes - as a .wasm bundle you can load and call from JavaScript, and as a monty-wasm-pyodide/pydantic_monty-0.0.3-cp313-cp313-emscripten_4_0_9_wasm32.whl wheel file which can be loaded into Pyodide and then called from Python in Pyodide in WebAssembly in a browser.

Here are those two demos, hosted on GitHub Pages:

Monty WASM demo - a UI over JavaScript that loads the Rust WASM module directly.
Monty Pyodide demo - this one provides an identical interface but here the code is loading Pyodide and then installing the Monty WASM wheel.

As a connoisseur of sandboxes - the more options the better! - this new entry from Pydantic ticks a lot of my boxes. It's small, fast, widely available (thanks to Rust and WebAssembly) and provides strict limits on memory usage, CPU time and access to disk and network.

It was also a great excuse to spin up another demo showing how easy it is these days to turn compiled code like C or Rust into WebAssembly that runs in both a browser and a Pyodide environment.

Tags: javascript, python, sandboxing, ai, rust, webassembly, pyodide, generative-ai, llms, ai-assisted-programming, pydantic, coding-agents, claude-code

MicroQuickJS

2025-12-23T20:53:40+00:00

MicroQuickJS

New project from programming legend Fabrice Bellard, of ffmpeg and QEMU and QuickJS and so much more fame:

MicroQuickJS (aka. MQuickJS) is a Javascript engine targetted at embedded systems. It compiles and runs Javascript programs with as low as 10 kB of RAM. The whole engine requires about 100 kB of ROM (ARM Thumb-2 code) including the C library. The speed is comparable to QuickJS.

It supports a subset of full JavaScript, though it looks like a rich and full-featured subset to me.

One of my ongoing interests is sandboxing: mechanisms for executing untrusted code - from end users or generated by LLMs - in an environment that restricts memory usage and applies a strict time limit and restricts file or network access. Could MicroQuickJS be useful in that context?

I fired up Claude Code for web (on my iPhone) and kicked off an asynchronous research project to see explore that question:

My full prompt is here. It started like this:

Clone https://github.com/bellard/mquickjs to /tmp

Investigate this code as the basis for a safe sandboxing environment for running untrusted code such that it cannot exhaust memory or CPU or access files or the network

First try building python bindings for this using FFI - write a script that builds these by checking out the code to /tmp and building against that, to avoid copying the C code in this repo permanently. Write and execute tests with pytest to exercise it as a sandbox

Then build a "real" Python extension not using FFI and experiment with that

Then try compiling the C to WebAssembly and exercising it via both node.js and Deno, with a similar suite of tests [...]

I later added to the interactive session:

Does it have a regex engine that might allow a resource exhaustion attack from an expensive regex?

(The answer was no - the regex engine calls the interrupt handler even during pathological expression backtracking, meaning that any configured time limit should still hold.)

Here's the full transcript and the final report.

Some key observations:

MicroQuickJS is very well suited to the sandbox problem. It has robust near and time limits baked in, it doesn't expose any dangerous primitive like filesystem of network access and even has a regular expression engine that protects against exhaustion attacks (provided you configure a time limit).
Claude span up and tested a Python library that calls a MicroQuickJS shared library (involving a little bit of extra C), a compiled a Python binding and a library that uses the original MicroQuickJS CLI tool. All of those approaches work well.
Compiling to WebAssembly was a little harder. It got a version working in Node.js and Deno and Pyodide, but the Python libraries wasmer and wasmtime proved harder, apparently because "mquickjs uses setjmp/longjmp for error handling". It managed to get to a working wasmtime version with a gross hack.

I'm really excited about this. MicroQuickJS is tiny, full featured, looks robust and comes from excellent pedigree. I think this makes for a very solid new entrant in the quest for a robust sandbox.

Update: I had Claude Code build tools.simonwillison.net/microquickjs, an interactive web playground for trying out the WebAssembly build of MicroQuickJS, adapted from my previous QuickJS plaground. My QuickJS page loads 2.28 MB (675 KB transferred). The MicroQuickJS one loads 303 KB (120 KB transferred).

Here are the prompts I used for that.

Tags: c, javascript, nodejs, python, sandboxing, ai, webassembly, deno, pyodide, generative-ai, llms, claude-code, fabrice-bellard

Recreating the Apollo AI adoption rate chart with GPT-5, Python and Pyodide

2025-09-09T06:47:49+00:00

Apollo Global Management's "Chief Economist" Dr. Torsten Sløk released this interesting chart which appears to show a slowdown in AI adoption rates among large (>250 employees) companies:

Here's the full description that accompanied the chart:

The US Census Bureau conducts a biweekly survey of 1.2 million firms, and one question is whether a business has used AI tools such as machine learning, natural language processing, virtual agents or voice recognition to help produce goods or services in the past two weeks. Recent data by firm size shows that AI adoption has been declining among companies with more than 250 employees, see chart below.

(My first thought on seeing that chart is that I hope it represents the peak of inflated expectations leading into the trough of dissillusionment in the Gartner Hype Cycle (which Wikipedia calls "largely disputed, with studies pointing to it being inconsistently true at best"), since that means we might be reaching the end of the initial hype phase and heading towards the slope of enlightenment.)

Finding the US Census data with GPT-5 search

This is the first I'd heard of the US Census Bureau running a biweekly (that's once every two weeks) survey about AI!

I decided to track down the numbers and see if I could recreate the chart myself. And since GPT-5 is really good at search now I fed it the following prompt to see how well it could do:

> The US Census Bureau conducts a biweekly survey of 1.2 million firms, and one question is whether a business has used AI tools such as machine learning, natural language processing, virtual agents or voice recognition to help produce goods or services in the past two weeks. Recent data by firm size shows that AI adoption has been declining among companies with more than 250 employees, see chart below.

Find me that original data

It took 1m19s to find exactly what I needed:

Here’s the original U.S. Census Bureau source for that series:

BTOS “Employment size class” time series (XLSX) — biweekly shares of firms answering Yes to “In the last two weeks, did this business use Artificial Intelligence (AI) in producing goods or services?”, broken out by firm-size buckets (incl. 250+ employees). Coverage: Sep 11, 2023 → Aug 24, 2025. (Census.gov) [...]

That Census page was not at all obvious. Thankfully GPT-5 had tipped me off to the "Employment size class" file, this link here:

So I downloaded that file, and confirmed that it was indeed a spreadsheet containing the data I wanted (in among all sorts of other survey questions). Here's a 374KB XLSX copy of the file I downloaded.

Recreating the chart with GPT-5 code interpreter

So what should I do with it now? I decided to see if GPT-5 could turn the spreadsheet back into that original chart, using Python running in its code interpreter tool.

So I uploaded the XLSX file back to ChatGPT, dropped in a screenshot of the Apollo chart and prompted:

Use this data to recreate this chart using python

I thought this was a pretty tall order, but it's always worth throwing big challenges at an LLM to learn from how well it does.

It really worked hard on this. I didn't time it exactly but it spent at least 7 minutes "reasoning" across 5 different thinking blocks, interspersed with over a dozen Python analysis sessions. It used pandas and numpy to explore the uploaded spreadsheet and find the right figures, then tried several attempts at plotting with matplotlib.

As far as I can tell GPT-5 in ChatGPT can now feed charts it creates back into its own vision model, because it appeared to render a broken (empty) chart and then keep on trying to get it working.

It found a data dictionary in the last tab of the spreadsheet and used that to build a lookup table matching the letters A through G to the actual employee size buckets.

At the end of the process it spat out this chart:

At first glance I thought it had nailed it... but then I compared the chart more closely with the Apollo original and spotted some definite discrepancies. GPT-5's chart peaked at 14.5% but the highest value in Apollo's was more like 13.5%. The GPT-5 chart was spikier - and most interestingly it included a clear uptick in the last data point where Apollo's had trended downwards.

I decided it was time to look at the actual data. I opened up the spreadsheet in Numbers, found the AI question columns and manually reviewed them. They seemed to match the GPT-5 chart results - so why the difference to Apollo's?

Then I noticed a crucial detail in the Apollo chart that I had cropped out of my original screenshot!

Note: Data is six-survey moving average.

So I told ChatGPT:

Do the first question, plot it as a six survey rolling average

I asked for the first question because it turned out there were two that were relevant in the survey spreadsheet.

In the last two weeks, did this business use Artificial Intelligence (AI) in producing goods or services? (Examples of AI: machine learning, natural language processing, virtual agents, voice recognition, etc.)
During the next six months, do you think this business will be using Artificial Intelligence (AI) in producing goods or services? (Examples of AI: machine learning, natural language processing, virtual agents, voice recognition, etc.)

It churned away for a little longer, added this code to the script:

# Compute 6-survey rolling average (biweekly cadence → ~12 weeks)
rolled = wide.rolling(window=6, min_periods=6).mean()

And popped out this chart (after I told it to fix the glitch in the title):

I think it's done it! This is a very solid match for the Apollo original, recreated using matplotlib and pandas from the same underlying source data from the US Census.

Here's the full Python code it wrote, which I think is quite readable (in as much as Pandas code can be):

import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.ticker import PercentFormatter

path = "/mnt/data/Employment Size Class.xlsx"

resp = pd.read_excel(path, sheet_name="Response Estimates")
dates = pd.read_excel(path, sheet_name="Collection and Reference Dates")

is_current = resp["Question"].astype(str).str.strip().str.startswith("In the last two weeks")
ai_yes = resp[is_current & resp["Answer"].astype(str).str.strip().str.lower().eq("yes")].copy()

code_to_bucket = {"A":"1-4","B":"5-9","C":"10-19","D":"20-49","E":"50-99","F":"100-249","G":"250 or more employees"}
ai_yes["Bucket"] = ai_yes["Empsize"].map(code_to_bucket)

period_cols = [c for c in ai_yes.columns if str(c).isdigit() and len(str(c))==6]
long = ai_yes.melt(id_vars=["Bucket"], value_vars=period_cols, var_name="Smpdt", value_name="value")

dates["Smpdt"] = dates["Smpdt"].astype(str)
long["Smpdt"] = long["Smpdt"].astype(str)
merged = long.merge(dates[["Smpdt","Ref End"]], on="Smpdt", how="left")
merged["date"] = pd.to_datetime(merged["Ref End"], errors="coerce")

merged["value"] = pd.to_numeric(long["value"].astype(str).str.replace("%","",regex=False).str.strip(), errors="coerce")

order = ["250 or more employees","100-249","50-99","20-49","10-19","5-9","1-4"]
wide = merged.pivot_table(index="date", columns="Bucket", values="value", aggfunc="mean").sort_index()
wide = wide[[c for c in order if c in wide.columns]]
rolled = wide.rolling(window=6, min_periods=6).mean()

start, end = pd.Timestamp("2023-11-01"), pd.Timestamp("2025-08-31")
rolled_win = rolled.loc[(rolled.index >= start) & (rolled.index <= end)]

fig, ax = plt.subplots(figsize=(12, 6))
for col in order:
    if col in rolled_win.columns:
        ax.plot(rolled_win.index, rolled_win[col], label=col, linewidth=2)

ax.set_title("AI adoption (last two weeks) — 6‑survey rolling average", pad=16)
ax.yaxis.set_major_formatter(PercentFormatter(100))
ax.set_ylabel("%")
ax.set_xlabel("")
ax.grid(True, alpha=0.25, linestyle="--")
ax.legend(title=None, loc="upper left", ncols=2, frameon=False)
plt.tight_layout()

png_path = "/mnt/data/ai_adoption_rolling6_by_firm_size.png"
svg_path = "/mnt/data/ai_adoption_rolling6_by_firm_size.svg"
plt.savefig(png_path, dpi=200, bbox_inches="tight")
plt.savefig(svg_path, bbox_inches="tight")

I like how it generated an SVG version of the chart without me even asking for it.

You can access the ChatGPT transcript to see full details of everything it did.

Rendering that chart client-side using Pyodide

I had one more challenge to try out. Could I render that same chart entirely in the browser using Pyodide, which can execute both Pandas and Matplotlib?

I fired up a new ChatGPT GPT-5 session and prompted:

Build a canvas that loads Pyodide and uses it to render an example bar chart with pandas and matplotlib and then displays that on the page

My goal here was simply to see if I could get a proof of concept of a chart rendered, ideally using the Canvas feature of ChatGPT. Canvas is OpenAI's version of Claude Artifacts, which lets the model write and then execute HTML and JavaScript directly in the ChatGPT interface.

It worked! Here's the transcript and here's what it built me, exported to my tools.simonwillison.net GitHub Pages site (source code here).

I've now proven to myself that I can render those Python charts directly in the browser. Next step: recreate the Apollo chart.

I knew it would need a way to load the spreadsheet that was CORS-enabled. I uploaded my copy to my /static/cors-allow/2025/... directory (configured in Cloudflare to serve CORS headers), pasted in the finished plotting code from earlier and told ChatGPT:

Now update it to have less explanatory text and a less exciting design (black on white is fine) and run the equivalent of this:

(... pasted in Python code from earlier ...)

Load the XLSX sheet from https://static.simonwillison.net/static/cors-allow/2025/Employment-Size-Class-Sep-2025.xlsx

It didn't quite work - I got an error about openpyxl which I manually researched the fix for and prompted:

Use await micropip.install("openpyxl") to install openpyxl - instead of using loadPackage

I had to paste in another error message:

zipfile.BadZipFile: File is not a zip file

Then one about a SyntaxError: unmatched ')' and a TypeError: Legend.__init__() got an unexpected keyword argument 'ncols' - copying and pasting error messages remains a frustrating but necessary part of the vibe-coding loop.

... but with those fixes in place, the resulting code worked! Visit tools.simonwillison.net/ai-adoption to see the final result:

Here's the code for that page, 170 lines all-in of HTML, CSS, JavaScript and Python.

What I've learned from this

This was another of those curiosity-inspired investigations that turned into a whole set of useful lessons.

GPT-5 is great at tracking down US Census data, no matter how difficult their site is to understand if you don't work with their data often
It can do a very good job of turning data + a screenshot of a chart into a recreation of that chart using code interpreter, Pandas and matplotlib
Running Python + matplotlib in a browser via Pyodide is very easy and only takes a few dozen lines of code

Fetching an XLSX sheet into Pyodide is only a small extra step using pyfetch and openpyxl:

import micropip
await micropip.install("openpyxl")
from pyodide.http import pyfetch
resp_fetch = await pyfetch(URL)
wb_bytes = await resp_fetch.bytes()
xf = pd.ExcelFile(io.BytesIO(wb_bytes), engine='openpyxl')

Another new-to-me pattern: you can render an image to the DOM from Pyodide code like this:

from js import document
document.getElementById('plot').src = 'data:image/png;base64,' + img_b64

I will most definitely be using these techniques again in future.

Update: Coincidentally Claude released their own upgraded equivalent to ChatGPT Code Interpreter later on the day that I published this story, so I ran the same chart recreation experiment against Claude Sonnet 4 to see how it compared.

Tags: census, data-journalism, javascript, python, tools, visualization, ai, pyodide, openai, generative-ai, chatgpt, llms, ai-assisted-programming, code-interpreter, llm-reasoning, vibe-coding, ai-assisted-search, gpt-5

Giving software away for free

2025-04-28T16:10:58+00:00

If you want to create completely free software for other people to use, the absolute best delivery mechanism right now is static HTML and JavaScript served from a free web host with an established reputation.

Thanks to WebAssembly the set of potential software that can be served in this way is vast and, I think, under appreciated. Pyodide means we can ship client-side Python applications now!

This assumes that you would like your gift to the world to keep working for as long as possible, while granting you the freedom to lose interest and move onto other projects without needing to keep covering expenses far into the future.

Even the cheapest hosting plan requires you to monitor and update billing details every few years. Domains have to be renewed. Anything that runs server-side will inevitably need to be upgraded someday - and the longer you wait between upgrades the harder those become.

My top choice for this kind of thing in 2025 is GitHub, using GitHub Pages. It's free for public repositories and I haven't seen GitHub break a working URL that they have hosted in the 17+ years since they first launched.

A few years ago I'd have recommended Heroku on the basis that their free plan had stayed reliable for more than a decade, but Salesforce took that accumulated goodwill and incinerated it in 2022.

It almost goes without saying that you should release it under an open source license. The license alone is not enough to ensure regular human beings can make use of what you have built though: give people a link to something that works!

Tags: github, html, javascript, open-source, web-standards, heroku, webassembly, pyodide

URL-addressable Pyodide Python environments

2025-02-13T05:13:27+00:00

This evening I spotted an obscure bug in Datasette, using Datasette Lite. I figure it's a good opportunity to highlight how useful it is to have a URL-addressable Python environment, powered by Pyodide and WebAssembly.

Here's the page that helped me discover the bug:

https://lite.datasette.io/?install=datasette-visible-internal-db&ref=1.0a17#/_internal/catalog_columns?_facet=database_name

To explain what's going on here, let's first review the individual components.

Datasette Lite

Datasette Lite is a version of Datasette that runs entirely in your browser. It runs on Pyodide, which I think is still the most underappreciated project in the Python ecosystem.

I built Datasette Lite almost three years ago as a weekend hack project to try and see if I could get Datasette - a server-side Python web application - to run entirely in the browser.

I've added a bunch of features since then, described in the README - most significantly the ability to load SQLite databases, CSV files, JSON files or Parquet files by passing a URL to a query string parameter.

I built Datasette Lite almost as a joke, thinking nobody would want to wait for a full Python interpreter to download to their browser each time they wanted to explore some data. It turns out internet connections are fast these days and having a version of Datasette that needs a browser, GitHub Pages and nothing else is actually extremely useful.

Just the other day I saw Logan Williams of Bellingcat using it to share a better version of this Excel sheet:

The NSF grants that Ted Cruz has singled out for advancing "neo-Marxist class warfare propaganda," in Datasette-Lite: lite.datasette.io?url=https://...

Let's look at that URL in full:

https://lite.datasette.io/?url=https://data-house-lake.nyc3.cdn.digitaloceanspaces.com/cruz_nhs.db#/cruz_nhs/grants

The ?url= parameter there poins to a SQLite database file, hosted on DigitalOcean Spaces and served with the all-important access-control-allow-origin: * header which allows Datasette Lite to load it across domains.

The #/cruz_nhs/grants part of the URL tells Datasette Lite which page to load when you visit the link.

Anything after the # in Datasette Lite is a URL that gets passed on to the WebAssembly-hosted Datasette instance. Any query string items before that can be used to affect the initial state of the Datasette instance, to import data or even to install additional plugins.

The Datasette 1.0 alphas

I've shipped a lot of Datasette alphas - the most recent is Datasette 1.0a17. Those alphas get published to PyPI, which means they can be installed using pip install datasette==1.0a17.

A while back I added the same ability to Datasette Lite itself. You can now pass &ref=1.0a17 to the Datasette Lite URL to load that specific version of Datasette.

This works thanks to the magic of Pyodide's micropip mechanism. Every time you load Datasette Lite in your browser it's actually using micropip to install the packages it needs directly from PyPI. The code looks something like this:

await pyodide.loadPackage('micropip', {messageCallback: log});
let datasetteToInstall = 'datasette';
let pre = 'False';
if (settings.ref) {
  if (settings.ref == 'pre') {
    pre = 'True';
  } else {
    datasetteToInstall = `datasette==${settings.ref}`;
  }
}
await self.pyodide.runPythonAsync(`
import micropip
await micropip.install("${datasetteToInstall}", pre=${pre})
`);

Full code here.

That settings object has been passed to the Web Worker that loads Datasette, incorporating various query string parameters.

This all means I can pass ?ref=1.0a17 to Datasette Lite to load a specific version, or ?ref=pre to get the most recently released pre-release version.

This works for plugins, too

Since loading extra packages from PyPI via micropip is so easy, I went a step further and added plugin support.

The ?install= parameter can be passed multiple times, each time specifying a Datasette plugin from PyPI that should be installed into the browser.

The README includes a bunch of examples of this mechanism in action. Here's a fun one that loads datasette-mp3-audio to provide inline MP3 playing widgets, originally created for my ScotRail audio announcements project.

This only works for some plugins. They need to be pure Python wheels - getting plugins with compiled binary dependencies to work in Pyodide WebAssembly requires a whole set of steps that I haven't quite figured out.

Frustratingly, it doesn't work for plugins that run their own JavaScript yet! I may need to rearchitect significant chunks of both Datasette and Datasette Lite to make that work.

It's also worth noting that this is a remote code execution security hole. I don't think that's a problem here, because lite.datasette.io is deliberately hosted on the subdomain of a domain that I never intend to use cookies on. It's possible to vandalize the visual display of lite.datasette.io but it shouldn't be possible to steal any private data or do any lasting damage.

datasette-visible-internal-db

This evening's debugging exercise used a plugin called datasette-visible-internal-db.

Datasette's internal database is an invisible SQLite database that sits at the heart of Datasette, tracking things like loaded metadata and the schemas of the currently attached tables.

Being invisible means we can use it for features that shouldn't be visible to users - plugins that record API secrets or permissions or track comments or data import progress, for example.

In Python code it's accessed like this:

internal_db = datasette.get_internal_database()

As opposed to Datasette's other databases which are accessed like so:

db = datasette.get_database("my-database")

Sometimes, when hacking on Datasette, it's useful to be able to browse the internal database using the default Datasette UI.

That's what datasette-visible-internal-db does. The plugin implementation is just five lines of code:

import datasette

@datasette.hookimpl
def startup(datasette):
    db = datasette.get_internal_database()
    datasette.add_database(db, name="_internal", route="_internal")

On startup the plugin grabs a reference to that internal database and then registers it using Datasette's add_database() method. That's all it takes to have it show up as a visible database on the /_internal path within Datasette.

Spotting the bug

I was poking around with this today out of pure curiosity - I hadn't tried ?install=datasette-visible-internal-db with Datasette Lite before and I wanted to see if it worked.

Here's that URL from earlier, this time with commentary:

https://lite.datasette.io/ // Datasette Lite
  ?install=datasette-visible-internal-db // Install the visible internal DB plugin
  &ref=1.0a17 // Load the 1.0a17 alpha release
  #/_internal/catalog_columns // Navigate to the /_internal/catalog_columns table page
  &_facet=database_name // Facet by database_name for good measure

And this is what I saw:

This all looked good... until I clicked on that _internal link in the database_name column... and it took me to this /_internal/databases/_internal 404 page.

Why was that a 404? Datasette introspects the SQLite table schema to identify foreign key relationships, then turns those into hyperlinks. The SQL schema for that catalog_columns table (displayed at the bottom of the table page) looked like this:

CREATE TABLE catalog_columns (
    database_name TEXT,
    table_name TEXT,
    cid INTEGER,
    name TEXT,
    type TEXT,
    "notnull" INTEGER,
    default_value TEXT, -- renamed from dflt_value
    is_pk INTEGER, -- renamed from pk
    hidden INTEGER,
    PRIMARY KEY (database_name, table_name, name),
    FOREIGN KEY (database_name) REFERENCES databases(database_name),
    FOREIGN KEY (database_name, table_name) REFERENCES tables(database_name, table_name)
);

Those foreign key references are a bug! I renamed the internal tables from databases and tables to catalog_databases and catalog_tables quite a while ago, but apparently forgot to update the references - and SQLite let me get away with it.

Fixing the bug

I fixed the bug in this commit. As is often the case the most interesting part of the fix is the accompanying test. I decided to use the introspection helpers in sqlite-utils to guard against every making another mistake like this again in the future:

@pytest.mark.asyncio
async def test_internal_foreign_key_references(ds_client):
    internal_db = await ensure_internal(ds_client)
    def inner(conn):
        db = sqlite_utils.Database(conn)
        table_names = db.table_names()
        for table in db.tables:
            for fk in table.foreign_keys:
                other_table = fk.other_table
                other_column = fk.other_column
                message = 'Column "{}.{}" references other column "{}.{}" which does not exist'.format(
                    table.name, fk.column, other_table, other_column
                )
                assert other_table in table_names, message + " (bad table)"
                assert other_column in db[other_table].columns_dict, (
                    message + " (bad column)"
                )
    await internal_db.execute_fn(inner)

This uses Datasette's await db.execute_fn() method, which lets you run Python code that accesses SQLite in a thread. That code can then use the blocking sqlite-utils introspection methods - here I'm looping through every table in that internal database, looping through each tables .foreign_keys and confirming that the .other_table and .other_column values reference a table and column that genuinely exist.

I ran this test, watched it fail, then applied the fix and it passed.

URL-addressable Steps To Reproduce

The idea I most wanted to highlight here is the enormous value provided by URL-addressable Steps To Reproduce.

Having good Steps To Reproduce is crucial for productively fixing bugs. Something you can click on to see the bug is the most effective form of STR there is.

Ideally, these URLs will continue to work long into the future.

The great thing about a system like Datasette Lite is that everything is statically hosted files. The application itself is hosted on GitHub Pages, and it works by loading additional files from various different CDNs. The only dynamic aspect is cached lookups against the PyPI API, which I expect to stay stable for a long time to come.

As a stable component of the Web platform for almost 8 years WebAssembly is clearly here to stay. I expect we'll be able to execute today's WASM code in browsers 20+ years from now.

I'm confident that the patterns I've been exploring in Datasette Lite over the past few years could be just as valuable for other projects. Imagine demonstrating bugs in a Django application using a static WebAssembly build, archived forever as part of an issue tracking system.

I think WebAssembly and Pyodide still have a great deal of untapped potential for the wider Python world.

Tags: python, urls, datasette, webassembly, pyodide, datasette-lite

APSW SQLite query explainer

2025-02-07T02:00:01+00:00

APSW SQLite query explainer

Today I found out about APSW's (Another Python SQLite Wrapper, in constant development since 2004) apsw.ext.query_info() function, which takes a SQL query and returns a very detailed set of information about that query - all without executing it.

It actually solves a bunch of problems I've wanted to address in Datasette - like taking an arbitrary query and figuring out how many parameters (?) it takes and which tables and columns are represented in the result.

I tried it out in my console (uv run --with apsw python) and it seemed to work really well. Then I remembered that the Pyodide project includes WebAssembly builds of a number of Python C extensions and was delighted to find apsw on that list.

... so I got Claude to build me a web interface for trying out the function, using Pyodide to run a user's query in Python in their browser via WebAssembly.

Claude didn't quite get it in one shot - I had to feed it the URL to a more recent Pyodide and it got stuck in a bug loop which I fixed by pasting the code into a fresh session.

Tags: python, sqlite, ai, webassembly, pyodide, generative-ai, llms, ai-assisted-programming, claude, claude-artifacts, apsw, prompt-to-app

ChatGPT Canvas can make API requests now, but it's complicated

2024-12-10T21:49:55+00:00

Today's 12 Days of OpenAI release concerned ChatGPT Canvas, a new ChatGPT feature that enables ChatGPT to pop open a side panel with a shared editor in it where you can collaborate with ChatGPT on editing a document or writing code.

I'm always excited to see a new form of UI on top of LLMs, and it's great seeing OpenAI stretch out beyond pure chat for this. It's definitely worth playing around with to get a feel for how a collaborative human+LLM interface can work. The feature where you can ask ChatGPT for "comments on my document" and it will attach them Google Docs style is particularly neat.

I wanted to focus in on one particular aspect of Canvas, because it illustrates a concept I've been talking about for a little while now: the increasing complexity of fully understanding the capabilities of core LLM tools.

Canvas runs Python via Pyodide

If a canvas editor contains Python code, ChatGPT adds a new "Run" button at the top of the editor.

ChatGPT has had the ability to run Python for a long time via the excellent Code Interpreter feature, which executes Python server-side in a tightly locked down Kubernetes container managed by OpenAI.

The new Canvas run button is not the same thing - it's an entirely new implementation of code execution that runs code directly in your browser using Pyodide (Python compiled to WebAssembly).

The first time I tried this button I got the following dialog:

"Python in canvas can make network requests"‽ This is a very new capability. ChatGPT Code Interpreter has all network access blocked, but apparently ChatGPT Canvas Python does not share that limitation.

I tested this a little bit and it turns out it can make direct HTTP calls from your browser to anywhere online with compatible CORS headers.

(Understanding CORS is a recurring theme in working with LLMs as a consumer, which I find deeply amusing because it remains a pretty obscure topic even among professional web developers.)

Claude Artifacts allow full JavaScript execution in a Canvas-like interface within Claude, but even those are severely restricted in terms of the endpoints they can access. OpenAI have apparently made the opposite decision, throwing everything wide open as far as allowed network request targets go.

I prompted ChatGPT like this:

use python to fetch "https://datasette.io/content.json?sql=select+*+from+stats++limit+10%0D%0A&_shape=array" and then display it nicely - the JSON looks like this:
[
  {
    "package": "airtable-export",
    "date": "2020-12-14",
    "downloads": 2
  },

I often find pasting the first few lines of a larger JSON example into an LLM gives it enough information to guess the rest.

Here's the result. ChatGPT wrote the code and showed it in a canvas, then I clicked "Run" and had the resulting data displayed in a neat table below:

What a neat and interesting thing! I can now get ChatGPT to write me Python code that fetches from external APIs and displays me the results.

It's not yet as powerful as Claude Artifacts which allows for completely custom HTML+CSS+JavaScript interfaces, but it's also more powerful than Artifacts because those are not allowed to make outbound HTTP requests at all.

What this all means

With the introduction of Canvas, here are some new points that an expert user of ChatGPT now needs to understand:

ChatGPT can write and then execute code in Python, but there are two different ways it can do that:
- If run using Code Interpreter it can access files you upload to it and a collection of built-in libraries but cannot make API requests.
- If run in a Canvas it uses Pyodide and can access API endpoints, but not files that you upload to it.
Code Interpreter cannot pip install additional packages, though you may be able to upload them as wheels and convince it to install them.
Canvas Python can install extra packages using micropip, but this will only work for pure Python wheels that are compatible with Pyodide.
Code interpreter is locked down: it cannot make API requests or communicate with the wider internet at all. If you want it to work on data you need to upload that data to it.
Canvas Python can fetch data via API requests (directly into your browser), but only from sources that implement an open CORS policy.
Both Canvas and Code Interpreter remain strictly limited in terms of the custom UI they can offer - but they both have access to the Pandas ecosystem of visualization tools so they can probably show you charts or tables.

This is really, really confusing

Do you find this all hopelessly confusing? I don't blame you. I'm a professional web developer and a Python engineer of 20+ years and I can just about understand and internalize the above set of rules.

I don't really have any suggestions for where we go from here. This stuff is hard to use. The more features and capabilities we pile onto these systems the harder it becomes to obtain true mastery of them and really understand what they can do and how best to put them into practice.

Maybe this doesn't matter? I don't know anyone with true mastery of Excel - to the point where they could compete in last week's Microsoft Excel World Championship - and yet plenty of people derive enormous value from Excel despite only scratching the surface of what it can do.

I do think it's worth remembering this as a general theme though. Chatbots may sound easy to use, but they really aren't - and they're getting harder to use all the time.

A new data exfiltration vector

Thinking about this a little more, I think the most meaningful potential security impact from this could be opening up a new data exfiltration vector.

Data exfiltration attacks occur when an attacker tricks someone into pasting malicious instructions into their prompt (often via a prompt injection attack) that cause ChatGPT to gather up any available private information from the current conversation and leak it to that attacker in some way.

I imagine it may be possible to construct a pretty gnarly attack that convinces ChatGPT to open up a Canvas and then run Python that leaks any gathered private data to the attacker via an API call.

Tags: python, security, usability, ai, webassembly, pyodide, openai, prompt-injection, generative-ai, chatgpt, llms, claude-artifacts, cors, prompt-to-app

ChainForge

2024-11-08T20:52:20+00:00

ChainForge

I'm still on the hunt for good options for running evaluations against prompts. ChainForge offers an interesting approach, calling itself "an open-source visual programming environment for prompt engineering".

The interface is one of those boxes-and-lines visual programming tools, which reminds me of Yahoo Pipes.

It's open source (from a team at Harvard) and written in Python, which means you can run a local copy instantly via uvx like this:

uvx chainforge serve

You can then configure it with API keys to various providers (OpenAI worked for me, Anthropic models returned JSON parsing errors due to a 500 page from the ChainForge proxy) and start trying it out.

The "Add Node" menu shows the full list of capabilities.

The JavaScript and Python evaluation blocks are particularly interesting: the JavaScript one runs outside of a sandbox using plain eval(), while the Python one still runs in your browser but uses Pyodide in a Web Worker.

Tags: javascript, python, ai, pyodide, prompt-engineering, prompt-injection, generative-ai, llms, evals, uv

Everything I built with Claude Artifacts this week

2024-10-21T14:32:57+00:00

I'm a huge fan of Claude's Artifacts feature, which lets you prompt Claude to create an interactive Single Page App (using HTML, CSS and JavaScript) and then view the result directly in the Claude interface, iterating on it further with the bot and then, if you like, copying out the resulting code.

I was digging around in my Claude activity export (I built a claude-to-sqlite tool to convert it to SQLite I could explore it in Datasette) and decided to see how much I'd used artifacts in the past week. It was more than I expected!

Being able to spin up a full interactive application - sometimes as an illustrative prototype, but often as something that directly solves a problem - is a remarkably useful tool.

Here's most of what I've used Claude Artifacts for in the past seven days. I've provided prompts or a full transcript for nearly all of them.

URL to Markdown with Jina Reader

I got frustrated at how hard it was to copy and paste the entire text of a web page into an LLM while using Mobile Safari. So I built a simple web UI that lets me enter a URL, calls the Jina Reader API to generate Markdown (which uses Puppeteer under the hood) and gives me that Markdown with a convenient "Copy" button.

Try it out: https://tools.simonwillison.net/jina-reader (Code)

I wrote more about that project here.

SQLite in WASM demo

A Hacker News conversation about SQLite's WASM build lead me to the @sqlite.org/sqlite-wasm package on NPM, and I decided to knock together a quick interactive demo.

Try it out here: tools.simonwillison.net/sqlite-wasm

Code, Claude transcript

Extract URLs

I found myself wanting to extract all of the underlying URLs that were linked to from a chunk of text on a web page. I realized the fastest way to do that would be to spin up an artifact that could accept rich-text HTML pastes and use an HTML parser to extract those links.

https://tools.simonwillison.net/extract-urls

Code, Claude transcript

Clipboard viewer

Messing around with a tool that lets you paste in rich text reminded me that the browser clipboard API is a fascinating thing. I decided to build a quick debugging tool that would let me copy and paste different types of content (plain text, rich text, files, images etc) and see what information was available to me in the browser.

https://tools.simonwillison.net/clipboard-viewer

Code, Claude transcript

Pyodide REPL

I didn't put a lot of effort into this one. While poking around with Claude Artifacts in the browser DevTools I spotted this CSP header:

content-security-policy: default-src https://www.claudeusercontent.com; script-src 'unsafe-eval' 'unsafe-inline' https://www.claudeusercontent.com https://cdnjs.cloudflare.com https://cdn.jsdelivr.net/pyodide/; connect-src https://cdn.jsdelivr.net/pyodide/; worker-src https://www.claudeusercontent.com blob:; style-src 'unsafe-inline' https://www.claudeusercontent.com https://cdnjs.cloudflare.com https://fonts.googleapis.com; img-src blob: data: https://www.claudeusercontent.com; font-src data: https://www.claudeusercontent.com; object-src 'none'; base-uri https://www.claudeusercontent.com; form-action https://www.claudeusercontent.com; frame-ancestors https://www.claudeusercontent.com https://claude.ai https://preview.claude.ai https://claude.site https://feedback.anthropic.com; upgrade-insecure-requests; block-all-mixed-content

The https://cdn.jsdelivr.net/pyodide/ in there caught my eye, because it suggested that the Anthropic development team had deliberately set it up so Pyodide - Python compiled to WebAssembly - could be loaded in an artifact.

I got Claude to spin up a very quick demo to prove that this worked:

https://claude.site/artifacts/a3f85567-0afc-4854-b3d3-3746dd1a37f2

I've not bothered to extract this one to my own tools.simonwillison.net site yet because it's purely a proof of concept that Pyodide can load correctly in that environment.

Photo Camera Settings Simulator

I was out on a photo walk and got curious about whether or not JavaScript could provide a simulation of camera settings. I didn't get very far with this one (prompting on my phone while walking along the beach) - the result was buggy and unimpressive and I quickly lost interest. It did expose me to the Fabric.js library for manipulating canvas elements though.

https://claude.site/artifacts/e645c231-8c13-4374-bb7d-271c8dd73825

LLM pricing calculator

This one I did finish. I built this pricing calculator as part of my experiments with Video scraping using Google Gemini, because I didn't trust my own calculations for how inexpensive Gemini was! Here are detailed notes on how I built that.

https://tools.simonwillison.net/llm-prices

YAML to JSON converter

I wanted to remind myself how certain aspects of YAML syntax worked, so I span up a quick YAML to JSON converter tool that shows the equivalent JSON live as you type YAML.

https://claude.site/artifacts/ffeb439c-fc95-428a-9224-434f5f968d51

Claude transcript

OpenAI Audio

This is my most interesting artifact of the week. I was exploring OpenAI's new Audio APIs and decided to see if I could get Claude to build we a web page that could request access to my microphone, record a snippet of audio, then base64 encoded that and send it to the OpenAI API.

Here are the full details on how I built this tool.

https://tools.simonwillison.net/openai-audio

Claude Artifacts can't make API requests to external hosts directly, but it can still spin up enough of a working version that it's easy to take that, move it to different hosting and finish getting it working.

I wrote more about this API pattern in Building a tool showing how Gemini Pro can return bounding boxes for objects in images.

QR Code Decoder

I was in a meeting earlier this week where one of the participants shared a slide with a QR code (for joining a live survey tool). I didn't have my phone with me, so I needed a way to turn that QR code into a regular URL.

https://tools.simonwillison.net/qr

Knocking up this QR decoder in Claude Artifacts took just a few seconds:

Build an artifact (no react) that lets me paste in a QR code and displays the decoded information, with a hyperlink if necessary

[ ... ]

have a file open box that also lets you drag and drop and add a onpaste handler to the page that catches pasted images as well

Full conversation here.

Image Converter and Page Downloader

Another very quick prototype. On Hacker News someone demonstrated a neat idea for a tool that let you drop photos onto a page and it would bake them into the page as base64 URLs such that you could "save as HTML" and get a self-contained page with a gallery.

I suggested they could add a feature that generated a "Download link" with the new page baked in - useful on mobile phones that don't let you "Save as HTML" - and got Claude to knock up a quick prototype:

In this case I shared the code in a Gist and then used the new-to-me https://gistpreview.github.io/?GIST_ID_GOES_HERE trick to render the result:

https://gistpreview.github.io/?14a2c3ef508839f26377707dbf5dd329

gistpreview turns out to be a really quick way to turn a LLM-generated demo into a page people can view.

Code, Claude transcript

HTML Entity Escaper

Another example of on-demand software: I needed to escape the HTML entities in a chunk of text on my phone, so I got Claude to build me a tool for that:

https://claude.site/artifacts/46897436-e06e-4ccc-b8f4-3df90c47f9bc

Here's the prompt I used:

Build an artifact (no react) where I can paste text into a textarea and it will return that text with all HTML entities - single and double quotes and less than greater than ampersand - correctly escaped. The output should be in a textarea accompanied by a "Copy to clipboard" button which changes text to "Copied!" for 1.5s after you click it. Make it mobile friendly

Claude transcript

text-wrap-balance-nav

Inspired by Terence Eden I decided to do a quick experiment with the text-wrap: balance CSS property. I got Claude to build me an example nav bar with a slider and a checkbox. I wrote about that here.

https://tools.simonwillison.net/text-wrap-balance-nav

ARES Phonetic Alphabet Converter

I was volunteering as a HAM radio communications operator for the Half Moon Bay Pumpkin Run and got nervous that I'd mess up using the phonetic alphabet - so I had Claude build me this tool:

https://claude.site/artifacts/aaadab20-968a-4291-8ce9-6435f6d53f4c

Claude transcript here. Amusingly it built it in Python first, then switched to JavaScript after I reminded it that I wanted "an interactive web app".

This is so useful, and so much fun!

As you can see, I'm a heavy user of this feature - I just described 14 projects produced in a single week. I've been using artifacts since they were released on 20th June (alongside the excellent Claude 3.5 Sonnet, still my daily-driver LLM) and I'm now at a point where I fire up a new interactive artifact several times a day.

I'm using artifacts for idle curiosity, rapid prototyping, library research and to spin up tools that solve immediate problems.

Most of these tools took less than five minutes to build. A few of the more involved ones took longer than that, but even the OpenAI Audio one took 11:55am to 12:07pm for the first version and 12:18pm to 12:27pm for the second iteration - so 21 minutes total.

Take a look at my claude-artifacts tag for even more examples, including SVG to JPG/PNG, Markdown and Math Live Renderer and Image resize and quality comparison.

I also have a dashboard of every post that links to my tools.simonwillison.net site, and the underlying simonw/tools GitHub repo includes more unlisted tools, most of which link to their Claude conversation transcripts in their commit history.

I'm beginning to get a little frustrated at their limitations - in particular the way artifacts are unable to make API calls, submit forms or even link out to other pages. I'll probably end up spinning up my own tiny artifacts alternative based on everything I've learned about them so far.

If you're not using artifacts, I hope I've given you a sense of why they're one of my current favourite LLM-based tools.

Tags: javascript, projects, tools, ai, pyodide, generative-ai, llms, ai-assisted-programming, anthropic, claude, claude-artifacts, claude-3-5-sonnet, prompt-to-app

marimo.app

2024-06-29T23:07:42+00:00

marimo.app

The Marimo reactive notebook (previously) - a Python notebook that's effectively a cross between Jupyter and Observable - now also has a version that runs entirely in your browser using WebAssembly and Pyodide. Here's the documentation.

Tags: python, jupyter, observable, webassembly, pyodide, marimo

Pyodide 0.26 Release

2024-05-28T19:04:17+00:00

Pyodide 0.26 Release

PyOdide provides Python packaged for browser WebAssembly alongside an ecosystem of additional tools and libraries to help Python and JavaScript work together.

The latest release bumps the Python version up to 3.12, and also adds support for pygame-ce, allowing games written using pygame to run directly in the browser.

The PyOdide community also just landed a 14-month-long PR adding support to cibuildwheel, which should make it easier to ship binary wheels targeting PyOdide.

Via @pyodide

Tags: javascript, pygame, python, webassembly, pyodide

Bringing Python to Workers using Pyodide and WebAssembly

2024-04-02T16:09:57+00:00

Bringing Python to Workers using Pyodide and WebAssembly

Cloudflare Workers is Cloudflare’s serverless hosting tool for deploying server-side functions to edge locations in their CDN.

They just released Python support, accompanied by an extremely thorough technical explanation of how they got that to work. The details are fascinating.

Workers runs on V8 isolates, and the new Python support was implemented using Pyodide (CPython compiled to WebAssembly) running inside V8.

Getting this to work performantly and ergonomically took a huge amount of work.

There are too many details in here to effectively summarize, but my favorite detail is this one:

“We scan the Worker’s code for import statements, execute them, and then take a snapshot of the Worker’s WebAssembly linear memory. Effectively, we perform the expensive work of importing packages at deploy time, rather than at runtime.”

Via Hacker News

Tags: python, serverless, cloudflare, webassembly, pyodide

urllib3 2.2.0

2024-01-30T16:31:00+00:00

urllib3 2.2.0

Highlighted feature: “urllib3 now works in the browser”—the core urllib3 library now includes code that can integrate with Pyodide, using the browser’s fetch() or XMLHttpRequest APIs to make HTTP requests (to CORS-enabled endpoints).

Tags: python, webassembly, pyodide, cors

Weeknotes: sqlite-utils 3.31, download-esm, Python in a sandbox

2023-05-10T22:07:01+00:00

A couple of speaking appearances last week - one planned, one unplanned. Plus sqlite-utils 3.31, download-esm and a new TIL.

Prompt injection video, Leaked Google document audio

I participated in the LangChain webinar about prompt injection. The session was recorded, so I extracted my 12 minute introduction to the topic and turned it into a blog post complete with a Whisper transcription, a video and the slides I used in the talk.

Then on Thursday I wrote about the leaked internal Google document that argued that Google and OpenAI have no meaningful moat given the accelerating pace of open source LLM research.

This lead to a last minute invitation to participate in a Latent Space Twitter Space about the document, which is now available as a podcast.

sqlite-utils 3.31

I realized that sqlite-utils had been quietly accumulating small fixes and pull requests since the 3.30 release last October, and spent a day tidying those up and turning them into a release.

Notably, four contributors get credited in the release notes: Chris Amico, Kenny Song, Martin Carpenter and Scott Perry.

Key changes are listed below:

Automatically locates the SpatiaLite extension on Apple Silicon. Thanks, Chris Amico. (#536)

New --raw-lines option for the sqlite-utils query and sqlite-utils memory commands, which outputs just the raw value of the first column of evy row. (#539)

Fixed a bug where table.upsert_all() failed if the not_null= option was passed. (#538)

table.convert(..., skip_false=False) and sqlite-utils convert --no-skip-false options, for avoiding a misfeature where the convert() mechanism skips rows in the database with a falsey value for the specified column. Fixing this by default would be a backwards-incompatible change and is under consideration for a 4.0 release in the future. (#527)

Tables can now be created with self-referential foreign keys. Thanks, Scott Perry. (#537)

sqlite-utils transform no longer breaks if a table defines default values for columns. Thanks, Kenny Song. (#509)

Fixed a bug where repeated calls to table.transform() did not work correctly. Thanks, Martin Carpenter. (#525)

download-esm

As part of my ongoing mission to figure out how to write modern JavaScript without surrendering to one of the many different JavaScript build tools, I built download-esm - a Python CLI tool for downloading the ECMAScript module versions of an npm package along with all of their module dependencies.

I wrote more about my justification for building that tool in download-esm: a tool for downloading ECMAScript modules.

Running Python in a Deno/Pyodide sandbox

I'm still trying to find the best way to run untrusted Python code in a safe WebAssembly sandbox.

My latest attempt takes advantage of Pyodide and Deno. It was inspired by this comment by Milan Raj, showing how Deno can load Pyodide now. Pyodide was previously only available in web browsers.

I came up with a somewhat convoluted mechanism that starts a Deno process running in a Python subprocess and then runs Pyodide inside of Deno.

See Running Python code in a Pyodide sandbox via Deno for the code and my thoughts on next steps for that prototype.

Blog entries this week

Releases this week

sqlite-utils 3.31 - 2023-05-08
Python CLI utility and library for manipulating SQLite databases

TIL this week

Running Python code in a Pyodide sandbox via Deno - 2023-05-10

Tags: projects, python, weeknotes, deno, sqlite-utils, pyodide

PyScript Updates: Bytecode Alliance, Pyodide, and MicroPython

2022-11-09T22:26:09+00:00

PyScript Updates: Bytecode Alliance, Pyodide, and MicroPython

Absolutely huge news about Python on the Web tucked into this announcement: Anaconda have managed to get a version of MicroPython compiled to WebAssembly running in the browser. Pyodide weighs in at around 6.5MB compressed, but the MicroPython build is just 303KB—the size of a large image. This makes Python in the web browser applicable to so many more potential areas.

Tags: python, webassembly, pyodide

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

Weeknotes: Datasette Lite, nogil Python, HYTRADBOI

2022-05-06T22:56:39+00:00

My big project this week was Datasette Lite, a new way to run Datasette directly in a browser, powered by WebAssembly and Pyodide. I also continued my research into running SQL queries in parallel, described last week. Plus I spoke at HYTRADBOI.

Datasette Lite

This started out as a research project, inspired by the excitement around Python in the browser from PyCon US last week (which I didn't attend, but observed with some jealousy on Twitter).

I've been wanting to explore this possibility for a while. JupyterLite had convinced me that it would be feasible to run Datasette using Pyodide, especially after I found out that the sqlite3 module from the Python standard library works there already.

I have a private "notes" GitHub repository which I use to keep notes in GitHub issues. I started a thread there researching the possibility of running an ASGI application in Pyodide, thinking that might be a good starting point to getting Datasette to work.

The proof of concept moved remarkably quickly, especially once I realized that Service Workers weren't going to work but Web Workers might.

Once I had comitted to Datasette Lite as a full project I started a new repository for it and transferred across my initial prototype issue thread. You can read that full thread for a blow-by-blow account of how my research pulled together in datasette-lite issue #1.

The rest of the project is documented in detail in my blog post.

Since launching it the biggest change I've made was a change of URL: since it's clearly going to be a core component of the Datasette project going forward I promoted it from simonw.github.io/datasette-lite/ to its new permanent home at lite.datasette.io. It's still hosted by GitHub Pages - here's my TIL about setting up the new domain.

It may have started as a proof of concept tech demo, but the response to it so far has convinced me that I should really take it seriously. Being able to host Datasette without needing to run any server-side code at all is an incredibly compelling experience.

It doesn't matter how hard I work on getting the Datasette deployment experience as easy as possible, static file hosting will always be an order of magnitude more accessible. And even at this early stage Datasette Lite is already proving to be a genuinely useful way to run the software.

As part of this research I also shipped sqlite-utils 3.26.1 with a minor dependency fix that means it works in Pyodide now. You can try that out by running the following in the Pyodide REPL:

>>> import micropip
>>> await micropip.install("sqlite-utils")
>>> import sqlite_utils
>>> db = sqlite_utils.Database(memory=True)
>>> list(db.query("select 3 * 5"))
[{'3 * 5': 15}]

Parallel SQL queries work... if you can get rid of the GIL

Last week I described my effort to implement Parallel SQL queries for Datasette.

The idea there was that many Datasette pages execute multiple SQL queries - a count(*) and a select ... limit 101 for example - that could be run in parallel instead of serial, for a potential improvement in page load times.

My hope was that I could get away with this despite Python's infamous Global Interpreter Lock because the sqlite3 C module releases the GIL when it executes a query.

My initial results weren't showing an increase in performance, even while the queries were shown to be overlapping each other. I opened a research thread and spent some time this week investigating.

My conclusion, sadly, was that the GIL was indeed to blame. sqlite3 releases the GIL to execute the query, but there's still a lot of work that happens in Python land itself - most importantly the code that assembles the objects that represent the rows returned by the query, which is still subject to the GIL.

Then this comment on a thread about the GIL on Lobsters reminded me of the nogil fork of Python by Sam Gross, who has been working on this problem for several years now.

Since that fork has a Docker image trying it out was easy... and to my amazement it worked! Running my parallel queries implementation against nogil Python reduced a page load time from 77ms to 47ms.

Sam's work is against Python 3.9, but he's discussing options for bringing his improvemets into Python itself with the core maintainers. I'm hopeful that this might happen in the next few years. It's an incredible piece of work.

An amusing coincidence: one restriction of WASM and Pyodide is that they can't start new threads - so as part of getting Datasette to work on that platform I had to add a new setting that disables the ability to run SQL queries in threads entirely!

datasette-copy-to-memory

One question I found myself asking while investigating parallel SQL queries (before I determined that the GIL was to blame) was whether parallel SQLite queries against the same database file were suffering from some form of file locking or contention.

To rule that out, I built a new plugin: datasette-copy-to-memory - which reads a SQLite database from disk and copies it into an in-memory database when Datasette first starts up.

This didn't make an observable difference in performance, but I've not tested it extensively - especially not against larger databases using servers with increased amounts of available RAM.

If you're inspired to give this plugin a go I'd love to hear about your results.

asgi-gzip and datasette-gzip

I mentioned datasette-gzip last week: a plugin that acts as a wrapper around the excellent GZipMiddleware from Starlette.

The performance improvements from this - especially for larger HTML tables, which it turns out compress extremely well - were significant. Enough so that I plan to bring gzip support into Datasette core very shortly.

Since I don't want to add the whole of Starlette as a dependency just to get gzip support, I extracted that code out into a new Python package called asgi-gzip.

The obvious risk with doing this is that it might fall behind the excellent Starlette implementation. So I came up with a pattern based on Git scraping that would automatically open a new GitHub issue should the borrowed Starlette code change in the future.

I wrote about that pattern in Automatically opening issues when tracked file content changes.

Speaking at HYTRADBOI

I spoke at the HYTRADBOI conference last week: Have You Tried Rubbing A Database On It.

HYTRADBOI was organized by Jamie Brandon. It was a neat event, with a smart format: 34 pre-recorded 10 minute long talks, arranged into a schedule to encourage people to watch and discuss them at specific times during the day of the event.

It's worth reading Jamie's postmortem of the event for some insightful thinking on online event organization.

My talk was Datasette: a big bag of tricks for solving interesting problems using SQLite. It ended up working out as a lightning-fast 10 minute tutorial on using the sqlite-utils CLI to clean up some data (in this case Manatee Carcass Recovery Locations in Florida since 1974) and then using Datasette to explore and publish it.

I've posted some basic notes to accompany the talk. My plan is to use this as the basis for an official tutorial on sqlite-utils for the tutorials section of the Datasette website.

Releases this week

datasette: 0.62a0 - (111 releases total) - 2022-05-02
An open source multi-tool for exploring and publishing data
sqlite-utils: 3.26.1 - (100 releases total) - 2022-05-02
Python CLI utility and library for manipulating SQLite databases
click-default-group-wheel: 1.2.2 - 2022-05-02
Extends click.Group to invoke a command without explicit subcommand name (this version publishes a wheel)
s3-credentials: 0.11 - (11 releases total) - 2022-05-01
A tool for creating credentials for accessing S3 buckets
datasette-copy-to-memory: 0.2 - (5 releases total) - 2022-04-30
Copy database files into an in-memory database on startup
datasette-gzip: 0.2 - (2 releases total) - 2022-04-28
Add gzip compression to Datasette
asgi-gzip: 0.1 - 2022-04-28
gzip middleware for ASGI applications, extracted from Starlette

TIL this week

Tags: gil, projects, python, speaking, datasette, asgi, webassembly, weeknotes, pyodide, datasette-lite

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

sqlite-utils 3.26.1

2022-05-02T18:43:14+00:00

sqlite-utils 3.26.1

I released sqlite-utils 3.36.1 with one tiny but exciting feature: I fixed its one dependency that wasn’t published as a pure Python wheel, which means it can now be used with Pyodide—Python compiled to WebAssembly running in your browser!

Via @simonw

Tags: python, webassembly, sqlite-utils, pyodide

PyScript demos

2022-04-30T21:50:53+00:00

PyScript demos

PyScript was announced at PyCon this morning. It’s a new open source project that provides Web Components built on top of Pyodide, allowing you to use Python directly within your HTML pages in a way that is executed using a WebAssembly copy of Python running in your browser. These demos really help illustrate what it can do—it’s a fascinating new piece of the Python web ecosystem.

Via @simonw

Tags: python, web-components, webassembly, pyodide

WebAssembly in my Browser Desktop Environment

2022-03-29T01:26:54+00:00

WebAssembly in my Browser Desktop Environment

Dustin Brett built the WebAssembly demo to end all WebAssembly demos: his daedalOS browser desktop environment simulates a Windows-style operating system, and bundles WebAssembly projects that include v86 for 486 emulation, js-dos for DOS emulation to run Doom, BoxedWine to run Wine applications like Notepad++, Ruffle to emulate Flash, ffmpeg.wasm to power audio and video conversion, WASM-ImageMagick for image conversion, Pyodide for a Python shell and more besides that!

Via Hacker News

Tags: webassembly, pyodide

Weeknotes: datasette-jupyterlite, s3-credentials and a Python packaging talk

2021-11-05T05:04:58+00:00

My big project this week was s3-credentials, described yesterday - but I also put together a fun expermiental Datasette plugin bundling JupyterLite and wrote up my PyGotham talk on Python packaging.

datasette-jupyterlite

JupyterLite is absolutely incredible: it's a full, working distribution of Jupyter that runs entirely in a browser, thanks to a Python interpreter (and various other parts of the scientific Python stack) that has been compiled to WebAssembly by the Pyodide project.

Since it's just static JavaScript (and WASM modules) it's possible to host it anywhere that can run a web server.

Datasette runs a web server...

So, I built datasette-jupyterlite - a Datasette plugin that bundles JupyterLite and serves it up as part of the Datasette instance.

You can try a live demo here:

Here's some Python code that will retrieve data from the associated Datasette instance and pull it into a Pandas DataFrame:

import pandas, pyodide
pandas.read_csv(pyodide.open_url(
  "https://latest-with-plugins.datasette.io/github/stars.csv")
)

(I haven't yet found a way to do this with a relative rather than absolute URL.)

The best part of this is that it works in Datasette Desktop! You can install the plugin using the "Install and manage plugins" menu item to get a version of Jupyter running in Python running in WebAssembly running in V8 running in Chromium running in Electron.

The plugin implementation is just 30 lines of code - it uses the jupyterlite Python package which bundles a .tgz file containing all of the required static assets, then serves files directly out of that tarfile.

Also this week

My other projects from this week are already written about on the blog:

s3-credentials: a tool for creating credentials for S3 buckets introduces a new CLI tool I built that automates the process of creating new, long-lived credentials that provide read-only, read-write or write-only access to just a single specified S3 bucket.
How to build, test and publish an open source Python library is a detailed write-up of the 10 minute workshop I presented at PyGotham this year showing how to create a Python library, bundle it up as a package using setup.py, publish it to PyPI and then set up GitHub Actions to test and publish future releases.

Releases this week

s3-credentials: 0.4 - (4 releases total) - 2021-11-04
A tool for creating credentials for accessing S3 buckets
datasette-notebook: 0.2a0 - (4 releases total) - 2021-11-02
A markdown wiki and dashboarding system for Datasette
datasette-jupyterlite: 0.1a0 - 2021-11-01
JupyterLite as a Datasette plugin

TIL this week

Tags: projects, jupyter, datasette, webassembly, weeknotes, pyodide

Pyodide: Bringing the scientific Python stack to the browser

2019-04-17T04:23:33+00:00

Pyodide: Bringing the scientific Python stack to the browser

More fun with WebAssembly: Pyodide attempts (and mostly succeeds) to bring the full Python data stack to the browser: CPython, NumPy, Pandas, Scipy, and Matplotlib. Also includes interesting bridge tools for e.g. driving a canvas element from Python. Really interesting project from the Firefox Data Platform team.

Via Hacker News

Tags: mozilla, pandas, python, scipy, webassembly, numpy, pyodide