Datasette as data tool for REST APIs

[jussiarpalahti]

At City of Helsinki we are thinking of how to make our open data easier to access. We have many REST APIs here where there’s possibly interesting data besides those APIs intended users. Datasette was selected for experiment to make that data more easily digestible. Its web interface means not having to deal with HTTP and understand JSON structures. Data is periodically read from APIs leveraging their OpenAPI specifications and query results are converted for SQLite using Datasette and its SQLite tooling. End result should allow users to explore data and download it for further use in spreadsheet.

REST divides data into endpoints with JSON documents that can have deep structure and relations between them. Both inside and across endpoints. In conversion to Datasette storage endpoints become tables. JSON documents become rows with flat fields (number, string, booleans, null) going into columns. Links with arrays and objects aren’t as straightforward. Some can be flattened into separate columns through naming (field.subfield -> field_subfield), but fields that link to other documents (say, places point to common address or administrative unit) doesn’t seem to have a natural place in Datasette’s preferred SQL database structure.

I wanted to introduce what we’re doing with Datasette and ask if community has ideas about how to make Datasette even better for this use case. One option I see is store deep fields as JSON and leverage SQLite JSON queries to join their relations into result rows. Other option could be to build the relations on SQL level using foreign keys and many to many tables. Both are approaches that I’m not sure about how to do with Datasette yet.

Test service: http://hrids.westeurope.azurecontainer.io/servicemap
And code to convert API data into Datasette: https://github.com/City-of-Helsinki/hri-api-publisher/