sqlite-utils 4.0rc3 swaps foreign_keys to a namedtuple, breaks old indexing

TL;DR

• sqlite-utils 4.0rc3 returns foreign_keys as a ForeignKey namedtuple with an is_compound flag.
• Old-shape indexing breaks silently: column reads None on compound keys instead of raising.
• Case-insensitive column matching aligns the library with SQLite’s native convention across many call sites.
• rc2 cost ~$149.25 in Claude Fable 5 API credits, a figure Willison publishes as standard practice.

Today’s single AI-tech read is a point-release with sharper edges than the version number suggests. sqlite-utils 4.0rc3 finally teaches table.foreign_keys about compound relationships by returning a ForeignKey namedtuple with an is_compound flag — a clean model, but one that quietly breaks any caller still indexing the old flat shape. On compound keys the old column slot now reads None instead of a name, and nothing raises. The same release folds in case-insensitive column matching to align with SQLite’s own convention, touching enough call sites that upgrading is a read, not a bump. Worth the attention as a worked example of the maintenance tax library authors are choosing to pay so downstream agent code doesn’t have to — and, per Willison’s now-standard disclosure, of what an rc round actually costs (~$149.25 in Claude Fable 5 credits for rc2).

sqlite-utils 4.0rc3 reshapes foreign_keys for compound FKs

Source: simon-willison · published 2026-07-06

TL;DR

• table.foreign_keys now returns a ForeignKey namedtuple with an is_compound flag for multi-column relationships.
• Code that indexed the old flat shape breaks silently — column reads None on compound keys instead of a name.
• Case-insensitive column matching aligns sqlite-utils with SQLite’s native convention and touched many call sites at once.
• rc2 cost ~$149.25 in Claude Fable 5 API credits, a figure Willison publishes as standard practice.

The breaking change to watch

Simon Willison had hoped to ship 4.0 stable this weekend. Instead, rc3 landed with two changes that alter public contracts and therefore had to make the 4.0 cut: compound foreign key support and case-insensitive column matching ¹.

The FK change is the concrete migration hazard. table.foreign_keys now yields a ForeignKey namedtuple with an is_compound boolean. For compound keys, column and other_column are None and the real data lives in columns / other_columns tuples ²:

list(db["courses"].foreign_keys)
## [ForeignKey(table='courses', column=None, other_table='departments',
##             other_column=None, columns=('campus_id','dept_id'),
##             other_columns=('campus_id','dept_id'), is_compound=True)]

Any code that unpacked the old flat structure — dashboards, schema diff tools, migration generators — will read None where it used to read a column name and fail in interesting ways. Check is_compound first, or you’ll ship the bug.

Case-insensitivity, everywhere

The second breaking item is quieter but wider. sqlite-utils now follows SQLite’s convention that column identifiers compare case-insensitively, a change Willison notes “turned out to touch a bunch of different places at once” ¹. In practice this cleans up long-standing paper cuts in add_missing_columns() and upsert(), where a dict key of "Email" against a stored column "email" would previously create a duplicate column or crash. It’s the right default, but if you were relying on case-sensitive collisions to detect schema drift, that signal is gone.

The AI-authored changelog debate

The rc2→rc3 diff is largely the product of Claude Fable 5 and GPT-5.5 grinding through the issue tracker. rc2 alone cost roughly $149.25 in API credits ³, a number Willison publishes as a matter of habit. Skeptics have paired it with the METR result that experienced developers were 19% slower with AI despite feeling 20% faster ³, and asked whether an ever-expanding release-candidate changelog is a productivity story or a churn story — models eager to please generating fixes that spawn more fixes.

The disclosure norm (model, cost, prompts posted alongside the release) is worth noting on its own. Even critics of AI-assisted maintenance concede it’s the right way to do it in public.

Where the design is contested

On Hacker News, jph00 and others used the rc2 thread to promote apsw-utils, a fork that ports the same ergonomics onto APSW. Their argument: sqlite-utils inherits the stdlib sqlite3 module’s transaction quirks — implicit BEGIN before DML, none before DDL — and a thin APSW wrapper matches SQLite’s own documented semantics far more cleanly ⁴.

That’s not a hypothetical complaint. Testers of rc1’s new migration system hit a concrete regression where migrations run inside an implicit transaction conflict with VACUUM, which SQLite forbids inside a transaction, breaking CI pipelines that vacuum after schema changes ⁵. Combined with the forward-only, no-down-migrations philosophy, this is the pressure point 4.0 stable will have to answer.

The compound-FK reshape is the change you’ll notice on upgrade day. The transaction-model debate is the one that decides whether 4.x has a 5.x waiting behind it.

Footnotes

1. sqlite-utils changelog (rc3, 2026-07-05) — https://sqlite-utils.datasette.io/en/latest/changelog.html

   Support for introspecting and creating compound foreign keys … a subtle breaking change to table.foreign_keys … sqlite-utils now follows SQLite’s convention for case insensitive column names, which turned out to touch a bunch of different places at once.

   ↩ ↩²

2. sqlite-utils Python API docs (compound FK example) — https://sqlite-utils.datasette.io/en/latest/python-api.html

   list(db[“courses”].foreign_keys) returns [ForeignKey(table=‘courses’, column=None, other_table=‘departments’, other_column=None, columns=(‘campus_id’,‘dept_id’), other_columns=(‘campus_id’,‘dept_id’), is_compound=True)]

   ↩

3. Medium: ‘An AI wrote 576,000 lines to replace SQLite’ — https://medium.com/write-a-catalyst/an-ai-wrote-576-000-lines-to-replace-sqlite-7ea538826d72

   Willison reported the rc2 cycle cost roughly $149.25 in Claude Fable API credits — a ‘cents-per-feature’ economics that critics call cognitive debt, noting a METR study found experienced devs were 19% slower with AI despite feeling 20% faster.

   ↩ ↩²

4. Hacker News thread on 4.0rc2 (jph00 comment) — https://news.ycombinator.com/item?id=48792563

   sqlite-utils is too tethered to Python’s standard library transaction handling … apsw-utils ports the utility ergonomics onto APSW, which matches SQLite’s own documented transaction semantics.

   ↩

5. AI Weekly digest of sqlite-utils 4.0rc1 — https://aiweekly.co/alerts/sqlite-utils-40rc1-brings-migrations-and-nested-transactions

   the new default of running migrations within a transaction can conflict with operations like VACUUM, which SQLite prohibits inside transactions … led to unexpected failures in CI/CD pipelines

   ↩