Datasette publishes immutable SQLite databases as queryable APIs

Publishing a dataset often leaves the reader choosing between downloading a file and using a fixed set of API endpoints. Simon Willison's first public Datasette release on 13 November combines a downloadable database's query flexibility with a hosted HTTP interface. A publisher supplies SQLite files, and readers can browse tables or submit their own SQL queries.

The design depends on a deliberate restriction. Datasette publishes a read-only database. It opens the SQLite files as immutable and builds the data's identity into its URLs. That choice connects its query interface to its deployment and caching model. The publisher distributes a fixed dataset, while readers choose how to examine it.

I find this a persuasive use of SQLite because it takes the database file seriously as a publication artifact. The schema and data can travel together. Adding an HTTP interface makes that artifact available to readers who need a query result immediately, without requiring each of them to install a database tool first.

Readers choose the query

The launch includes databases derived from FiveThirtyEight's Creative Commons CSV files and the ParlGov database of political parties. The examples demonstrate ordinary table browsing as well as SQLite views. A database prepared for publication can therefore contain useful query structure alongside its source rows.

Every operation has a URL. A table page renders HTML, while corresponding .json and .jsono URLs return machine-readable results. The README shipped in the launch-day 0.8 source distinguishes the two JSON representations. Ordinary JSON contains a column-name array and rows represented as arrays. The object representation associates column names with values in each row.

Those formats make a practical tradeoff. Repeating names in every row is convenient for consumers but produces a less compact representation. An API client can choose the form that fits its use. The same dataset remains available through a browser page, so exploring it and integrating it need not start with separate services.

Simple filters are URL parameters. Willison's example selects Bob Ross episodes with particular painting elements by adding conditions for clouds, mountains and bushes. These parameters give a reader a useful first query without requiring SQL. The response can still be requested as JSON for use by another program.

For questions beyond those filters, Datasette accepts SQL through a query parameter. The launch demonstrates aggregates over the painting dataset. This changes the division of work between publisher and reader. The publisher need not predict every useful grouping or selection in advance, because the reader can express a new read query against the published schema.

Named parameters separate SQL from values

The launch also demonstrates named SQL parameters. A query can include a placeholder and receive its value separately through the URL. For example, the article uses a query against the senators dataset to search tweet text.

select * from [twitter-ratio/senators] where "text" like :q

The request supplies the pattern as q, while the SQL statement retains :q. This is useful beyond assembling a URL. Datasette's initial SQL checks reject statements containing the string PRAGMA, because pragmas can change database settings. Passing a search term as a bound value lets the term contain that sequence of characters without placing it in the statement text.

That behavior is a specific property of the launch implementation. A string check is not a general description of SQL authorization, and the announcement does not present it as a universal parser-level security model. The demonstrated parameter mechanism gives values a separate path, which also keeps a reusable query distinct from a particular search term.

I like the combination of basic filtering and an explicit SQL interface. A reader can begin with a table page, discover the column names and then ask a more detailed question. The publication retains its relational structure instead of requiring the publisher to reduce every possible use to a custom endpoint.

Immutable data changes HTTP caching

Datasette includes the first characters of a SHA-256 hash of the database contents in API URLs. A changed database therefore gets a changed content-derived URL. The launch uses this to send year-long cache-expiry headers for those URLs.

The relationship is straightforward. A long cache lifetime is difficult when a URL silently changes its meaning as rows change underneath it. A URL tied to the database contents can remain valid for the corresponding immutable publication. Browsers and intermediary caches can reuse responses without needing every request to reach the database process.

Willison hosts the examples behind Cloudflare. That deployment illustrates how the database service and an intermediary cache cooperate, but the announcement supplies no general latency measurement. The architectural point is the cache key. The identity of the published data participates in the URL, so replacing the dataset creates a new address for its content.

This model also gives a citation a more precise target. A query against a particular database identity describes a question asked of particular contents. A later publication may contain different rows and produce different answers. The content-derived URL lets those publications remain distinguishable instead of requiring the reader to infer which version was available when a result was obtained.

The database travels with the application

The launch provides a local command that serves a SQLite database through a web interface. The README requires Python 3.5 or higher and describes port 8001 as the default. Its serve command is also the default subcommand, so a database path alone is sufficient after the executable name.

pip3 install datasette
datasette serve dataset.db

Publishing uses the same database-oriented model. With Zeit Now installed, datasette publish now dataset.db builds a Docker image containing the application and the supplied database files, deploys it and returns a URL. The separate datasette package command builds an image locally for another deployment workflow.

Putting a fixed database in the image makes the deployed application self-contained with respect to its published data. Additional containers can serve the same contents without coordinating writes to that publication. This follows from the read-only design. It does not describe a method for replicating a changing application database between writers.

The README also provides metadata for titles, licensing and source information. Datasette displays those fields in its pages and includes them in JSON. This matters for a dataset publication because correct query results do not by themselves tell a reader where the data came from or what reuse is permitted. The metadata remains available alongside the data in both human and programmatic interfaces.

A deployment may include several database files. The launch command and packaging interface both accept that arrangement, so the publication unit need not collapse unrelated datasets into one schema. Each supplied database can retain its own tables while the deployed service provides a common browsing entry point. The publisher still decides which files belong in the image before readers begin querying them.

Read-only queries still consume resources

Immutability removes the need to coordinate writes to the published files, but a read query can still take substantial work. The launch places limits on query execution and response size. Its default query threshold is 1,000 milliseconds, and it refuses to return more than 1,000 rows at once. Willison identifies JSON serialization overhead as part of the reason for the row limit.

These controls address different costs. A query can perform a large computation and return few rows, while a simple query can return enough data to make encoding and transfer expensive. Keeping execution and output limits separate fits the decision to accept questions the publisher did not write.

The implementation uses Sanic and Python's async/await support for the web service. SQLite interactions run in a thread pool managed by ThreadPoolExecutor. The launch explicitly distinguishes the asynchronous request handling from the database calls. Calling the server asynchronous does not mean that SQLite itself has acquired an asynchronous Python interface.

Cross-origin access is also part of the publishing story. The launch describes JSON responses available to browser code on other sites, and the 0.8 command help exposes a --cors option for sending Access-Control-Allow-Origin: *. That makes the hosted data useful to independent web pages when the server enables that behavior. It is an access policy at the HTTP layer, separate from SQLite's immutable file access.

For me, the important constraint is that updates produce another publication. Datasette's initial design does not try to combine arbitrary reader SQL with a mutable public database. It packages a fixed dataset, gives its contents an identity and lets readers query that identified publication through HTML or JSON. Those choices explain why its caching and deployment mechanisms fit together.