Simon Willison's Weblog: projects

alchemy-utils 0.1a0

2026-08-12T19:51:30+00:00

I've long pondered what a database agnostic version of my sqlite-utils Python library and CLI utility might look like. This morning (literally a shower project) I tasked Codex and GPT-5.6 Sol Ultra with building a prototype:

Do a research spike to see what it would take to build a library with the same core API as SQLite-utils - in particular the insert and upsert and insert_all and upsert_all and create and update methods, and the table introspection stuff - but backed by SQLalchemy so it works for multiple database engines

Test against PostgreSQL and SQLite and duckdb

Use ~/dev/sqlite-utils for reference

Create a git repo for this and commit and early and often - use uv init to start the project - use red/green TDD and pytest, see ~/dev/django-sql-dashboard for one idea as to how the PostgreSQL tests could work

It took very few follow-up prompts to produce this project in a state good enough to release as an alpha.

Here's a one-liner I can use to list the rows in a table in my local PostgreSQL copy of my blog's database:

uvx --with 'alchemy-utils[postgresql]' alchemy-utils rows 'postgresql+psycopg://simon@localhost:5432/simonwillisonblog' redirects_redirect

The output from that starts like this:

[
  {
    "id": 2328,
    "domain": "simonwillison.net",
    "path": "2020/May/21/apple-photos-sqlite/",
    "target": "/2020/May/21/dogsheep-photos/",
    "created": "2020-05-21T13:03:46.591692-07:00"
  },
  {
    "id": 3,
    "domain": "feeds.simonwillison.net",
    "path": "swn-links",
    "target": "https://simonwillison.net/atom/links/",
    "created": "2017-10-01T14:12:54.820729-07:00"
  }

Or if you'd like a DuckDB database with every tree in San Francisco, schema created automatically to match the file:

curl 'https://raw.githubusercontent.com/simonw/sf-tree-history/refs/heads/main/Street_Tree_List.csv' | uvx --with 'alchemy-utils[duckdb]' alchemy-utils insert 'duckdb:////tmp/trees.db' trees - --csv

(That one took nearly an hour the first time I ran it, so I had Codex optimize it and got it down to around 35 seconds.)

Tags: databases, postgresql, projects, python, sql, sqlalchemy, sqlite, sqlite-utils, duckdb, coding-agents, codex

New release of LLM adds support for reasoning traces, OpenAI Responses, server-side tools, and smarter logging

2026-08-04T23:58:24+00:00

I released LLM 0.32 this morning, the most significant new version of LLM since the initial launch of the project. The new version includes support for visible reasoning traces, server-side provider tools, redesigned content-addressable SQLite logs, new models, and new features enabled by the OpenAI Responses API. I also released a new version of the llm-anthropic plugin with substantial updates of its own.

Headline features for LLM CLI users

Running LLM against reasoning models now displays their reasoning traces to standard error, so you can see what they are "thinking" without that information being included in the standard output that you might pipe to another tool. Add -R/--hide-reasoning to turn this off.

LLM includes support out-of-the-box for the GPT-5.6 model family, and the new default model used with llm "prompt" is now the inexpensive but capable GPT-5.6 Luna.

LLM calls can now use server-side tools from various providers. OpenAI provide a code execution environment as a server-side tool; LLM can now run prompts that benefit from that like so:

llm --tool CodeInterpreter 'Show current python and SQLite versions'

OpenAI also gets a WebSearch tool.

The llm-anthropic plugin adds WebSearch, WebFetch, CodeExecution, and AnthropicMCP, which looks like this:

llm -m claude-sonnet-5 -T 'AnthropicMCP("https://datasette.simonwillison.net/-/mcp")' \
  'how many rows in the blog_blogmark table?'

That causes Anthropic to execute MCP calls against my new datasette-mcp plugin as part of a single request/response interaction with their API.

The new llm openai endpoint command provides a tool for executing prompts against any OpenAI compatible endpoint as a one-liner. These aren't logged, which makes this a handy tool for running one-off prompts against anything that speaks the lingua franca of the LLM API world.

Here's how I use that to run prompts against Gemma 4 12B running in my localhost LM Studio API, via uvx (no LLM installation required) and mixing in the llm-tools-quickjs tool plugin for good measure:

uvx --with llm-tools-quickjs \
  llm openai endpoint http://localhost:1234/v1 -m google/gemma-4-12b \
  -T QuickJS 'Use QuickJS to multiply 3434 * 2434' --td

New features in the Python API

LLM's Python API previously required you to create a conversation and then send messages to it one at a time. This was an abstraction over the true nature of LLMs, where each request carries a complete history of the messages that came before it. That abstraction started to get in the way for some more advanced cases, so the new release introduces a model.prompt(messages=[]) parameter that can be used like this:

import llm
from llm import user, assistant, system

model = llm.get_model("gpt-5.6-luna")

response = model.prompt(messages=[
    system("You are a helpful pirate."),
    user("What is the capital of France?"),
    assistant("Paris, matey."),
    user("And Germany?"),
])
print(response.text())

LLM previously returned an iterable sequence of strings from each prompt. This worked great when models returned a string response, but failed to predict the weird shape that models would evolve towards. Today many models return a mix of reasoning text, output strings, tool calls, and even image attachments. With LLM 0.32 you can do this instead:

for event in model.prompt("Explain cats").stream_events():
    if event.type == "reasoning":
        print(f"[thinking] {event.chunk}", end="", flush=True)
    elif event.type == "text":
        print(event.chunk, end="", flush=True)
    else:
        print(f"Other event: {event}")

Combine these features and we can finally provide a robust implementation of the semi-standard OpenAI chat completions API, which I've now released as the llm-chat-completions-server plugin:

llm install llm-chat-completions-server
llm chat-completions-server --port 9000
# Server is now running on http://127.0.0.1:9000/v1

Now you can run prompts against LLM via that server, using the new llm openai endpoint command!

llm openai endpoint http://127.0.0.1:9000/v1 'hello' -m gpt-5.4-mini

The bigger challenge with that kind of API concerns logging. If we're going to support the pattern where the message sequence is appended to on every request, ideally we can avoid logging all of that duplicate JSON for every turn.

The solution is the new content-addressable message store, modeled after Git. You can see the new schema for that in the documentation, but the llm logs and llm logs --json commands have both been upgraded to convert that format back into something that's easy to consume.

And the rest

There is a whole lot more in this release. The 0.32 release notes are pretty comprehensive, and the notes for 0.32rc2, 0.32rc, 0.32a3, 0.32a2, and 0.32a0 should fill in any gaps.

Existing LLM plugins should all continue to work, but plugins that provide extra models will need to be upgraded to 0.32 in order to participate fully in the new streaming events system. There's a guide to implementing plugins with Structured messages and streaming events in the documentation.

I've updated some of my own plugins:

llm-anthropic 0.26 adds support for the Claude 5 family of models, plus WebSearch, WebFetch, CodeExecution, and AnthropicMCP server-side tools.
llm-gemini and llm-openrouter and llm-mistral are nearly there, releases coming soon.

I guess LLM is an agent framework now

Quite a few of the lower-level tools changes in this release were driven by the needs of Datasette Agent. When I started work on LLM, the term "agent" had such a vague definition that I refused to use it. In September 2025 I came around to the idea that "An LLM agent runs tools in a loop to achieve a goal" is well established enough now that I could stop avoiding the term entirely.

Tool chains can now pause for human approval and resume from a stored message history - both needed by Datasette Agent.

Looking at LLM today it's beginning to look very agent-shaped to me. There's something neat about having a CLI utility that can mix and match different tools from different sources with different models all as a one-liner, and that includes a Python library powerful enough to build systems like Datasette Agent and llm-coding-agent.

Maybe the next version of LLM will bake the concept of an "agent" into the core library. I'm still trying to figure out what that would look like.

Tags: projects, releases, ai, openai, generative-ai, llms, llm, anthropic, llm-tool-use, llm-reasoning, model-context-protocol

condense-json 1.0

2026-08-02T22:19:59+00:00

Release: condense-json 1.0

I'm trying to get braver at releasing 1.0 versions. This little library is a year and a half old now - I've applied some sensible and non-disruptive fixes and shipped the big 1.0 for it.

Here's an example of what it can do, lifted from the README:

{
  "foo": {
    "bar": {
      "string": "This is a string with foxes in it",
      "nested": {
        "more": ["Here is a string", "another with foxes in it too"]
      }
    }
  }
}

Combine that with a replacements object:

{"1": "with foxes in it"}

And condense_json(input_json, replacements) produces the following:

{
  "foo": {
    "bar": {
      "string": {"$r": ["This is a string ", {"$": "1"}]},
      "nested": {
        "more": ["Here is a string", {"$r": ["another ", {"$": "1"}, " too"]}]
      }
    }
  }
}

It scans for strings or substrings that are present in that replacements object and replaces those with a special {"$r": ...} syntax in the output.

You can reverse the effect with uncondense_json(condensed, replacements).

The idea is to make it easier to store JSON that includes duplicated data from other related structures. I use it to save space in the SQLite logs generated by LLM - see PR #1586 for the latest iteration of that.

Tags: json, projects, python, llm

Stateless MCP has recaptured my interest (and inspired mcp-explorer and datasette-mcp)

2026-07-31T23:13:22+00:00

Tuesday was Stateless MCP day - the rollout of MCP 2.0, or the 2026-07-28 Model Context Protocol specification to use the more formal but less memorable name. This is the most significant change to the MCP spec since it first launched, and has also served to reignite my personal interest in the protocol.

For background: MCP is the Model Context Protocol, which describes a standard way to expose new tools to LLM-powered agent frameworks. It was introduced by Anthropic back in November 2024, had a huge spike of interest through much of 2025, and then became somewhat eclipsed by Skills (another Anthropic invention) when it became apparent that an agent harness with access to a terminal and curl could do most of what MCP did in a more flexible way. I wrote about that in my review of 2025.

I'm coming back around to MCP now. Giving an agent a shell environment with the ability to access the internet is fraught with risk, and requires a strong model that is capable of effectively driving such an environment. MCP tools are easier to audit and control, and simple enough that smaller models that run on a laptop can still drive them reasonably well.

The new stateless MCP specification also greatly decreases the complexity of implementing both clients and servers for the protocol. I built three of those this week!

What's easier with stateless MCP

The best demonstration of the difference between stateful and stateless MCP is in this May 21st blog post that introduced the RC for the new specification. It included a clear before-and-after example.

The older stateful MCP (I'm going to call it "legacy MCP") required two HTTP requests - the first to initialize a session and obtain a Mcp-Session-Id, and the second to actually call the tool:

POST /mcp HTTP/1.1
Content-Type: application/json

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "initialize",
  "params": {
    "protocolVersion": "2025-11-25",
    "capabilities": {
    },
    "clientInfo": {
      "name": "my-app",
      "version": "1.0"
    }
  }
}

POST /mcp HTTP/1.1
Mcp-Session-Id: 1868a90c-3a3f-4f5b
Content-Type: application/json

{
  "jsonrpc": "2.0",
  "id": 2,
  "method": "tools/call",
  "params": {
    "name": "search",
    "arguments": {
      "q": "otters"
    }
  }
}

The new stateless way uses a single HTTP request which looks like this:

POST /mcp HTTP/1.1
MCP-Protocol-Version: 2026-07-28
Mcp-Method: tools/call
Mcp-Name: search
Content-Type: application/json

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "search",
    "arguments": {
      "q": "otters"
    },
    "_meta": {
      "io.modelcontextprotocol/clientInfo": {
        "name": "my-app",
        "version": "1.0"
      }
    }
  }
}

This is so much cleaner from both a client- and server-side implementation perspective. It's also a better fit for building scalable web applications, since now you don't need to maintain server-side state to keep track of those session IDs, or worry about routing the same session to the same backend machine.

mcp-explorer

I couldn't find a great CLI tool for interactively probing an MCP server, so I had Codex help build my own.

mcp-explorer is the result. It's a stateless Python CLI tool, so you don't even need to install it to try it out - it works with uvx like this:

uvx mcp-explorer list https://agentic-mermaid.dev/mcp

This queries Ade Oshineye's agentic-mermaid.dev demo MCP. The above command returns the following list of tools:

execute(code: string, timeoutMs?: integer) - Execute Mermaid SDK code
  Run JavaScript in an isolated sandbox; return a value.

describe_sdk(family: string, detail?: string) - Describe Mermaid SDK operations
  Return version-matched mutation operations for one diagram family.

render_svg(source: string, options?: object) - Render Mermaid as SVG
  Render a Mermaid source string to themeable SVG. Returns { ok, svg }.

render_ascii(source: string, useAscii?: boolean, targetWidth?: integer, options?: object) - Render Mermaid as text
  Render a Mermaid source string to text. Returns { ok, text }.

render_png(source: string, scale?: number, background?: string, fitTo?: object, options?: object) - Render Mermaid as PNG
  Rasterize a Mermaid source string to PNG. Returns { ok, png_base64 }.
...

Then to inspect a tool:

uvx mcp-explorer inspect render_svg

This outputs a whole bunch of information, including the JSON schema of the inputs and outputs.

To call that tool and pass arguments to it:

uvx mcp-explorer call \
  https://agentic-mermaid.dev/mcp \
  render_svg \
  -a source 'graph TD; A-->B' \
  -a options '{"padding":24}'

Which returns:

{"ok":true,"svg":"<svg xmlns=\"http://www.w3.org/2000/svg\" width=...

To get just the raw SVG try adding | jq .svg -r to that command. I got back this image:

There are a few more commands in the README, but you get the general idea. I find building CLI tools like this to be a really productive way to get familiar with a specification, even if an agent writes most of the actual code.

datasette-mcp

The second project is datasette-mcp, a Datasette plugin which adds a /-/mcp endpoint to any Datasette instance.

This is probably the fourth time I've tried building this plugin, but thanks to the new stateless MCP specification I finally have a version that feels good to release.

It provides just three tools: list_databases(), get_database_schema(database_name), and execute_sql(database_name, sql). They do exactly what you would expect them to do - though execute_sql() is read-only for the moment.

Wire these into an agent, or a chat tool like ChatGPT or Claude, and they'll gain the ability to run SQL queries against your hosted Datasette instance.

So far I'm running it on the Datasette mirror of my blog, at datasette.simonwillison.net/-/mcp. It took a bit of fiddling to figure out how to attach that to ChatGPT and Claude, but I got there in the end. Here's a new TIL showing exactly how to do that.

Here's a shared Claude session where I asked it:

list tables in simonwillison.net

And then:

what has Simon said recently about MCP?

It ran 7 separate SQL queries to figure out the answer.

llm-mcp-client

My LLM tool is long overdue for an official MCP integration. The new alpha llm-mcp-client plugin is my attempt at exactly that:

llm install llm-mcp-client
llm -T 'MCP("https://datasette.simonwillison.net/-/mcp")' 'count the notes'

Here's the output (including reasoning trace, I'm using LLM 0.32rc2):

Considering note count

I see the question "count the notes" is probably asking me to tally up blog notes. It could also mean published notes or drafts, so there's some ambiguity there. I'll need to figure out the total number of notes, likely by querying the count for both published notes and drafts to get a clear answer. Let's execute that count!

There are 151 notes.

And the output of llm logs for that prompt.

Once this is fully baked, I'm considering bringing it directly into LLM core. I'm excited to experiment with MCP in Datasette Agent and llm-coding-agent as well.

MCP is a safer way to build with agents

A few months after MCP was first released, I wrote Model Context Protocol has prompt injection security problems, where I noted that the pattern of having end users mix and match tools pushed responsibility for avoiding data exfiltration attacks out to the users themselves. I hadn't coined the Lethal Trifecta yet, but that was absolutely what I had in mind.

Then general agents with arbitrary shell and curl access came along, and that's so much harder to keep secure!

Something I've come to appreciate about MCP is that it's much easier to reason about agent capabilities and what might go wrong than with arbitrary command execution in an open network environment - the default for most of today's general and coding agent tools.

I plan to lean into MCP a whole lot more when I'm building sensitive applications on top of LLMs.

Tags: projects, ai, datasette, mermaid, generative-ai, llms, llm, anthropic, model-context-protocol

smevals - a small eval suite for evaluating models, prompts, and harnesses

2026-07-31T21:15:23+00:00

smevals - a small eval suite for evaluating models, prompts, and harnesses

I've been working with Jesse Vincent's Prime Radiant applied AI research lab building out this evals framework to help answer questions about the capabilities of different models.

The result is smevals, a new tool for running small eval suites across different model configurations and grading the results.

The blog entry describes the tool in detail. Here's the 10 second version:

Tell your coding agent to run uvx smevals docs to learn the tool (this outputs the README)
Then tell it to build you an eval suite

Once you've created an eval - which takes the form of a directory with some YAML files - you can run it against models like this:

uvx smevals run path-to-eval/ -m gpt-5.5 -m claude-opus-4.6

Runs are treated separately from grading operations - you can grade your runs (against your defined set of checks) using:

uvx smevals grade path-to-eval/

Then you can run a localhost web server to explore the results:

uvx smevals serve path-to-eval/

Or run the smevals build command to build that report as static HTML, which you can then host anywhere. Here's an example showing an eval suite I built to evaluate how well models can write haikus.

The most time-consuming part of this project was figuring out the vocabulary for it! Here's what I settled on, quoted from the announcement:

An eval is a collection of challenges designed to answer a question about a model, for example, how good is that model at generating SVGs?

Each eval is a collection of tasks. A task is a specific challenge, for example "Generate an SVG of a pelican riding a bicycle".

When you run the eval you do so against one or more configs. Each config specifies a model to be evaluated, but may also include other parameters to test, such as different system prompts, model parameters, or agent harnesses.

A run records what happened when a specific config was used to execute a specific task. A runner is the script that executes a run.

Once you have collected one or more runs, you need to evaluate the results to see how well the model (or config) did. This is done by a grader, which produces a grade.

Each grader runs a sequence of checks. These can be simple operations, like checking for a specific string in the output, or confirming that the output is valid XML. They can also be more complicated custom operations (implemented as scripts called checkers), including using other models to answer questions about the run.

I've been trying to figure out an approach I like for evals for several years now. smevals is my third iteration on the idea and it feels right to me. I'm looking forward to expanding this more in the future, as well as pointing it at some of my own projects.

Tags: projects, ai, generative-ai, llms, llm, evals, jesse-vincent

llm-chat-completions-server 0.1a0

2026-07-30T15:43:16+00:00

Release: llm-chat-completions-server 0.1a0

A key goal of the new content-addressable logs in LLM 0.32rc1 was being able to support OpenAI Chat Completion style requests where each incoming message extends the previous conversation, like this:

curl http://localhost:8002/v1/chat/completions \
  -H 'Content-Type: application/json' \
  -d '{
    "model": "qwen3.5-4b",
    "messages": [
      {"role": "user", "content": "Capital of France?"},
      {"role": "assistant", "content": "Paris."},
      {"role": "user", "content": "Germany?"}
    ]
  }'

Here the conversation state is tracked by the client, so each of these requests gets longer and longer. The new schema design in LLM is designed to de-duplicate these using hashes of the individual message parts.

To test that out, I built this plugin:

uv tool install llm --pre
llm install llm-chat-completions-server
llm chat-completions-server -p 9001

Running this starts a localhost server on port 9001 that exposes your full collection of LLM models (from any plugins you have installed) using a ChatGPT Completions compatible endpoint.

GPT-5.6 Sol wrote the whole thing - it turns out it knows the OpenAI Chat Completions API shape really well.

Tags: projects, openai, llm

sqlite-utils 4.1

2026-07-11T23:50:20+00:00

Release: sqlite-utils 4.1

The first dot-release since 4.0 a few days ago, introducing a number of minor new features.

sqlite-utils insert and sqlite-utils upsert now accept a --code option for providing a block of Python code (or a path to a .py file) that defines a rows() function or rows iterable of rows to insert, as an alternative to importing from a file. (#684)

sqlite-utils already had features that allow you to pass blocks of Python code as CLI arguments, for example this one for the sqlite-utils convert command:

sqlite-utils convert content.db articles headline '
def convert(value):
    return value.upper()'

Allowing blocks of code to generate new rows directly was on obvious extension of that pattern:

sqlite-utils insert data.db creatures --code '
def rows():
    yield {"id": 1, "name": "Cleo"}
    yield {"id": 2, "name": "Suna"}
' --pk id

sqlite-utils insert and sqlite-utils upsert now accept --type column-name type to override the type automatically chosen when the table is created. This is useful for CSV or TSV columns such as ZIP codes that look like integers but should be stored as TEXT to preserve leading zeros. (#131)

A long-standing feature request which turned out to be a simple implementation.

New table.drop_index(name) method and sqlite-utils drop-index command for dropping an index by name. Both accept ignore=True/--ignore to ignore a missing index. (#626)

sqlite-utils query can now read the SQL query from standard input by passing - in place of the query, for example echo "select * from dogs" | sqlite-utils query dogs.db -. (#765)

Two more small features. I had Codex review all open issues and highlight the easiest ones!

sqlite-utils upsert can now infer the primary key of an existing table, so --pk can be omitted when upserting into a table that already has a primary key.

Another Codex suggestion, an obvious missing CLI feature from a Python library improvement that shipped in the 4.0 release.

table.transform() and table.transform_sql() now accept strict=True or strict=False to change a table’s SQLite strict mode. Omitting the option preserves the existing mode. (#787)

The sqlite-utils transform command now accepts --strict and --no-strict to change a table’s strict mode. (#787)

These two were inspired by Prefer STRICT tables in SQLite by Evan Hahn, which did the rounds on Hacker News today. Evan pointed out that:

Unfortunately, I don’t think there’s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one.

That's exactly what the sqlite-utils transform mechanism does, so I extended it to add the ability to switch tables from strict to non-strict and vice-versa.

Here's the GPT-5.6 Sol xhigh Codex transcript I used to implement those new strict table features. One of the most useful prompts I ran was this one:

use uv run python -c and manually exercise the new .transform(strict=) option, see if you can find any edge-cases or bugs

Effectively telling the model to manually test its work, outside of the automated tests it had already written. This turned up two minor issues that we then fixed.

Tags: projects, python, sqlite, sqlite-utils, annotated-release-notes, ai-assisted-programming

sqlite-utils 4.0, now with database schema migrations

2026-07-07T19:32:57+00:00

This morning I released sqlite-utils 4.0, the 124th release of that project and the first major version bump since 3.0 in November 2020. In addition to some small but significant breaking changes (described in this upgrade guide), this version introduces three major features: database migrations, nested transactions (via a new db.atomic() method), and support for compound foreign keys.

Database schema migrations using sqlite-utils

Schema migrations define a sequence of changes to be made to a SQLite database, plus a mechanism for tracking which migrations have been applied and applying any that are found to be pending.

Migrations are defined in Python files using the sqlite-utils Python library, which includes a powerful table.transform() method providing enhanced alter table capabilities that are not supported by SQLite's ALTER TABLE statement.

(table.transform() implements the pattern recommended by the SQLite documentation - create a new temporary table with the new schema, copy across the data, then drop the old table and rename the temporary one in its place.)

Here's an example migration file which creates a table called creatures, adds an additional column to it in a second step, then changes the types of two of the columns in a third:

from sqlite_utils import Migrations

migrations = Migrations("creatures")

@migrations()
def create_table(db):
    db["creatures"].create(
        {"id": int, "name": str, "species": str},
        pk="id",
    )

@migrations()
def add_weight(db):
    db["creatures"].add_column("weight", float)

@migrations()
def change_column_types(db):
    db["creatures"].transform(types={"species": int, "weight": str})

Save that as migrations.py and run it against a fresh database like this:

uvx sqlite-utils migrate data.db migrations.py

Then if you check the schema of that database:

uvx sqlite-utils schema data.db

You'll see this SQL:

CREATE TABLE "_sqlite_migrations" (
   "id" INTEGER PRIMARY KEY,
   "migration_set" TEXT,
   "name" TEXT,
   "applied_at" TEXT
);
CREATE UNIQUE INDEX "idx__sqlite_migrations_migration_set_name"
    ON "_sqlite_migrations" ("migration_set", "name");
CREATE TABLE "creatures" (
   "id" INTEGER PRIMARY KEY,
   "name" TEXT,
   "species" INTEGER,
   "weight" TEXT
);

The _sqlite_migrations table is used to keep track of which migration functions have been run. The creatures table above is the schema after all three migrations have been applied.

To see a list of migrations, both pending and applied, run this:

uvx sqlite-utils migrate data.db migrations.py --list

Output:

Migrations for: creatures

  Applied:
    create_table - 2026-07-07 17:58:41.360051+00:00
    add_weight - 2026-07-07 17:58:41.360608+00:00
    change_column_types - 2026-07-07 18:01:15.802000+00:00

  Pending:
    (none)

If you don't specify a migrations file, the sqlite-utils migrate data.db command will scan the current directory and its subdirectories for files called migrations.py and apply any Migrations() instances it finds in them.

You can also execute migrations from Python code using the migrations.apply(db) method, which is useful for building tools that manage their own database schemas over multiple versions. My own LLM tool has been using a version of this pattern for several years now, as shown in llm/embeddings_migrations.py.

Prior art

My favorite implementation of this pattern remains Django's Migrations, developed by Andrew Godwin based on his earlier project South. Fun fact: Andrew, Russ Keith-Magee, and I presented our competing approaches to schema migrations for Django on the Schema Evolution panel at the very first DjangoCon back in 2008! My attempt was called dmigrations, developed with a team at Global Radio in London.

Django's migrations can be automatically generated from model definitions and include the ability to roll back to a previous version. The sqlite-utils approach is deliberately simpler: unlike Django, sqlite-utils encourages programmatic table creation rather than a model definition ORM, so there isn't anything we can use to automatically generate migrations.

I decided to skip rollback, since in my experience it's a feature that is rarely used. With a SQLite project, an easy way to achieve rollback is to create a copy of your database file before you apply the migrations!

Migrating from sqlite-migrate

The design of sqlite-utils migrations is three years old now - I had originally released it as a separate package called sqlite-migrate, which never quite graduated beyond a beta release.

I've used that package in enough places now that I'm confident in the design, so I've decided to promote it to a feature of sqlite-utils to make it available by default to all of the other tools in the growing sqlite-utils/Datasette/LLM ecosystem.

I made one last release of sqlite-migrate, which switches it to depend on sqlite-utils>=4 and replaces the __init__.py file with the following:

from sqlite_utils import Migrations

__all__ = ["Migrations"]

Any existing project that depends on sqlite-migrate should continue to work without alterations.

Everything else in sqlite-utils 4.0

Here are the release notes for this version, with some inline annotations:

The 4.0 release includes some minor backwards-incompatible fixes (hence the major version number bump) and introduces three major new features:

Database migrations, providing a structured mechanism for evolving a project’s schema over time. (#752)

I think of migrations as the signature new feature, hence this blog post.

Nested transaction support via db.atomic(), plus numerous improvements to how transactions work across the library. (#755)

sqlite-utils has long had a confused relationship with database transactions, partly because when I started designing the library back in 2018 I didn't yet have a great feel for how those worked in SQLite itself.

Adding migrations to the core library made me determined to finally crack this nut, since transactions make migration systems a whole lot safer and easier to reason about.

I ended up building this around a db.atomic() context manager which looks like this:

with db.atomic():
    db.table("dogs").insert({"id": 1, "name": "Cleo"}, pk="id")
    db.table("dogs").insert({"id": 2, "name": "Pancakes"})

SQLite supports Savepoints, and as a result db.atomic() can be nested to carry out transactions inside of transactions. It's pretty neat!

Support for compound foreign keys, including creation, transformation and introspection through table.foreign_keys. (#594)

This came about when I asked a coding agent to review all open issues and PRs for things that should be included in a 4.0 release since they would represent breaking changes if I added them later, and it correctly identified that compound foreign keys were exactly that kind of feature.

I started with a breaking change to the table.foreign_keys introspection method, and then decided to see if Claude Fable 5 could handle the more fiddly job of integrating compound foreign key creation into the library. The API design it helped create felt exactly right to me - consistent with how the rest of the library worked already.

Other notable changes include:

Upserts now use SQLite’s INSERT ... ON CONFLICT ... DO UPDATE SET syntax, detect existing table primary keys automatically and reject records that are missing required primary key values. (#652)

This was the change that first pushed me to consider a breaking-change 4.0 version bump. I built this to help support sqlite-chronicle, which uses triggers to keep track of rows in a table that have been inserted, updated or deleted.

db.query() now executes immediately and rejects statements that do not return rows; use db.execute() for writes and DDL.

Probably the most disruptive breaking change - I've had to update a few places in my own code to switch from db.query() to db.execute() as a result.

CSV and TSV imports now detect column types by default, while inserts into existing tables preserve those tables’ column types. (#679)

The sqlite-utils insert data.db creatures creatures.csv --detect-types flag was a later addition to allow column types (text, integer, real) to be automatically detected based on the data in a CSV. It should be the default, and releasing a 4.0 means I can make it so.

table.extract() and extracts= no longer create lookup table records for all-null values. (#186)

The oldest issue addressed by this release - the underlying bug was opened (by me) in October 2020.

See Upgrading from 3.x to 4.0 for details on backwards-incompatible changes.

The detailed release notes for the features and fixes shipped during the 4.0 pre-release cycle are available in 4.0a0, 4.0a1, 4.0rc1, 4.0rc2, 4.0rc3 and 4.0rc4.

The upgrade guide was entirely written by Claude Fable 5, Claude Opus 4.8 and GPT-5.5. The same is true of the release notes.

This is the kind of documentation I've slowly become comfortable outsourcing to the robots. It doesn't need to convince people of anything, or express any opinions - its job is to be as accurate and detailed as possible. I've reviewed the release notes closely and can confirm they are accurate and comprehensive.

Claude Fable 5 helped a lot

I released the first alpha of sqlite-utils 4.0 over a year ago. I've been dragging my heels on the stable release because of the amount of work it would take to track down and clean up the many other minor design flaws that a major version number allowed me to take on.

Assistance from Claude Fable 5 (and to a lesser extent Opus 4.8 and GPT-5.5) gave me just the boost I needed to overcome inertia and make the most of the time I could afford to spend on this library.

Fable has really good taste in API design, and is relentlessly proactive if you give it a more open goal. My most successful prompt was a review task that I issued against what I thought was the last release candidate:

review the changes on main since the last tagged 3.x release - I am about to ship them as sqlite-utils 4.0, a stable version that promises no backwards-incompatible fixes for a very long time.

review the changelog and upgrade guide, and write yourself scratch scripts to try out all of the new features in v4 - save those scripts but don't commit them

I tried this with GPT-5.5 xhigh in Codex Desktop and Fable 5 in Claude Code.

GPT-5.5 wrote 5 Python scripts and didn't turn up anything particularly interesting - its final report is here.

Fable 5 wrote 12 scripts, identified 4 release blockers and 10 additional issues in its report, and built a neat combined repro script, which, when run, output the following:

=== 1. Failed db.execute() write leaves an implicit transaction open ===
  in_transaction after failed write: True
  BUG: table 'other' silently lost when connection closed

=== 2. Leading ';' bypasses the query() first-token scanner ===
  BUG: raised OperationalError: no such savepoint: sqlite_utils_query
  BUG: row persisted despite rollback (count=1)

=== 3. Rejected write PRAGMA via query() still takes effect ===
  BUG: user_version=5 after 'rejected' statement (docs say no effect)

=== 4. Implicit compound FK resolves pk columns in table order, not PK order ===
  BUG: other_columns reported as ('b', 'a'), should be ('a', 'b')
  BUG: transform of valid data raised IntegrityError: FOREIGN KEY constraint failed

=== 5. ForeignKey (now a dataclass) is no longer hashable ===
  BUG: cannot use 'sqlite_utils.db.ForeignKey' as a set element (unhashable type: 'ForeignKey')

=== 6. Mixed ForeignKey objects and tuples in foreign_keys= rejected ===
  BUG: foreign_keys= should be a list of tuples

=== 7. insert --csv into an EXISTING table transforms its column types ===
  BUG: existing zip '01234' is now 1234 (column type: int)

=== 8. insert(pk=, alter=True) regression: InvalidColumns before alter runs ===
  BUG: InvalidColumns: Invalid primary key column ['id'] for table t with columns ['a']

=== 9. migrate --stop-before an already-applied migration applies everything ===
  BUG: m2 was applied despite --stop-before m1 (m1 already applied)

=== 10. ensure_autocommit_on() silently commits an open transaction ===
  BUG: row survived rollback (count=1) - transaction was committed

I found myself agreeing with almost all of them. Here's the PR with 16 commits where we worked through them in turn.

There's no doubt in my mind that sqlite-utils 4.0 is a significantly higher-quality release than if I had built it without the assistance of the latest frontier models.

Tags: schema-migrations, projects, sqlite, ai, sqlite-utils, annotated-release-notes, generative-ai, llms, ai-assisted-programming, anthropic, claude, agentic-engineering, claude-mythos-fable

sqlite-utils 4.0rc3

2026-07-06T05:40:08+00:00

Release: sqlite-utils 4.0rc3

I hoped to release sqlite-utils 4.0 stable this weekend, but as I worked through the backlog of issues and PRs with a combination of Claude Fable 5 and GPT-5.5 the changelog since rc2 kept getting bigger.

The biggest new feature is support for introspecting and creating compound foreign keys - a feature that involves a subtle breaking change to table.foreign_keys and hence needed to land for the 4.0 stable release.

sqlite-utils also now follows SQLite's convention for case insensitive column names, which turned out to touch a bunch of different places at once.

Tags: projects, sqlite, sqlite-utils, annotated-release-notes, gpt, claude-mythos-fable

sqlite-utils 4.0rc2, mostly written by Claude Fable (for about $149.25)

2026-07-05T01:00:48+00:00

I wrote about the sqlite-utils 4.0rc1 release a couple of weeks ago. Since we only have Claude Fable on our Max subscriptions for a few more days, I decided to see if it could help me get to a 4.0 stable release that I felt truly comfortable about, since I try to keep to SemVer and like my incompatible major versions to be as rare as possible.

I started with this prompt, in Claude Code for web on my iPhone:

Final review before shipping a stable 4.0 release - very important to spot any last minute things that would be a breaking change if we fix them later

Here's that initial report it created for me. There were some significant problems that I hadn't myself encountered yet - 5 that Fable categorized as "release blockers". Here's the worst of the bunch:

1. delete_where() never commits and poisons the connection (data loss)

Table.delete_where() (sqlite_utils/db.py:2948) runs its DELETE via a bare self.db.execute() with no atomic() wrapper — compare Table.delete() at db.py:2944, which wraps correctly. The connection is left in_transaction=True, so every subsequent atomic() call takes the savepoint branch (db.py:430-440) and never commits either.

Reproduced end-to-end:
db = sqlite_utils.Database("dw.db")
db["t"].insert_all([{"id": i} for i in range(3)], pk="id")
db["t"].delete_where("id = ?", [0])   # conn.in_transaction is now True
db["t"].insert({"id": 50})
db["u"].insert({"a": 1})
db.close()
# Reopen: rows are [0, 1, 2] — the delete, row 50, AND table u are all gone.

That's a really bad bug! Very glad I didn't ship that, although at least it would have been a bug I could fix in a 4.0.1 point release, not a design flaw that would force a 5.0.

Over the course of 37 prompts, 34 commits and +1,321 -190 code changes over 30 separate files, we worked through the entire set of feedback in turn, making several other design improvements along the way.

A weird thing about coding agents is that harder tasks like this one actually provide more opportunity to do other things at the same time, since the agent sometimes needs 10-15 minutes to churn away on a new task. I went out to enjoy the Half Moon Bay 4th of July parade, occasionally checking in and prompting the next step for Fable from my phone.

Full details in the PR and this shared transcript. I switched to my laptop for the final review, which I conducted through GitHub's PR interface.

The most significant changes relate to transaction handling, which was the signature new feature in the earlier RC. The new RC now includes comprehensive documentation on the new transaction model, the intro to which I'll quote here in full:

Every method in this library that writes to the database - insert(), upsert(), update(), delete(), delete_where(), transform(), create_table(), create_index(), enable_fts() and the rest - runs inside its own transaction and commits it before returning. Your changes are saved to disk as soon as the method call finishes:
db = Database("data.db")
db.table("news").insert({"headline": "Dog wins award"})
# The new row is already saved - no commit() required
The same applies to raw SQL executed with db.execute() - a write statement is committed as soon as it has run.

You never need to call commit(), and you do not need to close the database to persist your changes. There are exactly two situations where you need to think about transactions:

You want to group several write operations together, so they either all succeed or all fail - use db.atomic().

You are managing a transaction yourself with db.begin(), in which case nothing is committed until you commit - the library will never commit a transaction you opened.

In reviewing Fable's documentation - I find that reviewing the documentation edits first is an excellent way to build an initial understanding of what has changed - I spotted this detail:

db.atomic() and the automatic per-method transactions are designed for connections in Python's default transaction handling mode. Connections created with the Python 3.12+ sqlite3.connect(..., autocommit=True) or autocommit=False options are not supported, because commit() and rollback() behave differently on those connections.

I admit I hadn't thought about how sqlite-utils would react to the more recent autocommit setting, added in Python 3.12. It turns out "behave differently on those connections" equated to almost the entire test suite failing, so I worked with the model to ensure that this difference would not break how the library works.

And a final review by GPT-5.5

I used to think that the idea of having one model review the work of another was somewhat absurd - it felt weirdly superstitious. The problem is it really does work - I've started habitually having Anthropic's best model review OpenAI's work and vice versa, because I've had that turn up interesting results often enough to be valuable.

I prompted Codex Desktop and GPT-5.5 xhigh with the following:

Review changes since the last RC. Also confirm that the changelog is up-to-date.

Which was enough to turn up two issues worth investigating:

Findings

[P1] sqlite_utils/db.py:663 db.query() now rejects non-row statements only after calling db.execute(), and sqlite_utils/db.py:705 auto-commits those writes first. So db.query("update ...") raises ValueError but the update is already committed. That is a surprising side effect for a method documented as “can only be used with SQL that returns rows.”

[P1] sqlite_utils/db.py:672 INSERT ... RETURNING through db.query() only commits after the returned generator is fully exhausted. db.query("insert ... returning ...") without iteration, or common next(db.query(...)) usage, leaves the transaction open and the write can be rolled back on close. This contradicts docs/changelog.rst:15 and docs/python-api.rst:232, which say it takes effect without iteration.

I pasted that into a fresh Fable session, which ran some experiments to confirm the problem:

Both findings were confirmed. db.query() called self.execute() first, which auto-commits writes, and only then checked cursor.description — so db.query("update ...") committed the update before raising ValueError. And the INSERT ... RETURNING commit lived at the end of the returned generator, so it never fired unless you exhausted the iterator — next(db.query(...)) or an un-iterated call left the transaction open, contradicting what the changelog and docs promise.

Here's the PR with the fix, and the full Claude Code transcript. Reviewing this code helped me build a better mental model of the edge cases of SQLite transaction semantics!

For an estimated (unsubsidized) cost of $149.25

I upgraded to the Claude Max $200/month plan (I was previously on $100/month) to increase my Fable allowance for the remaining time until the July 7th Fablepocalypse, when even Claude Max subscribers will have to pay full API cost for the model.

I was curious as to how much this would have cost me if I had been paying those costs directly. At first I thought those numbers weren't available to me since I had run the work remotely using Claude Code for web, and then I realized I could run AgentsView inside that existing session to get that cost estimate!

Run "uvx agentsview --help" and then use that tool to calculate the cost of this session

Claude figured out how to use the session list --include-children command and came out with the following:

Transcript	Model	Cost
Main session	claude-fable-5	$141.02
API-surface sweep agent	claude-fable-5	$2.40
Transactions/atomic review agent	claude-fable-5	$2.39
Post-rc1 commits review agent	claude-fable-5	$1.72
Migrations review agent	claude-fable-5	$1.40
Prompt-counting agent	claude-opus-4-8	$0.32
Total		$149.25

I'm very glad I'm on that subscription! I really should have followed my own advice and leaned more heavily into subagents with cheaper models.

Here's what claude.ai/settings/usage is showing me right now:

I have several other major Fable-driven projects on the go right now as well, with the goal of hitting 100% on that Fable bar just in time for the price increase.

The full release notes for sqlite-utils 4.0rc2

Here are the full release notes for the RC. I had Fable add these to an "Unreleased" section of the changelog as each change landed, reviewing them as it went. This has the neat side effect that the commit history of the changelog acts as a concise summary of each of the changes that went into the release.

In the past I've had a policy of writing release notes by hand, but honestly these are better than I would have created myself. Release notes are a great example of writing that I'm OK to outsource to agents because they need to be boring, predictable and accurate.

Breaking changes:

Write statements executed with db.execute() are now committed automatically, unless a transaction is already open in which case they join it. Previously they opened an implicit transaction that stayed open until something committed it - writes appeared to work when read on the same connection but were silently rolled back when the connection closed. Code that relied on rolling back uncommitted db.execute() writes should use the new db.begin() method to open an explicit transaction first. The transaction model is documented in full at Transactions and saving your changes.

db.query() now executes its SQL as soon as it is called, rather than waiting until the returned generator is first iterated. Rows are still fetched lazily during iteration. SQL errors are now raised at the call site, statements such as INSERT ... RETURNING are executed and committed immediately without needing to iterate over their results, and passing a statement that returns no rows - previously a silent no-op - now raises a ValueError recommending db.execute() instead. A statement rejected this way is rolled back before the error is raised, so it has no effect on the database.

Python API validation errors now raise ValueError instead of AssertionError. Previously invalid arguments - such as create_table() with no columns, transform() on a table that does not exist, or passing both ignore=True and replace=True - were rejected using bare assert statements, which are silently skipped when Python runs with the -O flag. Code that caught AssertionError for these cases should catch ValueError instead.

table.upsert() and table.upsert_all() now raise PrimaryKeyRequired if a record is missing a value for any primary key column, or has a value of None for one. Previously such records - which can never match an existing row - were quietly inserted as brand new rows, or triggered a confusing KeyError after the insert had already taken place.

db.enable_wal() and db.disable_wal() now raise a sqlite_utils.db.TransactionError if called while a transaction is open. Previously they would silently commit the open transaction as a side effect of changing the journal mode, breaking the rollback guarantee of db.atomic() and of user-managed transactions.

The View class no longer has an enable_fts() method. It existed only to raise NotImplementedError, since full-text search is not supported for views - calling it now raises AttributeError instead, and the method no longer appears in the API reference. The sqlite-utils enable-fts command shows a clean error when pointed at a view.

The no-op -d/--detect-types flag has been removed from the insert and upsert commands. Type detection has been the default for CSV/TSV data since 4.0a1, so the flag did nothing - invocations using it should simply drop it. --no-detect-types remains available to disable detection.

Database() now raises a sqlite_utils.db.TransactionError if passed a connection created with the Python 3.12+ sqlite3.connect(..., autocommit=True) or autocommit=False options. commit() and rollback() behave differently on those connections, which previously caused every write made by the library to be silently discarded when the connection closed.

Everything else:

Fixed a bug where table.delete_where(), table.optimize() and table.rebuild_fts() did not commit their changes, leaving the connection inside an open transaction. Their work - and any subsequent writes - could then be silently rolled back when the connection was closed. All three now use db.atomic(), consistent with the other write methods.

The sqlite-utils drop-table command now refuses to drop a view, and drop-view refuses to drop a table. Previously each would silently drop the wrong type of object if the name matched. Both now exit with an error suggesting the correct command to use.

Migrations applied by the new migrations system now run inside a transaction, together with the record of the migration having been applied. If a migration raises an exception its changes are rolled back and it stays pending, so it can be safely re-applied after the error is fixed. Migrations that cannot run inside a transaction, such as those executing VACUUM, can opt out using @migrations(transactional=False) - see Migrations and transactions.

table.upsert() and table.upsert_all() now detect the primary key or compound primary key of an existing table, so the pk= argument is no longer required when upserting into a table that already has a primary key.

db.table(table_name).insert({}) can now be used to insert a row consisting entirely of default values into an existing table, using INSERT INTO ... DEFAULT VALUES. (#759)

Improvements to the sqlite-utils migrate command: --stop-before values that do not match any known migration are now an error instead of being silently ignored, --stop-before now works correctly with migration files that still use the older sqlite_migrate.Migrations class, and --list is now a read-only operation that no longer creates the database file or the migrations tracking table. migrations.applied() now returns migrations in the order they were applied.

New db.begin(), db.commit() and db.rollback() methods for taking manual control of transactions, as an alternative to the db.atomic() context manager.

New documentation: Transactions and saving your changes describes how transactions work and when changes are committed, and a new Upgrading page details the changes needed to move between major versions.

Tags: projects, sqlite, ai, sqlite-utils, annotated-release-notes, generative-ai, llms, anthropic, claude, llm-pricing, coding-agents, claude-code, agentic-engineering, gpt, claude-mythos-fable

llm-coding-agent 0.1a0

2026-07-02T19:33:12+00:00

Release: llm-coding-agent 0.1a0

Another Fable 5 experiment. Now that my LLM library has evolved into more of an agent framework it's time to see what a simple coding agent would look like built on it.

I started a new Python library using my python-lib-template-repository GitHub template repository, then ran these two prompts (here's the Claude Code for web transcript):

Write a spec.md for this project - it will depend on the latest “llm” alpha from PyPI and implement a Claude code style coding agent complete with tools for reading and editing files and executing commands

Then:

Commit the spec, then build it using red/green TDD in a series of sensible commits (each with passing tests and updated docs) - occasionally manually test it using the OpenAI API key in your environment

Here's the spec, the resulting README file, and the sequence of commits.

I've shipped a slop-alpha to PyPI, so you can run the new agent like this:

uvx --prerelease=allow --with llm-coding-agent llm code

It's pretty good for a first attempt! Here's the (Fable-authored) README, which lists recipes like llm code --yolo and llm code --allow "pytest*" --allow "git diff*".

It also presents a Python API based around a CodingAgent(model="gpt-5.5", root="/path", approve=True).run("Fix the failing test in tests/test_parser.py") class which I didn't ask for but I'm delighted to see implemented.

Here's the suite of tools it implemented, listed using uvx ... llm tools:

CodingTools_edit_file(path: str, old_string: str, new_string: str, replace_all: bool = False) -> str

Replace an exact string in a file.

old_string must match the file contents exactly (including whitespace) and must identify a unique location unless replace_all is true. Returns a diff of the change so it can be verified.

CodingTools_execute_command(command: str, timeout: int = 120) -> str

Run a shell command in the session root directory.

Returns combined stdout and stderr followed by an Exit code line. timeout is in seconds (maximum 600); on timeout the whole process tree is killed.

CodingTools_list_files(pattern: str = '**/*', path: str = '.') -> str

List files matching a glob pattern, newest first.

Skips hidden directories, node_modules, __pycache__ and (in a git repository) anything covered by .gitignore. Returns at most 200 paths relative to the searched directory.

CodingTools_read_file(path: str, offset: int = 0, limit: int = 2000) -> str

Read a text file, returning numbered lines like cat -n.

Paths are relative to the session root. Use offset (0-based first line) and limit (max lines) to page through files too large to read in one call.

CodingTools_search_files(pattern: str, path: str = '.', glob: str = None, max_results: int = 100) -> str

Search file contents for a regular expression.

Returns matches as path:line_number:line, capped at max_results. Use glob (e.g. "*.py") to restrict which files are searched.

CodingTools_write_file(path: str, content: str) -> str

Create or overwrite a file with the given content.

Parent directories are created as needed. Prefer edit_file for modifying existing files.

I tried it out by running llm code --yolo and then prompting:

mkdir /tmp/demo and then in that folder create a simple swiftui CLI app for telling the time in ascii art

Here's the transcript, in which GPT-5.5 reasoning notes that "SwiftUI isn't suitable for a true CLI" and then builds an app that outputs this on swift run AsciiTime:

      █    █████         ████     █             █     ███   
     ██    █        █        █   ██      █     ██    █   █  
      █    ████           ███     █             █       █   
      █        █    █        █    █      █      █      █    
     ███   ████          ████    ███           ███   █████

Tags: projects, ai, generative-ai, llm, llm-tool-use, coding-agents, claude-code, claude-mythos-fable

Have your agent record video demos of its work with shot-scraper video

2026-06-30T16:54:26+00:00

shot-scraper video is a new command introduced in today's shot-scraper 1.10 release which accepts a storyboard.yml file defining a routine to run against a web application and uses Playwright to record a video of that routine. I've written before about the importance of having coding agents produce demos of their work; this is my latest attempt at enabling them to do that.

Here's an example video created using shot-scraper video, exercising a still in development feature adding the ability to create new tables in Datasette from pasted CSV, TSV or JSON data:

That video was created by running this command:

shot-scraper video datasette-bulk-insert-storyboard.yml \
  --auth datasette-demo-auth.json --mp4

(That --auth JSON file contains a cookie, as described here in the documentation.)

Here's the datasette-bulk-insert-storyboard.yml file:

output: /tmp/datasette-bulk-insert-demo.webm
server:
  - uv
  - --directory
  - /Users/simon/Dropbox/dev/datasette
  - run
  - datasette
  - -p
  - 6419
  - --root
  - --secret
  - "1"
  - /tmp/demo.db
url: http://127.0.0.1:6419/demo/tasks
viewport:
  width: 1280
  height: 720
cursor: true
wait_for: 'button[data-table-action="insert-row"]'
javascript: |
  (() => {
    let clipboardText = "";
    Object.defineProperty(navigator, "clipboard", {
      configurable: true,
      get: () => ({
        writeText: async (text) => {
          clipboardText = String(text);
        },
        readText: async () => clipboardText,
      }),
    });
  })();
scenes:
  - name: Bulk insert existing table rows
    do:
      - pause: 0.8
      - click: 'button[data-table-action="insert-row"]'
      - wait_for: "#row-edit-dialog[open]"
      - pause: 0.5
      - click: ".row-edit-bulk-insert"
      - wait_for: ".row-edit-bulk-textarea"
      - pause: 0.5
      - click: ".row-edit-copy-template"
      - wait_for: "text=Copied"
      - pause: 0.8
      - fill:
          into: ".row-edit-bulk-textarea"
          text: |
            title,owner,status,priority,notes
            Prepare release video,Ana,doing,1,Recorded with shot-scraper
            Check pasted CSV import,Ben,review,3,Previewed before inserting
            Share the branch demo,Chen,queued,2,Bulk insert creates three rows
      - pause: 0.8
      - click: ".row-edit-save"
      - wait_for: "text=Previewing 3 rows."
      - pause: 1.2
      - click: ".row-edit-save"
      - wait_for: "text=3 rows inserted."
      - pause: 1.0
      - click: ".row-edit-cancel"
      - wait_for: "text=Prepare release video"
      - pause: 1.0
  - name: Create a table from pasted CSV
    open: http://127.0.0.1:6419/demo
    wait_for: 'details.actions-menu-links summary'
    do:
      - pause: 0.8
      - click: 'details.actions-menu-links summary'
      - click: 'button[data-database-action="create-table"]'
      - wait_for: "#table-create-dialog[open]"
      - pause: 0.5
      - fill:
          into: ".table-create-table-name"
          text: "launch_metrics"
      - click: ".table-create-from-data"
      - wait_for: ".table-create-data-textarea"
      - pause: 0.5
      - fill:
          into: ".table-create-data-textarea"
          text: |
            metric_id,name,score,recorded_on
            m001,Activation rate,87.5,2026-06-29
            m002,Retention check,72.25,2026-06-30
            m003,CSV import health,95,2026-07-01
      - pause: 0.8
      - click: ".table-create-save"
      - wait_for: "text=Previewing 3 rows."
      - pause: 1.2
      - click: ".table-create-save"
      - wait_for_url: "**/demo/launch_metrics"
      - wait_for: "text=Activation rate"
      - pause: 1.2

The video command documentation includes simpler examples, but for the purpose of this post I thought I'd go with something more comprehensive.

That demo YAML storyboard was constructed entirely by GPT-5.5 xhigh running in Codex Desktop, using the following prompt run inside my ~/dev/datasette checkout of this branch:

Review the changes on this branch.

cd to ~/dev/shot-scraper and run the command "uv run shot-scraper video --help"

Now use that new video command to record a video demo of the new features from this branch, including running a "uv run datasette -p 6419 --root --secret 1 /tmp/demo.db" development server so you can record the video against a demo DB that you first create.

Now that I've released the feature the prompt could say "run uvx shot-scraper video --help" instead and it should achieve the same result.

I really like this pattern where the --help output for a command provides enough detail that a coding agent can use it - it works kind of like bundling a SKILL.md file directly inside the tool. I used the same pattern for showboat and rodney.

How I built this

shot-scraper video started as an experimental prototype. shot-scraper is built on top of Playwright, and the key feature it needed was for Playwright to be able to record video of browser sessions with enough control to create the desired demo.

I first tried this a few years ago and found that the Playwright-produced videos included additional chrome that was useful for debugging a test failure but unwanted for a product demo.

They fixed that a while ago, but there were still some minor blockers. In particular I was getting a few white frames at the start of the videos, since the recording mechanism kicked in before the first URL was loaded by the browser.

Playwright 1.59 added a new screencast mechanism providing much more finely grained control over video recording. This was very nearly what I needed, but the resulting videos were fixed at 800px wide.

I found a landed PR fixing that but it wasn't yet in a release. Then yesterday they shipped it in playwright-python 1.61.0 and I was finally unblocked to finish implementing the feature!

The code itself was all written by GPT-5.5 xhigh in Codex Desktop. I had it write the documentation as well which gave me a very useful frame for reviewing the design - much of the iteration on the feature came from reviewing that documentation, spotting things that were redundant, inconsistent or confusing, and requesting (or dictating) a better design.

The YAML format itself was mostly defined by the coding agent. I had it use Pydantic to both define and validate the format, partly to make the design easier to review.

This is a great example of the kind of feature that I almost certainly wouldn't have taken on without coding agent support. I filed the original issue in February 2024, and had difficulty finding the necessary time to solve this in amongst all of my other projects.

Tags: projects, python, yaml, ai, datasette, playwright, shot-scraper, generative-ai, llms, pydantic, coding-agents, agentic-engineering

simonw/browser-compat-db

2026-06-24T23:59:03+00:00

simonw/browser-compat-db

Inspired by Mozilla's new MDN MCP service - source code here - I decided to try converting their comprehensive mdn/browser-compat-data repository full of browser compatibility data into a SQLite database.

This new GitHub repo includes a Claude Code for web (Opus 4.8) generated script for doing that using sqlite-utils.

I wanted the resulting ~66MB SQLite database to be available via the GitHub CDN with open CORS headers. GitHub releases don't have those, but any file stored in a regular GitHub repository does - so I had Codex Desktop (GPT-5.5) build a GitHub Actions workflow that builds the database and then force-pushes it to a db "orphan" branch.

You can download the resulting database from here, and since it's hosted with open CORS headers you can also explore it with Datasette Lite.

Tags: github, mozilla, projects, github-actions, datasette-lite, ai-assisted-programming, model-context-protocol, mdn

sqlite-utils 4.0rc1 adds migrations and nested transactions

2026-06-21T23:35:47+00:00

sqlite-utils is my combined Python library and CLI tool for working with SQLite databases. It provides an extensive set of higher-level operations on top of Python's default sqlite3 package, including support for complex table transformations, automatic table creation from JSON data and a whole lot more.

I released sqlite-utils 4.0rc1, the first release candidate for sqlite-utils v4. The major version bump indicates some (minor) backwards incompatible changes, so I'm interested in having people try this out before I commit to a stable release.

New feature: migrations

There are two significant new features in this RC compared to the previous 4.0 alphas.

The first is support for database migrations. This isn't a completely new implementation - it's a slightly modified port of the sqlite-migrate package I released a few years ago. I think that package has proved itself over time, so I'm now ready to bundle it with sqlite-utils directly.

Here's what a set of migrations in a migrations.py file looks like:

from sqlite_utils import Database, Migrations

migrations = Migrations("creatures")

@migrations()
def create_table(db):
    db["creatures"].create(
        {"id": int, "name": str, "species": str},
        pk="id",
    )

@migrations()
def add_weight(db):
    db["creatures"].add_column("weight", float)

This defines a set of two migrations, one creating the creatures table and another adding a column to it.

You can then run those migrations either using Python:

db = Database("creatures.db")
migrations.apply(db)

Or with the command-line migrate command:

sqlite-utils migrate creatures.db migrations.py

The system is deliberately small: it doesn't provide reverse migrations, so any mistakes you make should be fixed by deploying a fresh migration to undo them.

Its predecessor has been used by LLM and various other projects for several years, so I'm confident that the design is stable and works well.

The new migrations feature is documented here.

New feature: db.atomic() transactions

This feature is a lot less exercised than migrations, so it deserves more attention from testers.

Previously, sqlite-utils mostly left transaction management up to its users, via a with db.conn: construct that reused the sqlite3 mechanism directly.

SQLite supports nested transactions in the form of savepoints, so I wanted an abstraction that could make those as easy to use as possible.

I borrowed the terminology "atomic" from Django and Peewee. Here's what the new API looks like:

with db.atomic():
    db.table("dogs").insert({"id": 1, "name": "Cleo"}, pk="id")
    try:
        with db.atomic():
            db.table("dogs").insert({"id": 2, "name": "Pancakes"})
            raise ValueError("skip this one")
    except ValueError:
        pass
    db.table("dogs").insert({"id": 3, "name": "Marnie"})

Backwards incompatible changes

The backwards incompatible changes in v4 were described in the alpha release notes. For 4.0a0:

Upsert operations now use SQLite's INSERT ... ON CONFLICT SET syntax on all SQLite versions later than 3.23.1. This is a very slight breaking change for apps that depend on the previous INSERT OR IGNORE followed by UPDATE behavior. (#652)

Python library users can opt-in to the previous implementation by passing use_old_upsert=True to the Database() constructor, see Alternative upserts using INSERT OR IGNORE.

Dropped support for Python 3.8, added support for Python 3.13. (#646)

sqlite-utils tui is now provided by the sqlite-utils-tui plugin. (#648)

Test suite now also runs against SQLite 3.23.1, the last version (from 2018-04-10) before the new INSERT ... ON CONFLICT SET syntax was added. (#654)

And for 4.0a1:

Breaking change: The db.table(table_name) method now only works with tables. To access a SQL view use db.view(view_name) instead. (#657)

The table.insert_all() and table.upsert_all() methods can now accept an iterator of lists or tuples as an alternative to dictionaries. The first item should be a list/tuple of column names. See Inserting data from a list or tuple iterator for details. (#672)

Breaking change: The default floating point column type has been changed from FLOAT to REAL, which is the correct SQLite type for floating point values. This affects auto-detected columns when inserting data. (#645)

Now uses pyproject.toml in place of setup.py for packaging. (#675)

Tables in the Python API now do a much better job of remembering the primary key and other schema details from when they were first created. (#655)

Breaking change: The table.convert() and sqlite-utils convert mechanisms no longer skip values that evaluate to False. Previously the --skip-false option was needed, this has been removed. (#542)

Breaking change: Tables created by this library now wrap table and column names in "double-quotes" in the schema. Previously they would use [square-braces]. (#677)

The --functions CLI argument now accepts a path to a Python file in addition to accepting a string full of Python code. It can also now be specified multiple times. (#659)

Breaking change: Type detection is now the default behavior for the insert and upsert CLI commands when importing CSV or TSV data. Previously all columns were treated as TEXT unless the --detect-types flag was passed. Use the new --no-detect-types flag to restore the old behavior. The SQLITE_UTILS_DETECT_TYPES environment variable has been removed. (#679)

Try it out

You can install the new RC like this:

pip install sqlite-utils==4.0rc1

Or try the CLI version directly with uvx like this:

uvx --with sqlite-utils==4.0rc1 sqlite-utils --help

Come chat with us about it in the sqlite-utils Discord channel, or file any bugs in GitHub Issues.

Tags: schema-migrations, projects, sqlite, sqlite-utils, annotated-release-notes

Datasette Apps: Host custom HTML applications inside Datasette

2026-06-18T23:58:38+00:00

Today we launched a new plugin for Datasette, datasette-apps, with this launch announcement post on the Datasette project blog. That post has the what, but I'm going to expand on that a little bit here to provide the why.

The TL;DR

Datasette Apps are self-contained HTML+JavaScript applications that run in a tightly constrained <iframe> sandbox hosted on your Datasette application. They can use JavaScript to run read-only SQL queries against data in Datasette, and can run write queries too if you configure them with some stored queries.

Here's a very simple example and a more complex custom timeline example - the latter looks like this:

Apps are allowed to run JavaScript and render HTML and CSS. They are limited in terms of access - the <iframe sandbox="allow-scripts allow-forms"> they run in prevents them from accessing cookies or localStorage and they also have an injected CSP header (thanks to this research) which prevents them from making HTTP requests to outside hosts, preventing a malicious or buggy app from exfiltrating private data.

Datasette Apps started out as my attempt at building a Claude Artifacts mechanism for Datasette Agent, but I quickly realised that the sandboxed pattern is interesting for way more than just adding custom apps in a chat interface and promoted it to its own top-level concept within the Datasette ecosystem.

They're also a fun way to turn my multi-year experiment in vibe-coded HTML tools into a core feature of my main project!

You can try out Datasette Apps by signing in with GitHub to the agent.datasette.io demo instance.

Why build this?

Since the very first release, Datasette has offered a flexible backend for creating custom HTML apps via its JSON API.

One of my earliest Datasette projects was an internal search engine for documentation when I worked at Eventbrite - it worked by importing documents from different systems into SQLite on a cron and then serving them through a Datasette instance with a custom HTML+JavaScript search interface that directly queried the Datasette API.

I had client-side JavaScript constructing SQL queries, which originally was intended as an engineering joke but turned out to be a really productive way of iterating on the app!

That project, combined with my experience building my HTML tools collection and my experiments with Claude Artifacts, has convinced me that adding a Datasette-style backend to a self-contained HTML frontend is an astonishingly powerful combination.

Imagine how much more useful Claude Artifacts could be if they had access to a persistent relational database. That's what I'm building with Datasette Apps!

Neat ideas in Datasette Apps

Here are a few of the ideas and patterns I've figured out building this which I think have staying power.

`<iframe sandbox="allow-scripts" srcdoc="...">` + `<meta http-equiv="Content-Security-Policy" content="default-src 'none'; script-src 'unsafe-inline'; style-src 'unsafe-inline'; img-src data: blob:;">`

This is the magic combination that makes Datasette Apps feasible in the first place. I need to run untrusted HTML and JavaScript on a highly sensitive domain - an authenticated Datasette instance can contain all sorts of private data. The sandbox= attribute lets me run that untrusted code in a way that cannot interact with the parent application - it can't read the DOM, or access cookies, or steal secrets from localStorage. It can however use fetch() and friends to load content (or exfiltrate data) from other domains. But... it turns out if you start an HTML page with a <meta http-equiv="Content-Security-Policy"> header you can set additional policies that lock down access to other domains. I was worried that malicious JavaScript would be able to update or remove that header but it turns out that doesn't work - once set, the CSP policy is immutable for the content of that frame.

Locked down APIs with `postMessage()` and `MessageChannel()`

Having locked down those iframes to the point that they couldn't do anything interesting at all, the challenge was to open them back again such that they could run an allow-list of operations, starting with read-only SQL queries against specified databases.

I built the first version of this with postMessage(), which allows a child iframe to send messages to the parent window. I created a simple protocol for requesting that the parent run a SQL query - the parent could then verify it was against an allow-listed database before executing it.

One of the LLM tools, I think it was GPT-5.5, suggested that postMessage() on its own can be exploited if the iframe somehow loads additional code from an untrusted domain. I don't think that applies to Datasette Apps, but I also believe in defense in depth, so I had GPT-5.5 help me port to a MessageChannel() based transport instead.

MessageChannel() has the advantage that if a page navigates to somewhere else the channel closes automatically, removing any chance of executing commands sent from an untrusted external page.

Visible logs, for queries and errors

If you navigate to the timeline demo and search for the string usercontent you'll pull in some search results that embed images from the user-images.githubusercontent.com domain. This domain is not in the CSP allow-list, so it trips an error.

Those errors are captured and transmitted back to the parent frame, where they can be displayed in a useful error log. This is meant to make hacking on apps more productive by surfacing otherwise-invisible problems.

I built an experiment demonstrating that you can even turn this into a one-click-to-allow mechanism for building the CSP allow-list based on what breaks, but I haven't integrated that idea into datasette-apps just yet.

SQL queries are also visibly logged - scroll to the bottom of the timeline page to see that in action.

Stored queries for write operations

I want apps to be able to conditionally write to the database, but this is an even more dangerous proposition than SQL reads!

My solution involves Datasette's stored queries feature, rebranded from "canned queries" and given a major upgrade in the recent Datasette 1.0a31 - work that was directly inspired by Datasette Apps.

Users can create a stored write query that performs an insert or update, then allow-list that specific query for an app to use. Usage from code inside an app looks like this:

const result = await datasette.storedQuery("todos", "add_todo", {
  title: "Buy milk",
  due_date: "2026-06-20",
  priority: "high",
  completed: false
});

I'm only just beginning to explore the possibilities this unlocks myself, but my goal is to support full read-write applications built safely as Datasette Apps.

Copy and paste a prompt to build an app

The Datasette Apps plugin has no dependency on LLMs at all, but these self-contained apps are the perfect shape to be written by a modern LLM.

The create app form includes a copyable prompt at the end. This prompt has everything a model needs to know to build a new app, including the schema of any selected databases.

This means you can click "copy", paste it into ChatGPT or Claude or Gemini, tell it what you need, and there's a good chance the model will spit out the code necessary to build the app.

If you have Datasette Agent installed your AI assistant will also gain tools to both create new apps and edit existing ones, Claude Artifacts style.

Built with so much AI assistance

Datasette Apps started life back in April as datasette-agent-artifacts, a plugin I have since renamed to datasette-agent-edit keeping only its editing tools. I built that as one of the first plugins for Datasette Agent, to help get the plugin hooks into the right shape. That first prototype was mainly built using Claude Opus 4.6 in Claude Code.

When I switched track to Datasette Apps I started with a plan constructed using Codex Desktop and GPT-5.5 xhigh, based on extensive dialog and feeding in both datasette-agent-artifacts and other prototypes I had built.

Most of the work that followed stuck with Codex, but in the few short days that we had access to Claude Fable 5 I had it run a security evaluation of the product (an ability that would get it banned by the US government shortly afterwards) and it found a very real problem.

I was allowing users to allow-list CSP hosts for their apps, but Fable pointed out the following attack:

A less privileged user with create-app permission creates an app that queries SQLite for all available tables and selects and exfiltrates all of the data to a host they had allow-listed via CSP.
They then trick an administrator user with access to private data into visiting their app.
... and the app can now run queries as that user and steal their private data!

That's clearly unacceptable. I fixed it by restricting the ability to allow-list any domain to a new apps-set-csp permission, which is intended just for trusted staff. Site administrators can also configure Datasette with a list of allowed_csp_origins, which regular users can then select. This means you can do things like allow cdnjs.cloudflare.com and your users will be able to build apps that load extra JavaScript libraries from the cdnjs CDN.

I've reviewed Datasette Apps extremely closely, especially the security-adjacent parts of it. The critical sandbox and CSP configuration are based on multiple AI-assisted prototypes and tests.

It's looking good so far

I'm really pleased with this initial release.

Datasette is growing beyond its origins as an application for serving read-only data into a much richer ecosystem of tools for doing useful things with that data once it has been collected.

Datasette's roots are in data journalism. I've always been interested in the question of what comes next after a journalist gets their hands on a giant dump of data about the world. Datasette supports exploring and publishing it. Datasette Agent adds interrogating it with AI assistance. Now Datasette Apps expands that to building custom interfaces and visualizations to help unlock the stories that are hidden within.

Tags: iframes, javascript, projects, sandboxing, ai, datasette, generative-ai, llms, ai-assisted-programming, content-security-policy, datasette-apps

datasette 1.0a34

2026-06-16T21:31:24+00:00

Release: datasette 1.0a34

Quoting the release notes:

The big feature in this alpha is tools to insert, edit and delete rows within the Datasette interface. These features are available on table pages, and edit and delete are also available as action items on the row page.

The inspiration for this feature - which is long overdue - was Datasette Agent. I added SQL write support to that the other day which highlighted how absurd it was that you could insert and edit ties via the chat interface but not in the regular Datasette UI!

Tags: projects, datasette, annotated-release-notes

datasette-agent 0.3a0

2026-06-15T17:19:27+00:00

Release: datasette-agent 0.3a0

New tool, execute_write_sql, which requests user approval and then writes to a database - taking user permissions into account. #27

I added a mechanism for asking user approval in datasette agent 0.2a0. The new execute_write_sql tool can now prompt the user for all kinds of useful operations. Here's an example where I add some pelican sightings to my pelican_sightings table:

The new version also enhances the datasette agent chat terminal mode to support approvals, and adds several new options including --unsafe mode for auto-approving them:

datasette agent chat can execute tools that require user approval. #30

Three new options for datasette agent chat - --root to run as root, --yes to approve all ask user questions, and --unsafe for both.

Tools can now provide plain text alternatives to HTML, for display in the datasette agent chat CLI. #31

The datasette agent chat content.db -m gpt-5.5 --unsafe command can now be used to chat directly with a specific database and directly modify it through prompts like "create a notes table", "add a note about X" etc.

Tags: projects, ai, datasette, annotated-release-notes, generative-ai, llms, llm-tool-use, datasette-agent

datasette 1.0a33

2026-06-11T15:26:49+00:00

Release: datasette 1.0a33

This alpha is a significant step on the road to a stable 1.0, finally extending the ?_extra= pattern I introduced in Datasette 1.0a3 to cover queries and rows in addition to tables. That pattern is also now documented!

I wrote a whole lot more about the new release on the Datasette project blog: Datasette 1.0a33 with JSON extras in the API.

Because API explorer tools are almost free to build now I had Claude Fable 5 in Claude Code (for the plan) and GPT-5.5 xhigh in Codex Desktop (for the implementation) build me this custom extras API explorer to help demonstrate the feature:

Tags: projects, datasette, annotated-release-notes, ai-assisted-programming

asyncinject 0.7

2026-06-11T06:28:09+00:00

Release: asyncinject 0.7

I built this utility library to support an asyncio dependency injection pattern a few years ago. I was using it with Datasette and Claude Fable 5 spotted some bugs in the dependency which it then fixed for me. It's a very proactive model!

Tags: async, projects, python, claude-mythos-fable

llm 0.32a3

2026-06-09T22:27:03+00:00

Release: llm 0.32a3

Almost entirely written by the new Claude Fable 5, see my write-up for more details.

Tags: projects, ai, generative-ai, llms, llm, claude-mythos-fable

datasette 1.0a31

2026-05-29T03:32:02+00:00

Release: datasette 1.0a31

Another significant alpha release, with two new headline features.

Datasette now offers users with the necessary permissions the ability to both execute write queries against their database and to save stored queries (renamed from "canned queries") both privately and for use by other members of their Datasette instance.

There's more detail in SQL write queries and stored queries in Datasette 1.0a31 on the Datasette blog, which now has three posts introducing new features since the blog launched two weeks ago.

Here's an animated demo from the blog post showing how the new execute query interface lets people get started with templated insert/update/delete queries from tables they have permission to edit:

Tags: projects, sql, sqlite, datasette, annotated-release-notes

datasette 1.0a30

2026-05-24T23:52:37+00:00

Release: datasette 1.0a30

The big new feature in this alpha is a new customizable "Jump to..." menu, described in detail in The extensible "Jump to" menu in Datasette 1.0a30 on the Datasette blog. You can try it out by hitting / on latest.datasette.io - it looks like this:

The new jump_items_sql() plugin hook allows plugins to add their own items to the set that's searched by the plugin.

Tags: projects, datasette, annotated-release-notes

Datasette Agent

2026-05-21T19:52:19+00:00

We just announced the first release of Datasette Agent, a new extensible AI assistant for Datasette. I've been working on my LLM Python library for just over three years now, and Datasette Agent represents the moment that LLM and Datasette finally come together. I'm really excited about it!

Datasette Agent provides a conversational interface for asking questions of the data you have stored in Datasette. Add the datasette-agent-charts plugin and it can generate charts of your data as well.

The demo

The announcement post (on the new Datasette project blog) includes this demo video:

I recorded the video against the new agent.datasette.io live demo instance, which runs Datasette Agent against example databases including the classic global-power-plants by WRI, and a copy of the Datasette backup of my blog.

The live demo runs on Gemini 3.1 Flash-Lite - it's cheap, fast and has no trouble writing SQLite queries.

A question I asked in the demo was:

when did Simon most recently see a pelican?

Which ran this SQL query:

SELECT
  title, commentary, created
FROM
  blog_beat
WHERE
  beat_type = 'sighting'
  AND (title LIKE '%pelican%' OR commentary LIKE '%pelican%')
ORDER BY
  created DESC
LIMIT
  5

And replied:

The most recent sighting of a pelican by Simon was recorded on May 20, 2026.

The observation included a California Brown Pelican, along with a Common Loon, Canada Goose, Striped Shore Crab, and a California Sea Lion.

Here's that sighting on my blog, and the Markdown export of the full conversation transcript.

The plugins

My favorite feature of Datasette Agent is that, like the rest of Datasette, it's extensible using plugins.

We've shipped three plugins so far:

datasette-agent-charts, shown in the video, adds charts to Datasette Agent, powered by Observable Plot.
datasette-agent-openai-imagegen adds an image generation tool to Datasette Agent using ChatGPT Images 2.0.
datasette-agent-sprites provides tools for executing code in a Fly Sprites persistent sandbox.

Building plugins is really fun. I have a bunch more prototypes that aren't quite alpha-quality yet.

Claude Code and OpenAI Codex are both proving excellent at writing plugins - just point them at a checkout of the datasette-agent repo for reference and tell them what you want to build!

Running it against local models

I've also been having fun running the new plugin against local models. Here's a uv one-liner to run the plugin against gemma-4-26b-a4b in LM Studio on a Mac:

uvx --prerelease=allow \
  --with datasette-agent --with llm-lmstudio \
  datasette --internal internal.db --root \
  -s plugins.datasette-llm.default_model lmstudio/google/gemma-4-26b-a4b \
  data.db

Datasette Agent needs reliable tool calls and the ability for a model to produce SQL queries that run against SQLite. The open weight models released in the past six months are increasingly able to handle that.

What's next

Datasette Agent opens up so many opportunities for the LLM and Datasette ecosystem in general.

It's already informed the major LLM 0.32a0 refactor which I'm nearly ready to roll into a stable release, maybe with some additional "LLM agent" abstractions extracte from Datasette Agent itself.

I've been exploring my own take on the Claude Artifacts, which is shaping up nicely as a plugin.

I'm excited to use Datasette Agent to build my own Claw - a personal AI assistant built around data imported from different parts of my digital life, which is a neat excuse to revisit my older Dogsheep family of tools.

We'll also be rolling out Datasette Agent for users of Datasette Cloud.

Join our #datasette-agent Discord channel if you'd like to talk about the project.

Tags: projects, sqlite, ai, datasette, generative-ai, llms, llm, uv, datasette-agent

inaturalist-clumper 0.1

2026-05-15T23:53:11+00:00

Release: inaturalist-clumper 0.1

Part of the infrastructure I use for publishing my iNaturalist sightings on my blog. I've been running this in production for a few weeks now, inspiring some iterations on how it works, so I decided to ship a 0.1 release.

You can see an example of the output in this JSON file.

Tags: projects, inaturalist

datasette 1.0a29

2026-05-12T23:41:06+00:00

Release: datasette 1.0a29

New TokenRestrictions.abbreviated(datasette) utility method for creating "_r" dictionaries. #2695

Table headers and column options are now visible even if a table contains zero rows. #2701

Fixed bug with display of column actions dialog on Mobile Safari. #2708

Fixed bug where tests could crash with a segfault due to a race condition between Datasette.close() and Database.close(). #2709

That segfault bug was gnarly. I added a mechanism to Datasette recently that would automatically close connections at the end of each test, but it turned out that introduced a race condition where an in-flight query could sometimes be executing in a thread against a connection while it was being closed. I ended up solving that by having Codex CLI (with GPT-5.5 xhigh) create a minimal Dockerfile that recreated the bug.

Tags: projects, datasette

llm 0.32a2

2026-05-12T17:45:07+00:00

Release: llm 0.32a2

A bunch of useful stuff in this LLM alpha, but the most important detail is this one:

Most reasoning-capable OpenAI models now use the /v1/responses endpoint instead of /v1/chat/completions. This enables interleaved reasoning across tool calls for GPT-5 class models. #1435

This means you can now see the summarized reasoning tokens when you run prompts against an OpenAI model, displayed in a different color to standard error. Use the -R or --hide-reasoning flags if you don't want to see that.

Tags: projects, ai, annotated-release-notes, openai, generative-ai, llms, llm

LLM 0.32a0 is a major backwards-compatible refactor

2026-04-29T19:01:47+00:00

I just released LLM 0.32a0, an alpha release of my LLM Python library and CLI tool for accessing LLMs, with some consequential changes that I've been working towards for quite a while.

Previous versions of LLM modeled the world in terms of prompts and responses. Send the model a text prompt, get back a text response.

import llm

model = llm.get_model("gpt-5.5")
response = model.prompt("Capital of France?")
print(response.text())

This made sense when I started working on the library back in April 2023. A lot has changed since then!

LLM provides an abstraction over thousands of different models via its plugin system. The original abstraction - of text input that returns text output - was no longer able to represent everything I needed it to.

Over time LLM itself has grown attachments to handle image, audio, and video input, then schemas for outputting structured JSON, then tools for executing tool calls. Meanwhile LLMs kept evolving, adding reasoning support and the ability to return images and all kinds of other interesting capabilities.

LLM needs to evolve to better handle the diversity of input and output types that can be processed by today's frontier models.

The 0.32a0 alpha has two key changes: model inputs can be represented as a sequence of messages, and model responses can be composed of a stream of differently typed parts.

Prompts as a sequence of messages

LLMs accept input as text, but ever since ChatGPT demonstrated the value of a two-way conversational interface, the most common way to prompt them has been to treat that input as a sequence of conversational turns.

The first turn might look like this:

user: Capital of France?
assistant:

(The model then gets to fill out the reply from the assistant.)

But each subsequent turn needs to replay the entire conversation up to that point, as a sort of screenplay:

user: Capital of France?
assistant: Paris
user: Germany?
assistant:

Most of the JSON APIs from the major vendors follow this pattern. Here's what the above looks like using the OpenAI chat completions API, which has been widely imitated by other providers:

curl https://api.openai.com/v1/chat/completions \
  -H "Authorization: Bearer $OPENAI_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "model": "gpt-5.5",
    "messages": [
      {
        "role": "user",
        "content": "Capital of France?"
      },
      {
        "role": "assistant",
        "content": "Paris"
      },
      {
        "role": "user",
        "content": "Germany?"
      }
    ]
  }'

Prior to 0.32, LLM modeled these as conversations:

model = llm.get_model("gpt-5.5")

conversation = model.conversation()
r1 = conversation.prompt("Capital of France?")
print(r1.text())
# Outputs "Paris"

r2 = conversation.prompt("Germany?")
print(r2.text())
# Outputs "Berlin"

This worked if you were building a conversation with the model from scratch, but it didn't provide a way to feed in a previous conversation from the start. This made tasks like building an emulation of the OpenAI chat completions API much harder than they should have been.

The llm CLI tool worked around this through a custom mechanism for persisting and inflating conversations using SQLite, but that never became a stable part of the LLM API - and there are many places you might want to use the Python library without committing to SQLite as the storage layer.

The new alpha now supports this:

import llm
from llm import user, assistant

model = llm.get_model("gpt-5.5")

response = model.prompt(messages=[
    user("Capital of France?"),
    assistant("Paris"),
    user("Germany?"),
])
print(response.text())

The llm.user() and llm.assistant() functions are new builder functions designed to be used within that messages=[] array.

The previous prompt= option still works, but LLM upgrades it to a single-item messages array behind the scenes.

You can also now reply to a response, as an alternative to building a conversation:

response2 = response.reply("How about Hungary?")
print(response2) # Default __str__() calls .text()

Streaming parts

The other major new interface in the alpha concerns streaming results back from a prompt.

Previously, LLM supported streaming like this:

response = model.prompt("Generate an SVG of a pelican riding a bicycle")
for chunk in response:
    print(chunk, end="")

Or this async variant:

import asyncio
import llm

model = llm.get_async_model("gpt-5.5")
response = model.prompt("Generate an SVG of a pelican riding a bicycle")

async def run():
    async for chunk in response:
        print(chunk, end="", flush=True)

asyncio.run(run())

Many of today's models return mixed types of content. A prompt run against Claude might return reasoning output, then text, then a JSON request for a tool call, then more text content.

Some models can even execute tools on the server-side, for example OpenAI's code interpreter tool or Anthropic's web search. This means the results from the model can combine text, tool calls, tool outputs and other formats.

Multi-modal output models are starting to emerge too, which can return images or even snippets of audio intermixed into that streaming response.

The new LLM alpha models these as a stream of typed message parts. Here's what that looks like as a Python API consumer:

import asyncio
import llm

model = llm.get_model("gpt-5.5")
prompt = "invent 3 cool dogs, first talk about your motivations"

def describe_dog(name: str, bio: str) -> str:
    """Record the name and biography of a hypothetical dog."""
    return f"{name}: {bio}"

def sync_example():
    response = model.prompt(
        prompt,
        tools=[describe_dog],
    )
    for event in response.stream_events():
        if event.type == "text":
            print(event.chunk, end="", flush=True)
        elif event.type == "tool_call_name":
            print(f"\nTool call: {event.chunk}(", end="", flush=True)
        elif event.type == "tool_call_args":
            print(event.chunk, end="", flush=True)

async def async_example():
    model = llm.get_async_model("gpt-5.5")
    response = model.prompt(
        prompt,
        tools=[describe_dog],
    )
    async for event in response.astream_events():
        if event.type == "text":
            print(event.chunk, end="", flush=True)
        elif event.type == "tool_call_name":
            print(f"\nTool call: {event.chunk}(", end="", flush=True)
        elif event.type == "tool_call_args":
            print(event.chunk, end="", flush=True)

sync_example()
asyncio.run(async_example())

Sample output (from just the first sync example):

My motivation: create three memorable dogs with distinct “cool” styles—one cinematic, one adventurous, and one charmingly chaotic—so each feels like they could star in their own story.
Tool call: describe_dog({"name": "Nova Jetpaw", "bio": "A sleek silver-gray whippet who wears tiny aviator goggles and loves sprinting along moonlit beaches. Nova is fearless, elegant, and rumored to outrun drones just for fun."}
Tool call: describe_dog({"name": "Mochi Thunderbark", "bio": "A fluffy corgi with a dramatic black-and-gold bandana and the confidence of a rock star. Mochi is short, loud, loyal, and leads a neighborhood 'security patrol' made entirely of squirrels."}
Tool call: describe_dog({"name": "Atlas Snowfang", "bio": "A massive white husky with ice-blue eyes and a backpack full of trail snacks. Atlas is calm, heroic, and always knows the way home—even during blizzards, fog, or confusing camping trips."}

At the end of the response you can call response.execute_tool_calls() to actually run the functions that were requested, or send a response.reply() to have those tools called and their return values sent back to the model:

print(response.reply("Tell me about the dogs"))

This new mechanism for streaming different token types means the CLI tool can now display "thinking" text in a different color from the text in the final response. The thinking text goes to stderr so it won't affect results that are piped into other tools.

This example uses Claude Sonnet 4.6 (with an updated streaming event version of the llm-anthropic plugin) as Anthropic's models return their reasoning text as part of the response:

llm -m claude-sonnet-4.6 'Think about 3 cool dogs then describe them' \
  -o thinking_display 1

You can suppress the output of reasoning tokens using the new -R/--no-reasoning flag. Surprisingly that ended up being the only CLI-facing change in this release.

A mechanism for serializing and deserializing responses

As mentioned earlier, LLM has quite inflexible code at the moment for persisting conversations to SQLite. I've added a new mechanism in 0.32a0 that should provide Python API users a way to roll their own alternative:

serializable = response.to_dict()
# serializable is a JSON-style dictionary
# store it anywhere you like, then inflate it:
response = Response.from_dict(serializable)

The dictionary this returns is actually a TypedDict defined in the new llm/serialization.py module.

What's next?

I'm releasing this as an alpha so I can upgrade various plugins and exercise the new design in real world environments for a few days. I expect the stable 0.32 release will be very similar to this alpha, unless alpha testing reveals some design flaw in the way I've put this all together.

There's one remaining large task: I'd like to redesign the SQLite logging system to better capture the more finely grained details that are returned by this new abstraction.

Ideally I'd like to model this as a graph, to best support situations like an OpenAI-style chat completions API where the same conversations are constantly extended and then repeated with every prompt. I want to be able to store those without duplicating them in the database.

I'm undecided as to whether that should be a feature in 0.32 or I should hold it for 0.33.

Tags: projects, python, ai, annotated-release-notes, generative-ai, llms, llm

Extract PDF text in your browser with LiteParse for the web

2026-04-23T21:54:24+00:00

LlamaIndex have a most excellent open source project called LiteParse, which provides a Node.js CLI tool for extracting text from PDFs. I got a version of LiteParse working entirely in the browser, using most of the same libraries that LiteParse uses to run in Node.js.

Spatial text parsing

Refreshingly, LiteParse doesn't use AI models to do what it does: it's good old-fashioned PDF parsing, falling back to Tesseract OCR (or other pluggable OCR engines) for PDFs that contain images of text rather than the text itself.

The hard problem that LiteParse solves is extracting text in a sensible order despite the infuriating vagaries of PDF layouts. They describe this as "spatial text parsing" - they use some very clever heuristics to detect things like multi-column layouts and group and return the text in a sensible linear flow.

The LiteParse documentation describes a pattern for implementing Visual Citations with Bounding Boxes. I really like this idea: being able to answer questions from a PDF and accompany those answers with cropped, highlighted images feels like a great way of increasing the credibility of answers from RAG-style Q&A.

LiteParse is provided as a pure CLI tool, designed to be used by agents. You run it like this:

npm i -g @llamaindex/liteparse
lit parse document.pdf

I explored its capabilities with Claude and quickly determined that there was no real reason it had to stay a CLI app: it's built on top of PDF.js and Tesseract.js, two libraries I've used for something similar in a browser in the past.

The only reason LiteParse didn't have a pure browser-based version is that nobody had built one yet...

Introducing LiteParse for the web

Visit https://simonw.github.io/liteparse/ to try out LiteParse against any PDF file, running entirely in your browser. Here's what that looks like:

The tool can work with or without running OCR, and can optionally display images for every page in the PDF further down the page.

Building it with Claude Code and Opus 4.7

The process of building this started in the regular Claude app on my iPhone. I wanted to try out LiteParse myself, so I started by uploading a random PDF I happened to have on my phone along with this prompt:

Clone https://github.com/run-llama/liteparse and try it against this file

Regular Claude chat can clone directly from GitHub these days, and while by default it can't access most of the internet from its container it can also install packages from PyPI and npm.

I often use this to try out new pieces of open source software on my phone - it's a quick way to exercise something without having to sit down with my laptop.

You can follow my full conversation in this shared Claude transcript. I asked a few follow-up questions about how it worked, and then asked:

Does this library run in a browser? Could it?

This gave me a thorough enough answer that I was convinced it was worth trying getting that to work for real. I opened up my laptop and switched to Claude Code.

I forked the original repo on GitHub, cloned a local copy, started a new web branch and pasted that last reply from Claude into a new file called notes.md. Then I told Claude Code:

Get this working as a web app. index.html, when loaded, should render an app that lets users open a PDF in their browser and select OCR or non-OCR mode and have this run. Read notes.md for initial research on this problem, then write out plan.md with your detailed implementation plan

I always like to start with a plan for this kind of project. Sometimes I'll use Claude's "planning mode", but in this case I knew I'd want the plan as an artifact in the repository so I told it to write plan.md directly.

This also means I can iterate on the plan with Claude. I noticed that Claude had decided to punt on generating screenshots of images in the PDF, and suggested we defer a "canvas-encode swap" to v2. I fixed that by prompting:

Update the plan to say we WILL do the canvas-encode swap so the screenshots thing works

After a few short follow-up prompts, here's the plan.md I thought was strong enough to implement.

I prompted:

build it.

And then mostly left Claude Code to its own devices, tinkered with some other projects, caught up on Duolingo and occasionally checked in to see how it was doing.

I added a few prompts to the queue as I was working. Those don't yet show up in my exported transcript, but it turns out running rg queue-operation --no-filename | grep enqueue | jq -r '.content' in the relevant ~/.claude/projects/ folder extracts them.

Here are the key follow-up prompts with some notes:

When you implement this use playwright and red/green TDD, plan that too - I've written more about red/green TDD here.
let's use PDF.js's own renderer (it was messing around with pdfium)
The final UI should include both the text and the pretty-printed JSON output, both of those in textareas and both with copy-to-clipboard buttons - it should also be mobile friendly - I had a new idea for how the UI should work
small commits along the way - see below
Make sure the index.html page includes a link back to https://github.com/run-llama/liteparse near the top of the page - it's important to credit your dependencies in a project like this!
View on GitHub → is bad copy because that's not the repo with this web app in, it's the web app for the underlying LiteParse library
Run OCR should be unchecked by default
When I try to parse a PDF in my browser I see 'Parse failed: undefined is not a function (near '...value of readableStream...') - it was testing with Playwright in Chrome, turned out there was a bug in Safari
... oh that is in safari but it works in chrome
When "Copy" is clicked the text should change to "Copied!" for 1.5s
[Image #1] Style the file input so that long filenames don't break things on Firefox like this - in fact add one of those drag-drop zone UIs which you can also click to select a file - dropping screenshots in of small UI glitches works surprisingly well
Tweak the drop zone such that the text is vertically centered, right now it is a bit closer to the top
it breaks in Safari on macOS, works in both Chrome and Firefox. On Safari I see "Parse failed: undefined is not a function (near '...value of readableStream...')" after I click the Parse button, when OCR is not checked - it still wasn't working in Safari...
works in safari now - but it fixed it pretty quickly once I pointed that out and it got Playwright working with that browser

I've started habitually asking for "small commits along the way" because it makes for code that's easier to understand or review later on, and I have an unproven hunch that it helps the agent work more effectively too - it's yet another encouragement towards planning and taking on one problem at a time.

While it was working I decided it would be nice to be able to interact with an in-progress version. I asked a separate Claude Code session against the same directory for tips on how to run it, and it told me to use npx vite. Running that started a development server with live-reloading, which meant I could instantly see the effect of each change it made on disk - and prompt with further requests for tweaks and fixes.

Towards the end I decided it was going to be good enough to publish. I started a fresh Claude Code instance and told it:

Look at the web/ folder - set up GitHub actions for this repo such that any push runs the tests, and if the tests pass it then does a GitHub Pages deploy of the built vite app such that the web/index.html page is the index.html page for the thing that is deployed and it works on GitHub Pages

After a bit more iteration here's the GitHub Actions workflow that builds the app using Vite and deploys the result to https://simonw.github.io/liteparse/.

I love GitHub Pages for this kind of thing because it can be quickly configured (by Claude, in this case) to turn any repository into a deployed web-app, at zero cost and with whatever build step is necessary. It even works against private repos, if you don't mind your only security being a secret URL.

With this kind of project there's always a major risk that the model might "cheat" - mark key features as "TODO" and fake them, or take shortcuts that ignore the initial requirements.

The responsible way to prevent this is to review all of the code... but this wasn't intended as that kind of project, so instead I fired up OpenAI Codex with GPT-5.5 (I had preview access) and told it:

Describe the difference between how the node.js CLI tool runs and how the web/ version runs

The answer I got back was enough to give me confidence that Claude hadn't taken any project-threatening shortcuts.

... and that was about it. Total time in Claude Code for that "build it" step was 59 minutes. I used my claude-code-transcripts tool to export a readable version of the full transcript which you can view here, albeit without those additional queued prompts (here's my issue to fix that).

Is this even vibe coding any more?

I'm a pedantic stickler when it comes to the original definition of vibe coding - vibe coding does not mean any time you use AI to help you write code, it's when you use AI without reviewing or caring about the code that's written at all.

By my own definition, this LiteParse for the web project is about as pure vibe coding as you can get! I have not looked at a single line of the HTML and TypeScript written for this project - in fact while writing this sentence I had to go and check if it had used JavaScript or TypeScript.

Yet somehow this one doesn't feel as vibe coded to me as many of my other vibe coded projects:

As a static in-browser web application hosted on GitHub Pages the blast radius for any bugs is almost non-existent: it either works for your PDF or doesn't.
No private data is transferred anywhere - all processing happens in your browser - so a security audit is unnecessary. I've glanced once at the network panel while it's running and no additional requests are made when a PDF is being parsed.
There was still a whole lot of engineering experience and knowledge required to use the models in this way. Identifying that porting LiteParse to run directly in a browser was critical to the rest of the project.

Most importantly, I'm happy to attach my reputation to this project and recommend that other people try it out. Unlike most of my vibe coded tools I'm not convinced that spending significant additional engineering time on this would have resulted in a meaningfully better initial release. It's fine as it is!

I haven't opened a PR against the origin repository because I've not discussed it with the LiteParse team. I've opened an issue, and if they want my vibe coded implementation as a starting point for something more official they're welcome to take it.

Tags: javascript, ocr, pdf, projects, ai, generative-ai, llms, vibe-coding, coding-agents, claude-code, agentic-engineering

scan-for-secrets 0.3

2026-04-06T02:59:28+00:00

Release: scan-for-secrets 0.3

New -r/--redact option which shows the list of matches, asks for confirmation and then replaces every match with REDACTED, taking escaping rules into account.

New Python function redact_file(file_path: str | Path, secrets: list[str], replacement: str = "REDACTED") -> int.

Tags: projects

scan-for-secrets 0.1

2026-04-05T03:27:13+00:00

Release: scan-for-secrets 0.1

I like publishing transcripts of local Claude Code sessions using my claude-code-transcripts tool but I'm often paranoid that one of my API keys or similar secrets might inadvertently be revealed in the detailed log files.

I built this new Python scanning tool to help reassure me. You can feed it secrets and have it scan for them in a specified directory:

uvx scan-for-secrets $OPENAI_API_KEY -d logs-to-publish/

If you leave off the -d it defaults to the current directory.

It doesn't just scan for the literal secrets - it also scans for common encodings of those secrets e.g. backslash or JSON escaping, as described in the README.

If you have a set of secrets you always want to protect you can list commands to echo them in a ~/.scan-for-secrets.conf.sh file. Mine looks like this:

llm keys get openai
llm keys get anthropic
llm keys get gemini
llm keys get mistral
awk -F= '/aws_secret_access_key/{print $2}' ~/.aws/credentials | xargs

I built this tool using README-driven-development: I carefully constructed the README describing exactly how the tool should work, then dumped it into Claude Code and told it to build the actual tool (using red/green TDD, naturally.)

Tags: projects, security, ai-assisted-programming, coding-agents, claude-code, agentic-engineering