sqlite-utils 4.0, now with database schema migrations

Simon Willison’s Weblog

Subscribe

Sponsored by: Sonar — Gartner just named Sonar a Leader in the 2026 Magic Quadrant™ for Technical Debt Management Tools. Read the report and learn how to measure and remediate technical debt across your codebase. Get the report

sqlite-utils 4.0, now with database schema migrations

7th July 2026

This morning I released sqlite-utils 4.0, the 124th release of that project and the first major version bump since 3.0 in November 2020. In addition to some small but significant breaking changes (described in this upgrade guide), this version introduces three major features: database migrations , nested transactions (via a new db.atomic() method), and support for compound foreign keys .

Database schema migrations using sqlite-utils

Schema migrations define a sequence of changes to be made to a SQLite database, plus a mechanism for tracking which migrations have been applied and applying any that are found to be pending.

Migrations are defined in Python files using the sqlite-utils Python library, which includes a powerful table.transform() method providing enhanced alter table capabilities that are not supported by SQLite’s ALTER TABLE statement.

(table.transform() implements the pattern recommended by the SQLite documentation—create a new temporary table with the new schema, copy across the data, then drop the old table and rename the temporary one in its place.)

Here’s an example migration file which creates a table called creatures, adds an additional column to it in a second step, then changes the types of two of the columns in a third:

from sqlite_utils import Migrations

migrations = Migrations("creatures")

@migrations()<br>def create_table(db):<br>db["creatures"].create(<br>{"id": int, "name": str, "species": str},<br>pk="id",

@migrations()<br>def add_weight(db):<br>db["creatures"].add_column("weight", float)

@migrations()<br>def change_column_types(db):<br>db["creatures"].transform(types={"species": int, "weight": str})<br>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" (<br>"id" INTEGER PRIMARY KEY,<br>"migration_set" TEXT,<br>"name" TEXT,<br>"applied_at" TEXT<br>);<br>CREATE UNIQUE INDEX "idx__sqlite_migrations_migration_set_name"<br>ON "_sqlite_migrations" ("migration_set", "name");<br>CREATE TABLE "creatures" (<br>"id" INTEGER PRIMARY KEY,<br>"name" TEXT,<br>"species" INTEGER,<br>"weight" TEXT<br>);

The _sqlite_migrations table is used to keep 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:<br>create_table - 2026-07-07 17:58:41.360051+00:00<br>add_weight - 2026-07-07 17:58:41.360608+00:00<br>change_column_types - 2026-07-07 18:01:15.802000+00:00

Pending:<br>(none)

If you don’t specify a migrations file, the sqlite-utils migrate data.db command will scan the current directory and its subdirectories for files called migrations.py and apply any Migrations() instances it finds in them.

You can also execute migrations from Python code using the migrations.apply(db) method, which 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 favorite 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 isn’t anything we can use to automatically generate migrations.

I decided to skip rollback, since in my experience it’s 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...