sqlite-utils 4.0 was released on 2026-07-07. It is the project's 124th release and the first major version bump since 3.0 in November 2020. Alongside a number of smaller breaking changes (documented in the upgrade guide), the release introduces three headline features: database schema migrations, nested transactions via a new db.atomic() context manager, and support for compound foreign keys.
Key additions
- Database schema migrations
- Nested transactions with db.atomic()
- Compound foreign keys (creation, transformation and introspection)
Together these features make evolving SQLite schemas and writing safe multi-step schema changes much easier.
Database schema migrations
Migrations let you define a sequence of changes to a SQLite database and record which migration steps have been applied. In sqlite-utils, migrations are written as Python files using the Migrations helper. The library's table.transform() method implements an enhanced alter-table pattern not directly supported by SQLite: create a temporary table with the new schema, copy the data across, drop the old table and rename the temporary table.
A typical migration file defines a Migrations("creatures") instance and multiple functions decorated to register each migration step. In the article's example the steps create a creatures table, add a weight column, then change the types of two columns. Save that as migrations.py and run:
- uvx sqlite-utils migrate data.db migrations.py
- uvx sqlite-utils schema data.db
sqlite-utils creates a tracking table called _sqlite_migrations that records which migration functions have run. After applying the example migrations, the creatures table has schema
CREATE TABLE "creatures" ("id" INTEGER PRIMARY KEY, "name" TEXT, "species" INTEGER, "weight" TEXT);
You can list applied and pending migrations with:
uvx sqlite-utils migrate data.db migrations.py --list
If you don't pass a migrations file to the migrate command, sqlite-utils will scan the current directory and subdirectories for migrations.py files and apply any Migrations() instances it finds. Migrations can also be executed programmatically with migrations.apply(db), which is useful for tools that manage their own schemas.
Promotion of sqlite-migrate into sqlite-utils
The migration design began as a separate package, sqlite-migrate. That package has now been promoted into sqlite-utils so migrations are available by default across the sqlite-utils / Datasette / LLM ecosystem. The last sqlite-migrate release switches its dependency to sqlite-utils>=4 and simply re-exports Migrations, so existing projects depending on sqlite-migrate should continue to work.
Other notable changes and breaking changes
Because the release fixes a number of edge-case design issues, it includes some backwards-incompatible changes:
-
Upserts now use SQLite's INSERT ... ON CONFLICT ... DO UPDATE SET syntax, detect existing table primary keys automatically, and reject records that are missing required primary key values. (PR #652)
-
db.query() now executes immediately and rejects statements that do not return rows; use db.execute() for writes and DDL. This is likely the most disruptive breaking change.
-
CSV and TSV imports now detect column types by default, and inserts into existing tables preserve those tables' column types. (PR #679)
-
table.extract() and extracts= no longer create lookup table records for all-null values. (Issue #186 — a longstanding bug originally opened in October 2020.)
See the "Upgrading from 3.x to 4.0" guide for full details on breaking changes.
The release notes for the pre-release cycle are available for 4.0a0, 4.0a1, 4.0rc1, 4.0rc2, 4.0rc3 and 4.0rc4.
Nested transactions with db.atomic()
Transactions were a pain point in earlier versions. The new db.atomic() context manager uses SQLite savepoints so atomic blocks can be nested. That allows transactions inside transactions, which simplifies reasoning about safety during migrations and other multi-step operations. Example usage from the release:
with db.atomic(): db.table("dogs").insert({"id": 1, "name": "Cleo"}, pk="id") db.table("dogs").insert({"id": 2, "name": "Pancakes"})
Because SQLite supports savepoints, db.atomic() can be nested and inner failures can be rolled back without necessarily aborting outer work.
Compound foreign keys
sqlite-utils 4.0 adds support for compound foreign keys—creation, transformation and introspection—via table.foreign_keys (PR #594). This was identified as an important breaking-change candidate early in the 4.0 planning and therefore included in the major release.
Development process and LLM assistance
The release notes and upgrade guide were written with help from Claude Fable 5, Claude Opus 4.8 and GPT-5.5. The author reports that model assistance was particularly helpful in reviewing changes, generating test scripts and identifying bugs. Claude Fable 5 produced a set of reproducible scripts and reports that surfaced multiple issues; the article lists a number of concrete failures that Fable found (transaction edge-cases, query token scanning issues, autocommit behavior, compound FK ordering, dataclass hashability regressions, CSV import type regressions, migrate command edge-cases and others). These findings were addressed across a multi-commit PR.
The author states that the release quality benefited substantially from this model-assisted review process.
Conclusion
sqlite-utils 4.0 is a significant quality and feature release: built-in schema migrations, nested transaction support and compound foreign keys make it easier to manage evolving SQLite schemas. The release does introduce some breaking changes, so projects upgrading from 3.x should consult the upgrade guide before migrating.
