sqlite-utils 4.0rc1 adds migrations and nested transactions

# sqlite-utils 4.0rc1 adds migrations and nested transactions Source: [https://simonwillison.net/2026/Jun/21/sqlite-utils-40rc1/](https://simonwillison.net/2026/Jun/21/sqlite-utils-40rc1/) 21st June 2026 [sqlite\-utils](https://sqlite-utils.datasette.io/en/latest/)is my combined Python library and CLI tool for working with SQLite databases\. It provides an extensive set of higher\-level operations on top of Python’s default[sqlite3 package](https://docs.python.org/3/library/sqlite3.html), including support for[complex table transformations](https://sqlite-utils.datasette.io/en/latest/cli.html#transforming-tables), automatic table creation[from JSON data](https://sqlite-utils.datasette.io/en/latest/cli.html#inserting-json-data)and a whole lot more\. I released[sqlite\-utils 4\.0rc1](https://sqlite-utils.datasette.io/en/latest/changelog.html#rc1-2026-06-21), the first release candidate for sqlite\-utils v4\. The major version bump indicates some \(minor\) backwards incompatible changes, so I’m interested in having people try this out before I commit to a stable release\. #### New feature: migrations There are two significant new features in this RC compared to the previous 4\.0 alphas\. The first is support for**database migrations**\. This isn’t a completely new implementation—it’s a slightly modified port of the[sqlite\-migrate](https://github.com/simonw/sqlite-migrate)package I released a few years ago\. I think that package has proved itself over time, so I’m now ready to bundle it with`sqlite\-utils`directly\. Here’s what a set of migrations in a`migrations\.py`file looks like: ``` from sqlite_utils import Database, Migrations migrations = Migrations("creatures") @migrations() def create_table(db): db["creatures"].create( {"id": int, "name": str, "species": str}, pk="id", ) @migrations() def add_weight(db): db["creatures"].add_column("weight", float) ``` This defines a set of two migrations, one creating the`creatures`table and another adding a column to it\. You can then run those migrations either using Python: ``` db = Database("creatures.db") migrations.apply(db) ``` Or with the command\-line`migrate`command: ``` sqlite-utils migrate creatures.db migrations.py ``` The system is deliberately small: it doesn’t provide reverse migrations, so any mistakes you make should be fixed by deploying a fresh migration to undo them\. Its predecessor has been used by[LLM](https://llm.datasette.io/)and various other projects for several years, so I’m confident that the design is stable and works well\. The new migrations feature[is documented here](https://sqlite-utils.datasette.io/en/latest/migrations.html)\. #### New feature: db\.atomic\(\) transactions This feature is a lot less exercised than migrations, so it deserves more attention from testers\. Previously,`sqlite\-utils`mostly left transaction management up to its users, via a`with db\.conn:`construct that reused the`sqlite3`mechanism directly\. SQLite supports nested transactions in the form of savepoints, so I wanted an abstraction that could make those as easy to use as possible\. I borrowed the terminology “atomic” from Django and Peewee\. Here’s what the new API looks like: ``` with db.atomic(): db.table("dogs").insert({"id": 1, "name": "Cleo"}, pk="id") try: with db.atomic(): db.table("dogs").insert({"id": 2, "name": "Pancakes"}) raise ValueError("skip this one") except ValueError: pass db.table("dogs").insert({"id": 3, "name": "Marnie"}) ``` More details[in the documentation](https://sqlite-utils.datasette.io/en/latest/python-api.html#transactions-with-db-atomic)\. #### Backwards incompatible changes The backwards incompatible changes in v4 were described in the alpha release notes\. For[4\.0a0](https://sqlite-utils.datasette.io/en/latest/changelog.html#a0-2025-05-08): > - Upsert operations now use SQLite’s`INSERT \.\.\. ON CONFLICT SET`syntax on all SQLite versions later than 3\.23\.1\. This is a very slight breaking change for apps that depend on the previous`INSERT OR IGNORE`followed by`UPDATE`behavior\. \([\#652](https://github.com/simonw/sqlite-utils/issues/652)\) - Python library users can opt\-in to the previous implementation by passing`use\_old\_upsert=True`to the`Database\(\)`constructor, see[Alternative upserts using INSERT OR IGNORE](https://sqlite-utils.datasette.io/en/latest/python-api.html#python-api-old-upsert)\. - Dropped support for Python 3\.8, added support for Python 3\.13\. \([\#646](https://github.com/simonw/sqlite-utils/issues/646)\) - `sqlite\-utils tui`is now provided by the[sqlite\-utils\-tui](https://github.com/simonw/sqlite-utils-tui)plugin\. \([\#648](https://github.com/simonw/sqlite-utils/issues/648)\) - Test suite now also runs against SQLite 3\.23\.1, the last version \(from 2018\-04\-10\) before the new`INSERT \.\.\. ON CONFLICT SET`syntax was added\. \([\#654](https://github.com/simonw/sqlite-utils/issues/654)\) And for[4\.0a1](https://sqlite-utils.datasette.io/en/latest/changelog.html#a1-2025-11-23): > - **Breaking change**: The`db\.table\(table\_name\)`method now only works with tables\. To access a SQL view use`db\.view\(view\_name\)`instead\. \([\#657](https://github.com/simonw/sqlite-utils/issues/657)\) - The`table\.insert\_all\(\)`and`table\.upsert\_all\(\)`methods can now accept an iterator of lists or tuples as an alternative to dictionaries\. The first item should be a list/tuple of column names\. See[Inserting data from a list or tuple iterator](https://sqlite-utils.datasette.io/en/latest/python-api.html#python-api-insert-lists)for details\. \([\#672](https://github.com/simonw/sqlite-utils/issues/672)\) - **Breaking change**: The default floating point column type has been changed from`FLOAT`to`REAL`, which is the correct SQLite type for floating point values\. This affects auto\-detected columns when inserting data\. \([\#645](https://github.com/simonw/sqlite-utils/issues/645)\) - Now uses`pyproject\.toml`in place of`setup\.py`for packaging\. \([\#675](https://github.com/simonw/sqlite-utils/issues/675)\) - Tables in the Python API now do a much better job of remembering the primary key and other schema details from when they were first created\. \([\#655](https://github.com/simonw/sqlite-utils/issues/655)\) - **Breaking change**: The`table\.convert\(\)`and`sqlite\-utils convert`mechanisms no longer skip values that evaluate to`False`\. Previously the`\-\-skip\-false`option was needed, this has been removed\. \([\#542](https://github.com/simonw/sqlite-utils/issues/542)\) - **Breaking change**: Tables created by this library now wrap table and column names in`"double\-quotes"`in the schema\. Previously they would use`\[square\-braces\]`\. \([\#677](https://github.com/simonw/sqlite-utils/issues/677)\) - The`\-\-functions`CLI argument now accepts a path to a Python file in addition to accepting a string full of Python code\. It can also now be specified multiple times\. \([\#659](https://github.com/simonw/sqlite-utils/issues/659)\) - **Breaking change:**Type detection is now the default behavior for the`insert`and`upsert`CLI commands when importing CSV or TSV data\. Previously all columns were treated as`TEXT`unless the`\-\-detect\-types`flag was passed\. Use the new`\-\-no\-detect\-types`flag to restore the old behavior\. The`SQLITE\_UTILS\_DETECT\_TYPES`environment variable has been removed\. \([\#679](https://github.com/simonw/sqlite-utils/issues/679)\) #### Try it out You can install the new RC like this: ``` pip install sqlite-utils==4.0rc1 ``` Or try the CLI version directly with`uvx`like this: ``` uvx --with sqlite-utils==4.0rc1 sqlite-utils --help ``` Come chat with us about it in the[sqlite\-utils Discord channel](https://discord.gg/Ass7bCAMDw), or file any bugs in[GitHub Issues](https://github.com/simonw/sqlite-utils/issues)\.