Article · 2026-07-07 · 6 moments

sqlite-utils 4.0, now with database schema migrations

This morning I released sqlite-utils 4.0, the 124th release of that project and the first major version bump since 3.0 in November 2020. In addition to some small but significant breaking changes (described in this upgrade guide), this version introduces three major features: database migrations, nested transactions (via a new db.atomic() method), and support for compound foreign keys. Database schema migrations using sqlite-utils Schema migrations define a sequence of changes to be made to a ✦ AI generated

Read original ↗

HOST Simon Willison

Context

After three years of using the standalone sqlite-migrate package and gaining confidence in its design, the author promoted it into sqlite-utils itself so the whole sqlite-utils/Datasette/LLM ecosystem gets migrations by default.

sqlite-migrate, previously a separate beta package, has been folded into sqlite-utils 4.0 after three years of real-world validation, with a compatibility shim keeping old dependents working.

transcript

Simon Willison: I've used that package in enough places now that I'm confident in the design, so I've decided to promote it to a feature of sqlite-utils to make it available by default to all of the other tools in the growing sqlite-utils/Datasette/LLM ecosystem.

Anecdote

Claude Fable 5 handled integrating compound foreign key creation into the library, and the resulting API design felt exactly consistent with how the rest of sqlite-utils already worked.

The author had Claude Fable 5 design the compound foreign key creation API, and found the resulting design fit naturally with the rest of sqlite-utils.

transcript

Simon Willison: I started with a breaking change to the table.foreign_keys introspection method, and then decided to see if Claude Fable 5 could handle the more fiddly job of integrating compound foreign key creation into the library. The API design it helped create felt exactly right to me - consistent with how the rest of the library worked already.

Claim

Rollback support was deliberately left out of sqlite-utils migrations because it's rarely used in practice, and an easy alternative is simply copying the database file before applying migrations.

Unlike Django's migrations, sqlite-utils skips rollback support by design, reasoning that copying the SQLite file beforehand is an adequate substitute.

transcript

Simon Willison: I decided to skip rollback, since in my experience it's a feature that is rarely used. With a SQLite project, an easy way to achieve rollback is to create a copy of your database file before you apply the migrations!

Fact◆

sqlite-utils 4.0 is the first major version bump since 3.0 in November 2020, and introduces three major features: database migrations, nested transactions via db.atomic(), and support for compound foreign keys.

The author announces the release of sqlite-utils 4.0, the first major version since late 2020, headlined by three big features: schema migrations, nested transactions, and compound foreign keys.

transcript

Simon Willison: This morning I released sqlite-utils 4.0, the 124th release of that project and the first major version bump since 3.0 in November 2020. In addition to some small but significant breaking changes... this version introduces three major features: database migrations, nested transactions (via a new db.atomic() method), and support for compound foreign keys.

explains mechanism · 1

Mechanism

table.transform() implements the SQLite-documentation-recommended pattern for altering tables beyond what ALTER TABLE supports: create a new temp table with the new schema, copy the data across, then drop the old table and rename the new one into place.

sqlite-utils migrations rely on table.transform(), which follows SQLite's own recommended workaround for altering table schemas in ways ALTER TABLE cannot handle.

transcript

Simon Willison: table.transform() implements the pattern recommended by the SQLite documentation - create a new temporary table with the new schema, copy across the data, then drop the old table and rename the temporary one in its place.

explains mechanism · 1

Anecdote◆

When given an open-ended review prompt, Claude Fable 5 wrote 12 scratch scripts and found 4 release blockers plus 10 additional issues, far outperforming GPT-5.5 xhigh in Codex Desktop, which wrote only 5 scripts and found nothing particularly interesting.

Given the same open-ended pre-release review task, Claude Fable 5 dramatically outperformed GPT-5.5, surfacing 14 real issues (including transaction and foreign-key bugs) via self-written repro scripts, versus GPT-5.5 finding nothing notable.

transcript

Simon Willison: Fable 5 wrote 12 scripts, identified 4 release blockers and 10 additional issues in its report, and built a neat combined repro script... I found myself agreeing with almost all of them. Here's the PR with 16 commits where we worked through them in turn.

gives example · 1rebuts · 1

Highlight slides

sqlite-utils 4.0 released✦ from: sqlite-utils 4.0 is the first major version bump since 3.0 in November 2020, and introduces three major features: database migrations, nested transactions via db.atomic(), and support for compound foreign keys.Fable 5 vs GPT-5.5: Open-Ended Review Test✦ from: When given an open-ended review prompt, Claude Fable 5 wrote 12 scratch scripts and found 4 release blockers plus 10 additional issues, far outperforming GPT-5.5 xhigh in Codex Desktop, which wrote only 5 scripts and found nothing particularly interesting.Three major new features✦ from: sqlite-utils 4.0 is the first major version bump since 3.0 in November 2020, and introduces three major features: database migrations, nested transactions via db.atomic(), and support for compound foreign keys.Fable 5's Findings Were Real and Actionable✦ from: When given an open-ended review prompt, Claude Fable 5 wrote 12 scratch scripts and found 4 release blockers plus 10 additional issues, far outperforming GPT-5.5 xhigh in Codex Desktop, which wrote only 5 scripts and found nothing particularly interesting.

Related episodes

Video · Syntax

Do You Read The Code? ⟡ HTTP Gets a NEW Method ⟡ Codex Gets Sol Ultra ⌁ Syntax Weekly ⌁

→

Video · The Cognitive Revolution

Alignment with Awakening: Davidad on Moral Realism, AI Wisdom, & why His p(Doom) is Down to 5%

→

Video · Syntax

LGTM, Ship It: The AI Code Review Problem

→