MechanismArticle

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. ✦ AI generated

Simon Willison · Simon Willison's Weblog · 2026-07-07 · original ↗

Share card ↓Export .md Export .txt

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.

Read full article ↗excerpt · fair-use quotation

✳ SQLite ✳ table.transform()

Around this claim

Mechanism · 1

sqlite-utils 4.2 adds lots of improvements to the table.transform() feature, which supports complex alter table operations by creating a fresh table, copying across the data, and then dropping and replacing the old one.Simon Willison · Simon Willison's Weblog · conf 70%

This moment responds to

explains mechanism → There is no way to ALTER an existing SQLite table to make it strict; you have to copy the data out of the non-strict table into a new strict one.Evan Hahn · Simon Willison's Weblog explains mechanism → table.transform()/table.transform_sql() and the sqlite-utils transform CLI command now accept strict=True/False or --strict/--no-strict to change a table's SQLite strict mode, since transform already copies data into a new table and can therefore switch strictness in either direction.Simon Willison · Simon Willison's Weblog explains mechanism → 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.Simon Willison · Simon Willison's Weblog provides context → 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).Simon Willison · Simon Willison's Weblog provides context → 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.Simon Willison · Simon Willison's Weblog