Article · 2026-07-12 · 3 moments

sqlite-utils 4.1.1

Release: sqlite-utils 4.1.1 table.transform() now raises a TransactionError if called while a transaction is open with PRAGMAforeign_keys enabled and the table is referenced by foreign keys with destructive ON DELETE actions - CASCADE, SET NULL or SET DEFAULT. The pragma cannot be changed inside a transaction, so previously dropping the old table as part of the transform could fire those actions and silently delete or modify referencing rows. See Foreign keys and transactions for deta ✦ AI generated

Read original ↗

HOST Simon Willison

Mechanism◆

Because PRAGMA foreign_keys cannot be changed inside an open transaction, dropping the old table as part of table.transform() could previously fire destructive ON DELETE actions and silently delete or modify rows that referenced that table.

The bug arose because the foreign_keys pragma can't be toggled mid-transaction, so the table drop inside transform() could unintentionally cascade-delete or modify referencing rows.

transcript

Simon Willison: The pragma cannot be changed inside a transaction, so previously dropping the old table as part of the transform could fire those actions and silently delete or modify referencing rows.

provides context · 1

Fact◆

table.transform() now raises a TransactionError if it's called inside an open transaction with PRAGMA foreign_keys enabled when the table is referenced by foreign keys with destructive ON DELETE actions (CASCADE, SET NULL, or SET DEFAULT).

sqlite-utils 4.1.1 adds a safety check that stops table.transform() from running when it could silently trigger destructive cascading foreign key actions during an open transaction.

transcript

Simon Willison: table.transform() now raises a TransactionError if called while a transaction is open with PRAGMAforeign_keys enabled and the table is referenced by foreign keys with destructive ON DELETE actions - CASCADE, SET NULL or SET DEFAULT.

explains mechanism · 1provides context · 1

Fact

The CLI and Python API documentation now cross-reference each other, with CLI sections linking to equivalent Python API functionality and Python API sections linking back to corresponding CLI commands.

sqlite-utils 4.1.1 improves documentation navigation by adding mutual links between CLI command docs and their equivalent Python API docs.

transcript

Simon Willison: The CLI and Python API documentation now cross-reference each other: CLI sections link to the equivalent Python API functionality and Python API sections link back to the corresponding CLI command.

Highlight slides

The Hidden SQLite Bug in table.transform()✦ from: Because PRAGMA foreign_keys cannot be changed inside an open transaction, dropping the old table as part of table.transform() could previously fire destructive ON DELETE actions and silently delete or modify rows that referenced that table.sqlite-utils 4.1.1: safer table.transform()✦ from: table.transform() now raises a TransactionError if it's called inside an open transaction with PRAGMA foreign_keys enabled when the table is referenced by foreign keys with destructive ON DELETE actions (CASCADE, SET NULL, or SET DEFAULT).Why It Was Dangerous✦ from: Because PRAGMA foreign_keys cannot be changed inside an open transaction, dropping the old table as part of table.transform() could previously fire destructive ON DELETE actions and silently delete or modify rows that referenced that table.Destructive ON DELETE actions guarded✦ from: table.transform() now raises a TransactionError if it's called inside an open transaction with PRAGMA foreign_keys enabled when the table is referenced by foreign keys with destructive ON DELETE actions (CASCADE, SET NULL, or SET DEFAULT).

Related episodes

Video · Syntax

This Week in AI ⟡ OpenAI Agent Hacks Hugging Face ⟡ React Compiler Ported to Rust ⌁ Syntax Weekly

→

Article · Interconnects

5 useful things you'll learn in my new post-training textbook (shipping now!)

→

Article · Simon Willison's Weblog

sqlite-utils 4.1

→