sqlite-utils 4.0rc1: migrations for agent local state

Last verified: 2026-06-23.

In short: If an agent can write to a SQLite-backed queue, crawler log, content index, eval store, or task database, migrations and transactions are not polish. They are the safety rails that keep generated code from turning local state into mystery meat. sqlite-utils 4.0rc1 is a useful hook because the release adds a bundled migrations system and a db.atomic() context manager with nested transaction support. Treat 4.0rc1 as a release candidate, not a silent production upgrade.

Simon Willison announced sqlite-utils 4.0rc1 on June 21, 2026. The matching GitHub release says the release adds a new database migrations system, incorporating functionality that was previously provided by the separate sqlite-migrate plugin. It also says the release adds a new db.atomic() context manager that provides nested transaction support using SQLite savepoints.

The project documentation describes sqlite-utils as a CLI tool and Python library for manipulating SQLite databases. The same documentation says it helps create SQLite databases from existing data and is not intended to be a full ORM. That matters for agent work: sqlite-utils is a practical state tool, not a magic safety layer around arbitrary writes.

For broader permission design, pair this with Toolhalla's agent write-permission UX checklist. For benchmark-style agent claims, keep the coding-agent benchmark methodology checklist separate from this state-safety checklist.

Source/evidence map

What remains unverified

Toolhalla has not run sqlite-utils 4.0rc1 against a production agent state database. This article is an operator checklist built from primary release/documentation sources. Before applying it to your own state store, verify the exact sqlite-utils version you install, run migrations against a copy, and confirm your own read-back proof.

What sqlite-utils 4.0rc1 changes

The release has two changes that are especially relevant when agents write local state.

First, migrations are now part of sqlite-utils itself. The release note says migration sets can be defined with sqlite_utils.Migrations and applied with the sqlite-utils migrate command or the Python migrations API. The post's example command is:

sqlite-utils migrate creatures.db migrations.py

Second, db.atomic() now supports nested transactions. The release note says it uses SQLite savepoints, which is the important word. SQLite documents savepoints as named transaction marks inside a transaction. In this checklist, that means a substep can have a narrower rollback boundary, provided the caller handles exceptions deliberately and verifies the resulting state.

Those features do not make agent writes safe by default. They give you better primitives for making agent writes reviewable, repeatable, and easier to recover from.

Why agents make this urgent

A human developer usually changes a local database slowly: edit a script, run a command, inspect the output, fix the migration, try again. An agent can do the same loop much faster and with more confidence than the situation deserves.

That creates a specific failure mode. The agent does not merely produce a bad answer; it mutates the state that future runs depend on. A crawler queue can mark work as done when it failed. A content index can point at stale files. An eval database can mix old schema rows with new schema rows. A task runner can retry the wrong unit because the state transition was written outside the real success boundary.

For these systems, the safety question is not "does SQLite work?" It is: what has to be true before an agent is allowed to write into this database?

Migration checklist before agent writes

Use a migration file before the agent changes schema. Do not let generated code discover that a column is missing and patch the database ad hoc during the work run.

A practical checklist:

For production-like state, take a database copy first. Run the migration against the copy. Run the agent against the copy. Only then decide whether the real database should move.

Transaction checklist before agent writes

Transactions should match the business boundary, not the code boundary. If the unit of work is "fetch source, parse source, update queue row, write artifact metadata," then the database state should not claim success halfway through that unit.

Use db.atomic() around related writes. Use nested db.atomic() blocks only when a substep has a clear recovery path and its failure should not poison the outer operation. Log the failure at the same boundary where you roll back the substep, otherwise the next run will not know what actually happened.

Questions to answer before enabling writes:

• What is the smallest state transition that must be atomic?
• Which errors should roll back the whole task?
• Which errors should roll back only a substep?
• What row proves that the write happened?
• What read-back check runs after the write?
• What makes the operation idempotent if the agent retries?

Nested transactions are a tool for partial failure boundaries. They are not a substitute for validation, locking, backups, or a human approval path for dangerous writes.

Upgrade checklist for sqlite-utils v4

4.0rc1 is a release candidate. The GitHub release was marked prerelease during this check, and PyPI package metadata reported sqlite-utils 3.39 as the current stable package version. That means the safe default is to test, not auto-upgrade.

Before moving an agent workflow to sqlite-utils v4, check the compatibility items named in the release note and GitHub release rather than relying on memory:

1. Python version support for your runner; the release material notes Python 3.8 was dropped earlier in the v4 line and Python 3.13 support was added.

2. Upsert semantics in scripts that rely on conflict handling; the release material describes a change to INSERT ... ON CONFLICT.

3. Any code that treats tables and views through the same API surface; the v4 change list separates table and view behavior.

4. CSV or TSV ingest assumptions if the database is fed from files; the release material flags type-detection changes.

5. Whether sqlite-utils tui is in the core path you use; the release material says it moved to a plugin.

6. Whether migrations run cleanly against a copy of the real database.

7. Whether the agent still produces the same read-back proof after the migration.

If that list feels too heavy, that is a sign the workflow should not be letting an agent write the real state yet.

Pattern: content-system state

A content machine often has several small SQLite-backed state tables: source candidates, fetched URLs, dedupe hashes, generated drafts, editor verdicts, publish attempts, and social queues. The dangerous bug is not one bad row. The dangerous bug is a row that makes the next job skip a necessary check.

A safer pattern is:

1. Migrate the schema in a reviewed step.

2. Insert a candidate row with a stable key.

3. Write source-read evidence before draft status.

4. Mark draft status only after the artifact exists.

5. Mark publishable only after strict gate and editor verdict.

6. Mark published only after live read-back.

That pattern works because every status has proof behind it. If an agent can only move state when it also writes the proof, the database becomes easier to inspect after a failure.

Toolhalla update pack

Use this article to tag three Toolhalla surfaces:

• sqlite-utils — Python/CLI SQLite utility, release-candidate watch item, no affiliate fit.
• SQLite agent state — local queues, crawlers, eval stores, and content-system state that need migration and transaction controls.
• database-agent safety — pair with write-permission UX, idempotency, backups, and read-back proof before enabling writes.

Suggested internal cluster: agent write permissions, database agents, local state, migrations, transaction boundaries, and content-pipeline state.

FAQ

Is sqlite-utils an ORM?

No. The official documentation says sqlite-utils is not intended to be a full ORM. It is a CLI tool and Python library for manipulating SQLite databases.

Are sqlite-utils migrations reversible?

Do not assume reverse migrations. The 4.0rc1 release material emphasizes forward migration sets. If a migration is wrong, plan the repair as another forward migration unless the current docs for your exact version say otherwise.

What does db.atomic() protect against?

It helps group related database writes into transaction boundaries. In 4.0rc1, nested transaction support uses SQLite savepoints. It does not validate the data, prove that the source was correct, or guarantee that an agent chose the right state transition.

Should an agent write production SQLite databases?

Only after the write path has migrations, backups, transaction boundaries, idempotent keys, logging, and read-back. If those controls are missing, keep the agent in draft mode and require a human or a guarded publisher to perform the final mutation.