sqlite-utils 4.0rc1 adds migrations and nested transactions

Simon Willison has released sqlite-utils 4.0rc1, the first release candidate for the Python library and command line tool that manages SQLite databases. The update introduces database migrations and nested transaction support while making several backwards incompatible changes to the API.

Database migrations are now bundled

The release adds support for database migrations. This is not a new implementation from scratch but a modified port of the sqlite-migrate package Willison released years ago. He has decided to bundle it directly with sqlite-utils because the standalone package has proven its reliability over time.

Developers define migrations in a migrations.py file. The example below shows a script that creates a creatures table and then adds a weight column to it:

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)

Users can apply these changes using Python code or the migrate command line tool:

sqlite-utils migrate creatures.db migrations.py

The system is intentionally minimal. It does not support reverse migrations, meaning developers must deploy a new migration script to undo any mistakes.

Willison notes that the predecessor package has been used by projects like LLM for years, indicating the design is stable.

Nested transactions via db.atomic()

SQLite supports nested transactions through savepoints, but the library previously left transaction management entirely up to the user via the with db.conn: construct. The new db.atomic() method abstracts this away using terminology borrowed from Django and Peewee.

The following code demonstrates how to insert data with a nested transaction that rolls back on error:

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"})

Breaking changes in version 4

The major version bump introduces several backwards incompatible changes. Willison described these in the alpha release notes for 4.0a0 and 4.0a1.

Upsert operations now use SQLite’s INSERT ... ON CONFLICT SET syntax on all versions later than 3.23.1. This alters the previous INSERT OR IGNORE followed by UPDATE behavior. Users can opt for the old implementation by passing use_old_upsert=True to the Database() constructor.

Support for Python 3.8 has been dropped while support for Python 3.13 has been added. The sqlite-utils tui command is now provided by the separate sqlite-utils-tui plugin.

The db.table(table_name) method now only works with tables. To access a SQL view, users must use db.view(view_name).

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 in this iterator must be a list or tuple of column names.

The default floating point column type has changed from FLOAT to REAL. This is the correct SQLite type for floating point values and affects auto-detected columns when inserting data.

Packaging now uses pyproject.toml in place of setup.py.

Tables in the Python API now remember the primary key and other schema details from when they were first created more accurately.

The table.convert() and sqlite-utils convert mechanisms no longer skip values that evaluate to False. The --skip-false option has been removed.

Tables created by the library now wrap table and column names in double-quotes in the schema, replacing the previous use of square-braces.

The --functions CLI argument now accepts a path to a Python file in addition to a string of code. It can also be specified multiple times.

Type detection is now the default behavior for the insert and upsert CLI commands when importing CSV or TSV data. All columns were previously treated as TEXT unless the --detect-types flag was passed. Users must now use the --no-detect-types flag to restore the old behavior. The SQLITE_UTILS_DETECT_TYPES environment variable has been removed.

How to try it

Install the release candidate with pip:

pip install sqlite-utils==4.0rc1

Or test the CLI version directly using uvx:

uvx --with sqlite-utils==4.0rc1 sqlite-utils --help

Users can discuss the release in the sqlite-utils Discord channel or file bugs on the GitHub Issues page.