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

Simon Willison’s Weblog

Subscribe

Sponsored by: Sonar — Gartner just named Sonar a Leader in the 2026 Magic Quadrant™ for Technical Debt Management Tools. Read the report and learn how to measure and remediate technical debt across your codebase. Get the report

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

5th July 2026

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")<br>db["t"].insert_all([{"id": i} for i in range(3)], pk="id")<br>db["t"].delete_where("id = ?", [0]) # conn.in_transaction is now True<br>db["t"].insert({"id": 50})<br>db["u"].insert({"a": 1})<br>db.close()<br># 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")<br>db.table("news").insert({"headline": "Dog wins award"})<br># The new row is already saved - no commit() required<br>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...