Datasette gives SQLite clients a choice of JSON shapes

A JSON API can make a relational table easy to fetch while still making it awkward to consume. Column names might live separately from values, a charting library might expect an array of objects, and a lookup widget might want a dictionary keyed by record identity. Those are different representations of the same rows. Datasette 0.21 lets the client request the representation that matches its immediate use.

The 5 May 2018 release adds a root-level array shape and changes the root-level object shape. It also adds controls for table page size and searching individual full-text columns. Together, these changes make the published SQLite database easier to use directly from a consumer without writing a separate response adapter for each application.

I like the fit between SQLite's file-based model and a tool that publishes a queryable dataset. The useful question here is how much application code needs to sit between a database and its reader. Response shapes remove some of that code, provided the reader understands what the selected shape leaves out.

Rows and response metadata are separate decisions

The JSON API documentation at the 0.21 tag describes the default response as an object containing column names, rows and other information about the query. Each row is an array, and the order of its values matches the separate columns array. A consumer combines the two to assign names to values.

That representation avoids repeating every column name for every row. It also keeps query metadata in the response. The documented example includes a continuation token and a next-page URL, alongside a flag indicating truncation and the measured query time. A client that needs to retrieve more than one page can use those fields.

With _shape=objects, each row becomes an object with named properties while the surrounding response object remains. This changes how the consumer accesses a value without discarding the envelope. A client can read a property such as row.title and still inspect pagination metadata outside the rows.

The new _shape=array goes further. The entire response becomes an array of row objects. For an illustrative table of documents, the resulting data can have this form.

[
  {"id": 1, "title": "Database notes"},
  {"id": 2, "title": "Query examples"}
]

This is convenient when a component expects to receive an array directly. There is no outer object to unpack. There is also no outer object in which to find the normal continuation fields. Selecting a shape therefore changes more than the spelling of an accessor in JavaScript.

A consumer that must traverse all results needs to choose its representation with that requirement in mind. A root array can be appropriate for a known bounded result, while an enveloped shape retains the documented pagination information. Increasing page size does not turn the array into a promise that the response contains an entire table.

Record identity can determine the root object

The _shape=object representation returns an object keyed by the primary keys of the rows. Each value is itself a row object. The release changes this option so the keyed object is the response root, making it usable as a lookup structure without another level of nesting.

The documentation limits this shape to table queries. Views and custom SQL queries do not provide an obvious primary key for the mapping. That restriction follows from the representation itself. An arbitrary query can produce rows whose apparent identity is neither unique nor stable, so a dictionary requires information the query does not necessarily supply.

Keys in this JSON object are strings. For a compound primary key, Datasette joins the key components with commas. A consumer must therefore follow the documented key representation instead of assuming that an integer database identifier remains a numeric object key in JSON.

The difference between objects and object is easy to overlook. Using the plural spelling changes the rows within an envelope. The singular spelling selects a keyed root object. Version 0.21 also renames the default shape to arrays, replacing lists, so code that explicitly requests a shape needs to use the release's vocabulary.

Page size remains subject to the server limit

The new _size parameter lets a table client request a page size. A request such as ?_size=1000 asks for a larger page, while ?_size=max requests the configured maximum. The documentation says neither can exceed the server's max_returned_rows setting.

This gives a consumer a useful degree of control without handing it control over the server's response limit. A compact widget can request fewer rows. A data consumer can request more. Both remain within the publishing application's configured bound.

Column-specific search uses _search_COLUMN=keywords. It applies to tables configured for SQLite full-text search and narrows the search to the selected indexed column. Searching a document title can express a different question from searching every indexed field, even when the same keywords appear in the request.

The release also changes the query time-limit parameter to _timelimit. Its documentation gives autocomplete as a use case for a small budget. This parameter limits query execution time, whereas _size controls returned rows. A small response is not necessarily a cheap query, and these controls address different parts of the work.

For me, the strongest part of this release is that these choices remain request parameters on the published database. A client can select named row objects, preserve the pagination envelope and constrain a search to a column without requiring a new custom endpoint. The server still defines its row limits and the database still defines the identities that a keyed response can represent.