Simon Willison's Weblog: fly

datasette-agent-sprites 0.1a0

2026-05-21T18:21:07+00:00

Release: datasette-agent-sprites 0.1a0

A Datasette Agent plugin for running commands in a Fly Sprites sandbox.

Tags: sandboxing, datasette, fly, datasette-agent

An Update on Heroku

2026-02-06T18:44:21+00:00

An Update on Heroku

An ominous headline to see on the official Heroku blog and yes, it's bad news.

Today, Heroku is transitioning to a sustaining engineering model focused on stability, security, reliability, and support. Heroku remains an actively supported, production-ready platform, with an emphasis on maintaining quality and operational excellence rather than introducing new features. We know changes like this can raise questions, and we want to be clear about what this means for customers.

Based on context I'm guessing a "sustaining engineering model" (this definitely isn't a widely used industry term) means that they'll keep the lights on and that's it.

This is a very frustrating piece of corporate communication. "We want to be clear about what this means for customers" - then proceeds to not be clear about what this means for customers.

Why are they doing this? Here's their explanation:

We’re focusing our product and engineering investments on areas where we can deliver the greatest long-term customer value, including helping organizations build and deploy enterprise-grade AI in a secure and trusted way.

My blog is the only project I have left running on Heroku. I guess I'd better migrate it away (probably to Fly) before Salesforce lose interest completely.

Tags: salesforce, heroku, fly

Introducing Deno Sandbox

2026-02-03T22:44:50+00:00

Introducing Deno Sandbox

Here's a new hosted sandbox product from the Deno team. It's actually unrelated to Deno itself - this is part of their Deno Deploy SaaS platform. As such, you don't even need to use JavaScript to access it - you can create and execute code in a hosted sandbox using their deno-sandbox Python library like this:

export DENO_DEPLOY_TOKEN="... API token ..."
uv run --with deno-sandbox python

Then:

from deno_sandbox import DenoDeploy

sdk = DenoDeploy()

with sdk.sandbox.create() as sb:
    # Run a shell command
    process = sb.spawn(
        "echo", args=["Hello from the sandbox!"]
    )
    process.wait()
    # Write and read files
    sb.fs.write_text_file(
        "/tmp/example.txt", "Hello, World!"
    )
    print(sb.fs.read_text_file(
        "/tmp/example.txt"
    ))

There’s a JavaScript client library as well. The underlying API isn’t documented yet but appears to use WebSockets.

There’s a lot to like about this system. Sandboxe instances can have up to 4GB of RAM, get 2 vCPUs, 10GB of ephemeral storage, can mount persistent volumes and can use snapshots to boot pre-configured custom images quickly. Sessions can last up to 30 minutes and are billed by CPU time, GB-h of memory and volume storage usage.

When you create a sandbox you can configure network domains it’s allowed to access.

My favorite feature is the way it handles API secrets.

with sdk.sandboxes.create(
    allowNet=["api.openai.com"],
    secrets={
        "OPENAI_API_KEY": {
            "hosts": ["api.openai.com"],
            "value": os.environ.get("OPENAI_API_KEY"),
        }
    },
) as sandbox:
    # ... $OPENAI_API_KEY is available

Within the container that $OPENAI_API_KEY value is set to something like this:

DENO_SECRET_PLACEHOLDER_b14043a2f578cba...

Outbound API calls to api.openai.com run through a proxy which is aware of those placeholders and replaces them with the original secret.

In this way the secret itself is not available to code within the sandbox, which limits the ability for malicious code (e.g. from a prompt injection) to exfiltrate those secrets.

From a comment on Hacker News I learned that Fly have a project called tokenizer that implements the same pattern. Adding this to my list of tricks to use with sandoxed environments!

Via Hacker News

Tags: python, sandboxing, security, deno, fly

The Design & Implementation of Sprites

2026-01-15T16:08:27+00:00

The Design & Implementation of Sprites

I wrote about Sprites last week. Here's Thomas Ptacek from Fly with the insider details on how they work under the hood.

I like this framing of them as "disposable computers":

Sprites are ball-point disposable computers. Whatever mark you mean to make, we’ve rigged it so you’re never more than a second or two away from having a Sprite to do it with.

I've noticed that new Fly Machines can take a while (up to around a minute) to provision. Sprites solve that by keeping warm pools of unused machines in multiple regions, which is enabled by them all using the same container:

Now, today, under the hood, Sprites are still Fly Machines. But they all run from a standard container. Every physical worker knows exactly what container the next Sprite is going to start with, so it’s easy for us to keep pools of “empty” Sprites standing by. The result: a Sprite create doesn’t have any heavy lifting to do; it’s basically just doing the stuff we do when we start a Fly Machine.

The most interesting detail is how the persistence layer works. Sprites only charge you for data you have written that differs from the base image and provide ~300ms checkpointing and restores - it turns out that's power by a custom filesystem on top of S3-compatible storage coordinated by Litestream-replicated local SQLite metadata:

We still exploit NVMe, but not as the root of storage. Instead, it’s a read-through cache for a blob on object storage. S3-compatible object stores are the most trustworthy storage technology we have. I can feel my blood pressure dropping just typing the words “Sprites are backed by object storage.” [...]

The Sprite storage stack is organized around the JuiceFS model (in fact, we currently use a very hacked-up JuiceFS, with a rewritten SQLite metadata backend). It works by splitting storage into data (“chunks”) and metadata (a map of where the “chunks” are). Data chunks live on object stores; metadata lives in fast local storage. In our case, that metadata store is kept durable with Litestream. Nothing depends on local storage.

Via @tqbf

Tags: sandboxing, sqlite, thomas-ptacek, software-architecture, fly, litestream

Fly's new Sprites.dev addresses both developer sandboxes and API sandboxes at the same time

2026-01-09T23:57:12+00:00

New from Fly.io today: Sprites.dev. Here's their blog post and YouTube demo. It's an interesting new product that's quite difficult to explain - Fly call it "Stateful sandbox environments with checkpoint & restore" but I see it as hitting two of my current favorite problems: a safe development environment for running coding agents and an API for running untrusted code in a secure sandbox.

Disclosure: Fly sponsor some of my work. They did not ask me to write about Sprites and I didn't get preview access prior to the launch. My enthusiasm here is genuine.

Developer sandboxes

I predicted earlier this week that "we’re due a Challenger disaster with respect to coding agent security" due to the terrifying way most of us are using coding agents like Claude Code and Codex CLI. Running them in --dangerously-skip-permissions mode (aka YOLO mode, where the agent acts without constantly seeking approval first) unlocks so much more power, but also means that a mistake or a malicious prompt injection can cause all sorts of damage to your system and data.

The safe way to run YOLO mode is in a robust sandbox, where the worst thing that can happen is the sandbox gets messed up and you have to throw it away and get another one.

That's the first problem Sprites solves:

curl https://sprites.dev/install.sh | bash

sprite login
sprite create my-dev-environment
sprite console -s my-dev-environment

That's all it takes to get SSH connected to a fresh environment, running in an ~8GB RAM, 8 CPU server. And... Claude Code and Codex and Gemini CLI and Python 3.13 and Node.js 22.20 and a bunch of other tools are already installed.

The first time you run claude it neatly signs you in to your existing account with Anthropic. The Sprites VM is persistent so future runs of sprite console -s will get you back to where you were before.

... and it automatically sets up port forwarding, so you can run a localhost server on your Sprite and access it from localhost:8080 on your machine.

There's also a command you can run to assign a public URL to your Sprite, so anyone else can access it if they know the secret URL.

Storage and checkpoints

In the blog post Kurt Mackey argues that ephemeral, disposable sandboxes are not the best fit for coding agents:

The state of the art in agent isolation is a read-only sandbox. At Fly.io, we’ve been selling that story for years, and we’re calling it: ephemeral sandboxes are obsolete. Stop killing your sandboxes every time you use them. [...]

If you force an agent to, it’ll work around containerization and do work . But you’re not helping the agent in any way by doing that. They don’t want containers. They don’t want “sandboxes”. They want computers.

[...] with an actual computer, Claude doesn’t have to rebuild my entire development environment every time I pick up a PR.

Each Sprite gets a proper filesystem which persists in between sessions, even while the Sprite itself shuts down after inactivity. It sounds like they're doing some clever filesystem tricks here, I'm looking forward to learning more about those in the future.

There are some clues on the homepage:

You read and write to fast, directly attached NVMe storage. Your data then gets written to durable, external object storage. [...]

You don't pay for allocated filesystem space, just the blocks you write. And it's all TRIM friendly, so your bill goes down when you delete things.

The really clever feature is checkpoints. You (or your coding agent) can trigger a checkpoint which takes around 300ms. This captures the entire disk state and can then be rolled back to later.

For more on how that works, run this in a Sprite:

cat /.sprite/docs/agent-context.md

Here's the relevant section:

## Checkpoints
- Point-in-time checkpoints and restores available
- Copy-on-write implementation for storage efficiency
- Last 5 checkpoints mounted at `/.sprite/checkpoints`
- Checkpoints capture only the writable overlay, not the base image

Or run this to see the --help for the command used to manage them:

sprite-env checkpoints --help

Which looks like this:

sprite-env checkpoints - Manage environment checkpoints

USAGE:
    sprite-env checkpoints <subcommand> [options]

SUBCOMMANDS:
    list [--history <ver>]  List all checkpoints (optionally filter by history version)
    get <id>                Get checkpoint details (e.g., v0, v1, v2)
    create                  Create a new checkpoint (auto-versioned)
    restore <id>            Restore from a checkpoint (e.g., v1)

NOTE:
    Checkpoints are versioned as v0, v1, v2, etc.
    Restore returns immediately and triggers an async restore that restarts the environment.
    The last 5 checkpoints are mounted at /.sprite/checkpoints for direct file access.

EXAMPLES:
    sprite-env checkpoints list
    sprite-env checkpoints list --history v1.2.3
    sprite-env checkpoints get v2
    sprite-env checkpoints create
    sprite-env checkpoints restore v1

Really clever use of Claude Skills

I'm a big fan of Skills, the mechanism whereby Claude Code (and increasingly other agents too) can be given additional capabilities by describing them in Markdown files in a specific directory structure.

In a smart piece of design, Sprites uses pre-installed skills to teach Claude how Sprites itself works. This means you can ask Claude on the machine how to do things like open up ports and it will talk you through the process.

There's all sorts of interesting stuff in the /.sprite folder on that machine - digging in there is a great way to learn more about how Sprites works.

A sandbox API

Also from my predictions post earlier this week: "We’re finally going to solve sandboxing". I am obsessed with this problem: I want to be able to run untrusted code safely, both on my personal devices and in the context of web services I'm building for other people to use.

I have so many things I want to build that depend on being able to take untrusted code - from users or from LLMs or from LLMs-driven-by-users - and run that code in a sandbox where I can be confident that the blast radius if something goes wrong is tightly contained.

Sprites offers a clean JSON API for doing exactly that, plus client libraries in Go and TypeScript and coming-soon Python and Elixir.

From their quick start:

# Create a new sprite
curl -X PUT https://api.sprites.dev/v1/sprites/my-sprite \
-H "Authorization: Bearer $SPRITES_TOKEN"

# Execute a command
curl -X POST https://api.sprites.dev/v1/sprites/my-sprite/exec \
-H "Authorization: Bearer $SPRITES_TOKEN" \
-d '{"command": "echo hello"}'

You can also checkpoint and rollback via the API, so you can get your environment exactly how you like it, checkpoint it, run a bunch of untrusted code, then roll back to the clean checkpoint when you're done.

Managing network access is an important part of maintaining a good sandbox. The Sprites API lets you configure network access policies using a DNS-based allow/deny list like this:

curl -X POST \
  "https://api.sprites.dev/v1/sprites/{name}/policy/network" \
  -H "Authorization: Bearer $SPRITES_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "rules": [
      {
        "action": "allow",
        "domain": "github.com"
      },
      {
        "action": "allow",
        "domain": "*.npmjs.org"
      }
    ]
  }'

Scale-to-zero billing

Sprites have scale-to-zero baked into the architecture. They go to sleep after 30 seconds of inactivity, wake up quickly when needed and bill you for just the CPU hours, RAM hours and GB-hours of storage you use while the Sprite is awake.

Fly estimate a 4 hour intensive coding session as costing around 46 cents, and a low traffic web app with 30 hours of wake time per month at ~$4.

(I calculate that a web app that consumes all 8 CPUs and all 8GBs of RAM 24/7 for a month would cost ((7 cents * 8 * 24 * 30) + (4.375 cents * 8 * 24 * 30)) / 100 = $655.2 per month, so don't necessarily use these as your primary web hosting solution for an app that soaks up all available CPU and RAM!)

Two of my favorite problems at once

I was hopeful that Fly would enter the developer-friendly sandbox API market, especially given other entrants from companies like Cloudflare and Modal and E2B.

I did not expect that they'd tackle the developer sandbox problem at the same time, and with the same product!

My one concern here is that it makes the product itself a little harder to explain.

I'm already spinning up some prototypes of sandbox-adjacent things I've always wanted to build, and early signs are very promising. I'll write more about these as they turn into useful projects.

Update: Here's some additional colour from Thomas Ptacek on Hacker News:

This has been in the works for quite awhile here. We put a long bet on "slow create fast start/stop" --- which is a really interesting and useful shape for execution environments --- but it didn't make sense to sandboxers, so "fast create" has been the White Whale at Fly.io for over a year.

Tags: sandboxing, thomas-ptacek, ai, fly, coding-agents

How I automate my Substack newsletter with content from my blog

2025-11-19T22:00:34+00:00

I sent out my weekly-ish Substack newsletter this morning and took the opportunity to record a YouTube video demonstrating my process and describing the different components that make it work. There's a lot of digital duct tape involved, taking the content from Django+Heroku+PostgreSQL to GitHub Actions to SQLite+Datasette+Fly.io to JavaScript+Observable and finally to Substack.

The core process is the same as I described back in 2023. I have an Observable notebook called blog-to-newsletter which fetches content from my blog's database, filters out anything that has been in the newsletter before, formats what's left as HTML and offers a big "Copy rich text newsletter to clipboard" button.

I click that button, paste the result into the Substack editor, tweak a few things and hit send. The whole process usually takes just a few minutes.

I make very minor edits:

I set the title and the subheading for the newsletter. This is often a direct copy of the title of the featured blog post.
Substack turns YouTube URLs into embeds, which often isn't what I want - especially if I have a YouTube URL inside a code example.
Blocks of preformatted text often have an extra blank line at the end, which I remove.
Occasionally I'll make a content edit - removing a piece of content that doesn't fit the newsletter, or fixing a time reference like "yesterday" that doesn't make sense any more.
I pick the featured image for the newsletter and add some tags.

That's the whole process!

The Observable notebook

The most important cell in the Observable notebook is this one:

raw_content = {
  return await (
    await fetch(
      `https://datasette.simonwillison.net/simonwillisonblog.json?sql=${encodeURIComponent(
        sql
      )}&_shape=array&numdays=${numDays}`
    )
  ).json();
}

This uses the JavaScript fetch() function to pull data from my blog's Datasette instance, using a very complex SQL query that is composed elsewhere in the notebook.

Here's a link to see and execute that query directly in Datasette. It's 143 lines of convoluted SQL that assembles most of the HTML for the newsletter using SQLite string concatenation! An illustrative snippet:

with content as (
  select
    id,
    'entry' as type,
    title,
    created,
    slug,
    '<h3><a href="' || 'https://simonwillison.net/' || strftime('%Y/', created)
      || substr('JanFebMarAprMayJunJulAugSepOctNovDec', (strftime('%m', created) - 1) * 3 + 1, 3) 
      || '/' || cast(strftime('%d', created) as integer) || '/' || slug || '/' || '">' 
      || title || '</a> - ' || date(created) || '</h3>' || body
      as html,
    'null' as json,
    '' as external_url
  from blog_entry
  union all
  # ...

My blog's URLs look like /2025/Nov/18/gemini-3/ - this SQL constructs that three letter month abbreviation from the month number using a substring operation.

This is a terrible way to assemble HTML, but I've stuck with it because it amuses me.

The rest of the Observable notebook takes that data, filters out anything that links to content mentioned in the previous newsletters and composes it into a block of HTML that can be copied using that big button.

Here's the recipe it uses to turn HTML into rich text content on a clipboard suitable for Substack. I can't remember how I figured this out but it's very effective:

Object.assign(
  html`<button style="font-size: 1.4em; padding: 0.3em 1em; font-weight: bold;">Copy rich text newsletter to clipboard`,
  {
    onclick: () => {
      const htmlContent = newsletterHTML;
      // Create a temporary element to hold the HTML content
      const tempElement = document.createElement("div");
      tempElement.innerHTML = htmlContent;
      document.body.appendChild(tempElement);
      // Select the HTML content
      const range = document.createRange();
      range.selectNode(tempElement);
      // Copy the selected HTML content to the clipboard
      const selection = window.getSelection();
      selection.removeAllRanges();
      selection.addRange(range);
      document.execCommand("copy");
      selection.removeAllRanges();
      document.body.removeChild(tempElement);
    }
  }
)

From Django+Postgresql to Datasette+SQLite

My blog itself is a Django application hosted on Heroku, with data stored in Heroku PostgreSQL. Here's the source code for that Django application. I use the Django admin as my CMS.

Datasette provides a JSON API over a SQLite database... which means something needs to convert that PostgreSQL database into a SQLite database that Datasette can use.

My system for doing that lives in the simonw/simonwillisonblog-backup GitHub repository. It uses GitHub Actions on a schedule that executes every two hours, fetching the latest data from PostgreSQL and converting that to SQLite.

My db-to-sqlite tool is responsible for that conversion. I call it like this:

db-to-sqlite \
  $(heroku config:get DATABASE_URL -a simonwillisonblog | sed s/postgres:/postgresql+psycopg2:/) \
  simonwillisonblog.db \
  --table auth_permission \
  --table auth_user \
  --table blog_blogmark \
  --table blog_blogmark_tags \
  --table blog_entry \
  --table blog_entry_tags \
  --table blog_quotation \
  --table blog_quotation_tags \
  --table blog_note \
  --table blog_note_tags \
  --table blog_tag \
  --table blog_previoustagname \
  --table blog_series \
  --table django_content_type \
  --table redirects_redirect

That heroku config:get DATABASE_URL command uses Heroku credentials in an environment variable to fetch the database connection URL for my blog's PostgreSQL database (and fixes a small difference in the URL scheme).

db-to-sqlite can then export that data and write it to a SQLite database file called simonwillisonblog.db.

The --table options specify the tables that should be included in the export.

The repository does more than just that conversion: it also exports the resulting data to JSON files that live in the repository, which gives me a commit history of changes I make to my content. This is a cheap way to get a revision history of my blog content without having to mess around with detailed history tracking inside the Django application itself.

At the end of my GitHub Actions workflow is this code that publishes the resulting database to Datasette running on Fly.io using the datasette publish fly plugin:

datasette publish fly simonwillisonblog.db \
  -m metadata.yml \
  --app simonwillisonblog-backup \
  --branch 1.0a2 \
  --extra-options "--setting sql_time_limit_ms 15000 --setting truncate_cells_html 10000 --setting allow_facet off" \
  --install datasette-block-robots \
  # ... more plugins

As you can see, there are a lot of moving parts! Surprisingly it all mostly just works - I rarely have to intervene in the process, and the cost of those different components is pleasantly low.

Tags: blogging, django, javascript, postgresql, sql, sqlite, youtube, heroku, datasette, observable, github-actions, fly, newsletter, substack, site-upgrades

You should write an agent

2025-11-07T04:40:12+00:00

You should write an agent

Thomas Ptacek on the Fly blog:

Agents are the most surprising programming experience I’ve had in my career. Not because I’m awed by the magnitude of their powers — I like them, but I don’t like-like them. It’s because of how easy it was to get one up on its legs, and how much I learned doing that.

I think he's right: hooking up a simple agentic loop that prompts an LLM and runs a tool for it any time it request one really is the new "hello world" of AI engineering.

Via Hacker News

Tags: thomas-ptacek, ai, fly, generative-ai, llms, ai-agents

Litestream v0.5.0 is Here

2025-10-03T15:10:21+00:00

Litestream v0.5.0 is Here

I've been running Litestream to backup SQLite databases in production for a couple of years now without incident. The new version has been a long time coming - Ben Johnson took a detour into the FUSE-based LiteFS before deciding that the single binary Litestream approach is more popular - and Litestream 0.5 just landed with this very detailed blog posts describing the improved architecture.

SQLite stores data in pages - 4096 (by default) byte blocks of data. Litestream replicates modified pages to a backup location - usually object storage like S3.

Most SQLite tables have an auto-incrementing primary key, which is used to decide which page the row's data should be stored in. This means sequential inserts to a small table are sent to the same page, which caused previous Litestream to replicate many slightly different copies of that page block in succession.

The new LTX format - borrowed from LiteFS - addresses that by adding compaction, which Ben describes as follows:

We can use LTX compaction to compress a bunch of LTX files into a single file with no duplicated pages. And Litestream now uses this capability to create a hierarchy of compactions:

at Level 1, we compact all the changes in a 30-second time window

at Level 2, all the Level 1 files in a 5-minute window

at Level 3, all the Level 2’s over an hour.

Net result: we can restore a SQLite database to any point in time, using only a dozen or so files on average.

I'm most looking forward to trying out the feature that isn't quite landed yet: read-replicas, implemented using a SQLite VFS extension:

The next major feature we’re building out is a Litestream VFS for read replicas. This will let you instantly spin up a copy of the database and immediately read pages from S3 while the rest of the database is hydrating in the background.

Via Hacker News

Tags: sqlite, fly, litestream, ben-johnson

Phoenix.new is Fly's entry into the prompt-driven app development space

2025-06-23T18:42:02+00:00

Here's a fascinating new entrant into the AI-assisted-programming / coding-agents space by Fly.io, introduced on their blog in Phoenix.new – The Remote AI Runtime for Phoenix: describe an app in a prompt, get a full Phoenix application, backed by SQLite and running on Fly's hosting platform. The official Phoenix.new YouTube launch video is a good way to get a sense for what this does.

Background on Phoenix and Elixir and Fly

First, some background. Phoenix is an open source web framework for Elixir, the Ruby-like language that compiles to Erlang's BEAM bytecode and runs on top of the highly concurrent Erlang runtime. The signature feature of the framework is Phoenix LiveView, a toolkit for building realtime interfaces through streaming diffs to server-side HTML over a WebSocket connection.

Phoenix was created by Chris McCord 11 years ago, and Chris joined Fly nearly four years ago. Phoenix.new is his latest project.

Phoenix LiveView is a really great fit for Fly's geographically distributed application serving infrastructure. Fly co-founder Kurt Mackey wrote about that in April 2021, before they had hired Chris, describing how LiveView benefits from low latency by "moving app processes close to users" - something Fly has been designed to help with from the start.

There's one major challenge though: Elixir is still a niche programming language, which means the number of people out there who are ready to spin up a new Phoenix app has always been artificially limited.

Fly's solution? Get LLMs to shave that learning curve down to almost nothing.

Prompt-driven application development with Phoenix.new

Phoenix.new is a prompt-driven application development platform. You describe what you want to build, then watch as an LLM-powered coding agent writes, tests and iterates on code to help achieve that goal. It's in the same broad category as Lovable, v0.dev and and Val Town's Townie.

One of the most important problems to solve with coding agents is to give them a robust sandbox where they can run code without breaking things outside of that space. Fly, at their heart, are a sandboxing company - their Fly Machines product makes it trivial to spin up a new sandboxed VM in just a few seconds. I'm building Datasette Cloud on Fly for exactly that reason.

I tried out Phoenix.new with the following starter prompt:

A notebook application. Notes are rich text, using a nice visual markdown editors. The first line of a note becomes the title. Notes have a URL that is a 6 character random text string - thise can be used to link notes to each other by dropping in an @ sign and that text string. A hash and then text becomes a tag, clicking a tag goes to a page with that tag on. Notes are all in SQLite FTS so they can be searched with a search tool.

Watching it work was fascinating. It provides a full IDE-style environment in the browser, running a dedicated container and with a forwarded port to allow you to interact with the running application while it is being built.

It spun up a plan.md, planned out the approach and started working through the code. Most interestingly, it constantly tested the application it was building using a testing tool that looked similar to my own shot-scraper. Here's an example of the output from that:

I can see the issue! The page is still showing the "Welcome to your Notebook" message instead of the form. Let me wait a moment for the JavaScript to execute and then check if the form appeared.

$ web http://localhost:4000 --js "setTimeout(() => { document.querySelector('button[phx-click=\"start_creating\"]').click(); }, 500); setTimeout(() => { console.log('Form visible?', !!document.querySelector('textarea')); console.log('Creating note state:', document.body.innerHTML.includes('Create New Note')); }, 2000);"

I'm used to coding agents that write and execute their own tests, but this is the most impressive example I've seen of a coding agent that actively interacts with and tests the live application while it is iterating on the code.

Most interestingly of all, since LiveView is a realtime framework, I could interact directly with the application at the same time as the machine was writing and testing it, seeing any changes it made instantly in my browser!

How did it do? It got there in the end, but with hindsight my initial prompt was a little too complex: I had to rescue it after it got stuck in an error loop trying to implement SQLite full-text search. Just like a project you build yourself it turns out to work much better if you start with the simplest possible version of the application and then iterate on it to add additional features.

One of my favorite details is how Git integration works. Phoenix.new commits constantly as it works, and a menu option for "Clone X to your local computer" then gives you a command that looks like this:

git clone "https://phoenix.new/git/WENQLj...big-random-string...VHFW/$RANDOM/notebook"

Run that locally to get a full copy of the repo! I ran the following to push it to a new repository in my GitHub account:

git remote add github https://github.com/simonw/phoenix-new-notebook.git
git push -u github main

You can see the code (and the commit history) in my simonw/phoenix-new-notebook repo.

How much did I learn?

My initial experiments with Phoenix.new were very much vibe coding - I interacted with the resulting application but didn't pay a great deal of attention to the code that was being written, despite it being presented to me in an IDE that made it very easy to review what was going on.

As a result, I didn't learn much about the low-level syntax details of how Phoenix and Elixir work. I did however get a strong feel for the shape of Elixir and Phoenix at a high level as the code danced past my eye.

It turns out having an LLM write an application in front of you is a great way to start building understanding of what a framework can do.

It’s almost like watching a YouTube livestream of an experienced developer speed running building an application, except that app is exactly what you asked them to build and you can interject and send them in a new direction at any moment.

Expanding beyond Elixir and Phoenix

Chris's announcement included this note:

At this point you might be wondering – can I just ask it to build a Rails app? Or an Expo React Native app? Or Svelte? Or Go?

Yes, you can.

Our system prompt is tuned for Phoenix today, but all languages you care about are already installed. We’re still figuring out where to take this, but adding new languages and frameworks definitely ranks highly in my plans.

The browser-based IDE includes a terminal, and I checked and python3 and python3 -m pip install datasette work there already.

If Fly do evolve this into a framework-agnostic tool for building web apps they'll probably need to rebrand it from Phoenix.new to something a bit more generic!

Phoenix.new is currently priced as a $20/month subscription. Val Town recently switched the pricing for their similar Townie assistant from a subscription to to pay per use, presumably because for many users this kind of tool is something they would only use intermittently, although given the capabilities of Phoenix.new it's possible it could become a monthly driver for people, especially as it expands out to cover more frameworks.

Fly sponsor some of our work on Datasette Cloud (see disclosures), but this article is not sponsored content and Fly did not request or review this post.

Tags: erlang, sqlite, ai, fly, generative-ai, llms, ai-assisted-programming, ai-agents, vibe-coding, coding-agents, prompt-to-app

django-simple-deploy

2025-05-17T12:49:52+00:00

django-simple-deploy

Eric Matthes presented a lightning talk about this project at PyCon US this morning. "Django has a deploy command now". You can run it like this:

pip install django-simple-deploy[fly_io]
# Add django_simple_deploy to INSTALLED_APPS.
python manage.py deploy --automate-all

It's plugin-based (inspired by Datasette!) and the project has stable plugins for three hosting platforms: dsd-flyio, dsd-heroku and dsd-platformsh.

Currently in development: dsd-vps - a plugin that should work with any VPS provider, using Paramiko to connect to a newly created instance and run all of the commands needed to start serving a Django application.

Tags: django, paramiko, plugins, python, heroku, datasette, fly

Fly: We're Cutting L40S Prices In Half

2024-08-16T04:44:04+00:00

Fly: We're Cutting L40S Prices In Half

Interesting insider notes from Fly.io on customer demand for GPUs:

If you had asked us in 2023 what the biggest GPU problem we could solve was, we’d have said “selling fractional A100 slices”. [...] We guessed wrong, and spent a lot of time working out how to maximize the amount of GPU power we could deliver to a single Fly Machine. Users surprised us. By a wide margin, the most popular GPU in our inventory is the A10.

[…] If you’re trying to do something GPU-accelerated in response to an HTTP request, the right combination of GPU, instance RAM, fast object storage for datasets and model parameters, and networking is much more important than getting your hands on an H100.

Via Hacker News

Tags: fly, gpus

Making Machines Move

2024-07-30T21:45:32+00:00

Making Machines Move

Another deep technical dive into Fly.io infrastructure from Thomas Ptacek, this time describing how they can quickly boot up an instance with a persistent volume on a new host (for things like zero-downtime deploys) using a block-level cloning operation, so the new instance gets a volume that becomes accessible instantly, serving proxied blocks of data until the new volume has been completely migrated from the old host.

Tags: ops, thomas-ptacek, zero-downtime, fly

GPUs on Fly.io are available to everyone!

2024-02-14T04:28:23+00:00

GPUs on Fly.io are available to everyone!

We’ve been experimenting with GPUs on Fly for a few months for Datasette Cloud. They’re well documented and quite easy to use—any example Python code you find that uses NVIDIA CUDA stuff generally Just Works. Most interestingly of all, Fly GPUs can scale to zero—so while they cost $2.50/hr for a A100 40G (VRAM) and $3.50/hr for a A100 80G you can configure them to stop running when the machine runs out of things to do.

We’ve successfully used them to run Whisper and to experiment with running various Llama 2 LLMs as well.

To look forward to: “We are working on getting some lower-cost A10 GPUs in the next few weeks”.

Tags: ai, datasette-cloud, fly, generative-ai, whisper, llms, nvidia, gpus

Macaroons Escalated Quickly

2024-01-31T16:57:23+00:00

Macaroons Escalated Quickly

Thomas Ptacek’s follow-up on Macaroon tokens, based on a two year project to implement them at Fly.io. The way they let end users calculate new signed tokens with additional limitations applied to them (“caveats” in Macaroon terminology) is fascinating, and allows for some very creative solutions.

Via Hacker News

Tags: apis, security, thomas-ptacek, fly

Introducing datasette-write-ui: a Datasette plugin for editing, inserting, and deleting rows

2023-08-16T01:48:07+00:00

Introducing datasette-write-ui: a Datasette plugin for editing, inserting, and deleting rows

Alex García is working with me on Datasette Cloud for the next few months, graciously sponsored by Fly. We will be working in public, releasing open source code and documenting how to build a multi-tenant SaaS product using Fly Machines.

Alex’s first project is datasette-write-ui, a plugin that finally lets you directly edit data stored inside Datasette. Alex wrote about the plugin on our new Datasette Cloud blog.

Tags: plugins, datasette, datasette-cloud, fly, alex-garcia

Vector Search

2023-06-02T05:02:22+00:00

Vector Search

Amjith Ramanujam provides a very thorough tutorial on implementing vector similarity search using SentenceTransformers embeddings (all-MiniLM-L6-v2) executed using sqlite-utils, then served via datasette-sqlite-vss and deployed using Fly.

Tags: sqlite, ai, datasette, fly, vector-search, embeddings

Carving the Scheduler Out of Our Orchestrator

2023-02-02T21:46:35+00:00

Carving the Scheduler Out of Our Orchestrator

Thomas Ptacek describes Fly’s new custom-built alternative to Nomad and Kubernetes in detail, including why they eventually needed to build something custom to best serve their platform. In doing so he provides the best explanation I’ve ever seen of what an orchestration system actually does.

Via @tqbf

Tags: thomas-ptacek, kubernetes, fly

Stringing together several free tiers to host an application with zero cost using fly.io, Litestream and Cloudflare

2022-10-07T17:47:34+00:00

Stringing together several free tiers to host an application with zero cost using fly.io, Litestream and Cloudflare

Alexander Dahl provides a detailed description (and code) for his current preferred free hosting solution for small sites: SQLite (and a Go application) running on Fly’s free tier, with the database replicated up to Cloudflare’s R2 object storage (again on a free tier) by Litestream.

Tags: hosting, sqlite, cloudflare, fly, litestream

Introducing LiteFS

2022-09-21T18:56:42+00:00

Introducing LiteFS

LiteFS is the new SQLite replication solution from Fly, now ready for beta testing. It’s from the same author as Litestream but has a very different architecture; LiteFS works by implementing a custom FUSE filesystem which spies on SQLite transactions being written to the journal file and forwards them on to other nodes in the cluster, providing full read-replication. The signature Litestream feature of streaming a backup to S3 should be coming within the next few months.

Via Hacker News

Tags: databases, replication, sqlite, fly, litestream, ben-johnson

Quoting Thomas Ptacek

2022-09-16T01:49:27+00:00

[SQLite is] a database that in full-stack culture has been relegated to "unit test database mock" for about 15 years that is (1) surprisingly capable as a SQL engine, (2) the simplest SQL database to get your head around and manage, and (3) can embed directly in literally every application stack, which is especially interesting in latency-sensitive and globally-distributed applications.

Reason (3) is clearly our ulterior motive here, so we're not disinterested: our model user deploys a full-stack app (Rails, Elixir, Express, whatever) in a bunch of regions around the world, hoping for sub-100ms responses for users in most places around the world. Even within a single data center, repeated queries to SQL servers can blow that budget. Running an in-process SQL server neatly addresses it.

— Thomas Ptacek

Tags: sql, sqlite, thomas-ptacek, fly

Exploring the training data behind Stable Diffusion

2022-09-05T00:18:42+00:00

Two weeks ago, the Stable Diffusion image generation model was released to the public. I wrote about this last week, in Stable Diffusion is a really big deal - a post which has since become one of the top ten results for "stable diffusion" on Google and shown up in all sorts of different places online.

Andy Baio pinged me a week ago on Friday and asked if I'd be interested in collaborating with him on digging into the training data. The Stable Diffusion Model Card provides a detailed description of how the model was trained - primarily on the LAION 2B-en) dataset (a subset of LAION 5B), with further emphasis given to images with higher calculated aesthetic scores.

We ended up deciding to dig into the improved_aesthetics_6plus subset, which consists of 12 million images with an aesthetics score of 6 or higher.

This isn't the full training set used for the model, but it's small enough that it fits comfortably in a SQLite database on inexpensive hosting...

So I built a search engine, powered by Datasette!

Update, 20th December 2023: This search tool is no longer available.

You can search for images by keyword using the following interface:

laion-aesthetic.datasette.io/laion-aesthetic-6pls/images

Or see a breakdown of image counts by the domain they were scraped from on this page.

The search engine provides access to 12,096,835 rows, and uses SQLite full-text search to power search across their text descriptions.

Andy used this Datasette instance to conduct a thorough analysis of the underlying training data, which he wrote about in Exploring 12 Million of the 2.3 Billion Images Used to Train Stable Diffusion’s Image Generator.

This analysis has had a really huge impact! Stories mentioning it made the front page of the websites of both the New York Times and the Washington Post on the same day:

He used AI art from Midjourney to win a fine-arts prize. Did he cheat? - The Washington Post
An A.I.-Generated Picture Won an Art Prize. Artists Aren’t Happy. - The New York Times

Further afield, we spotted coverage from publications that included:

Diese Software macht Sie zum KI-Künstler - Der Spiegel. I get quoted in this one (a translated snippet from my blog at least).
23億枚もの画像で構成された画像生成AI「Stable Diffusion」のデータセットのうち1200万枚がどこから入手した画像かを調査した結果が公開される - Gigazine, a long-running (22 years old) Japanese online news magazine.

How I built the database

The code for the Datasette instance can be found in this GitHub repository. The issues in that repo contain a detailed record of the various steps I took to build the database.

The data subset I loaded into the search engine is published on Hugging Face by Christoph Schuhmann. It consists of 7 parquet files, each of which are 325MB and stored in a GitHub repo using Git LFS.

The first step was to fetch that data.

This was my first time running git lfs - I had to install it first using:

brew install git-lfs
git lfs install

Then I cloned the repo and fetched the data like this. Note that to make the actual files available in the directory you need to run both git lfs fetch and git lfs checkout:

git clone https://huggingface.co/datasets/ChristophSchuhmann/improved_aesthetics_6plus
cd improved_aesthetics_6plus
git lfs fetch
git lfs checkout

The result is 7 parquet files. I wanted to load these into SQLite.

The first solution I found that worked was to use the parquet-tools Python package:

pipx install parquet-tools

I could then convert the parquet data to CSV like this:

parquet-tools csv train-00002-of-00007-709151a2715d894d.parquet

This outputs the contents of the file as CSV.

Since this is a lot of data it made sense to create an empty SQLite table first (with columns with the correct column types) before inserting the data. I did that like so:

sqlite3 laion-aesthetic-6pls.db '
CREATE TABLE IF NOT EXISTS images (
   [url] TEXT,
   [text] TEXT,
   [width] INTEGER,
   [height] INTEGER,
   [similarity] FLOAT,
   [punsafe] FLOAT,
   [pwatermark] FLOAT,
   [aesthetic] FLOAT,
   [hash] TEXT,
   [__index_level_0__] INTEGER
);'

Then I used a bash loop to insert all of the data:

for filename in *.parquet; do
    parquet-tools csv $filename | sqlite3 -csv laion-aesthetic-6pls.db ".import --skip 1 '|cat -' images"
done

This uses the sqlite3 tool's .import mechanism, because it's really fast. The --skip 1 option is necessary to skip the first line, which is the CSV column names. The '|cat -' is the idiom used to tell SQLite to read from standard input.

This did the job! The result was a SQLite database file, about 3.5GB in size.

Enabling search

To enable SQLite full-text search against the images, I used sqlite-utils enable-fts:

sqlite-utils enable-fts laion-aesthetic-6pls.db images text

This took about a minute and a half to run. The resulting database file was around 3.9GB in size - the full text index didn't add as much to the file size as I had expected.

Best of all, the search was fast! Most search queries took in the order of 20ms to run. My opinion of SQLite FTS keeps improving the more I use it.

Extracting domains with sqlite-utils --functions

We knew we wanted to count how many images had been scraped from each domain - but we currently only had the full image URLs:

https://cdn.idahopotato.com/cache/4075b86c99bc2c46f927f3be5949d161_w310.jpg

While walking Cleo I had an idea: what if sqlite-utils made it really easy to register custom SQL functions and use them from the command-line? Then I could use a Python function to extract the domain names.

This became the impetus for releasing sqlite-utils 3.29 with a brand new feature: sqlite-utils --functions, which lets you do exactly that.

Here's how I used that to extract the domain names from the URLs:

# First, add an empty 'domain' column to the table
sqlite-utils add-column data.db images domain

# Now populate it using a custom SQL function:
sqlite-utils laion-aesthetic-6pls.db 'update images set domain = domain(url)' \
--functions '
from urllib.parse import urlparse

def domain(url):
    return urlparse(url).netloc
'

Here we are executing this SQL query against the database:

update images set domain = domain(url)

Where that domain(url) function is defined in the Python snippet passed to the --functions option:

from urllib.parse import urlparse

def domain(url):
    return urlparse(url).netloc

sqlite-utils runs eval() against the code in that block, then loops through any callable objects defined by that code (skipping them if their name starts with an underscore) and registers those as custom SQL functions with SQLite.

I'm really excited about this pattern. I think it makes sqlite-utils an even more useful tool for running ad-hoc data cleanup and enrichment tasks.

Populating the domains table

The domain column in the images table was now populated, but it was a bit of a verbose column: it duplicated a chunk of text from the existing url, and was repeated for over 12 million rows.

The sqlite-utils extract command is designed for this exact use-case. It can extract a column from an existing table out into a separate lookup table, reducing the database size by swapping those duplicate text fields for a much smaller integer foreign key column instead.

I ran that like so:

sqlite-utils extract laion-aesthetic-6pls.db images domain

The result was a new domains table, and a domain_id column in the images table that pointed to records there.

One more step: I didn't want people visiting the site to have to run an expensive group by/count query to see which domains had the most images. So I denormalized that data into the domains table.

First I added a new integer column to it, called image_counts:

sqlite-utils add-column laion-aesthetic-6pls.db domain image_counts integer

Then I populated it with a query like this:

sqlite-utils laion-aesthetic-6pls.db '
with counts as (
  select domain_id, count(*) as c from images group by domain_id
)
update domain
  set image_counts = counts.c
  from counts
  where id = counts.domain_id
'

I first learned to combine CTEs and SQL updates while working with Django migrations - I was delighted to see the same trick works for SQLite as well.

You can see the result of this query in the domain table. The first five rows look like this:

id	domain	image_counts
24	i.pinimg.com	1043949
7	render.fineartamerica.com	601106
16	us.123rf.com	497244
5	cdn.shopify.com	241632
136	photos.smugmug.com	225582

Doing the same for celebrities, artists, characters

We also wanted to provide pre-calculated counts for searches against a number of celebrities, artists and fictional characters - to help give a sense of the kinds of images that were included in the data.

Andy gathered the ones we wanted to track in this Google Sheet.

I recently learned how to use the /export?format=csv endpoint to export a Google Sheet as CSV. I found out that you can use /export?format=csv&gid=1037423923 to target a specific tab in a multi-tabbed sheet.

So I imported Andy's data into SQLite using the following:

curl -L 'https://docs.google.com/spreadsheets/d/1JLQQ3U6P0d4vDkAGuB8avmXOPIDPeDUdAEZsWTRYpng/export?format=csv' \
  | sqlite-utils insert laion-aesthetic-6pls.db artists - --csv
curl -L 'https://docs.google.com/spreadsheets/d/1JLQQ3U6P0d4vDkAGuB8avmXOPIDPeDUdAEZsWTRYpng/export?format=csv&gid=1037423923' \
  | sqlite-utils insert laion-aesthetic-6pls.db celebrities - --csv
curl -L 'https://docs.google.com/spreadsheets/d/1JLQQ3U6P0d4vDkAGuB8avmXOPIDPeDUdAEZsWTRYpng/export?format=csv&gid=480391249' \
  | sqlite-utils insert laion-aesthetic-6pls.db characters - --csv

This gave me artists, celebrities and characters tables.

The next challenge was to run a search query for each row in each of those tables and return the count of results. After some experimentation I found that this one worked:

select name, (
  select count(*) from images_fts where images_fts match '"' || name || '"'
) as search_count from celebrities order by search_count desc

Note the match '"' || name || '"' part - this was necessary to ensure the name was correctly quoted in a way that would avoid names like Dwayne 'The Rock' Johnson from breaking the search query.

Now that I had the query I could use that same CTE update trick to populate a counts column in the tables:

sqlite-utils add-column laion-aesthetic-6pls.db celebrities image_counts integer

sqlite-utils laion-aesthetic-6pls.db "$(cat <<EOF
with counts as (
  select name,
    (
      select count(*) from images_fts where images_fts match '"' || name || '"'
    ) as search_count
    from celebrities
)
update celebrities
  set image_counts = counts.search_count
  from counts
  where celebrities.name = counts.name
EOF
)"

I'm using the cat <<EOF trick here to avoid having to use shell escaping for the single and double quotes, as described in this TIL: Passing command arguments using heredoc syntax.

Here are the finished tables: characters, celebrities, artists.

Deploying it to Fly

At just under 4GB the resulting SQLite database was an awkward size. I often deploy ~1GB databases to Google Cloud Run, but this was a bit too large for me to feel comfortable with that. Cloud Run can also get expensive for projects that attract a great deal of traffic.

I decided to use Fly instead. Fly includes support for mountable volumes, which means it's a great fit for these larger database files.

I wrote about Using SQLite and Datasette with Fly Volumes back in February, when I added support to volumes to the datasette-publish-fly Datasette plugin.

This was still the largest database I had ever deployed to Fly, and it took a little bit of work to figure out the best way to handle it.

In the end, I used the following recipe:

datasette publish fly \
  --app laion-aesthetic \
  --volume-name datasette \
  --install datasette-json-html \
  --extra-options "-i /data/laion-aesthetic-6pls.db --inspect-file /data/inspect.json --setting sql_time_limit_ms 10000 --setting suggest_facets 0 --setting allow_download 0" \
  -m metadata.yml

The first time I ran this I used --create-volume 20 to create a 20GB volume called datasette. I over-provisioned this so I could run commands like sqlite-utils vacuum, which need twice the amount of space as is taken up by the database file itself.

I uploaded the database file itself using scp, and ran fly ssh console -a laion-aesthetic to SSH in and execute other commands such as datasette inspect laion-aesthetic-6pls.db > inspect.json to create the inspect JSON file.

The --extra-options deserve explanation.

Normally when you run datasette publish the file you pass to the command is automatically deployed using immutable mode. This mode is specifically designed for running read-only databases, and uses optimizations like only counting the rows in the table once on startup (or loading the counts from a pre-prepared inspect.json file).

I wanted those optimizations for this project. But datasette publish fly is currently designed with the assumption that any databases you put in the /data volume are designed to accept writes, and hence shouldn't be opened in immutable mode.

I ended up coming up with a horrible hack. I add -i /data/laion-aesthetic-6pls.db to the --extra-options command to tell Datasette to open the file in immutable mode.

But this wasn't enough! datasette publish fly also configures Datasette to automatically open any databases in /data in read-only mode, so that newly saved database files will be served correctly.

This meant my instance was loading the same database twice - once in read-only mode and once in immutable mode.

Rather than fixing the design of datasette-publish-fly, I went for a cheap workaround. I start Datasette with the following metadata.yml configuration (simplified):

databases:
  laion-aesthetic-6pls:
    tables:
      domain:
        label_column: domain
  laion-aesthetic-6pls_2:
    allow: false

This ensures that the laion-aesthetic-6pls database - the immutable one - is served correctly, and has a label column set for the domain table too.

laion-aesthetic-6pls_2 is the second copy of that database, loaded because Datasette spotted it in the /data directory. Setting allow: false on it uses Datasette's permissions framework to hide that duplicate database from view.

I'm not proud of these workarounds, and I hope to fix them in the future - but for the moment this is what it took to deploy the project.

Scaling it to meet demand

I launched the first version of the application on Fly's cheapest instance - 256MB of RAM, costing $1.87/month.

This worked fine when it was just me and Andy playing with the site, but it started to struggle as traffic started to increase.

Fly have a "scale app" button which lets you upgrade your instance. I hadn't actually used it before, but I was delighted to find that it worked exactly as expected: I bumped the RAM up to 4GB (not coincidentally the size of the SQLite database file) and the instance restarted within a few seconds with upgraded capacity.

Fly provide a preconfigured Grafana interface for watching your instances, and it helped me feel confident that the resized instance was happily dealing with the traffic.

I plan to dial back down to a cheaper instance once interest in the project starts to fade.

Got a problem? Throw a search engine at it

This is the third time I've used Datasette to build a search engine in the past three weeks! My other two recent projects are:

The ability to spin up a full search engine for anything that you can stuff into a SQLite database table (which it turns out is almost everything) is a really powerful ability. I plan to write a Datasette tutorial about this in the future.

Releases this week

datasette-render-image-tags: 0.1 - 2022-09-04
Turn any URLs ending in .jpg/.png/.gif into img tags with width 200
datasette-sitemap: 1.0 - (3 releases total) - 2022-08-30
Generate sitemap.xml for Datasette sites
datasette-block-robots: 1.1 - (6 releases total) - 2022-08-30
Datasette plugin that blocks robots and crawlers using robots.txt
sqlite-utils: 3.29 - (103 releases total) - 2022-08-28
Python CLI utility and library for manipulating SQLite databases

TIL this week

Tags: andy-baio, ethics, search, ai, datasette, parquet, weeknotes, fly, sqlite-utils, stable-diffusion, generative-ai, laion, training-data, text-to-image, ai-ethics

Digitizing 55,000 pages of civic meetings

2022-08-22T16:26:04+00:00

Digitizing 55,000 pages of civic meetings

Philip James has been building public, searchable archives of city council meetings for various cities—Oakland and Alamedia so far—using my s3-ocr script to run Textract OCR against the PDFs of the minutes, and deploying them to Fly using Datasette. This is a really cool project, and very much the kind of thing I’ve been hoping to support with the tools I’ve been building.

Via @phildini

Tags: archiving, ocr, political-hacking, datasette, fly

Litestream backups for Datasette Cloud (and weeknotes)

2022-08-11T17:19:52+00:00

My main focus this week has been adding robust backups to the forthcoming Datasette Cloud.

Datasette Cloud is a SaaS service for Datasette. It allows people to create a private Datasette instance where they can upload data, visualize and transform it and share it with other members of their team. You can join the waiting list to try it out using this form.

I'm building Datastte Cloud on Fly, specifically on Fly Machines.

Security is a big concern for Datasette Cloud. Teams should only be able to access their own data - bugs where users accidentally (or maliciously) access data for another team should be protected against as much as possible.

To help guarantee that, I've designed Datasette Cloud so that each team gets their own, dedicated instance, running in a Firecracker VM managed by Fly. Their data lives in a dedicated volume.

Fly volumes already implement snapshot backups, but I'm interested in defence in depth. This is where Litestream comes in (coincidentally now part of Fly, although it wasn't when I first selected it as my backup strategy).

I'm using Litestream to constantly backup the data for each Datasette Cloud team to an S3 bucket. In the case of a complete failure of a volume, I can restore data from a backup that should be at most a few seconds out of date. Litestream also gives me point-in-time backups, such that I can recover a previous version of the data within a configurable retention window.

Keeping backups isolated

Litestream works by writing a constant stream of pages from SQLite's WAL (Write-Ahead Log) up to an S3 bucket. It needs the ability to both read and write from S3.

This requires making S3 credentials available within the containers that run Datasette and Litestream for each team account.

Credentials in those containers are not visible to the users of the software, but I still wanted to be confident that if the credentials leaked in some way the isolation between teams would be maintained.

Initially I thought about having a separate S3 bucket for each team, but it turns out AWS has a default limit of 100 buckets per account, and a hard limit of 1,000. I aspire to have more than 1,000 customers, so this limit makes a bucket-per-team seem like the wrong solution.

I've learned an absolute ton about S3 and AWS permissions building my s3-credentials tool for creating credentials for accessing S3.

One of the tricks I've learned is that it's possible to create temporary, time-limited credentials that only work for a prefix (effectively a folder) within an S3 bucket.

This means I can run Litestream with credentials that are specific to the team - that can read and write only from the team-ID/ prefix in the S3 bucket I am using to store the backups.

Obtaining temporary credentials

My s3-credentials tool can create credentials for a prefix within an S3 bucket like this:

s3-credentials create my-bucket-for-backus \
  --duration 12h \
  --prefix team-56/

This command uses the sts.assume_role() AWS method to create credentials that allow access to that bucket, attaching this generated JSON policy to it in order to restrict access to the provided prefix.

I extracted the relevant Python code from s3-credentials and used it to create a private API endpoint in my Datasette Cloud management server which could return the temporary credentials needed by the team container.

With the endpoint in place, my code for launching a team container can do this:

Create the volume and machine for that team (if they do not yet exist)
Generate a signed secret token that the machine container can exchange for its S3 credentials
Launch the machine container, passing it the secret token
On launch, the container runs a script which exchanges that secret token for its 12 hour S3 credentials, using the private API endpoint I created
Those credentials are used to populate the AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and AWS_SESSION_TOKEN environment variables used by Litestream
Start Litestream, which then starts Datasette

Restarting every 12 hours

You may be wondering why I bothered with that initial secret token - why not just pass the temporary AWS credentials to the container when I launch it?

The reason for this is that I need to be able to obtain fresh credentials every 12 hours.

A really neat feature of Fly Machines is that they support scale-to-zero. You can stop them, and Fly will automatically restart them the next time they recieve traffic.

All you need to do is call sys.exit(0) in your Python code (or the equivalent in any other language) and Fly will stop your container... and then restart it again with a couple of seconds of cold start time the next time an HTTP request for your container hits the Fly router.

So far I'm mainly using this to avoid the cost of running containers when they aren't actually in- use. But there's a neat benefit when it comes to Litestream too.

I'm using S3 credentials which expire after 12 hours. This means I need to periodically refresh the credentials and restart Litestream or it will stop being able to write to the S3 bucket.

After considering a few ways of doing this, I selected the simplest to implement: have Datasette call sys.exit(0) after ten hours, and let Fly restart the container causing my startup script to fetch freshly generated 12 hour credentials and pass them to Litestream.

I implemented this by adding it as a new setting to my existing datasette-scale-to-zero plugin. You can now configure that with "max-age": "10h" and it will shut down Datasette once the server has been running for that long.

Why does this require my own secret token system? Because when the container is restarted, it needs to make an authenticated call to my endpoint to retrieve those fresh S3 credentials. Fly persists environment variable secrets between restarts to the container, so that secret can be long-lived even while it is exchanged for short-term S3 credentials.

I only just put the new backup system in place, so I'm exercising it a bit before I open things up to trial users - but so far it's looking like a very robust solution to the problem.

s3-ocr improvements

I released a few new versions of s3-ocr this week, as part of my ongoing project working with the San Francisco Microscopical Society team to release a searchable version of their scanned document archives.

The two main improvements are:

A new --dry-run option to s3-ocr start which shows you what the tool will do without making any changes to your S3 bucket, or triggering any OCR jobs. #22
s3-ocr start used to fail with an error if running it would create more than 100 (or 600 depending on your region) concurrent OCR jobs. The tool now knows how to identify that error and pause and retry starting the jobs instead. #21

The fix that took the most time is this: installations of the tool no longer arbitrarily fail to work depending on the environment you install them into!

Solving this took me the best part of a day. The short version is this: Click 8.1.0 introduced a new feature that lets you use @cli.command as a decorator instead of @cli.command(). This meant that installing s3-ocr in an environment that already had a previous version of Click would result in silent errors.

The solution is simple: pin to click>=8.1.0 in the project dependencies if you plan to use this new syntax.

If I'd read the Click changelog more closely I would have saved myself a whole lot of time.

Issues #25 and #26 detail the many false turns I took trying to figure this out.

More fun with GPT-3 and DALL-E

This tweet scored over a million impressions on Twitter:

New hobby: prototyping video games in 60 seconds using a combination of GPT-3 and DALL-E

Here's "Raccoon Heist" pic.twitter.com/xQ3Vm8p2XW
- Simon Willison (@simonw) August 5, 2022

As this got retweeted outside of my usual circles it started confusing people who thought the "prototype" was a working game, as opposed to a fake screenshot and a paragraph of descriptive text! I wasn't kidding when I said I spent 60 seconds on this.

I also figured out how to use GPT-3 to write jq one-liners. I love jq but I have to look up how to use it every time, so having GPT-3 do the work for me is a pretty neat time saver. More on that in this TIL: Using GPT-3 to figure out jq recipes

Releases this week

s3-ocr: 0.6.3 - (9 releases total) - 2022-08-10
Tools for running OCR against files stored in S3
datasette-scale-to-zero: 0.2 - (4 releases total) - 2022-08-05
Quit Datasette if it has not received traffic for a specified time period
shot-scraper: 0.14.3 - (18 releases total) - 2022-08-02
A command-line utility for taking automated screenshots of websites
s3-credentials: 0.12.1 - (13 releases total) - 2022-08-01
A tool for creating credentials for accessing S3 buckets
datasette-sqlite-fts4: 0.3.2 - (2 releases total) - 2022-07-31

TIL this week

Tags: ocr, s3, datasette, weeknotes, datasette-cloud, fly, litestream, gpt-3, dalle, gpt

How SQLite Helps You Do ACID

2022-08-10T15:39:09+00:00

How SQLite Helps You Do ACID

Ben Johnson’s series of posts explaining the internals of SQLite continues with a deep look at how the rollback journal works. I’m learning SO much from this series.

Via @benbjohnson

Tags: sqlite, fly, ben-johnson

Quoting Thomas Ptacek

2022-07-07T20:31:32+00:00

SOC2 is about the security of the company, not the company’s products. A SOC2 audit would tell you something about whether the customer support team could pop a shell on production machines; it wouldn’t tell you anything about whether an attacker could pop a shell with a SQL Injection vulnerability.

— Thomas Ptacek

Tags: security, sql-injection, thomas-ptacek, fly

Weeknotes: Datasette Cloud ready to preview

2022-06-07T01:00:00+00:00

I made an absolute ton of progress building Datasette Cloud on Fly this week, and also had a bunch of fun playing with GPT-3.

Datasette Cloud

Datasette Cloud is my upcoming hosted SaaS version of Datasette. I've been re-building my initial alpha on top of Fly because I want to be able to provide each team account with their own Datasette instance running in a dedicated Firecracker container, and the recently announced Fly Machines lets me do exactly that.

As-of this weekend I have all of the different pieces in place, and I'm starting to preview it to potential customers.

Interested in trying it out? You can request access to the preview here.

GPT-3 explorations

Most of my GPT-3 explorations over the past week are covered by these two blog posts:

A Datasette tutorial written by GPT-3 is the point at which I really started taking GPT-3 seriously, after convincing myself that I could use it to help with real work, not just as a form of entertainment.
How to play with the GPT-3 language model is a very quick getting started tutorial, because I polled people on Twitter and found that more than half didn't know you could try GPT-3 out now for free.

Searching my tweets for GPT captures a bunch of other, smaller experiments. A few highlights:

Releases this week

datasette-upload-csvs: 0.7 - (8 releases total) - 2022-06-03
Datasette plugin for uploading CSV files and converting them to database tables
mbox-to-sqlite: 0.1a0 - 2022-05-31
Load email from .mbox files into SQLite
datasette-graphql: 2.0.2 - (34 releases total) - 2022-05-30
Datasette plugin providing an automatic GraphQL API for your SQLite databases
datasette-auth-existing-cookies: 1.0a1 - (12 releases total) - 2022-05-29
Datasette plugin that authenticates users based on existing domain cookies

TIL this week

Using just with Django

Tags: projects, datasette, weeknotes, datasette-cloud, fly, gpt-3, llms, gpt

Weeknotes: Building Datasette Cloud on Fly Machines, Furo for documentation

2022-05-26T04:35:11+00:00

Hosting provider Fly released Fly Machines this week. I got an early preview and I've been working with it for a few days - it's a fascinating new piece of technology. I'm using it to get my hosting service for Datasette ready for wider release.

Datasette Cloud

Datasette Cloud is the name I've given my forthcoming hosted SaaS version of Datasette. I'm building it for two reasons:

This is an obvious step towards building a sustainable business model for my open source project. It's a reasonably well-trodden path at this point: plenty of projects have demonstrated that offering paid hosting for an open source project can build a valuable business. GitLab are an especially good example of this model.
There are plenty of people who could benefit from Datasette, but the friction involved in hosting it prevents them from taking advantage of the software. I've tried to make it as easy to host as possible, but without a SaaS hosted version I'm failing to deliver value to the people that I most want the software to help.

My previous alpha was built directly on Docker, running everything on a single large VPS. Obviously it needed to scale beyond one machine, and I started experimenting with Kubernetes to make this happen.

I also want to allow users to run their own plugins, without risk of malicious code causing problems for other accounts. Docker and Kubernetes containers don't offer the isolation that I need to feel comfortable doing this, so I started researching Firecracker - constructed by AWS to power Lambda and Fargate, so very much designed with potentially malicious code in mind.

Spinning up Firecracker on a Kubernetes cluster is no small lift!

And then I heard about Fly Machines. And it looks like it's exactly what I need to get this project to the next milestone.

Fly Machines

Fly's core offering allows you to run Docker containers in regions around the world, compiled (automatically by Fly) to Firecracker containers with geo-load-balancing so users automatically get routed to an instance running near them.

Their new Fly Machines product gives you a new way to run containers there: you get full control over when containers are created, updated, started, stopped and destroyed. It's the exact level of control I need to build Datasette Cloud.

It also implements scale-to-zero: you can stop a container, and Fly will automatically start it back up again for you (generally in less than a second) when fresh traffic comes in.

(I had built my own version of this for my Datasette Cloud alpha, but the spin up time took more like 10s and involved showing the user a custom progress bar to help them see what was going on.)

Being able to programatically start and stop Firecracker containers was exactly what I'd been trying to piece together using Kubernetes - and the ability to control which global region they go in (with the potential for Litestream replication between regions in the future) is a feature I hadn't expected to be able to offer for years.

So I spent most of this week on a proof of concept. I've successfully demonstrated that the Fly Machines product has almost exactly the features that I need to ship Datasette Cloud on Fly Machines - and I've confirmed that the gaps I need to fill are on Fly's near-term roadmap.

I don't have anything to demonstrate publicly just yet, but I do have several new TILs.

If this sounds interesting to you or your organization and you'd like to try it out, drop me an email at swillison @ Google's email service.

The Furo theme for Sphinx

My shot-scraper automated screenshot tool's README had got a little too long, so I decided to upgrade it to a full documentation website.

I chose to use MyST and Sphinx for this, hosted on Read The Docs.

MyST adds Markdown syntax to Sphinx, which is easier to remember (and for people to contribute to) than reStructuredText.

After putting the site live, Adam Johnson suggested I take a look at the Furo theme. I'd previously found Sphinx themes hard to navigate because they had so much differing functionality, but a personal recommendation turned out to be exactly what I needed.

Furo is really nice - it fixed a slight rendering complaint I had about nested lists in the theme I was using, and since it doesn't use web fonts it dropped the bytes transferred for a page of documentation by more than half!

I switched shot-scraper over to Furo, and liked it so much that I switched over Datasette and sqlite-utils too.

Here's what the shot-scraper documentation looks like now:

Screenshot taken using shot-scraper itself, like this:

shot-scraper \
  https://shot-scraper.datasette.io/en/latest/ \
  --retina --height 1200

Full details of those theme migrations (including more comparative screenshots) can be found in these issues:

Releases this week

datasette-unsafe-expose-env: 0.1 - 2022-05-25
Datasette plugin to expose some environment variables at /-/env for debugging
shot-scraper: 0.14.1 - (16 releases total) - 2022-05-22
A comand-line utility for taking automated screenshots of websites
google-calendar-to-sqlite: 0.1a0 - 2022-05-21
Create a SQLite database containing your data from Google Calendar
datasette-upload-dbs: 0.1.1 - (2 releases total) - 2022-05-17
Upload SQLite database files to Datasette
datasette-insert: 0.7 - (7 releases total) - 2022-05-16
Datasette plugin for inserting and updating data

TIL this week

Tags: documentation, projects, datasette, weeknotes, datasette-cloud, fly, firecracker

Using SQLite and Datasette with Fly Volumes

2022-02-15T02:17:28+00:00

A few weeks ago, Fly announced Free Postgres Databases as part of the free tier of their hosting product. Their announcement included this snippet:

The lede is "free Postgres" because that's what matters to full stack apps. You don't have to use these for Postgres. If SQLite is more your jam, mount up to 3GB of volumes and use "free SQLite." Yeah, we're probably underselling that.

I don't know if I've ever been nerd sniped so effectively (they knew what they were doing).

This has the potential to address one of the big challenges in deploying Datasette, my open source web framework for building dynamic applications on top of SQLite.

Datasette currently has plenty of good answers for publishing read-only databases online - Cloud Run, Heroku and Vercel are three great options. But the moment you want to write back to that SQLite database you're in for a harder time.

Accepting writes requires a working read-write filesystem, and that generally means spinning up a virtual machine - and having to take responsibility for keeping a full Linux instance fed and updated. That's a much bigger commitment than deploying a container to something like Cloud Run.

Fly Volumes are exactly what I've been waiting for here. They provide persistent storage for Fly apps - a volume you can mount as a directory inside a containerized application that will persist data through subsequent deploys.

Add a free tier, and I finally have something I can point people towards when they want to try building something persistent on top of Datasette without taking on the burden of maintaining their own virtual server somewhere, or committing to spend money before they've evaluated if this is going to work for them or not.

datasette-publish-fly

I built the first version of the datasette-publish-fly plugin nearly two years ago, as a tool for publishing read-only databases to Fly in a similar way to Cloud Run or Vercel.

(That plugin actually broke a few months ago without me noticing due to some changes made to the flyctl utility that it shells out to - figuring out what had happened and wanting to avoid surprises like this in the future lead me to develop the Help scraping technique I described last week.)

This week I released datasette-publish-fly 1.1 with the option to attach volumes to the published instances and start serving mutable, persistent SQLite databases!

Basic usage looks like this:

datasette publish fly \
--app my-new-fly-application \
--create-volume 1 \
--create-db my-new-db

This will create a new Fly application on your account called my-new-fly-application, create a 1GB volume for that application, mount the volume and then create an empty SQLite database file in that volume called my-new-db.db.

A blank database file isn't very interesting! Datasette's Writable canned queries feature can be used to configure SQL queries that write to the database. Let's try that now, by creating a metadata.yml file containing the following:

plugins:
  # Create messages table in messages.db on startup
  datasette-init:
    messages:
      tables:
        messages:
          columns:
            id: integer
            message: text
            datetime: text
          pk: id
databases:
  messages:
    queries:
      # /messages/add_message query
      add_message:
        sql: |-
          INSERT INTO messages (
            message, datetime
          ) VALUES (
            :message, :_now_datetime_utc
          )
        write: true

Now we can run the deploy like this:

datasette publish fly \
--app messages-demo \
--create-volume 1 \
--create-db messages \
--install datasette-init \
--metadata metadata.yml

This example uses the datasette-init plugin to create the messages table if it doesn't exist, and configures a canned query that can insert rows into that table (using the _now_datetime_utc magic parameter to populate the current datetime.)

Once deployed, the following URL provides a form that can be used to add messages:

https://messages-demo.fly.dev/messages/add_message

As a general rule though, writable databases become more interesting when you combine them with Datasette plugins.

Using Fly volumes with datasette-tiddlywiki

datasette-tiddlywiki is a plugin that bundles a full install of TiddlyWiki, configured to use Datasette as a storage backend. I introduced this new plugin back in December.

Version 0.2 of the plugin, which I released yesterday, adds authentication. TiddlyWiki instances can now be viewed by anyone but can only be edited by users with the edit-tiddlywiki permisson, which is available to the root user by default and can be granted to more users using the Datasette permissions system.

Here's how to deploy datasette-tiddlywiki to Fly, with an authenticated account that can then be used to edit the wiki.

We're going to use the datasette-auth-passwords plugin to authenticate users. We need to install that locally in order to generate a password:

datasette install datasette-auth-passwords

Installing this plugin adds a new command to Datasette called hash-password - we can use that like so:

% datasette hash-password
Password: 
Repeat for confirmation: 
pbkdf2_sha256$260000$b1ec52979ecf0c4810e3e22ea63c119e$AjaNnyuXzDXDRK/ZQjyn881J5GnVouKxI8B3DFu/C+M=

(The example hash shown here is for the password "password", so don't use it for anything!)

Having created that password hash, we can use it to deploy our TiddlyWiki instance.

# First, install datasette-publish-fly
datasette install datasette-publish-fly

# Now deploy an instance with datasette-tiddlywiki
datasette publish fly \
--app simon-tiddlywiki \
--create-volume 1 \
--create-db tiddlywiki \
--install datasette-auth-passwords \
--install datasette-tiddlywiki \
--plugin-secret datasette-auth-passwords root_password_hash 'pbkdf2_sha256$2600...'

The --app value here is the name of the app on Fly, which needs to be globally unique - so pick your own value here.

The full password hash needs to be pasted in with single quotes on that last line.

Running this will churn away for a little bit deploying the application:

It can take a few minutes after the script finishes running for the application to become available - Fly are working at speeding this up. If you visit it too early you may see a TLS connection error.

Once the application is live, the following pages will be available:

https://simon-tiddlywiki.fly.dev/-/tiddlywiki - TiddlyWiki, initially in read-only mode
https://simon-tiddlywiki.fly.dev/-/login - the login form provided by datasette-auth-passwords - sign in here with username root and the password you used to create the hash
https://simon-tiddlywiki.fly.dev/ - the Datasette homepage

Since the plugin saves data to an underlying SQLite table, you can visit /tiddlywiki/tiddlers to see the data that has been stored.

Other plugins to try

Here are some other interesting plugins that make use of Datasette's internal methods for writing to a database:

datasette-write provides a UI for executing write queries against the database
datasette-upload-csvs adds an interface for uploading a CSV file to create a table
datasette-insert adds a full JSON API for inserting and updating data
datasette-configure-fts can be used to configure full-text search against an existing table
datasette-edit-schema provides tools for adding, renaming and re-ordering columns
datasette-saved-queries lets users save queries to a database table

datasette-auth-github and datasette-auth-tokens can be used to provide additional ways of authenticating with an instance.

Releases this week

datasette-publish-fly: 1.1.1 - (7 releases total) - 2022-02-14
Datasette plugin for publishing data using Fly
datasette-tiddlywiki: 0.2 - (2 releases total) - 2022-02-14
Run TiddlyWiki in Datasette and save Tiddlers to a SQLite database
strip-hidden-form-values: 0.2.1 - (3 releases total) - 2022-02-11
CLI tool for stripping hidden form values from an HTML document
datasette-auth-passwords: 1.0 - (11 releases total) - 2022-02-09
Datasette plugin for authentication using passwords
datasette: 0.60.2 - (107 releases total) - 2022-02-07
An open source multi-tool for exploring and publishing data
sqlite-utils: 3.23 - (95 releases total) - 2022-02-04
Python CLI utility and library for manipulating SQLite databases
datasette-leaflet-freedraw: 0.3.1 - (9 releases total) - 2022-02-03
Draw polygons on maps in Datasette

TIL this week

Tags: sqlite, datasette, weeknotes, fly

Help scraping: track changes to CLI tools by recording their --help using Git

2022-02-02T23:46:35+00:00

I've been experimenting with a new variant of Git scraping this week which I'm calling Help scraping. The key idea is to track changes made to CLI tools over time by recording the output of their --help commands in a Git repository.

My new help-scraper GitHub repository is my first implementation of this pattern.

It uses this GitHub Actions workflow to record the --help output for the Amazon Web Services aws CLI tool, and also for the flyctl tool maintained by the Fly.io hosting platform.

The workflow runs once a day. It loops through every available AWS command (using this script) and records the output of that command's CLI help option to a .txt file in the repository - then commits the result at the end.

The result is a version history of changes made to those help files. It's essentially a much more detailed version of a changelog - capturing all sorts of details that might not be reflected in the official release notes for the tool.

Here's an example. This morning, AWS released version 1.22.47 of their CLI helper tool. They release new versions on an almost daily basis.

Here are the official release notes - 12 bullet points, spanning 12 different AWS services.

My help scraper caught the details of the release in this commit - 89 changed files with 3,543 additions and 1,324 deletions. It tells the story of what's changed in a whole lot more detail.

The AWS CLI tool is enormous. Running find aws -name '*.txt' | wc -l in that repository counts help pages for 11,401 individual commands - or 11,390 if you checkout the previous version, showing that there were 11 commands added just in this morning's new release.

There are plenty of other ways of tracking changes made to AWS. I've previously kept an eye on the botocore GitHub history, which exposes changes to the underlying JSON - and there are projects like awschanges.info which try to turn those sources of data into something more readable.

But I think there's something pretty neat about being able to track changes in detail for any CLI tool that offers help output, independent of the official release notes for that tool. Not everyone writes release notes with the detail I like from them!

I implemented this for flyctl first, because I wanted to see what changes were being made that might impact my datasette-publish-fly plugin which shells out to that tool. Then I realized it could be applied to AWS as well.

Help scraping my own projects

I got the initial idea for this technique from a change I made to my Datasette and sqlite-utils projects a few weeks ago.

Both tools offer CLI commands with --help output - but I kept on forgetting to update the help, partly because there was no easy way to see its output online without running the tools themselves.

So, I added documentation pages that list the output of --help for each of the CLI commands, generated using the Cog file generation tool:

sqlite-utils CLI reference (39 commands!)
datasette CLI reference

Having added these pages, I realized that the Git commit history of those generated documentation pages could double up as a history of changes I made to the --help output - here's that history for sqlite-utils.

It was a short jump from that to the idea of combining it with Git scraping to generate history for other tools.

Bonus trick: GraphQL schema scraping

I've started making selective use of the Fly.io GraphQL API as part of my plugin for publishing Datasette instances to that platform.

Their GraphQL API is openly available, but it's not extensively documented - presumably because they reserve the right to make breaking changes to it at any time. I collected some notes on it in this TIL: Using the undocumented Fly GraphQL API.

This gave me an idea: could I track changes made to their GraphQL schema using the same scraping trick?

It turns out I can! There's an NPM package called get-graphql-schema which can extract the GraphQL schema from any GraphQL server and write it out to disk:

npx get-graphql-schema https://api.fly.io/graphql > /tmp/fly.graphql

I've added that to my help-scraper repository too - so now I have a commit history of changes of changes they are making there too. Here's an example from this morning.

Other weeknotes

I've decided to start setting goals on a monthly basis. My goal for February is to finally ship Datasette 1.0! I'm trying to make at least one commit every day that takes me closer to that milestone.

This week I did a bunch of work adding a Link: https://...; rel="alternate"; type="application/datasette+json" HTTP header to a bunch of different pages in the Datasette interface, to support discovery of the JSON version of a page based on a URL to the human-readable version.

(I had originally planned to also support Accept: application/json request headers for this, but I've been put off that idea by the discovery that Cloudflare deliberately ignores the Vary: Accept header.)

Unrelated to Datasette: I also started a new Twitter thread, gathering behind the scenes material from the movie the Mitchells vs the Machines. There's been a flurry of great material shared recently by the creative team, presumably as part of the run-up to awards season - and I've been enjoying trying to tie it all together in a thread.

The last time I did this was for Into the Spider-Verse (from the same studio) and that thread ended up running for more than a year!

TIL this week

Tags: cli, git, github, projects, scraping, graphql, weeknotes, github-actions, git-scraping, fly

Weeknotes: python_requires, documentation SEO

2022-01-25T23:54:52+00:00

Fixed Datasette on Python 3.6 for the last time. Worked on documentation infrastructure improvements. Spent some time with Fly Volumes.

Datasette 0.60.1 for Python 3.6

I got a report that users of Python 3.6 were seeing errors when they tried to install Datasette.

I actually dropped support for 3.6 a few weeks ago, but that shouldn't have affected the already released Datasette 0.60 - so something was clearly wrong.

This lead me to finally get my head around how pip install handles Python version support. It's actually a very neat system which I hadn't previously taken the time to understand.

Python packages can (and should!) provide a python_requires= line in their setup.py. That line for Datasette currently looks like this:

python_requires=">=3.7"

But in the 0.60 release it was still this:

python_requires=">=3.6"

When you run pip install package this becomes part of the pip resolution mechanism - it will default to attempting to install the highest available version of the package that supports your version of Python.

So why did pip install datasette break? It turned out that one of Datasette's dependencies, Uvicorn, had dropped support for Python 3.6 but did not have a python_requires indicator that pip could use to resolve the correct version.

Coincidentally, Uvicorn actually added python_requires just a few weeks ago - but it wasn't out in a release yet, so pip install couldn't take it into account.

I raised this issue with the Uvicorn development team and they turned around a fix really promptly - 0.17.0.post1.

But before I had seen how fast the Uvicorn team could move I figured out how to fix the issue myself, thanks to a tip from Sam Hames on Twitter.

The key to fixing it was environment markers, a feature of Python's dependency resolution system that allows you to provide extra rules for when a dependency should be used.

Here's an install_requires= example showing these in action:

install_requires=[
    "uvicorn~=0.11",
    'uvicorn<=0.16.0;python_version<="3.6"'
]

This will install a Uvicorn version that loosely matches 0.11, but over-rides that rule to specify that it must be <=0.16.0 if the user's Python version is 3.6 or less.

Since Datasette 0.60.1 will be the last version of Datasette to support Python 3.6, I decided to play it safe and pin the dependencies of every library to the most recent version that I have tested in Python 3.6 myself. Here's the setup.py file I constructed for that.

This ties into a larger open question for me about Datasette's approach to pinning dependencies.

The rule of thumb I've heard is that you should pin dependencies for standalone released tools but leave dependencies loose for libraries that people will use as dependencies in their own projects - ensuring those users can run with different dependency versions if their projects require them.

Datasette is mostly a standalone tool - but it can also be used as a library. I'm actually planning to make its use as a library more obvious through improvements to the documentation in the future.

As such, pinning exact versions doesn't feel quite right to me.

Maybe the solution here is to split the reusable library parts of Datasette out into a separate package - maybe datasette-core - and have the datasette CLI package depend on exact pinned dependencies while the datasette-core library uses loose dependencies instead.

Still thinking about this.

Datasette documentation tweaks

Datasette uses Read The Docs to host the documentation. Among other benefits, this makes it easy to host multiple documentation versions:

docs.datasette.io/en/latest/ is the latest version of the documentation, continuously deployed from the main branch on GitHub
docs.datasette.io/en/stable/ is the documentation for the most recent stable (non alpha or beta) release - currently 0.60.1. This is the version you get when you run pip install datasette.
docs.datasette.io/en/0.59/ is the documentation for version 0.59 - and every version back to 0.22.1 is hosted under similar URLs, currently covering 73 different releases.

Those previous versions all automatically show a note at the top of the page warning that this is out-dated documentation and linking back to stable - a feature which Read The Docs provides automatically.

But... I noticed that /en/latest/ didn't do this. I wanted a warning banner to let people know that they were looking at the in-development version of the documentation.

After some digging around, I fixed it using a little bit of extra JavaScript added to my documentation template. Here's the key implementation detail:

jQuery(function ($) {
  // Show banner linking to /stable/ if this is a /latest/ page
  if (!/\/latest\//.test(location.pathname)) {
    return;
  }
  var stableUrl = location.pathname.replace("/latest/", "/stable/");
  // Check it's not a 404
  fetch(stableUrl, { method: "HEAD" }).then((response) => {
    if (response.status == 200) {
      // Page exists, insert a warning banner linking to it

This uses fetch() to make an HTTP HEAD request for the stable documentation page, and inserts a warning banner only if that page isn't a 404. This avoids linking to a non-existant documentation page if it has been created in development but not yet released as part of a stable release. Example here.

Thinking about this problem got me thinking about SEO.

A problem I've had with other projects that host multiple versions of their documentation is that sometimes I'll search on Google and end up landing on a page covering a much older version of their project. I think I've had this happen for both PostgreSQL and Python in the past.

What's best practice for avoiding this? I asked on Twitter and also started digging around for answers. "If in doubt, imitate Django" is a good general rule of thumb, so I had a look at how Django did this and spotted the following in the HTML of one of their prior version pages:

<link rel="canonical" href="https://docs.djangoproject.com/en/4.0/topics/db/">

So Django are using the rel=canonical tag to point crawlers towards their most recent release.

I decided to implement that myself... and then discovered that the Datasette documentation was doing it already! Read The Docs implement this piece of SEO best practice out of the box.

Datasette on Fly volumes

This one isn't released yet, but I made some good progress on it this week.

Fly.io announced this week that they would be providing 3GB of volume storage to accounts on their free tier. They called this announcement Free Postgres Databases, but tucked away in the blog post was this:

The lede is "free Postgres" because that's what matters to full stack apps. You don't have to use these for Postgres. If SQLite is more your jam, mount up to 3GB of volumes and use "free SQLite." Yeah, we're probably underselling that.

(There is evidence that they may have been nerd sniping me with that paragraph.)

I have a plugin called datasette-publish-fly which publishes Datasette instances to Fly. Obviously that needs to grow support for configuring volumes!

I've so far completed the research on how that feature should work. The next step is to finish implementing the feature.

sqlite-utils --help

I pushed out minor release sqlite-utils 3.22.1 today with one notable improvement: every single one of the 39 commands in the CLI tool now includes an example of usage as part of the --help text.

This feature was inspired by the new CLI reference page in the documentation, which shows the help output for every command on one page - making it much easier to spot potential improvements.

Releases this week

sqlite-utils: 3.22.1 - (94 releases total) - 2022-01-26
Python CLI utility and library for manipulating SQLite databases
s3-credentials: 0.10 - (10 releases total) - 2022-01-25
A tool for creating credentials for accessing S3 buckets
datasette: 0.60.1 - (106 releases total) - 2022-01-21
An open source multi-tool for exploring and publishing data

TIL this week

Tags: python, seo, datasette, weeknotes, fly, sqlite-utils, read-the-docs