Datasette stops assuming that every database is immutable

An immutable database gives a publishing tool unusually strong caching assumptions. Count its rows once, identify its contents with a hash and let an HTTP cache retain responses for a long time. Those assumptions fail when another process inserts a row into the same file. Datasette 0.28, released on 19 May, changes the default so that serving a database no longer asserts that its contents will stay fixed.

The release makes it safe to point Datasette at a SQLite database being updated by another process. Its version-pinned changelog describes the work behind that change, including reconsideration of table counts and content-hash URLs. This is a change to the publisher's assumptions about the database. The database engine's existing ability to coordinate concurrent access is the basis for it.

I appreciate SQLite's ability to support a useful application without a separate database server. A browser over the same local database is a natural extension of that arrangement. Requiring a frozen export before browsing can be a sensible publication policy, but it should be an explicit choice when the database already has an active writer.

Reading and immutability are different promises

Datasette's earlier design centered on databases that would not change. The release notes explain how this allowed static database copies inside containers and aggressive caching. A process that only reads a database does not, by itself, establish that stronger condition. Another process can still own the write side of the workload.

That difference determines how SQLite should open the file. The 0.28 performance documentation says immutable mode disables locking and change detection. Those are useful optimizations only when the promise of unchanged data is true. Choosing that mode because Datasette itself does not write would confuse the behavior of one process with the lifetime of the file.

The new default supports the weaker, more broadly useful assumption. The file can change while Datasette serves it. A publisher who can guarantee that it will remain fixed can still opt into immutable mode with the -i argument. The command line makes the assertion visible.

datasette serve live.db
datasette -i published.db

These examples describe separate uses. The first lets an independently maintained database remain mutable. The second declares a fixed publication artifact. They do not describe two interchangeable spellings for a read-only web interface. A publisher has to know which kind of file is being supplied.

A row count has a lifetime

Table counts are an immediate consequence of the new default. A count calculated when a fixed database starts serving can remain valid for the lifetime of that database. With inserts or deletes, the same number becomes historical information. Retaining it indefinitely would make the interface disagree with the data it can query.

The performance documentation describes the immutable optimization explicitly. Datasette calculates and caches each table's row count at startup. For a large file, that initial counting can itself increase startup time. An immutable publication can move the work earlier with datasette inspect, storing the resulting counts in a JSON file.

datasette inspect published.db --inspect-file=counts.json
datasette -i published.db --inspect-file=counts.json

The immutable flag is necessary in the second command. The documentation says Datasette ignores counts from that JSON file for a mutable database. This rule prevents an optimization file from silently becoming an authority about changing content. Precomputation is valid because the publication artifact stays fixed, rather than because the count happens to have been saved somewhere.

That also changes what the inspection command means. The 0.28 changelog says datasette inspect now calculates table counts without introspecting other database metadata. This release removes the old inspection page as well. Its narrower inspection command fits a specific startup optimization for a fixed publication artifact, rather than a general mechanism for describing a database forever.

HTTP cache identity must follow content

A table URL names a queryable resource, but a mutable resource can return a different answer tomorrow. The 0.28 documentation gives the ordinary default cache lifetime as five seconds. A publisher can set it to zero to disable HTTP caching or override it for individual requests with _ttl. These are time-based policies for responses whose underlying data may change.

Immutable files support a stronger option. Datasette calculates a SHA-256 content hash when opening such a file. Enabling hashed URL mode redirects an ordinary database URL to one containing a shortened hash. The documentation gives the hashed response cache lifetime a default of 365 days. A changed database obtains another content identity, which allows those long-lived responses to remain associated with the old content.

Before 0.28, hashed URLs were the default because the application assumed every database was immutable. Now the publisher enables that behavior through the hash_urls configuration or the _hash=1 request argument for an immutable file. The optimization survives, but its premise is no longer hidden in the default startup behavior.

This matters to an API client as much as to a browser. A client using an ordinary URL can follow the redirect to the currently published hashed resource. A client retaining the hashed URL deliberately keeps referring to that content identity. The documentation recommends ordinary URLs for clients that should not remain attached to the long-cached version.

What the new default enables

The change supports a division of responsibilities in which one process maintains a SQLite database and Datasette exposes it for inspection. It does not require the inspecting process to become the owner of all writes. The release notes ground the choice in SQLite's concurrent access support, with the qualification that only one thread attempts a write at a time.

I favor keeping the immutable path because distributing a fixed SQLite artifact remains useful. The improvement is that the command now distinguishes that publication model from browsing a database that continues to receive updates. Cached counts and content-addressed URLs remain available where their assumptions hold, while a changing file can use the default serving behavior without making the same promise.