sqlite-utils 4.0, now with database schema migrations

Simon Willison released sqlite-utils 4.0 this morning. It is the 124th release of the project and the first major version bump since 3.0 in November 2020. The update introduces three major features: database migrations, nested transactions via a new `db.atomic()` method, and support for compound foreign keys.

Database schema migrations

Schema migrations define a sequence of changes for a SQLite database. They include a mechanism for tracking which migrations have been applied and running any that are pending.

Developers define migrations in Python files using the sqlite-utils library. This includes a powerful `table.transform()` method that provides enhanced alter table capabilities not supported by SQLite’s standard `ALTER TABLE` statement.

The `table.transform()` method implements the pattern recommended by the SQLite documentation. It creates a new temporary table with the new schema, copies the data across, drops the old table, and renames the temporary one in its place.

Here is an example migration file. It creates a table called `creatures`, adds an additional column in a second step, then changes the types of two columns in a third:

from sqlite_utils import 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)

@migrations()
def change_column_types(db):
    db["creatures"].transform(types={"species": int, "weight": str})

Save that as `migrations.py` and run it against a fresh database like this:

uvx sqlite-utils migrate data.db migrations.py

Then if you check the schema of that database:

uvx sqlite-utils schema data.db

You’ll see this SQL:

CREATE TABLE "_sqlite_migrations" (
   "id" INTEGER PRIMARY KEY,
   "migration_set" TEXT,
   "name" TEXT,
   "applied_at" TEXT
);
CREATE UNIQUE INDEX "idx__sqlite_migrations_migration_set_name"
    ON "_sqlite_migrations" ("migration_set", "name");
CREATE TABLE "creatures" (
   "id" INTEGER PRIMARY KEY,
   "name" TEXT,
   "species" INTEGER,
   "weight" TEXT
);

The `_sqlite_migrations` table keeps track of which migration functions have been run. The `creatures` table above is the schema after all three migrations have been applied.

To see a list of migrations, both pending and applied, run this:

uvx sqlite-utils migrate data.db migrations.py --list

Output:

Migrations for: creatures

  Applied:
    create_table - 2026-07-07 17:58:41.360051+00:00
    add_weight - 2026-07-07 17:58:41.360608+00:00
    change_column_types - 2026-07-07 18:01:15.802000+00:00

  Pending:
    (none)

If you do not specify a migrations file, the `sqlite-utils migrate data.db` command scans the current directory and its subdirectories for files called `migrations.py` and applies any `Migrations()` instances it finds in them.

You can also execute migrations from Python code using the `migrations.apply(db)` method. This is useful for building tools that manage their own database schemas over multiple versions. My own LLM tool has been using a version of this pattern for several years now, as shown in `llm/embeddings_migrations.py`.

Prior art

My favourite implementation of this pattern remains Django’s Migrations, developed by Andrew Godwin based on his earlier project South.

Fun fact: Andrew, Russ Keith-Magee, and I presented our competing approaches to schema migrations for Django on the Schema Evolution panel at the very first DjangoCon back in 2008. My attempt was called dmigrations, developed with a team at Global Radio in London.

Django’s migrations can be automatically generated from model definitions and include the ability to roll back to a previous version. The sqlite-utils approach is deliberately simpler. Unlike Django, sqlite-utils encourages programmatic table creation rather than a model definition ORM, so there is nothing we can use to automatically generate migrations.

I decided to skip rollback, since in my experience it is a feature that is rarely used. With a SQLite project, an easy way to achieve rollback is to create a copy of your database file before you apply the migrations.

Migrating from sqlite-migrate

The design of sqlite-utils migrations is three years old now. I had originally released it as a separate package called sqlite-migrate, which never quite graduated beyond a beta release.

I’ve used that package in enough places now that I’m confident in the design, so I’ve decided to promote it to a feature of sqlite-utils to make it available by default to all of the other tools in the growing sqlite-utils/Datasette/LLM ecosystem.

I made one last release of sqlite-migrate, which switches it to depend on sqlite-utils>=4 and replaces the `__init__.py` file with the following:

from sqlite_utils import Migrations

__all__ = ["Migrations"]

Any existing project that depends on sqlite-migrate should continue to work without alterations.

Everything else in sqlite-utils 4.0

Here are the release notes for this version, with some inline annotations:

The 4.0 release includes some minor backwards-incompatible fixes (hence the major version number bump) and introduces three major new features:

• Source Read original →