The sqlite-utils 4.0rc2 release fixes a set of transaction-handling bugs that could leave writes uncommitted and silently rolled back when a connection closed. Many of the fixes were developed with the help of Anthropic’s Claude Fable coding model and were later reviewed by GPT-5.5. The work involved 37 prompts, 34 commits and code changes totaling +1,321 / -190 across 30 files.
What happened and how the bugs were found
The author had previously published sqlite-utils 4.0rc1 and wanted to reach a 4.0 stable release that respects SemVer by avoiding unnecessary breaking changes. With limited remaining access to Claude Fable on a Max subscription, the author used the model to help with a final review.
Claude Fable produced an initial report that identified several serious issues; five of these were flagged as "release blockers." One of the most severe problems was that Table.delete_where() executed a DELETE without wrapping it in an atomic transaction, leaving the connection with in_transaction=True. Subsequent atomic() calls then took the savepoint path and never committed, which could cause deletions and later writes to be silently rolled back on reopen — the author provided a reproduction showing that the delete and subsequent inserts were lost after closing and reopening the database.
Workflow and refactoring
Over a sequence of 37 prompts the author and Fable iterated on fixes, producing 34 commits and substantial edits (totaling +1,321 and -190 lines across 30 files). Several design improvements were made along the way. Because the coding agent sometimes needed 10–15 minutes to process tasks, the author could prompt the next step from a phone while doing other things.
Final review was performed in GitHub’s PR interface on a laptop, and the documentation edits were inspected early to build an understanding of what changed.
Key changes: transaction model and API behavior
A core change in 4.0rc2 is that every write method — insert(), upsert(), update(), delete(), delete_where(), transform(), create_table(), create_index(), enable_fts() and raw write statements executed with db.execute() — runs in its own transaction and commits before returning. As a result, in most cases users no longer need to call commit() or close the database to persist changes.
There are two situations where transaction semantics still require user attention:
- Group multiple writes so they all succeed or fail together: use the db.atomic() context manager.
- Manually manage a transaction with db.begin(): if you open a transaction yourself, the library will not commit it for you.
Documentation notes that connections created with Python 3.12+ sqlite3.connect(..., autocommit=True) or autocommit=False behave differently and are not supported; the author adjusted behavior after noticing those modes broke most of the test suite.
GPT-5.5 review and additional findings
The author also had GPT-5.5 (and Codex Desktop) review the changes. This surfaced two further issues:
- db.query() previously called self.execute() and only afterward checked whether the statement returned rows, so db.query("update ...") could raise ValueError while the update had already been committed.
- INSERT ... RETURNING via db.query() only committed after the returned generator was fully exhausted. Calling db.query("insert ... returning ...") without iterating — or using next(db.query(...)) — left the transaction open and the write could be rolled back on close. This contradicted the documentation that said the effect takes place without iteration.
Claude Fable ran experiments to confirm both findings. The fixes make db.query() execute and commit write statements at call time and roll back a rejected non-row statement before raising an error.
Estimated session cost: $149.25
To increase the Fable allowance the author temporarily upgraded from a $100/month to a $200/month Claude Max subscription. Because access to Fable was changing on July 7 (the author dubbed the date the "July 7th Fablepocalypse"), the author wanted to finish the work before the change.
Using AgentsView inside the existing session, Claude produced a cost breakdown for the session (model and 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
The author notes that using cheaper models for subagents would be more economical in future projects, but the Max subscription made finishing this work practical within the available time.
Detailed changes and compatibility notes
The rc2 changelog and documentation list many specific changes. Highlights include:
- db.execute() write statements are now automatically committed unless a transaction is already open; code that relied on implicit rollback should open an explicit transaction with db.begin().
- db.query() now executes SQL at call time; SQL errors are raised at the call site, INSERT ... RETURNING commits immediately, and statements that return no rows now raise ValueError (and are rolled back before the error is raised).
- Python API validation errors now raise ValueError instead of AssertionError because bare assert statements can be skipped under Python’s -O optimization flag.
- table.upsert() and table.upsert_all() now raise PrimaryKeyRequired if a record is missing a primary key value or has None for a primary key column; previously such records could be silently inserted as new rows.
- db.enable_wal() and db.disable_wal() now raise sqlite_utils.db.TransactionError if called while a transaction is open; previously changing journal mode could silently commit an open transaction.
- The View class no longer exposes an enable_fts() method; attempting full-text search on a view now raises AttributeError. The sqlite-utils enable-fts command reports a clean error when pointed at a view.
- The no-op -d/--detect-types flag was removed from insert and upsert commands; --no-detect-types remains to disable detection.
- Database() now raises sqlite_utils.db.TransactionError if passed a connection created with Python 3.12+ sqlite3.connect(..., autocommit=True) or autocommit=False, since commit() and rollback() behave differently on those connections.
Other fixes:
- table.delete_where(), table.optimize() and table.rebuild_fts() now commit their changes (they previously left the connection in an open transaction).
- sqlite-utils drop-table now refuses to drop a view and drop-view refuses to drop a table; previously the wrong object type could be dropped silently.
- The new migrations system runs migrations inside transactions and records their application; migrations that raise exceptions are rolled back and remain pending so they can be retried. Non-transactional migrations (e.g. VACUUM) can opt out using @migrations(transactional=False).
- table.upsert() and table.upsert_all() now detect an existing table’s primary key, 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 insert a row consisting entirely of default values using INSERT INTO ... DEFAULT VALUES.
- Improvements to sqlite-utils migrate: --stop-before now errors on unknown migration names, works with older sqlite_migrate.Migrations class files, and --list is read-only and no longer creates a database file or migrations table.
New manual transaction APIs: db.begin(), db.commit() and db.rollback() complement the db.atomic() context manager.
Conclusion
sqlite-utils 4.0rc2 addresses critical transaction semantics issues and clarifies the library’s transactional behavior in documentation. The fixes remove cases where writes could be silently discarded and add explicit transaction controls and clearer error behavior. The process illustrates how human developers and generative models can be combined to find and fix subtle edge cases in a real-world open source project.
