元記事の説明文
<p>I wrote about the <a href="https://simonwillison.net/2026/Jun/21/sqlite-utils-40rc1/">sqlite-utils 4.0rc1</a> 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 <a href="https://semver.org">SemVer</a> and like my incompatible major versions to be as rare as possible.</p>
<p>I started with this prompt, in Claude Code for web on my iPhone:</p>
<blockquote>
<p><code>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</code></p>
</blockquote>
<p>Here's <a href="https://github.com/simonw/sqlite-utils/blob/0c369a447eeaf39084f0d14a45b3eeb7eacb631b/fable-review-4.0rc1.md">that initial report</a> it created for me. There were some <em>significant</em> problems that I hadn't myself encountered yet - 5 that Fable categorized as "release blockers". Here's the worst of the bunch:</p>
<blockquote>
<p><strong>1. <code>delete_where()</code> never commits and poisons the connection (data loss)</strong></p>
<p><code>Table.delete_where()</code> (<code>sqlite_utils/db.py:2948</code>) runs its DELETE via a bare <code>self.db.execute()</code> with no <code>atomic()</code> wrapper — compare <code>Table.delete()</code> at <code>db.py:2944</code>, which wraps correctly. The connection is left <code>in_transaction=True</code>, so every <em>subsequent</em> <code>atomic()</code> call takes the savepoint branch (<code>db.py:430-440</code>) and never commits either.</p>
<p>Reproduced end-to-end:</p>
<pre><span class="pl-s1">db</span> <span class="pl-c1">=</span> <span class="pl-s1">sqlite_utils</span>.<span class="pl-c1">Database</span>(<span class="pl-s">"dw.db"</span>)
<span class="pl-s1">db</span>[<span class="pl-s">"t"</span>].<span class="pl-c1">insert_all</span>([{<span class="pl-s">"id"</span>: <span class="pl-s1">i</span>} <span class="pl-k">for</span> <span class="pl-s1">i</span> <span class="pl-c1">in</span> <span class="pl-en">range</span>(<span class="pl-c1">3</span>)], <span class="pl-s1">pk</span><span class="pl-c1">=</span><span class="pl-s">"id"</span>)
<span class="pl-s1">db</span>[<span class="pl-s">"t"</span>].<span class="pl-c1">delete_where</span>(<span class="pl-s">"id = ?"</span>, [<span class="pl-c1">0</span>]) <span class="pl-c"># conn.in_transaction is now True</span>
<span class="pl-s1">db</span>[<span class="pl-s">"t"</span>].<span class="pl-c1">insert</span>({<span class="pl-s">"id"</span>: <span class="pl-c1">50</span>})
<span class="pl-s1">db</span>[<span class="pl-s">"u"</span>].<span class="pl-c1">insert</span>({<span class="pl-s">"a"</span>: <span class="pl-c1">1</span>})
<span class="pl-s1">db</span>.<span class="pl-c1">close</span>()
<span class="pl-c"># Reopen: rows are [0, 1, 2] — the delete, row 50, AND table u are all gone.</span></pre>
</blockquote>
<p>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.</p>
<p>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.</p>
<p>A weird thing about coding agents is that harder tasks like this one actually provide <em>more</em> 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.</p>
<p>Full details <a href="https://github.com/simonw/sqlite-utils/pull/767">in the PR</a> and <a href="https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd">this shared transcript</a>. I switched to my laptop for the final review, which I conducted through GitHub's PR interface.</p>
<p>The most significant changes relate to transaction handling, which was the signature new feature in <a href="https://simonwillison.net/2026/Jun/21/sqlite-utils-40rc1/#new-feature-db-atomic-transactions">the earlier RC</a>. The new RC now includes <a href="https://sqlite-utils.datasette.io/en/latest/python-api.html#transactions-and-saving-your-changes">comprehensive documentation</a> on the new transaction model, the intro to which I'll quote here in full:</p>
<blockquote>
<p>Every method in this library that writes to the database - <code>insert()</code>, <code>upsert()</code>, <code>update()</code>, <code>delete()</code>, <code>delete_where()</code>, <code>transform()</code>, <code>create_table()</code>, <code>create_index()</code>, <code>enable_fts()</code> 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:</p>
<pre><span class="pl-s1">db</span> <span class="pl-c1">=</span> <span class="pl-en">Database</span>(<span class="pl-s">"data.db"</span>)
<span class="pl-s1">db</span>.<span class="pl-c1">table</span>(<span class="pl-s">"news"</span>).<span class="pl-c1">insert</span>({<span class="pl-s">"headline"</span>: <span class="pl-s">"Dog wins award"</span>})
<span class="pl-c"># The new row is already saved - no commit() required</span></pre>
<p>The same applies to raw SQL executed with <a href="https://sqlite-utils.datasette.io/en/latest/python-api.html#python-api-transactions-execute">db.execute()</a> - a write statement is committed as soon as it has run.</p>
<p>You never need to call <code>commit()</code>, 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:</p>
<ol>
<li>
<p>You want to group several write operations together, so they either all succeed or all fail - use <a href="https://sqlite-utils.datasette.io/en/latest/python-api.html#python-api-atomic">db.atomic()</a>.</p>
</li>
<li>
<p>You are <a href="https://sqlite-utils.datasette.io/en/latest/python-api.html#python-api-transactions-manual">managing a transaction yourself</a> with <code>db.begin()</code>, in which case nothing is committed until you commit - the library will never commit a transaction you opened.</p>
</li>
</ol>
</blockquote>
<p>In reviewing Fable's documentation - I find that reviewing the documentation edits first is an <em>excellent</em> way to build an initial understanding of what has changed - I spotted this detail:</p>
<blockquote>
<p><code>db.atomic()</code> and the automatic per-method transactions are designed for connections in Python's default transaction handling mode. Connections created with the Python 3.12+ <code>sqlite3.connect(..., autocommit=True)</code> or <code>autocommit=False</code> options are not supported, because <code>commit()</code> and <code>rollback()</code> behave differently on those connections.</p>
</blockquote>
<p>I admit I hadn't thought about how <code>sqlite-utils</code> would react to the more recent <a href="https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.autocommit">autocommit setting</a>, 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 <a href="https://github.com/simonw/sqlite-utils/commit/f7ff3e2027aefb9905ebb2e611e5bbb0a62382c5">ensure that this difference</a> would not break how the library works.</p>
<h4 id="and-a-final-review-by-gpt-5-5">And a final review by GPT-5.5</h4>
<p>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 <em>it really does work</em> - 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.</p>
<p>I prompted Codex Desktop and GPT-5.5 xhigh with the following:</p>
<blockquote>
<p><code>Review changes since the last RC. Also confirm that the changelog is up-to-date.</code></p>
</blockquote>
<p>Which was enough to turn up two issues worth investigating:</p>
<blockquote>
<p><strong>Findings</strong></p>
<ul>
<li>[P1] <a href="https://github.com/simonw/sqlite-utils/blob/04f8971546418962aaf6579d4028c7117d6c3a20/sqlite_utils/db.py#L663">sqlite_utils/db.py:663</a> <code>db.query()</code> now rejects non-row statements only after calling <code>db.execute()</code>, and <a href="https://github.com/simonw/sqlite-utils/blob/04f8971546418962aaf6579d4028c7117d6c3a20/sqlite_utils/db.py#L705">sqlite_utils/db.py:705</a> auto-commits those writes first. So <code>db.query("update ...")</code> raises <code>ValueError</code> 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.”</li>
<li>[P1] <a href="https://github.com/simonw/sqlite-utils/blob/04f8971546418962aaf6579d4028c7117d6c3a20/sqlite_utils/db.py#L672">sqlite_utils/db.py:672</a> <code>INSERT ... RETURNING</code> through <code>db.query()</code> only commits after the returned generator is fully exhausted. <code>db.query("insert ... returning ...")</code> without iteration, or common <code>next(db.query(...))</code> usage, leaves the transaction open and the write can be rolled back on close. This contradicts <a href="https://github.com/simonw/sqlite-utils/blob/04f8971546418962aaf6579d4028c7117d6c3a20/docs/changelog.rst?plain=1#L15">docs/changelog.rst:15</a> and <a href="https://github.com/simonw/sqlite-utils/blob/04f8971546418962aaf6579d4028c7117d6c3a20/docs/python-api.rst?plain=1#L232">docs/python-api.rst:232</a>, which say it takes effect without iteration.</li>
</ul>
</blockquote>
<p>I pasted that into a fresh Fable session, which ran some experiments to confirm the problem:</p>
<blockquote>
<p>Both findings were confirmed. <code>db.query()</code> called <code>self.execute()</code> first, which auto-commits writes, and only then checked <code>cursor.description</code> — so <code>db.query("update ...")</code> committed the update before raising <code>ValueError</code>. And the <code>INSERT ... RETURNING</code> commit lived at the end of the returned generator, so it never fired unless you exhausted the iterator — <code>next(db.query(...))</code> or an un-iterated call left the transaction open, contradicting what the changelog and docs promise.</p>
</blockquote>
<p>Here's <a href="https://github.com/simonw/sqlite-utils/pull/768">the PR</a> with the fix, and the <a href="https://claude.ai/code/session_012U3iRfJoTZ5vd22cBSF2nJ">full Claude Code transcript</a>. Reviewing this code helped me build a better mental model of the edge cases of SQLite transaction semantics!</p>
<h4 id="for-an-estimated-unsubsidized-cost-of-149-25">For an estimated (unsubsidized) cost of $149.25</h4>
<p>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 <a href="https://www.anthropic.com/news/redeploying-fable-5">the July 7th Fablepocalypse</a>, when even Claude Max subscribers will have to pay full API cost for the model.</p>
<p>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 <a href="https://www.agentsview.io">AgentsView</a> inside that existing session to get that cost estimate!</p>
<blockquote>
<p><code>Run "uvx agentsview --help" and then use that tool to calculate the cost of this session</code></p>
</blockquote>
<p>Claude figured out how to use the <code>session list --include-children</code> command and came out with the following:</p>
<table>
<thead>
<tr>
<th>Transcript</th>
<th>Model</th>
<th>Cost</th>
</tr>
</thead>
<tbody>
<tr>
<td>Main session</td>
<td>claude-fable-5</td>
<td>$141.02</td>
</tr>
<tr>
<td>API-surface sweep agent</td>
<td>claude-fable-5</td>
<td>$2.40</td>
</tr>
<tr>
<td>Transactions/atomic review agent</td>
<td>claude-fable-5</td>
<td>$2.39</td>
</tr>
<tr>
<td>Post-rc1 commits review agent</td>
<td>claude-fable-5</td>
<td>$1.72</td>
</tr>
<tr>
<td>Migrations review agent</td>
<td>claude-fable-5</td>
<td>$1.40</td>
</tr>
<tr>
<td>Prompt-counting agent</td>
<td>claude-opus-4-8</td>
<td>$0.32</td>
</tr>
<tr>
<td><strong>Total</strong></td>
<td></td>
<td><strong>$149.25</strong></td>
</tr>
</tbody>
</table>
<p>I'm very glad I'm on that subscription! I really should have <a href="https://simonwillison.net/2026/Jul/3/judgement/">followed my own advice</a> and leaned more heavily into subagents with cheaper models.</p>
<p>Here's what <a href="https://claude.ai/settings/usage">claude.ai/settings/usage</a> is showing me right now:</p>
<p><img alt="Screenshot of a Claude plan usage limits panel: "Plan usage limits Max (20x)"; "Current session" with "Resets in 3 hr 52 min" showing a progress bar at "7% used"; "Weekly limits" heading with a "Learn more about usage limits" link; "All models" with "Resets Wed 12:00 PM" showing a progress bar at "32% used"; "Fable" with "Resets Wed 12:00 PM" showing a progress bar at "63% used"." src="https://static.simonwillison.net/static/2026/fable-plan-usage.webp" /></p>
<p>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.</p>
<h4 id="the-full-release-notes-for-sqlite-utils-4-0rc2">The full release notes for sqlite-utils 4.0rc2</h4>
<p>Here are <a href="https://sqlite-utils.datasette.io/en/latest/changelog.html#rc2-2026-07-04">the full release notes</a> 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 <a href="https://github.com/simonw/sqlite-utils/commits/4.0rc2/docs/changelog.rst">the commit history of the changelog</a> acts as a concise summary of each of the changes that went into the release.</p>
<p>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.</p>
<blockquote>
<p>Breaking changes:</p>
<ul>
<li>Write statements executed with <code>db.execute()</code> 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 <code>db.execute()</code> writes should use the new <code>db.begin()</code> method to open an explicit transaction first. The transaction model is documented in full at <a href="https://sqlite-utils.datasette.io/en/latest/python-api.html#python-api-transactions">Transactions and saving your changes</a>.</li>
<li>
<code>db.query()</code> 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 <code>INSERT ... RETURNING</code> 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 <code>ValueError</code> recommending <code>db.execute()</code> instead. A statement rejected this way is rolled back before the error is raised, so it has no effect on the database.</li>
<li>Python API validation errors now raise <code>ValueError</code> instead of <code>AssertionError</code>. Previously invalid arguments - such as <code>create_table()</code> with no columns, <code>transform()</code> on a table that does not exist, or passing both <code>ignore=True</code> and <code>replace=True</code> - were rejected using bare <code>assert</code> statements, which are silently skipped when Python runs with the <code>-O</code> flag. Code that caught <code>AssertionError</code> for these cases should catch <code>ValueError</code> instead.</li>
<li>
<code>table.upsert()</code> and <code>table.upsert_all()</code> now raise <code>PrimaryKeyRequired</code> if a record is missing a value for any primary key column, or has a value of <code>None</code> for one. Previously such records - which can never match an existing row - were quietly inserted as brand new rows, or triggered a confusing <code>KeyError</code> after the insert had already taken place.</li>
<li>
<code>db.enable_wal()</code> and <code>db.disable_wal()</code> now raise a <code>sqlite_utils.db.TransactionError</code> 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 <code>db.atomic()</code> and of user-managed transactions.</li>
<li>The <code>View</code> class no longer has an <code>enable_fts()</code> method. It existed only to raise <code>NotImplementedError</code>, since full-text search is not supported for views - calling it now raises <code>AttributeError</code> instead, and the method no longer appears in the API reference. The <code>sqlite-utils enable-fts</code> command shows a clean error when pointed at a view.</li>
<li>The no-op <code>-d/--detect-types</code> flag has been removed from the <code>insert</code> and <code>upsert</code> 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. <code>--no-detect-types</code> remains available to disable detection.</li>
<li>
<code>Database()</code> now raises a <code>sqlite_utils.db.TransactionError</code> if passed a connection created with the Python 3.12+ <code>sqlite3.connect(..., autocommit=True)</code> or <code>autocommit=False</code> options. <code>commit()</code> and <code>rollback()</code> behave differently on those connections, which previously caused every write made by the library to be silently discarded when the connection closed.</li>
</ul>
<p>Everything else:</p>
<ul>
<li>Fixed a bug where <code>table.delete_where()</code>, <code>table.optimize()</code> and <code>table.rebuild_fts()</code> 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 <code>db.atomic()</code>, consistent with the other write methods.</li>
<li>The <code>sqlite-utils drop-table</code> command now refuses to drop a view, and <code>drop-view</code> 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.</li>
<li>Migrations applied by the new <a href="https://sqlite-utils.datasette.io/en/latest/migrations.html#migrations">migrations system</a> 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 <code>VACUUM</code>, can opt out using <code>@migrations(transactional=False)</code> - see <a href="https://sqlite-utils.datasette.io/en/latest/migrations.html#migrations-transactions">Migrations and transactions</a>.</li>
<li>
<code>table.upsert()</code> and <code>table.upsert_all()</code> now detect the primary key or compound primary key of an existing table, so the <code>pk=</code> argument is no longer required when upserting into a table that already has a primary key.</li>
<li>
<code>db.table(table_name).insert({})</code> can now be used to insert a row consisting entirely of default values into an existing table, using <code>INSERT INTO ... DEFAULT VALUES</code>. (<a href="https://github.com/simonw/sqlite-utils/issues/759">#759</a>)</li>
<li>Improvements to the <code>sqlite-utils migrate</code> command: <code>--stop-before</code> values that do not match any known migration are now an error instead of being silently ignored, <code>--stop-before</code> now works correctly with migration files that still use the older <code>sqlite_migrate.Migrations</code> class, and <code>--list</code> is now a read-only operation that no longer creates the database file or the migrations tracking table. <code>migrations.applied()</code> now returns migrations in the order they were applied.</li>
<li>New <code>db.begin()</code>, <code>db.commit()</code> and <code>db.rollback()</code> methods for taking manual control of transactions, as an alternative to the <code>db.atomic()</code> context manager.</li>
<li>New documentation: <a href="https://sqlite-utils.datasette.io/en/latest/python-api.html#python-api-transactions">Transactions and saving your changes</a> describes how transactions work and when changes are committed, and a new <a href="https://sqlite-utils.datasette.io/en/latest/upgrading.html#upgrading">Upgrading</a> page details the changes needed to move between major versions.</li>
</ul>
</blockquote>
<p>Tags: <a href="https://simonwillison.net/tags/projects">projects</a>, <a href="https://simonwillison.net/tags/sqlite">sqlite</a>, <a href="https://simonwillison.net/tags/sqlite-utils">sqlite-utils</a>, <a href="https://simonwillison.net/tags/annotated-release-notes">annotated-release-notes</a>, <a href="https://simonwillison.net/tags/anthropic">anthropic</a>, <a href="https://simonwillison.net/tags/claude">claude</a>, <a href="https://simonwillison.net/tags/coding-agents">coding-agents</a>, <a href="https://simonwillison.net/tags/claude-code">claude-code</a>, <a href="https://simonwillison.net/tags/agentic-engineering">agentic-engineering</a>, <a href="https://simonwillison.net/tags/gpt">gpt</a>, <a href="https://simonwillison.net/tags/claude-mythos-fable">claude-mythos-fable</a></p>