> "My breakthrough was the realization that read-only data packaged as a SQLite database could be deployed to inexpensive serverless hosting"

My breakthrough was the realization that any text data packaged as a SQLite database could be deployed to inexpensive serverless hosting

And binary data too! https://simonwillison.net/2020/Jul/30/fun-binary-data-and-sq...

Simon thanks for taking the time to explain Datasette internals.

Not a Python developer myself, I was intrigued by the use of hooks to extend Datasette and ended up looking into pluggy and how Datasette plugins use hooks.

I noticed some Datasette hooks like 'extra_template_vars' override values set by other hooks, while 'extra_body_script' simply append stuff to a list.

How does Datasette avoid plugins clashing with each other? Say one plugin writes a header and another overrides it.

That's something I'm still figuring out. In particular, I want plugins that add extra content to pages to be able to collaborate with each other. I have an open issue for that here: https://github.com/simonw/datasette/issues/1191

I'm not too worried about extra template vars overrides though - that will only be a problem if two plugins accidentally use the same variable name. Maybe I should encourage plugins to use a namespace based on their name in the documentation though.

What can government data providers like data.cdc.gov do to support datasette better? Is it expected that these providers would make SQLite distros of data? Or do you think people will just suck stuff down in csv and convert it to datasette to make it easier to work with and reproduce the work of others?

My ultimate dream is that providers like that will use Datasette itself - or a similar system that has the same characteristics: make it really easy for people to slice and dice the data using querystring parameters or even SQL queries and get back out just the data they want as JSON, CSV and other formats.

I do think that SQLite is a really interesting format for publishing data, and I'd love to see more places publish raw SQLite files. It's much better at preserving things like column type information and relationships between tables than CSV is.

For sites that are running in CKAN using the datastore plugin on CSV/tabular resources, there is essentially a sql endpoint to make queries against that data. There are also automated conversions of that data to csv/json/xml. In my experience though, there's not a lot of activity around those apis -- people tend to just download the data.

(CKAN is an open source, traditional web app style (flask/jinja/postgresql) open data portal that powers a bunch of open data portals, including data.gov.ie)

Simon, I have a wish list item for Datasette, pagination of ad-hock queries. I know thats a really difficult thing to implement as it would require parsing and altering the sql query, but with large datasets it would be so useful!

(Love Datasette!)

I want this too!

Datasette avoids offset/limit pagination because it performs poorly on huge queries - and I don't want random visitors (and crawlers) hurting performance of a public instance by crawling through offset/limit of thousands of pages.

That's why table pages implement keyset pagination instead - so you can do https://congress-legislators.datasettes.com/legislators/legi... and get back records following the one with A000106, which is a fast query because the ID column has an index on it.

Supporting this with arbitrary queries is harder. One idea I had is to allow the user to specify which column and sort order should be used for keyset pagination - so you could construct a URL like this:

    /?sql=select+*+from+legislators+order+by+id&_pagination_column=id

If a pagination column has been specified, Datasette would use the same trick it uses on regular table pages and add next links that way.

Would that work for you?

The other, probably easier option is a setting that enables offset/limit pagination of arbitrary SQL queries - turned off by default, but easy to turn on for users who are running Datasette on a private server. If that takes several seconds people can at least opt into it.

Either would work, but as you said the latter is easer to implement and would have done the job for what I was working on. If I had had more time I would have looking into trying to do it myself, but there is never enough time...