source: simon willison: sqlite-utils 4.1.1
level: technical
sqlite-utils 4.1.1 is a new release of the python cli utility and library for manipulating sqlite databases. the main change fixes a bug in the table.transform() method. previously, if a transaction was open with pragma foreign_keys enabled and the table had foreign keys with destructive on delete actions like cascade, set null, or set default, dropping the old table during transform could silently delete or modify referencing rows. now, table.transform() raises a transactionerror in that situation, preventing unintended data loss.
the issue occurred because the foreign_keys pragma cannot be changed inside a transaction. the transform method needs to drop and recreate the table, but with foreign key enforcement on, the drop could trigger cascading actions. the fix alerts users to the conflict so they can handle it explicitly, for example by disabling foreign keys before starting the transaction or by using the workarounds documented in the foreign keys and transactions section.
the release also improves documentation. the cli and python api docs 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. this makes it easier for users to switch between the command-line and programmatic interfaces.
why it matters: data scientists using sqlite-utils for etl or data cleaning can avoid silent data corruption when transforming tables with foreign keys.