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

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

fifth July 2026

I wrote in regards to the sqlite-utils 4.0rc1 launch a few weeks in the past. Since we solely have Claude Fable on our Max subscriptions for a couple of extra days, I made a decision to see if it may assist me get to a 4.0 steady launch that I felt actually snug about, since I attempt to preserve to SemVer and like my incompatible main variations to be as uncommon as doable.

I began with this immediate, in Claude Code for net on my iPhone:

Ultimate assessment earlier than transport a steady 4.0 launch – crucial to identify any final minute issues that may be a breaking change if we repair them later

Right here’s that preliminary report it created for me. There have been some vital issues that I hadn’t myself encountered but—5 that Fable categorized as “launch blockers”. Right here’s the worst of the bunch:

1. delete_where() by no means commits and poisons the connection (knowledge loss)

Desk.delete_where() (sqlite_utils/db.py:2948) runs its DELETE by way of a naked self.db.execute() with no atomic() wrapper — evaluate Desk.delete() at db.py:2944, which wraps appropriately. The connection is left in_transaction=True, so each subsequent atomic() name takes the savepoint department (db.py:430-440) and by no means commits both.

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.shut()
# Reopen: rows are [0, 1, 2] — the delete, row 50, AND desk u are all gone.

That’s a extremely unhealthy bug! Very glad I didn’t ship that, though no less than it could have been a bug I may repair in a 4.0.1 level launch, not a design flaw that may power a 5.0.

Over the course of 37 prompts, 34 commits and +1,321 -190 code adjustments over 30 separate recordsdata, we labored by means of the complete set of suggestions in flip, making a number of different design enhancements alongside the way in which.

A bizarre factor about coding brokers is that tougher duties like this one truly present extra alternative to do different issues on the identical time, because the agent typically wants 10-Quarter-hour to churn away on a brand new process. I went out to benefit from the Half Moon Bay 4th of July parade, often checking in and prompting the following step for Fable from my telephone.

Full particulars within the PR and this shared transcript. I switched to my laptop computer for the ultimate assessment, which I carried out by means of GitHub’s PR interface.

Essentially the most vital adjustments relate to transaction dealing with, which was the signature new characteristic within the earlier RC. The brand new RC now contains complete documentation on the brand new transaction mannequin, the intro to which I’ll quote right here in full:

Each methodology on this library that writes to the database—insert(), upsert(), replace(), delete(), delete_where(), rework(), create_table(), create_index(), enable_fts() and the remainder—runs inside its personal transaction and commits it earlier than returning. Your adjustments are saved to disk as quickly as the tactic name finishes:

db = Database(“knowledge.db”)
db.desk(“information”).insert({“headline”: “Canine wins award”})
# The brand new row is already saved – no commit() required

The identical applies to uncooked SQL executed with db.execute()—a write assertion is dedicated as quickly because it has run.

You by no means must name commit(), and you don’t want to shut the database to persist your adjustments. There are precisely two conditions the place it’s essential to take into consideration transactions:

You need to group a number of write operations collectively, in order that they both all succeed or all fail—use db.atomic().

You might be managing a transaction your self with db.start(), during which case nothing is dedicated till you commit—the library won’t ever commit a transaction you opened.

In reviewing Fable’s documentation—I discover that reviewing the documentation edits first is a superb approach to construct an preliminary understanding of what has modified—I noticed this element:

db.atomic() and the automated per-method transactions are designed for connections in Python’s default transaction dealing with mode. Connections created with the Python 3.12+ sqlite3.join(…, autocommit=True) or autocommit=False choices usually are not supported, as a result of commit() and rollback() behave in another way on these connections.

I admit I hadn’t thought of how sqlite-utils would react to the newer autocommit setting, added in Python 3.12. It seems “behave in another way on these connections” equated to virtually the complete take a look at suite failing, so I labored with the mannequin to make sure that this distinction wouldn’t break how the library works.

And a ultimate assessment by GPT-5.5

I used to suppose that the concept of getting one mannequin assessment the work of one other was considerably absurd—it felt weirdly superstitious. The issue is it actually does work—I’ve began habitually having Anthropic’s finest mannequin assessment OpenAI’s work and vice versa, as a result of I’ve had that flip up attention-grabbing outcomes typically sufficient to be worthwhile.

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

Assessment adjustments because the final RC. Additionally verify that the changelog is up-to-date.

Which was sufficient to show up two points value investigating:

Findings

[P1] sqlite_utils/db.py:663 db.question() now rejects non-row statements solely after calling db.execute(), and sqlite_utils/db.py:705 auto-commits these writes first. So db.question(“replace …”) raises ValueError however the replace is already dedicated. That could be a shocking aspect impact for a technique documented as “can solely be used with SQL that returns rows.”
[P1] sqlite_utils/db.py:672 INSERT … RETURNING by means of db.question() solely commits after the returned generator is totally exhausted. db.question(“insert … returning …”) with out iteration, or frequent subsequent(db.question(…)) utilization, leaves the transaction open and the write may be rolled again on shut. This contradicts docs/changelog.rst:15 and docs/python-api.rst:232, which say it takes impact with out iteration.

I pasted that right into a contemporary Fable session, which ran some experiments to verify the issue:

Each findings have been confirmed. db.question() referred to as self.execute() first, which auto-commits writes, and solely then checked cursor.description — so db.question(“replace …”) dedicated the replace earlier than elevating ValueError. And the INSERT … RETURNING commit lived on the finish of the returned generator, so it by no means fired until you exhausted the iterator — subsequent(db.question(…)) or an un-iterated name left the transaction open, contradicting what the changelog and docs promise.

Right here’s the PR with the repair, and the complete Claude Code transcript. Reviewing this code helped me construct a greater psychological mannequin of the sting circumstances of SQLite transaction semantics!

For an estimated (unsubsidized) price of $149.25

I upgraded to the Claude Max $200/month plan (I used to be beforehand on $100/month) to extend my Fable allowance for the remaining time till the July seventh Fablepocalypse, when even Claude Max subscribers should pay full API price for the mannequin.

I used to be curious as to how a lot this could have price me if I had been paying these prices immediately. At first I assumed these numbers weren’t accessible to me since I had run the work remotely utilizing Claude Code for net, after which I noticed I may run AgentsView inside that present session to get that price estimate!

Run “uvx agentsview –help”​ after which use that software to calculate the price of this session

Claude discovered easy methods to use the session record –include-children command and got here out with the next:

Transcript
Mannequin
Price

Essential session
claude-fable-5
$141.02

API-surface sweep agent
claude-fable-5
$2.40

Transactions/atomic assessment agent
claude-fable-5
$2.39

Put up-rc1 commits assessment agent
claude-fable-5
$1.72

Migrations assessment agent
claude-fable-5
$1.40

Immediate-counting agent
claude-opus-4-8
$0.32

Complete

$149.25

I’m very glad I’m on that subscription! I actually ought to have adopted my very own recommendation and leaned extra closely into subagents with cheaper fashions.

Right here’s what claude.ai/settings/utilization is exhibiting me proper now:

I’ve a number of different main Fable-driven initiatives on the go proper now as effectively, with the purpose of hitting 100% on that Fable bar simply in time for the worth enhance.

The complete launch notes for sqlite-utils 4.0rc2

Listed here are the complete launch notes for the RC. I had Fable add these to an “Unreleased” part of the changelog as every change landed, reviewing them because it went. This has the neat aspect impact that the commit historical past of the changelog acts as a concise abstract of every of the adjustments that went into the discharge.

Up to now I’ve had a coverage of writing launch notes by hand, however actually these are higher than I might have created myself. Launch notes are an amazing instance of writing that I’m OK to outsource to brokers as a result of they must be boring, predictable and correct.

Breaking adjustments:

Write statements executed with db.execute() at the moment are dedicated routinely, until a transaction is already open during which case they be part of it. Beforehand they opened an implicit transaction that stayed open till one thing dedicated it—writes appeared to work when learn on the identical connection however have been silently rolled again when the connection closed. Code that relied on rolling again uncommitted db.execute() writes ought to use the brand new db.start() methodology to open an express transaction first. The transaction mannequin is documented in full at Transactions and saving your adjustments.

db.question() now executes its SQL as quickly as it’s referred to as, slightly than ready till the returned generator is first iterated. Rows are nonetheless fetched lazily throughout iteration. SQL errors at the moment are raised on the name website, statements reminiscent of INSERT … RETURNING are executed and dedicated instantly while not having to iterate over their outcomes, and passing an announcement that returns no rows—beforehand a silent no-op—now raises a ValueError recommending db.execute() as a substitute. An announcement rejected this fashion is rolled again earlier than the error is raised, so it has no impact on the database.
Python API validation errors now elevate ValueError as a substitute of AssertionError. Beforehand invalid arguments—reminiscent of create_table() with no columns, rework() on a desk that doesn’t exist, or passing each ignore=True and substitute=True—have been rejected utilizing naked assert statements, that are silently skipped when Python runs with the -O flag. Code that caught AssertionError for these circumstances ought to catch ValueError as a substitute.

desk.upsert() and desk.upsert_all() now elevate PrimaryKeyRequired if a document is lacking a worth for any major key column, or has a worth of None for one. Beforehand such data—which might by no means match an present row—have been quietly inserted as model new rows, or triggered a complicated KeyError after the insert had already taken place.

db.enable_wal() and db.disable_wal() now elevate a sqlite_utils.db.TransactionError if referred to as whereas a transaction is open. Beforehand they might silently commit the open transaction as a aspect impact of fixing the journal mode, breaking the rollback assure of db.atomic() and of user-managed transactions.
The View class now not has an enable_fts() methodology. It existed solely to lift NotImplementedError, since full-text search shouldn’t be supported for views—calling it now raises AttributeError as a substitute, and the tactic now not seems within the API reference. The sqlite-utils enable-fts command exhibits a clear error when pointed at a view.
The no-op -d/–detect-types flag has been faraway from the insert and upsert instructions. Sort detection has been the default for CSV/TSV knowledge since 4.0a1, so the flag did nothing—invocations utilizing it ought to merely drop it. –no-detect-types stays accessible to disable detection.

Database() now raises a sqlite_utils.db.TransactionError if handed a connection created with the Python 3.12+ sqlite3.join(…, autocommit=True) or autocommit=False choices. commit() and rollback() behave in another way on these connections, which beforehand precipitated each write made by the library to be silently discarded when the connection closed.

All the pieces else:

Mounted a bug the place desk.delete_where(), desk.optimize() and desk.rebuild_fts() didn’t commit their adjustments, leaving the connection inside an open transaction. Their work—and any subsequent writes—may then be silently rolled again when the connection was closed. All three now use db.atomic(), in keeping with the opposite write strategies.
The sqlite-utils drop-table command now refuses to drop a view, and drop-view refuses to drop a desk. Beforehand every would silently drop the improper kind of object if the title matched. Each now exit with an error suggesting the proper command to make use of.
Migrations utilized by the brand new migrations system now run inside a transaction, along with the document of the migration having been utilized. If a migration raises an exception its adjustments are rolled again and it stays pending, so it may be safely re-applied after the error is mounted. Migrations that can’t run inside a transaction, reminiscent of these executing VACUUM, can decide out utilizing @migrations(transactional=False)—see Migrations and transactions.

desk.upsert() and desk.upsert_all() now detect the first key or compound major key of an present desk, so the pk= argument is now not required when upserting right into a desk that already has a major key.

db.desk(table_name).insert({}) can now be used to insert a row consisting totally of default values into an present desk, utilizing INSERT INTO … DEFAULT VALUES. (#759)
Enhancements to the sqlite-utils migrate command: –stop-before values that don’t match any recognized migration at the moment are an error as a substitute of being silently ignored, –stop-before now works appropriately with migration recordsdata that also use the older sqlite_migrate.Migrations class, and –list is now a read-only operation that now not creates the database file or the migrations monitoring desk. migrations.utilized() now returns migrations within the order they have been utilized.
New db.start(), db.commit() and db.rollback() strategies for taking guide management of transactions, as a substitute for the db.atomic() context supervisor.
New documentation: Transactions and saving your adjustments describes how transactions work and when adjustments are dedicated, and a brand new Upgrading web page particulars the adjustments wanted to maneuver between main variations.