AI Weekly Malaysia

Back to items Summaries

sqlite-utils 4.0rc1 adds migrations and nested transactions

ID

1987

Status

new

Published

22 Jun 2026, 7:35 AM

Fetched

27 Jun 2026, 8:23 PM

Provider

Simon Willison

Category

developer-ai

Original URL

https://simonwillison.net/2026/Jun/21/sqlite-utils-40rc1/

Source URL

https://simonwillison.net/atom/everything/

Excerpt

sqlite-utils 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, including support for complex table transformations, automatic table creation from JSON data and a whole lot more. I released sqlite-utils 4.0rc1, 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 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 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. 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. Backwards incompatible changes The backwards incompatible changes in v4 were described in the alpha release notes. For 4.0a0: Upsert operations now use SQLite's INSERT ... ON CONFLICT SET syntax on all SQLite versions later than 3.23.1