source: simon willison: sqlite-utils 4.0rc1 adds migrations and nested transactions
level: technical
sqlite-utils 4.0rc1 is the first release candidate for version 4 of the python library and cli tool for sqlite. it includes two major new features: database migrations and nested transactions. the migration system is a port of the existing sqlite-migrate package, now bundled directly. migrations are defined in a python file using decorators, and can be applied via python or the command line. the system is intentionally minimal, with no reverse migrations; errors are fixed by adding new migrations.
the second feature is atomic nested transactions, using the term from django and peewee. the new db.atomic() context manager handles savepoints automatically, making nested transactions easier to use. this feature is less tested than migrations and needs more user feedback. the release also includes several backwards incompatible changes from earlier alphas, such as using insert on conflict for upserts, dropping python 3.8 support, and changing the default float type to real.
other breaking changes include restricting db.table() to tables only, with db.view() for views, and wrapping schema names in double quotes instead of square brackets. type detection is now default for csv and tsv imports. users can install the release candidate via pip or try the cli with uvx. feedback is welcomed on discord or github issues.
why it matters: these updates improve database management for ai and data science workflows by simplifying schema changes and transaction handling in sqlite.
source: simon willison: sqlite-utils 4.0rc1 adds migrations and nested transactions