Simon Willison’s Weblog

Fixes a crashing bug in sqlite-utils 4.2. I'd introduced code that looks like this:

from typing_extensions import Self

It turned out the typing-extensions package was not listed as a dependency for sqlite-utils - it was installed by one of the other dependencies in the dev dependency group, but when you uvx sqlite-utils directly you don't get those dependencies.

As part of fixing this I figured out how to run a smoke test to ensure the CLI tool still works even without those dev dependencies, which can be run from the project checkout:

uv run --isolated --no-default-groups sqlite-utils --help

The --no-default-groups argument prevents it from installing that default dev group, and --isolated means that even if there is a .venv/ folder containing extra dependencies they will be ignored for the duration of that uv run command.

Lots of improvements in this one relating to the table.transform() feature, which adds support for complex alter table operations by creating a fresh table, copying across the data and then dropping and replacing the old one.

transform() now preserves a much larger array of edge-case schema definitions, including check constraints, unique constraints and even comments describing the columns.

There are also new introspection properties for check constraints, and a whole lot of other smaller changes.

Includes contributions from Bunlong Heng, ethanhawkes-gif, Rami Abdelrazzaq, nyxst4ck, and ikatyal2110.

(It later turned out 4.2 had a crashing bug, fixed in 4.2.1.)

It's been a while since the last llm-gemini release. This version of the plugin adds support for today's Gemini 3.7 Flash release, plus gemini-3.6-flash, gemini-3.5-flash-lite and two embedding models gemini-embedding-2 and gemini-embedding-001.

The plugin is also upgraded for compatibility with LLM 0.32, which means you can now see reasoning traces and you can also enable server-side tools using this pattern:

llm -m gemini-3.7-flash -T CodeExecution \
  'use python to calculate (factorial of 13) * 3'

I had Gemini 3.7 Flash draw me some pelicans riding bicycles at high, medium, and low thinking efforts (minimal, which was an option in 3.6 Flash, has been removed in 3.7.) Here's the high level one, which is pretty great:

Update 14th August 2026: I had originally said that the SVG rendered incorrectly in Chrome and Firefox, and blamed Gemini 3.7 Flash for producing invalid SVG. That was entirely incorrect: the rendering glitch was my fault, caused by a bug In my rendering tool. I've now fixed that bug.

Performance boost for DuckDB exports and CSV imports, see here.

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.)

This plugin has been around for a while - it lets users upload a brand new SQLite database to a hosted Datasette instance, at which point that database will start being served by that instance.

It can also be used to atomically swap a database with a more recent version. The uploaded database is saved to a file, verified, then swapped in so /name starts serving the new one.

The new release adds a formalized API, so you can replace an existing database (or add a new one) like this:

curl -X POST \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Accept: application/json" \
  -F "db=@content.db" \
  -F "db_name=content" \
  https://your-instance.example.com/-/upload-dbs

This means you can build fresh databases in an environment such as GitHub Actions and swap them in production as soon as that build has completed.

Upgraded for compatibility with `sqlite-utils 4.

This release fixes a SQL injection security issue that affects Datasette instances that serve a mixture of public and private tables in the same database, with access configured using the Datasette permissions system.

Site administrators who serve private tables in this way are advised to disable the execute-sql permission ` on that database to prevent users from accessing private tables using raw SQL queries. The bug that has been fixed would have allowed users with access to any public table to execute SQL injection attacks despite that restriction, giving them read-only access to data in private tables in the same database.

This fix is also available in Datasette 0.65.3.

Thankfully this particular configuration - private tables and public tables exposed for the same database within the same instance - is likely to be rare. I've not encountered an instance like that myself.

Back-ported the SQL Injection security fix from 1.0a38.

Includes new features enabled by LLM 0.32:

• New models: claude-fable-5, claude-sonnet-5, and claude-opus-5. #75, #76
• Added server-side tools for WebSearch, WebFetch, CodeExecution, and AnthropicMCP, available through LLM's -T interface or Python tools=. The previous -o web_search* options have been removed in favor of -T WebSearch. #79
• Upgraded to llm>=0.32. Reasoning, tool calls, tool results, and server-side tool results now stream as typed events. Reasoning for llm CLI prompts now displays to standard error unless you pass --hide-reasoning/-R.
• Simplified extended thinking to thinking and thinking_effort (low, medium, high, xhigh, or max). Claude 5 models think by default; -o thinking 0 disables thinking for Sonnet 5 and Opus 5, while Fable 5 always thinks. -R/--hide-reasoning now omits reasoning from responses and logs. The thinking_budget, thinking_display, and thinking_adaptive options have been removed. #80

See my detailed blog post about this release.

After shipping condense-json 1.0 I started integrating it into LLM, and found there were some desirable new features already:

• Replacements object can now include values other than strings. These will be identified and used as structural replacements by condense_json() and uncondense_json(). #8
• Objects can be used as the basis for merge operations. condense_json() will identify if there are objects that are a close match and will store instructions for keys to update or delete. uncondense_json() can then apply these merges.

I also added some round-trip tests using the Hypothesis property-based Python testing library.

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.

Changes that improve Datasette Apps when created and edited using Datasette Agent:

• New app_debug() tool allowing agent to open an app (invisibly) and test it using JavaScript. #33
• New app_list() tool for listing apps the user has permission to edit, so the agent can edit them. #36

The app_debug() tool is pretty neat: it works by displaying the app in a opacity: 0 iframe with pointer-events: none (so it can't be seen or interacted with) and then executing agent-provided JavaScript inside that sandboxed iframe. This means the agent can smoke test that the app is working and even do things like measure the dimensions of different elements.

This uses the new context.browser_task() mechanism added in datasette-agent 0.4a0.

See this blog entry.

• New await context.browser_task() mechanism allowing agent tools to run code directly in the user's browser. #33

This is an exciting new capability: it makes it easy for Datasette Agent plugins to provide tools that execute custom JavaScript in the user's browser.

I used this to add a debug loop to Datasette Apps in datasette-apps 0.2a0.

Hot on the heels of RC1, this fixes a dependency issue and also adds two neat new features:

• The default model for users who have not set their own default is now GPT-5.6 Luna. It was previously GPT-4o mini. Luna is a much better and more recent model, albeit slightly more expensive - $0.20 per million input tokens and $1.20 per million output tokens, compared to $0.15/$0.60 for 4o mini. You can switch back to 4o mini using llm models default gpt-4o-mini, or switch to GPT-5 nano, an even cheaper default model ($0.05/$0.40), using llm models default gpt-5-nano. #1576
• New llm openai endpoint command for running prompts, chats and model listings against arbitrary OpenAI-compatible endpoints without first configuring a model. These calls are not logged. #1565

The llm openai endpoint command is really cool. I got frustrated at the lack of an obvious CLI tool for trying out prompts against arbitrary OpenAI Chat Completions imitation endpoints, so I decided to add that to LLM itself.

You don't even have to install LLM to use this. Here's a uvx one-liner for running a prompt - with tools - against an LM Studio local model:

uvx --pre llm openai endpoint http://127.0.0.1:1234/v1 \
  T llm_version -T llm_time --td \
  -m google/gemma-4-31b 'what is the current LLM version? And the time?'

Output here.

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.

This RC for LLM 0.32 finishes the work that started in LLM 0.32a0 - it adds a new schema design that does a much better job of capturing the details of the prompts and responses returned by the latest model families.

The most important change is the use of content-addressable hash IDs for stored messages. This allows de-duplication in the database, and means that LLM can now represent trees of messages for forked conversations.

Since it involves a significant schema change - new tables only, and old data should not be affected at all - it's worth running a backup of your existing logs.db before upgrading to the RC:

llm logs backup logs-backup.db

The RC also adds support for gpt-5.6-sol, gpt-5.6-terra, and gpt-5.6-luna.

I back-ported a fix for table.delete_where() that shipped in version 4.

A minor release. Performance and documentation improvements to the permissions system, plus I reverted a cosmetic API change which caused almost every existing plugin test suite to break.

Some minor improvements, mainly around command option consistency and making the server: mechanism used by both shot-scraper video and shot-scraper multi work if the server takes longer than a second to start serving traffic.

• server: processes used by shot-scraper multi and shot-scraper video now wait up to 30 seconds for the target URL to accept connections, polling for port availability and replacing the previous fixed one-second delay. #197
• The shot-scraper, pdf, html, accessibility and har commands now have a --js-file option for loading JavaScript from a local file, standard input or gh:username/script, as an alternative to --javascriptwhich accepts the string of JavaScript directly as an argument. #192
• shot-scraper multi supports the equivalent js_file: YAML key.
• The shot-scraper javascript and shot-scraper html commands now have a --timeout option for consistency with other commands. #118

Mainly a fix for an edge case that regular Claude chat spotted while experimenting with the 4.1 release to answer a question about ON DELETE.

• table.transform() now raises a TransactionError if called while a transaction is open with PRAGMA foreign_keys enabled and the table is referenced by foreign keys with destructive ON DELETE actions - CASCADE, SET NULL or SET DEFAULT. The pragma cannot be changed inside a transaction, so previously dropping the old table as part of the transform could fire those actions and silently delete or modify referencing rows. See Foreign keys and transactions for details and workarounds. (#794)
• The CLI and Python API documentation now cross-reference each other: CLI sections link to the equivalent Python API functionality and Python API sections link back to the corresponding CLI command. (#791)

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.

Let's LLM run prompts against the new muse-spark-1.1 model.

• Fix for a bug with OpenAI Chat Completion endpoints where a tool call with empty arguments could result in a JSON error from some providers. #1521

This bug came up when I was testing llm-meta-ai.

The signature features of this alpha are new UIs for inserting multiple rows at once (from TSV, CSV or JSON) and for creating a table from rows, plus a large number of small JSON API consistency fixes in preparation for a 1.0 stable release.

The version that retires the library, instead implementing a compatibility shim against the new sqlite-utils 4.0 dependency.

See sqlite-utils 4.0, now with database schema migrations for details.