sqlite-utils 4.0rc1 adds migrations and nested transactions
sqlite-utils 4.0rc1 adds database migrations (ported from sqlite-migrate) and a db.atomic() method for nested transactions. The release candidate also introduces several breaking changes, including updated upsert syntax, dropped Python 3.8 support, and default type detection for CSV imports.
Background
Simon Willison is a well-known Python developer and the creator of Datasette, an open-source tool for exploring and publishing SQLite databases. sqlite-utils is his companion Python library and command-line tool that makes it easier to work with SQLite databases — handling tasks like importing JSON data, transforming tables, and running queries.
This is the release candidate for version 4.0, a major update. The two headline additions are:
- **Migrations**: a system for defining schema changes (creating tables, adding columns) as versioned, repeatable functions, so database structure evolves cleanly alongside code.
- **Nested transactions**: a new `db.atomic()` API that wraps SQLite's savepoint mechanism, letting you commit or roll back inner operations independently of outer ones — useful when a complex operation partially fails.
Version 4 also ships several breaking changes (e.g., the default floating-point column type changed from `FLOAT` to `REAL`, upsert behavior changed, and separate access for tables vs. views) and drops Python 3.8 support.