sqlite-utils 4.0rc1 adds migrations and nested transactions

2026-06-21 · Source: Simon Willison's Weblog · Field: Technology & Digital — Software Development & Engineering, Data Science & Analytics · Depth: Intermediate, short

Summary

sqlite-utils 4.0rc1, released on June 21st, 2026, is the first release candidate for the v4 Python library and CLI tool for SQLite databases. This major version introduces two significant new features: database migrations and db.atomic() nested transactions. The migration system, a port of the sqlite-migrate package, allows users to define and apply schema changes through Python or a "migrate" CLI command, supporting operations like table creation and column additions. The db.atomic() feature provides a new API for managing nested transactions using SQLite savepoints, simplifying complex database operations. Additionally, sqlite-utils 4.0rc1 includes several backwards incompatible changes from its alpha releases, such as updated upsert syntax for SQLite versions later than 3.23.1, dropped support for Python 3.8, and a change in the default floating point column type to REAL.

Key takeaway

For Python developers and data engineers managing SQLite databases, upgrading to sqlite-utils 4.0rc1 offers significant enhancements. You should test this release candidate to evaluate the new migration system for schema evolution and the db.atomic() API for robust nested transactions. Be aware of the breaking changes, particularly regarding upsert behavior and type detection, and adjust your code or configurations accordingly to ensure smooth integration and prevent unexpected issues.

Key insights

sqlite-utils 4.0rc1 integrates database migrations and nested transactions, streamlining SQLite schema evolution and complex data operations.

Principles

• Migrations simplify schema evolution.
• Nested transactions ensure data integrity.
• Backward compatibility changes require testing.

Method

Define schema changes in "migrations.py" with @migrations() decorators, then apply via Python's "migrations.apply(db)" or "sqlite-utils migrate" CLI. Manage nested operations using "with db.atomic():" blocks.

In practice

• Define schema changes in Python.
• Use db.atomic() for complex inserts.
• Test 4.0rc1 for compatibility.

Topics

• sqlite-utils
• SQLite
• Database Migrations
• Nested Transactions
• Python Library
• Breaking Changes

Code references

• simonw/sqlite-migrate
• simonw/sqlite-utils
• simonw/sqlite-utils-tui

Best for: Software Engineer, Data Engineer

Related on AIssential

• sqlite-utils 4.0rc1 — sqlite-utils 4.0rc1 enhances SQLite management with migrations and nested transactions.
• datasette 1.0a27 — Datasette 1.0a27 enhances security and plugin reactivity while refining internal database handling and API consistency.
• I Compared 4 Python HTTP Libraries. One Shocked Me Completely. — The author, after experiencing significant performance bottlenecks with the `requests` library in a high-throughput system making hundreds of API calls per minute, undertook a comprehensive benchmark…
• Extra #8 - Python Dictionaries vs. MongoDB Documents — Python dictionaries and MongoDB documents, though similar, have critical underlying structural differences.
• Reinventing the Python Notebook with Akshay Agrawal — Marimo reinvents Python notebooks with a reactive execution model, enabling reproducible, version-controlled, and interactive data exploration and application development.
• Enabling Evolutionary Database Development: database branching with Lakebase — Copy-on-write database branching eliminates shared database bottlenecks, enabling true per-developer isolation and faster feedback.

Open in AIssential →

Editorial summary, takeaway, and curation by AIssential. Original article published by Simon Willison's Weblog.