source: simon willison: sqlite-utils 4.0, now with database schema migrations
level: technical
sqlite-utils 4.0 is a major release of the python library for sqlite database manipulation. it adds three main features: database schema migrations, nested transactions via db.atomic(), and support for compound foreign keys. migrations let you define a sequence of changes to a database schema in python files, using the table.transform() method for complex alterations that sqlite's alter table cannot do directly. the library tracks applied migrations in a _sqlite_migrations table and can apply pending ones automatically.
the migration system is simpler than django's, with no automatic generation from models or rollback support. instead, users write migration functions manually. the design was previously available as a separate package called sqlite-migrate, which is now merged into sqlite-utils. nested transactions use sqlite savepoints, allowing safe, composable operations. compound foreign keys can now be created, introspected, and transformed. the release also includes breaking changes like upsert improvements, immediate query execution, and automatic column type detection for csv imports.
the development process used ai assistants like claude fable 5 to review changes, write tests, and identify bugs. a review prompt against the release candidate found 10 issues, including transaction handling bugs and api inconsistencies. the author notes that ai help significantly improved the release quality. the upgrade guide and release notes were also written by ai models and reviewed for accuracy.
why it matters: schema migrations help ai and data science projects manage evolving sqlite database structures reliably, reducing manual errors and enabling reproducible workflows.
source: simon willison: sqlite-utils 4.0, now with database schema migrations