Simon Willison's Weblog: sqlite-utils

sqlite-utils 4.1.1

2026-07-12T20:55:30+00:00

Mainly a fix for an edge case that regular Claude chat spotted while experimenting with the 4.1 release to answer a question about ON DELETE.

table.transform() now raises a TransactionError if called while a transaction is open with PRAGMA foreign_keys enabled and the table is referenced by foreign keys with destructive ON DELETE actions - CASCADE, SET NULL or SET DEFAULT. The pragma cannot be changed inside a transaction, so previously dropping the old table as part of the transform could fire those actions and silently delete or modify referencing rows. See Foreign keys and transactions for details and workarounds. (#794)

The CLI and Python API documentation now cross-reference each other: CLI sections link to the equivalent Python API functionality and Python API sections link back to the corresponding CLI command. (#791)

Tags: sqlite, sqlite-utils

sqlite-utils 4.1

2026-07-11T23:50:20+00:00

Release: sqlite-utils 4.1

The first dot-release since 4.0 a few days ago, introducing a number of minor new features.

sqlite-utils insert and sqlite-utils upsert now accept a --code option for providing a block of Python code (or a path to a .py file) that defines a rows() function or rows iterable of rows to insert, as an alternative to importing from a file. (#684)

sqlite-utils already had features that allow you to pass blocks of Python code as CLI arguments, for example this one for the sqlite-utils convert command:

sqlite-utils convert content.db articles headline '
def convert(value):
    return value.upper()'

Allowing blocks of code to generate new rows directly was on obvious extension of that pattern:

sqlite-utils insert data.db creatures --code '
def rows():
    yield {"id": 1, "name": "Cleo"}
    yield {"id": 2, "name": "Suna"}
' --pk id

sqlite-utils insert and sqlite-utils upsert now accept --type column-name type to override the type automatically chosen when the table is created. This is useful for CSV or TSV columns such as ZIP codes that look like integers but should be stored as TEXT to preserve leading zeros. (#131)

A long-standing feature request which turned out to be a simple implementation.

New table.drop_index(name) method and sqlite-utils drop-index command for dropping an index by name. Both accept ignore=True/--ignore to ignore a missing index. (#626)

sqlite-utils query can now read the SQL query from standard input by passing - in place of the query, for example echo "select * from dogs" | sqlite-utils query dogs.db -. (#765)

Two more small features. I had Codex review all open issues and highlight the easiest ones!

sqlite-utils upsert can now infer the primary key of an existing table, so --pk can be omitted when upserting into a table that already has a primary key.

Another Codex suggestion, an obvious missing CLI feature from a Python library improvement that shipped in the 4.0 release.

table.transform() and table.transform_sql() now accept strict=True or strict=False to change a table’s SQLite strict mode. Omitting the option preserves the existing mode. (#787)

The sqlite-utils transform command now accepts --strict and --no-strict to change a table’s strict mode. (#787)

These two were inspired by Prefer STRICT tables in SQLite by Evan Hahn, which did the rounds on Hacker News today. Evan pointed out that:

Unfortunately, I don’t think there’s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one.

That's exactly what the sqlite-utils transform mechanism does, so I extended it to add the ability to switch tables from strict to non-strict and vice-versa.

Here's the GPT-5.6 Sol xhigh Codex transcript I used to implement those new strict table features. One of the most useful prompts I ran was this one:

use uv run python -c and manually exercise the new .transform(strict=) option, see if you can find any edge-cases or bugs

Effectively telling the model to manually test its work, outside of the automated tests it had already written. This turned up two minor issues that we then fixed.

Tags: projects, python, sqlite, sqlite-utils, annotated-release-notes, ai-assisted-programming

sqlite-utils 4.0, now with database schema migrations

2026-07-07T19:32:57+00:00

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()
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 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:
    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 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 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:

Database migrations, providing a structured mechanism for evolving a project’s schema over time. (#752)

I think of migrations as the signature new feature, hence this blog post.

Nested transaction support via db.atomic(), plus numerous improvements to how transactions work across the library. (#755)

sqlite-utils has long had a confused relationship with database transactions, partly because when I started designing the library back in 2018 I didn't yet have a great feel for how those worked in SQLite itself.

Adding migrations to the core library made me determined to finally crack this nut, since transactions make migration systems a whole lot safer and easier to reason about.

I ended up building this around a db.atomic() context manager which looks like this:

with db.atomic():
    db.table("dogs").insert({"id": 1, "name": "Cleo"}, pk="id")
    db.table("dogs").insert({"id": 2, "name": "Pancakes"})

SQLite supports Savepoints, and as a result db.atomic() can be nested to carry out transactions inside of transactions. It's pretty neat!

Support for compound foreign keys, including creation, transformation and introspection through table.foreign_keys. (#594)

This came about when I asked a coding agent to review all open issues and PRs for things that should be included in a 4.0 release since they would represent breaking changes if I added them later, and it correctly identified that compound foreign keys were exactly that kind of feature.

I started with a breaking change to the table.foreign_keys introspection method, and then decided to see if Claude Fable 5 could handle the more fiddly job of integrating compound foreign key creation into the library. The API design it helped create felt exactly right to me - consistent with how the rest of the library worked already.

Other notable changes include:

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. (#652)

This was the change that first pushed me to consider a breaking-change 4.0 version bump. I built this to help support sqlite-chronicle, which uses triggers to keep track of rows in a table that have been inserted, updated or deleted.

db.query() now executes immediately and rejects statements that do not return rows; use db.execute() for writes and DDL.

Probably the most disruptive breaking change - I've had to update a few places in my own code to switch from db.query() to db.execute() as a result.

CSV and TSV imports now detect column types by default, while inserts into existing tables preserve those tables’ column types. (#679)

The sqlite-utils insert data.db creatures creatures.csv --detect-types flag was a later addition to allow column types (text, integer, real) to be automatically detected based on the data in a CSV. It should be the default, and releasing a 4.0 means I can make it so.

table.extract() and extracts= no longer create lookup table records for all-null values. (#186)

The oldest issue addressed by this release - the underlying bug was opened (by me) in October 2020.

See Upgrading from 3.x to 4.0 for details on backwards-incompatible changes.

The detailed release notes for the features and fixes shipped during the 4.0 pre-release cycle are available in 4.0a0, 4.0a1, 4.0rc1, 4.0rc2, 4.0rc3 and 4.0rc4.

The upgrade guide was entirely written by Claude Fable 5, Claude Opus 4.8 and GPT-5.5. The same is true of the release notes.

This is the kind of documentation I've slowly become comfortable outsourcing to the robots. It doesn't need to convince people of anything, or express any opinions - its job is to be as accurate and detailed as possible. I've reviewed the release notes closely and can confirm they are accurate and comprehensive.

Claude Fable 5 helped a lot

I released the first alpha of sqlite-utils 4.0 over a year ago. I've been dragging my heels on the stable release because of the amount of work it would take to track down and clean up the many other minor design flaws that a major version number allowed me to take on.

Assistance from Claude Fable 5 (and to a lesser extent Opus 4.8 and GPT-5.5) gave me just the boost I needed to overcome inertia and make the most of the time I could afford to spend on this library.

Fable has really good taste in API design, and is relentlessly proactive if you give it a more open goal. My most successful prompt was a review task that I issued against what I thought was the last release candidate:

review the changes on main since the last tagged 3.x release - I am about to ship them as sqlite-utils 4.0, a stable version that promises no backwards-incompatible fixes for a very long time.

review the changelog and upgrade guide, and write yourself scratch scripts to try out all of the new features in v4 - save those scripts but don't commit them

I tried this with GPT-5.5 xhigh in Codex Desktop and Fable 5 in Claude Code.

GPT-5.5 wrote 5 Python scripts and didn't turn up anything particularly interesting - its final report is here.

Fable 5 wrote 12 scripts, identified 4 release blockers and 10 additional issues in its report, and built a neat combined repro script, which, when run, output the following:

=== 1. Failed db.execute() write leaves an implicit transaction open ===
  in_transaction after failed write: True
  BUG: table 'other' silently lost when connection closed

=== 2. Leading ';' bypasses the query() first-token scanner ===
  BUG: raised OperationalError: no such savepoint: sqlite_utils_query
  BUG: row persisted despite rollback (count=1)

=== 3. Rejected write PRAGMA via query() still takes effect ===
  BUG: user_version=5 after 'rejected' statement (docs say no effect)

=== 4. Implicit compound FK resolves pk columns in table order, not PK order ===
  BUG: other_columns reported as ('b', 'a'), should be ('a', 'b')
  BUG: transform of valid data raised IntegrityError: FOREIGN KEY constraint failed

=== 5. ForeignKey (now a dataclass) is no longer hashable ===
  BUG: cannot use 'sqlite_utils.db.ForeignKey' as a set element (unhashable type: 'ForeignKey')

=== 6. Mixed ForeignKey objects and tuples in foreign_keys= rejected ===
  BUG: foreign_keys= should be a list of tuples

=== 7. insert --csv into an EXISTING table transforms its column types ===
  BUG: existing zip '01234' is now 1234 (column type: int)

=== 8. insert(pk=, alter=True) regression: InvalidColumns before alter runs ===
  BUG: InvalidColumns: Invalid primary key column ['id'] for table t with columns ['a']

=== 9. migrate --stop-before an already-applied migration applies everything ===
  BUG: m2 was applied despite --stop-before m1 (m1 already applied)

=== 10. ensure_autocommit_on() silently commits an open transaction ===
  BUG: row survived rollback (count=1) - transaction was committed

I found myself agreeing with almost all of them. Here's the PR with 16 commits where we worked through them in turn.

There's no doubt in my mind that sqlite-utils 4.0 is a significantly higher-quality release than if I had built it without the assistance of the latest frontier models.

Tags: schema-migrations, projects, sqlite, ai, sqlite-utils, annotated-release-notes, generative-ai, llms, ai-assisted-programming, anthropic, claude, agentic-engineering, claude-mythos-fable

sqlite-migrate 0.2

2026-07-07T16:33:55+00:00

Release: sqlite-migrate 0.2

The version that retires the library, instead implementing a compatibility shim against the new sqlite-utils 4.0 dependency.

Tags: sqlite-utils

sqlite-utils 4.0

2026-07-07T15:42:43+00:00

Release: sqlite-utils 4.0

See sqlite-utils 4.0, now with database schema migrations for details.

Tags: sqlite-utils

sqlite-utils 4.0rc4

2026-07-07T05:36:15+00:00

Release: sqlite-utils 4.0rc4

The last RC before the 4.0 stable release. Mainly implements feedback from a detailed review by Claude Fable 5.

Tags: sqlite-utils, claude-mythos-fable

sqlite-utils 4.0rc3

2026-07-06T05:40:08+00:00

Release: sqlite-utils 4.0rc3

I hoped to release sqlite-utils 4.0 stable this weekend, but as I worked through the backlog of issues and PRs with a combination of Claude Fable 5 and GPT-5.5 the changelog since rc2 kept getting bigger.

The biggest new feature is support for introspecting and creating compound foreign keys - a feature that involves a subtle breaking change to table.foreign_keys and hence needed to land for the 4.0 stable release.

sqlite-utils also now follows SQLite's convention for case insensitive column names, which turned out to touch a bunch of different places at once.

Tags: projects, sqlite, sqlite-utils, annotated-release-notes, gpt, claude-mythos-fable

sqlite-utils 4.0rc2, mostly written by Claude Fable (for about $149.25)

2026-07-05T01:00:48+00:00

I wrote about the sqlite-utils 4.0rc1 release a couple of weeks ago. Since we only have Claude Fable on our Max subscriptions for a few more days, I decided to see if it could help me get to a 4.0 stable release that I felt truly comfortable about, since I try to keep to SemVer and like my incompatible major versions to be as rare as possible.

I started with this prompt, in Claude Code for web on my iPhone:

Final review before shipping a stable 4.0 release - very important to spot any last minute things that would be a breaking change if we fix them later

Here's that initial report it created for me. There were some significant problems that I hadn't myself encountered yet - 5 that Fable categorized as "release blockers". Here's the worst of the bunch:

1. delete_where() never commits and poisons the connection (data loss)

Table.delete_where() (sqlite_utils/db.py:2948) runs its DELETE via a bare self.db.execute() with no atomic() wrapper — compare Table.delete() at db.py:2944, which wraps correctly. The connection is left in_transaction=True, so every subsequent atomic() call takes the savepoint branch (db.py:430-440) and never commits either.

Reproduced end-to-end:
db = sqlite_utils.Database("dw.db")
db["t"].insert_all([{"id": i} for i in range(3)], pk="id")
db["t"].delete_where("id = ?", [0])   # conn.in_transaction is now True
db["t"].insert({"id": 50})
db["u"].insert({"a": 1})
db.close()
# Reopen: rows are [0, 1, 2] — the delete, row 50, AND table u are all gone.

That's a really bad bug! Very glad I didn't ship that, although at least it would have been a bug I could fix in a 4.0.1 point release, not a design flaw that would force a 5.0.

Over the course of 37 prompts, 34 commits and +1,321 -190 code changes over 30 separate files, we worked through the entire set of feedback in turn, making several other design improvements along the way.

A weird thing about coding agents is that harder tasks like this one actually provide more opportunity to do other things at the same time, since the agent sometimes needs 10-15 minutes to churn away on a new task. I went out to enjoy the Half Moon Bay 4th of July parade, occasionally checking in and prompting the next step for Fable from my phone.

Full details in the PR and this shared transcript. I switched to my laptop for the final review, which I conducted through GitHub's PR interface.

The most significant changes relate to transaction handling, which was the signature new feature in the earlier RC. The new RC now includes comprehensive documentation on the new transaction model, the intro to which I'll quote here in full:

Every method in this library that writes to the database - insert(), upsert(), update(), delete(), delete_where(), transform(), create_table(), create_index(), enable_fts() and the rest - runs inside its own transaction and commits it before returning. Your changes are saved to disk as soon as the method call finishes:
db = Database("data.db")
db.table("news").insert({"headline": "Dog wins award"})
# The new row is already saved - no commit() required
The same applies to raw SQL executed with db.execute() - a write statement is committed as soon as it has run.

You never need to call commit(), and you do not need to close the database to persist your changes. There are exactly two situations where you need to think about transactions:

You want to group several write operations together, so they either all succeed or all fail - use db.atomic().

You are managing a transaction yourself with db.begin(), in which case nothing is committed until you commit - the library will never commit a transaction you opened.

In reviewing Fable's documentation - I find that reviewing the documentation edits first is an excellent way to build an initial understanding of what has changed - I spotted this detail:

db.atomic() and the automatic per-method transactions are designed for connections in Python's default transaction handling mode. Connections created with the Python 3.12+ sqlite3.connect(..., autocommit=True) or autocommit=False options are not supported, because commit() and rollback() behave differently on those connections.

I admit I hadn't thought about how sqlite-utils would react to the more recent autocommit setting, added in Python 3.12. It turns out "behave differently on those connections" equated to almost the entire test suite failing, so I worked with the model to ensure that this difference would not break how the library works.

And a final review by GPT-5.5

I used to think that the idea of having one model review the work of another was somewhat absurd - it felt weirdly superstitious. The problem is it really does work - I've started habitually having Anthropic's best model review OpenAI's work and vice versa, because I've had that turn up interesting results often enough to be valuable.

I prompted Codex Desktop and GPT-5.5 xhigh with the following:

Review changes since the last RC. Also confirm that the changelog is up-to-date.

Which was enough to turn up two issues worth investigating:

Findings

[P1] sqlite_utils/db.py:663 db.query() now rejects non-row statements only after calling db.execute(), and sqlite_utils/db.py:705 auto-commits those writes first. So db.query("update ...") raises ValueError but the update is already committed. That is a surprising side effect for a method documented as “can only be used with SQL that returns rows.”

[P1] sqlite_utils/db.py:672 INSERT ... RETURNING through db.query() only commits after the returned generator is fully exhausted. db.query("insert ... returning ...") without iteration, or common next(db.query(...)) usage, leaves the transaction open and the write can be rolled back on close. This contradicts docs/changelog.rst:15 and docs/python-api.rst:232, which say it takes effect without iteration.

I pasted that into a fresh Fable session, which ran some experiments to confirm the problem:

Both findings were confirmed. db.query() called self.execute() first, which auto-commits writes, and only then checked cursor.description — so db.query("update ...") committed the update before raising ValueError. And the INSERT ... RETURNING commit lived at the end of the returned generator, so it never fired unless you exhausted the iterator — next(db.query(...)) or an un-iterated call left the transaction open, contradicting what the changelog and docs promise.

Here's the PR with the fix, and the full Claude Code transcript. Reviewing this code helped me build a better mental model of the edge cases of SQLite transaction semantics!

For an estimated (unsubsidized) cost of $149.25

I upgraded to the Claude Max $200/month plan (I was previously on $100/month) to increase my Fable allowance for the remaining time until the July 7th Fablepocalypse, when even Claude Max subscribers will have to pay full API cost for the model.

I was curious as to how much this would have cost me if I had been paying those costs directly. At first I thought those numbers weren't available to me since I had run the work remotely using Claude Code for web, and then I realized I could run AgentsView inside that existing session to get that cost estimate!

Run "uvx agentsview --help" and then use that tool to calculate the cost of this session

Claude figured out how to use the session list --include-children command and came out with the following:

Transcript	Model	Cost
Main session	claude-fable-5	$141.02
API-surface sweep agent	claude-fable-5	$2.40
Transactions/atomic review agent	claude-fable-5	$2.39
Post-rc1 commits review agent	claude-fable-5	$1.72
Migrations review agent	claude-fable-5	$1.40
Prompt-counting agent	claude-opus-4-8	$0.32
Total		$149.25

I'm very glad I'm on that subscription! I really should have followed my own advice and leaned more heavily into subagents with cheaper models.

Here's what claude.ai/settings/usage is showing me right now:

I have several other major Fable-driven projects on the go right now as well, with the goal of hitting 100% on that Fable bar just in time for the price increase.

The full release notes for sqlite-utils 4.0rc2

Here are the full release notes for the RC. I had Fable add these to an "Unreleased" section of the changelog as each change landed, reviewing them as it went. This has the neat side effect that the commit history of the changelog acts as a concise summary of each of the changes that went into the release.

In the past I've had a policy of writing release notes by hand, but honestly these are better than I would have created myself. Release notes are a great example of writing that I'm OK to outsource to agents because they need to be boring, predictable and accurate.

Breaking changes:

Write statements executed with db.execute() are now committed automatically, unless a transaction is already open in which case they join it. Previously they opened an implicit transaction that stayed open until something committed it - writes appeared to work when read on the same connection but were silently rolled back when the connection closed. Code that relied on rolling back uncommitted db.execute() writes should use the new db.begin() method to open an explicit transaction first. The transaction model is documented in full at Transactions and saving your changes.

db.query() now executes its SQL as soon as it is called, rather than waiting until the returned generator is first iterated. Rows are still fetched lazily during iteration. SQL errors are now raised at the call site, statements such as INSERT ... RETURNING are executed and committed immediately without needing to iterate over their results, and passing a statement that returns no rows - previously a silent no-op - now raises a ValueError recommending db.execute() instead. A statement rejected this way is rolled back before the error is raised, so it has no effect on the database.

Python API validation errors now raise ValueError instead of AssertionError. Previously invalid arguments - such as create_table() with no columns, transform() on a table that does not exist, or passing both ignore=True and replace=True - were rejected using bare assert statements, which are silently skipped when Python runs with the -O flag. Code that caught AssertionError for these cases should catch ValueError instead.

table.upsert() and table.upsert_all() now raise PrimaryKeyRequired if a record is missing a value for any primary key column, or has a value of None for one. Previously such records - which can never match an existing row - were quietly inserted as brand new rows, or triggered a confusing KeyError after the insert had already taken place.

db.enable_wal() and db.disable_wal() now raise a sqlite_utils.db.TransactionError if called while a transaction is open. Previously they would silently commit the open transaction as a side effect of changing the journal mode, breaking the rollback guarantee of db.atomic() and of user-managed transactions.

The View class no longer has an enable_fts() method. It existed only to raise NotImplementedError, since full-text search is not supported for views - calling it now raises AttributeError instead, and the method no longer appears in the API reference. The sqlite-utils enable-fts command shows a clean error when pointed at a view.

The no-op -d/--detect-types flag has been removed from the insert and upsert commands. Type detection has been the default for CSV/TSV data since 4.0a1, so the flag did nothing - invocations using it should simply drop it. --no-detect-types remains available to disable detection.

Database() now raises a sqlite_utils.db.TransactionError if passed a connection created with the Python 3.12+ sqlite3.connect(..., autocommit=True) or autocommit=False options. commit() and rollback() behave differently on those connections, which previously caused every write made by the library to be silently discarded when the connection closed.

Everything else:

Fixed a bug where table.delete_where(), table.optimize() and table.rebuild_fts() did not commit their changes, leaving the connection inside an open transaction. Their work - and any subsequent writes - could then be silently rolled back when the connection was closed. All three now use db.atomic(), consistent with the other write methods.

The sqlite-utils drop-table command now refuses to drop a view, and drop-view refuses to drop a table. Previously each would silently drop the wrong type of object if the name matched. Both now exit with an error suggesting the correct command to use.

Migrations applied by the new migrations system now run inside a transaction, together with the record of the migration having been applied. If a migration raises an exception its changes are rolled back and it stays pending, so it can be safely re-applied after the error is fixed. Migrations that cannot run inside a transaction, such as those executing VACUUM, can opt out using @migrations(transactional=False) - see Migrations and transactions.

table.upsert() and table.upsert_all() now detect the primary key or compound primary key of an existing table, so the pk= argument is no longer required when upserting into a table that already has a primary key.

db.table(table_name).insert({}) can now be used to insert a row consisting entirely of default values into an existing table, using INSERT INTO ... DEFAULT VALUES. (#759)

Improvements to the sqlite-utils migrate command: --stop-before values that do not match any known migration are now an error instead of being silently ignored, --stop-before now works correctly with migration files that still use the older sqlite_migrate.Migrations class, and --list is now a read-only operation that no longer creates the database file or the migrations tracking table. migrations.applied() now returns migrations in the order they were applied.

New db.begin(), db.commit() and db.rollback() methods for taking manual control of transactions, as an alternative to the db.atomic() context manager.

New documentation: Transactions and saving your changes describes how transactions work and when changes are committed, and a new Upgrading page details the changes needed to move between major versions.

Tags: projects, sqlite, sqlite-utils, annotated-release-notes, anthropic, claude, llm-pricing, coding-agents, claude-code, agentic-engineering, gpt, claude-mythos-fable

sqlite-utils 4.0rc1 adds migrations and nested transactions

2026-06-21T23:35:47+00:00

sqlite-utils is my combined Python library and CLI tool for working with SQLite databases. It provides an extensive set of higher-level operations on top of Python's default sqlite3 package, including support for complex table transformations, automatic table creation from JSON data and a whole lot more.

I released sqlite-utils 4.0rc1, the first release candidate for sqlite-utils v4. The major version bump indicates some (minor) backwards incompatible changes, so I'm interested in having people try this out before I commit to a stable release.

New feature: migrations

There are two significant new features in this RC compared to the previous 4.0 alphas.

The first is support for database migrations. This isn't a completely new implementation - it's a slightly modified port of the sqlite-migrate package I released a few years ago. I think that package has proved itself over time, so I'm now ready to bundle it with sqlite-utils directly.

Here's what a set of migrations in a migrations.py file looks like:

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)

This defines a set of two migrations, one creating the creatures table and another adding a column to it.

You can then run those migrations either using Python:

db = Database("creatures.db")
migrations.apply(db)

Or with the command-line migrate command:

sqlite-utils migrate creatures.db migrations.py

The system is deliberately small: it doesn't provide reverse migrations, so any mistakes you make should be fixed by deploying a fresh migration to undo them.

Its predecessor has been used by LLM and various other projects for several years, so I'm confident that the design is stable and works well.

The new migrations feature is documented here.

New feature: db.atomic() transactions

This feature is a lot less exercised than migrations, so it deserves more attention from testers.

Previously, sqlite-utils mostly left transaction management up to its users, via a with db.conn: construct that reused the sqlite3 mechanism directly.

SQLite supports nested transactions in the form of savepoints, so I wanted an abstraction that could make those as easy to use as possible.

I borrowed the terminology "atomic" from Django and Peewee. Here's what the new API looks like:

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

Backwards incompatible changes

The backwards incompatible changes in v4 were described in the alpha release notes. For 4.0a0:

Upsert operations now use SQLite's INSERT ... ON CONFLICT SET syntax on all SQLite versions later than 3.23.1. This is a very slight breaking change for apps that depend on the previous INSERT OR IGNORE followed by UPDATE behavior. (#652)

Python library users can opt-in to the previous implementation by passing use_old_upsert=True to the Database() constructor, see Alternative upserts using INSERT OR IGNORE.

Dropped support for Python 3.8, added support for Python 3.13. (#646)

sqlite-utils tui is now provided by the sqlite-utils-tui plugin. (#648)

Test suite now also runs against SQLite 3.23.1, the last version (from 2018-04-10) before the new INSERT ... ON CONFLICT SET syntax was added. (#654)

And for 4.0a1:

Breaking change: The db.table(table_name) method now only works with tables. To access a SQL view use db.view(view_name) instead. (#657)

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 should be a list/tuple of column names. See Inserting data from a list or tuple iterator for details. (#672)

Breaking change: The default floating point column type has been changed from FLOAT to REAL, which is the correct SQLite type for floating point values. This affects auto-detected columns when inserting data. (#645)

Now uses pyproject.toml in place of setup.py for packaging. (#675)

Tables in the Python API now do a much better job of remembering the primary key and other schema details from when they were first created. (#655)

Breaking change: The table.convert() and sqlite-utils convert mechanisms no longer skip values that evaluate to False. Previously the --skip-false option was needed, this has been removed. (#542)

Breaking change: Tables created by this library now wrap table and column names in "double-quotes" in the schema. Previously they would use [square-braces]. (#677)

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

Breaking change: Type detection is now the default behavior for the insert and upsert CLI commands when importing CSV or TSV data. Previously all columns were treated as TEXT unless the --detect-types flag was passed. Use the new --no-detect-types flag to restore the old behavior. The SQLITE_UTILS_DETECT_TYPES environment variable has been removed. (#679)

Try it out

You can install the new RC like this:

pip install sqlite-utils==4.0rc1

Or try the CLI version directly with uvx like this:

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

Come chat with us about it in the sqlite-utils Discord channel, or file any bugs in GitHub Issues.

Tags: schema-migrations, projects, sqlite, sqlite-utils, annotated-release-notes

sqlite-utils 4.0rc1

2026-06-21T23:30:04+00:00

Release: sqlite-utils 4.0rc1

See sqlite-utils 4.0rc1 adds migrations and nested transactions.

Tags: sqlite-utils

Under the hood of Canada Spends with Brendan Samek

2025-12-09T23:52:05+00:00

I talked to Brendan Samek about Canada Spends, a project from Build Canada that makes Canadian government financial data accessible and explorable using a combination of Datasette, a neat custom frontend, Ruby ingestion scripts, sqlite-utils and pieces of LLM-powered PDF extraction.

Here's the video on YouTube.

Sections within that video:

02:57 Data sources and the PDF problem
05:51 Crowdsourcing financial data across Canada
07:27 Datasette demo: Search and facets
12:33 Behind the scenes: Ingestion code
17:24 Data quality horror stories
20:46 Using Gemini to extract PDF data
25:24 Why SQLite is perfect for data distribution

Build Canada and Canada Spends

Build Canada is a volunteer-driven non-profit that launched in February 2025 - here's some background information on the organization, which has a strong pro-entrepreneurship and pro-technology angle.

Canada Spends is their project to make Canadian government financial data more accessible and explorable. It includes a tax sources and sinks visualizer and a searchable database of government contracts, plus a collection of tools covering financial data from different levels of government.

Datasette for data exploration

The project maintains a Datasette instance at api.canadasbilding.com containing the data they have gathered and processed from multiple data sources - currently more than 2 million rows plus a combined search index across a denormalized copy of that data.

Processing PDFs

The highest quality government financial data comes from the audited financial statements that every Canadian government department is required to publish. As is so often the case with government data, these are usually published as PDFs.

Brendan has been using Gemini to help extract data from those PDFs. Since this is accounting data the numbers can be summed and cross-checked to help validate the LLM didn't make any obvious mistakes.

sqlite-utils 3.39

2025-11-24T18:59:14+00:00

sqlite-utils 3.39

I got a report of a bug in sqlite-utils concerning plugin installation - if you installed the package using uv tool install further attempts to install plugins with sqlite-utils install X would fail, because uv doesn't bundle pip by default. I had the same bug with Datasette a while ago, turns out I forgot to apply the fix to sqlite-utils.

Since I was pushing a new dot-release I decided to integrate some of the non-breaking changes from the 4.0 alpha I released last night.

I tried to have Claude Code do the backporting for me:

create a new branch called 3.x starting with the 3.38 tag, then consult https://github.com/simonw/sqlite-utils/issues/688 and cherry-pick the commits it lists in the second comment, then review each of the links in the first comment and cherry-pick those as well. After each cherry-pick run the command "just test" to confirm the tests pass and fix them if they don't. Look through the commit history on main since the 3.38 tag to help you with this task.

This worked reasonably well - here's the terminal transcript. It successfully argued me out of two of the larger changes which would have added more complexity than I want in a small dot-release like this.

I still had to do a bunch of manual work to get everything up to scratch, which I carried out in this PR - including adding comments there and then telling Claude Code:

Apply changes from the review on this PR https://github.com/simonw/sqlite-utils/pull/689

Here's the transcript from that.

The release is now out with the following release notes:

Fixed a bug with sqlite-utils install when the tool had been installed using uv. (#687)

The --functions argument now optionally accepts a path to a Python file as an alternative to a string full of code, and can be specified multiple times - see Defining custom SQL functions. (#659)

sqlite-utils now requires on Python 3.10 or higher.

Tags: projects, sqlite, sqlite-utils, annotated-release-notes, uv, coding-agents, claude-code

sqlite-utils 3.39

2025-11-24T18:42:29+00:00

Release: sqlite-utils 3.39

Tags: sqlite, sqlite-utils

sqlite-utils 4.0a1 has several (minor) backwards incompatible changes

2025-11-24T14:52:34+00:00

I released a new alpha version of sqlite-utils last night - the 128th release of that package since I started building it back in 2018.

sqlite-utils is two things in one package: a Python library for conveniently creating and manipulating SQLite databases and a CLI tool for working with them in the terminal. Almost every feature provided by the package is available via both of those surfaces.

This is hopefully the last alpha before a 4.0 stable release. I use semantic versioning for this library, so the 4.0 version number indicates that there are backward incompatible changes that may affect code written against the 3.x line.

These changes are mostly very minor: I don't want to break any existing code if I can avoid it. I made it all the way to version 3.38 before I had to ship a major release and I'm sad I couldn't push that even further!

Here are the annotated release notes for 4.0a1.

Breaking change: The db.table(table_name) method now only works with tables. To access a SQL view use db.view(view_name) instead. (#657)

This change is for type hint enthusiasts. The Python library used to encourage accessing both SQL tables and SQL views through the db["name_of_table_or_view"] syntactic sugar - but tables and view have different interfaces since there's no way to handle a .insert(row) on a SQLite view. If you want clean type hints for your code you can now use the db.table(table_name) and db.view(view_name) methods instead.

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 should be a list/tuple of column names. See Inserting data from a list or tuple iterator for details. (#672)

A new feature, not a breaking change. I realized that supporting a stream of lists or tuples as an option for populating large tables would be a neat optimization over always dealing with dictionaries each of which duplicated the column names.

I had the idea for this one while walking the dog and built the first prototype by prompting Claude Code for web on my phone. Here's the prompt I used and the prototype report it created, which included a benchmark estimating how much of a performance boost could be had for different sizes of tables.

Breaking change: The default floating point column type has been changed from FLOAT to REAL, which is the correct SQLite type for floating point values. This affects auto-detected columns when inserting data. (#645)

I was horrified to discover a while ago that I'd been creating SQLite columns called FLOAT but the correct type to use was REAL! This change fixes that. Previously the fix was to ask for tables to be created in strict mode.

Now uses pyproject.toml in place of setup.py for packaging. (#675)

As part of this I also figured out recipes for using uv as a development environment for the package, which are now baked into the Justfile.

Tables in the Python API now do a much better job of remembering the primary key and other schema details from when they were first created. (#655)

This one is best explained in the issue.

Breaking change: The table.convert() and sqlite-utils convert mechanisms no longer skip values that evaluate to False. Previously the --skip-false option was needed, this has been removed. (#542)

Another change which I would have made earlier but, since it introduces a minor behavior change to an existing feature, I reserved it for the 4.0 release.

Breaking change: Tables created by this library now wrap table and column names in "double-quotes" in the schema. Previously they would use [square-braces]. (#677)

Back in 2018 when I started this project I was new to working in-depth with SQLite and incorrectly concluded that the correct way to create tables and columns named after reserved words was like this:

create table [my table] (
  [id] integer primary key,
  [key] text
)

That turned out to be a non-standard SQL syntax which the SQLite documentation describes like this:

A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.

Unfortunately I baked it into the library early on and it's been polluting the world with weirdly escaped table and column names ever since!

I've finally fixed that, with the help of Claude Code which took on the mind-numbing task of updating hundreds of existing tests that asserted against the generated schemas.

The above example table schema now looks like this:

create table "my table" (
  "id" integer primary key,
  "key" text
)

This may seem like a pretty small change but I expect it to cause a fair amount of downstream pain purely in terms of updating tests that work against tables created by sqlite-utils!

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

I made this change first in LLM and decided to bring it to sqlite-utils for consistency between the two tools.

Breaking change: Type detection is now the default behavior for the insert and upsert CLI commands when importing CSV or TSV data. Previously all columns were treated as TEXT unless the --detect-types flag was passed. Use the new --no-detect-types flag to restore the old behavior. The SQLITE_UTILS_DETECT_TYPES environment variable has been removed. (#679)

One last minor ugliness that I waited for a major version bump to fix.

Update: Now that the embargo has lifted I can reveal that a substantial amount of the work on this release was performed using a preview version of Anthropic's new Claude Opus 4.5 model. Here's the Claude Code transcript for the work to implement the ability to use an iterator over lists instead of dictionaries for bulk insert and upsert operations.

Tags: projects, sqlite, sqlite-utils, annotated-release-notes, ai-assisted-programming, coding-agents, claude-code

sqlite-utils 4.0a1

2025-11-24T06:32:51+00:00

Release: sqlite-utils 4.0a1

Tags: sqlite, sqlite-utils

Datasette ecosystem poster for PyCon US

2025-05-17T20:34:39+00:00

In addition to my workshop the other day I'm also participating in the poster session at PyCon US this year.

This means that tomorrow (Sunday 18th May) I'll be hanging out next to my poster from 10am to 1pm in Hall A talking to people about my various projects.

I'll confess: I didn't pay close enough attention to the poster information, so when I first put my poster up it looked a little small:

... so I headed to the nearest CVS and printed out some photos to better represent my interests and personality. I'm going for a "teenage bedroom" aesthetic here, I'm very happy with the result:

Here's the poster in the middle (also available as a PDF). It has columns for Datasette, sqlite-utils and LLM.

If you're at PyCon I'd love to talk to you about things I'm working on!

Update: Thanks to everyone who came along. Here's a 6MB photo of the poster setup. The museums were all from my www.niche-museums.com site and the pelicans riding a bicycle SVGs came from my pelican-riding-a-bicycle tag.

Tags: museums, pycon, datasette, sqlite-utils, llm, pelican-riding-a-bicycle

sqlite-utils 4.0a0

2025-05-09T04:02:31+00:00

sqlite-utils 4.0a0

New alpha release of sqlite-utils, my Python library and CLI tool for manipulating SQLite databases.

It's the first 4.0 alpha because there's a (minor) backwards-incompatible change: I've upgraded the .upsert() and .upsert_all() methods to use SQLIte's UPSERT mechanism, INSERT INTO ... ON CONFLICT DO UPDATE. Details in this issue.

That feature was added to SQLite in version 3.24.0, released 2018-06-04. I'm pretty cautious about my SQLite version support since the underlying library can be difficult to upgrade, depending on your platform and operating system.

I'm going to leave the new alpha to bake for a little while before pushing a stable release. Since this is a major version bump I'm going to take the opportunity to see if there are any other minor API warts that I can clean up at the same time.

Tags: cli, projects, sqlite, sqlite-utils

sqlite-utils 4.0a0

2025-05-09T03:54:32+00:00

Release: sqlite-utils 4.0a0

Tags: sqlite, sqlite-utils

sqlite-utils-tui 0.1

2025-01-11T01:11:59+00:00

Release: sqlite-utils-tui 0.1

Tags: sqlite, sqlite-utils

Ask questions of SQLite databases and CSV/JSON files in your terminal

2024-11-25T01:33:03+00:00

I built a new plugin for my sqlite-utils CLI tool that lets you ask human-language questions directly of SQLite databases and CSV/JSON files on your computer.

It's called sqlite-utils-ask. Here's how you install it:

sqlite-utils install sqlite-utils-ask

It picks up API keys from an OPENAI_API_KEY environment variable, or you can install LLM and use llm keys set openai to store a key in a configuration file.

Then you can use it like this:

curl -O https://datasette.io/content.db
sqlite-utils ask content.db "how many sqlite-utils pypi downloads in 2024?"

This command will extract the SQL schema for the provided database file, send that through an LLM along with your question, get back a SQL query and attempt to run it to derive a result.

If all goes well it spits out an answer something like this:

SELECT SUM(downloads)
FROM stats
WHERE package = 'sqlite-utils' AND date >= '2024-01-01' AND date < '2025-01-01';

[
    {
        "SUM(downloads)": 4300221
    }
]

If the SQL query fails to execute (due to a syntax error of some kind) it passes that error back to the model for corrections and retries up to three times before giving up.

Add -v/--verbose to see the exact prompt it's using:

System prompt:
You will be given a SQLite schema followed by a question. Generate a single SQL
query to answer that question. Return that query in a ```sql ... ```
fenced code block.

Example: How many repos are there?
Answer:
```sql
select count(*) from repos
```

Prompt:
...
CREATE TABLE [stats] (
   [package] TEXT,
   [date] TEXT,
   [downloads] INTEGER,
   PRIMARY KEY ([package], [date])
);
...
how many sqlite-utils pypi downloads in 2024?

I've truncated the above to just the relevant table - it actually includes the full schema of every table in that database.

By default, the tool sends just that database schema and your question to the LLM. If you add the -e/--examples option it will also include five common values for each of the text columns in that schema with an average length less than 32 characters. This can sometimes help get a better result, for example sending values "CA" and "FL" and "TX" for a state column can tip the model of that it should use state abbreviations rather than full names in its queries.

Asking questions of CSV and JSON data

The core sqlite-utils CLI usually works against SQLite files directly, but three years ago I added the ability to run SQL queries against CSV and JSON files directly with the sqlite-utils memory command. This works by loading that data into an in-memory SQLite database before executing a SQL query.

I decided to reuse that mechanism to enable LLM prompts against CSV and JSON data directly as well.

The sqlite-utils ask-files command looks like this:

sqlite-utils ask-files transactions.csv "total sales by year"

This command accepts one or more files, and you can provide a mix of CSV, TSV and JSON. Each provided file will be imported into a different table, allowing the model to construct join queries where necessary.

Implementation notes

The core of the plugin is implemented as around 250 lines of Python, using the sqlite-utils register_commands() plugin hook to add the ask and ask-files commands.

It adds LLM as a dependency, and takes advantage of LLM's Python API to abstract over the details of talking to the models. This means sqlite-utils-ask can use any of the models supported by LLM or its plugins - if you want to run your prompt through Claude 3.5 Sonnet you can do this:

sqlite-utils install llm-claude-3
sqlite-utils ask content.db "count rows in news table" -m claude-3.5-sonnet

The plugin defaults to gpt-4o-mini initially to take advantage of that model's automatic prompt caching: if you run multiple questions against the same schema you'll end up sending the same lengthy prompt prefix multiple times, and OpenAI's prompt caching should automatically kick in and provide a 50% discount on those input tokens.

Then I ran the actual numbers and found that gpt-4o-mini is cheap enough that even without caching a 4,000 token prompt (that's a pretty large SQL schema) should cost less than a tenth of a cent. So those caching savings aren't worth anything at all!

Tags: cli, plugins, projects, sqlite, ai, sqlite-utils, generative-ai, llms, ai-assisted-programming, llm

sqlite-utils 3.38

2024-11-23T22:40:30+00:00

Release: sqlite-utils 3.38

Plugins can now reuse the implementation of the sqlite-utils memory CLI command with the new return_db=True parameter. (#643)

table.transform() now recreates indexes after transforming a table. A new sqlite_utils.db.TransformErrorexception is raised if these indexes cannot be recreated due to conflicting changes to the table such as a column rename. Thanks, Mat Miller. (#633)

table.search() now accepts a include_rank=True parameter, causing the resulting rows to have a rank column showing the calculated relevance score. Thanks, liunux4odoo. (#628)

Fixed an error that occurred when creating a strict table with at least one floating point column. These FLOATcolumns are now correctly created as REAL as well, but only for strict tables. (#644)

Tags: sqlite, sqlite-utils

sqlite-utils 3.38a0

2024-11-08T20:14:47+00:00

Release: sqlite-utils 3.38a0

Tags: sqlite, sqlite-utils

Using sqlite-vec with embeddings in sqlite-utils and Datasette

2024-08-11T23:37:42+00:00

Using sqlite-vec with embeddings in sqlite-utils and Datasette

My notes on trying out Alex Garcia's newly released sqlite-vec SQLite extension, including how to use it with OpenAI embeddings in both Datasette and sqlite-utils.

Tags: sqlite, datasette, sqlite-utils, openai, alex-garcia, embeddings

Weeknotes: GPT-4o mini, LLM 0.15, sqlite-utils 3.37 and building a staging environment

2024-07-19T00:11:14+00:00

Upgrades to LLM to support the latest models, and a whole bunch of invisible work building out a staging environment for Datasette Cloud.

GPT-4o mini and LLM 0.15

Today's big news was the release of GPT-4o mini, which I wrote about here. If you build applications on top of LLMs this is a very significant release - it's the cheapest of the high performing hosted models (cheaper even than Claude 3 Haiku and Gemini 1.5 Flash) and has some notable characteristics, most importantly the 16,000 token output limit.

I shipped a new LLM release to support the new model. Full release notes for LLM 0.15:

Support for OpenAI's new GPT-4o mini model: llm -m gpt-4o-mini 'rave about pelicans in French' #536

gpt-4o-mini is now the default model if you do not specify your own default, replacing GPT-3.5 Turbo. GPT-4o mini is both cheaper and better than GPT-3.5 Turbo.

Fixed a bug where llm logs -q 'flourish' -m haiku could not combine both the -q search query and the -m model specifier. #515

sqlite-utils 3.37

LLM had a frustrating bug involving a weird numpy issue that only manifested on LLM when installed via Homebrew. I ended up fixing that in its sqlite-utils dependency - here are the full release notes for sqlite-utils 3.37:

The create-table and insert-files commands all now accept multiple --pk options for compound primary keys. (#620)

Now tested against Python 3.13 pre-release. (#619)

Fixed a crash that can occur in environments with a broken numpy installation, producing a module 'numpy' has no attribute 'int8'. (#632)

Datasette Cloud staging environment

I'm a big believer in reducing the friction involved in making changes to code. The main reason I'm so keen on the combination of automated tests, GitHub Actions for CI/CD and extensive documentation (as described in Coping strategies for the serial project hoarder) is that

Sadly, Datasette Cloud hasn't been living up these standards as much as I would like. I have great comprehensive tests for it, continuous deployment that deploys when those tests pass and pretty solid internal documentation (mainly spread out across dozens of GitHub Issues) - but the thing I've been missing is a solid staging environment.

This matters because a lot of the most complex code in Datasette Cloud involves deploying new instances of Datasette to Fly Machines. The thing that's been missing is a separate environment where I can exercise my Fly deployment code independently of the production cluster.

I've been working towards this over the past week, and in doing so have found all sorts of pieces of the codebase that are hard-coded in a way that needs to be unwrapped to correctly support that alternative environment.

I'm getting there, but it's been one of those frustrating projects where every step forward uncovers at least one more tiny problem that needs to be resolved.

A lot of these problems relate to the GitHub Actions workflows being used to build, test and deploy my containers. Thankfully Claude 3.5 Sonnet is great at helping refactor GitHub Actions YAML, which has been saving me a lot of time.

I'm really looking forward to wrapping this up, because I plan to celebrate by shipping a flurry of Datasette Cloud features that have been held up by the lack of a robust way to extensively test them before sending them out into the world.

Blog entries

I also updated my write-up of my recent AI World's Fair keynote to include a link to the standalone YouTube video of the talk.

Releases

llm 0.15 - 2024-07-18
Access large language models from the command-line
sqlite-utils 3.37 - 2024-07-18
Python CLI utility and library for manipulating SQLite databases
llm-mistral 0.4 - 2024-07-16
LLM plugin providing access to Mistral models using the Mistral API
datasette-python 0.1 - 2024-07-12
Run a Python interpreter in the Datasette virtual environment

TILs

Trying out free-threaded Python on macOS - 2024-07-13
Accessing 1Password items from the terminal - 2024-07-10

Tags: projects, ai, weeknotes, datasette-cloud, sqlite-utils, llms, llm

sqlite-utils 3.37

2024-07-18T18:44:02+00:00

Release: sqlite-utils 3.37

Tags: sqlite, sqlite-utils

Announcing our DjangoCon US 2024 Talks!

2024-07-17T03:20:57+00:00

Announcing our DjangoCon US 2024 Talks!

I'm speaking at DjangoCon in Durham, NC in September.

My accepted talk title was How to design and implement extensible software with plugins. Here's my abstract:

Plugins offer a powerful way to extend software packages. Tools that support a plugin architecture include WordPress, Jupyter, VS Code and pytest - each of which benefits from an enormous array of plugins adding all kinds of new features and expanded capabilities.

Adding plugin support to an open source project can greatly reduce the friction involved in attracting new contributors. Users can work independently and even package and publish their work without needing to directly coordinate with the project's core maintainers. As a maintainer this means you can wake up one morning and your software grew new features without you even having to review a pull request!

There's one catch: information on how to design and implement plugin support for a project is scarce.

I now have three major open source projects that support plugins, with over 200 plugins published across those projects. I'll talk about everything I've learned along the way: when and how to use plugins, how to design plugin hooks and how to ensure your plugin authors have as good an experience as possible.

I'm going to be talking about what I've learned integrating Pluggy with Datasette, LLM and sqlite-utils. I've been looking for an excuse to turn this knowledge into a talk for ages, very excited to get to do it at DjangoCon!

Tags: django, djangocon, plugins, python, speaking, datasette, sqlite-utils, llm

fastlite

2024-05-27T21:14:01+00:00

fastlite

New Python library from Jeremy Howard that adds some neat utility functions and syntactic sugar to my sqlite-utils Python library, specifically for interactive use in Jupyter notebooks.

The autocomplete support through newly exposed dynamic properties is particularly neat, as is the diagram(db.tables) utility for rendering a graphviz diagram showing foreign key relationships between all of the tables.

Via @jeremyphoward

Tags: python, sqlite, jupyter, sqlite-utils, jeremy-howard

sqlite-utils-shell 0.3

2023-12-21T06:24:17+00:00

Release: sqlite-utils-shell 0.3

Tags: sqlite, sqlite-utils

Weeknotes: datasette-enrichments, datasette-comments, sqlite-chronicle

2023-12-08T06:04:54+00:00

I've mainly been working on Datasette Enrichments and continuing to explore the possibilities enabled by sqlite-chronicle.

Enrichments

This is the biggest new Datasette feature to arrive in quite a while, and it's entirely implemented as a plugin.

I described these in detail in Datasette Enrichments: a new plugin framework for augmenting your data (with an accompanying YouTube video demo). The short version: you can now install plugins that can "enrich" data by running transformations (or data fetches) against selected rows - geocoding addresses, or executing a GPT prompt, or applying a regular expression.

The datasette-enrichments plugin provides the mechanism for running these enrichments. Other plugins can then depend on it and define all manner of interesting options for enriching and transforming data.

I've built four of these so far, and I wrote some extensive documentation to help people build more. I'm excited to see how people use and build further on this initial foundation.

Datasette Comments

Alex Garcia released the first version of datasette-comments as part of our continuing collaboration to build out Datasette Cloud.

He wrote about that on the Datasette Cloud blog: Annotate and explore your data with datasette-comments.

This is another capability I've been looking forward to for years: the plugin lets you leave comments on individual rows within a Datasette instance, in order to collaborate with others on finding stories in data.

sqlite-chronicle and datasette-chronicle

I first wrote about sqlite-chronicle in weeknotes back in September. This week, inspired by my work on embeddings, I spent a bit more time on it and shipped a 0.2 release.

sqlite-chronicle is a Python library that implements a SQL pattern where a table can have a _chronicle_tablename companion table created, which is then updated using triggers against the main table.

The chronicle table has a shadow row for every row in the main table, duplicating its primary keys and then storing millisecond timestamp columns for added_ms and updated_ms, an integer version column and a deleted boolean indicator.

The goal is to record when a row was last inserted or updated, with an atomically incrementing version ID representing the version of the entire table.

This can then enable all sorts of interesting potential use-cases:

Identify which rows have been updated or inserted since a previously recorded version
Synchronize a table with another table, only updating/inserting/deleting rows that have changed since last time
Run scheduled tasks that only consider rows that have changed in some way

The relevance to enrichments is that I'd like to implement a form of "persistent" enrichment - an enrichment which is configured to run repeatedly against new or updated rows, geocoding new addresses for example.

To do that, I need a mechanism to identify which rows have already been enriched and which need to be enriched again. sqlite-chronicle is my current plan to provide that mechanism.

It's still pretty experimental. I recently found that INSERT OR REPLACE INTO queries don't behave how I would expect them to, see issue #7.

I also started a new plugin to accompany the feature: datasette-chronicle, which adds two features to Datasette:

"enable/disable chronicle tracking" table actions for users with the correct permissions, which can be used in the Datasette UI to turn chronicle tracking on and off for a specific table
For tables that have chronicle enabled, a ?_since=VERSION querystring parameter which can be used to filter the table to only rows that have changed since the specified version

I'm running the plugin against the documents table on demos.datasette.cloud - see _chronicle_documents there for the result. That table is populated via GitHub scheduled actions and the Datasette API, as described in Getting started with the Datasette Cloud API - it's also where I first spotted the INSERT OR REPLACE INTO issue I described earlier.

Newsroom Robots

I recorded an episode of the Newsroom Robots AI in journalism podcast with Nikita Roy a couple of weeks ago.

She split our conversation into two episodes:

Simon Willison (Part One): Breaking Down OpenAI's New Features & Security Risks of Large Language Models - which I ended up using as the basis for two blog entries:
- I'm on the Newsroom Robots podcast, with thoughts on the OpenAI board
- Prompt injection explained, November 2023 edition
Simon Willison (Part Two): How Datasette Helps With Investigative Reporting which has the best audio description of Datasette I've managed to produce so far.

sqlite-utils 3.36

Quoting the release notes.

Support for creating tables in SQLite STRICT mode. Thanks, Taj Khattra. (#344)

CLI commands create-table, insert and upsert all now accept a --strict option.

Python methods that can create a table - table.create() and insert/upsert/insert_all/upsert_all all now accept an optional strict=True parameter.

The transform command and table.transform() method preserve strict mode when transforming a table.

The sqlite-utils create-table command now accepts str, int and bytes as aliases for text, integer and blob respectively. (#606)

Taj Khattra's contribution of the --strict and strict=True options is a beautiful example of my ideal pull request: a clean implementation, comprehensive tests and thoughtful updates to the documentation all bundled together in one go.

Releases

sqlite-utils 3.36 - 2023-12-08
Python CLI utility and library for manipulating SQLite databases
datasette-leaflet-geojson 0.8.1 - 2023-12-07
Datasette plugin that replaces any GeoJSON column values with a Leaflet map.
datasette-chronicle 0.2 - 2023-12-06
Enable sqlite-chronicle against tables in Datasette
datasette-enrichments-jinja 0.1 - 2023-12-06
Datasette enrichment for evaluating templates in a Jinja sandbox
sqlite-chronicle 0.2.1 - 2023-12-06
Use triggers to track when rows in a SQLite table were updated or deleted
datasette-enrichments-gpt 0.3 - 2023-12-01
Datasette enrichment for analyzing row data using OpenAI's GPT models
datasette-statistics 0.2.1 - 2023-11-30
SQL statistics functions for Datasette
datasette-enrichments-opencage 0.1 - 2023-11-30
Geocoding and reverse geocoding using OpenCage
datasette-enrichments-re2 0.1 - 2023-11-30
Enrich data using regular expressions powered by re2
datasette-enrichments 0.2 - 2023-11-29
Tools for running enrichments against data stored in Datasette
datasette-pretty-json 0.3 - 2023-11-28
Datasette plugin that pretty-prints any column values that are valid JSON objects or arrays

TILs

Grabbing a transcript of a short snippet of a YouTube video with MacWhisper - 2023-12-01
Cryptography in Pyodide - 2023-11-26
Running pip install '.[docs]' on ReadTheDocs - 2023-11-24

Tags: projects, sqlite, datasette, weeknotes, sqlite-utils, enrichments

sqlite-utils 3.36

2023-12-08T05:32:21+00:00

Release: sqlite-utils 3.36

Tags: sqlite, sqlite-utils

Simon Willison's Weblog: sqlite-utils

sqlite-utils 4.1.1

sqlite-utils 4.1

sqlite-utils 4.0, now with database schema migrations

Database schema migrations using sqlite-utils

Prior art

Migrating from sqlite-migrate

Everything else in sqlite-utils 4.0

Claude Fable 5 helped a lot

sqlite-migrate 0.2

sqlite-utils 4.0

sqlite-utils 4.0rc4

sqlite-utils 4.0rc3

sqlite-utils 4.0rc2, mostly written by Claude Fable (for about $149.25)

And a final review by GPT-5.5

For an estimated (unsubsidized) cost of $149.25

The full release notes for sqlite-utils 4.0rc2

sqlite-utils 4.0rc1 adds migrations and nested transactions

New feature: migrations

New feature: db.atomic() transactions

Backwards incompatible changes

Try it out

sqlite-utils 4.0rc1

Under the hood of Canada Spends with Brendan Samek

Build Canada and Canada Spends

Datasette for data exploration

Processing PDFs

Further reading

sqlite-utils 3.39

sqlite-utils 3.39

sqlite-utils 4.0a1 has several (minor) backwards incompatible changes

sqlite-utils 4.0a1

Datasette ecosystem poster for PyCon US

sqlite-utils 4.0a0

sqlite-utils 4.0a0

sqlite-utils-tui 0.1

Ask questions of SQLite databases and CSV/JSON files in your terminal

Asking questions of CSV and JSON data

Implementation notes

sqlite-utils 3.38

sqlite-utils 3.38a0

Using sqlite-vec with embeddings in sqlite-utils and Datasette

Weeknotes: GPT-4o mini, LLM 0.15, sqlite-utils 3.37 and building a staging environment

GPT-4o mini and LLM 0.15

sqlite-utils 3.37

Datasette Cloud staging environment

Blog entries

Releases

TILs

sqlite-utils 3.37

Announcing our DjangoCon US 2024 Talks!

fastlite

sqlite-utils-shell 0.3

Weeknotes: datasette-enrichments, datasette-comments, sqlite-chronicle

Enrichments

Datasette Comments

sqlite-chronicle and datasette-chronicle

Newsroom Robots

sqlite-utils 3.36

Releases

TILs

sqlite-utils 3.36