source: simon willison: sqlite-utils 4.0rc1
level: technical
simon willison released sqlite-utils 4.0rc1, a python cli utility and library for manipulating sqlite databases. the update introduces two major features: a built-in migration system and support for nested transactions. the migration system lets developers define schema changes in python code and apply them incrementally, similar to tools like alembic but tailored for sqlite. nested transactions use sqlite savepoints, allowing multiple levels of commit and rollback within a single connection.
the migration feature works by storing a `_sqlite_utils_migrations` table in the database to track applied changes. users write migration functions that receive a database connection and can execute arbitrary sql or use the sqlite-utils api. the cli provides commands to create, apply, and list migrations. nested transactions are exposed through a context manager, making it easier to compose database operations without worrying about transaction state conflicts.
this release candidate also includes smaller improvements like better type handling for json columns and fixes for edge cases in table introspection. the project remains a key building block in the datasette ecosystem and is widely used for small to medium data projects. the new features aim to reduce boilerplate and make database evolution safer, especially in applications that need to manage schema changes over time without external migration tools.
why it matters: simplifies schema management and safe transactional logic in lightweight sqlite projects, reducing dependency on heavier database tooling.